All things dbt

TIL_20250226_dbt.md

dbt Core

set up dbt (Data Build Tool) for using Postgres locally

To set up dbt (Data Build Tool) for using Postgres locally, you need to:

1. Install dbt on your local machine.
2. Create a dbt project.
3. Configure the dbt project to connect to your local Postgres database.
4. Run dbt commands to test the setup.

Here are the detailed steps:

Step 1: Install dbt

You can install dbt using pip:

pip install dbt-postgres

This will install both dbt-core and its plugin dbt-postgres:

$ dbt --version
installed version: 1.0.0
   latest version: 1.0.0

Up to date!

Plugins:
  - postgres: 1.0.0

Step 2: Create a dbt Project

Navigate to the directory where you want to create your dbt project and run the following command:

dbt init my_dbt_project

Replace my_dbt_project with the name of your project.

Step 3: Configure the dbt Project

Navigate to the project directory:

cd my_dbt_project

Open the profiles.yml file located in the ~/.dbt directory (create it if it doesn't exist) and add the following configuration:

my_dbt_project:
  target: dev
  outputs:
    dev:
      type: postgres
      host: localhost
      user: postgres
      password: postgres
      port: 5433  # This should match the port mapping in your docker-compose file for the postgres_dbt container
      dbname: nyc_taxi
      schema: public

Step 4: Run dbt Commands

You can now run dbt commands to test the setup. For example, to run the dbt debug command to test the connection:

dbt debug

If the connection is successful, you can start developing your dbt models. Here are some common dbt commands:

• dbt run: Compile and run the models in your project.
• dbt test: Run tests on your data.
• dbt docs generate: Generate documentation for your project.
• dbt docs serve: Serve the documentation locally.

Example dbt Model

Create a simple dbt model to test the setup. Create a file named example_model.sql in the models directory of your dbt project with the following content:

-- models/example_model.sql

SELECT *
FROM {{ ref('my_table') }}

Replace my_table with the name of a table in your Postgres database.

Running the Model

Run the dbt model:

dbt run

This will execute the SQL in example_model.sql and create a table/view in the Postgres database.

By following these steps, you will have dbt set up to work with your local Postgres database using Docker.

dbt Cloud

Commonly used dbt commands:

• dbt init: Initialize a new dbt project.
• dbt defer: Defers compilation of a model, allowing you to break up long-running models.
• dbt run: Runs all compiled SQL scripts in the project.
• dbt test: Tests all models in the project.
• dbt build: Runs dbt seed, dbt run, dbt test, and dbt snapshot. together. Builds and tests all selected resources (models, seeds, snapshots, tests).
• dbt seed: Loads data from CSV files into the database.
• dbt docs generate: Generates documentation for the project.
• dbt docs serve: Serves the generated documentation on a webserver.

For more information, refer to the dbt documentation.