dbt-Analytics-Engineering dbt Analytics Engineering Certification Exam Questions and Answers

(branch)

(commit)

(pull request)

In dbt development workflows, version control using Git is essential for ensuring collaborative, safe, and trackable changes to analytics code. The correct first step when making updates—such as adding new models—is to create a new Git branch. This isolates development work from the production (main) branch, preventing incomplete or experimental logic from affecting deployed transformations. Branching supports dbt’s modular development approach and aligns with best practices for analytics engineering.

Once the branch is created, the developer modifies SQL models, tests, macros, or documentation as required. To permanently record these modifications in Git, the developer must commit the changes. A commit serves as a snapshot of progress and creates an auditable history of transformations made to the project, enabling rollbacks, diffs, and peer review.

After development is complete, the developer submits a pull request (PR). The pull request triggers CI checks—often including dbt build, schema tests, and contract validations—to ensure code quality and identify impacts on downstream models. PRs allow team members to review and comment before changes merge into the main branch, enforcing governance, consistency, and reliability. This workflow embodies the engineering rigor dbt encourages: modular development, testing, versioning, and peer review.

This test is a singular test, meaning it is defined as a standalone SQL file placed inside the /tests directory (not attached through YAML like generic tests). dbt executes singular tests by running the SQL query, and the test fails if the query returns any rows. In this case, the test selects all records from fct_orders where status_code = 13. That means if even one row in fct_orders has a status code of 13, the test will fail, because dbt interprets returned rows as evidence of a data-quality issue.

The configuration block at the top sets severity='error', meaning the test raises an error when it fails, rather than a warning. This setting overrides default severity behavior, so option B is incorrect. Singular tests do not need to be attached in YAML because they run automatically when placed under /tests → making option A incorrect. Meanwhile, generic tests live in /tests/generic, but singular tests should live directly under /tests (option C incorrect).

Therefore, the only correct statement is that records with status_code = 13 will cause the test to fail, because the test query is explicitly written to identify such rows.

In dbt, a singular test is any SQL file placed directly inside the tests/ directory—not inside the tests/generic/ folder. Singular tests contain a custom SQL query where returning any rows means failure. They are distinct from generic tests, which are YAML-defined and live inside the generic directory, often referencing macros.

Looking at the folder structure:

In tests/generic/

furniture_customers_test.sql → This is a generic test, not a singular test.

In the root tests/ directory

macro_stg_tpch_orders_assert_pos_price.sql → singular test

macro_stg_tpch_suppliers_assert_pos_acct_bal.sql → singular test

stg_tpch_orders_assert_positive_price.sql → singular test

When you run:

dbt test --select 'test_type:singular'

dbt selects only singular tests, meaning tests that are individually written SQL files in the top-level tests/ directory.

Therefore, dbt will run:

macro_stg_tpch_orders_assert_pos_price

macro_stg_tpch_suppliers_assert_pos_acct_bal

stg_tpch_orders_assert_positive_price

It will not run furniture_customers_test, because that file is inside tests/generic/, which is reserved for template-based generic tests and is not classified as singular.

Thus, the correct answer is Option C.

The requirement is:

Select all models that have been modified → this uses the state selector:state:modified

But only those modified models that are materialized as views → this uses the config selector:config.materialized:view

Then include upstream and downstream dependencies of those models → this requires adding + around the selectors.

To combine both selectors, dbt uses a comma-separated list within --select, meaning both selectors must match simultaneously.

The correct syntax is:

dbt build --select +state:modified, +config.materialized:view+

This command:

Finds models whose state is modified.

Filters them to only those materialized as views.

Includes upstream (+) and downstream (+) dependencies.

Runs and tests the selected nodes because dbt build includes running models, tests, seeds, and snapshots.

Why the other options are wrong:

A and C use invalid selector syntax (config.materialized:view+ must be prefixed with +).

B only selects modified models but does not filter by materialization type.

D uses materialized:view, which is invalid without the config. prefix.

Thus, only Option E satisfies all conditions and uses correct dbt selector syntax.

The only correct statement is Option C, because dbt allows you to override the schema for a package’s models directly within your project’s dbt_project.yml file. Packages behave just like subfolders within your own project, and therefore the standard configuration pattern applies:

models:

timeformat:

+schema: timeseries

This correctly overrides the package’s default schema, allowing all models within the timeformat package to be built into the timeseries schema.

Why the other options are incorrect

Option A

Local packages can be added using a path reference, but that is not what a private Git repository refers to. A private repo must be added using git: plus SSH/HTTPS URL, not local: unless the developer literally cloned the repo into a local directory. The question specifically describes a private repository, so this is not the correct statement.

Option B

Packages are installed using dbt deps, not dbt build.

dbt build only runs models, tests, and snapshots; it does not install dependencies.

Option D

Including a version or revision is allowed for private Git-based packages. You can specify branches, tags, or commit hashes safely in packages.yml.

When converting a model-specific SQL query into a generic test, dbt expects the test macro to accept the two standard arguments used across all generic tests: model and column_name. These arguments allow dbt to automatically pass the correct table and the correct column when the test is applied in YAML.

The argument model represents the actual relation (table/view) being tested. dbt compiles the reference itself—so instead of writing ref('stg_orders'), generic tests always use {{ model }} to represent the referenced relation.

The argument column_name represents the actual column being tested. In this case, the hard-coded column amount_usd becomes the dynamic argument {{ column_name }}, allowing you to apply the logic to any numeric or validated column across multiple models.

So the generic version of your SQL becomes:

select *

from {{ model }}

where {{ column_name }} < 0

The other options are incorrect:

source is not the standard argument for generic tests.

model_name is not automatically passed by dbt.

field is not a recognized standard test argument.

Thus, the two correct standard arguments are model and column_name.

The correct answer is D: Declare the external table as a source using the external configuration.

When data arrives in an external storage system such as S3, many warehouses (like Snowflake, BigQuery, and Databricks) support external tables that reference data stored outside the warehouse. dbt fully supports external tables by allowing them to be declared as sources using the external: configuration block inside sources: in a YAML file. This tells dbt that the upstream object exists outside the warehouse and should not be materialized or transformed by dbt directly. It also enables dbt to include external data in lineage graphs, freshness checks, and downstream model dependencies.

Option A is not ideal because you still need to formally register the external table as a source; simply querying it in a model breaks the data lineage structure. Option B is unnecessary and operationally brittle—temporary tables are not required for external data ingestion. Option C is incorrect because dbt seed is intended for uploading local static CSV files, not files arriving via S3 or controlled by external vendors. Seeds do not support dynamic ingestion patterns.

Thus, the correct and documented method is to declare the external table as a source with the external configuration, allowing dbt to consume the data appropriately while maintaining proper governance and lineage.

The --fail-fast flag tells dbt to stop scheduling any new nodes as soon as one node fails. Importantly, dbt does not kill models that are already running; in-flight nodes are allowed to finish.

Here’s what happens step by step with 2 threads:

Roots model_a and model_b start first.

model_a finishes successfully. That makes model_c eligible to run.

dbt now runs model_b and model_c in parallel.

While they are running, model_b fails.

Because --fail-fast is set, dbt immediately stops scheduling any additional models (like model_d, model_e, or model_f).

model_c was already running when model_b failed, so it is allowed to complete successfully.

Downstream models of either branch (model_d, model_e, and model_f) never start, because fail-fast prevents any further nodes from being queued after the first failure.

So, the only models that successfully build during this run are:

model_a (completed before model_b failed)

model_c (already running at the time of failure and allowed to finish)

Hence the correct choice is B: model_a, model_c.

The correct answer is A: The freshness check can be disabled with freshness: null.

In dbt, freshness checks are controlled by the freshness configuration block within a source definition. If a project includes a default freshness configuration, but you want to turn off freshness checking for a specific table, you can explicitly set freshness: null. This overrides inherited settings and tells dbt not to compute or evaluate freshness for that object.

Option B is incorrect because dbt evaluates all configured sources before finishing; it does not exit after the first failure. Instead, it completes the run and reports pass/warn/error statuses according to the configured thresholds.

Option C is incorrect because the placement of freshness checks is flexible. Some teams run dbt source freshness first to validate data arrival before running models; others run it separately or at the end of a workflow. There is no requirement that it always be last.

Option D is incorrect because dbt build does not automatically run freshness checks. Freshness checks are performed only when dbt source freshness is executed explicitly or configured in a dbt Cloud job step.

Therefore, A is the only statement that correctly describes the behavior of source freshness checks.

Hook 1

on-run-end: "{{ macro_x() }}"

Answer: macro_1

Hook 2

models:

:

post-hook: "{{ macro_x() }}"

Answer: macro_2

Hook 3

on-run-start: "{{ macro_x() }}"

Answer: macro_3

Hook 4

{{

config(

pre-hook: "{{ macro_x() }}"

)

}}

Answer: macro_4

dbt supports run-level hooks and model-level hooks. Run-level hooks fire once per invocation, while model-level hooks fire around each individual model.

on-run-end is a run-level after hook that executes once after the entire dbt command completes. Because macro_1() must run after every dbt run, it correctly belongs here.

The post-hook configured under the models: section runs after each model in that scope finishes building. This matches the requirement for macro_2() to execute after a model runs.

on-run-start is a run-level before hook and fires once before dbt begins executing any models for that command, making it the right place for macro_3() which must run before every dbt run.

Finally, the pre-hook specified inside a model’s config() block runs before that specific model is built. Since macro_4() must execute before a model runs, it belongs in the pre-hook configuration.

Thus the correct mapping is:

on-run-end → macro_1

model post-hook → macro_2

on-run-start → macro_3

model pre-hook → macro_4.

Drops and recreates a definition of a query which is executed at the time it's referenced

✅ view

Inserts or updates records of an existing object in the warehouse using a delta

✅ incremental

Code belonging to the model is inserted into the places the model is referenced

✅ ephemeral

Drops and recreates an object in the warehouse as stored results when using dbt run

✅ table

The correct answers are C and D.

dbt supports two main categories of tests: generic tests and singular tests. Generic tests are defined in YAML and applied to models or columns, while singular tests are written as SQL files placed in the /tests/ directory. This makes Option C correct—singular tests must be created as .sql files inside that folder, and dbt will execute each file as a test query.

Option D is also correct. dbt includes four built-in generic tests: unique, not_null, relationships, and accepted_values. These are considered “native” tests and are available without requiring any additional packages. They cover the most common data quality checks and are applied through YAML configurations on models or columns.

Option A is incorrect because dbt does not automatically apply any tests. All tests—native or custom—must be explicitly defined in YAML or created manually. Nothing is inferred from primary keys.

Option B is incorrect because dbt’s package hub contains community packages, not the list of native tests. Native tests are documented directly within dbt’s core functionality.

Option E is incorrect because the constraints configuration is used to create database-level constraints on supported warehouses, not to run dbt tests. Tests still require YAML test definitions or .sql files.

Thus, only C and D accurately describe dbt's testing behavior.

The correct answers are B and E.

The dbt retry command was introduced to help developers quickly re-run only what failed in the prior dbt invocation. According to dbt documentation, dbt retry examines metadata from the previous execution, including the run results and the manifest.json, to determine which nodes failed. This aligns with Option E, as dbt uses the manifest to identify the models, tests, and other nodes that errored or were not executed successfully.

Option B is also correct because dbt retry works only when the previous run failed due to execution errors (e.g., a warehouse failure or a logic error in SQL). If the failure was due to a syntax error that prevented dbt from compiling, there will be no valid manifest produced, so retry cannot execute. Thus, dbt retry can only re-run models when the previous failure occurred after successful compilation.

Options A and C are incorrect because retry does not run all nodes nor does it automatically pick up exactly where an execution stopped — instead, it runs failed and downstream unbuilt nodes as determined by the manifest. Option D is also incorrect; retry uses the execution metadata, not selectors, to determine what should run.

The correct answers are B: All the code changes along the lifecycle of a project are tracked, and E: Code can be reverted to a previous state.

Git is a distributed version control system designed to maintain a complete, chronological history of all code changes. Every commit records who made the change, when it occurred, and what the modification included. This ensures transparency, reproducibility, and accountability across the development lifecycle, which makes B correct. Git also allows users to revert code to any previous commit, branch, or tag, making E correct as well. This capability is critical for recovering from mistakes, undoing faulty deployments, and ensuring stable releases.

Option A is incorrect because Git does not create file versions with suffixes; instead, it stores changes as snapshots within a repository. File suffixing is not part of Git’s functionality.

Option C is incorrect because Git does not automatically send email notifications. Notification mechanisms come from hosting platforms like GitHub, GitLab, or Bitbucket—not from Git itself.

Option D is incorrect because Git does not prevent committing sensitive information. Developers must manually ensure secrets are excluded via .gitignore, secret managers, or pre-commit hooks. Git will store whatever is committed unless prevented through tooling.

Thus, only B and E correctly describe how Git supports version control in analytics engineering and dbt workflows.

dbt Python models support a limited set of materializations because they rely on execution within the data platform’s Python compute engine (such as Snowpark for Snowflake, Dataproc for BigQuery, or Spark). These engines require models to materialize into actual relations—tables or views—in order to persist the results of Python-based transformations.

The ephemeral materialization, however, is fundamentally incompatible with this behavior. Ephemeral models do not create relations in the warehouse; instead, dbt inlines their SQL logic directly into downstream models. Since Python models cannot be inlined (they execute Python code, not SQL), dbt does not allow ephemeral Python models. dbt requires Python model outputs to be materialized as either:

table

view

incremental

Therefore, ephemeral is not supported for Python models, and attempting to configure a Python model as ephemeral will result in a compilation error.

The reason is straightforward: ephemeral logic depends on SQL compilation, while Python models depend on executing Python code in the data platform. Because these mechanisms are incompatible, dbt restricts Python models to relational materializations only.

Thus, the correct answer is No — ephemeral is not supported for Python models.

The behavior described—dbt running the orders model as a view despite being explicitly configured as a table—indicates that dbt is not correctly detecting or applying the model-level configuration during compilation. dbt relies heavily on the target-path directory to write compiled SQL, manifest files, and run artifacts. If the target-path is misconfigured, pointing to a location that dbt does not handle correctly or that overlaps with another folder used internally, dbt may fail to load the correct configuration from the merged project settings.

When dbt cannot locate the compiled configuration for a model, it defaults to its standard materialization type, which is view. This explains why the logs show:

“Finished running 1 view model”

even though the dbt_project.yml clearly declares:

materialized: table.

Additionally, the logs indicate no warnings or parsing errors, meaning dbt ran successfully but with incorrect settings—another indicator of configuration metadata being overridden or misplaced due to an incorrect target-path.

By resolving the target-path issue, dbt will successfully load the model configuration and materialize the orders model as a table as intended.