dbt Reference

dbt (data build tool) handles the Transform step of ELT pipelines. Unlike traditional ETL tools that move and transform data between systems, dbt runs entirely inside your data warehouse — you write SELECT statements, and dbt compiles and runs them as CREATE TABLE or CREATE VIEW statements. dbt brings software engineering practices to data transformation: version control with Git, automated testing with schema.yml, documentation, and a clear lineage graph showing how data flows between models.

ref() is used to reference another dbt model — it creates a dependency in the DAG and ensures the referenced model runs first. For example, SELECT * FROM {{ ref("stg_orders") }} references the stg_orders.sql model. source() is used to reference a raw table that exists in your data warehouse but is not managed by dbt — it comes from an external ingestion tool like Fivetran. You declare sources in a sources.yml file. Using source() enables dbt to include raw tables in the lineage graph and run source freshness checks.

An incremental model processes only new or changed data on each run, instead of reprocessing the entire dataset. You wrap the filter condition inside {% if is_incremental() %}...{% endif %} so it only applies on incremental runs (not on the initial build). For example: WHERE event_date > (SELECT MAX(event_date) FROM {{ this }}) filters to only new events. Set materialized="incremental" and unique_key in the config() block. On first run, dbt builds the full table. On subsequent runs, it merges (UPSERT) only new rows.

dbt generic tests are data quality checks defined in schema.yml YAML files alongside your models. The four built-in generic tests are: unique (checks no duplicate values in a column), not_null (checks no NULL values), accepted_values (checks values are from a specified list), and relationships (checks referential integrity — that every value in a column exists as a key in another model). You define them under a column's tests: key. Run them with dbt test. Failed tests by default generate warnings; you can configure severity to error to fail the pipeline.

A dbt macro is a reusable piece of Jinja templating code defined with {% macro name(args) %} ... {% endmacro %} and stored in the macros/ directory. Call it in any model SQL with {{ macro_name(args) }}. Macros eliminate SQL repetition across models — for example, a cents_to_dollars macro converts a column from integer cents to decimal dollars and can be called from any model. Use macros for: unit conversions, repeated CASE statements, dynamic column generation, and cross-database SQL abstraction.

dbt run executes all model SQL files and materializes them as tables or views in your warehouse. dbt test runs all tests defined in schema.yml files — both generic tests (unique, not_null, etc.) and any custom singular tests. dbt build runs both dbt run and dbt test together in the correct order: it runs each model and immediately tests it before proceeding to downstream models. Use dbt build in CI/CD pipelines for the most thorough execution. Add --select model_name to target a specific model or subgraph.

A dbt snapshot implements SCD (Slowly Changing Dimension) Type 2 history tracking. It takes a periodic "snapshot" of a source table and tracks how records have changed over time by adding dbt_valid_from and dbt_valid_to columns. When a row changes (detected by a timestamp or a hash of all columns), the snapshot inserts a new row and closes the old one. Use snapshots to preserve history for source tables that do not maintain their own change history — like a CRM contacts table that overwrites records on update.

dbt seeds load static CSV files from the seeds/ directory into your data warehouse as tables. Run dbt seed to load them. Reference them in models with {{ ref("seed_name") }}. Seeds are ideal for small, static lookup tables that change rarely — such as country codes, state abbreviations, category mappings, or cost center codes. Do not use seeds for large datasets (dbt recommends keeping seeds under a few MB) or for data that changes frequently. For frequently updated lookup data, use a source table ingested by your ELT pipeline instead.