Free dbt Analytics Engineering Certification practice test

When target.name is 'prod', the {% if target.name == 'dev' %} condition evaluates to false, so the block renders nothing — no LIMIT clause appears in the compiled SQL. A) is wrong — the default argument n=100 only matters if the block executes; since the condition is false, n is irrelevant. B) is wrong — dbt does not substitute a zero; it simply renders an empty string. D) is wrong — macros with conditional logic on target.name are a standard, supported dbt pattern.

Tags are one of the few dbt configurations that are ADDITIVE rather than overriding. dbt merges tags from the project-level (dbt_project.yml), directory-level, and model-level config() blocks. So fct_revenue will have all three tags: finance, nightly, and critical. The order of listing (C vs D) is a minor detail — dbt does not guarantee order, and both C and D reflect the correct merged set; however, C is the canonical framing. A) is wrong — tags are not overridden; they are merged. B) is wrong — model-level tags are not discarded.

Both options C and D are syntactically correct macro definitions. Option D renders amount_cents / 100.0 correctly. Option C uses whitespace-control markers (-) which trim surrounding whitespace but produce the same SQL output. Option A would render amount_cents / 100.0 as well via the float filter, but the filter renders 100.0 not 100.0 — actually 100.0 is correct; however this is a valid alternative. Option B uses {% set %} which is for variable assignment, not macro definition, and is syntactically invalid for this purpose.

protected is the default access level for all models in dbt. Protected models can be referenced by any model within the same project (or package), but cannot be referenced from another project in a dbt Mesh setup. public is not the default — making everything public by default would defeat the purpose of governance. private is the most restrictive setting and must be set explicitly; it limits references to models in the same group. dbt does not error when access is omitted; it simply applies the default.

By default dbt looks for <model_name>_v<version>.sql, so fct_orders_v2.sql. If you want a different filename, you set defined_in on that version. The dotted, prefixed, and subdirectory-numbered forms are not conventions dbt recognizes; dbt would treat v2_fct_orders.sql as an unrelated model.

Only public models are visible across project boundaries, and protected (the default) already blocks cross-project refs — so marking just the three curated models public is minimal, low-effort, and correct. Leaving the curated models at the default would keep them invisible to marketing. Access is a model-level property; groups do not have an access setting that cascades. Splitting into a new project is a heavy, unnecessary restructuring when access modifiers solve it directly.

Correct: dbt builds a DAG from ref() edges and cannot topologically sort a cyclic graph, so it fails fast with a cycle error naming the nodes involved — before executing anything. Option 2 is wrong: dbt never orders by file name; it orders by the DAG, which is exactly what cannot be produced here. Option 3 is wrong: nothing executes, so no Database Error occurs. Option 4 is wrong: dbt does not silently break cycles.

Correct: 'depends on a node named X which was not found' is a compile-time (DAG resolution) error raised because dbt cannot resolve the ref() to any node in the project graph — almost always a misspelled model name ('stg_custmers'). Option 2 is wrong: a missing relation in the warehouse produces a database error at runtime, not a graph-resolution compilation error; dbt resolves refs from the manifest, not the warehouse. Option 3 is wrong: permission problems surface as Database Errors from the adapter, not Compilation Errors. Option 4 is wrong: circular dependencies produce a distinct 'Found a cycle' / dependency cycle error naming both nodes.

The + prefix operator selects a model and all of its upstream ancestors (parents, grandparents, etc.). This ensures all dependencies of orders are also run. Option A describes the orders+ syntax (suffix +), which selects descendants. Option C is wrong — +orders includes all ancestors at all depths, not just direct parents, and it does include orders itself. Option B is wrong — the + operator is a graph traversal operator, not a directory-scoped selector.

By default, dbt Cloud queues a new scheduled run if the previous run for the same job is still in progress, executing it as soon as the prior run completes. Option D is wrong — dbt Cloud does not cancel in-flight runs by default to start new ones. Option B is wrong — runs are not silently dropped; they are queued. Option C is wrong — dbt Cloud does not automatically parallelize runs of the same job by default; each job has its own run queue.

The + prefix operator, when placed BEFORE a selector, means 'include all ancestors (upstream nodes) of the selected nodes.' So +marts/finance selects all models in that path plus everything they depend on. Option C (marts/finance+) uses the + as a suffix, which selects all models in the path AND their downstream dependents — the opposite of what is needed. Option D (@marts/finance) selects the nodes, all their ancestors, AND all descendants of those ancestors — a much broader set than required. Option B is not valid dbt CLI syntax.

In dbt, the minimum required fields for a valid exposure are name (a unique identifier) and owner (which must include at least an email or name). type is also commonly required by dbt to be a recognized value, but strictly speaking the dbt schema enforces name and owner as the mandatory fields. Option A (name) and Option C (owner) are the two required fields. Option B (type) is strongly recommended and validated when present but has a default behavior; however in most dbt versions type is required in practice — but between A and C those are the fields the dbt docs list as required. Option D (maturity) is optional metadata. Option E (url) is optional. Option F (tags) is optional.

D is correct. dbt 1.8+ unit tests use given for mock inputs, where each input is a ref() or source() call with inline rows, and expect: rows: for the expected output. A is wrong: there is no seeds key for mock input data; inputs go under given. B is wrong: input must be a ref() or source() call such as ref('fct_orders_summary'), not a bare model name. Its optional config: tags block would be fine on its own. C is wrong: it uses keys that are not in the unit test schema (inputs, model: inside an input, data, expected).

When store_failures: true is set, dbt materializes the failing rows into a table or view in the specified schema (here audit), making them available for investigation — this is option A. The test result is still marked as a failure in dbt's run results; store_failures only adds persistence, it does not change the outcome — this is option D. Option C is wrong — dbt has no built-in retry logic for tests. Option E is wrong — store_failures has no effect on severity. Option B is wrong — failures are stored in the warehouse, not the local filesystem.

Correct: the identifier property decouples the dbt-facing source name from the physical table name, so setting identifier: accounts under the account entry is a one-line YAML change with no model edits and no duplicated nodes. Mass find-and-replace touches 40 files and risks missed references or merge conflicts for zero benefit. Creating a compatibility view in the raw database requires write access to raw data the analytics team typically should not modify and adds untracked warehouse objects. Adding a duplicate source entry creates two nodes pointing at the same data, splitting freshness and tests and inviting drift.

Correct: sources unlock freshness monitoring, YAML-attached tests/documentation on raw data, and graph operators/selectors such as source:stripe+. dbt never loads or ingests data into sources — extraction/loading is handled by tools outside dbt (that is the 'T' in ELT boundary). dbt also does not auto-create staging models; staging views must be written explicitly, though packages like codegen can scaffold them.

Correct: dbt deps downloads and installs the declared packages into the install path; without it, package macros and models are missing and compilation fails. dbt init scaffolds a brand-new project. dbt clean deletes target and package directories — useful before deps but it installs nothing. dbt parse parses the project and would itself fail if dependencies are not installed.

fct_orders+ (trailing +) selects fct_orders and all of its downstream dependents, giving a full picture of what would be affected by changes to the model. +fct_orders (leading +) selects all upstream parents of fct_orders, which answers "what does this model depend on" — not the impact downstream. dbt run --select fct_orders only runs that single model and doesn't reveal downstream impact. dbt test --select fct_orders runs tests on that model alone and also provides no downstream impact information.

The + suffix on state:modified+ instructs dbt to include all downstream descendants of every modified node. dim_customer is detected as modified (its compiled SQL changed), and then the + traverses the DAG forward to include all models that depend on it. state:modified without + would select only dim_customer itself. run_results.json statuses do not influence state-based selection. dbt does not recompile or re-hash downstream models simply because an upstream model changed — their own SQL hashes remain the same.

Model contracts enforce that the relation produced by a model matches the declared schema (column names, data types) — if the contract is violated, the run fails before bad data reaches downstream consumers. Schema tests (e.g., not_null, unique) complement contracts by validating data quality constraints at runtime. Together they protect both the structural interface and data integrity. Model versions help manage breaking changes but do not prevent unexpected schema drift on a single version. state:modified detects changes but does not enforce schema. Exposures document consumers but do not enforce anything.