Getting started with dbt

As a contract data engineer, I sometimes experience — ahem — let's just say, periods of inactivity. When I browse the online market for suitable roles during those times, one of the most in-demand skills I keep seeing is experience with a tool called dbt. So, to give myself the best possible chance of obtaining work, I decided to learn as much about dbt as I thought I needed to, to at least be confident enough to talk about it in general terms to a fellow techie at an interview stage should the need arise. This article distils that process and what I’ve learnt. Of course you can't learn a subject just by reading about it, so as usual, I'll show plenty of practical code and real-world examples.To be clear, I have no affiliation or commercial association with dbt, DuckDB, or their creators. dbt Core is a free open-source software released under the Apache 2.0 licence, and you can run it locally without a dbt account. DuckDB is also free to use under the permissive MIT licence.dbt provides a broad range of capabilities, but as this is an introduction to the topic, I’m concentrating on explaining the basics. This includes using dbt models and sources, and using it to test data and create documentation. More about all of these later.If you’ve worked on any reasonably sized analytics or data engineering project, you’ve probably ended up with a folder full of SQL scripts.When your project first starts up, everything feels manageable. You run the scripts manually or schedule them in whatever orchestration tool your company uses. Everything is good.Then the project grows.A column gets renamed in one table, and suddenly some downstream report or dashboard stops working or, worse still, your nightly 10 million record data ingestion job fails, and your whole system grinds to a halt. The list of issues that a wrongly applied bit of SQL or a table change can do to a database system is scary. And you know what, it happens all the time.Part of the problem is that, traditionally, SQL has been treated as a collection of isolated scripts rather than as a software project.If this sounds all too familiar, the folks behind dbt think they have a solution.Table of contentsWhat is dbt?Why do data teams use dbt?PrerequisitesInstalling dbtSetting up a dbt projectCreating our DuckDB databaseCreating and running a dbt model with a sourceUsing dbt for testing your dataNot null testAccepted values testUsing dbt to document your systemNext stagesWhat is dbt?dbt (data build tool) was created in the mid 2010s by a group now known as dbt Labs. It grew from an internal analytics workflow into a widely used open-source, free (on the developer plan) CLI tool called dbt Core, alongside a fully managed, paid version called dbt Platform. I’ll be using the free version.dbt is used to transform data already stored in a database, warehouse or lakehouse. It does this by creating tables or views based on user-supplied SQL, but it also handles the following:Testing data qualityDocumenting datasets and lineageReusing SQL through macrosManaging development, testing and production environmentsRunning transformations through scheduled jobs or CI/CD pipelinesdbt is widely used by teams operating enterprise-grade data storage platforms such as Snowflake, BigQuery, Redshift and Databricks. But for my examples, I’m going to be using a local DuckDB database.Why do data teams use dbt?Mainly because it’s good at what it does. Imagine you’re building a sales reporting platform. Raw order data lands in your data warehouse every hour, say. You write one SQL script to clean the data, another to calculate customer totals, another to build daily sales figures, and another to generate executive dashboards.At first, the project has four or five SQL files, and it’s easy to keep track of them. Six months later, there are fifty, and the order in which they run is no longer obvious.Which script runs in which order? What breaks if someone renames a column? How do you check that the data is still valid? Could a new developer understand the project without opening every SQL file?Often, analytics teams solved these problems with naming conventions, handwritten notes passed around and a lot of shared systems knowledge.As organisations became more data-driven, analytics projects started looking more and more like software projects. Teams needed version control, automated testing, documentation and dependency management because they were writing thousands of lines of SQL.Rather than treating SQL scripts as independent files, dbt treats them as components of a single project, where every transformation has a defined purpose, and every dependency is understood.PrerequisitesI’m using Windows as my operating system and have Python 3.13 installed. Everything should work in the same way if you're on Linux or macOS but you definitely need to have Python installed. You’ll also need access to a suitable database for dbt to act on. Each database will have differences in how you set it up to use dbt. I'll be using DuckDB as my database and will show you the set up for that. Consult the dbt docs (linked at the end) if you're using a different data store.Installing dbtNow that we have a better understanding of dbt, in the rest of this article I’ll show you how to install it and, by way of example code, demonstrate the most common dbt commands you’ll use in your day-to-day job.The first thing we should do is set up a separate Python development environment to keep our projects siloed.You can install dbt using a simple pip command like the one shown below. To connect dbt to a data source, we use something called an adapter. dbt has many different types of adapters, for example, BigQuery, AWS Redshift, Snowflake, etc. For this demo, I’m going to be using a local DuckDB database. Most adapters have to be installed separately from the dbt-core product, but for DuckDB, dbt provides a one-file install.Setting up a dbt projectThe next thing we want to do is initialise a dbt project. We do this using the dbt init command.Running the above command will create a number of folders and files. It will look something like this,The models/example folder shows two example model files and a schema file. We’ll talk more about model files in a bit, but for now you can safely delete the entire example folder and its contents. One of the most important files that the dbt init process creates is called profiles.yml. This holds your database connection properties, but you won’t see it in your dbt project structure. Instead, in Windows, its full path is,In my setup, the file contained this.Now we can see what dbt expects our database to be called and where it should reside. Of course, you can edit this file and change those details if you want. The path is relative to your HOME directory. I want my duckDB data file to be in,So I updated my profiles.yml file to look like thisCreating our DuckDB databaseNow we can create our DuckDB database. To do that, we need to install the DuckDB CLI. Click the link below and follow the instructions relevant to your environment.Run the duckdb CLI and pass it the name of a suitable file to permanently store your database. You can also run it without a parameter if you’re not bothered about keeping the data when you exit. Type in the following command.Creating and running a dbt model with a sourceNow that we have data in our database, we can start to use dbt. Two of the most important concepts to grasp in dbt are those of models and sources. A model is simply a file containing a snippet of SQL that dbt uses to create a new table or view in your target database. A source is an existing table or view in your data store that dbt didn't create, such as raw data loaded by an application or ingestion tool. Sources are how models refer to existing tables in your database/schema. You define a source using a YAML configuration file. As we’re working with an orders table, we’ll call ours orders.yml. For our example, we’re going to create a model that builds a table to hold completed orders. This will refer to our existing orders database table, so it makes sense to create a source YAML file for it. That looks like this:And our model SQL file looks like this.Create both the model SQL file and source YAML file under the models folder in your dbt project.Hopefully you can immediately see the benefit of using a source in our model file. Because the FROM clause in the SQL uses a reference rather than an actual table name, if the source table name were to change at some point in the future, you would only need to update that change in one place — the source file. Any and all SQLs using the source file would run unchanged.Ok, now that those files are in place, we can run our dbt transformation. You do that using the dbt run command.The output is as expected. A new summary table is created with the required records. That’s all I’m going to say on models and sources. What I’ve shown might seem a bit of a faff for just one table, and it is, but trust me, if you are dealing with dozens or hundreds of tables and transformations, you won’t regret the time spent on creating models and sources.Using dbt for testing your dataAnother benefit of using dbt is its ability to automate your SQL testing cycle. Tests are defined (in YAML) alongside your models and sources and can be executed independently or whenever the project is built. You can write your own SQL tests, but dbt also provides four built-in test conditions:uniquenot_nullrelationshipsaccepted_valuesWe’ll demo two of these tests to give you a flavour of what you can do with them.Not null testOur test will be run against the customer_name column of the customer_order_summary table. As we’re testing a table that dbt is creating, we add the test YAML to a models section in our orders.yml file. It now looks like this:As I didn’t have any null customer names in my original orders table, I created one so we can see what a failing test looks like.Now, to run our test, we can simply type the dbt build command like this, which runs and validates the selected parts of a dbt project in dependency order.The issue is caught and reported on. dbt doesn't delete or roll back a model when its subsequent data test fails. However, models downstream of the failed test are normally skipped during the build. If you want to run the test without re-creating any tables, etc., just use the dbt test command.Accepted values testThis does exactly what it sounds like. It enables you to test that a column contains only specific values. If we look at our orders table, we can see that the order_status column should only contain either completed, processing, returned or cancelled values. So let’s update the table and change one of the values to be something else.As we’re testing a source table, we should put our test YAML config in the sources section of our YAML file. You can leave or remove the original null test if you want. I’ve kept it in.We're running the test on an existing table, so we don't need to run the build command. We can just use dbt test.The other two types of built-in test are equally easy to set up and run, so I'll leave it at that.Using dbt to document your systemThe final introductory dbt topic we’re going to look at is arguably one of its best features. Most documentation starts life with good intentions before quietly becoming out of date. dbt approaches documentation differently.Because your models, tests and metadata all live alongside your SQL, dbt can generate project documentation automatically. More importantly, it also creates a visual lineage graph showing exactly how your models depend on one another.This is invaluable when someone new joins your project because they don’t need to reverse-engineer hundreds of SQL files. They can see the entire transformation pipeline almost immediately.It’s one of those features that doesn’t seem particularly exciting until you’ve inherited someone else’s analytics project.Right off the bat, dbt can do some automated documentation for you, but it’s one of those things that the more you put into it, the better documentation you will get out. Without doing anything extra to our project, here is the bare documentation you get. We use the dbt docs generate command to create the documentation like this.Now that we’ve generated the documentation, we can visualise it in a web browser using the dbt docs serve command.You should see a browser window open that looks something like this,As I mentioned, it’s pretty bare-bones but nonetheless useful. To see the real power, you have to add your own descriptive documentation text in the form of YAML to your orders.yml file. Here is an example.Now, when we run the two dbt documentation commands, we get a much richer output like this.Next stagesdbt is a large ecosystem, and as I explained, I only wanted to touch on some of the basics of its operation. As things stand, I’m happy with the knowledge I have on using dbt. If you want to take things further, you might want to dive deeper into the following topics, which build on what I’ve talked about here.Incremental models: Process only new or changed records instead of rebuilding an entire table on every run.Jinja: A templating language that lets you add variables, conditions, loops and reusable functions to SQL.Macros: Reusable pieces of Jinja and SQL logic that can accept parameters and generate SQL.Snapshots: Record how source records change over time, allowing you to retain their historical values.Reusable packages: Use models, macros and tests created by other dbt projects instead of building everything yourself.Here is a link to the official dbt Labs home page where you'll find all the information you need to know about dbt.https://www.getdbt.comHappy learning.