Blog

Data

Best Practices for your dbt Style Guide

dbt Style Guide Best Practices - Ensure optimal usage with best practices for dbt style guides.

DATA

February 15, 2022

15 min read

Madison Schott

Summarize with AI:

dbt is the core of analytics engineering and the tool behind the “T” in ELT. It is a powerful way to transform your raw data into usable data models. It is the tool I, and most, analytics engineers use to transform raw data into models called base models which are then used in every other data model built.

Base models are models that reference the raw data you ingest directly into your data warehouse. These are the only models that should read from the raw data source. Once your base models are written, other models then reference these. These models are relatively simple and only use casting and column name changes. You never want to include advanced SQL code like aggregate or window functions. You also don’t want to filter any data here.

Base models are now more frequently referred to as staging models in dbt’s documentation. They changed the naming because they didn’t want to be limited by the name “base”, meaning the first step in building a data model.

From dbt documentation

If you look at their example DAG, you can see they have both base and staging models. Base models act as the first layer when there are also staging models involved. dbt views staging as the model before the final product and base models as an extra “helper” stage when needed.

Personally, I like using the term “base” because it is more straightforward. “Staging” models can be easily confused with “intermediate” models, another term used in dbt organization. But, just know, if you’re reading dbt’s updated documentation, these same base models are referred to as staging models.

Let’s go into further detail on why base models are so important.

The importance of base models

It is considered a dbt best practice to have a copy of your raw data in your data warehouse at all times. You never know what can go wrong and always want to prepare for the worst. Having your raw data always available to you allows you to pinpoint data discrepancies faster and always rerun changed data models without having to ingest all of your data over again. This is why ELT tools like Airbyte that load all data to the destination before applying any transformation are becoming the norm.

Ingesting all of your historical data is costly and you only want to do it once, when you first set up your data ingestion tool. Having your raw data untouched allows you the flexibility that you wouldn’t have otherwise.

Base models make it so that you never have to reference your raw data sources within your data models. They are essentially a cleaner version of your raw data. While there are not many changes in the data, they make the lives of analysts easier.

Creating a dbt style guide

A dbt style guide should include naming conventions for your models and define the structure of your dbt project. Raw data from different sources often has different naming conventions or repeat column names. Determining a style guide for your data is key. Your data team needs to agree on the best practices to organize your dbt projects to make collaboration easier. You want your data to be uniform across all data sources and you do this by following your team's best practices for defining your base models in dbt.

Here are a few things to consider:

Column naming style (snake case or camel case?)
Column naming convention (<table name>_<column name>)
Date types (timestampntz or date?)
Decimal places

The most important thing here is uniformity. With whatever styling you choose, you need to make sure it is consistent across all base models, no matter the data source. Analysts and analytics engineers want to be able to code using these data sources without constantly checking the column names in the database tables. The styles you choose should be consistent and intuitive so that they pick it up in no time.

I’ll share with you the best practices I use in my base models when we get to the examples of building them out using dbt.

Ingesting raw data

Before we set up our dbt source files, we need to make sure we have raw data in our data warehouse to reference. I’ll be using Airbyte to ingest data from Mailchimp into Snowflake. You can read more about setting up sources and destinations in Airbyte’s documentation. I also wrote a detailed article about how to ingest Mailchimp data.

Setting up dbt source files

dbt uses YAML files to organize projects. The inner workings of these files are what make dbt special. They include important information like database credentials, table descriptions, model descriptions, and schema details. YAML files contain all the behind-the-scene connections while SQL files contain all the transformation code.

Source files are just one of the YAML files utilized by dbt to help ensure everything runs smoothly. They essentially give dbt access to your raw data. They include the database and schema of the data source, tables, and model descriptions of those made using this raw data.

The first thing you need to do is set up YAML files for your sources. Depending on how many data sources you have, you can set up one or multiple files. Personally, I like to set up one per data source or even multiple per data source depending on how many tables that source contains. This makes it so that the files stay readable and organized. It can be overwhelming to have 50+ sources in one .yml file.

Let’s look at the tables we loaded in from Mailchimp.

You can see there are mainly campaign and list related tables. Instead of having these all in one .yml file, I consider a good practice to create two different folders under my Mailchimp folder, one for campaigns and the other for lists. These folders would then have their own .yml files that declare the data sources and their related tables.

Here’s what the file structure would look like:

Now let’s look at what one of these individual yml files looks like. We will be writing the source file for the campaign-related data in Mailchimp. I would call this file src_mailchimp_campaigns.yml. I always follow this naming structure for my source files:


src__< table group name>.yml

Choose whatever makes sense for you. But, no matter what you decide on, make sure it is consistent across all of your source files. I prefix all of my source files with src_ to help identify the source files from my models within this same folder. It is easier on the eyes when you have a bunch of base models under one folder.

Within each source file you need to start by listing your sources. Under the “source” section you can list the name, description, and tables that make up your source. I like to only have one of these per file, but, again, this depends on how you’re organizing your sources. If you have one file for all of your sources then you will have multiple listed here.

src_mailchimp_campaigns.yml

Here in my src_mailchimp_campaigns.yml file I listed mailchimp_campaigns as my source name, added a description saying this was for all Mailchimp tables related to campaigns, and listed the tables that fall under this grouping.

It can be easy to skip adding descriptions to these tables, but I highly recommend it. As an analytics engineer, your data models are only as good as the documentation describing them. If people don’t understand their purpose or how they work, they aren’t going to be useful. More information is always better than less. Assume nobody knows what you are talking about and make it so that they get all their questions asked just by reading your documentation.

Here are some descriptions I included for the first few tables in this src_mailchimp_campaigns.yml file:

src_mailchimp_campaigns.yml

‍

Integrate with 700+ apps using Airbyte

Move data from 700+ sources into warehouses, lakes, and beyond. Set up pipelines in minutes with pre-built connectors and the Connector Builder.

Try it free Talk to sales

About the Author

Madison Schott

Madison Schott is an Analytics Engineer, author of The ABCs of Analytics Engineering book. Madison blogs about the modern data stack on Medium and writes a newsletter on Substack.

Integrate with 700+ apps using Airbyte

Try Airbyte for free

Best Practices for your dbt Style Guide

Integrate with 700+ apps using Airbyte

Integrate with 700+ apps using Airbyte

Related posts