Pass the dbt Labs Analytics Engineers dbt-Analytics-Engineering Questions and answers with CertsForce

The correct answers are B: writing and using dbt macros and D: using dbt packages.

dbt strongly encourages DRY (Don't Repeat Yourself) principles, and two of the core mechanisms that support reusable logic are macros and packages. Macros allow you to write Jinja-powered reusable functions that can generate SQL statements dynamically, reducing duplication across models, tests, and project logic. Macros can encapsulate filters, joins, auditing logic, timestamps, and more—allowing developers to centralize logic in one place while referencing it across many models.

Packages extend this concept even further by allowing entire sets of macros, models, tests, and utilities to be imported into a project. Packages like dbt-utils contain widely used generic macros that help standardize transformations and testing. Using packages ensures consistent logic across teams and eliminates the need to rewrite common transformations.

Option A contradicts DRY principles because copy/pasting increases maintenance burden. Option C is not a mechanism for reusing logic; singular tests validate logic but do not reduce duplication. Option E simply changes a model’s materialization and does not support code reuse.

Thus, macros and packages are the only correct dbt mechanisms that provide reusable, modular, DRY logic.

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

dbt includes built-in support for model versioning and controlled deprecation. When you introduce a new version of a model, dbt will only warn about the old version being removed if the old version explicitly includes a deprecation_date configuration. This parameter signals to dbt that the model is scheduled for removal after a certain date, allowing dbt to produce structured warnings to users and downstream systems. These warnings serve as a formal notice that the old version should no longer be referenced and will eventually be removed.

Option A, simply creating a new version, does not trigger warnings by itself. dbt allows multiple versions to coexist without any deprecation messaging unless you explicitly configure it. Option C (enabled: false) disables the model entirely, which is not a graceful deprecation strategy and does not result in version-removal warnings. Option D (adding “deprecated” in the description) is only informational text and has no functional effect on dbt’s deprecation system.

Thus, only B—setting deprecation_date on the old version—activates dbt’s official deprecation warning mechanism, making it the correct answer.

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.

In dbt, grants can be configured globally, at the project level, or directly inside a model using the config() function. When a grant is set in dbt_project.yml, it becomes a base definition, and individual models may extend (append to) or override that configuration.

According to dbt documentation, prefixing a grant with a plus sign (+) means “extend the list defined in higher-level configurations.”

Thus, if the project-level config already sets:

select: ['reporter']

…and a model needs to add an additional role (here, bi) without removing the existing one, the correct syntax is:

{{ config(grants = { '+select': ['bi'] }) }}

This tells dbt: “Take the existing grant list (reporter) and append bi to it.”

Option B is incorrect because '+bi' is not a valid grants syntax.

Option C is invalid because grants do not use an inherits parameter.

Option D is invalid because include: is not a grants configuration key.

Therefore, Option A correctly applies dbt’s documented merging behavior for grants.

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, source freshness can be defined at the source level (affecting all tables) or overridden at the individual table level. When freshness is declared at the source root, dbt applies the freshness policy to every table within that source unless a specific table opts out. The official dbt documentation states that setting:

freshness: null

at the table level disables freshness checks for that specific table, even when the source itself defines default freshness rules. This is the only documented way to exclude an individual table from a source freshness policy.

Option B is incorrect because tables can override and disable freshness even when the parent source defines it. dbt’s configuration hierarchy supports overriding child-level settings on a per-resource basis.

Option C (loaded_at_field: null) does not disable freshness—it simply removes the loaded_at_field, which would cause dbt to error because a freshness check requires a timestamp field to operate.

Option D (error_after: null) only removes the error threshold but does not disable freshness. The table would still participate in freshness checks and could trigger warnings.

Thus, the correct and dbt-supported method to exclude a table from freshness is to explicitly set:

freshness: null

making A the correct answer.

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.

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.