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

as best practice when should one run dbt deps command?

Running the dbt deps command is essential to ensure that all dependencies specified in your packages.yml file are correctly installed and up-to-date. Here are some best practices for when to run dbt deps:

1. After Updating packages.yml

Whenever you add, remove, or update a package in your packages.yml file, you should run dbt deps to install the new dependencies or update the existing ones.

2. After Cloning a Repository

If you clone a dbt project from a repository, you should run dbt deps to install all the dependencies specified in the packages.yml file. This ensures that your local environment has all the necessary packages to run the project.

3. Before Running dbt Commands in CI/CD Pipelines

In Continuous Integration/Continuous Deployment (CI/CD) pipelines, it's a good practice to run dbt deps before executing any dbt commands like dbt run, dbt test, or dbt build. This ensures that all dependencies are installed and up-to-date in the CI/CD environment.

4. Periodically to Check for Updates

Periodically running dbt deps can help ensure that your project is using the latest versions of the dependencies specified in your packages.yml. This can be part of your regular maintenance routine.

5. After Switching Branches

If you switch branches in your version control system (e.g., Git), and the new branch has different dependencies specified in the packages.yml file, you should run dbt deps to ensure that the correct dependencies are installed for the branch you are working on.

Summary

Running dbt deps is a crucial step in maintaining a dbt project. Here’s a summary of when to run the command:

• After updating the packages.yml file
• After cloning a repository
• Before running dbt commands in CI/CD pipelines
• Periodically to check for updates
• After switching branches

By following these best practices, you can ensure that your dbt project has all the necessary dependencies installed and up-to-date.

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.