dbt-Analytics-Engineering Exam Dumps - dbt Analytics Engineering Certification Exam

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

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.

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.

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.

The correct answer is B: By adding the configurations in dbt_project.yml within the package’s namespace.

dbt packages are installed as read-only code, meaning you should never modify files inside /dbt_packages, nor should you attempt to overwrite generated artifacts inside /target. Instead, dbt provides a clean and officially supported method for overriding or extending configurations on models from packages: using the dbt_project.yml file.

Each package comes with its own namespace, and users can override model-level configurations by referencing that namespace inside the models: section of dbt_project.yml. dbt merges project-level configurations with package-level defaults, and configurations defined in dbt_project.yml take precedence. This is the only correct and recommended way to override settings such as materializations, tags, schema, or custom config blocks on models imported through a package.

Option A is incorrect because packages.yml is used only to declare package dependencies—not to configure package models. Option C is incorrect because the ./target folder contains compiled and ephemeral artifacts that should never be edited. Option D is incorrect because modifying code in /dbt_packages violates dbt best practices and will be overwritten each time packages are reinstalled.

Therefore, overriding configurations must be done exclusively through dbt_project.yml.

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.

Operation

Correct Location

Aggregations (booked_hotel, booked_ride)

In a model dedicated to pre-aggregate data for reporting

Standardizing customer_id

In the first layer of models

Selecting customer_name

In the first layer of models

Joining employees

In a model between the first layer and final layer of models

In dbt’s recommended modeling framework, transformations should be centralized according to their purpose and level of abstraction. Basic cleaning and column standardization—such as shortening customers.id using left(customers.id, 12)—belongs in the first layer of models, commonly known as staging models. This layer is responsible for producing clean, consistent, analytics-ready fields. Selecting raw descriptive fields like customers.name also belongs in this layer.

Joins that enrich a dataset by combining cleaned staging outputs across domains—such as joining customers with employees—belong in intermediate models. These models unify business logic that sits between the raw staging layer and the final aggregations used for reporting.

Finally, aggregations like counting types of accommodations (booked_hotel and booked_ride) belong in marts or reporting models, especially when they involve summarization that downstream tools (dashboards, BI reports) will consume. This keeps heavy business logic centralized and reusable.