ETL Replatform Deploy

After SnowConvert AI Replatform converts SSIS packages into dbt projects and Snowflake TASKs/procedures, this skill validates the output, deploys it to Snowflake, and runs smoke tests.

When to Use

• User has SnowConvert AI Replatform output (the Output/ETL/ folder) ready to deploy
• User mentions SSIS-to-dbt migration, replatform output, or converted ETL packages
• User needs to validate, fix, and deploy converted dbt projects to Snowflake
• User wants to set up orchestration (TASKs/procedures) for deployed dbt projects
• User needs to troubleshoot deployment issues with replatformed ETL

Tools Used

• bash — Run scanner CLI (uv run python -m replatform_scanner), run snow dbt deploy
• snowflake_sql_execute — Create databases/schemas/tables, deploy orchestration SQL, run smoke tests
• ask_user_question — Present briefing, confirm fixes, get deployment approval at every phase gate
• read / write / edit — Fix profiles.yml, sources.yml, orchestration SQL files

Stopping Points

• ✋ Phase 0: User approves the workflow before any action
• ✋ Phase 1: User reviews scan inventory before validation
• ✋ Phase 2: User approves each fix; confirms all fixes before deployment
• ✋ Phase 3.1.5: User approves source table creation (stubs/seeds)
• ✋ Phase 3.3: User reviews deployment summary before smoke tests
• ✋ Phase 4: User reviews smoke test results

Rules

1. Consent Before Action

   • ALWAYS present the Phase 0 briefing BEFORE running any commands, tools, or scripts
   • DO NOT execute any bash commands, SQL statements, or file modifications until the user explicitly approves
   • If the user declines, stop immediately and explain what they can do manually instead

2. Use ONLY Provided Scripts

   • ONLY use the provided CLI commands: python -m replatform_scanner
   • DO NOT create custom scripts to read or manipulate the JSON file
   • DO NOT use grep, jq, or other tools to parse the inventory directly
   • All interactions MUST go through the provided CLI

3. Deploy in Correct Order

   • etl_configuration/ objects FIRST (tables, UDFs, procedures)
   • dbt projects SECOND (via snow dbt deploy)
   • Orchestration SQL THIRD (CREATE TASK / CREATE PROCEDURE)
   • NEVER deploy orchestration before dbt projects are deployed

4. User Confirmation at Every Phase

   • Stop at mandatory checkpoints before each phase
   • Present summary of what will happen
   • Wait for explicit approval before proceeding

5. Schema Awareness

   • Ask user for target DATABASE and SCHEMA before any deployment
   • Update hardcoded public schema references in etl_configuration/ if target differs
   • Ensure EXECUTE DBT PROJECT names match deployed project names exactly

6. No Custom HTML Reports

   • DO NOT write custom HTML reports manually

Prerequisites

Before using this skill:

• SnowConvert AI Replatform output directory (the Output/ETL/ folder from a completed conversion)
• Snowflake CLI (snow) installed and configured for snow dbt deploy / snow dbt execute
• Active Snowflake connection with permissions to create tables, UDFs, procedures, tasks, and deploy dbt projects
• Python 3.11+ and uv package manager installed

Workflow

Replatform Deploy Progress:
- [ ] Phase 0: Briefing & Consent
- [ ] Phase 1: Scan & Inventory
- [ ] Phase 2: Validate & Fix
- [ ] Phase 3: Deploy
- [ ] Phase 4: Smoke Test
- [ ] Phase 5: Operationalize (optional)

Phase 3 Detail:

Phase 3: Deploy
  ├─ 3.0: Collect target info (DB, schema, connection, warehouse)
  ├─ 3.0.1: Verify DB/schema exist
  │   ├─ DB missing → Create? / Different name? / Stop
  │   └─ Schema missing → Create? / Different name? / Stop
  ├─ 3.1: Deploy etl_configuration
  ├─ 3.1.5: Create source tables
  │   ├─ Seeds exist → dbt seed (preferred)
  │   ├─ No seeds → CREATE TABLE stubs
  │   └─ Skip (user creates manually)
  ├─ 3.2: Deploy dbt projects (snow dbt deploy)
  └─ 3.3: Deploy orchestration SQL

Phase 0: Briefing & Consent

Goal: Explain what this skill does and get explicit user approval before executing anything.

⚠️ STOP: This phase MUST be completed before ANY other action. Do not run any commands, read any files, or execute any tools until the user approves.

Load references/phase0-briefing.md and present its content to the user using ask_user_question.

Ask the user: "Shall I proceed with Phase 1 (Scan & Inventory)? This is read-only — it only reads files in your Replatform output directory and writes a JSON inventory file."

Options:

• Yes, proceed — Continue to Phase 1
• No, stop here — End the workflow; explain they can run the CLI commands manually

⚠️ STOP: Do NOT proceed until the user selects "Yes, proceed".

Phase 1: Scan & Inventory

Goal: Build a complete inventory of the Replatform output and understand the deployment topology.

Step 1.1: Locate the Output Directory

Ask user for the path to the Replatform output (typically the Output/ETL/ folder from SnowConvert AI).

Step 1.2: Run the Scanner

uv run --project <SKILL_DIR> python -m replatform_scanner scan <ETL_OUTPUT_DIR> <INVENTORY_OUTPUT_PATH>

Step 1.3: Review Inventory

uv run --project <SKILL_DIR> python -m replatform_scanner summary <INVENTORY_JSON>

Present summary (packages, dbt projects, orchestration types, etl_configuration components, validation issues). Ask: Proceed to Phase 2?

⚠️ STOP: Wait for user confirmation before proceeding to Phase 2.

Phase 2: Validate & Fix

Goal: Identify and resolve issues before deployment.

Step 2.1: Run Validation

uv run --project <SKILL_DIR> python -m replatform_scanner validate <INVENTORY_JSON>

This checks: placeholders in sources.yml/profiles.yml, missing files, project name mismatches, unsupported profiles.yml fields, etl_configuration/ schema refs, EXECUTE DBT PROJECT refs, CREATE TASK clause ordering, sources.yml hardcoded database/schema, TASK DAG orphans, hardcoded schema prefixes in EXECUTE DBT PROJECT, hardcoded warehouse names in orchestration SQL, PROCEDURE-based orchestration using EXECUTE DBT PROJECT (ERROR — this is a Snowflake platform limitation, not just a warning), and '{{ var(...) }}'::DATE casts that fail on partial dates (ERROR — YYYY-MM is not a valid Snowflake DATE).

Step 2.2: Present Issues and Apply Fixes

For each issue: show severity, category, file, problem, suggested fix. Ask: Apply this fix? (Yes/No/Skip all of this type)

Issue Categories: PLACEHOLDER, NAME_MISMATCH, SCHEMA_MISMATCH, MISSING_FILE, ORPHAN_TASK, DANGLING_REF, SOURCE_SCHEMA_MISMATCH, PROFILES_OVERRIDE, ORCH_SCHEMA_PREFIX, ORCH_WAREHOUSE, PROC_EXECUTE_DBT, PARTIAL_DATE_CAST

Fix Patterns:

• PROFILES_OVERRIDE: Replace source-env connection fields with target values. snow dbt deploy requires account, user, role, database, schema, warehouse to be present. role, account, user are used verbatim — they MUST be real target values (not 'placeholder'). database, schema, warehouse are overridden by CLI flags.
• PROC_EXECUTE_DBT (ERROR): EXECUTE DBT PROJECT cannot run inside SQL stored procedures. Convert to a TASK-based DAG where each EXECUTE DBT PROJECT is a separate child task with AFTER dependency.
• PARTIAL_DATE_CAST (ERROR): '{{ var("x") }}'::DATE fails when the variable value is YYYY-MM format. Fix by using TO_DATE('{{ var("x") }}', 'YYYY-MM') or ensuring variables are always YYYY-MM-DD.
• ORCH_SCHEMA_PREFIX: Replace hardcoded source schema (e.g., ETL.) in EXECUTE DBT PROJECT with the target schema (e.g., PUBLIC.).
• ORCH_WAREHOUSE: Replace hardcoded source warehouses (e.g., ETL_WH) with the target warehouse.
• SOURCE_SCHEMA_MISMATCH: Remove database field from sources.yml and set schema to the target schema.

After all fixes, present summary (found/fixed/skipped/remaining). Ask: Proceed to Phase 3?

⚠️ STOP: Wait for user confirmation before proceeding to Phase 3.

Phase 3: Deploy

Goal: Deploy all components to Snowflake in the correct order.

Step 3.0: Collect Target Information

Ask the user for: target DATABASE, target SCHEMA, Snowflake CLI connection name, warehouse.

Step 3.0.1: Ensure Database and Schema Exist

IMPORTANT — do this BEFORE any other SQL in Phase 3.

Run these two statements silently (do NOT run USE DATABASE first — it will fail if the database does not exist yet):

CREATE DATABASE IF NOT EXISTS <TARGET_DATABASE>;
CREATE SCHEMA IF NOT EXISTS <TARGET_DATABASE>.<TARGET_SCHEMA>;

Then set active context:

USE DATABASE <TARGET_DATABASE>;
USE SCHEMA <TARGET_DATABASE>.<TARGET_SCHEMA>;

Step 3.1: Deploy etl_configuration

Deploy shared infrastructure objects first (tables, UDFs, procedures). For each SQL file: read, check schema refs, present for review, execute on approval.

If error occurs:

• Object already exists → safe to skip if user confirms existing object is correct
• Schema reference error → check etl_configuration/ SQL for hardcoded schema names; update to target schema

Step 3.1.5: Create Source Tables

Before deploying dbt projects, check whether source tables from sources.yml exist in the target. For missing tables, offer:

1. Seed from CSV (preferred) — if seeds/ has matching CSVs
2. Create stub tables — empty tables with columns from sources.yml
3. Skip — user creates manually

IMPORTANT: When creating stub tables, do NOT rely solely on sources.yml column declarations — they may be incomplete. Always read the staging model SQL (e.g., stg_raw__customers.sql) to find the exact columns referenced in the SELECT list. The model SQL is the source of truth for required columns.

IMPORTANT: Use CREATE TABLE IF NOT EXISTS for new stubs, CREATE OR REPLACE TABLE for tables with wrong columns. Never use ALTER TABLE ADD COLUMN. See references/snowflake-sql-patterns.md for exact syntax.

⚠️ STOP: Present the list of tables to create/seed and get approval before executing any CREATE or DROP statements.

Step 3.2: Deploy dbt Projects

snow dbt deploy <PROJECT_NAME> \
  --source <PROJECT_DIR> \
  --database <TARGET_DATABASE> \
  --schema <TARGET_SCHEMA> \
  --connection <CONNECTION_NAME> \
  --warehouse <TARGET_WAREHOUSE> \
  --role <TARGET_ROLE> \
  --force

Track: [DONE] / [FAIL] / [PENDING] per project.

If error occurs:

• Missing required fields → profiles.yml needs all connection fields (see PROFILES_OVERRIDE fix pattern)
• Failed to use role <name> → role in profiles.yml must be a real target role, NOT a placeholder. snow dbt deploy uses role verbatim from profiles.yml — it is NOT overridden by CLI flags. Set it to the role from your Snowflake connection (e.g. snow connection list to check)
• source table not found → go back to Step 3.1.5 to create stubs/seeds
• Project already exists → use --force flag to overwrite

Step 3.3: Deploy Orchestration SQL

For each package: read orchestration SQL, verify EXECUTE DBT PROJECT refs, present for review, execute.

CRITICAL — TASK DAG creation order: Tasks that reference predecessors via AFTER <task> will fail with Invalid predecessor if the predecessor does not exist yet. You MUST create tasks in dependency order:

1. Root tasks first — tasks with a SCHEDULE clause and NO AFTER clause.
2. Then child tasks — in topological order so that every task referenced in an AFTER clause already exists.
3. General rule: if task B has AFTER task_A, then task_A must be created before task_B.

Parse each orchestration SQL file to identify the DAG structure before executing any CREATE TASK statements. If a file contains multiple tasks, reorder the statements so roots come first.

If error occurs:

• Invalid predecessor 'X' was specified → task creation order is wrong; create the predecessor task first, then retry
• Object 'X' does not exist → hardcoded schema prefix; apply ORCH_SCHEMA_PREFIX fix
• Warehouse 'X' does not exist → hardcoded warehouse; apply ORCH_WAREHOUSE fix
• Unsupported statement type 'SHOW PARAMETER' → procedure-based orchestration; must convert to TASK DAG (see PROC_EXECUTE_DBT)

⚠️ STOP: Present deployment summary (etl_configuration, dbt projects, orchestration). Ask: Proceed to Phase 4?

Phase 4: Smoke Test

Goal: Verify the deployed pipeline works end-to-end.

Step 4.1: Test dbt Projects Individually

EXECUTE DBT PROJECT <database>.<schema>.<project_name>
  ARGS = 'build --select tag:<tag> --target dev';

Passing dbt vars (e.g. for report_month):

EXECUTE DBT PROJECT <database>.<schema>.<project_name>
  ARGS = 'run --vars ''{report_month: 2024-01-01}''';

IMPORTANT: The syntax is ARGS = '...' (single equals, string literal). Do NOT use ARGS => '...' (Snowflake named-parameter syntax) — it will fail with a syntax error. Use fully-qualified DATABASE.SCHEMA.PROJECT_NAME in both EXECUTE statements and TASK definitions.

Track: [PASS] / [FAIL] per project with model counts and errors.

Step 4.2: Test Orchestration

For TASK-based: EXECUTE TASK <database>.<schema>.<package_name>; then check TASK_HISTORY(). For PROCEDURE-based: ⚠️ WARNING — EXECUTE DBT PROJECT inside SQL stored procedures is a known Snowflake limitation (fails with Unsupported statement type 'SHOW PARAMETER'). If the scanner flagged PROC_EXECUTE_DBT, this orchestration pattern must be converted to a TASK-based DAG before it can work. See Troubleshooting table.

Step 4.3: Data Validation

Row count checks on mart tables. Present results summary.

Phase 5: Operationalize (Optional)

Goal: Set up ongoing scheduling and monitoring.

Options: Schedule TASKs with CRON, convert to Dynamic Tables, set up monitoring queries, or skip.

For CRON scheduling: ALTER TASK ... SET SCHEDULE, then RESUME child-to-root. For Dynamic Tables: suggest loading the dynamic-tables skill. For monitoring: provide TASK_HISTORY() query from references/snowflake-sql-patterns.md.

CLI Reference

All CLI commands use:

uv run --project <SKILL_DIR> python -m replatform_scanner <command> [args]

Output

This skill produces:

• replatform_inventory.json — complete inventory of scanned Replatform output
• Deployed Snowflake objects — dbt projects, orchestration TASKs/procedures, etl_configuration tables/UDFs/procedures
• Smoke test results — pass/fail per dbt project build and orchestration execution
• Data validation — row counts on output mart tables

Troubleshooting

Session Diary

⚠️ STOP: At the start of every session, check for an existing diary before running any commands.

Load references/session-diary.md for the full diary format and template.

Quick reference:

• Diary location: ~/.snowflake/cortex/memory/replatform/<connection>/<database>.<schema>.md
• If diary exists: read it to restore context (phase reached, packages deployed, fixes applied)
• If new deployment: create diary after Phase 1 completes
• Update diary after each phase completes

Success Criteria

Deployment is complete when:

• All etl_configuration/ objects created in Snowflake
• All dbt projects deployed via snow dbt deploy
• All orchestration SQL executed (TASKs/procedures created)
• Smoke tests pass for at least the dbt project builds
• User has reviewed and approved the deployment

Pre-Completion Checklist

[ ] Did I deploy etl_configuration objects first?
[ ] Did I verify dbt project names match EXECUTE DBT PROJECT references?
[ ] Did I run smoke tests on deployed dbt projects?
[ ] Did I present all results to the user?