explore-dbt-models

Explore dbt Models — Data Platform Plugin

When NOT to use

• Running dbt commands (dbt compile, dbt run, dbt test) — this skill is read-only exploration.
• Committing to DataPlatformSnowflake — schema changes go through PRs only.
• Verifying deployed Snowflake state — use query-snowflake.

Purpose

Check the DataPlatformSnowflake dbt project before writing any SQL transformation. This skill prevents duplicate work and ensures new models are consistent with the existing lineage.

---

⛔ Critical Rules

1. Read-only: Never commit or push changes to DataPlatformSnowflake from this skill.
2. Check first: Before creating any table, view, or model — search the dbt project first.
3. Production via PRs only: Schema changes must go through a DataPlatformSnowflake PR, not direct DDL.
4. No PII in queries: If a model contains PII columns (check schema.yml for descriptions), follow query-snowflake PII rules when verifying the model's output in Snowflake.

---

Repository Structure

GitHub: ImagineLearning/DataPlatformSnowflake dbt root: dw_dbt/app/snowflake/

dw_dbt/app/snowflake/
├── dbt_project.yml
├── models/
│   ├── core/
│   │   ├── dimension/          ← user_dim, org_dim, etc.
│   │   ├── fact/               ← activity_attempt_fact, etc.
│   │   └── logs/
│   ├── il-math/
│   │   ├── dim_model/
│   │   └── staging/
│   ├── staging/                ← raw → staging
│   ├── sources/                ← {{ source() }} definitions
│   ├── cloud-events/
│   ├── il-events/
│   └── standalone/
├── macros/                     ← surrogate_key, generate_schema_name, etc.
├── tests/
├── snapshots/
└── analyses/

---

Access Methods

Method 1: Local Clone (preferred)

DPS_DBT_ROOT (exported in your shell config — ~/.bashrc / ~/.zshrc / PowerShell $PROFILE / Windows User env vars) must point at the dw_dbt/app/snowflake/ directory inside a DataPlatformSnowflake clone. If unset, fall back to Method 2.

Use the Grep tool (cross-platform) against $DPS_DBT_ROOT/models/. Common queries:

Goal	Pattern
Find a model by name	{table_name} in *.sql
Find downstream consumers	ref\('{model_name}'\) in *.sql
Find source-table consumers	source\('staging' in *.sql
Find a column producer	{column_name} in *.sql

Method 2: GitHub MCP (fallback)

mcp__plugin_il_github__search_code:
  query: "user_dim repo:ImagineLearning/DataPlatformSnowflake path:dw_dbt"

mcp__plugin_il_github__get_file_contents:
  owner: "ImagineLearning"
  repo:  "DataPlatformSnowflake"
  path:  "dw_dbt/app/snowflake/models/core/dimension/user_dim.sql"

---

Model Exploration Workflow

1. Locate the model in $DPS_DBT_ROOT/models/ (see Method 1 patterns above).
2. Read the model SQL. Standard dbt mechanics apply — {{ config(...) }}, {{ source() }}, {{ ref() }}, unique_key, incremental strategy. See https://docs.getdbt.com/reference/dbt-jinja-functions for syntax details.
3. Read schema.yml in the same directory for column descriptions, tests, and PII flags. The IL convention is one schema.yml per model directory.
4. Trace lineage by grepping for ref('{this_model}') across models/ to find downstream consumers; follow {{ source() }} references back to models/sources/.
5. Verify deployment (optional) via the query-snowflake skill: SELECT TABLE_NAME, ROW_COUNT, LAST_ALTERED FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME ILIKE '{model_name}'.

IL-specific gotchas

• surrogate_key macro (in macros/) is the IL convention for composite keys — prefer it over hand-written MD5(CONCAT(...)).
• generate_schema_name macro routes models to the right schema by directory; do not override per-model.
• il-math/ tree is a separate dbt sub-project for the math product — its models do NOT auto-build with the main project.

---

Schema Target Reference

Schema/path/materialization mapping: see dp:dbt-fix-conventions.

---

Related Skills

Need	Skill
Verify table in Snowflake	query-snowflake
GitHub code search	mcp__plugin_il_github__search_code