dbt

Put a {% macro name(args) %} ... {% endmacro %} block in a .sql file under the macros/ directory, then call it from any model with {{ name(args) }}. The macro's rendered text is spliced into the model's SQL at compile time.

In dbt, a view materialization stores only the SQL, so every read recomputes the query; a table materialization runs the query once per dbt run and stores the rows, making reads fast but data only as fresh as the last build.

A dbt model is one SELECT statement in a .sql file under models/. dbt renders its Jinja, wraps it in the DDL for the chosen materialization, and builds a table or view named after the file.

Because ref() does three things a literal name cannot: it declares a dependency so dbt builds the upstream model first, it resolves to the right database and schema for the environment you are running in, and it feeds lineage, docs and selection.

A dbt snapshot records how mutable source rows change over time as a Type 2 history table. Each run closes changed versions and inserts new ones, maintaining dbt_valid_from, dbt_valid_to, dbt_updated_at and dbt_scd_id.

Use source() for raw tables dbt reads but never builds, declared in a YAML sources block. Use ref() for anything dbt builds itself: models, seeds and snapshots. Both create DAG edges instead of hardcoded table names.

A seed is a CSV file in the project's seeds directory that dbt loads into the warehouse as a table when you run dbt seed. It suits small, static, version-controlled reference data, not raw source data.

dbt's four built-in generic tests assert column rules from YAML: unique flags duplicate values, not_null flags missing ones, accepted_values flags values outside a declared list, and relationships flags keys with no matching row in a referenced model.

In dbt, is_incremental() returns true only when the model is materialized as incremental, its target relation already exists in the warehouse, and the run is not in full-refresh mode. Otherwise dbt builds the model in full and the guarded filter is skipped.

Use dbt's check strategy when the source has no trustworthy updated_at column: it compares the values in check_cols instead. Prefer timestamp whenever a reliable modification timestamp exists, because it dates history by when the change happened rather than when dbt ran.

Every dbt test compiles to a SELECT that returns the failing rows, wrapped in a count. Zero rows means pass; any rows returned means fail, and the count is reported as the number of failures.

List the package with a version range in packages.yml, run dbt deps to install it under dbt_packages/, then call its macros namespaced by package name, for example {{ dbt_utils.star(from=ref('orders')) }}.

Query the values with run_query(), guard the result with {% if execute %} because run_query returns nothing during parsing, pull the column with results.columns[0].values(), then loop over that list to emit one SQL expression per value.

In dbt, unique_key names the column or columns that identify a row, so an incremental run updates or replaces matching rows in the target instead of only appending. Without it, an incremental model appends and can accumulate duplicates.

Compilation renders the Jinja and replaces the call with the referenced model's fully qualified relation for the active target. The plain SELECT is written under target/compiled; the wrapped DDL dbt actually executes is written under target/run when the model builds.

A trailing plus selects the model and everything downstream of it; a leading plus selects the model and everything upstream. Both include the model itself, and both traverse the dependency graph dbt builds from ref() and source() calls.

A dbt snapshot samples the source's current state each time it runs. If a row moves through several values between runs, only the last one is seen, so intermediate versions are never recorded and cannot be recovered later.

It takes the maximum value of the source table's loaded_at_field, measures the lag to the current time, and compares that lag with the warn_after and error_after thresholds in the source YAML, returning pass, warn or error per table.

They form the physical relation dbt compiles a source() call into. The source name defaults to the schema and the table name defaults to the identifier, so you set schema or identifier only when the warehouse names differ from the names you want in code.

dbt docs generate writes manifest.json and catalog.json into the target directory — the project graph plus column metadata queried from the warehouse. dbt docs serve starts a local web server rendering those files as a browsable catalog with a lineage graph.

Write a dbt singular test when the assertion is one-off and specific to particular models: a plain SQL file in tests/ that selects offending rows. Generic tests are parameterised macros declared in YAML and reused across many models and columns.

In dbt, append blindly inserts new rows; merge matches on unique_key and updates in place; delete+insert removes matching keys then reinserts; insert_overwrite replaces whole partitions present in the run. Which are available depends on the adapter.

That filter compares business event time against the highest event time already loaded, so any row arriving later but dated earlier falls below the watermark and is never selected. The run succeeds and the rows are silently lost forever.

The dependency becomes invisible: dbt may build the model before or alongside its upstream, so results are intermittently stale. Development and CI runs read production data, lineage and docs are wrong, and selection or slim CI silently skips the model.

By default nothing: dbt never sees it, so its latest version keeps dbt_valid_to NULL and still looks current forever. Configuring hard-delete handling makes dbt close that version out at run time instead.

Every past version. A rebuilt dbt snapshot contains one open row per current source row, so all closed validity windows vanish permanently — unlike models, snapshots accumulate state that cannot be re-derived from a mutable source.

A dbt run never checks freshness, so you must gate it: run dbt source freshness as a separate orchestrator step that fails the pipeline on error, or put a recency test on the source so dbt build skips every downstream model when it fails.

dbt build walks the DAG node by node, running each model and then its own tests before moving on, so a failed test marks that node an error and its descendants are skipped. dbt run then dbt test builds everything first and only tests afterwards.

Set the test's severity to warn, or give it a threshold with warn_if and error_if so small counts warn while large ones still fail. Turn on store_failures to persist the offending rows for investigation instead of deleting the test.