Practice Free dbt-Analytics-Engineering dbt Analytics Engineering Certification Exam Exam Questions Answers With Explanation

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.

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.

1. There is a config block at the top of the model with materialized = 'table'

?? Answer: False

2. The corresponding table already exists in the data warehouse

?? Answer: True

3. The incremental model was executed with the --full-refresh flag

?? Answer: False

4. The corresponding table does not already exist in the data warehouse

?? Answer: False

The is_incremental() macro in dbt is used within an incremental model to determine whether the current invocation should perform an incremental update or a full rebuild. It evaluates to True only during an incremental run when the target table already exists in the data warehouse and dbt is not performing a full refresh.

For is_incremental() to return True, two conditions must be met:

The model must be materialized as incremental, not table or view.Therefore, if the config specifies materialized = 'table', the model is not incremental, and the function evaluates to False.

The table already exists in the target schema.In this case, dbt will run the model in incremental mode, making is_incremental() return **True`.

If the incremental model is run with --full-refresh, dbt intentionally rebuilds the entire table, meaning is_incremental() evaluates to False, even if the table exists.

Lastly, if the target table does not exist yet, dbt must create it from scratch, so the run is treated as a full rebuild, not an incremental update, causing is_incremental() to evaluate to False.

Thus, the only scenario where is_incremental() returns True is when the table exists and the model is actually incremental.

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.

The purpose of this question is to determine how to validate that the input values in the state column support the business logic that determines the is_active field. Since the logic checks whether state = 'Active', it is critical that the state column only contains values that the business process expects. In the example shown, acceptable states appear to be: 'active', 'churned', and 'trial'.

The correct way to enforce this expectation is to apply an accepted_values test on the state column. This ensures that any unexpected state (e.g., 'inactive', 'pending', 'deleted', or NULL) will cause the test to fail, alerting the team that the upstream system is producing unexpected or invalid values. Additionally, adding not_null ensures every user record contains a valid state.

Option A is the only configuration that applies the accepted values test to the correct column (state) and reflects the expected domain of values.

Options B and D incorrectly apply tests to the derived column is_active, not the source column that needs validation. Option C only checks nullability and uniqueness, which does not validate the range of allowed values and thus does not protect the business logic.

Therefore, Option A is the only correct answer.

Information Type

Retrieved From

Singular tests

C. .sql files

Column data types

A. Data platform information schema

Generic tests

B. .yml configuration

SQL code

C. .sql files

Column descriptions

B. .yml configuration

Model dependencies

C. .sql files

The dbt docs command compiles metadata about your project by gathering information from three primary sources: your warehouse’s information schema, your YAML configuration files, and your SQL model files. Understanding which metadata comes from which source is essential for debugging and for effective documentation practices.

Singular tests live inside .sql files within the /tests directory. Since dbt renders these tests directly from SQL files, their definitions appear in documentation sourced from that location.

Column data types come from the warehouse itself. dbt introspects the data platform information schema to retrieve actual types because dbt does not infer or define column types—only the warehouse does.

Generic tests (e.g., unique, not_null, accepted_values) are declared in .yml files. These YAML definitions contain test configurations, descriptions, and parameters, which dbt uses to document and execute these tests.

SQL code for models is naturally sourced from .sql files where the models are defined. This includes logic such as SELECT statements, CTEs, and transformations.

Column descriptions are written exclusively in .yml files. dbt never extracts descriptions from SQL comments—only from YAML.

Model dependencies come from the ref() and source() calls inside .sql model files, which dbt parses to build the DAG.

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.

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 correct answer is C: Model groups can limit references by other models which aren’t in the same group.

According to the dbt documentation, dbt provides a formal access-control mechanism for models through model groups and the access property. Models can be assigned to groups, and each model can be marked as public, protected, or private. The purpose of this system is to create clear boundaries within a project, making it possible to control which models are allowed to reference others. Specifically, dbt enforces rules where models in a group can restrict references from models outside that group, which helps maintain modularity and prevents accidental coupling of unrelated data layers. This is extremely valuable in large analytics engineering projects where teams manage different domains or data products.

Option A is incorrect because dbt does not use Git user groups or any form of identity-based access control for model usage. Option B is incorrect because dbt does not integrate with warehouse roles for controlling model references—access is handled strictly within the dbt project’s metadata. Option D is incorrect because user emails or platform identities have no role in determining which models can reference others. dbt enforces usage rules only through its metadata-driven grouping and access configuration system.

The correct answers are A: run-operation and E: snapshot.

According to dbt’s retry documentation, the dbt retry command works by examining the artifacts of a previous invocation (manifest, run results) and then re-running failed nodes for commands that generate node executions. Only commands that write run results and maintain execution state can be retried. These include dbt run, dbt test, dbt seed, dbt snapshot, and dbt run-operation when the operation executes nodes or macros that generate runtime artifacts.

Option A is correct because run-operation supports retry when the macro being run triggers execution tracked in run results. Option E is correct because snapshots execute SQL against the warehouse and record stateful results, meaning dbt can retry failed snapshots using the retry mechanism.

Options B (parse), C (debug), and D (deps) do not create runnable nodes or execution results; they simply validate or install project resources. These commands do not produce retryable artifacts, so dbt retry cannot operate on them.

Thus, the only options that work with dbt retry from the list provided are run-operation and snapshot.

In dbt, Jinja runs at compile time and operates only on Python objects that exist in the Jinja context (variables, lists, dictionaries, etc.). The tasks inside your with clause:

with tasks as (

select * from {{ ref('stg_tasks') }}

)

defines a SQL CTE named tasks, not a Jinja variable. Jinja cannot iterate over a SQL CTE; it can only loop over a Python iterable that has been created or passed into the template. Because there is no Jinja variable named tasks defined with something like:

{% set tasks = ['task_a', 'task_b', 'task_c'] %}

the for task in tasks loop has nothing to iterate over. As a result, the entire loop body is effectively skipped during compilation, and the compiled SQL only contains:

select

user_id,

with no generated case when expressions.

Option A is incorrect because loop.last is only needed for formatting (e.g., commas), not for the loop to render. Option B is wrong because Jinja control structures correctly use {% %}, while {{ }} is for output. Option D is irrelevant to compilation; missing columns would cause a runtime database error, not an empty compiled block.

Therefore, the problem is that no Jinja variable tasks was defined, making C the correct answer.

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.

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 A: schema and C: tags.

In dbt, the dbt_project.yml file is the central configuration file that defines model-level settings. Under the models: section, you can specify a wide range of model configurations such as schema, materialized, tags, alias, and custom meta fields. The schema configuration allows you to control which database schema a model should be built in, giving analytics engineers the flexibility to organize models by domain or environment. The tags configuration is also valid under models: and is widely used to group models logically for selection, documentation, or orchestration workflows.

Option B (source) is incorrect because sources are defined under YAML files in the sources: section, not under models: in dbt_project.yml. Option D (test) is incorrect because tests must be defined in model or source YAML files, not inside the project configuration. Option E (target) is not a configuration that applies to models; rather, it refers to dbt runtime environments and cannot be configured under the models: block.

dbt's project configuration system ensures that model-level behavior is managed centrally and consistently, and schema and tags are two of the officially supported configuration keys under models:.