Skip to content

12. DBT

Jump to bottom
Janemesi edited this page Oct 22, 2025 · 5 revisions

What is dbt?

dbt is a transformation workflow that helps you get more work done while producing higher quality results. You can use dbt to modularize and centralize your analytics code, while also providing your data team with guardrails typically found in software engineering workflows. Collaborate on data models, version them, and test and document your queries before safely deploying them to production, with monitoring and visibility.

dbt compiles and runs your analytics code against your data platform, enabling you and your team to collaborate on a single source of truth for metrics, insights, and business definitions. This single source of truth, combined with the ability to define tests for your data, reduces errors when logic changes, and alerts you when issues arise.

Structure of dbt

dbt follows a modular and structured approach to building data pipelines It consists of three main components:

  • Models: SQL-based transformations that define how raw data should be transformed into analysis-ready data
  • Tests: Automated tests to ensure data quality and integrity throughout the pipeline
  • Documentation: Automatically generated documentation to maintain data lineage and promote collaboration

dbt vs. Classical SQL Editors

dbt offers several advantages over traditional SQL editors:

  • Reproducibility: dbt ensures consistent and reproducible data transformations by leveraging version control.
  • Collaboration: With dbt, multiple team members can work on the same codebase, promoting collaboration and reducing duplication.
  • Automation: dbt automates the execution of data transformations, making it easy to build and maintain data pipelines at scale.

Jinja Templating

dbt leverages Jinja templating language for dynamic SQL transformations Jinja allows for the use of control structures, variables, and macros in SQL code This enables conditional logic, loops, and modular code reusability, enhancing the flexibility and scalability of data transformations.

Example to show all the distinct values for each column in a table, and count how many records for each. i.e. loop through all columns.

Dynamic SQL:

DECLARE @sql VARCHAR(MAX) = '';

DECLARE @tablename VARCHAR(255) = 'your_table_name';

SELECT @sql = @sql + 'SELECT [' + c1.name + '] AS KeyColumn, COUNT(*) AS RecordCount FROM [' + @tablename + '] GROUP BY [' + c1.name + '];' + 'SELECT [' + c2.name + '] AS DistinctColumn, COUNT(*) AS RecordCount FROM [' + @tablename + '] GROUP BY [' + c2.name + '];' + 'SELECT [' + c3.name + '] AS DistinctColumn, COUNT(*) AS RecordCount FROM [' + @tablename + '] GROUP BY [' + c3.name + '];' + 'SELECT [' + c4.name + '] AS DistinctColumn, COUNT(*) AS RecordCount FROM [' + @tablename + '] GROUP BY [' + c4.name + '];'

FROM sys.columns c1, sys.columns c2, sys.columns c3, sys.columns c4

WHERE c1.object_id = OBJECT_ID(@tablename) AND c2.object_id = OBJECT_ID(@tablename) AND c3.object_id = OBJECT_ID(@tablename) AND c4.object_id = OBJECT_ID(@tablename) AND c1.column_id = 1 AND c2.column_id = 2 AND c3.column_id = 3 AND c4.column_id = 4;

EXEC (@sql);

dbt jinja

{% set tablename = 'your_table_name' %} {% set columns = ['column1', 'column2', 'column3', 'column4'] %}

{% set sql = '' %} {% for column in columns %} {% set query = "SELECT [' || column || '] AS KeyColumn, COUNT(*) AS RecordCount FROM [' || tablename || '] GROUP BY [' || column || '];" %} {% set sql = sql + query %} {% endfor %}

{% do run_query(sql) %}

{% set tablename = 'your_table_name' %} {% set columns_query = adapter.get_columns_in_relation(database=target.database, schema=target.schema, relation=tablename) %}

{% set sql = '' %} {% for column in columns_query %} {% set column_name = column.name %} {% set query = "SELECT [" ~ column_name ~ "] AS KeyColumn, COUNT(*) AS RecordCount FROM [" ~ tablename ~ "] GROUP BY [" ~ column_name ~ "];" %} {% set sql = sql + query %} {% endfor %}

{% do run_query(sql) %}

Initiating or migrating a project to dbt using cli

  1. Verify that dbt Core is installed and check the version using the dbt --version command.
  2. Use the dbt CLI to create a new project by running the dbt init command followed by the name of your project. For example, to create a project named "my_dbt_project", run the command dbt init my_dbt_project. This will create a directory with the same name as your project and populate it with some starter files, including a dbt_project.yml file and some sample model files.
  3. Update the values in the dbt_project.yml file to match your project's configuration. For example, you can change the name of your project, specify the name of your dbt profile, and define the directories where your models and data sources will be stored.
  4. Source configuration.
  5. Use a code editor like Atom or VSCode to open the project directory you created in the previous steps. The content includes folders and .sql and .yml files generated by the init command.
  6. Commit your changes so that the repository contains the latest code. Link the GitHub repository you created to your dbt project by running the following commands in Terminal. Make sure you use the correct git URL for your repository.
  7. Once your project is set up, you can use the dbt CLI to run your project by navigating to the project directory and running the dbt command followed by a command such as run or test. For example, to run all the models in your project, run the command dbt run.

A few things to keep in mind:

  • Before running the dbt project from the command line, make sure the working directory is your dbt project directory and the branch is not main.
  • A dbt project informs dbt about the context of your project and how to transform your data. By design, dbt enforces the top-level structure of a dbt project which includes the dbt_project.yml file, models directory, and the snapshots directory. You can organize the content within the directories of the top-level of your project in any way that meets the needs of the project and data pipeline.

Installing the dbt-cli VSCode Extension

  1. Ensure that you have Python installed and set up in Visual Studio Code.

  2. Install dbt-cli in a dedicated environment following this guide and modify the path to the default Python environment in VSCode to point to the Python instance in the dbt environment.

  3. Activate the virtual environment from VSCode's terminal:

  cd [link to venv]/Scripts/

  activate
  1. Install the vscode-dbt extension by going to the Visual Studio Code Marketplace and clicking on "Install".
  2. To enable support for snippets when viewing a SQL, YAML, or Markdown file in Visual Studio Code, enter Ctrl + K, M (or Ctrl + Shift + P and search for "Change Language Mode"). Then select Jinja SQL, Jinja YAML, or Jinja Markdown respectively. Alternatively, add the following to your Visual Studio Code settings in your dbt project:
 "files.associations": {
 "**/<dbt-project-dir>/**/*.yaml": "jinja-yaml",
 "**/<dbt-project-dir>/**/*.yml": "jinja-yaml",
 "**/<dbt-project-dir>/**/*.sql": "jinja-sql",
 "**/<dbt-project-dir>/**/docs/**/*.md": "jinja-md"
 }
  1. If snippet suggestions still do not work after selecting the appropriate language, try adding the following configuration to your Visual Studio Code project settings:
 "editor.quickSuggestions": {
 "strings": true
 }
  1. In addition to vscode-dbt, there is another extension called find-related that allows you to use regular expressions to correspond a .sql file in your models/ directory to its compiled and run counterparts in the target/ folder. This can be a huge timesaver compared to manually navigating the target/ dir in the explorer sidebar and allow you to easily jump to your compiled SQL files. After you install the find-related extension, you can enable it by adding the following to your .vscode/settings.json.

You may need to tweak these settings if they're not working for you:

"findrelated.workspaceRulesets": [
{
        "name": "sql",
        "rules": [
            {
                "pattern": "^(.*/)?models/(.*/)?(.+\\.sql)$",
                "locators": [
                    "**/compiled/**/\"
                ]
            },
            {
                "pattern": "^(.*/)?compiled/(.*/)?(.+\\.sql)$",
                "locators": [
                    "**/run/**/\"
                ]
            },
            {
                "pattern": "^(.*/)?run/(.*/)?(.+\\.sql)$",
                "locators": [
                    "**/models/**/\"
                ]
            }
        ]
    }
],
"findrelated.applyRulesets": [
    "sql"
]
}
  1. Finally, if you want to implement sqlfluff linting for your dbt projects, you can install the vscode-sqlfluff extension and add the following to your settings.json:
 `pip install sqlfluff`

Then:

 `where sqlfluff`

to verify installation.

`"sql.linter.executablePath": "<PATH_TO_YOUR_SQLFLUFF>`

FAQs - Troubleshooting common errors

`An error occured while trying to execute your query: Database Error relation "schema.table" does not exist`

Execute dbt-run in your terminal to build the project's models and run the model again.

`Could not execute query, because the Python bridge has not been initalized. If the issue persists, please open a Github issue.`

In the VS-Code Settings (File > Preferences > Settings [Ctrl+,]), change the value for Dbt: Dbt Python Path Override to the Python instance in the virtual environment used by DBT:

image

`Database Error connection to server at "canopy.cngdpnwb7fy5.eu-central-1.rds.amazonaws.com" (3.68.81.46), port 5432 failed: timeout expired.`

Modify host: and port: in profiles.yaml to 127.0.0.1 and 5050 respectively.

 `Unable to do partial parsing because a project config has changed
  12:05:53  [WARNING]: Configuration paths exist in your dbt_project.yml file which do not apply to any resources.
  There are 2 unused configuration paths:
   - models.marts
   - models.intermediate`

Modify your dbt_project.yml file and structure it as such:

 `models:
    <project-name>:
# Applies to all files under models/staging/ and models/intermediate
# (dbt materializes as view by default, but this is good to see)
     intermediate:
      +materialized: view
# Applies to all files under models/marts/
     marts:
      +materialized: table`

Replacing <project-name> with the relevant project name (found at the beginning of the file).

`Runtime Error 'NoneType' object has no attribute 'find_generate_macro_by_name'.`

Occurs while using the dbt Power User VSCode extension.

Try dbt clean & dbt deps.

If this doesn't work, close all instances of VSCode and reopen your project.

Here's another potential cause/solution.

Installing SQLFluff

Follow the instructions outlined here >> https://stackoverflow.com/a/77143144

Benefits of dbt in Analytics Workflow

  • Streamlined Workflow: dbt simplifies the end-to-end data analytics workflow, from data transformation to analysis, by providing a unified platform.
  • Data Confidence: dbt's testing capabilities ensure data quality, reducing the risk of erroneous analysis.
  • Documentation: dbt automatically generates documentation, enabling easy exploration and understanding of data models.
  • Scalability: dbt's modular structure allows for scalable development and maintenance of data pipelines as your organization's data needs grow.

How we use DBT at Ona

DBT is the T part of our ELT process (Extract, Load, and Transform). It is fundamental in most analytics projects.

Useful links

Getting started: https://docs.getdbt.com/quickstarts

Docs: https://docs.getdbt.com/

Learning: https://courses.getdbt.com/

Security, Access, Deployment, And Data Flows

Clone this wiki locally