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

The correct answer is D: dbt run --select +1 my_model.

In dbt’s selection syntax, the + operator is used to include parents (upstream) and/or children (downstream) of a given node. When used as +my_model or my_model+, it means “include all ancestors” or “all descendants” respectively, with no limit on depth.

To limit how many levels of parents or children are included, dbt allows a numeric depth between the + and the resource name. +1 my_model (or my_model+1) means:

Select my_model

Plus only its first-degree parents (direct dependencies), and no further ancestors.

Because you want to materialize the models, you must use dbt run, not dbt compile. dbt compile only compiles SQL and does not create or update relations in the data platform.

So:

Option A (dbt run --select +my_model) would include all upstream ancestors, not just first-degree parents.

Options B and E use compile, so they don’t materialize.

Option C is just syntactically wrong.

Therefore, the only command that both runs models and limits selection to my_model plus its first-degree parents is dbt run --select +1 my_model.

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.

dbt model contracts enforce that the column names, data types, and number of columns defined in the contract exactly match the columns produced by the compiled SQL. If any column appears in one location (the contract or the SQL) but not in the other, dbt raises an enforcement error.

In this scenario, the error message shows:

ORDER_ID is missing in the model definition, meaning the SQL no longer contains a column named order_id, but the contract still expects it.

ORDER_KEY is missing in the contract, meaning the model SQL now contains a new column, but this column has not been added to the contract.

To fix the mismatch, you must remove columns from the contract that no longer exist in the SQL and add to the contract any new columns that now appear in the SQL.

Therefore:

Option A — Remove order_id from the contract — is correct because the column no longer exists in the model SQL.

Option D — Add order_key to the contract — is correct because the SQL now produces this column.

Options B and C incorrectly alter the wrong side of the definition, and Option E would create a mismatch in the opposite direction.

Thus, the correct fixes are A and D.

The correct answer is E: They are written using the {% macro %} wrapper.

Generic tests in dbt are implemented as macros, which means they must be defined using the {% macro %} Jinja syntax. These macros return a SQL query that evaluates to rows representing test failures. dbt executes these queries and marks the test as failed if any rows are returned. This requirement is explicitly documented: all generic tests are macros located in a test directory or within a package.

Option A is partially correct but not universal. Generic tests typically accept arguments such as model or column_name, but dbt does not require both or either. Custom tests can accept any parameters defined by the user. Option B is incorrect because tests do not require ref(); dbt injects the model relation for you when the test is executed. Option C is false because generic tests must be declared in YAML under a model or source for dbt to run them. Option D is incorrect because tests do not always need a column_name (e.g., relationship tests, row-count tests, table-level validations).

Therefore, the defining and universally required characteristic is that generic tests are written as macros, making E the correct answer.

Check your terminal or log file for the output from the most recent dbt run.

Look for specific error messages associated with the models that failed.

3️⃣ Isolate the problem by dbt run --select model_name to run a single model and confirm whether the issue is localized to that model.

4️⃣ Use dbt run --select model_name+ to run the model and its downstream dependencies, ensuring that your fix works across the DAG.

Brief Explanation

First, you always inspect the latest run output (step 1).

Then, identify the exact failing models and error messages (step 2).

Next, you reproduce the issue on the individual model to be sure the fix works locally (step 3).

Finally, you re-run the model plus its downstream dependencies to validate the fix across the DAG (step 4).

model_a contains an in-model config explicitly setting:

{{ config(materialized = "view") }}

In dbt, in-model config overrides project-level config, including folder-level defaults. Even though the staging/ folder is configured as:

staging:

+materialized: ephemeral

…this does not apply because the in-file config has higher precedence.

So model_a is materialized as a view.

A view in dbt is not persisted as data; instead, it stores the SQL definition. When a downstream query selects from that view, the database executes the underlying SQL at query time. As a result:

Queries run slower than selecting from a table, because the underlying computation happens each time.

But the data is always up to date, because views read directly from the current underlying tables.

This matches Option C.

Why the other options are wrong

A & D describe table behavior (faster queries, data only as fresh as last run). model_a is not materialized as a table.

B is incorrect because views are fully queryable; no error occurs.

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 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.

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.

For models not stored under finance:

✔ reporter and bi

For models inside the finance folder:

✔ finance, reporter, bi, and public

In dbt, grants configured at the root level apply to all models unless overridden by a more specific folder- or model-level configuration.

The root-level grant is:

+grants:

+select: ['reporter', 'bi']

This means all models by default are selectable by:

reporter

bi

Now the finance folder contains its own override:

finance:

+grants:

+select: ['finance']

When dbt merges grants, overrides do not replace the entire list—they add onto inherited grants unless explicitly cleared. Therefore, models inside the finance folder inherit the parent grants and add the finance grant.

So models under finance are accessible to:

finance

reporter

bi

and public (implicit default in most warehouses unless denied)

Models not inside finance use only the root grants:

reporter

bi

Thus the correct dropdown answers are:

reporter and bi

finance, reporter, bi, and public

The correct answers are B: tags and C: enabled.

dbt tests—both generic and singular—support a limited but clearly defined set of configurations. Two of the supported configurations are tags, which allow grouping and selecting tests using dbt’s selector syntax, and enabled, which can be set to true or false to control whether a test should run. These configurations are commonly used to manage large test suites, such as disabling slow tests in development or tagging tests for CI pipelines.

Option A (on_schema_change) is a model-only configuration and cannot be applied to tests.

Option D (materialized) applies only to models, not tests, since tests compile into queries that evaluate failures rather than physical objects.

Option E (persist_docs) applies to models and sources for documentation purposes. Tests do not support persisted documentation properties beyond what appears in test metadata.

dbt’s documentation makes clear that tests inherit only a small subset of configurations compared to models—primarily enabled, tags, severity, and custom test arguments. Among the options provided, only tags and enabled are valid and supported.

Thus, the correct answers are B and C.

The goal in this scenario is:

Select only models, tests, and snapshots that were modified in development, using:state:modified

Use already-built production objects whenever possible, so development does not need to rebuild everything. This is done with:--defer

Compare development code against production artifacts, which requires:--state

Run everything (models + tests + snapshots) in a single command. This is exactly what dbt build does.

dbt build = run models + run tests + run snapshots + run seeds.

Putting it all together, the correct command is:

dbt build --select state:modified --defer --state

This single command satisfies all requirements:

It runs only modified resources.

It uses the production state artifacts for comparison.

It defers to production-built models where possible.

It runs all resource types automatically.

Why the other options are incorrect:

A → Missing --state, so state:modified cannot function.

B → Splits run + test separately; question asks which single command you will invoke.

D → Missing --defer, which defeats the purpose of using production objects.

E → Also missing --defer.

Thus, Option C is the only correct answer.

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.

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.