Create dbt models following FF Analytics Kimball patterns and 2×2 stat model. This skill should be used when creating staging models, core facts/dimensions, or analytical marts. Guides through model creation with proper grain, tests, External Parquet configuration, and per-model YAML documentation using dbt 1.10+ syntax.
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).
Use this skill proactively when:
The FF Analytics project follows:
_<model>.yml file per modelarguments:Staging models normalize raw provider data.
Steps:
Identify source: Determine provider and dataset
Design grain: Define one row per...
Create SQL using assets/staging_template.sql:
stg_{provider}__{dataset}.sql{{ source('{provider}', '{dataset}') }}Create YAML using assets/staging_yaml_template.yml:
_stg_{provider}__{dataset}.ymlRun and test:
make dbt-run --select stg_{provider}__{dataset}
make dbt-test --select stg_{provider}__{dataset}
Fact tables capture measurable events/processes.
Steps:
Design grain: Define composite primary key (e.g., player_id + game_id + stat_name)
Map foreign keys: Join to conformed dimensions (dim_player, dim_team, etc.)
Create SQL using assets/fact_template.sql:
fact_{process}.sqlmaterialized='table', external=true, partition_by=['season','week']Create YAML using assets/fact_yaml_template.yml:
dbt_utils.unique_combination_of_columns for grain testRun 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
Dimensions provide descriptive context for facts.
Steps:
assets/dim_template.sql:
dim_{entity}.sqldbt_utils.generate_surrogate_key()SCD Type 2 pattern:
Marts provide wide-format, analytics-ready data.
2×2 Model Quadrants:
mart_real_world_actuals - NFL stats (actuals)mart_real_world_projections - Projected NFL statsmart_fantasy_actuals - Fantasy points (actuals, apply scoring rules)mart_fantasy_projections - Projected fantasy pointsSteps:
dim_scoring_rule{stat} * {points_per_stat}assets/mart_template.sql:
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
Real models from the codebase:
Templates for creating models:
CRITICAL: Every model must explicitly declare grain:
-- Grain: one row per...Staging models:
Fact tables:
Dimensions:
CRITICAL: Follow these two rules to avoid deprecation warnings:
data_tests: key (not tests:): dbt 1.5+ introduced data_tests: to distinguish from unit_tests:arguments:: dbt 1.10+ requires this for all generic tests with parameters# CORRECT - Column-level tests