dbt Analytics Engineering Certification Exam — Questions and Answers

Question 1: Which Redshift config influences how table rows are distributed across nodes to reduce data shuffling in joins?

require_partition_filter
dist (Correct answer)
cluster_by
partition_by

Correct answer: dist

The dist config sets the distribution style or key for Redshift tables, minimizing data movement during joins.

Question 2: What environment variable or flag tells dbt where to find the baseline manifest for state comparison?

--state or DBT_STATE (Correct answer)
--baseline-dir
DBT_MANIFEST
--manifest-path

Correct answer: --state or DBT_STATE

The `--state` flag (or `DBT_STATE` env var) specifies the directory containing the `manifest.json` to use as the comparison baseline.

Question 3: In packages.yml, how do you pin a git-hosted package to a specific tag or commit?

revision: (Correct answer)
branch_lock:
version:
pin:

Correct answer: revision:

The revision key accepts a branch, tag, or commit SHA for git packages.

Question 4: Which directory stores dbt snapshot files by default?

data/snapshots/
seeds/
snapshots/ (Correct answer)
models/snapshots/

Correct answer: snapshots/

Snapshot `.sql` files are placed in the top-level `snapshots/` directory of a dbt project.

Question 5: What information does `run_results.json` contain after a dbt invocation?

The connection credentials used during the run
A list of all tests that passed during the most recent test run
The model selection criteria used for the current run
Execution metadata such as model status, timing, and row counts from the last dbt invocation (Correct answer)

Correct answer: Execution metadata such as model status, timing, and row counts from the last dbt invocation

`run_results.json` is generated after every dbt invocation and records execution status, timing, and results for each node that was processed.

Question 6: Which file defines top-level project configuration such as model paths, project name, and version in dbt?

dbt_project.yml (Correct answer)
schema.yml
packages.yml
profiles.yml

Correct answer: dbt_project.yml

`dbt_project.yml` is the required project-level configuration file that defines project name, version, model directories, and global configurations.

Question 7: Which command shows the compiled SQL without executing it against the warehouse as a model build?

dbt compile (Correct answer)
dbt debug
dbt docs serve
dbt clean

Correct answer: dbt compile

dbt compile renders Jinja into SQL in target/compiled without materializing.

Question 8: Which practice improves incremental model performance by avoiding unnecessary comparisons?

Use a unique_key in incremental config (Correct answer)
Create redundant CTEs
Run all models in full-refresh mode
Disable WHERE filters

Correct answer: Use a unique_key in incremental config

For incremental models, defining a `unique_key` in the model's configuration significantly improves performance and correctness. When a `unique_key` is specified, dbt can efficiently identify and update or delete existing records based on this key, rather than performing full table scans or complex comparisons. This ensures that only the necessary changes are applied, making incremental runs much faster and more reliable.

Question 9: Which YAML key defines how old source data can be before dbt warns about staleness?

staleness
freshness (Correct answer)
loaded_at_threshold
max_age

Correct answer: freshness

The `freshness:` block under a source (or table) defines `warn_after` and `error_after` thresholds for data staleness.

Question 10: What is the recommended way to handle secrets (e.g., database passwords) in a dbt CI pipeline?

Hardcode them in dbt_project.yml
Store them in profiles.yml committed to the repo
Store them in a seed CSV
Use environment variables injected by the CI platform (Correct answer)

Correct answer: Use environment variables injected by the CI platform

Secrets should be passed as environment variables from the CI platform (GitHub Actions, GitLab CI, etc.) and referenced in profiles.yml using `env_var()`.

Question 11: Why should dbt users commit changes to version control regularly?

To speed up tests
To track project history and changes (Correct answer)
To reduce compile time
To encrypt SQL files

Correct answer: To track project history and changes

Regularly committing changes to version control, specifically Git, is crucial for maintaining a detailed history of your dbt project. Each commit acts as a snapshot, allowing you to track who made what changes, when, and why. This history is invaluable for debugging, reverting to previous states, understanding the evolution of your data models, and facilitating collaborative development.

Question 12: Which column added by dbt snapshots holds a hash of all snapshot-tracked columns for change detection?

dbt_row_hash
dbt_scd_id (Correct answer)
dbt_unique_key
dbt_checksum

Correct answer: dbt_scd_id

`dbt_scd_id` is a hash-based unique identifier generated by dbt for each snapshot row, combining the unique key and `dbt_updated_at` to ensure row-level uniqueness.

Question 13: Which file would you use to define a reusable SQL snippet called via {{ my_macro() }}?

seeds/ CSV
analyses/ file
A .sql file in the macros directory (Correct answer)
A .yml file in models

Correct answer: A .sql file in the macros directory

Macros live in .sql files inside the macros directory, wrapped in {% macro %} blocks.

Question 14: What does the node selection syntax `dbt run --select +my_model` do?

Runs all models except my_model
Runs my_model and all its upstream parent models (Correct answer)
Runs only my_model, excluding any dependencies
Runs my_model and all models downstream of it

Correct answer: Runs my_model and all its upstream parent models

The `+` prefix in the selector syntax means 'include all upstream parents,' so `+my_model` runs my_model and every model it depends on.

Question 15: Which file defines the target warehouse connection details for a dbt Core project?

sources.yml
profiles.yml (Correct answer)
connections.yml
dbt_project.yml

Correct answer: profiles.yml

`profiles.yml` (stored in `~/.dbt/` by default) holds named targets with adapter type, credentials, and warehouse-specific settings.

Question 16: What built-in dbt variable returns the current target name (e.g., 'dev' or 'prod')?

{{ target.name }} (Correct answer)
{{ env_var('DBT_TARGET') }}
{{ run_started_at }}
{{ this.schema }}

Correct answer: {{ target.name }}

`{{ target.name }}` returns the name of the active target as defined in profiles.yml, commonly used to branch logic between environments.

Question 17: What is a dbt adapter?

A plugin that allows dbt to connect to and compile SQL for a specific data platform (Correct answer)
A Jinja macro that adapts SQL syntax between different models
A configuration block that adapts profile settings to different environments
A dbt package that provides pre-built transformations for specific industries

Correct answer: A plugin that allows dbt to connect to and compile SQL for a specific data platform

A dbt adapter (e.g., dbt-bigquery, dbt-snowflake) is a plugin that enables dbt to translate its generic operations into platform-specific SQL and API calls.

Question 18: Which SQL technique improves performance by reducing the size of result sets before joins?

Remove indexes
Avoid WHERE clauses
Filter rows before joins (Correct answer)
Use SELECT * in subqueries

Correct answer: Filter rows before joins

Filtering rows before performing joins is a critical SQL technique for improving query performance. By reducing the number of rows in each table before the join operation, you minimize the amount of data the database needs to process and compare. This leads to smaller intermediate result sets, faster join execution, and overall more efficient queries, especially with large datasets.

Question 19: What is the main purpose of the is_incremental() macro in an incremental model?

To validate the unique_key
To return true only when the model already exists as a table and is not run with --full-refresh (Correct answer)
To switch the materialization to view
To force a full table rebuild

Correct answer: To return true only when the model already exists as a table and is not run with --full-refresh

is_incremental() lets you filter source rows only on subsequent runs, when the target table exists and no full refresh is requested.

Question 20: A model contract is enforced with contract: {enforced: true}. What must be defined for the model?

Every column's name and data_type in its YAML (Correct answer)
A unique_key and incremental strategy
A post-hook granting access
A snapshot strategy

Correct answer: Every column's name and data_type in its YAML

Enforced contracts require column names and data types, and dbt validates them before building.

Question 21: What does `dbt build` do compared with `dbt run`?

Runs models, seeds, snapshots, and tests in DAG order (Correct answer)
Only generates docs
Only runs tests
Only compiles

Correct answer: Runs models, seeds, snapshots, and tests in DAG order

build executes seeds, models, snapshots, and tests together, in dependency order.

Question 22: Why might a view-materialized model chain become slow?

Views cannot be tested
Views require full refresh
Views block threads
Each downstream query re-executes all upstream view logic at query time (Correct answer)

Correct answer: Each downstream query re-executes all upstream view logic at query time

Views store only a query, so stacked views are recomputed on every read, which can be expensive.

Question 23: What is the purpose of the `target` key in a dbt profile?

Sets the maximum number of threads dbt can use
Points to the schema.yml file for validation
Specifies the file path where compiled SQL is saved
Defines the default environment (e.g., dev or prod) to use when running dbt (Correct answer)

Correct answer: Defines the default environment (e.g., dev or prod) to use when running dbt

The `target` key in `profiles.yml` specifies which named environment configuration (such as dev or prod) dbt uses by default when executing commands.

Question 24: What does the `--full-refresh` flag do when running incremental models?

Clears the dbt cache and recompiles all Jinja templates
Resets the incremental model's unique key column to null
Forces dbt to drop and recreate the incremental model as a full table (Correct answer)
Refreshes only the most recent partition of the incremental model

Correct answer: Forces dbt to drop and recreate the incremental model as a full table

The `--full-refresh` flag causes dbt to drop the existing incremental table and rebuild it from scratch, identical to running it as a table materialization.

Question 25: Where does a dbt Cloud CI job build models for a pull request?

Directly into the production schema
No models are built
A temporary schema unique to that PR (Correct answer)
The developer's personal schema

Correct answer: A temporary schema unique to that PR

CI jobs create a temporary schema named for the PR so production is untouched.

Question 26: Which artifact produced by `dbt docs generate` holds column data types and table statistics gathered from the warehouse?

run_results.json
manifest.json
sources.json
catalog.json (Correct answer)

Correct answer: catalog.json

catalog.json is built by introspecting the warehouse, while manifest.json describes the project's parsed resources.

Question 27: Which dbt command is used to preview generated documentation in a local browser?

dbt seed
dbt run
dbt docs serve (Correct answer)
dbt debug

Correct answer: dbt docs serve

After generating the documentation files with `dbt docs generate`, the `dbt docs serve` command launches a local web server. This server hosts the generated documentation website, allowing you to view and interact with your project's documentation in a web browser. It provides an accessible way to explore your data models, lineage, and tests without needing to deploy the documentation externally.

Question 28: What is the main advantage of using CTEs (Common Table Expressions) in dbt models?

Reduces project size
Improves query readability and modularity (Correct answer)
Prevents model execution
Increases data redundancy

Correct answer: Improves query readability and modularity

The main advantage of using Common Table Expressions (CTEs) in dbt models is that they significantly improve query readability and modularity. CTEs allow you to break down complex SQL queries into smaller, named, logical steps, making the code easier to understand, debug, and maintain. This enhances collaboration and reduces the likelihood of errors in intricate transformations.

Question 29: What dbt command runs all tests defined in your project?

dbt compile
dbt test (Correct answer)
dbt check
dbt run

Correct answer: dbt test

`dbt test` executes all schema and data tests and reports pass/fail results.

Question 30: What is the default port used by `dbt docs serve`?

8888
3000
8080 (Correct answer)
5000

Correct answer: 8080

dbt docs serve hosts the documentation site on port 8080 unless --port is specified.

Question 31: What dbt Cloud feature lets a CI job use prod table results for unmodified models instead of rebuilding them?

Deferral (Correct answer)
State comparison
Run bypass
Model caching

Correct answer: Deferral

Deferral (`--defer`) tells dbt to use a different environment's tables for refs that are not being rebuilt in the current run.

Question 32: In a dbt Cloud job schedule, what format is used to define cron-based run timing?

ISO 8601 duration strings
Unix epoch intervals
Natural language strings like 'every hour'
Standard 5-field cron expressions (Correct answer)

Correct answer: Standard 5-field cron expressions

dbt Cloud accepts standard cron expressions (e.g., `0 6 * * *`) to schedule jobs at specific times.

Question 33: Which directory in a dbt project is typically used to store static CSV data for loading into the warehouse?

logs/
snapshots/
macros/
data/ (Correct answer)

Correct answer: data/

The `data/` directory in a dbt project is specifically designated for storing static CSV files, often referred to as 'seeds'. These seed files can be loaded directly into your data warehouse as tables using the `dbt seed` command. This is useful for managing small, static datasets like country codes, lookup tables, or configuration data within your dbt project.

Question 34: What is the purpose of writing descriptions in dbt models and columns?

To clean old logs
To format code
To speed up query performance
To describe data models for users and developers (Correct answer)

Correct answer: To describe data models for users and developers

Writing clear descriptions for dbt models and columns is essential for data governance and collaboration. These descriptions provide context and meaning to the data, making it easier for both technical and non-technical users to understand what each model represents and what data each column contains. This significantly improves data discoverability, trust, and usability across the organization.

Question 35: In GitHub Actions, which `dbt run` flag would you add to run only models changed in a PR versus the production manifest?

--select state:modified+ --defer --state ./prod-manifest (Correct answer)
--modified-only
--select changed:true
--select pr:changed

Correct answer: --select state:modified+ --defer --state ./prod-manifest

Combining `state:modified+` (changed model plus its downstream dependents), `--defer`, and `--state` pointing to the production manifest implements Slim CI.

Question 36: Which dbt selector syntax runs a model and all models downstream of it?

*my_model
+my_model
my_model*
my_model+ (Correct answer)

Correct answer: my_model+

The trailing `+` syntax (`my_model+`) selects the specified model and all models downstream of it in the DAG.

Question 37: Which dbt command installs packages listed in packages.yml?

dbt get
dbt deps (Correct answer)
dbt packages install
dbt install

Correct answer: dbt deps

`dbt deps` downloads all packages listed in packages.yml into the `dbt_packages/` directory.

Question 38: What type of dbt materialization creates a new physical table in the warehouse each time it runs?

Ephemeral
Table (Correct answer)
Incremental
View

Correct answer: Table

The 'table' materialization in dbt creates a new physical table in your data warehouse every time the model is run. This means that the entire table is rebuilt from scratch with the latest data, replacing any existing version. While simple, it can be resource-intensive for very large datasets as it involves a full refresh each time.

Question 39: Which Jinja statement would you use to set a local variable inside a dbt model?

{% let my_var = 'value' %}
{% assign my_var = 'value' %}
{% set my_var = 'value' %} (Correct answer)
{% var my_var = 'value' %}

Correct answer: {% set my_var = 'value' %}

`{% set variable_name = value %}` is the Jinja statement for assigning a value to a local variable within a template.

Question 40: A `relationships` test in dbt verifies what condition?

Column values are unique
Column values are not null
Column values exist in a referenced model's column (Correct answer)
Column data types match

Correct answer: Column values exist in a referenced model's column

The `relationships` test is a referential-integrity check ensuring every foreign-key value exists in the parent table.

Question 41: Which file defines the packages and dependencies required by a dbt project?

profiles.yml
packages.yml (Correct answer)
dbt_project.yml
models.sql

Correct answer: packages.yml

The `packages.yml` file defines the external dbt packages and dependencies required by your dbt project. It specifies which packages to install and from where (e.g., dbt Hub or a Git repository). This allows you to leverage pre-built transformations and utilities developed by the dbt community, accelerating development and promoting best practices.

Question 42: When using dbt Core, which directory stores compiled SQL and run artifacts by default?

logs/
target/ (Correct answer)
artifacts/
compiled/

Correct answer: target/

dbt writes compiled SQL, run results, and other artifacts to the `target/` directory by default, which is configurable in `dbt_project.yml`.

Question 43: What dbt command checks whether source tables are fresh based on a loaded_at_field?

dbt source freshness (Correct answer)
dbt run --freshness
dbt test --source-freshness
dbt check sources

Correct answer: dbt source freshness

`dbt source freshness` queries the `loaded_at_field` of each source and compares it against the defined freshness thresholds.

Question 44: Which command verifies that your dbt project YAML and SQL are syntactically valid without connecting to a warehouse?

dbt check
dbt parse (Correct answer)
dbt validate
dbt lint

Correct answer: dbt parse

`dbt parse` reads all project files and builds the internal graph, catching YAML syntax errors and reference issues without running queries.

Question 45: What is the primary purpose of the `ref()` function in dbt?

To reference external database tables not managed by dbt
To define a variable that can be reused across models
To reference another dbt model and build the DAG dependency graph (Correct answer)
To call a dbt macro within a SQL model

Correct answer: To reference another dbt model and build the DAG dependency graph

The `ref()` function references another dbt model and allows dbt to infer dependencies, building the correct DAG execution order automatically.

Question 46: What does the `var()` function do in a dbt model?

Retrieves an environment variable
Creates a SQL variable
Declares a Jinja variable
Reads a project variable defined in dbt_project.yml or passed via --vars (Correct answer)

Correct answer: Reads a project variable defined in dbt_project.yml or passed via --vars

`{{ var('name', default) }}` reads variables from the `vars:` section of dbt_project.yml or those overridden at runtime with `--vars`.

Question 47: What is the performance benefit of selecting only required columns instead of SELECT * in a columnar warehouse?

Faster Jinja parsing
Reduced bytes scanned and lower cost (Correct answer)
More concurrent threads
Larger tables

Correct answer: Reduced bytes scanned and lower cost

Columnar stores read only referenced columns, so explicit selection lowers I/O and cost.

Question 48: What is the default materialization type for dbt models when none is explicitly configured?

incremental
view (Correct answer)
ephemeral
table

Correct answer: view

By default, dbt materializes models as views unless overridden in `dbt_project.yml` or a model's config block.

Question 49: What does `dbt docs generate` produce?

A test results report in HTML format
A catalog.json and manifest.json used to serve the documentation site (Correct answer)
A compiled SQL file for each model in the project
A YAML schema file based on existing database tables

Correct answer: A catalog.json and manifest.json used to serve the documentation site

`dbt docs generate` creates `catalog.json` (column metadata from the warehouse) and `manifest.json` (project graph), which are used by `dbt docs serve` to render the documentation site.

Question 50: In dbt Cloud, what does a state-aware approach to jobs primarily avoid?

Documentation generation
Authentication
Version control
Rebuilding models whose upstream data and code have not changed (Correct answer)

Correct answer: Rebuilding models whose upstream data and code have not changed

State-aware orchestration skips nodes with no changes in code or upstream data, saving warehouse compute.

Question 51: What does the selector `state:modified+` combined with `--state` select?

Only upstream parents of changed models
All models in the project
Modified nodes and everything downstream of them (Correct answer)
Only models that failed last run

Correct answer: Modified nodes and everything downstream of them

state:modified+ compares against a prior manifest and includes changed nodes plus their descendants, enabling Slim CI.

Question 52: Which version control system is most commonly used with dbt projects?

FTP
Git (Correct answer)
Subversion
MongoDB

Correct answer: Git

Git is the most widely adopted version control system for software development, and it is also the standard for dbt projects. It allows teams to track changes to code, collaborate effectively, and manage different versions of their dbt models, tests, and configurations. Its distributed nature and robust branching/merging capabilities make it ideal for managing complex data transformation workflows.

Question 53: Which property declares multiple versions of a model in YAML?

versions: (Correct answer)
revisions:
releases:
variants:

Correct answer: versions:

The versions property, with latest_version and optional defined_in, supports model versioning.

Question 54: Why does dbt recommend selecting from sources only in staging models?

Sources cannot be used elsewhere by design
It centralizes source references so downstream models use ref() on cleaned data (Correct answer)
Tests only run on staging
Sources are slower in marts

Correct answer: It centralizes source references so downstream models use ref() on cleaned data

Keeping source() calls in staging gives one place to adjust when raw tables change.

Question 55: Which practice is essential when collaborating on dbt projects using Git?

Avoid committing minor changes
Push directly to main
Use pull requests for code review and quality (Correct answer)
Only update on weekends

Correct answer: Use pull requests for code review and quality

When collaborating on dbt projects using Git, using pull requests (or merge requests) is an essential practice for maintaining code quality and ensuring proper review. Pull requests facilitate a structured review process where team members can examine proposed changes, provide feedback, and ensure that new code adheres to standards before being merged into the main branch. This prevents errors and promotes knowledge sharing.

Question 56: Which selector is used in dbt to run only models that differ from a previous state manifest?

--select state:modified (Correct answer)
--select changed
--select diff:new
--select modified:true

Correct answer: --select state:modified

`state:modified` compares the project against a baseline manifest.json to identify nodes with code or config changes.

Question 57: Which materialization type is used in dbt to improve performance by only updating new or changed records?

Incremental (Correct answer)
View
Table
Seed

Correct answer: Incremental

The 'incremental' materialization type in dbt is designed to improve performance by only processing new or changed records since the last run. Instead of rebuilding the entire table, dbt intelligently inserts or updates only the relevant rows. This significantly reduces query times and computational resources, making it ideal for large, frequently updated datasets.

Question 58: Which command executes all dbt models in a project?

dbt deploy
dbt build
dbt execute
dbt run (Correct answer)

Correct answer: dbt run

`dbt run` executes all models in the project, materializing them in the target data warehouse according to their configured materialization type.

Question 59: Which is a typical drawback of using the view materialization for a heavily queried, complex model?

It reruns the underlying query every time it is queried (Correct answer)
It requires a unique_key
It cannot be referenced
It stores duplicate data

Correct answer: It reruns the underlying query every time it is queried

Views store only the query, so each downstream query pays the compute cost.

Question 60: What does the `vars` block in `dbt_project.yml` enable?

Declaring Jinja macros reusable across all models
Defining project-wide variables accessible via the `var()` function (Correct answer)
Listing all test types available for schema validation
Specifying environment-specific database connection settings

Correct answer: Defining project-wide variables accessible via the `var()` function

The `vars` block defines project-level variables accessible via `var('variable_name')` in models and macros, and can be overridden at runtime with `--vars`.

Question 61: How can you document a macro and its arguments?

Macros cannot be documented
Add a comment inside the macro only
Use a seed file
Add a macros: entry in a YAML file with description and arguments (Correct answer)

Correct answer: Add a macros: entry in a YAML file with description and arguments

Macro properties files support description and arguments entries displayed in the docs.

Question 62: In dbt, what does a DAG (Directed Acyclic Graph) represent?

The branching strategy used for version control
The dependency relationships and execution order between models (Correct answer)
The order of test execution across all models
The hierarchy of database schemas used in the project

Correct answer: The dependency relationships and execution order between models

The DAG in dbt represents the dependency graph between models, ensuring each model is built only after all its upstream dependencies are complete.

Question 63: Where are dbt connection credentials and target environment settings typically stored?

dbt_project.yml
profiles.yml (Correct answer)
schema.yml
.env file in the project root

Correct answer: profiles.yml

`profiles.yml` stores database connection credentials and target configurations, typically located in `~/.dbt/` to keep secrets out of version control.

Question 64: Which microbatch behavior makes the incremental strategy more efficient for large time-series data?

Loading everything in one query
Running only on views
Skipping the event_time column
Processing data in separate time-based batches that can be retried or run in parallel (Correct answer)

Correct answer: Processing data in separate time-based batches that can be retried or run in parallel

Microbatch splits the build into batches by event_time so each batch can be processed, retried and backfilled independently.

Question 65: A model's SQL uses {{ ref('stg_orders', v=2) }}. What feature is being used?

Seed column overrides
Model versions (Correct answer)
Snapshot versions
Package versioning

Correct answer: Model versions

Model versions let you ref a specific version of a model while migrating consumers.

dbt Analytics Engineering Certification Exam

This certification validates an individual's proficiency in using dbt (data build tool) for data transformation, modeling, and analytics engineering best practices.

Questions

90 min

Time Limit

65%

To Pass

Checking for saved progress...

Exam Rules

You can skip questions and return to them later
Flag questions for review before submitting
No feedback shown until you submit the entire exam
Unanswered questions count as wrong — answer everything
10 pretest questions are mixed in and don't affect your score
Timer auto-submits when time runs out
Your progress is auto-saved every 30 seconds

These practice questions were written with AI tools. They are not real exam questions. We have not yet matched this exam's facts to an official source, so confirm fees, dates and rules with the exam body. Not reviewed by a subject-matter expert. Editorial policy