dbt-Analytics-Engineering Questions and Answers

dbt Python models support a limited set of materializations because they rely on execution within the data platform’s Python compute engine (such as Snowpark for Snowflake, Dataproc for BigQuery, or Spark). These engines require models to materialize into actual relations—tables or views—in order to persist the results of Python-based transformations.

The ephemeral materialization, however, is fundamentally incompatible with this behavior. Ephemeral models do not create relations in the warehouse; instead, dbt inlines their SQL logic directly into downstream models. Since Python models cannot be inlined (they execute Python code, not SQL), dbt does not allow ephemeral Python models. dbt requires Python model outputs to be materialized as either:

table

view

incremental

Therefore, ephemeral is not supported for Python models, and attempting to configure a Python model as ephemeral will result in a compilation error.

The reason is straightforward: ephemeral logic depends on SQL compilation, while Python models depend on executing Python code in the data platform. Because these mechanisms are incompatible, dbt restricts Python models to relational materializations only.

Thus, the correct answer is No — ephemeral is not supported for Python models.

In dbt, when you configure a generic test like not_null in YAML, the configuration for that test must be a mapping, not another list item. The correct structure is:

tests:

- not_null:

config:

where: "state = 'completed'"

In your original YAML, you wrote:

tests:

- not_null:

- config:

where: "state = 'completed'"

The extra dash (- config) makes config an element of a list rather than a key under the not_null test. This breaks the expected shape of the test definition. When dbt parses the schema.yml, it fails to correctly interpret the patch for the stg_tasks model, which leads to the warning: “Did not find matching node for patch with name 'stg_tasks'…”.

By removing the extra dash and nesting config directly under not_null, dbt now reads this as a single generic test named not_null with a config block that passes the where argument. This allows dbt to correctly attach the test to the completed_at column of the stg_tasks model and eliminates the compiler warning.

Therefore, Option B is the only structurally valid configuration and is the correct answer.

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

The true root cause is not related to run hooks or regular expressions. The warning clearly indicates that dbt found a configuration path that does not match any actual model path inside the project. In this case, the config block uses the key models.jaffle-shop, which is incorrect because dbt model paths must match the folder structure and cannot contain hyphens unless the folder itself contains a hyphen. Since the project folder is named jaffle_shop (with an underscore), dbt cannot map the configuration to any model.

dbt evaluates configuration paths based on directory names under the models/ folder. A mismatch between the folder name and the configuration key causes dbt to ignore the configuration entirely. This results in the warning:

“Configuration paths exist in your dbt_project.yml file which do not apply to any resources.”

This warning is specifically triggered when dbt identifies unused configuration paths due to typos, incorrect nesting, or invalid identifiers. dbt does not interpret hyphens (-) as valid Python identifiers for resource names, so a config path like models.jaffle-shop will never be applied.

Therefore, the assertion that the cause is a "run hook with an incorrect regular expression" is incorrect. The actual root cause is that the configuration path uses the wrong name (jaffle-shop instead of jaffle_shop).

In dbt, the source() function resolves a source by its declared source name and table name, not by the physical schema or identifier in the warehouse. The YAML block defines a source named jaffle_shop, and under that source, a table named orders. The identifier: customer_orders field tells dbt that although the logical table name is orders, the actual physical object in the warehouse is named customer_orders.

dbt always expects the syntax:

{{ source(source_name, table_name) }}

Here, the correct reference uses jaffle_shop as the source name and orders as the table name because these are the logical names assigned in the YAML. dbt internally resolves the physical table name via the identifier field, so the model should not reference customer_orders directly.

Option A and B are incorrect because the first argument is not the schema; dbt does not use schemas in the source() call. Option D is incorrect because customer_orders is the warehouse identifier, not the logical table name recognized by dbt.

Therefore, the correct reference is:

{{ source('jaffle_shop', 'orders') }}

This ensures consistent modeling, dependency tracking, and accurate documentation.

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.

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.

Yes, this is a benefit of breaking large CTE-heavy SQL models into modular dbt models. According to dbt and Analytics Engineering best practices, modularity improves clarity, maintainability, and documentation quality. When CTEs remain embedded inside a single large SQL file, their purposes are often unclear, difficult to document, and hard for other developers to reuse. By extracting a logical CTE into its own model, dbt treats it as a first-class resource—meaning it can have its own description, tests, documentation, lineage, and metadata defined in YAML.

dbt’s documentation system allows each model to include a description explaining what the transformation does, the assumptions being made, and the expected behavior of the data. This aligns with the Analytics Engineering principle of creating self-documenting pipelines, where transformations are transparent and easier for downstream users to understand.

Additionally, modular models improve lineage visualization in the DAG. Instead of a single model hiding multiple transformation layers, a modular structure reveals how data flows through each intermediate step, helping both debugging and governance. Modularization also enables reusability—other models can reference the intermediate model rather than rebuilding the same logic through duplicated CTEs, supporting DRY (Don't Repeat Yourself) principles.

Therefore, moving CTEs into separate dbt models absolutely provides a documentation benefit and improves the overall engineering quality of the project.

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.

Drops and recreates a definition of a query which is executed at the time it's referenced

✅ view

Inserts or updates records of an existing object in the warehouse using a delta

✅ incremental

Code belonging to the model is inserted into the places the model is referenced

✅ ephemeral

Drops and recreates an object in the warehouse as stored results when using dbt run

✅ table

The correct answer is C: Underlying data must be accessible on your data platform.

dbt does not perform data ingestion or data loading. Instead, dbt operates after raw data is already available in your warehouse. This means that before dbt can build any models—whether staging, intermediate, or mart-layer models—the underlying source data must already exist and be accessible in the connected data platform (Snowflake, BigQuery, Redshift, Databricks, etc.). dbt uses SQL to transform existing relations; therefore, if the data platform cannot access the underlying tables or external sources, model execution will fail.

Option A is incorrect because sources do not need to be defined before building models. Models can be built without using sources at all. Source definitions are optional metadata and lineage declarations, not prerequisites.

Option B is incorrect because service accounts are not required; dbt can connect through any credential mechanism supported by the warehouse (OAuth, user accounts, tokens, etc.).

Option D is incorrect because dbt itself performs transformations on raw data—cleaning raw data beforehand is not required; in fact, that is one of dbt’s main responsibilities.

Thus, the only true prerequisite is that the warehouse must contain accessible underlying data.

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

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.