```json { "title": "Dbt Insights", "url": "https://narwhaldatasolutions.com/blog/dbt-insights/", "datePublished": "2022-11-11", "dateModified": "2025-01-10", "language": "en-GB" } ``` # Dbt Insights Don’t expect magic from [dbt](https://www.getdbt.com/) and don’t expect it will fix all your problems. Instead, expect to get a stable framework that makes your project as simple as possible. The only side effect of simplification I noticed is that I face much fewer problems and the ones I encounter are usually simple to debug. Bear in mind that every tool works just as smart as we allow it. The general concept of dbt is to use models, which in short, are SQL statements. Instead of using stored procedures, dynamic SQL, or Python data wrangling, dbt forces us to stick to its generic strategy. It gives us no choice, but to use it straightforwardly and organize our pipelines simply and efficiently. In the long term, this simplification brings benefits. Let’s check, how the main dbt features work in detail. A simple example: ``` ``` While checking logs, I found out, that there are many run arguments available. Most of the arguments use defaults. ``` ``` After a successful run, let’s explore logs and check how does the dbt execution sequence look like. As the first step, dbt parses [Jinja](https://jinja.palletsprojects.com/) macros and compiles the model. ``` ``` Then establishes a connection. ``` ``` Then generates a list of dependencies used in orchestration. At this step, dbt verifies if we have no loops and it puts the model in the correct place in our pipeline. This is how DAG (Directed Acyclic Graph) is generated in dbt. ``` ``` Then, dbt checks configurations. These may be set in the dbt_project.yml file or directly in model.sql. Dbt executes configured pre_hooks which are meant to run before model execution, although normally in the same transaction. For example, we can use pre_hooks to set the session variables: ``` ``` Then, dbt dynamically generates and executes a sequence of dbt-generic SQLs with Jinja injections. The body defined in our model is used in the sequence, as feed. The generic SQLs are injected with it and eventually committed. Dbt uses here a common strategy with temporary objects and renaming. It increases the availability of tables, reduces locks, and provides a simple rollback when necessary. Here is the code for `full-refresh`. I think you have already seen something similar before. ![create temp table --> rename original table to backup --> rename temp table to original --> drop backup table](http://narwhaldatasolutions.com/wp-content/uploads/2020/11/dbt2-2.png) full-refresh flow ``` ``` In the case of incremental load, the execution sequence looks different, as we use only a slice of data. We run the examples on PostgreSQL, so below we can see the delete/insert strategy. However, the incremental strategies are database-dependent and for other database systems, we may also use the merge strategy. Increment loads are something we deal with every day in data engineering, yet here we get a generic solution that, once more, makes the project unified and easy to maintain. Moreover, porting it to a different DB backend is mostly a matter of minor configuration changes, while the model’s logic remains untouched. ![create temp table --> delete from target table --> insert into target table](http://narwhaldatasolutions.com/wp-content/uploads/2020/11/dbt2-3.png) ``` ``` In the configuration block, we also defined an *after_commit* post_hook, which dbt executes outside the transaction. Pre_hooks and post_hooks may run within the transaction, however, in this case, we had an important reason to put one outside. Due to the fact, that all indices (created formerly) remain in the backup object until the transaction is committed, the database does not allow us to create a duplicate. Indices have unique names within the database. It is impossible to have two indices at the same time with the same name. This is why we need to wait till the backup object (with original indices) is dropped and then proceed with new ones. That may happen only *after_commit*. We can see that dbt gives us a lot of flexibility in this matter. Other option is not to specify the index name but to rely on the automatically generated one. Another good example is vacuum post_hook, defined on a project level in dbt_project.yml file. In this case, we do not need to repeat the same code in every model. This is how DRY code works in dbt. ``` ``` Dbt run command can have more complex syntax. We may execute all depending objects with or without a tag or other constraints. Conditions may be joined with a comma (AND) or without a comma (OR) to get the exact set of conditions. Dbt docs example: ``` ``` Other commands, like `dbt test`, `dbt source freshness` or `dbt snapshot` are also generic SQL commands that are generated and executed in a similar way to the commands above. We do not see any rocket science or magic in dbt. We don’t care about building slowly changing dimensions (SCD type2) or source validations. They are already there. We are allowed to manipulate the generic macros or build our own to fit exceptional models. However, the main idea remains, to build our project as simple as possible. ![not_null: To check data quality and pipeline quality accepted_values: To check business assertions and unexpected values uniqueness: To verify the granularity and check duplicates integrity: To check the integrity of source and our models](http://narwhaldatasolutions.com/wp-content/uploads/2023/01/dbt2-4.png) dbt generic tests The most important fact is that all dbt features are managed and executed by a centralized engine. Dbt gives us common core features, that cover most of the engineering requirements and leave some place for developers’ fantasies with Jinja macros or community snippets. This is what makes this tool unique. We as a team are empowered to use the same engineering standards and same coding techniques. In the very beginning, I thought it would constrain our work too much, but after a year of using it, the constraints appeared to be smart. The pipelines, with their simplicity, are almost error-proof and much easier to debug. ![pipeline stages](http://narwhaldatasolutions.com/wp-content/uploads/2020/11/dbt2-5.png) pipeline stages Within a project, all models use the same strategies and the same macros. Once more I strongly recommend dbt for all projects that are supposed to have standardized architecture and standardized solutions. If our project nature or business forces us somehow to use different techniques for each model or pipeline, dbt can probably handle that, but it was not meant to work that way. It was meant to simplify and organize data transformations in a very convenient and efficient way. In the long term, this simplification brings benefits.