Exam target: dbt Analytics Engineering Certification Exam Current alignment used in this guide: dbt Core / dbt platform concepts around dbt 1.11, with legacy v1.7/v1.9 themes retained where still exam-relevant. Source pattern summary: 1,100 scenario-style practice rows, strongest concentration in developing/optimizing models, testing, debugging, pipeline troubleshooting, governance, state, and external dependencies.
1. Exam Overview
What the exam is testing
The dbt Analytics Engineering Certification validates whether you can use dbt in a production analytics workflow, not just whether you can remember commands. The exam expects you to reason through realistic project situations such as:
building a clean model DAG from raw sources to marts;
choosing the right materialization for performance and maintainability;
using ref() and source() correctly instead of hard-coded object names;
testing assumptions about source data and transformed models;
debugging model, YAML, SQL, dependency, and pipeline failures;
applying model governance features such as access, groups, versions, contracts, and grants;
using state-aware workflows for CI, slim CI, retries, and production-safe deployments;
managing packages, source freshness, exposures, and documentation so downstream users trust the data.
The real exam is scenario-heavy. A typical question gives you a project problem, then asks for the dbt-native fix. The best answer is usually the option that improves lineage, repeatability, maintainability, and production safety without over-engineering.
Current official exam structure to know
The official dbt Analytics Engineering Certification page currently lists these logistics and domains:
Item
Current detail
Duration
2 hours
Questions
65
Passing score
65%
Supported version
dbt 1.11
Expected background
SQL proficiency and practical dbt experience
Question style
Practical and scenario-based; expect multiple-choice and interactive-style reasoning
Older official guides and older question banks often mention dbt Core 1.7 and include a separate documentation domain. The current public exam page lists 7 domains and no separate documentation domain. Documentation still matters because it appears across sources, model properties, lineage, exposures, and governance scenarios.
How to think like the exam
Think like a production analytics engineer:
Use dbt abstractions before warehouse-specific shortcuts. Prefer ref(), source(), model configs, selectors, tests, contracts, and exposures over manual warehouse object names.
Preserve the DAG. Anything that hides lineage, bypasses dependencies, or relies on manually ordered SQL is suspicious.
Choose the simplest materialization that satisfies usage. Do not make everything incremental or table. Do not make a dashboard-facing mart ephemeral.
Test assumptions, not implementation trivia. Use tests where they express business rules or data quality guarantees.
Debug from dbt outward. Check logs, compiled SQL, YAML validity, dependencies, target/profile config, and then warehouse-specific SQL/errors.
Separate development from production. Use state, defer, clone, CI jobs, and PR review to avoid rebuilding or querying expensive production tables unnecessarily.
Govern public interfaces. If other teams depend on a model, use model access, versioning, contracts, docs, exposures, and deprecation instead of silently changing schemas.
How to use this course
Read Sections 1–3 once to orient yourself. Then study each domain in Section 4 and use Sections 5–10 as quick revision material. The course intentionally merges repeated CSV themes into decision frameworks so you can answer new scenarios rather than memorize answers.
The largest share of the analyzed bank is model development and optimization. This makes sense because almost every other topic depends on a correct mental model of dbt resources, DAG lineage, materializations, and configuration precedence.
High-yield cross-domain concepts:
ref() vs source();
model materializations: view, table, incremental, ephemeral;
tests: generic vs singular vs custom generic;
source freshness and source testing;
compiled SQL for debugging;
YAML indentation and resource properties;
dbt build vs dbt run + dbt test;
model contracts, versions, access, and groups;
state selection, defer, and CI efficiency;
packages and macro compatibility.
What matters most
If the question says...
The exam usually wants you to think about...
Hard-coded schema/table names inside models
Replace with ref() for models or source() for raw tables
Model builds in wrong order
Missing ref() dependencies
Raw table dependency is not documented/tested
Define a source in YAML and use source()
Large append-only table
Incremental model with correct is_incremental() logic
Small stable mapping file
Seed
Point-in-time history of slowly changing source data
debugging YAML, SQL, package, and pipeline failures.
Hands-on checklist:
Add a contract to a model and intentionally break it.
Add a v2 model and deprecate v1.
Make a protected/public model and test dependency behavior.
Run state selection against a previous manifest.
Debug a failing test from the failure output and compiled SQL.
Final review phase
In the final review, do not reread everything equally. Focus on scenario triggers:
If the model is downstream-facing, think governance.
If the model is expensive and append-only, think incremental.
If dependencies are invisible, think ref()/source().
If only changed work should run, think state/defer.
If source data timeliness is the issue, think freshness.
If a dashboard breaks, think exposure, model contract/versioning, docs, and lineage.
If the error is unclear, inspect logs and compiled SQL before changing dbt configs.
4. Core Concepts by Domain
Domain 1 — Developing and Optimizing dbt Models
Concepts
This is the highest-yield domain. You must know how dbt turns modular SQL files into a dependency graph and production data objects.
Key resource types:
Resource
What it represents
Common exam signal
Model
A SQL or Python transformation managed by dbt
Build clean staging/intermediate/mart layers
Source
Raw data object loaded outside dbt
Use when referring to raw tables
Seed
Static CSV version-controlled in the project
Small lookup/mapping/reference data
Snapshot
Point-in-time history of mutable source records
SCD-style history where source overwrites changes
Macro
Reusable Jinja logic
Repeated SQL pattern or generated logic
Test
Data assertion
Validate uniqueness, not null, relationships, accepted values, business rules
Exposure
Downstream asset such as BI dashboard, notebook, ML job
Show downstream dependency and ownership
Package
External reusable dbt project
Shared macros/models/tests from dbt Hub or Git
ref() and source()
ref() is for dbt models. source() is for raw objects loaded outside dbt.
Need
Use
Why
A mart depends on stg_orders
{{ ref('stg_orders') }}
Creates DAG dependency and environment-aware relation name
A staging model reads raw stripe.payments
{{ source('stripe', 'payments') }}
Documents raw dependency and supports source tests/freshness
A model directly queries analytics_prod.stg_orders
Replace with ref()
Hard-coding breaks lineage and environment portability
A model directly queries raw.shopify.orders
Replace with source()
Raw dependencies belong in source YAML
Exam trap: If the question says models build in the wrong order, do not choose a scheduler workaround. dbt order comes from ref() dependencies.
Materializations
Materialization
Use when
Avoid when
Exam trap
View
Logic should stay lightweight and always query fresh upstream data
Heavy repeated dashboard queries need fast performance
View does not store results; it can push cost to query time
Table
Model is expensive to compute and queried often
Data changes frequently and full rebuild is too expensive
Table rebuilds entire relation each run
Incremental
Large table, small new/changed subset per run
Large percentage updates each run or logic cannot isolate changes
Requires correct filter and unique key/strategy where needed
Ephemeral
Reusable intermediate logic not queried directly
Many downstream refs create repeated SQL; business users need to query it
Ephemeral is inlined as CTE, not created as a database object
Seed
Small static CSV controlled in git
Large dynamic data or frequently updated operational data
Seeds are not an ingestion system
Snapshot
Track historical changes in mutable source records
You only need latest state or immutable event data
Snapshot is not a materialization for performance
Incremental models
Use incremental when a model has many rows and only a small subset is added or changed each run.
Core reasoning:
is_incremental() gates logic that should only run on incremental runs.
The SQL must be valid for both full-refresh and incremental runs.
Use a reliable event/update timestamp or high-water mark.
Use unique_key and an incremental strategy when records can update.
Use --full-refresh when logic changes require rebuilding historical rows.
Schema changes may require on_schema_change handling or full refresh depending on the warehouse and change type.
Common bad answers:
“Incremental models are always rebuilt.” False.
“Incremental models are always best for large tables.” Not if most rows change each run.
“You never need is_incremental().” Usually false for selective processing.
“Use ephemeral to make a large dashboard model faster.” Usually wrong; ephemeral can duplicate heavy SQL.
Sources
A source maps to a raw data location, commonly database + schema, with tables underneath. Use sources to centralize raw object naming and document external dependencies.
Good source YAML includes:
source name;
database/schema where needed;
tables;
source and column descriptions;
tests on raw data assumptions;
freshness where timeliness matters;
loaded timestamp field for freshness checks.
Exam trap: If multiple raw tables are in the same database/schema, they are usually one source with multiple tables, not multiple sources.
Modularity and DRY SQL
Good dbt modeling decomposes SQL into layers:
Layer
Typical purpose
Typical materialization
Staging
Clean, rename, cast, standardize one source
View, sometimes ephemeral/table
Intermediate
Reusable transformations and joins
Ephemeral, view, table depending on cost
Mart
Business-facing facts/dimensions
Table/incremental for performance
Use macros for reusable logic patterns, not for hiding business-critical model lineage. Use models when you need DAG visibility and testable transformation steps.
Jinja, variables, and environment config
Feature
Use case
Trap
var()
Project variables provided in dbt_project.yml or CLI
Do not store secrets in vars
env_var()
Environment-specific values and secrets
Must be available in runtime environment
target
Branch logic by target/profile
Avoid excessive target-specific model logic that makes behavior hard to test
config()
Model-level configs in SQL
Remember config precedence
dbt_project.yml
Default project/folder configs
Bad indentation or wrong resource path breaks expectations
Python models
Python models are used for transformations easier in Python than SQL, such as advanced data science-style transformations. Exam reasoning remains dbt-native:
They are still models in the DAG.
They can use dbt.ref() and dbt.source().
They are not a replacement for simple SQL transformations.
Support depends on the adapter/platform.
Git workflow
Expected git skills:
create feature branches;
commit changes;
pull from main/head branch to stay updated;
resolve conflicts;
open pull requests;
use CI before merging.
Exam trap: If the question says your branch is behind main, the correct general action is to pull/reconcile with the head branch, not manually copy files or merge straight into production.
Patterns
Raw table reference → define source + use source().
Model-to-model dependency → use ref().
Repeated business logic → refactor into staging/intermediate models or macros.
Expensive dashboard model → table or incremental.
Append-only high-volume data → incremental.
Static mapping table → seed.
Source overwrites values but history needed → snapshot.
Direct warehouse object permissions required → grants.
Traps
Choosing a materialization only because it is “faster” without considering freshness, cost, and query pattern.
Using hard-coded schema names in dbt models.
Making every model a table, which increases rebuild time/storage.
Making every staging model ephemeral, which can duplicate heavy SQL downstream.
Treating seeds as ingestion for operational data.
Using macros where a model would provide better lineage and testing.
Forgetting that a table model fully rebuilds by default.
Domain 2 — Managing dbt Models Governance
Concepts
Governance is about protecting model consumers. The exam often frames this as: “A downstream team relies on a model and the analytics team needs to change it safely.”
Key features:
Feature
Purpose
Typical scenario
Model access
Controls whether models can be referenced across groups/projects
Keep internal models private while exposing stable interfaces
Groups
Assign ownership to resources
Domain/team ownership and access control
Model versions
Evolve a public model without breaking consumers
Add/change columns while keeping old version available
Deprecation
Signal old version should be migrated away from
v1 still works but users should move to v2
Contracts
Enforce model shape and column data types
Prevent breaking schema changes
Grants
Apply warehouse permissions
Ensure analysts or BI tools can query produced relations
Documentation
Explain ownership, meaning, usage, and downstream impact
Make models discoverable and trustworthy
Model access
Typical access levels:
Access
Use when
Exam implication
Private
Internal implementation detail
Should not be depended on by other groups
Protected
Reusable within defined boundary/project/group pattern
Compiled SQL is critical because dbt model SQL contains Jinja, macros, and adapter-specific rendering. If a warehouse error references SQL that does not look like your model file, inspect compiled SQL.
Use compiled SQL to:
see expanded ref() and source() relation names;
see macro output;
verify is_incremental() branches;
diagnose SQL syntax errors after Jinja rendering;
distinguish dbt compilation problems from warehouse execution problems.
YAML debugging
YAML traps:
wrong indentation;
putting tests under the wrong level;
misspelling columns, description, data_tests/tests depending on version style;
placing model properties under the wrong model name;
using tabs or invalid quoting;
confusing source table properties with model properties.
SQL vs dbt issue
Question clue
More likely
Error mentions YAML parser, undefined macro, ref not found
dbt project/config issue
Error mentions warehouse SQL syntax, invalid identifier, function not found
SQL/warehouse issue after compilation
Error only appears in production target
environment/target/schema/permissions issue
Error appears after package upgrade
package compatibility or macro behavior
Error appears only on incremental runs
incremental branch, unique key, schema change, or high-water logic
Fix and test before merge
Good workflow:
Reproduce locally or in dev environment.
Inspect logs/compiled SQL/failing rows.
Apply minimal fix.
Run targeted model and downstream tests.
Open PR and let CI validate before merging.
Patterns
Failing model due to generated SQL → inspect compiled SQL.
source() relation not found → verify source YAML, database/schema/table, target config, and permissions.
YAML error after adding tests → fix indentation and property nesting.
Test failure after new source load → inspect failing rows; maybe source issue, not dbt code.
Error only in CI → compare CI target/profile/env vars/packages to local.
Traps
Editing compiled SQL instead of model SQL.
Assuming all SQL-looking errors are pure SQL; Jinja may have generated bad SQL.
Running all models when a targeted selector would prove the fix faster.
Ignoring package or adapter version compatibility.
Treating failing tests as “dbt broken” instead of data quality signal.
Domain 4 — Troubleshooting and Optimizing dbt Pipelines
Concepts
This domain tests production operation: jobs, DAG failures, retries, CI, clone, and orchestration boundaries.
Key idea: dbt transforms data inside the warehouse. External orchestrators schedule and coordinate broader workflows, but dbt should still own model dependencies and transformation correctness.
dbt build vs dbt run / dbt test
Command
What it does
Use when
dbt run
Builds models
You only want transformations
dbt test
Runs tests
You want to validate already-built resources
dbt build
Runs seeds, models, snapshots, tests in DAG-aware order
Compiles project without executing transformations
Debug generated SQL/Jinja
dbt debug
Checks connection/profile/project validity
Credential/profile/setup issues
dbt retry
Retries failed nodes from previous invocation when supported
Resume after transient/partial failure
DAG failure management
If an upstream model fails, downstream dependent models should not blindly run. The exam wants you to respect the DAG rather than bypass it.
Good actions:
fix the upstream failure first;
use selectors to rerun affected nodes;
use + operators to include parents/children as needed;
use dbt build for DAG-aware build/test ordering;
use retries or result selectors to avoid unnecessary rebuilds after transient failures.
Bad actions:
manually build downstream tables out of order;
remove tests to make the job pass;
hard-code production relations into dev models;
ignore failing upstream source freshness.
Clone
dbt clone can create environment copies by cloning relations where supported by the data platform. It is useful for efficient environment setup, especially when you do not want to rebuild all expensive models.
Use clone when:
creating a dev/CI environment from existing production objects;
supported by the adapter/data platform;
you need fast setup without full transformation cost.
Do not use clone as a substitute for fixing model logic or tests.
CI and deployment
Strong CI pattern:
feature branch opens PR;
CI runs only changed models and relevant downstream dependencies;
CI defers unchanged upstream references to production artifacts;
tests validate the changed surface area;
merge only after passing checks.
This reduces cost and catches issues without rebuilding the entire warehouse.
Complex business rule not easily expressed as generic test
Custom generic test
Reusable test macro
Repeated organization-specific assertion
Source test
Test applied to raw source/table/column
Validate assumptions at the ingestion boundary
Built-in generic tests
Test
Validates
Example scenario
not_null
Column has no nulls
Primary key cannot be null
unique
Values are unique
Order ID should appear once
relationships
Foreign key values exist in parent model
orders.customer_id exists in customers.customer_id
accepted_values
Column values are in allowed set
status must be placed, shipped, returned
Test configuration
Important configs:
Config
Why it matters
severity
Warn vs error behavior
where
Restrict tested rows, often for recent/incremental data
limit
Limit returned failures, not necessarily test scope semantics
store_failures
Persist failing rows for inspection
error_if / warn_if
Threshold-based pass/warn/error
tags
Select tests for workflows
Exam trap: store_failures helps inspect failures; it does not prevent scanning the table or reuse old test results as a pass.
Testing incremental models
For large incremental models, do not always test all historical rows if the scenario requires efficiency. Options include:
use a where condition to test recent partitions/new rows;
use contracts/warehouse constraints where supported for column shape/type/not-null-like enforcement;
design incremental logic so new rows are validated during load;
run full validation periodically if required.
Testing sources
Use source tests for assumptions about raw data:
IDs not null;
source natural key uniqueness where expected;
accepted values for raw status codes;
relationships between raw source tables where useful;
freshness checks for timeliness.
Freshness is not the same as a generic test. Freshness measures whether data has arrived recently based on a timestamp.
Singular vs generic tests
Situation
Better choice
Column should never be null
Generic not_null
Column should be unique
Generic unique
Status must be one of a list
Generic accepted_values
Foreign key must exist in parent
Generic relationships
Revenue should not be negative after refunds logic
Singular or custom generic depending reuse
Same business rule applies to many models
Custom generic test
Patterns
Need failing rows saved for debugging → store_failures.
Need test only recent incremental data → where config.
Need failure threshold → error_if / warn_if.
Need reusable organization rule → custom generic test.
Need one-off complex SQL assertion → singular test.
Need raw table timeliness → source freshness.
Traps
Over-testing every column without business value.
Confusing relationships with uniqueness.
Applying unique to a column that is only unique in combination with another column.
Using accepted_values when values change often and should come from a reference model.
Treating warnings as passing quality gates in production when the business requires failure.
Not testing sources before trusting staging models.
Domain 6 — Leveraging the dbt State
Concepts
State lets dbt compare the current project to artifacts from a previous run, usually production. This powers slim CI and targeted rebuilds.
Key artifacts:
Artifact
Use
manifest.json
Project graph, resources, configs, dependencies; used for state comparison
run_results.json
Results of prior invocations; used for result selectors/retry-like workflows
compiled/run artifacts
Debugging and inspection
State selectors
Common patterns:
Selector
Meaning
state:modified
Resources changed compared with prior state
state:new
Resources new compared with prior state
state:old
Resources removed/changed relative to old state context
state:modified+
Modified resources and downstream children
+state:modified
Parents of modified resources and modified resources
state:modified+ --defer
Common slim CI pattern: build changed nodes/downstream while deferring unchanged refs to prod
Exact selector behavior can vary by context and dbt version, but the reasoning is stable: use state to avoid unnecessary full rebuilds while still validating impacted areas.
Defer
--defer tells dbt to resolve references to unselected nodes using a previous state/production environment when possible. It is useful in CI because changed models can reference stable production versions of unchanged upstream models.
Use defer when:
CI should not rebuild the entire upstream graph;
production artifacts are available;
unchanged parent models are trusted;
you still want correct references and realistic dependencies.
Avoid defer when:
you need to validate changes to upstream parents too;
production state artifacts are stale or missing;
environment parity is broken.
Result selectors and retry
Result selectors use prior run results to select nodes by outcome, such as failed or errored nodes. dbt retry helps rerun failed work from the previous invocation in supported contexts.
Use result-aware workflows when:
a production job failed on a subset of nodes;
transient warehouse issue was fixed;
you do not want to rerun everything.
Patterns
PR changed one staging model → run state:modified+ to include impacted downstream models.
CI should not rebuild unchanged parents → use --defer to production state.
Previous production run had failed nodes → retry failed subset after fixing transient issue.
Need compare current branch with production → use prior manifest as state.
Traps
Using state selectors without providing a valid state artifact.
Assuming state:modified automatically includes downstream models; use + when needed.
Deferring to production when the changed model depends on changed upstream logic that is not selected.
Treating state as data freshness; state compares project artifacts, not source arrival times.
Confusing source freshness with state freshness.
Domain 7 — Implementing and Maintaining External Dependencies
Concepts
External dependencies include dbt packages, source freshness, and exposures. These features make a project more maintainable and connected to the broader data ecosystem.
Packages
Packages provide reusable macros, models, and tests. They are declared in package configuration and installed with dbt deps.
Use packages when:
a well-maintained package solves a common need;
your team wants standardized macros/tests;
compatibility with your adapter and dbt version is confirmed.
Package risks:
version incompatibility after dbt upgrade;
adapter-specific macro behavior;
hidden SQL complexity;
unmaintained dependencies;
namespace conflicts;
changing package behavior after upgrade.
Best practice:
pin versions intentionally;
review package changelogs before upgrades;
test package macros in CI;
do not blindly trust package output for critical logic;
run dbt deps when dependencies change or are missing.
Source freshness
Source freshness checks whether raw source data is recent enough. It is based on a timestamp column and configured thresholds.
Use source freshness when:
ingestion can be late;
downstream dashboards depend on timely data;
you need alerting/gating before transformations;
SLAs exist for source updates.
Freshness usually includes:
loaded_at_field;
warning threshold;
error threshold.
Exam trap: Freshness is about recency of loaded source data, not whether transformed model columns are unique/not null.
Exposures
Exposures document downstream dependencies such as dashboards, notebooks, ML jobs, or applications. They connect produced data models to business assets.
Use exposures when:
a BI dashboard depends on dbt models;
owners need to know impact before changing a model;
lineage should show downstream consumers;
failures should be assessed by business impact.
Exposures are documentation/lineage resources; they do not grant permissions or execute dashboards.
Patterns
Missing macro from package → run/check dbt deps, package name/version/namespace.
Package works locally but fails in CI → check dependency installation and version parity.
Package macro works on Snowflake but not BigQuery → check adapter compatibility/dispatch.
Raw data arrives late → configure source freshness.
Dashboard owner needs impact awareness → add exposure with owner and dependency refs.
Traps
Treating dbt deps as a model build command.
Upgrading packages without checking breaking changes.
Using source freshness to test transformed model logic.
Assuming exposures enforce access control.
Not documenting package-generated models/macros because they are “external.”
Documentation Skills Across Domains
Although the current public exam page no longer lists documentation as its own domain, documentation remains a repeated source-bank theme and a practical exam skill.
Know how to document:
model descriptions;
column descriptions;
source descriptions;
source table/column metadata;
exposures;
owner information;
docs blocks where reusable long-form docs help;
lineage through ref(), source(), and exposures.
Commands:
dbt docs generate builds documentation artifacts;
dbt docs serve is commonly used locally to view docs;
dbt Cloud has its own docs hosting/viewing workflows.
Exam trap: Documentation does not create dependencies by itself. ref() and source() create lineage edges; descriptions explain them.
5. Service Selection Guide
In dbt, “service selection” mostly means choosing the right dbt feature/resource/command for the scenario.
Resource selection
Scenario
Choose
Not this
Why
Raw table loaded by Fivetran/Airbyte/custom ingestion
Source
Model ref
Raw objects should be declared as sources
Transform source into clean business table
Model
Seed
Models are transformations
Small manually maintained mapping CSV
Seed
Source
Seed is version-controlled static data
Track historical changes in mutable source
Snapshot
Incremental model only
Snapshot records change history
Business dashboard consumes dataset
Mart model + exposure
Ephemeral model
Needs stable queryable relation and downstream lineage
Reused SQL snippet across many models
Macro
Copy-paste SQL
Macro supports DRY logic
Reusable intermediate transformation with lineage/tests
dbt Analytics Engineering Certification Study Guide PDF: https://www.getdbt.com/dbt-assets/certifications/dbt-certificate-study-guide
The official certification page currently lists dbt 1.11 support and 7 exam coverage areas. The older PDF study guide still contains useful v1.7-style topic details and sample question styles; this course prioritizes the current domain structure while preserving still-relevant concepts from the older outline and the analyzed question bank.
lock_open
Unlock the full course
All 20 modules with detailed explanations, code examples, and exam tips.
