DBT-Analytics-Engineering Practice Questions

Update int_order_items by replacing:

{{ source('tpch', 'orders') }} → {{ ref('stg_tpch_orders') }}

{{ source('tpch', 'lineitem') }} → {{ ref('stg_tpch_line_items') }}

In dbt’s recommended modeling pattern, sources should be referenced only in the staging layer. Downstream models (intermediate and mart/fact models) should reference staging models via ref(), not the raw source() directly. This keeps all raw-to-clean logic centralized in the staging layer and makes refactoring and documentation easier.

In the original DAG, int_order_items depends directly on tpch.orders and tpch.lineitem via source(), and staging models also depend on those same sources. That creates parallel paths from the sources and breaks the clean layered architecture.

By updating the int_order_items SQL to use:

from {{ ref('stg_tpch_orders') }}

join {{ ref('stg_tpch_line_items') }} ...

instead of:

from {{ source('tpch', 'orders') }}

join {{ source('tpch', 'lineitem') }} ...

you ensure that int_order_items sits entirely downstream of the staging layer. The new DAG becomes linear: sources → staging → intermediate (int_order_items) → any further marts. This improves modularity, reusability of cleaned logic, and makes the DAG easier to reason about and maintain.

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.

1. Can only be defined on SQL models

Answer: contract

2. Errors are returned before the model is built

Answer: contract

3. May be configured to customize severity

Answer: test

4. May be run on ephemeral models

Answer: test

dbt tests and contracts serve different purposes in ensuring data quality and model correctness. Tests evaluate data after it is produced, while contracts validate the structure of a model before dbt attempts to build it.

A model contract is schema-level enforcement that describes required columns, data types, and constraints. Contracts can only be applied to SQL models, not Python or ephemeral models. Because contracts validate the model’s schema before executing any SQL, dbt surfaces those errors before the model is built, preventing invalid schemas from being deployed.

In contrast, tests evaluate the data after dbt builds a model. Tests can be written generically (unique, not_null, relationships, accepted_values) or as custom SQL tests. They run after the model is materialized. Tests also allow severity configuration, enabling warnings instead of failures for less critical issues—something contracts do not support.

Tests also run on ephemeral models, because dbt expands ephemeral SQL inline within downstream models, allowing tests to still execute logically against the resulting compiled SQL. Contracts, however, do not apply because ephemeral models never materialize into database objects.

Thus:

“SQL-only” and “errors before build” → contract

“custom severity” and “run on ephemeral models” → test

If you want the next question formatted the same way, send it!

Information Type

Retrieved From

Singular tests

C.  

Column data types

A.  

Generic tests

B.  

SQL code

C.  

Column descriptions

B.  

Model dependencies

C.  

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 DA

G.  

(branch)

(commit)

(pull request)

In dbt development workflows, version control using Git is essential for ensuring collaborative, safe, and trackable changes to analytics code. The correct first step when making updates—such as adding new models—is to create a new Git branch. This isolates development work from the production (main) branch, preventing incomplete or experimental logic from affecting deployed transformations. Branching supports dbt’s modular development approach and aligns with best practices for analytics engineering.

Once the branch is created, the developer modifies SQL models, tests, macros, or documentation as required. To permanently record these modifications in Git, the developer must commit the changes. A commit serves as a snapshot of progress and creates an auditable history of transformations made to the project, enabling rollbacks, diffs, and peer review.

After development is complete, the developer submits a pull request (PR). The pull request triggers CI checks—often including dbt build, schema tests, and contract validations—to ensure code quality and identify impacts on downstream models. PRs allow team members to review and comment before changes merge into the main branch, enforcing governance, consistency, and reliability. This workflow embodies the engineering rigor dbt encourages: modular development, testing, versioning, and peer review.