dbt (data build tool) is an open-source analytics engineering framework that enables data teams to transform data inside the cloud data warehouse using SQL + software engineering best practices.

Key Points

• Transformation Only: It only does transformation (the “T” in ELT).
• In-Warehouse Processing: It runs inside your warehouse (Snowflake, BigQuery, Redshift, Databricks, etc.).
• Analytics-Ready: It turns raw data into analytics-ready data products.
• Engineering Best Practices: It brings version control, testing, modularity, documentation, and CI/CD to data work.

How it Works in Practice

1. Extract & Load: Raw data is loaded into the warehouse (by Fivetran, Airbyte, Stitch, custom scripts, etc.).
2. Transform (dbt): dbt transforms it (staging → intermediate → marts).
3. Consume: Clean data is consumed by BI tools, data science, applications, or AI models.

Models in dbt

A model is a single .sql file that contains a SELECT statement (plus optional configuration). When you run dbt run, dbt compiles that SQL and executes it in your data warehouse to create a table or view.

Key Characteristics of a Model

• Single Responsibility: One model = one logical transformation.
• SQL & Jinja: Written in pure SQL + Jinja templating.
• Organization: Stored in the models/ folder.
• References: Can reference other models or raw sources.

Referencing Models: The ref() Function

ref() is a Jinja function that tells dbt: “I depend on another model. Please give me its final table/view name and build me after it.”

Basic Syntax

1select * from {{ ref('stg_orders') }}

Why ref() is Revolutionary

• Automatic Dependency Management: dbt builds a Directed Acyclic Graph (DAG) based on every ref() call. It knows the exact order in which models must run.
• No Hardcoded Table Names: You never write from analytics.stg_orders. You always use ref().
• Environment Awareness: In development vs. production, dbt can use different schemas/databases automatically.
• Lineage & Documentation: Every ref() creates a visible data lineage.

ref() vs. source()

Advanced ref() Examples

1-- Referencing a model in a specific package
2{{ ref('package_name', 'model_name') }}
3
4-- Using a variable
5{{ ref(var('staging_prefix') ~ 'orders') }}

How the DAG (Directed Acyclic Graph) Works

• Nodes: Represents Models, Sources, Snapshots, etc.
• Arrows: Represent dependencies created by ref() calls.
• dbt always runs models in the correct topological order.
• If one model fails, all downstream models are automatically skipped.

Materializations

A materialization tells dbt how to persist the output of a model in the data warehouse. You define it using a config block at the top of your model:

1{{ config(materialized='table') }}

Full Breakdown of Materializations

Deep Dive into Materialization Types

1. View

When using the view materialization, your model is rebuilt as a view on each run, via a CREATE VIEW AS statement.

• How it works: Stores only the SQL logic of the transformation in the database, not the actual data.
• Advantage: No additional storage is consumed; queries on top of the view always fetch the latest records.
• Use Case: Best suited for models that do not perform heavy transformations (e.g., renaming or recasting columns).

2. Table

When using the table materialization, your model is rebuilt as a physical table on each run, via a CREATE TABLE AS statement.

• How it works: Recreates the physical table completely during every run.
• Disadvantage: As data volume grows and transformations become complex, rebuilding tables takes a significant amount of time.
• Use Case: Ideal for tables queried directly by BI tools to ensure a faster querying experience for the end users.

3. Ephemeral

Ephemeral models do not create any physical representation in the database.

• How it works: dbt inlines the SQL code of the ephemeral model directly into any downstream models that reference it (similar to a Common Table Expression, or CTE).
• Advantage: Avoids database object overhead and extra storage costs for intermediate steps.
• Disadvantage: Cannot be queried directly or referenced as easily using ref() from multiple distant models without compiling large nested CTEs.

4. Incremental

Incremental models allow dbt to insert or update records into a table since the last time that model was run.

• How it works: Instead of rebuilding the entire table, it only processes new or changed records.
• Advantage: Saves considerable time and warehouse compute resources when dealing with large datasets.
• Disadvantage: Requires more advanced configurations and a finer touch.
• Use Case: Best for event-style data. Start with views or tables first, and transition to incremental models only when runs begin to slow down.

[!NOTE] Detailed settings and strategies for incremental models are covered in the Incremental Models in Depth section below.

Example: Incremental Model Syntax

Here is a basic example of an incremental model configuration:

 1{{ config(
 2    materialized='incremental',
 3    unique_key='order_id'
 4) }}
 5
 6select
 7    order_id,
 8    customer_id,
 9    order_date,
10    amount
11from {{ ref('stg_orders') }}
12
13{% if is_incremental() %}
14  where order_date > (select max(order_date) from {{ this }})
15{% endif %}

How Models, ref(), and Materializations Work Together

This is the core mental model of dbt:

1. Write a model: Create a .sql file.
2. Declare dependencies: Use ref() to reference other models.
3. Choose a materialization: Control how the table/view is built in the warehouse.
4. Execute: dbt compiles the code into optimized SQL and executes it in topological order.

Real-World Pipeline Example

[stg_orders (view)] ──> [int_orders_enriched (table)] ──> [fct_sales (incremental)]

When you execute dbt run, dbt will:

1. Build stg_orders first.
2. Build int_orders_enriched next (since it depends on stg_orders).
3. Build fct_sales last (applying incremental logic).

Model Naming Conventions & Project Structure

Organizing models into distinct layers makes your codebase modular, easier to maintain, and logical to navigate.

The Recommended Layers

1. Staging (stg_)

• Goal: Make raw data usable and consistent.
• Details: This is the first layer built on top of raw sources. It should not contain complex business logic.
• Focus Areas: Renaming columns, casting data types, basic filtering, and handling null values.

2. Intermediate (int_)

• Goal: Apply core business rules and pre-combine data.
• Details: Joins multiple staging models together. It contains reusable calculations and intermediate transformations.

3. Fact Tables (fct_)

• Goal: Store measurable, event-based business activities (e.g., transactions, logs).
• Details: These are the primary tables that analysts and dashboards query. They typically contain foreign keys referencing dimension tables and numeric metrics.

4. Dimension Tables (dim_)

• Goal: Store descriptive attribute data (e.g., users, products).
• Details: These answer questions like “who”, “what”, “where”, and “when”. Often utilizes Slowly Changing Dimension (SCD) logic.

Best Practices for Models

• One Model = One Business Concept: Avoid monster queries that do too much.
• Keep Models Small: Ideally under 200–300 lines of SQL.
• Always Use ref(): Never hardcode table or schema names.
• Start with Views: Use view for staging and intermediate layers first.
• Use Tables for Marts: Choose table for most final marts unless data size becomes prohibitive.
• Apply Incremental Sparingly: Only use incremental models when there is a clear, verified performance benefit.
• Use Config Blocks: Add a clear config() block at the top of your model file.

Example of an Optimized Config Block

1{{ config(
2    materialized='incremental',
3    unique_key='order_id',
4    incremental_strategy='merge',
5    on_schema_change='sync_all_columns',
6    tags=['finance', 'daily']
7) }}

Incremental Models in Depth

Incremental models process only new or updated records rather than rebuilding the entire table from scratch. To configure them successfully, you must tell dbt:

1. How to filter the rows on an incremental run.
2. The unique key of the model (optional, but highly recommended for handling updates).

The is_incremental() Macro

The is_incremental() macro powers incremental materializations. It returns true only if all of the following conditions are met:

• The model already exists as a table in the database.
• The --full-refresh flag is not passed during execution.
• The running model is configured with materialized='incremental'.

Filtering for New Rows

To ensure we only process new data and avoid duplicate rows, we apply a conditional filter using is_incremental().

 1{{
 2    config(
 3        materialized='incremental'
 4    )
 5}}
 6
 7select
 8    *,
 9    my_slow_function(my_column)
10from {{ ref('app_data_events') }}
11
12{% if is_incremental() %}
13  -- This filter will only be applied on an incremental run.
14  -- Uses >= to include records whose timestamp occurred since the last run of this model.
15  -- If event_time is NULL or the table is truncated, the condition will always be true and load all records.
16  where event_time >= (select coalesce(max(event_time), '1900-01-01') from {{ this }})
17{% endif %}

Managing Updates with unique_key

Defining the optional unique_key parameter enables updating existing rows instead of just appending new rows:

• If new information arrives matching an existing unique_key, the new record replaces the old one.
• If duplicate records arrive, they are safely ignored/deduplicated.
• Configuration: Specify the key as a single column name string or a list of single-quoted column names (e.g., ['col1', 'col2', ...]).
• Gotcha: Columns used as unique keys should not contain NULL values, or the incremental model might fail to match rows and generate duplicates.

Rebuilding Incremental Models

If your transformation logic changes, the old rows in the database will not match the new logic. In this case, you must rebuild the table from scratch:

1dbt run --full-refresh --select my_incremental_model+

[!WARNING] Using the --full-refresh flag will cause dbt to drop the existing target table in the database before rebuilding it for all time.

Handling Schema Changes (on_schema_change)

You can configure the on_schema_change parameter to control behavior when source and target column structures diverge. This helps avoid unnecessary --full-refresh operations.

Supported values for on_schema_change:

• ignore (Default): Does not update the schema in the target table.
• fail: Triggers an error message and stops execution if schemas diverge.
• append_new_columns: Appends new columns to the existing table, but does not remove columns that are no longer present.
• sync_all_columns: Adds new columns and removes missing ones (inclusive of data type changes).

  [!IMPORTANT] On BigQuery, changing column types requires a full table scan; be mindful of the cost and performance trade-offs.

Executing Models with dbt run

The dbt run command compiles and executes SQL model files against the target database.

[!NOTE] The dbt run command only applies to models. It does not execute tests, snapshots, seeds, or other resource types.

How dbt run Works

1. Establishes Connections: dbt connects to the target database and runs the compiled SQL required to materialize the data.
2. Topological Execution: Models are executed in the exact order defined by the Directed Acyclic Graph (DAG).
3. Multi-Threading: Intelligent multi-threading is used to run independent models in parallel, minimizing execution time.
4. Zero-Downtime Deployment: Deploying updated models often requires destroying prior versions. dbt minimizes downtime by first building models under a temporary name, then dropping and renaming them within a single transaction (for adapters that support transactions).

Incremental Strategies

Incremental strategies control how dbt applies data changes to target tables during incremental runs.

Strategy-to-Macro Mapping

Detailed Strategy Breakdown

1. append

• How it works: Inserts selected records directly into the destination table without updating or deleting existing rows.
• Pros/Cons: Very simple to implement and has extremely low processing costs. However, it does not check for duplicates. If a record appears multiple times in the source, it will be inserted again, resulting in duplicate rows.
• SCD Alignment: Does not align directly with typical Slowly Changing Dimension (SCD) type 1 or type 2 logic.

2. delete+insert

• How it works: Deletes existing data for a given unique_key from the target table, then inserts the new records.
• Pros/Cons: Ensures updated records are fully replaced, avoiding partial updates. It is useful when a unique_key isn’t truly unique or when the database doesn’t support a native merge command. However, it can be less efficient for very large datasets.

3. merge

• How it works: Inserts records with a unique_key that doesn’t exist yet in the target table, and updates existing records with matching keys.
• Pros/Cons: Best suited for smaller tables or incremental datasets. It can become expensive for large tables because it scans the entire destination table to determine what to update/insert.

  [!NOTE] If you use merge without specifying a unique_key, it behaves exactly like the append strategy.

• SCD Alignment: Mirrors the logic of SCD Type 1, where changes overwrite historical values rather than tracking them over time.

4. insert_overwrite

• How it works: Overwrites entire partitioned sections of a target table with new data rather than merging or updating row-by-row.
• Pros/Cons: Highly efficient for tables partitioned by date or another key, as it refreshes recent or corrected data without a full table rebuild.
• SCD Alignment: Since it replaces partitions wholesale, it does not track row-level history (non-SCD).

5. microbatch

• How it works: Splits large time-series datasets into distinct time-based batches (e.g., daily or hourly).
• Pros/Cons: Supports parallel batch execution, significantly speeding up processing for massive time-series datasets.

Custom Incremental Strategies

From dbt v1.2 onwards, users can define custom incremental strategies without creating a completely new materialization:

1. Define a macro: Create a macro named get_incremental_STRATEGY_sql (replace STRATEGY with the name of your custom strategy).
2. Configure the model: Set incremental_strategy='STRATEGY' within your incremental model configuration.

[!IMPORTANT] dbt does not validate custom user-defined strategies; it will search for the corresponding macro and raise an error if it cannot be found.