dbt Analytics Engineering flashcards

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

Ephemeral materialization

An ephemeral model is never created in the warehouse. dbt inlines it as a CTE inside each downstream model that refs it, so it adds no storage but increases downstream query size.

is_incremental() guard

A Jinja function returning true only when the target table already exists and the model is running in incremental mode. It wraps the filter so the first build loads all rows.

Incremental merge strategy

With a unique_key, dbt issues a merge statement that updates matching rows and inserts new ones. Without a unique_key, merge degrades to append and duplicates can appear.

delete+insert strategy

Default incremental strategy on Postgres and Redshift. dbt deletes all rows matching the unique_key values in the new batch, then inserts the batch, giving idempotent reruns.

insert_overwrite strategy

BigQuery and Spark strategy that replaces whole partitions rather than individual rows. Requires a partition config; it is cheaper than merge for large partitioned fact tables.

ref() function

Jinja function resolving a model name to its fully qualified relation at compile time. It records a DAG dependency, enables lineage, and lets dbt swap environments.

Surrogate key generation

dbt_utils.generate_surrogate_key hashes a list of columns into a stable key. Null inputs yield null unless wrapped in coalesce, so handle nulls before hashing.

Staging model conventions

Staging models map one-to-one with source tables, applying renames, casts, and deduplication only. Business logic and joins belong in intermediate or mart models downstream.

Source freshness thresholds

Configured per source table with loaded_at_field plus warn_after and error_after durations. dbt source freshness compares the max loaded timestamp to current time and warns or fails.

relationships generic test

Checks referential integrity: every value in the tested column must exist in the specified field of the referenced model or source. Failing rows are returned by the test query.

accepted_values generic test

Asserts that every non-null value in a column belongs to an explicit list. Useful for status and enum columns; combine with not_null to catch missing values.

Singular tests

Standalone SQL files in the tests/ directory. Each returns rows that violate the assertion; zero rows means pass. They take no arguments and cannot be reused parametrically.

Custom generic test

A macro in the tests/ or macros/ directory that accepts arguments and returns a failing-row query. Referenced in YAML like built-in tests and reusable across models.

store_failures config

When enabled on a test, dbt writes the failing rows to an audit table in the target schema. It preserves evidence for debugging instead of discarding the result.

dbt build command

Runs seeds, snapshots, models, and tests in DAG order in a single invocation. A failing test blocks downstream models, preventing bad data from propagating.

Test severity levels

severity: error fails the run and returns non-zero; severity: warn logs a warning and continues. Warn is used for soft quality gates that should not block deployment.

Source declaration block

YAML entry giving a raw table a logical name, database, schema, and optional freshness and tests. Declaring sources enables source() refs, lineage, and freshness checks.

dbt docs generate

Compiles the project and produces manifest.json and catalog.json containing model metadata, column descriptions, compiled SQL, and the lineage graph used by the docs site.

Exposures

YAML declarations of downstream consumers such as dashboards, reports, or ML pipelines. They extend lineage beyond dbt models and let impact analysis trace what a model change affects.

Model contracts

With contract: enforced: true plus declared column names and data types, dbt validates the model output at build time and fails if the warehouse schema does not match.

Access modifiers

private, protected, and public settings on models control which other models or groups may ref them. Private models are restricted to their own group, enforcing project boundaries.

Model versions

Declaring versions with latest_version lets a model evolve while consumers pin to a specific version via ref('model', v=1), avoiding breaking downstream dependencies.

Docs blocks

Markdown files wrapped in {% docs name %} ... {% enddocs %} and referenced by name in YAML descriptions. They centralize long descriptions and are reused across columns.

Meta governance fields

Arbitrary key-value pairs attached to models or columns in YAML, such as owner, pii, or sla. They are queryable from the manifest for automated policy enforcement.

dbt_project.yml hierarchy

Folder-level config such as models: project: marts: +materialized: table applies to all models beneath that path. Deeper, more specific paths override broader settings.

env_var() in profiles

Jinja function reading environment variables, typically for credentials and schema names. It keeps secrets out of version control and lets one profile serve multiple environments.

Slim CI with state:modified

CI runs dbt build --select state:modified+ --defer --state prod-artifacts, building only changed models and their children while deferring unchanged refs to production.

Graph operators in selection

Prefix + selects ancestors (parents), suffix + selects descendants (children), n+ limits depth, and @ selects parents plus the children of those parents.

Snapshots and SCD Type 2

Snapshots capture row history with unique_key, a strategy of timestamp or check, and updated_at. dbt_valid_from and dbt_valid_to columns delimit each version's validity window.

dbt seed command

Loads CSV files from the seeds/ directory into the warehouse as tables. Suitable for small static mappings; large datasets belong in the source system instead.

Threads configuration

The threads setting in profiles.yml controls how many models dbt runs in parallel. Higher values speed up wide DAGs but increase warehouse load and can hit connection limits.