dbt Data Modeling Pattern

Create a dimensional data model using dbt with medallion architecture for [Business Domain].

Architecture Layers

1. Bronze Layer (Staging Models)

• Source data with minimal transformation
• models/staging/
• Clean column names, data types
• Light validation

2. Silver Layer (Intermediate Models)

• models/intermediate/
• Cleaned and joined data
• Business logic applied
• Deduplication and quality checks

3. Gold Layer (Mart Models)

• models/marts/
• Business-ready dimensional models
• Fact tables - Metrics and transactions
• Dimension tables - Descriptive attributes

dbt Best Practices

SQL Patterns

• Use CTEs (Common Table Expressions)
• Follow DRY principle (Don't Repeat Yourself)
• Use {{ ref() }} for model dependencies
• Use {{ source() }} for raw data

Testing

Schema Tests

• unique - No duplicate values
• not_null - Required fields
• relationships - Foreign key integrity
• accepted_values - Enum validation

Data Tests

• Custom business logic tests
• Validate calculations
• Check data quality metrics

Documentation

YAML Files

• Model descriptions
• Column-level documentation
• Test definitions
• Metadata and tags

Macros

• Create reusable logic
• DRY code across models
• Custom functions

Deliverables

Folder Structure

models/
├── staging/
│   └── stg_[source]__[table].sql
├── intermediate/
│   └── int_[entity]__[description].sql
└── marts/
    ├── core/
    │   ├── fct_[entity].sql
    │   └── dim_[entity].sql

Code Samples

• At least one model from each layer
• Complete with tests and documentation
• Production-ready SQL