dbt Model Builder

Create complete dbt models for the Fantasy Football Analytics project following Kimball dimensional modeling and the 2×2 stat model (actuals/projections × real-world/fantasy).

When to Use This Skill

Use this skill proactively when:

• Creating new dbt models (staging, core, marts)
• User asks to "create a model for {entity/process}"
• User mentions dbt modeling, dimensional modeling, or Kimball patterns
• Adding analytics-ready views or transformations
• Implementing 2×2 model quadrants (actuals/projections, real-world/fantasy scoring)

dbt Modeling Approach

The FF Analytics project follows:

1. Kimball Dimensional Modeling - Facts, dimensions, conformed entities
2. 2×2 Stat Model - Separate facts for actuals vs projections
3. Per-Model YAML - One

   _<model>.yml

   file per model
4. External Parquet - Large models use external=true with partitioning
5. dbt 1.10+ syntax - Test arguments wrapped in

   arguments:

   block

Model Building Workflows

Workflow 1: Create Staging Model

Staging models normalize raw provider data.

Steps:

1. Identify source: Determine provider and dataset

2. Design grain: Define one row per...

3. Create SQL using

   assets/staging_template.sql

   :
   • Name:

     stg_{provider}__{dataset}.sql

   • Materialize as view
   • Select from

     {{ source('{provider}', '{dataset}') }}

   • Rename columns to standard names

4. Create YAML using

   assets/staging_yaml_template.yml

   :
   • Name:

     _stg_{provider}__{dataset}.yml

   • Document grain and source
   • Add not_null, unique tests for PKs
   • Add accepted_values for enums

5. Run and test:

   make dbt-run --select stg_{provider}__{dataset}
   make dbt-test --select stg_{provider}__{dataset}
   

Workflow 2: Create Fact Table

Fact tables capture measurable events/processes.

Steps:

1. Design grain: Define composite primary key (e.g., player_id + game_id + stat_name)

2. Map foreign keys: Join to conformed dimensions (

   dim_player

   ,

   dim_team

   , etc.)

3. Create SQL using

   assets/fact_template.sql

   :
   • Name:

     fact_{process}.sql

   • Config:

     materialized='table', external=true, partition_by=['season','week']

   • Join staging to dimensions for FK resolution
   • Select grain keys + measures

4. Create YAML using

   assets/fact_yaml_template.yml

   :
   • Document grain explicitly
   • Add

     dbt_utils.unique_combination_of_columns

     for grain test
   • Add relationship tests for all FKs
   • Add not_null for required measures

5. Run and test:

   make dbt-run --select fact_{process}
   make dbt-test --select fact_{process}
   

Critical: Fact tables MUST have grain uniqueness test with dbt 1.10+ syntax:

data_tests:
  - dbt_utils.unique_combination_of_columns:
      arguments:
        combination_of_columns:
          - column1
          - column2
      config:
        severity: error

Workflow 3: Create Dimension Table

Dimensions provide descriptive context for facts.

Steps:

1. Determine SCD type: Type 1 (replace) or Type 2 (historical tracking)
2. Design natural key: Business key for the entity
3. Create SQL using

   assets/dim_template.sql

   :
   • Name:

     dim_{entity}.sql

   • Generate surrogate key with

     dbt_utils.generate_surrogate_key()

   • For SCD Type 2: Add valid_from, valid_to, is_current
4. Create YAML:
   • Document grain: "one row per {entity}"
   • Add unique test on surrogate key
   • Add not_null on natural key
5. Run and test

SCD Type 2 pattern:

• Track changes over time with validity dates
• Include version_number for multiple versions
• Set is_current flag for latest version

Workflow 4: Create Analytical Mart (2×2 Model)

Marts provide wide-format, analytics-ready data.

2×2 Model Quadrants:

• mart_real_world_actuals

  - NFL stats (actuals)

• mart_real_world_projections

  - Projected NFL stats

• mart_fantasy_actuals

  - Fantasy points (actuals, apply scoring rules)

• mart_fantasy_projections

  - Projected fantasy points

Steps:

1. Select quadrant: Determine actuals vs projections, real-world vs fantasy
2. Pivot fact table: Convert long-form stats to wide columns
3. Join dimensions: Enrich with descriptive attributes
4. Apply scoring (fantasy quadrants only):
   • Join

     dim_scoring_rule

   • Calculate points:

     {stat} * {points_per_stat}

5. Create SQL using

   assets/mart_template.sql

   :
   • Partition by season
   • Wide format with one column per stat
6. Run and test

Example pivot:

SUM(CASE WHEN stat_name = 'passing_yards' THEN stat_value END) AS passing_yards,
SUM(CASE WHEN stat_name = 'passing_tds' THEN stat_value END) AS passing_tds

Resources Provided

references/

Real models from the codebase:

• example_staging_model.sql - stg_ktc_assets
• example_staging_yaml.yml - YAML with tests
• example_fact_model.sql - Fact table example
• example_dim_model.sql - Dimension example
• example_mart_model.sql - Mart example

assets/

Templates for creating models:

• staging_template.sql - Staging model SQL
• staging_yaml_template.yml - Staging YAML with tests
• fact_template.sql - Fact table SQL with FK joins
• fact_yaml_template.yml - Fact YAML with grain test
• dim_template.sql - Dimension SQL with SCD Type 2
• mart_template.sql - Mart SQL with pivot pattern

Best Practices

Grain Declaration

CRITICAL: Every model must explicitly declare grain:

• In SQL comments:

  -- Grain: one row per...

• In YAML description
• In grain uniqueness test (facts)

Testing Strategy

Staging models:

• not_null on all PKs
• unique on single-column PKs
• accepted_values on enums

Fact tables:

• dbt_utils.unique_combination_of_columns (grain test)
• relationships to all dimensions
• not_null on FKs and measures

Dimensions:

• unique on surrogate key
• not_null on natural key

dbt 1.10+ Test Syntax

CRITICAL: Follow these two rules to avoid deprecation warnings:

1. Use

   data_tests:

   key (not

   tests:

   ): dbt 1.5+ introduced

   data_tests:

   to distinguish from

   unit_tests:

2. Wrap test parameters in

   arguments:

   : dbt 1.10+ requires this for all generic tests with parameters

# CORRECT - Column-level tests
columns:
  - name: position
    data_tests:  # Use data_tests:, not tests:
      - not_null
      - accepted_values:
          arguments:  # Arguments must be nested
            values: ['QB', 'RB', 'WR', 'TE']

  - name: player_id
    data_tests:
      - not_null
      - relationships:
          arguments:  # Wrap to, field in arguments:
            to: ref('dim_player')
            field: player_id
          config:  # config: is sibling to arguments:
            where: "player_id > 0"

# CORRECT - Model-level tests
data_tests:  # Use data_tests:, not tests:
  - dbt_utils.unique_combination_of_columns:
      arguments:
        combination_of_columns:
          - player_key
          - game_id

# WRONG - Deprecated syntax (will cause warnings)
columns:
  - name: position
    tests:  # WRONG - should be data_tests:
      - accepted_values:
          values: ['QB', 'RB']  # WRONG - should be under arguments:

tests:  # WRONG - should be data_tests:
  - relationships:
      to: ref('dim_player')  # WRONG - should be under arguments:
      field: player_id

Key Points:

• Always use

  data_tests:

  (not

  tests:

  )

• arguments:

  wraps test parameters (to, field, values, combination_of_columns)

• config:

  is a sibling to

  arguments:

  , not nested inside

• not_null

  and

  unique

  have no arguments, use directly

External Parquet Configuration

Large models use External Parquet:

{{ config(
    materialized='table',
    external=true,
    partition_by=['season', 'week']
) }}

Naming Conventions

• Staging:

  stg_{provider}__{dataset}

• Facts:

  fact_{process}

  (e.g.,

  fact_player_stats

  )
• Dimensions:

  dim_{entity}

  (e.g.,

  dim_player

  )
• Marts:

  mart_{purpose}

  (e.g.,

  mart_fantasy_actuals_weekly

  )
• YAML:

  _<model_name>.yml

Integration with Other Skills

• data-ingestion-builder - Create staging models after adding providers
• data-quality-test-generator - Enhance testing beyond basics