dbt Analytics Engineering study plan

Explain how dbt turns a SELECT statement into a materialized relation (view/table/incremental/ephemeral) and when each is chosen.

Write a small dbt project with at least three models that ref each other to form a staging → intermediate → mart chain.

Configure a source and a model with unique and not_null tests, then run dbt build and interpret the results.

Distinguish ref, source, and var, and describe how dbt resolves them at compile time.

Design a star schema (facts and dimensions) and justify grain decisions for each fact table.

Implement an incremental model with a correct is_incremental() filter and a unique_key, then rerun it to confirm no duplicates.

Use dbt_utils or a surrogate-key pattern to build a dimension with a stable key.

Explain the trade-offs of ephemeral models and CTE-heavy intermediate models in a multi-layer project.

Define source freshness checks and configure warn/error thresholds for at least two sources.

Write singular tests (custom SQL) and generic tests (unique, not_null, relationships, accepted_values) and explain when each is appropriate.

Add a dbt_expectations or dbt_utils test to validate a range or row-count condition on a mart model.

Describe how test severity (warn vs error) affects dbt build and downstream models.

Configure a source in a YAML file with freshness, quoting, and column descriptions.

Use dbt source freshness and explain how it differs from a data test.

Implement a data-quality gate that fails the build when a critical test fails, and verify the failure path.

Document a strategy for handling late-arriving data and duplicates at the source layer.

Write model and column descriptions in schema YAML and generate the docs site with dbt docs generate.

Add exposures to document a downstream dashboard or report and verify it appears in the lineage graph.

Use dbt docs serve to trace lineage from a mart model back to its sources and identify orphan models.

Explain how the manifest and catalog power lineage and documentation in dbt.

Configure project variables and environment-specific targets (dev/prod) in profiles and dbt_project.yml.

Set model-level configs (materialized, tags, schema) in dbt_project.yml and override them per model.

Use selectors or tags to run a subset of models and explain how state:modified works with deferral.

Describe a governance approach for model ownership, naming conventions, and deprecation.

Set up a CI job (dbt Cloud or a CLI script) that runs dbt build on a pull request against a slim CI state.

Configure dbt deps, packages.yml, and a lockfile strategy for reproducible builds.

Explain how dbt run, dbt test, and dbt build differ and when to use each in a deployment pipeline.

Document a rollback and recovery approach for a failed production run.

Complete a timed 60-question mixed practice set across all four domains and log weak areas.

Re-implement one weak-area concept (e.g., incremental strategy or test severity) from memory.

Review the official dbt documentation pages for sources, tests, docs, and deployment configs.

Write a one-page cheat sheet of commands, config keys, and test types.