dbt Analytics Engineering Certification Exam — Questions and Answers

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

Defining project-wide variables accessible via the `var()` function (Correct answer)
Declaring Jinja macros reusable across all models
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 2: Where are dbt documentation files generated by default?

profiles/
docs/
target/ (Correct answer)
build/

Correct answer: target/

By default, when you run `dbt docs generate`, the generated documentation files (HTML, CSS, JavaScript, and JSON) are stored within the `target/` directory of your dbt project. This directory is where all compiled SQL, run logs, and other build artifacts are placed. The `dbt docs serve` command then uses these files from the `target/` directory to display the documentation.

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

Seed
View
Table
Incremental (Correct answer)

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 4: What is the purpose of the `target` key in a dbt profile?

Sets the maximum number of threads dbt can use
Specifies the file path where compiled SQL is saved
Points to the schema.yml file for validation
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 5: What does the 'persist_docs' setting do in dbt to optimize project documentation management?

Persists documentation metadata in the warehouse (Correct answer)
Deletes logs
Removes ephemeral models
Increases incremental run frequency

Correct answer: Persists documentation metadata in the warehouse

The `persist_docs` setting in dbt allows you to store model and column descriptions directly in your data warehouse as metadata. When enabled, dbt will update the comments or descriptions of tables and columns in the database itself. This makes documentation accessible directly through SQL clients and data catalog tools, improving data discoverability and governance beyond the dbt documentation website.

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

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

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 7: Which delimiter is used in Jinja to output a value in dbt SQL files?

{# ... #}
{% ... %}
{{ ... }} (Correct answer)
<< ... >>

Correct answer: {{ ... }}

`{{ }}` is the Jinja expression delimiter that evaluates and renders a value into the SQL output.

Question 8: Why should 'SELECT *' be avoided in dbt models for performance?

Because it disables filters
Because it retrieves unnecessary columns (Correct answer)
Because it's faster
Because it increases logging

Correct answer: Because it retrieves unnecessary columns

Using `SELECT *` in dbt models, or any SQL query, is generally discouraged for performance reasons because it retrieves all columns from a table, even those that are not needed. This can lead to increased data transfer, higher memory usage, and slower query execution, especially with wide tables. Explicitly selecting only the required columns reduces the data processed, improving efficiency and clarity.

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

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

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 10: Which dbt artifact is essential for enabling Slim CI state comparison?

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

Correct answer: manifest.json

`manifest.json` is the compiled graph of all nodes and their checksums, which dbt diffs against to find modified nodes.

Question 11: What happens when you run `dbt seed --full-refresh`?

Indexes are rebuilt
Source tables are re-imported
Only new CSV rows are appended
The seed table is dropped and recreated from scratch (Correct answer)

Correct answer: The seed table is dropped and recreated from scratch

`--full-refresh` causes dbt to drop the existing seed table before recreating it, ensuring a clean load.

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

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

Correct answer: profiles.yml

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

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

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

Correct answer: snapshots/

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

Question 14: What does a singular dbt test return to indicate a failure?

A NULL value
An exception
Any non-empty result set (Correct answer)
A boolean FALSE value

Correct answer: Any non-empty result set

dbt runs the SQL and considers any rows returned as failures; zero rows means the test passes.

Question 15: Which configuration parameter can adjust query concurrency limits in dbt Cloud runs?

Warehouse size
Threads (Correct answer)
Seeds config
Models directory

Correct answer: Threads

The `threads` configuration parameter in dbt controls the maximum number of concurrent SQL queries dbt can execute against your data warehouse. Adjusting this setting allows you to fine-tune the parallelism of your dbt runs. Increasing the number of threads can speed up execution by running more models simultaneously, but it should be balanced with your warehouse's capacity to avoid overloading it.

Question 16: Which dbt Cloud concept separates the environment where CI tests run from where production runs land?

Namespaces
Tenants
Workspaces
Environments (Correct answer)

Correct answer: Environments

dbt Cloud Environments (e.g., 'CI', 'Production') hold distinct connection credentials, dbt versions, and custom schemas for isolation.

Question 17: What is the purpose of `adapter.dispatch()` in dbt macros?

Finds the correct macro implementation for the active database adapter (Correct answer)
Routes traffic to different dbt targets
Sends the compiled SQL to the warehouse
Dispatches a dbt Cloud run via API

Correct answer: Finds the correct macro implementation for the active database adapter

`adapter.dispatch()` enables polymorphism by resolving a macro to its adapter-specific implementation (e.g., Snowflake vs BigQuery).

Question 18: Where are singular (bespoke) dbt tests stored in a standard project layout?

models/
analyses/
macros/
tests/ (Correct answer)

Correct answer: tests/

Singular tests are plain SQL SELECT files placed in the `tests/` directory; a non-empty result means the test fails.

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

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

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 20: Which dbt package is the most widely adopted for extending the set of generic tests beyond the four built-in ones?

dbt-expectations
dbt-utils (Correct answer)
dbt-codegen
dbt-audit-helper

Correct answer: dbt-utils

`dbt-utils` ships dozens of reusable macros and generic tests such as `expression_is_true` and `recency`.

Question 21: How do you reference a seed table inside a dbt model?

{{ table('seed_name') }}
{{ ref('seed_name') }} (Correct answer)
{{ seed('seed_name') }}
{{ source('seeds', 'seed_name') }}

Correct answer: {{ ref('seed_name') }}

Seeds are treated like models in the DAG, so you reference them with `{{ ref('seed_name') }}` just like any model.

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

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

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 23: Which dbt feature allows you to apply a test to all columns of a source table at once using a meta tag?

Column-level tests defined in schema.yml (Correct answer)
Tag-based test selection
Model contracts
Column-level configs

Correct answer: Column-level tests defined in schema.yml

Defining tests under each column in the sources YAML lets you attach generic tests like `not_null` or `unique` to individual source columns.

Question 24: What does `dbt run --full-refresh` do to an incremental model?

Appends all historical records again
Skips the model entirely
Converts it to a view temporarily
Rebuilds the table from scratch, ignoring the existing data (Correct answer)

Correct answer: Rebuilds the table from scratch, ignoring the existing data

`--full-refresh` drops and recreates an incremental model's table from the ground up, equivalent to the initial build.

Question 25: What node selector prefix lets you run `dbt test` on only source tests?

source:*
src:*
raw:*
sources:* (Correct answer)

Correct answer: sources:*

The `sources:` selector prefix targets source nodes, so `dbt test --select sources:` runs only source-defined tests.

Question 26: Which dbt macro generates a series of comma-separated column expressions from a list?

dbt.columns_join()
dbt_utils.star() (Correct answer)
dbt_utils.pivot()
dbt.list_columns()

Correct answer: dbt_utils.star()

`dbt_utils.star()` selects all columns from a relation, optionally excluding specified ones, and renders them as a comma-separated list.

Question 27: What happens if you call `{{ var('my_var') }}` and `my_var` is not defined anywhere?

It returns None
dbt raises a compilation error (Correct answer)
It returns the string 'my_var'
It returns an empty string

Correct answer: dbt raises a compilation error

If a variable is referenced without a default and is not defined in dbt_project.yml or via `--vars`, dbt raises a compilation error.

Question 28: Which `incremental_strategy` inserts all new rows without checking for duplicates, suitable for append-only event streams?

merge
delete+insert
append (Correct answer)
upsert

Correct answer: append

The `append` strategy simply inserts new rows without any deduplication or updating, ideal for immutable event logs.

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

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

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 30: Which dbt command can be used to preview model SQL performance by compiling models without executing them?

dbt compile (Correct answer)
dbt docs generate
dbt test
dbt run

Correct answer: dbt compile

The `dbt compile` command is used to process your dbt project and generate the executable SQL for each model, saving it to the `target/` directory. This command is invaluable for previewing the exact SQL that dbt will send to your data warehouse without actually running it. By reviewing the compiled SQL, you can identify potential performance issues or logical errors before execution, aiding in optimization and debugging.

Question 31: Which Git command is used to create a new branch for feature development?

git checkout -b (Correct answer)
git merge main
git commit -m
git stash

Correct answer: git checkout -b

The `git checkout -b <branch-name>` command is used to create a new branch and immediately switch to it. This is a fundamental practice in Git for feature development, as it allows developers to work on new features or bug fixes in isolation without affecting the main codebase. Once the work is complete, the branch can be merged back into the main branch.

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

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

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 33: In dbt, what is a 'source' used for?

Triggering external pipelines
Storing lookup data in CSV files
Declaring raw database tables that dbt did not create (Correct answer)
Defining output tables

Correct answer: Declaring raw database tables that dbt did not create

Sources let you declare raw tables loaded by external tools so dbt can reference, document, and test them.

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

Execution metadata such as model status, timing, and row counts from the last dbt invocation (Correct answer)
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

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 35: Which command executes all dbt models in a project?

dbt build
dbt deploy
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 36: In a dbt Cloud job schedule, what format is used to define cron-based run timing?

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

Correct answer: Standard 5-field cron expressions

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

Question 37: Which accepted_values test parameter makes the test case-insensitive?

normalize: true
ignore_case: true
case_sensitive: false (Correct answer)
lower_values: true

Correct answer: case_sensitive: false

Setting `case_sensitive: false` in the `accepted_values` test config causes dbt to compare values without regard to letter case.

Question 38: Which severity level causes dbt test to exit with a non-zero code but continue running remaining tests?

info
error
warn (Correct answer)
debug

Correct answer: warn

Setting `severity: warn` makes dbt report the failure as a warning and continue without stopping the run.

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

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

Correct answer: my_model+

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

Question 40: Which config allows you to set a threshold so a test only fails if MORE than N rows are returned?

threshold
max_failures
error_if (Correct answer)
warn_if

Correct answer: error_if

`error_if` accepts a numeric expression (e.g., `>100`) so a test only errors when the failure count exceeds that limit.

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

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

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 42: Which dbt materialization type reduces query execution time by persisting data in tables rather than views?

Ephemeral
View
Table (Correct answer)
Seed

Correct answer: Table

The 'table' materialization type in dbt creates a permanent table in your data warehouse for the model's output. Unlike views, which re-execute the underlying query every time they are accessed, tables store the processed data, significantly reducing query execution time for downstream consumers. This is ideal for models that are frequently queried or involve complex transformations, as it pre-computes and persists the results.

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

table
ephemeral
incremental
view (Correct answer)

Correct answer: view

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

Question 44: Which YAML key is used to define generic tests on a column inside a schema.yml file?

validations
constraints
tests (Correct answer)
checks

Correct answer: tests

Under each column definition you add a `tests:` list to attach generic tests.

Question 45: What does `dbt debug` do?

Shows detailed SQL output for each model as it runs
Validates the project configuration and database connection (Correct answer)
Displays the full DAG for the current project
Tests all data quality constraints defined in schema.yml

Correct answer: Validates the project configuration and database connection

`dbt debug` checks that the project configuration is valid and that dbt can successfully connect to the target data warehouse using the current profile.

Question 46: Which command is used to install dbt packages listed in `packages.yml`?

dbt install
dbt init
dbt update
dbt deps (Correct answer)

Correct answer: dbt deps

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

Question 47: What does the `on-run-start` hook in dbt_project.yml execute?

SQL run after every model completes
A validation check before dbt installs packages
A Python script triggered when the server starts
SQL run before any model or test in the job (Correct answer)

Correct answer: SQL run before any model or test in the job

`on-run-start` hooks run arbitrary SQL statements before any models, seeds, or tests are executed in a dbt invocation.

Question 48: How do you pass a runtime variable override when running dbt?

dbt run --config key=value
dbt run --vars '{key: value}' (Correct answer)
dbt run --env key=value
dbt run --set key=value

Correct answer: dbt run --vars '{key: value}'

The `--vars` flag accepts a YAML dictionary string that overrides project variables for the duration of the run.

Question 49: What does the `+schema` config in dbt_project.yml do when set for a model folder?

Sets the source schema to read from
Appends a custom suffix to the target schema for that folder's models (Correct answer)
Locks the schema from future changes
Renames the model

Correct answer: Appends a custom suffix to the target schema for that folder's models

`+schema` defines a custom schema suffix that dbt appends to the target schema, allowing logical separation (e.g., `dbt_<user>_staging`).

Question 50: What is the default column delimiter used when dbt reads CSV seed files?

Comma (,) (Correct answer)
Pipe (|)
Tab (\t)
Semicolon (;)

Correct answer: Comma (,)

dbt expects standard CSV format with comma-separated values by default; you can override this with the `delimiter` seed config.

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

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

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 52: Which dbt command creates the compiled SQL files and saves them in the target directory without running them?

dbt build
dbt snapshot
dbt compile (Correct answer)
dbt clean

Correct answer: dbt compile

The `dbt compile` command processes your dbt project, including models, tests, and macros, and generates the compiled SQL files. These compiled SQL files are saved in the `target/` directory. This command is useful for reviewing the final SQL that dbt will execute without actually running it against your data warehouse, aiding in debugging and understanding the generated queries.

Question 53: What does the `dbt compile` command do?

Generates compiled SQL files without executing them against the database (Correct answer)
Executes all models and writes results to the data warehouse
Installs all packages listed in packages.yml
Validates tests defined in schema.yml files

Correct answer: Generates compiled SQL files without executing them against the database

`dbt compile` resolves Jinja templating and `ref()` calls to produce compiled SQL in the `target/compiled/` directory without running any queries.

Question 54: What is the purpose of the 'macros/' directory in a dbt project?

Stores model descriptions
Houses reusable Jinja-based SQL snippets (Correct answer)
Holds compiled models
Manages logs

Correct answer: Houses reusable Jinja-based SQL snippets

The `macros/` directory in a dbt project is used to house reusable Jinja-based SQL snippets, known as macros. These macros allow you to define custom SQL logic or functions that can be called and reused across multiple models, tests, or even other macros. This promotes code reusability, reduces redundancy, and helps maintain consistency in your transformations.

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

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

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 56: Which file is commonly used to describe fields, columns, and tests in dbt models?

models.sql
schema.yml (Correct answer)
dbt_project.yml
snapshots.yml

Correct answer: schema.yml

The `schema.yml` file (or any `.yml` file within your `models/` or `sources/` directories) is where you define metadata for your dbt resources. This includes descriptions for models, sources, and their individual columns, as well as defining tests to ensure data quality. It's crucial for documenting your data assets and enforcing data quality checks within your dbt project.

Question 57: In a dbt Cloud job, what does enabling 'Generate docs' do?

Adds doc() blocks to all models
Publishes docs to a public URL automatically
Sends documentation to Confluence
Runs `dbt docs generate` to refresh the catalog artifact after the run (Correct answer)

Correct answer: Runs `dbt docs generate` to refresh the catalog artifact after the run

The 'Generate docs' toggle appends a `dbt docs generate` step to the job, refreshing the catalog.json and making docs available in dbt Cloud.

Question 58: Which command would you run to build all models in your dbt project?

dbt run (Correct answer)
dbt test
dbt seed
dbt clean

Correct answer: dbt run

To build all models in your dbt project, you would run the `dbt run` command. This command executes the SQL defined in your dbt models, applying the specified materializations (e.g., creating tables or views) in your data warehouse. It processes models in the correct dependency order, ensuring data transformations are applied sequentially.

Question 59: In dbt, what is an 'ephemeral' materialization?

A model that runs only once and is never refreshed
A model stored in a temporary table and dropped after the session ends
A model that is deleted from the database after each run
A model that is never written to the database and exists only as a CTE in downstream models (Correct answer)

Correct answer: A model that is never written to the database and exists only as a CTE in downstream models

Ephemeral models are not materialized in the database at all; dbt inlines them as CTEs in any downstream model that references them.

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

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

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 61: Which Jinja statement would you use to set a local variable inside a dbt model?

{% let my_var = 'value' %}
{% var my_var = 'value' %}
{% set my_var = 'value' %} (Correct answer)
{% assign 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 62: Which dbt-utils test checks that a numeric column never decreases over time within a partition?

not_decreasing
expression_is_true
recency
monotonic_increase (Correct answer)

Correct answer: monotonic_increase

`dbt_utils.monotonic_increase` asserts that values in a column are always greater than or equal to the previous row's value.

Question 63: Where are dbt model SQL files typically stored inside a dbt project structure?

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

Correct answer: models/

In a dbt project structure, dbt model SQL files are typically stored inside the `models/` directory. This directory is where you define all your data transformation logic, organized into subdirectories as needed. Keeping models in this dedicated location helps maintain a clean and organized project structure, making it easy to locate and manage your transformations.

Question 64: What is the purpose of dbt Cloud's Slim CI feature?

Reduces Cloud plan costs automatically
Runs only models that have changed since the last successful job (Correct answer)
Minifies generated SQL for faster queries
Compiles dbt models without running them

Correct answer: Runs only models that have changed since the last successful job

Slim CI uses the `state:modified` selector with a deferred environment to run only the models impacted by a pull request, saving time and cost.

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

The dependency relationships and execution order between models (Correct answer)
The order of test execution across all models
The branching strategy used for version control
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.

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