My previous blog post explored what dbt is and where it fits in the ELT process, highlighting that dbt allows for transformations to raw data that has already been loaded into a data warehouse.
But how does dbt actually transform data?.. This is where dbt models come in.
What are dbt models?
In dbt, models define how raw data should be transformed into clean and structured datasets that can be used for analysis and reporting.
A model typically comprises of a SQL file that contains a SELECT statement which defines how data should be transformed. For example, when extracting raw data you may find that there are inconsistent column names, unnecessary fields or information that needs to be formatted before it can be used for reporting. A dbt model allows you to write a SELECT statement to select the relevant fields, rename them and apply any necessary transformations.
When a dbt model is executed, dbt will instruct the data warehouse it is connected to to run the SQL on the data and create a resulting dataset.
A key thing to note is that dbt does not store or process the raw data itself. Instead it uses the computing resources of the data warehouse it is connected to.
How dbt models are stored
A useful feature of dbt is that it allows you to choose how the results of a model are stored in the data warehouse. The method of determining how dbt builds and maintains a model in the data warehouse it's connected to is referred to as materialisation.
The two main materialisations are:
Views - stores the transformations of an SQL query as an SQL query, such that it can be queried like a table. A view does not store a physical copy of the query results, as a table would, but the SQL is executed when the view is queried.
As a result, a benefit of views is that they can reflect changes in the underlying data without needing to rebuild the view each time. However, complex views can be slower to query.
Tables - stores the results of an SQL query physically within the data warehouse. dbt instructs the data warehouse to execute the transformations and physically store the resulting data.
A benefit of materialising a model as a table is that it can improve query performance, particularly for complex transformations because the results have already been calculated. However, a table will not automatically reflect any changes in the underlying data when queried, as a view would. The model would need to be run again for the table to update and reflect the changes.
The choice of which materialisation to use depends on a number of factors, such as query performance, storage costs, data freshness requirements and the complexity of the transformation.
Why are dbt models useful?
dbt models are dbt's way of providing a structured way to develop, organise and manage SQL transformations to raw data.
Some of the benefits of this are:
Reusability - models can be referenced by other models, meaning the same transformation logic can be reused throughout a project without having to repeatedly write out the same SQL
Maintainability - breaking transformations up into individual models makes it easier to understand, troubleshoot and update specific parts of the data pipeline
Flexibility - dbt allows users to choose how models are materialiased, which provides flexibility to balance query performance, storage requirements and data freshness
Consistency - the configurations of a model can be applied across an entire project, helping teams develop consistent development standards
Collaboration - since models are stored as code (SQL SELECT statements), they can be version-controlled using Git, which makes it easier for multiple developers to collaborate, track changes and review modifications