A Data Analyst’s introduction to dbt

And you’ve heard people talking about dbt, but don’t really know what it’s all about? If so, this article is for you. It explains how we at DEPT® use dbt and covers what a data analyst who is new to one of our projects would need to know. Hopefully it will give you a practical insight into how dbt could help you in your day to day work.

This article is written with the data analyst audience in mind, for someone who primarily writes complex SQL in their role. It is worth bearing in mind that there is infrastructure and setup required to run dbt that we don’t consider in this article, for a few reasons:

• There are several ways in which you might want to run dbt, the main choice being between using dbt Cloud (hosted dbt service) and dbt-core (running the set up yourself). A data analyst shouldn’t need to worry about it! That’s one of the joys of dbt.
• Finally, this would warrant an article in itself. Watch this space!

dbt is most useful when you are setting up data pipelines or some kind of rerunnable scheduled SQL processing. It’s less useful if you are just writing one-off SQL scripts, Though be careful thinking your work is just one-off SQL scripts. If you’re writing the same thing again and again, and using similar processes, then dbt can help there too!

The below diagram describes a typical project where we’ve used dbt. For this client, we were setting up a new Snowflake data warehouse to consolidate and modernize their data stack. This data warehouse was used initially to drive Power BI dashboards but also to answer ad-hoc questions from around the organization and to provide direct access to curated data sets for another area of the organization.

We use four schemas for our data:

Ingress: This contains the data as it arrives from the source. We don’t modify it in any way.

Raw: We process the data from Ingress, remove any test or useless data, deduplicate any redundant data, and clean and process data without applying business logic. (For example, we don’t make all strings uppercase.) Finally, we write those records into the Raw schema.

Semantic: From the Raw schema, we construct more business-appropriate tables and relationships.  Some of these we want to display in dashboards, and/or send to our end users. The SQL that we write between Raw and Semantic contains all our business logic.

Reporting:  Although the Semantic schema contains business objects, these are still at the lowest level of granularity. For reporting or performance purposes we often want to provide data that is aggregated at a higher level. We write this data to the reporting layer.

All dbt code is contained in the dags/dbt directory in the repository, in the below structure:

|- models

This is the main content of our data model. Each sql file in here is a model which you can think of as a table, goes in the relevant subdirectory, dependent on which schema it lives in:
|- ingress
|- raw
|- semantic
|- reporting
|- tests

“tests” contains tests for the data (more on this later). Tests can be used to check for anything from simple uniqueness constraints, to whether a table is empty, to evaluating the results of complex custom SQL.

|- macros

Contains “functions” that generate SQL – use this to extract out pieces of SQL logic that would otherwise be repeated across the codebase.

We’ve had a look at simple dbt model files and the structure of the code base.

But when we’ve created our templated dbt SQL file, we need to convert those source files into the SQL that we can use to update the database. To generate this SQL, from the command line, run dbt compile. This takes source model-files and source test-files and compiles them under the target/compiled directory.

Note that this doesn’t run anything in your database! Nor does it check that your SQL is valid, like a regular code compiler. You’ve just taken the templated SQL (or configured test configuration files) and converted them to actual SQL that can be run against the database.

So, if you want to test the SQL you have written in templated format, you have a choice:

• You can load the compiled SQL file from under the target/compiled directory into your database query tool to run. This compiled file contains the SELECT statement that will be used to create your model when dbt runs, so loading and running this will show you what your table will look like, but not update the database itself
• You can run dbt run –select <model_name> This does update the database with your model (assuming it is not ephemeral), because it uses dbt’s framework to wrap the compiled SELECT statement with the relevant DDL SQL to create your table.

Open the relevant SQL file in your code editor I use VSCode. Make the changes to the file so that the end SELECT statement will produce what you want to put in your table.

To test your changes:

• run dbt compile, 
• load the compiled SQL file under `targets/ci/<schema>/<filename>.sql in the tool you use to run SQL on your database
• Run the SQL, and see if it produces the correct output. If not, alter your source file and repeat this process.
• Once you are happy the output is correct, you can run your changes into the development database using dbt run –select <name of your model>. If you want to update your model and all the downstream models run dbt run –select <name of your model>+. 

Consider adding tests for your change (see below), and don’t forget to extract any reusable or complex functionality into macros (also see below) or ephemeral models (for CTEs).

What tools you use to edit your SQL is obviously up to you, but I have had great success using the VSCode “dbt Power User” VS Code plugin, which enables to browse your data model quickly (seeing parent and child models), and to easily run and compile your models without having to leave VS Code. 

For each model in the repository, we add a .yml file to the models/test directory. These contain standard tests such as:

• Checking for the number of rows in a table
• Checking a column is unique
• Checking the freshness of the data in ingress tables

We add tests for our models in a file called <model_name>.yml under models/tests. All models have a test file. Many of the above examples can be done using dbt standard tests, dbt_utils tests or the test package we already use, dbt_expectations. That means you don’t need to write any code to set up a test, you can just add the relevant configuration to the yml file.

For example, to check whether a column is unique and not null, a test looks like this:

version: 2
models:
– name: dim_contact
tests:
columns:
– name: contact_key
tests:
– not_null
– unique

Or to check that multiple columns are unique, like this:

version: 2
models:
– name: dim_users
tests:
– dbt_expectations.expect_compound_columns_to_be_unique:    
column_list: [“user_id”, “user_id_sfdc”]

We use macros to extract pieces of SQL code that we use repeatedly throughout the code. For example, we currently format last names in the same way in several places. Extracting the code into macros means that if we want to change the logic of how we do this, we can do it in one place.

Macros live under macros, and once you have defined one, you can call it by simply inserting  {{ macroname(param1, param2 }}, etc. in your SQL code.

Create a macro file with `.sql` extension under `macros`. We usually organize our macros by the schema they run in. You can have more than one macro in a single file, and we tend to group them by functionality – for example a set of macros that refers to a particular business problem all live within one file.

Example – Format last name

{% macro format_last_name(last_name) %}
{% set last_names_not_ilike = [“d”%”, “da %”, “de %”, “del %”, “den %”, “di %”, “du %” , “ter %”, “van %”, “von %”, “zu %”] %}
  CASE WHEN
      ((SUBSTRING({{ last_name }}, 1, 1)
          != UPPER(SUBSTRING({{ last_name }}, 1, 1))
      {% for not_ilike in last_names_not_ilike %}
          AND {{ last_name }} NOT ILIKE ‘{{not_ilike}}’
      {% endfor %})
      OR {{ last_name }} = UPPER({{ last_name }}))
  THEN INITCAP({{ last_name }})
  ELSE {{ last_name }}
  END
{% endmacro %}

You can see here that this macro takes one parameter, which is the column.

This way you can apply this macro to any column you like (e.g. your name columns may be called different things in different tables)

It also has a list of last names to compare against and treat differently. You can see we put these in a list at the top and generate the SQL code through a loop.

In order to call the macro in your model code, you can simply call (for example):

{{format_last_name(’employee_last_name’)}}

There is no need to declare the file the macro is in, nor to import it in any way. (Because of this, all macro names must be unique)

Note that you can reference dbt models within your macros, but not ephemeral models.

We also document our models. We put the documentation of our tables and columns within our test  YML files. This makes it easy to find the tests and documentation for a single model in one place. 

To document anything in dbt, add a description tag to the element. You can either add documentation inline (recommended if it’s a short one off description), or you can add a “doc” tag as shown below (if the description is likely to be reused in several models, or is longer) – then in a separate “.md” file you can add the documentation tag’s implementation, as shown underneath.

version: 2
models:
– name: fact_web_events
description: “{{ doc(‘web_events_table’) }}”
{% docs web_events_table %}# fact_web_events
# fact_web_events

The `fact_web_events` table is our core model for on-site behavior. It contains all tagged events from the website. This table has several downstream tables for more specific use cases, such as individual page loads, web sessions, etc. {% enddocs %}

Note that if you use a doc tag, it’s possible to include markdown content, and even images.