Complete guide for dbt data transformation including models, tests, documentation, incremental builds, macros, packages, and production workflows

dbt Data Transformation A comprehensive skill for mastering dbt (data build tool) for analytics engineering. This skill covers model development, testing strategies, documentation practices, incremental builds, Jinja templating, macro development, package management, and production deployment workflows. When to Use This Skill Use this skill when: Building data transformation pipelines for analytics and business intelligence Creating a data warehouse with modular, testable SQL transformations Implementing ELT (Extract, Load, Transform) workflows Developing dimensional models (facts, dimensions) for analytics Managing complex SQL dependencies and data lineage Creating reusable data transformation logic across projects Testing data quality and implementing data contracts Documenting data models and business logic Building incremental models for large datasets Orchestrating dbt with tools like Airflow, Dagster, or dbt Cloud Migrating legacy ETL processes to modern ELT architecture Implementing DataOps practices for analytics teams Core Concepts What is dbt? dbt (data build tool) enables analytics engineers to transform data in their warehouse more effectively. It's a development framework that brings software engineering best practices to data transformation: Version Control : SQL transformations as code in Git Testing : Built in data quality testing framework Documentation : Auto generated, searchable data dictionary Modularity : Reusable SQL through refs and macros Lineage : Automatic dependency resolution and visualization Deployment : CI/CD for data transformations The dbt Workflow Key dbt Entities 1. Models : SQL SELECT statements that define data transformations 2. Sources : Raw data tables in your warehouse 3. Seeds : CSV files loaded into your warehouse 4. Tests : Data quality assertions 5. Macros : Reusable Jinja SQL functions 6. Snapshots : Type 2 slowly changing dimension captures 7. Exposures : Downstream uses of dbt models (dashboards, ML models) 8. Metrics : Business metric definitions Model Development Basic Model Structure A dbt model is a SELECT statement saved as a .sql file: Key Points: Models are SELECT statements only (no DDL) Use CTEs (Common Table Expressions) for readability Reference sources with {{ source() }} dbt handles CREATE/INSERT logic based on materialization The ref() Function Reference other models using {{ ref() }} : Benefits of ref(): Builds dependency graph automatically Resolves to correct schema/database Enables testing in dev without affecting prod Powers lineage visualization The source() Function Define and reference raw data sources: Source Features: Document raw data tables Test source data quality Track freshness with freshness config Separate source definitions from transformations Model Organization Recommended project structure: Naming Conventions: stg : Staging models (one to one with sources) int : Intermediate models (not exposed to end users) fct : Fact tables dim : Dimension tables Materializations Materializations determine how dbt builds models in your warehouse: 1. View (Default) Characteristics: Lightweight, no data stored Query runs each time view is accessed Best for: Small datasets, models queried infrequently Fast to build, slower to query 2. Table Characteristics: Full table rebuild on each run Data physically stored Best for: Small to medium datasets, heavily queried models Slower to build, faster to query 3. Incremental Characteristics: Only processes new data on subsequent runs First run builds full table Best for: Large datasets, event/time series data Fast incremental builds, maintains historical data Incremental Strategies: 4. Ephemeral Characteristics: Not built in warehouse Interpolated as CTE in dependent models Best for: Lightweight transformations, avoiding view proliferation No storage, compiled into downstream models Configuration Comparison Materialization Build Speed Query Speed Storage Use Case View Fast Slow None Small datasets, infrequent queries Table Slow Fast High Medium datasets, frequent queries Incremental Fast Fast High Large datasets, time series data Ephemeral N/A Varies None Intermediate logic, CTEs After initial full build Testing Schema Tests Built in generic tests defined in YAML: Built in Tests: unique : No duplicate values not null : No null values accepted values : Value in specified list relationships : Foreign key validation Custom Data Tests Create custom tests in tests/ directory: How it works: Test fails if query returns any rows Query should return failing records Can use any SQL logic Advanced Testing Patterns Testing with dbt utils Test Severity Levels Documentation Model Documentation Documentation Blocks Create reusable documentation: Reference documentation blocks: Generating Documentation Documentation Features: Interactive lineage graph (DAG visualization) Searchable model catalog Column level documentation Source freshness tracking Test coverage visibility Compiled SQL preview Documentation Best Practices 1. Document at all levels : Project, models, columns, sources 2. Explain business logic : Why transformations exist 3. Define grain explicitly : One row represents... 4. Note refresh schedules : How often data updates 5. Document assumptions : Edge cases, known issues 6. Link to external resources : Confluence, wiki, dashboards Incremental Models Basic Incremental Pattern Key Components: is incremental() : True after first run {{ this }} : References current model's table unique key : Column(s) for deduplication Incremental with Merge Strategy Merge Strategy Features: Updates existing records based on unique key Inserts new records Optional: Specify which columns to update/exclude Best for: Slowly changing data, updates to historical records Incremental with Delete+Insert Delete+Insert Strategy: Deletes all rows matching unique key Inserts new rows Best for: Aggregated data, full partition replacement More efficient than merge for bulk updates Handling Late Arriving Data Incremental with Partitioning Partition Benefits: Improved query performance Cost optimization (scan less data) Efficient incremental processing Better for time series data Full Refresh Capability Macros & Jinja Basic Macro Structure Usage: Reusable Data Quality Macros Date Spine Macro Dynamic SQL Generation Usage: Grant Permissions Macro Usage in hooks: Environment Specific Logic Audit Column Macro Usage: Jinja Control Structures Package Management Installing Packages Install packages: Using dbt utils Creating Custom Packages Project structure for a package: Package Versioning Production Workflows CI/CD Pipeline (GitHub Actions) Slim CI (Test Changed Models Only) Production Deployment Orchestration with Airflow dbt Cloud Integration Monitoring & Alerting Usage: Best Practices Naming Conventions Models: Tests: Macros: SQL Style Guide Performance Optimization 1. Use Incremental Models for Large Tables 2. Leverage Clustering and Partitioning 3. Reduce Data Scanned 4. Use Ephemeral for Simple Transformations Project Structure Best Practices 1. Layer Your Transformations 2. Modularize Complex Logic 3. Use Consistent File Organization Testing Strategy 1. Test at Multiple Levels 2. Use Appropriate Test Severity 3. Test Coverage Goals 100% of primary keys: unique + not null 100% of foreign keys: relationships tests All business logic: custom data tests Critical calculations: expression tests Documentation Standards 1. Document Every Model 2. Document Complex Logic 3. Keep Docs Updated Update docs when logic changes Review docs during code reviews Generate docs regularly: dbt docs generate 20 Detailed Examples Example 1: Basic Staging Model Example 2: Fact Table with Multiple Joins Example 3: Incremental Event Table Example 4: Customer Dimension with SCD Type 2 Example 5: Aggregated Metrics Table Example 6: Pivoted Metrics Using Macro Example 7: Snapshot for SCD Type 2 Example 8: Funnel Analysis Model Example 9: Cohort Retention Analysis Example 10: Revenue Attribution Model Example 11: Data Quality Test Suite Example 12: Slowly Changing Dimension Merge Example 13: Window Functions for Rankings Example 14: Union Multiple Sources Example 15: Surrogate Key Generation Example 16: Date Spine for Time Series Example 17: Custom Schema Macro Override Example 18: Cross Database Query Macro Usage: Example 19: Pre Hook and Post Hook Configuration Example 20: Exposure Definition Quick Reference Commands Essential dbt Commands Model Selection Syntax Resources Official dbt Documentation : https://docs.getdbt.com/ dbt Discourse Community : https://discourse.getdbt.com/ dbt GitHub Repository : https://github.com/dbt labs/dbt core dbt Package Hub : https://hub.getdbt.com/ dbt Learn : https://courses.getdbt.com/ dbt Style Guide : https://github.com/dbt labs/corp/blob/main/dbt style guide.md Analytics Engineering Guide : https://www.getdbt.com/analytics engineering/ dbt Slack Community : https://www.getdbt.com/community/join the community/ Skill Version : 1.0.0 Last Updated : October 2025 Skill Category : Data Engineering, Analytics Engineering, Data Transformation Compatible With : dbt Core 1.0+, dbt Cloud, Snowflake, BigQuery, Redshift, Postgres, Databricks

dbt-data-transformation