[Data Build Tool] DBT - The Ultimate Guide | With CI/CD

High-level summary

This is an end-to-end DBT (data build tool) masterclass / tutorial demonstrating installation, project setup, model development, testing, snapshots (SCD Type 2), Jinja templating & macros, seeds, and CI/CD (Git + deployment) using DBT Core + Databricks (Free Edition). The instructor uses hands-on examples and explains why DBT is used in modern data engineering.

Why DBT (short)

DBT = Data Build Tool — focused on the transformation layer (ELT). It standardizes SQL-based transformations, enables modular/templated code (Jinja), and simplifies common data engineering patterns (incremental loads, SCDs, tests).

• DBT generates SQL that runs on your data platform (Databricks, Snowflake, BigQuery, Synapse, Redshift, Fabric, etc.). It does not provide compute — you still use the platform’s clusters/warehouses.

DBT product landscape and tooling

• DBT Core: open-source CLI (the backbone). You manage compute, CI/CD, and repositories.
• DBT Cloud: managed product on top of Core (UI, managed Git, scheduling).
• DBT Canvas: visual drag-and-drop transformation tool inside DBT Cloud (for non-coders).
• DBT adapters: platform-specific adapters (e.g., dbt-databricks, dbt-snowflake) — install the adapter for your platform.

Environment setup (demo uses Databricks Free Edition)

• Databricks Free Edition is used for the demo — easy and free to use.
• Local toolchain prerequisites:
  • VS Code + DBT Power User extension (Jinja/YAML support, autocompletion, local DAG/lineage)
  • Git
  • Python (DBT requires a compatible version; tutorial uses Python 3.12 — warns about 3.13 compatibility issues)
  • uv (an alternative for managing virtual envs and dependencies; used instead of poetry/pip)
• Common uv commands shown:
  • uv init
  • uv add <package> (e.g., uv add dbt-core, uv add dbt-databricks)
  • uv sync, uv remove, uv install -r requirements.txt

DBT project basics (commands and files)

• dbt init — initialize a project (creates models/, macros/, snapshots/, seeds/, tests/, dbt_project.yml)
• profiles.yml — critical file holding connection details (host, http_path, token, catalog/database, schema, threads, target). Default: ~/.dbt/profiles.yml; can be copied into project root for convenience.
• dbt debug — validate connection
• dbt run — build models (creates tables/views in the target data warehouse)
• dbt compile — compiled SQL placed under target/ (useful for inspecting generated CREATE/REPLACE statements)
• dbt clean — removes target/ files
• dbt build — runs models, snapshots, seeds, tests in the correct order (recommended for CI/CD)
• dbt test — run tests
• dbt seed — load CSV files from /seeds into the warehouse
• dbt snapshot — run snapshots (SCD Type 2)
• Node selection:
  • dbt run --select (or dbt run -m / -S) to run specific models, folders, or selectors
• Targets:
  • dbt run --target <target> — use different targets (dev/prod) configured in profiles.yml

Project structure & configuration

• dbt_project.yml configures project-level settings (model paths, default materializations, etc).
• Configuration precedence (lowest → highest):
  1. Project-level (dbt_project.yml)
  2. Folder-level properties.yml (YAML under a models directory)
  3. Model-level config block inside a model file
• Materializations: table, view, incremental, ephemeral — control how DBT writes objects.
• Use profile targets (dev/prod) and target.catalog, target.schema in YAML to avoid hard-coded database names and enable CI/CD.

Sources and models

• sources.yml: declare sources (catalog/database, schema, tables). Use source('source_name', 'table_name') rather than hard-coding object paths — improves lineage.
• Models: write SELECT statements only — DBT generates the CREATE/REPLACE statements during compile/run.
• Use ref('model_name') to reference other models; DBT resolves catalog/schema/name.
• Recommended medallion layout: models/bronze, models/silver, models/gold (and models/sources).

DBT Power User VS Code extension

• Maps .sql/.yml files to a Jinja dialect, provides autocompletion, lineage preview, run/compile actions — makes local development feel more like DBT Cloud.

Testing

• Generic tests (column-level, defined in properties YAML):
  • Built-in: not_null, unique, accepted_values, relationships
  • Multiple tests can be applied per column.
  • Configure severity (error vs warn) in test YAML: config: severity: WARN.
• Singular tests (SQL tests):
  • Place SQL files in tests/. Any SELECT that returns rows → test fails (tests typically SELECT rows WHERE ).
• Custom generic tests:
  • Build Jinja macros under tests/generic/ and reuse like built-in tests; macros accept model/column args and return SQL.
  • Example: a custom generic non-negative test implemented as a reusable macro.

Seeds and analysis

• Seeds: CSV files placed in /seeds; loaded by dbt seed into the data platform as tables — good for small lookup/mapping/static tables. Reference them with ref().
• analysis/: query files for ad-hoc queries or saved analyses — not executed during dbt run by default.

Jinja templating

• Jinja is the templating language used by DBT to make SQL dynamic:
  • Variables: {% set var = 'value' %} and {{ var }}
  • Control structures: loops and conditionals ({% for ... %}, {% if ... %})
  • Use loop.last to avoid trailing commas when generating SQL lists.
• Macros: functions stored in macros/, called with {{ my_macro(arg1, arg2) }}. Used for DRY, reusable transformations, and custom generic tests.

Snapshots (SCD Type 2)

• DBT snapshots record changes over time to mutable tables (SCD Type 2).
• Modern snapshot config uses a YAML snapshot file with:
  • strategy: timestamp (or check)
  • unique_key
  • updated_at column
  • Optional config such as dbt_valid_to_current
• Demo flow: create a small source table → deduplicate with a model (ROW_NUMBER) → run snapshot. DBT produces valid_from / valid_to history for SCD Type 2 behavior.
• Run snapshots via dbt snapshot or as part of dbt build.

Lineage & compiled SQL

• DBT generates a DAG (lineage). Compiled SQL is under target/ and useful for debugging.
• The VS Code extension shows lineage; DBT Cloud also visualizes it.

CI/CD & deployment workflow

• Git workflow: feature branch → PR → merge to main.
  • Example: git init, git add, git commit, git switch -c feature/..., then merge back to main.
• Parameterize profiles.yml with multiple targets (dev, prod) and deploy with dbt build --target prod (no code changes required beyond profiles/target mapping).
• Recommended pipeline:
  1. Local dev → feature branch
  2. Run tests locally (dbt test)
  3. Push and open PR
  4. CI runs dbt build (with the chosen target) to deploy to the environment
  5. Monitor dbt test results and logs
• Push the project to a remote Git repo (GitHub) to enable CI pipelines (recommended homework).

Commands and snippets (key ones)

# uv / package management
uv init
uv add dbt-core
uv add dbt-databricks

# dbt commands
dbt init
dbt debug
dbt run
dbt run --select <model|dir|tag>
dbt compile
dbt test
dbt seed
dbt snapshot
dbt build
dbt clean

# dbt helpers
# use ref('model') and source('source_name','table_name') in SQL

• For custom tests: create a macro under tests/generic/<test>.sql and reference it in properties.yml.

Demo specifics

• Data files come from the instructor’s GitHub repo (CSV files: fact_sales, dim_customer, dim_product, dim_store, dim_date, etc.).
• Instructor demonstrates uploading CSVs to Databricks and creating tables.
• Uses Databricks SQL warehouses and stores connection details (host, http_path, token) in profiles.yml.
• Builds bronze models (raw copies), a silver model (joined/enriched), seeds, custom macros, tests, and snapshots.
• Shows compiling and viewing the CREATE statements DBT generates.

Best practices & tips

• Use Jinja templating and macros for DRY, reusable SQL.
• Declare sources with YAML and use source() instead of hard-coding object paths for better lineage.
• Put common configs in dbt_project.yml; use properties.yml for folder-level model configs; use model-level config blocks for overrides.
• Use profiles.yml targets and target.catalog / target.schema to parameterize environments for CI/CD.
• Use dbt build as the single command for CI/CD orchestration.
• Keep Python compatibility in mind when installing DBT.
• Use Git feature-branch workflow and push the repository to GitHub for CI.

What the tutorial includes / what you learn (checklist)

• Set up Databricks Free workspace and upload source CSVs
• Install tools: VS Code, Git, Python 3.12, uv, DBT Core + dbt-databricks adapter
• Initialize DBT project and profiles.yml
• Use DBT Power User extension for local development
• Declare sources.yml and use source() function
• Build models with ref() and materializations (table/view)
• Configure materialization at project / folder / model levels (precedence)
• Run dbt run, dbt compile, view compiled SQL in target/
• Add and run generic tests (not_null, unique, accepted_values, relationships)
• Add singular tests (custom SQL-based tests)
• Create custom generic tests via macros under tests/generic/
• Use seeds (CSV → dbt seed)
• Use Jinja (variables, loops, conditionals), write macros and call them
• Build snapshots for SCD Type 2 (dbt snapshot)
• Use dbt build for CI/CD; deploy to prod using profiles.yml target parameter
• Git commit/branching workflow and push to GitHub

Links / tools referenced

• DBT Core documentation (official)
• DBT Cloud & DBT Canvas
• Databricks Free Edition
• DBT Power User VS Code extension
• GitHub repo with CSV test data (instructor’s repo)

Main speaker / sources

• Instructor: “Anlamba” / “An Lamba” (channel appears as “Anlama”). Primary references: DBT official docs (core, snapshots, tests), Databricks connection docs, DBT Power User extension, and the instructor’s GitHub repository.

Extras available

• Practical checklist (commands + files to copy/paste) to reproduce the exact demo
• Compact cheat-sheets:
  • DBT command cheat sheet
  • profiles.yml example
  • Sample sources.yml, model, and properties.yml snippets