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

In dbt, singular tests are custom SQL tests that live as standalone .sql files inside the root /tests directory, not inside /tests/generic. A singular test returns rows that indicate failure, and dbt runs the SQL directly as written. Generic tests, on the other hand, live inside the /tests/generic folder and are YAML-based macro-driven tests.

From your test folder structure (shown in the original screenshot), the following SQL files exist:

Inside /tests/generic/

furniture_customers_test.sqlThis file is a generic test, not singular.

Inside the root /tests directory:

macro_stg_tpch_orders_assert_pos_price.sql

macro_stg_tpch_suppliers_assert_pos_acct_bal.sql

stg_tpch_orders_assert_positive_price.sql

These are singular tests, because they are standalone SQL files in the tests root folder.

When you run:

dbt test --select 'test_type:singular'

dbt filters for only tests classified as singular. It ignores generic tests entirely.

Therefore, only the following 3 tests will run:

macro_stg_tpch_orders_assert_pos_price

macro_stg_tpch_suppliers_assert_pos_acct_bal

stg_tpch_orders_assert_positive_price

This matches Option C, making it the correct answer.

The true root cause is not related to run hooks or regular expressions. The warning clearly indicates that dbt found a configuration path that does not match any actual model path inside the project. In this case, the config block uses the key models.jaffle-shop, which is incorrect because dbt model paths must match the folder structure and cannot contain hyphens unless the folder itself contains a hyphen. Since the project folder is named jaffle_shop (with an underscore), dbt cannot map the configuration to any model.

dbt evaluates configuration paths based on directory names under the models/ folder. A mismatch between the folder name and the configuration key causes dbt to ignore the configuration entirely. This results in the warning:

“Configuration paths exist in your dbt_project.yml file which do not apply to any resources.”

This warning is specifically triggered when dbt identifies unused configuration paths due to typos, incorrect nesting, or invalid identifiers. dbt does not interpret hyphens (-) as valid Python identifiers for resource names, so a config path like models.jaffle-shop will never be applied.

Therefore, the assertion that the cause is a "run hook with an incorrect regular expression" is incorrect. The actual root cause is that the configuration path uses the wrong name (jaffle-shop instead of jaffle_shop).

Create a new branch in git and switch to itB. Update the dbt code according to the new logicC. Create a Pull / Merge RequestD. Run some automated CI checks and/or manual review of the updated codeE. Merge the updated code to the main git branch

Version control best practices in dbt follow the same engineering workflow used in modern software development. The first step is always to create a new git branch and switch into it, ensuring all work is isolated from production-ready code and allowing your team to develop safely without affecting others. Once inside the feature branch, you update the dbt code according to the new logic, which may include modifying models, tests, macros, or documentation.

Next, you create a Pull Request (PR), also known as a Merge Request, to propose integrating your changes into the main branch. This is important because dbt projects are collaborative, and PRs facilitate peer review, enforce project standards, and prevent regressions. Once the PR is created, automated CI pipelines—such as running dbt build, schema tests, data quality checks, and code-style checks—are executed. Reviewers may also manually inspect code for logic correctness, naming conventions, and modeling consistency.

After all checks have passed and reviewers approve the PR, the final step is to merge the updated code into the main branch, making the new transformations part of the production dbt project. This workflow ensures governance, reliability, and auditable development practices, all of which are core principles in analytics engineering.

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.

In dbt’s recommended layered modeling architecture, the staging layer is intended to provide a clean, one-to-one representation of raw source tables. Each stg_ model should depend only on sources, not on other staging models. This ensures staging remains a simple, transparent layer where data is renamed, recast, standardized, and lightly transformed before being passed to intermediate and mart layers.

In the DAG shown, at least one staging model (for example, stg_line_items or stg_tpch_line_items) appears downstream of another staging model, meaning a stg_ model is referencing another stg_ model. This violates dbt’s recommended modeling practice, because it creates unnecessary complexity in the staging layer and reduces modularity and transparency. Downstream layers such as intermediate (int_) and marts (fct_) should be used to combine, enrich, or join multiple staging outputs.

When staging models depend on each other, it becomes harder to trace lineage, reduces clarity about where transformations occur, and complicates the entire DAG. The proper pattern is:

Sources → Staging (stg_) → Intermediate (int_) → Marts (fct_)

Since the DAG shows staging models referencing other staging models, the rules have indeed been violated.

Will always point to the latest version of the source schema

Correct Match: ✔ defer

Allows to use objects built in the target schema with any downstream tool

Correct Match: ✔ dbt clone

3️⃣ Allows to safely modify objects built in the target schema

Correct Match: ✔ defer

4️⃣ Is a point-in-time operation

Correct Match: ✔ dbt clone

defer and dbt clone serve very different purposes in dbt, and understanding their behavior is essential for choosing the right command.

The --defer flag tells dbt to use already-built objects from a previous environment (often production) instead of rebuilding them. Because it always references the existing target schema’s most recent objects, it “always points to the latest version of the source schema.” Since no objects are overwritten when using defer, it also “allows safely modifying objects built in the target schema”—your development environment uses production objects without altering them.

By contrast, dbt clone creates a point-in-time copy of existing relations. This cloned schema is static; it does not auto-update when source data changes. Therefore, clone is classified as a “point-in-time operation.” Since clone copies physical tables/views into a new schema, downstream tools (BI dashboards, ML pipelines) can safely query the cloned environment without affecting production, making “allows to use objects built in the target schema with any downstream tool” the correct match.

Thus, defer is used for logic substitution without copies, while clone is used for replicable, point-in-time snapshots.

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.

Yes, this is a benefit of breaking large CTE-heavy SQL models into modular dbt models. According to dbt and Analytics Engineering best practices, modularity improves clarity, maintainability, and documentation quality. When CTEs remain embedded inside a single large SQL file, their purposes are often unclear, difficult to document, and hard for other developers to reuse. By extracting a logical CTE into its own model, dbt treats it as a first-class resource—meaning it can have its own description, tests, documentation, lineage, and metadata defined in YAML.

dbt’s documentation system allows each model to include a description explaining what the transformation does, the assumptions being made, and the expected behavior of the data. This aligns with the Analytics Engineering principle of creating self-documenting pipelines, where transformations are transparent and easier for downstream users to understand.

Additionally, modular models improve lineage visualization in the DAG. Instead of a single model hiding multiple transformation layers, a modular structure reveals how data flows through each intermediate step, helping both debugging and governance. Modularization also enables reusability—other models can reference the intermediate model rather than rebuilding the same logic through duplicated CTEs, supporting DRY (Don't Repeat Yourself) principles.

Therefore, moving CTEs into separate dbt models absolutely provides a documentation benefit and improves the overall engineering quality of the project.

The correct answer is A: dbt test --select "orders" --indirect-selection=empty".

In dbt, the --indirect-selection flag controls how dbt handles indirectly related resources during selection, such as tests whose parent models may or may not be selected. By default, dbt may include related resources depending on the selection behavior.

indirect-selection=empty is the strictest form of selection. It means that dbt will not run tests whose parent models are not explicitly selected, even if the tests are indirectly related. This prevents executing tests on models that you did not intend to build or include in your workflow, ensuring test execution is tightly scoped.

Other options behave differently:

Option B (no indirect-selection flag) uses dbt’s default behavior, which does include related tests, meaning tests could run even when their parent models were not selected.

Option C: buildable includes resources that can be built (e.g., when downstream dependencies require them).

Option D: cautious runs tests if their parents are selected transitively through dependencies.

Therefore, to prevent tests from running when parent models are unselected, you must use --indirect-selection=empty, making A the correct choice.

Modularity in dbt means breaking transformations into small, reusable steps that are easy to reason about, test, and reuse. In this DAG, two staging models (stg_detail_categories and stg_details) feed into two mart-level models (skills_with_details and lessons_with_details). If there is repeated logic in those two mart models—such as common joins, filters, or derived fields—that logic is a good candidate to be extracted into an intermediate model.

Option E follows dbt best practices: inspect the SQL of lessons_with_details and skills_with_details and identify shared logic to move into a new intermediate model (for example, int_details_with_categories). Both downstream models can then reference this intermediate model using ref(), centralizing logic and improving maintainability.

Options A and B push joins/unions into the staging layer, which should focus on clean, 1:1 representations of sources—not combining multiple sources. Option C collapses distinct staging models into one, reducing clarity and breaking the source-to-staging contract. Option D creates an overly wide, less reusable final table instead of focusing on shared reusable logic.

Therefore, the action that truly increases modularity is Option E: creating an intermediate model based on common SQL patterns.

The dbt compilation error explicitly states that the model fct_tasks depends on a node named stg_tasks, but dbt cannot find any resource with that name in the project. dbt resolves ref('stg_tasks') at compile time by searching for a model, seed, or source named stg_tasks. When no such model exists, dbt raises the exact error shown: “depends on a node named 'stg_tasks' which was not found.”

This error occurs before execution and is unrelated to whether the table exists in the warehouse. For dbt, the existence of a warehouse table is irrelevant during compilation—only the presence of a declared dbt resource matters. Therefore, option C (no table in the warehouse) cannot cause this error.

Option A is incorrect because ephemeral models still count as dbt models, and dbt can resolve ref() to them without problem.

Option D is wrong because defining a model in schema.yml is optional and unrelated to dbt’s ability to find the model. Tests and documentation require YAML entries, but model definitions do not.

Thus, the only correct explanation is that no model file named stg_tasks.sql exists under /models, making Option B the correct choice.

In dbt, the Directed Acyclic Graph (DAG) is created from model dependencies, which dbt identifies only through two mechanisms: the ref() function for referencing dbt models and the source() function for referencing declared sources. These functions are explicitly designed to tell dbt how models relate to each other so that dbt can track lineage, enforce build order, and generate documentation.

Option A, {{ source('source', 'table') }}, correctly references a declared source. Any model using source() establishes an upstream dependency on that source table. dbt will therefore draw a lineage line in the DAG from the source to the model.

Option B, {{ ref('stg_jaffle_shop__customers') }}, is the canonical way to reference another dbt model. ref() ensures dbt determines dependencies at compile time, and thus creates a DAG edge between this model and its parent.

The remaining options do not create lineage:

C directly references a database object, which dbt cannot interpret as a dependency.

D uses a Jinja variable unrelated to lineage.

E is invalid, because source() requires two arguments: a source name and table name, not a dotted string.

Therefore, A and B are the only valid answers.

The --fail-fast flag changes dbt’s execution behavior by immediately stopping the scheduling of any further models as soon as the first failure occurs. This is true regardless of the number of threads; in this case, the environment uses one thread, meaning models run strictly in sequence based on dependency order.

In the DAG provided, model_d is downstream of model_b, and model_f depends on model_d. The execution sequence when using a single thread would be something like:

model_a runs

model_c runs

model_e runs

model_b runs

model_d runs → fails

Once model_d fails:

With normal dbt run, dbt would attempt to continue running any unrelated models (though in this DAG, there are no more independent nodes left anyway).

With --fail-fast, dbt immediately stops scheduling any additional work, halting the run.

Therefore, no further models—including model_f or any other—will run. dbt does not drop models, retry them, or attempt related branches. It simply stops.

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.

The correct answer is A: Add model groups and set access to private.

dbt’s access control system is designed to restrict which models may reference others purely within the dbt project itself, independent of data warehouse permissions. This system uses model groups and an access property, which can be set to public, protected, or private. Setting a model to private means that only models within the same group are allowed to reference it. Any attempts to reference the model from outside the group will cause a compilation error, enforcing strong boundaries between domains or data products within a dbt project.

Option C (protected) is incorrect because protected allows references from other groups but requires explicit configuration—the intention is stewardship, not restriction. It does not fully restrict referencing.

Option B is incorrect because dbt model access control is not tied to warehouse permissions. Warehouse-level grants cannot control dependency references inside the dbt DAG.

Option D is irrelevant to access control. Versioning does not restrict references; it only manages updates to published models.

Therefore, the only correct way to restrict which models can reference another model is by placing it into a model group and marking it as private, which enforces strict internal usage boundaries.

Execute the last dbt command from the node point of failure.

Answer: retry

The --retry flag in dbt is specifically designed to re-execute a dbt command starting from the node where execution previously failed. In large DAGs, rebuilding all upstream models repeatedly is inefficient, especially when a downstream transformation is the only point of failure. dbt includes retry logic to support resiliency within orchestrated pipelines and CI/CD processes.

Using --retry allows dbt to leverage the metadata stored in the run_results.json to determine which model triggered the failure. Instead of re-running the entire DAG, dbt intelligently continues execution from the failed node, speeding up recovery and making iterative debugging far more efficient. This is particularly valuable in production pipelines where minimizing runtime is critical.

This behavior aligns with dbt’s philosophy of incremental, test-driven, modular development: failures should be isolated, reproducible, and simple to resume. By re-running only the subset of affected models, dbt improves developer productivity and orchestration reliability.

**Create a copy of an existing database object in a sandbox environment.

Answer: clone

The clone capability in dbt refers to creating a zero-copy clone (Snowflake) or snapshot-like duplicated object using the data platform's native features. This is extremely useful when developers or analysts need a safe sandbox environment to test transformations, experiment, or validate logic without affecting production data.

A clone operation duplicates the table or view metadata instantly without physically copying data, which is both cost-efficient and fast. dbt leverages this through commands such as dbt clone (dbt Cloud) or through adapters that support cloning.

Sandboxes created via cloning allow engineers to develop safely by ensuring that testing and prototyping has no impact on production tables. Because cloned objects share underlying data blocks, they are nearly storage-free unless mutated—keeping warehouse costs low.

This practice is aligned with modern analytics engineering best practices where separation of environments (dev/test/prod) is essential for governance, data quality, and reproducibility.

**Run a subset of models or tests in a sandbox environment without having to first build their upstream parents.

Answer: defer

The --defer flag allows dbt to reference already-built objects from another environment—commonly production—so that developers can run only the models they are modifying, without recreating all upstream dependencies.

When combined with --state, dbt compares the working branch’s DAG with previously generated artifacts and determines which nodes should be rebuilt. Any upstream nodes not modified in the current branch are deferred, meaning dbt will point to the existing production-built version instead of materializing them again.

This dramatically speeds up development because staging layers or intermediate models, which may take significant computation time, do not need to be rebuilt for every developer iteration.

The mechanism also prevents accidental overwrites of production objects because deferred references always point to a safe, isolated schema established by state comparison.

This concept is central to dbt’s “safe development via deferral,” enabling rapid experimentation while ensuring consistency between dev and prod environments.

**Compare nodes against a previous version of the same nodes.

Answer: state

The --state flag allows dbt to compare the current project against a previously generated manifest. This enables dbt to determine which models have changed, which tests must be re-run, and how the DAG differs between two versions.

State comparison is foundational to features such as Slim CI, deferred builds, and change-aware testing. dbt reads the state directory—typically containing artifacts from a production run—and examines differences in SQL, configurations, macros, or schema definitions.

This allows dbt to selectively build only modified nodes, increasing efficiency in CI/CD pipelines and reducing unnecessary warehouse costs. Additionally, state comparison is used for validating backward compatibility, testing versioned models, or enforcing governance rules such as model contracts.

By comparing nodes to their historical equivalents, teams can adopt modern engineering strategies such as impact analysis, regression detection, and selective deployment. This capability aligns with dbt’s focus on modular analytics engineering and automated change management.