Unleashing DuckDB & dbt for local analytics triumphs

0:00If you have been coding SQL data pipelines, chances are high you came across dbt. It has become a gold standard in the analytics space for a simple reason. It helps you to write production-ready data pipelines using SQL. dbt usually works great with a cloud data warehouse.

0:21You run a client, here a Python process that would execute the SQL queries. However, it's too bad that this cloud data warehouse can't be run on my laptop easily. It will help the development workflow, the CI, and other stuff. What? You didn't read the title of the video? So let's Quack how these two frameworks work together.

0:44So let's understand first the challenges of the current dbt plus data warehouse setup. If you are not familiar with DuckDB, I would definitely check out our beginner video about it so that you get it up to speed. But don't worry, if you are really too lazy, I'll recall the fundamentals in this one so that you don't get lost. So DuckDB is blazingly fast. OLAP SQL in-process database.

1:08And within the Python ecosystem, as we will use it with dbt, you can install it with just the pip install DuckDB. And the in-process here is a game changer. Let's look at the classic architecture when working with dbt. So users will write SQL, or rather SQL template, and this will be built into actual SQL files to dbt

1:30within a Python process and act as a client, JDBC client for most of the time, to execute the SQL to your cloud data warehouse. There is an obvious challenge with such a setup that brings a couple of drawbacks. You are actually not leveraging your local compute. You know that expensive MacBook Pro that your company paid? You must always be connected to your cloud data warehouse for any development,

1:55and it's hard to do real unit tests locally or within your CI pipelines. By definition, these tests should not involve interaction with APIs or services. It should be isolated. And finally, onboarding and learning experience for new SQL users can be difficult. You will always need to set up a cloud playground, maybe dump some simple data over there,

2:19just to be able to start running some simple SQL queries. So entering DuckDB. Because DuckDB is a lightweight database running in-process, it can actually run directly within your dbt Python process. You don't need to have a connection with a cloud data warehouse. You can leverage your local computes and therefore improve your development experience.

2:42So your previous dreams can be realized. Oops, wrong clap. Next to that, you could also decide to leverage an object storage and run a dbt process to load from S3, let's say, and then write back the result to it. That process could run anywhere you want in a container. And again, no cloud data warehouse dependency.

3:05So now that we understand the value and the possible use cases, let's dive into an analytic example where we will understand how DuckDB and dbt works together. And as always, you can follow along by checking out the GitHub repository. I'll put the link in the description. I assume you have some basic knowledge on how dbt works for this tutorial.

3:26And I'll put some additional resources regarding dbt if you want to read that first. So to work with DuckDB within dbt, it's the same as working with any other cloud data warehouse. You will need to have what dbt called an adapter. And lucky for us, someone built one for us for DuckDB. Thanks, Josh. So you can install dbt with the DuckDB adapter,

3:48and DuckDB also installed through dependency with just a single pip install dbt-duckdb. Yes? Which question are we answering today? Well, I'm glad you asked. If you know me, I like to test the data tool by answering important questions. And that's why today we are going to look at. . .

4:08air quality data. We are going to rank the air quality of each city based on the WHO. Wait, who? WHO. W-H-O. And also have a look at the evolution of the air quality of a specific city, Berlin, because I'm based in Berlin. A couple information about the dataset.

4:27It has on average one or more entry of measures per city per year. And for each sample we have a couple of metrics as you can see. I'm going to quickly cover the most important columns and their meaning. But for that, let me get my scientist background. That's much better. So PM 2. 5 is like extremely tiny dust that's so small, about 30 times smaller than a strand of

4:51hair. For those who still have some hair. You can't see these individual particles with your eyes, but when there is a lot of them in the air, you might notice that the air looks hazy or smoky. This tiny dust can get deep into our lungs when we breathe. And if there is a lot of these tiny dusts in the air, it can cause health problems, especially for people with conditions like asthma

5:16or heart disease. So PM 2. 5 concentration and PM 10 concentration represent the concentration of particulate matters that are less than 2. 5 micrometers and less than 10 micrometers in diameter. TLDR, high level of those metrics can impact negatively health. NO2 concentration

5:35represents the concentration of nitrogen dioxide, so NO2, and it's a pollutant that is largely coming from burning fossil fuels. And again, high level of NO2 can also negatively impact health. All right, that's all for the crash course on air quality metrics. The data is located on a public

5:54S3 bucket as a parquet file, and we will process the results as local CSV just for the sake of the demo so that we can inspect those quickly. Let's look at how our profiles. yaml would look like. Type is basically referring to the adapter. Path is where you want, optionally, to persist data when they are loaded and transformed into DuckDB. So this is the path or your DuckDB file format.

6:19It's a single file format that would include all the tables together. Extensions are DuckDB extensions you want to install, and settings are parameters that some extension needs. Here, for example, the S3 credentials. My source file. yaml looks like this, and this is where we are going to read the dataset. I'm going to compute two queries. The goal of the first one is to be able

6:43to rank each city air quality with simple terms like poor, moderate, and good based on the WHO recommendation. I'm grouping the results per city and per year. The second one is a simple query to look out for a specific city and its average of concentration of PM 2. 5, 10, and NO2. All right,

7:04let's execute the dbt process through a dbt run and see the result. So what happened behind the scenes? It built a SQL file, executed them against an in-memory DuckDB that has been launched, and write the CSV as an output. The cool thing with this setup is that we can also quickly iterate locally and add a couple of unit tests based on a sample of data. And you could load

7:28this sample of data through the seed feature from dbt into DuckDB and run your test. So let's see the result. So we can see each city ranked with its air quality, poor, moderate, or good. And poof! Berlin is moderate. If we look at the second query focused on Berlin, fortunately, we can see

7:49that all the indicators are going down, which is good. It means that the air quality has been getting better these past few years. All right, let's wrap up. In this video, we've seen the possibilities of running pure production-ready SQL pipelines using dbt without any cloud data

8:09warehouse dependency using DuckDB. With the dbt-duckdb adapter, we saw how we can can easily even configure extensions and other properties of DuckDB to make our analytical project work. And of course, there are some limits in a real-world use case. How do you handle access to data?

8:29How do you share a dataset with others and easily query this through a BI tool? Well, yes, you could build something custom. We are building this at MotherDuck. So subscribe if you want to stay tuned. All right, that's all for today. Take care, and may the SQL be with you.