![\"\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2024\/11\/word-image-104727-1.png\")
<\/a><\/figure>\n\n\n\n<\/p>\n\n\n\n
dbt is heavily centered around the SQL language and it aims to make the SQL development in an analytics project more efficient, more reusable and in the end better tested and documented. dbt calls people working with their product \u201canalytics engineers\u201d. In this article, you\u2019ll get an introduction to the dbt tool. It will by no means be a complete overview, but you\u2019ll get a good grip of what this tool is supposed to do.<\/p>\n\n\n\n
Disclaimer<\/strong>: this article is not endorsed by dbt, nor is the author affiliated in any way with dbt. The author writes from personal experience from having used the tool in data projects.<\/p>\n\n\n\n dbt comes in two flavors: an open-source and free-to-use, command-line version called dbt core<\/a>, and a SaaS cloud version called dbt cloud<\/a>. The cloud version comes with more bells and whistles but has paid plans<\/a>. However, there\u2019s a free developer license you can use to try the product out.<\/p>\n\n\n\n <\/p>\n\n\n\n You can find a comparison between the dbt core and cloud versions on here<\/a>. In this article, I\u2019m using the free dbt cloud license, as it\u2019s easier to use and explain with the user interface.<\/p>\n\n\n\n When you log in for the first time after you\u2019ve created your account, you will be asked to complete the project setup.<\/p>\n\n\n\n <\/p>\n\n\n\n Let\u2019s create a connection to a database. When you want to create a new connection from the dropdown, you will be taken to a new tab where you can define the connection properties. Originally, dbt only supported a couple of databases out-of-the-box, such as Snowflake, Databricks and PostgreSQL, but nowadays there\u2019s a wide range of databases to choose from:<\/p>\n\n\n\n <\/p>\n\n\n\n It\u2019s possible to connect other data stores as well (MySQL for example), if there\u2019s an adaptor available. You can find those in the community adaptors<\/a>. Let\u2019s create a connection to Microsoft Fabric. Since dbt is a SQL-based tool which will create tables and views, you are implicitly restricted to the warehouse. You\u2019ll be asked to specify the server name, which you can find the warehouse settings, and the database name. You can leave the default port as is.<\/p>\n\n\n\n <\/p>\n\n\n\n Once the connection is saved, you can select it in the previous screen:<\/p>\n\n\n\n <\/p>\n\n\n\n In the next screen, you need to specify the credentials that will be used to authenticate against the Fabric warehouse. You can choose between an Active Directory (Entra ID) account with password (you might want to test out how this works with your Multi-Factor Authentication setup), or a service principal.<\/p>\n\n\n\n <\/p>\n\n\n\n Personally, I went for the user with password. I also changed the schema to the default <\/p>\n\n\n\n The schema you specify here will be used to contain the objects created by dbt. This might be useful when you work with multiple developers on the same development database. Every developer can specify their own development credentials with their own specific schema. That way, each developer can work on their own schema in the database. Don\u2019t forget to test the connection at the end!<\/p>\n\n\n\n <\/p>\n\n\n\n Optionally, you can configure source control for the project. You can connect to one of the git providers or use a managed repository<\/a>.<\/p>\n\n\n\n <\/p>\n\n\n\n And the project is now configured! Now we can get our hands dirty with some SQL. In dbt terminology, a model<\/a> is typically a SELECT statement. Depending on the properties of the model, the result of that SELECT statement will either be a view, table or ephemeral. The last option basically means the model will not be persisted in some way, but rather will be used as a common table expression (CTE) when referenced by another model.<\/p>\n\n\n\n Let\u2019s dive into the code. Go to the IDE to get started.<\/p>\n\n\n\n <\/p>\n\n\n\n You will be asked to initialize the project.<\/p>\n\n\n\n <\/p>\n\n\n\n This will create standard configuration files (such as the <\/p>\n\n\n\n In the changes section of the version control, you get an overview of all the changes tracked by the git repository. In the file explorer you can find the project structure, along with their current git status. By hitting Commit and sync<\/strong>, we can commit all the changes to the git repo. Don\u2019t forget to supply a meaningful commit message.<\/p>\n\n\n\n <\/p>\n\n\n\n Once the changes are committed, the version control panel will allow you to create a new branch or to change a branch, refresh the git state (useful if you work with multiple developers on the same project), or revert any changes.<\/p>\n\n\n\n <\/p>\n\n\n\n In the default file structure, we can find some predefined folders, such as:<\/p>\n\n\n <\/p>\n\n\n\n Let\u2019s take a look at those sample models. The first one, <\/p>\n\n\n\n The configuration line specifies how the model will be materialized. If you want the model to be created as a view, you can change it:<\/p>\n\n\n\n The second model, <\/p>\n\n\n\n In the <\/p>\n\n\n\n Okay, it doesn\u2019t seem like much when you only have two models, but for larger data warehouse projects it certainly is useful. Suppose a source column changes data type. With the lineage view you can easily determine which models upstream are possibly impacted by this change.<\/p>\n\n\n\n When you schedule jobs<\/a> in dbt (a dbt cloud feature) to execute the models, dbt can use the lineage to construct a DAG (directed acyclic graph). This DAG can be used to execute the models efficiently. By knowing which dependencies exist, parallelization can be optimized. Or, for example, when you want to reload a single fact table, dbt can determine which dimensions must be loaded first.<\/p>\n\n\n\n There are some other files in the project that are important as well. The <\/p>\n\n\n\n For each model, you can specify a description and its corresponding columns. A column can have a description as well, and you can associate \u201ctests\u201d with it. All these descriptions can be useful when you want to generate documentation. In this example, each column has two tests: a However, not all databases support those constraints. For example, the Fabric warehouse doesn\u2019t have a unique constraint (it does \u201csupport\u201d primary key and foreign keys, but they are not enforced. See the documentation<\/a> for more information.). You can also create your own custom tests which are more complex than the built-in ones. More on that in a later article. Keep in mind that the language specification for the schema file is YAML<\/a>, so indentation matters.<\/p>\n\n\n\n Another file is <\/p>\n\n\n\n By specifying the following line, we can force the materialization of \u2018table\u2019 for all the models within that subfolder:<\/p>\n\n\n\n +materialized: table<\/strong><\/p>\n\n\n\n You can still overrule this specification by explicitly setting the materialization in the model itself.<\/p>\n\n\n\n Now it\u2019s time to actually build the models. Let\u2019s go to the second model <\/p>\n\n\n\n When you hit Build<\/strong>, it will try to create the table in the Fabric warehouse, which will fail because the first model doesn\u2019t exist yet.<\/p>\n\n\n\n <\/p>\n\n\n\n But we can click on the arrow right next to Build to get all the different Build options:<\/p>\n\n\n\n <\/p>\n\n\n\n When we choose Build +model (Upstream)<\/strong>, dbt will try to create the table, but also all the other tables it might be dependent on. You can also choose to create all tables up- and downstream.<\/p>\n\n\n\n We still get an error though, but this time because dbt tries to create the table, but it fails to do so because the <\/p>\n\n\n\n In the left-hand pane you can also see the actual dbt cli commands that were used. In the system logs, you can find all the actual SQL statements dbt issued against the database.<\/p>\n\n\n\n <\/p>\n\n\n\n You can also see in the log that dbt successfully created the first table, populated it with data (since tables are created with CTAS statements<\/a>)<\/p>\n\n\n\n <\/p>\n\n\n\n But since a test failed, everything was rolled back. Let\u2019s change the first model to remove the <\/p>\n\n\n\n Don\u2019t forget to save the model and build everything again. This time, all the tables will be created, and the tests will succeed.<\/p>\n\n\n\n <\/p>\n\n\n\n It\u2019s interesting to see how dbt actually does the tests. It will run a SQL statement and when it returns one or more rows, the test will fail. In the case of the unique test, it will do a group by on the unique field and return all rows with <\/p>\n\n\n\n When we look at the Fabric warehouse, we can see the two new tables:<\/p>\n\n\n\n <\/p>\n\n\n\n In this article, we give you a short introduction to the dbt tool. This software, which is available as an open-source CLI tool or as a Freemium SaaS cloud service, allows you to do the \u201cT\u201d of \u201cELT\u201d using a flair of dynamic SQL. It comes with tons of extra features (not all of them were shown in this article for sake of brevity), such as automated testing, git integration, documentation, data lineage, macros for code reuse, and efficient job scheduling using DAGS. You can also extend the tool with packages<\/a> which can contain more functionality, additional connectivity or pre-defined macros. It\u2019s a bit like how extensions work in your browser, or plugins in Visual Studio.<\/p>\n\n\n\n However, there are some downsides as well. It\u2019s an extra tool in your stack and if you have a larger development team you\u2019ll need to pay for licenses (unless you use the dbt core version). It also means that you need to switch contexts. For example, if you build your pipelines in Fabric, then write your SQL in dbt and then the reports in Power BI, it\u2019s no longer in one centralized environment. It boosts the power of SQL, but since you\u2019re a bit stuck with the CTE and CTAS structure, (very) complex SQL might not be as easily translated to dbt.<\/p>\n\n\n\n For example, I had some issues with a model in a project where I needed to load the table incrementally, but I had to do some checks on the table itself first. This works fine as long as it\u2019s one SQL statement, since you can refer to a model itself (which might not exist yet at compile time) with the Jinja People with quite some SQL experience might get the feeling that they don\u2019t need this tool, they can write the SQL themselves just fine. But dbt comes with extra features as mentioned before, and it can boost the productivity of your development team.<\/p>\n","protected":false},"excerpt":{"rendered":" In a typical data warehouse project, data is loaded, transformed and stored into a data store using an ETL (extract \u2013 transform \u2013 load) process. Recently, there has been a shift to ELT processing, where data is first loaded into a data store (this can be a database, but also a data lake or something……<\/p>\n","protected":false},"author":110905,"featured_media":104760,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[53,159165],"tags":[159230,4824],"coauthors":[159081],"class_list":["post-104727","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-featured","category-general-analytics","tag-dbt","tag-etl"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/104727","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/110905"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=104727"}],"version-history":[{"count":3,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/104727\/revisions"}],"predecessor-version":[{"id":104876,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/104727\/revisions\/104876"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media\/104760"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=104727"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=104727"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=104727"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=104727"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}How to get dbt<\/h2>\n\n\n\n
![\"A](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2024\/11\/a-screenshot-of-a-computer-description-automatica-13.png\")
<\/figure>\n\n\n\nCreate your first project<\/h2>\n\n\n\n
![\"\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2024\/11\/word-image-104727-3.png\")
<\/figure>\n\n\n\n![\"A](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2024\/11\/a-screenshot-of-a-computer-description-automatica-14.png\")
<\/figure>\n\n\n\n![\"A](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2024\/11\/a-screenshot-of-a-computer-description-automatica-15.png\")
<\/figure>\n\n\n\n![\"A](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2024\/11\/a-screenshot-of-a-computer-description-automatica-16.png\")
<\/figure>\n\n\n\n![\"A](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2024\/11\/a-screenshot-of-a-computer-description-automatica-17.png\")
<\/figure>\n\n\n\ndbo <\/code>schema.<\/p>\n\n\n\n![\"A](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2024\/11\/a-screenshot-of-a-login-screen-description-automa.png\")
<\/figure>\n\n\n\n![\"A](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2024\/11\/a-screen-shot-of-a-computer-description-automatic-1.png\")
<\/figure>\n\n\n\n![\"A](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2024\/11\/a-screenshot-of-a-chat-description-automatically.png\")
<\/figure>\n\n\n\n![\"A](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2024\/11\/a-screenshot-of-a-computer-description-automatica-18.png\")
<\/p>\n\n\n\nCreate your first model<\/h2>\n\n\n\n
![\"A](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2024\/11\/a-screen-shot-of-a-computer-description-automatic-2.png\")
<\/figure>\n\n\n\n![\"\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2024\/11\/word-image-104727-13.png\")
<\/figure>\n\n\n\ndbt_project.yml<\/code> file) and a default folder structure.<\/p>\n\n\n\n![\"A](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2024\/11\/a-screenshot-of-a-computer-description-automatica-19.png\")
<\/figure>\n\n\n\n![\"\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2024\/11\/word-image-104727-15.png\")
<\/figure>\n\n\n\n![\"\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2024\/11\/word-image-104727-16.png\")
<\/figure>\n\n\n\n\n
![\"\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2024\/11\/word-image-104727-17.png\")
<\/figure>\n\n\n\nmy_first_dbt_model.sql<\/code>, simply creates two records of data.<\/p>\n\n\n\n![\"A](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2024\/11\/a-screenshot-of-a-computer-program-description-au-1.png\")
<\/figure>\n\n\n\n{{ config(materialized='view')}}<\/pre>\n\n\n\nmy_second_dbt_model.sql<\/code>, simply reads from the first model and applies a filter with a WHERE<\/code> clause.<\/p>\n\n\n\n![\"A](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2024\/11\/a-screenshot-of-a-computer-program-description-au-2.png\")
<\/figure>\n\n\n\nFROM<\/code> clause, you can see the actual table name is not used. Instead, a reference<\/strong> to the fist model is created \u2013 using the model name \u2013 with Jinja<\/a>, a Python-esque templating language. This is one of the keys strengths of dbt. Instead of hard-coding object names, references are used instead, which allow dbt to construct a data lineage graph:<\/p>\n\n\n\n![\"\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2024\/11\/word-image-104727-20.png\")
<\/figure>\n\n\n\nschema.yml<\/strong><\/code> in the example folder contains a schema definition for the two sample models.<\/p>\n\n\n\n![\"A](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2024\/11\/a-screenshot-of-a-computer-program-description-au-3.png\")
<\/figure>\n\n\n\nunique<\/code> and a not_null<\/code> test. These are built-in tests, and in an RDBMS like SQL Server they would be implemented with constraints, such as the unique constraint (which is a unique index) and a NOT NULL<\/code> constraint.<\/p>\n\n\n\ndbt_project.yml<\/code>. It contains project-specific configurations and most of the time you don\u2019t need to make many changes to this file. However, there\u2019s an interesting feature where you can force certain properties as default for a folder. We find this at the end of the file:<\/p>\n\n\n\n![\"A](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2024\/11\/a-screenshot-of-a-computer-program-description-au-4.png\")
<\/figure>\n\n\n\nmy_second_dbt_model.sql<\/code>. At the bottom, you can select compile<\/strong>. This will replace all of the Jinja code with the actual SQL code, so you can see what the final SQL will look like:<\/p>\n\n\n\n![\"A](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2024\/11\/a-screenshot-of-a-computer-program-description-au-5.png\")
<\/figure>\n\n\n\n![\"\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2024\/11\/word-image-104727-24.png\")
<\/figure>\n\n\n\n![\"A](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2024\/11\/a-screenshot-of-a-computer-program-description-au-6.png\")
<\/figure>\n\n\n\nnot_null<\/code> test fails.<\/p>\n\n\n\n![\"A](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2024\/11\/a-screenshot-of-a-chat-description-automatically-1.png\")
<\/figure>\n\n\n\n![\"A](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2024\/11\/a-screenshot-of-a-computer-program-description-au-7.png\")
<\/figure>\n\n\n\n![\"A](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2024\/11\/a-screenshot-of-a-chat-description-automatically-2.png\")
<\/figure>\n\n\n\nNULL<\/code> value:<\/p>\n\n\n\n![\"A](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2024\/11\/a-screenshot-of-a-computer-description-automatica-20.png\")
<\/figure>\n\n\n\n![\"A](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2024\/11\/a-screenshot-of-a-computer-description-automatica-21.png\")
<\/figure>\n\n\n\nHAVING COUNT(*)>1<\/code>.<\/p>\n\n\n\n![\"A](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2024\/11\/a-screenshot-of-a-computer-code-description-autom.png\")
<\/figure>\n\n\n\n![\"A](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2024\/11\/a-screenshot-of-a-computer-description-automatica-22.png\")
<\/figure>\n\n\n\nConclusion<\/h2>\n\n\n\n
{{this}}<\/code>. But since the SQL itself was very complex, I needed to split it up in multiple statements and then \u201ccircular\u201d logic broke down.<\/p>\n\n\n\n