메뉴
Generates SQL validation notebooks for dbt PR changes with before/after comparison queries.
기본적으로 소스를 먼저 확인하는 Prompt가 선택됩니다. 직접 명령으로 전환하거나 로컬 사본을 다운로드할 수도 있습니다.
설치 여부를 결정하기 전에 SKILL.md와 SkillsMP에 표시된 보조 파일을 읽어 보세요.
Codex 또는 Claude로 설치 이 Prompt를 복사해 Codex, Claude 또는 다른 어시스턴트에 붙여 넣으면 Skill 페이지를 검토하고 설치를 진행할 수 있습니다.
직접 명령은 검토 Prompt를 거치지 않습니다. 실행하기 전에 소스를 확인하세요.
npx skills add https://github.com/phoroth/AGENTIC --skill monte-carlo-validation-notebook명령은 한 줄로 유지됩니다. 복사하기 전에 가로로 스크롤해 전체 내용을 확인하세요.
로컬 사본을 원하시나요? SkillsMP에서 현재 제공할 수 있는 파일을 다운로드하세요.
Development skill from AGENTIC (https://github.com/phoroth/AGENTIC)
Development skill from AGENTIC (https://github.com/phoroth/AGENTIC)
Generate clean, human-sounding, SEO-optimized WordPress blog posts with optional Yoast metadata, JSON-LD schema markup, and image SEO planning. Supports modular batch output.
SKILL.md 표시 중
SOC 직업 분류 기준
| name | monte-carlo-validation-notebook |
| description | Generates SQL validation notebooks for dbt PR changes with before/after comparison queries. |
| category | data |
| risk | safe |
| source | community |
| source_repo | monte-carlo-data/mc-agent-toolkit |
| source_type | community |
| date_added | 2026-04-08 |
| author | monte-carlo-data |
| tags | ["data-observability","validation","dbt","monte-carlo","sql-notebook"] |
| tools | ["claude","cursor","codex"] |
Tip: This skill works well with Sonnet. Run
/model sonnetbefore invoking for faster generation.
Generate a SQL Notebook with validation queries for dbt changes.
Arguments: $ARGUMENTS
Use this skill when the user wants to validate dbt model or snapshot changes with Monte Carlo SQL Notebook queries, either from a GitHub PR or a local dbt repository.
Parse the arguments:
--mc-base-url <URL> — defaults to https://getmontecarlo.com--models <model1,model2,...> — comma-separated list of model filenames (without .sql extension) to generate queries for. Only these models will be included. By default, all changed models are included up to a maximum of 10.Prerequisites:
gh (GitHub CLI) — required for PR mode. Must be authenticated (gh auth status).python3 — required for helper scripts.pyyaml — install with pip3 install pyyaml (or pip install pyyaml, uv pip install pyyaml, etc.)Note: Generated SQL uses ANSI-compatible syntax that works across Snowflake, BigQuery, Redshift, and Athena. Minor adjustments may be needed for specific warehouse quirks.
This skill includes two helper scripts in ${CLAUDE_PLUGIN_ROOT}/skills/monte-carlo-validation-notebook/scripts/:
resolve_dbt_schema.py - Resolves dbt model output schemas from dbt_project.yml routing rules and model config overrides.generate_notebook_url.py - Encodes notebook YAML into a base64 import URL and opens it in the browser.Auto-detect mode from the target argument:
:// or github.com) -> PR mode./path/to/repoThis command generates a SQL Notebook containing validation queries for dbt changes. The notebook can be opened in the MC Bridge SQL Notebook interface for interactive validation.
The output is an import URL that opens directly in the notebook interface:
<MC_BASE_URL>/notebooks/import#<base64-encoded-yaml>
Key Features:
text parameters (prod_db and dev_db) for selecting databasesdbt_project.yml and model configs{{prod_db}}.<SCHEMA>.<TABLE>{{prod_db}} vs {{dev_db}}Key structure:
version: 1
metadata:
id: string # kebab-case + random suffix
name: string # display name
created_at: string # ISO 8601
updated_at: string # ISO 8601
default_context: # optional database/schema context
database: string
schema: string
cells:
- id: string
type: sql | markdown | parameter
content: string # SQL, markdown, or parameter config (JSON)
display_type: table | bar | timeseries
Parameter cells allow defining variables referenced in SQL via {{param_name}} syntax:
- id: param-prod-db
type: parameter
content:
name: prod_db # variable name
config:
type: text # free-form text input
default_value: "ANALYTICS"
placeholder: "Prod database"
display_type: table
Parameter types:
text: Free-form text input (used for database names)schema_selector: Two dropdowns (database -> schema), value stored as DATABASE.SCHEMAdropdown: Select from predefined optionsGenerate a SQL Notebook with validation queries based on the mode and target.
The approach differs based on mode:
Extract the PR number and repo from the target URL.
https://github.com/monte-carlo-data/dbt/pull/3386 -> owner=monte-carlo-data, repo=dbt, PR=3386Fetch PR metadata using gh:
gh pr view <PR#> --repo <owner>/<repo> --json number,title,author,mergedAt,headRefOid
gh pr view <PR#> --repo <owner>/<repo> --json files --jq '.files[].path'
gh pr diff <PR#> --repo <owner>/<repo>
Filter the changed files list to only .sql files under models/ or snapshots/ directories (at any depth — e.g., models/, analytics/models/, dbt/models/). These are the dbt models to analyze. If no model SQL files were changed, report that and stop.
For each changed model file, fetch the full file content at the head SHA:
gh api repos/<owner>/<repo>/contents/<file_path>?ref=<head_sha> --jq '.content' | python3 -c "import sys,base64; sys.stdout.write(base64.b64decode(sys.stdin.read()).decode())"
dbt_project.yml. Try these paths in order until one succeeds:gh api repos/<owner>/<repo>/contents/<dbt_root>/dbt_project.yml?ref=<head_sha> --jq '.content' | python3 -c "import sys,base64; sys.stdout.write(base64.b64decode(sys.stdin.read()).decode())"
Common <dbt_root> locations: analytics, . (repo root), dbt, transform. Try each until found.
Save dbt_project.yml to /tmp/validation_notebook_working/<PR#>/dbt_project.yml.
Change to the target directory.
Get current branch info:
git rev-parse --abbrev-ref HEAD
Detect base branch - try main, master, develop in order, or use upstream tracking branch.
Get the list of changed SQL files compared to base branch:
git diff --name-only <base_branch>...HEAD -- '*.sql'
Filter to only .sql files under models/ or snapshots/ directories (at any depth — e.g., models/, analytics/models/, dbt/models/). If no model SQL files were changed, report that and stop.
Get the diff for each changed file:
git diff <base_branch>...HEAD -- <file_path>
Read model files directly from the filesystem.
Find dbt_project.yml:
find . -name "dbt_project.yml" -type f | head -1
local-<branch-name>-<timestamp>Local: <branch-name>git config user.nameAfter filtering to .sql files under models/ or snapshots/:
If --models was specified: Filter the changed files list to only include models whose filename (without .sql extension, case-insensitive) matches one of the specified model names. If any specified model is not found in the changed files, warn the user but continue with the models that were found. If none match, report that and stop.
Model cap: If more than 10 models remain after filtering, select the first 10 (by file path order) and warn the user:
⚠️ <total_count> models changed — generating validation queries for the first 10 only.
To generate for specific models, re-run with: --models <model1,model2,...>
Skipped models: <list of skipped model filenames>
For EACH changed dbt model .sql file, parse and extract:
Output table name -- Derive from file name:
<any_path>/models/<subdir>/<model_name>.sql -> table is <MODEL_NAME> (uppercase, taken from the filename)Output schema -- Use the schema resolution script:
Setup: Save dbt_project.yml and model files to /tmp/validation_notebook_working/<id>/ preserving paths:
/tmp/validation_notebook_working/<id>/
+-- dbt_project.yml
+-- models/
+-- <path>/<model>.sql
Run the script for each model:
python3 ${CLAUDE_PLUGIN_ROOT}/skills/monte-carlo-validation-notebook/scripts/resolve_dbt_schema.py /tmp/validation_notebook_working/<id>/dbt_project.yml /tmp/validation_notebook_working/<id>/models/<path>/<model>.sql
Error handling: If the script fails, STOP immediately and report the error. Do NOT proceed with notebook generation if schema resolution fails.
Output: The script prints the resolved schema (e.g., PROD, PROD_STAGE, PROD_LINEAGE)
Note: Do NOT manually parse dbt_project.yml or model configs for schema -- always use the script. It handles model config overrides, dbt_project.yml routing rules, PROD_ prefix for custom schemas, and defaults to PROD.
Config block -- Look for {{ config(...) }} and extract:
materialized -- 'table', 'view', 'incremental', 'ephemeral'unique_key -- the dedup key (may be a string or list)cluster_by -- clustering fields (may contain the time axis)Core segmentation fields -- Scan the entire model SQL for fields likely to be business keys:
*_id (e.g., account_id, resource_id, monitor_id) that appear in JOIN ON, GROUP BY, PARTITION BY, or unique_keyTime axis field -- Detect the model's time dimension (in priority order):
is_incremental() block: field used in the WHERE comparisoncluster_by config: timestamp/date fieldsingest_ts, created_time, date_part, timestamp, run_start_time, export_ts, event_created_timeIf no time axis is found, skip time-axis queries for this model.
Parse the diff hunks for this file. Classify each changed line:
unique_key in config was modified, note both old and new values.Classify each model as new or modified based on the diff:
new file mode → classify as newThis classification determines which query patterns are generated in Phase 3.
Note: For new models, Phase 2b diff analysis is skipped (there is no "before" to compare against). Phase 2a metadata extraction still applies.
For each changed model, generate the applicable queries based on its classification (new vs modified).
CRITICAL: Parameter Placeholder Syntax
Use double curly braces {{...}} for parameter placeholders. Do NOT use ${...} or any other syntax.
Correct: {{prod_db}}.PROD.AGENT_RUNS
Wrong: ${prod_db}.PROD.AGENT_RUNS
Table Reference Format:
{{prod_db}}.<SCHEMA>.<TABLE_NAME> for prod queries{{dev_db}}.<SCHEMA>.<TABLE_NAME> for dev queries<SCHEMA> is hardcoded per-model using the output from the schema resolution scriptFor new models, all queries target {{dev_db}} only. No comparison queries are generated since no prod table exists.
Trigger: Always.
SELECT COUNT(*) AS total_rows
FROM {{dev_db}}.<SCHEMA>.<TABLE_NAME>
Trigger: Always.
SELECT *
FROM {{dev_db}}.<SCHEMA>.<TABLE_NAME>
LIMIT 20
Trigger: Always.
SELECT
<segmentation_field>,
COUNT(*) AS row_count
FROM {{dev_db}}.<SCHEMA>.<TABLE_NAME>
GROUP BY <segmentation_field>
ORDER BY row_count DESC
LIMIT 100
Trigger: Always for new models (verify unique_key constraint from the start).
SELECT
COUNT(*) AS total_rows,
COUNT(DISTINCT <key_fields>) AS distinct_keys,
COUNT(*) - COUNT(DISTINCT <key_fields>) AS duplicate_count
FROM {{dev_db}}.<SCHEMA>.<TABLE_NAME>
SELECT <key_fields>, COUNT(*) AS n
FROM {{dev_db}}.<SCHEMA>.<TABLE_NAME>
GROUP BY <key_fields>
HAVING COUNT(*) > 1
ORDER BY n DESC
LIMIT 100
Trigger: Always. Checks all output columns since everything is new.
SELECT
COUNT(*) AS total_rows,
SUM(CASE WHEN <col1> IS NULL THEN 1 ELSE 0 END) AS <col1>_null_count,
ROUND(100.0 * SUM(CASE WHEN <col1> IS NULL THEN 1 ELSE 0 END) / NULLIF(COUNT(*), 0), 2) AS <col1>_null_pct,
SUM(CASE WHEN <col2> IS NULL THEN 1 ELSE 0 END) AS <col2>_null_count,
ROUND(100.0 * SUM(CASE WHEN <col2> IS NULL THEN 1 ELSE 0 END) / NULLIF(COUNT(*), 0), 2) AS <col2>_null_pct
-- repeat for each output column
FROM {{dev_db}}.<SCHEMA>.<TABLE_NAME>
Trigger: Model is materialized='incremental' OR a time axis field was identified.
SELECT
CAST(<time_axis> AS DATE) AS day,
COUNT(*) AS row_count
FROM {{dev_db}}.<SCHEMA>.<TABLE_NAME>
WHERE <time_axis> >= CURRENT_TIMESTAMP - INTERVAL '14' DAY
GROUP BY day
ORDER BY day DESC
LIMIT 30
For modified models, single-table queries use {{prod_db}} and comparison queries use both.
Trigger: Always.
SELECT COUNT(*) AS total_rows
FROM {{prod_db}}.<SCHEMA>.<TABLE_NAME>
Trigger: Always.
SELECT *
FROM {{prod_db}}.<SCHEMA>.<TABLE_NAME>
LIMIT 20
Trigger: Always.
SELECT
<segmentation_field>,
COUNT(*) AS row_count
FROM {{prod_db}}.<SCHEMA>.<TABLE_NAME>
GROUP BY <segmentation_field>
ORDER BY row_count DESC
LIMIT 100
Trigger: Changed fields found in Phase 2b. Exclude added columns (from "New columns" in Phase 2b) — only include fields that exist in prod.
SELECT
<changed_field>,
COUNT(*) AS row_count,
ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER(), 2) AS pct
FROM {{prod_db}}.<SCHEMA>.<TABLE_NAME>
GROUP BY <changed_field>
ORDER BY row_count DESC
LIMIT 100
Trigger: JOIN condition changed, unique_key changed, or model is incremental.
SELECT
COUNT(*) AS total_rows,
COUNT(DISTINCT <key_fields>) AS distinct_keys,
COUNT(*) - COUNT(DISTINCT <key_fields>) AS duplicate_count
FROM {{dev_db}}.<SCHEMA>.<TABLE_NAME>
SELECT <key_fields>, COUNT(*) AS n
FROM {{dev_db}}.<SCHEMA>.<TABLE_NAME>
GROUP BY <key_fields>
HAVING COUNT(*) > 1
ORDER BY n DESC
LIMIT 100
Trigger: New column added, or column wrapped in COALESCE/NULLIF.
Important: Added columns (from "New columns" in Phase 2b) do NOT exist in prod yet. For added columns, query {{dev_db}} only. For modified columns (COALESCE/NULLIF changes), compare both databases.
For added columns (dev only):
SELECT
COUNT(*) AS total_rows,
SUM(CASE WHEN <column> IS NULL THEN 1 ELSE 0 END) AS null_count,
ROUND(100.0 * SUM(CASE WHEN <column> IS NULL THEN 1 ELSE 0 END) / NULLIF(COUNT(*), 0), 2) AS null_pct
FROM {{dev_db}}.<SCHEMA>.<TABLE_NAME>
For modified columns (prod vs dev):
SELECT
'prod' AS source,
COUNT(*) AS total_rows,
SUM(CASE WHEN <column> IS NULL THEN 1 ELSE 0 END) AS null_count,
ROUND(100.0 * SUM(CASE WHEN <column> IS NULL THEN 1 ELSE 0 END) / NULLIF(COUNT(*), 0), 2) AS null_pct
FROM {{prod_db}}.<SCHEMA>.<TABLE_NAME>
UNION ALL
SELECT
'dev' AS source,
COUNT(*) AS total_rows,
SUM(CASE WHEN <column> IS NULL THEN 1 ELSE 0 END) AS null_count,
ROUND(100.0 * SUM(CASE WHEN <column> IS NULL THEN 1 ELSE 0 END) / NULLIF(COUNT(*), 0), 2) AS null_pct
FROM {{dev_db}}.<SCHEMA>.<TABLE_NAME>
Trigger: Model is materialized='incremental' OR a time axis field was identified.
SELECT
CAST(<time_axis> AS DATE) AS day,
COUNT(*) AS row_count
FROM {{prod_db}}.<SCHEMA>.<TABLE_NAME>
WHERE <time_axis> >= CURRENT_TIMESTAMP - INTERVAL '14' DAY
GROUP BY day
ORDER BY day DESC
LIMIT 30
Trigger: Always (for changed fields + top segmentation field). Modified models only.
Important: Exclude added columns (from "New columns" in Phase 2b) from <group_fields>. Only use fields that exist in BOTH prod and dev. Added columns don't exist in prod and will cause query errors.
WITH prod AS (
SELECT <group_fields>, COUNT(*) AS cnt
FROM {{prod_db}}.<SCHEMA>.<TABLE_NAME>
GROUP BY <group_fields>
),
dev AS (
SELECT <group_fields>, COUNT(*) AS cnt
FROM {{dev_db}}.<SCHEMA>.<TABLE_NAME>
GROUP BY <group_fields>
)
SELECT
COALESCE(b.<field>, d.<field>) AS <field>,
COALESCE(b.cnt, 0) AS cnt_prod,
COALESCE(d.cnt, 0) AS cnt_dev,
COALESCE(d.cnt, 0) - COALESCE(b.cnt, 0) AS diff
FROM prod b
FULL OUTER JOIN dev d ON b.<field> = d.<field>
ORDER BY ABS(diff) DESC
LIMIT 100
Trigger: Always. Modified models only.
SELECT 'prod' AS source, COUNT(*) AS row_count FROM {{prod_db}}.<SCHEMA>.<TABLE_NAME>
UNION ALL
SELECT 'dev' AS source, COUNT(*) AS row_count FROM {{dev_db}}.<SCHEMA>.<TABLE_NAME>
version: 1
metadata:
id: validation-pr-<PR_NUMBER>-<random_suffix>
name: "Validation: PR #<PR_NUMBER> - <PR_TITLE_TRUNCATED>"
created_at: "<current_iso_timestamp>"
updated_at: "<current_iso_timestamp>"
Only include prod_db if there are modified models. If all models are new, only include dev_db.
# Include ONLY if there are modified models:
- id: param-prod-db
type: parameter
content:
name: prod_db
config:
type: text
default_value: "ANALYTICS"
placeholder: "Prod database (e.g., ANALYTICS)"
display_type: table
# Always include:
- id: param-dev-db
type: parameter
content:
name: dev_db
config:
type: text
default_value: "PERSONAL_<USER>"
placeholder: "Dev database (e.g., PERSONAL_JSMITH)"
display_type: table
- id: cell-summary
type: markdown
content: |
# Validation Queries for <PR or Local Branch>
## Summary
- **Title:** <title>
- **Author:** <author>
- **Source:** <PR URL or "Local branch: <branch>">
- **Status:** <merge_timestamp or "Not yet merged" or "N/A (local)">
## Changes
<brief description based on diff analysis>
## Changed Models
- `<SCHEMA>.<TABLE_NAME>` (from `<file_path>`)
## How to Use
1. Select your Snowflake connector above
2. Set **dev_db** to your dev database (e.g., `PERSONAL_JSMITH`)
3. If modified models are present, set **prod_db** to your prod database (e.g., `ANALYTICS`)
4. Run single-table queries first, then comparison queries
display_type: table
- id: cell-<pattern>-<model>-<index>
type: sql
content: |
/*
========================================
<Pattern Name (human-readable, e.g. "Total Row Count" — do NOT include pattern numbers like "Pattern 7:")>
========================================
Model: <SCHEMA>.<TABLE_NAME>
Triggered by: <why this pattern was generated>
What to look for: <interpretation guidance>
----------------------------------------
*/
<actual_sql_query>
display_type: table
Cells are ordered consistently for both model types, following this sequence:
New models:
Modified models:
/tmp/validation_notebook_working/<id>/notebook.yamlpython3 ${CLAUDE_PLUGIN_ROOT}/skills/monte-carlo-validation-notebook/scripts/generate_notebook_url.py /tmp/validation_notebook_working/<id>/notebook.yaml --mc-base-url <MC_BASE_URL>
Present:
# Validation Notebook Generated
## Summary
- **Source:** PR #<number> - <title> OR Local: <branch>
- **Author:** <author>
- **Changed Models:** <count> models (of <total_count> changed)
- **Generated Queries:** <count> queries
> ⚠️ If models were capped: "Only the first 10 of <total_count> changed models were included. Re-run with `--models` to select specific models."
## Notebook Opened
The notebook has been opened directly in your browser.
Select your Snowflake connector in the notebook interface to begin running queries.
*Make sure MC Bridge is running. Let me know if you want tips on how to install this locally*
{{prod_db}} NOT ${prod_db}{{prod_db}}.<SCHEMA>.<TABLE> and {{dev_db}}.<SCHEMA>.<TABLE>prod_db and dev_db are parameterscell-p3-model-1| block scalar for SQL with comments| Pattern | Name | Trigger | Model Type | Database | Order |
|---|---|---|---|---|---|
| 7 / 7-new | Total Row Count | Always | Both | {{prod_db}} (modified) / {{dev_db}} (new) | 1 |
| 9 | Sample Data Preview | Always | Both | {{prod_db}} (modified) / {{dev_db}} (new) | 2 |
| 2 / 2-new | Core Segmentation Counts | Always | Both | {{prod_db}} (modified) / {{dev_db}} (new) | 3 |
| 1 | Changed Field Distribution | Column modified in diff (not added) | Modified only | {{prod_db}} | 4 |
| 5 | Uniqueness Check | JOIN/unique_key changed (modified) / Always (new) | Both | {{dev_db}} | 5 |
| 6 / 6-new | NULL Rate Check | New column or COALESCE (modified) / Always (new) | Both | Added col: {{dev_db}} only; COALESCE: Both (modified) / {{dev_db}} (new) | 5 |
| 8 | Time-Axis Continuity | Incremental or time field | Both | {{prod_db}} (modified) / {{dev_db}} (new) | 5 |
| 3 | Before/After Comparison | Changed fields (not added) | Modified only | Both | 6 |
| 7b | Row Count Comparison | Always | Modified only | Both | 6 |
If the user asks how to install or set up MC Bridge, fetch the README from the mc-bridge repo and show the relevant quick start / setup instructions:
gh api repos/monte-carlo-data/mc-bridge/readme --jq '.content' | base64 --decode
Focus on: how to install, configure connections, and run MC Bridge. Don't dump the entire README — extract just the setup-relevant sections.