For years, data transformation in the data warehouse was done with scattered SQL scripts, difficult to maintain, without version control and without testing. dbt (data build tool) arrived to change this forever, introducing a software engineering approach to the data world that has revolutionized the way data teams build, test and document their transformation pipelines. dbt has democratized access to data transformation, enabling analysts, data scientists and engineers to collaborate with the same tools and practices, reducing friction and accelerating the time-to-value of data projects.
dbt is an open source tool that allows data teams to transform information in the data warehouse using SQL but with software development best practices: versioning, testing, automatic documentation and CI/CD. Its philosophy is simple but powerful: apply software engineering principles (modularity, reusability, testing, documentation) to the data transformation process. dbt does not perform extraction or loading (the E and L of ELT), but focuses exclusively on the T: transformation. It works directly in your data warehouse (Snowflake, BigQuery, Redshift, Databricks, PostgreSQL, among others) executing SQL queries that create tables and views, leveraging the processing power of these platforms to transform data at scale.
Before dbt, analysts and data engineers worked with loose SQL scripts without organization, stored in scattered files without a clear structure; without version control or with Git but without applying good practices such as code reviews and branching; without documentation or with outdated documentation that quickly lost value; without automated quality tests that verified data integrity; and with dependencies between tables difficult to understand and manage, leading to errors and high technical debt. dbt solves all these problems and also democratizes data transformation, allowing analysts and data scientists to work with the same tools as engineers, eliminating silos and fostering interdisciplinary collaboration in data teams.
A typical dbt project has an organized structure that reflects software engineering best practices. The models/ folder contains the data models organized in logical layers: staging/ for the cleaning and normalization layer, where basic transformations are applied such as renaming columns, correctly typing fields and standardizing formats; intermediate/ (optional) for intermediate models that combine data from multiple staging sources; and marts/ for business models (final tables) that answer specific business questions. The tests/ folder contains custom tests that verify data quality, such as validating that emails have the correct format or that null values in critical fields do not exceed a defined threshold. The macros/ folder contains reusable SQL macros that encapsulate complex logic and avoid code duplication. The analyses/ folder stores ad-hoc analyses and exploratory queries. The snapshots/ folder manages SCD (Slowly Changing Dimensions) type data, allowing tracking of data changes over time. The dbt_project.yml file contains the project configuration, defining the structure, variables, paths and execution settings. The profiles.yml file manages connections to the data warehouse, allowing different environments to be configured (development, staging, production).
The fundamental concepts of dbt are the basis of its power and flexibility. A Model is a SQL file that defines a table or view that dbt executes in your data warehouse. Models can reference other models using the {{ ref('model_name') }} function, allowing the construction of a DAG (Directed Acyclic Graph) of dependencies between models. This DAG defines the execution order, ensuring that models run in the correct order respecting their dependencies. dbt automatically builds this DAG from the references between models, simplifying dependency management and ensuring pipeline consistency.
Macros are reusable SQL code fragments defined in the macros/ folder and invoked with {{ macro_name() }}. Macros allow encapsulating complex logic, such as string cleaning, date formatting, or custom aggregation functions, avoiding code duplication and facilitating maintenance. Macros in dbt use Jinja, a templating engine that allows adding conditional logic, loops and variables to SQL queries, making models much more flexible and dynamic.
Variables in dbt allow parameterizing models, facilitating configuration and reuse. They can be defined in dbt_project.yml or passed on the command line, and are used in models with {{ var('variable_name') }}. Variables are especially useful for handling different environments (development, staging, production) or for configuring business parameters such as dates, thresholds or categories.
Tests in dbt are one of its most powerful features and ensure the quality of transformed data. dbt supports two types of tests: generic tests predefined that apply to columns (such as not_null, unique, accepted_values) and custom tests defined as SQL queries in the tests/ folder. Custom tests allow verifying specific business rules, such as that an order date is not prior to the customer registration date, or that the total amount of an order is consistent with the sum of its line items. dbt runs tests automatically as part of the execution process, facilitating early detection of data quality issues.
Documentation in dbt is automatically generated from metadata and comments in models. dbt can generate a static website with complete project documentation, including descriptions of each model, column, dependencies, and the underlying SQL code. This automatic documentation ensures documentation is always up to date and accessible to all team members, facilitating onboarding of new members and understanding of the data pipeline.
Snapshots in dbt allow managing SCD (Slowly Changing Dimensions) type data simply and efficiently. Using the type 2 (historical) strategy, dbt tracks changes in data over time, maintaining a complete history of each record with start and end dates. This is essential for temporal analysis, auditing and understanding the evolution of business entities.
Exposures in dbt allow documenting how models are used by data consumers, such as BI dashboards (Tableau, Looker, Power BI) or external applications. Exposures help understand the impact of changes in models and communicate the dependency between data pipelines and applications that consume that data.
dbt Cloud is dbt Labs' SaaS platform that adds additional functionality such as in-browser development (IDE), execution scheduling, alerts and team collaboration. dbt Cloud simplifies dbt adoption in large teams and organizations looking for a managed solution with support and advanced CI/CD features.
Best practices in dbt include organizing models into logical layers (staging, intermediate, marts) to maintain clarity and modularity; using consistent naming conventions to facilitate navigation and understanding of the project; implementing comprehensive tests to ensure data quality; documenting all models and columns to maintain transparency and facilitate onboarding; using macros to encapsulate reusable logic and avoid duplication; and adopting a Git-based development workflow with code reviews and CI/CD to ensure quality and stability of the project.
The dbt ecosystem is growing rapidly, with a wide range of packages and plugins available. dbt-utils is a package of useful macros for common tasks such as pivoting tables, generating dates, or building dynamic SQL. dbt-external-tables facilitates managing external tables in the data warehouse. dbt-codegen automatically generates dbt code from existing databases, accelerating development and facilitating migration of legacy projects. The dbt community is very active and constantly contributes new packages and improvements, making the tool increasingly powerful and versatile.
dbt has transformed the way data teams work, turning data transformation into an agile, collaborative and controlled process. By applying software development best practices to data, dbt has significantly reduced technical debt in data pipelines and improved the quality and reliability of information available for decision making.
At Curaduriadedatos.com, we help organizations implement dbt in their data projects, providing training, advice on model design, performance optimization, and best practices to ensure the success of data transformation.