dbt Architect flashcards

32 cards. Read the term and the definition here. The deck at the top of this page is optional.

Staging model scope

Staging models map one-to-one to source tables and perform only renaming, casting, and light cleaning. Business logic belongs in intermediate or mart models, keeping staging cheap and reusable.

is_incremental() guard

Returns true only when the target relation exists and full-refresh is off. The incremental filter must sit inside this block, or the first build queries a table that does not exist.

Merge incremental strategy

Matches incoming rows to existing rows on unique_key and updates or inserts accordingly. The key must be unique within each batch, or merge produces duplicate rows in the target.

Insert overwrite strategy

Replaces whole partitions instead of matching rows, suited to partitioned warehouses such as BigQuery and Spark. It is fast and atomic per partition but requires a correct partition column.

Ephemeral materialization

Inlines the model as a CTE in every downstream model rather than creating a warehouse object. Use it only for logic reused within a single downstream chain, since each consumer re-executes it.

Fact and dimension grain

A fct_ model declares one row per event or transaction; a dim_ model declares one row per entity. The grain statement drives which tests and joins are valid.

Enforced model contracts

Setting contract enforced true locks column names and data types. Every column must declare a type, and schema changes become explicit review events instead of silent drift.

generate_schema_name macro

A custom macro that controls target schema naming. The common pattern appends the custom schema to the target schema, so dev and CI builds never overwrite production tables.

Target selection

The target key in profiles.yml chooses which connection block dbt uses, overridable with --target or DBT_TARGET. CI sets it per pull request to isolate concurrent builds.

Source freshness thresholds

warn_after and error_after per source table define acceptable ingestion lag. They should reflect the ingestion SLA, and dbt source freshness runs before transformation jobs.

Warehouse separation

Loading, transformation, and BI workloads use separate warehouses so a heavy backfill cannot starve dashboard queries. Transformation size follows worst-case spill, not average query cost.

Partition pruning

Filtering directly on the partition column lets the warehouse skip irrelevant data. Wrapping that column in a function such as cast() disables pruning and forces a full scan.

Package lock file

packages.lock.yml pins exact resolved versions of packages and their transitive dependencies. Committing it alongside packages.yml makes builds reproducible across environments and time.

Slim CI selection

dbt build --select state:modified+ --defer --state prod-manifest builds only changed models and their downstream dependents, resolving unbuilt upstream refs to production relations.

Defer flag

With --defer, refs to models not built in the current run resolve to their relations in the state manifest. This lets CI test a few changed models against production data.

dbt build versus run

dbt build interleaves tests with models in DAG order, so a failing test blocks downstream models in the same run. Separate run then test commands do not block.

dbt retry

Re-executes only the nodes that failed or were skipped in the previous invocation, reading run_results.json. It never re-runs successful nodes, saving time and compute.

Graph operators

Selection syntax where +model selects upstream parents, model+ selects downstream children, and 2+model selects two levels up. Commas intersect selections; spaces union them.

Run artifacts

manifest.json holds the compiled DAG and configs, run_results.json holds per-node status and timing, and catalog.json holds warehouse statistics from dbt docs generate.

Deployment promotion

The tested git commit is the unit promoted from CI to production. Manual edits to production models are forbidden because they break reproducibility and lineage.

Test severity levels

Severity warn records a failure and keeps the run green; severity error fails the node and blocks downstream models. Choose severity per test based on business impact.

Relationships test

Validates referential integrity by checking that every child foreign key value exists in the parent key column. It is the main defense against orphaned fact rows.

Model access modifiers

Private limits refs to the same group, protected to the same project, and public allows cross-project refs. Combined with groups, they enforce domain boundaries at compile time.

Exposures

Declarations of downstream consumers such as dashboards and ML models. They appear in lineage so upstream model changes surface their downstream impact during review.

PII tagging

Marking columns or models with meta.pii lets masking policies and selectors target them. Exclude PII-tagged models from broad selectors used by non-privileged roles.

Singular tests

SQL files in the tests directory that return failing rows. They encode business rules generic schema tests cannot express, such as balance reconciliation across tables.

Incremental lookback buffer

The filter uses max(loaded_at) minus a buffer sized for late-arriving data. Buffers that are too small silently drop late rows; too large wastes scanned bytes.

Microbatch incremental

A dbt 1.9+ strategy that splits a model into bounded time batches, each retried independently. It controls memory and recovery cost on very large event tables.

Fan-out join

A join where the dimension side has duplicate keys, multiplying fact rows. It inflates every downstream aggregate, so grain must be validated with a unique test.

Thread count limits

Threads control dbt concurrency, but throughput is capped by the warehouse's concurrent-query limit. Raising threads beyond it adds queue time rather than speed.

Cost optimization order

Cut scanned rows first via pruning and filters, then avoid rebuilding unchanged models via state selection, then right-size compute. Reversing the order wastes effort.

Model splitting rule

Split a model when a single run spills to disk or exceeds its time budget. Keep it whole when splitting adds extra scans without reducing the data volume processed.