DBT for Everyone: Get Started Without the Cloud

dbt, which stands for Data Build Tool, is a popular open-source tool specifically designed to streamline data transformation within data warehouses.

Here’s a breakdown of how dbt helps with data transformation:

• Focus on SQL: dbt allows data analysts and engineers to leverage their existing SQL knowledge to define data transformations. Essentially, you write SQL queries to transform raw data into usable datasets for analysis.
• Modular and Reusable: Transformations are written as modular components, making them reusable across different models. This saves time and reduces redundancy in your code.
• Automated Testing: dbt integrates data quality checks into the process. You can write tests to ensure the transformed data meets specific criteria before it’s used for analysis.
• Documentation: dbt automatically generates documentation for your data transformations, making it easier for others to understand the data and how it’s derived.

To set up dbt (Data Build Tool) on macOS, you can follow these steps:

There are two main methods to install dbt on your Mac:

• Using Homebrew (if applicable): If you already use Homebrew as a package manager on your Mac, you can install dbt and the adapter with these commands:

brew update
brew install git brew tap dbt-labs/dbt
brew install dbt dbt-postgres

• Using pip: This is the recommended method for most users. Open your terminal and run the following command:
  you can use the following commands or as shown in the screenshot

pip install dbt-postgres
pip install dbt dbt-core

During the installation, it will ask you to update pip if you are not running the latest version

Verify the installation:
Now that the dbt is installed, let's verify it. Run the following command to check if dbt is installed successfully

Project Setup and Initialization:
Use the terminal to create a new directory for your dbt project. It is recommended to create a project folder in lowercase. Navigate to your desired location. Within your project directory, initialize a new dbt project using the following command

dbt init <project-name>
dbt init learn_dbt

When you run the dbt init command, dbt will create a new directory with the necessary structure and configuration files for a dbt project.

Verify project directory:
This will create a new dbt project in the learn_dbt directory, with the following structure

learn_dbt/
├── dbt_project.yml
├── models/
├── seeds/
├── sources/
├── tests/
├── macros/
├── analysis/
└── docs/

With our dbt project created, let's bring in dbt Postgres support and configure the Postgres docker instance that will run locally

Create a Docker Compose file
To create a PostgreSQL container using Docker Compose, we need to define its configuration in a Docker Compose file. Create a new file called docker-compose.ymlHere is the sample docker-compose file that I will be using for this article, make sure you configure yours with a stronger password and your project requirements need

With our docker-compose file ready, let's start the Postgres docker and test the connectivity to the database

Connection profiles
In a dbt project, the profiles.yml file is used to store connection information and other settings for different environments or profiles. This file is typically located in the ~/.dbt directory, which is the default location for dbt configuration files. Here’s an example configuration for Postgres:

Customize the values according to your specific database connection details.

Running dbt: dbt offers various commands to manage your project. Here are a few common ones:

• dbt compile: Compiles your SQL files into executable SQL statements.
• dbt run: Runs the compiled SQL statements against your data warehouse.
• dbt test: Executes any data quality tests you've written for your models.

Test the connection:
Run the following command to check if dbt can connect to your database using the configured profile:

You should see debug information about your database connection if it is successful.

After successful compilation, run the command to execute the compiled statements against your PostgreSQL database:

With the successful execution, let's validate the changes in the Postgres db running in the docker

Now let’s run our test via dbt to check uniqueness and not-null data inside the dbt models, open the schema.yml file, and you will find a sample test written there

Test run result via dbt

dbt will help you structure your transformation and as a platform tool will anticipate your future needs like testing, deploying to different environments, etc.

You have now set up dbt on your macOS machine. You can start building and running your dbt models by following the dbt documentation and using the dbt CLI commands.

And that’s a wrap!

I appreciate you and the time you took out of your day to read this! Please watch out (follow & subscribe) for more, Cheers!