Convert Sas To Snowflake

Convert SAS to Snowflake

© Snowflake Inc. This skill and its contents are the proprietary intellectual property of Snowflake Inc.

Expert SAS to Snowflake migration - ALWAYS outputs .sql by default, with PySpark/SCOS as last resort.

Output: .sql by default (Tier 1 SQL + Tier 2 Stored Procedures). PySpark/SCOS notebook = LAST RESORT (HASH objects, CALL EXECUTE, statistical procs only). See SQL-First Classification below for full tier tables.

Snowflake Interaction Policy: ALL Snowflake operations (object creation, compilation, testing) require explicit user confirmation. See workflows/steps-8-10-post-conversion.md for confirmation patterns and testing mode selection.

User Consent Propagation: When a user confirms a high-level decision (validation scope, compilation approval), downstream sub-decisions that are direct consequences auto-resolve without re-prompting. See "Consent Propagation Rules" section below.

Validation Prompt Policy (EXPLICIT — No Auto-Propagation)

Each validation phase requires its own explicit user confirmation. No phase auto-proceeds based on a prior selection. Snowflake operations ALWAYS show credit impact before executing.

Hard Artifact Gates: Each phase produces artifacts on disk. The NEXT phase CANNOT begin unless the prior phase's gate passes (artifacts verified to exist). State file updates without corresponding disk artifacts are INVALID.

Consent propagation is ONLY retained for:

• Fix-and-retry loop within Phase 3 (auto-retry compilation fixes without re-prompting)
• Auto-drop of temporary compilation environment after Phase 3 completes
• These are sub-operations WITHIN a phase the user already approved

No batch-mode amplifier. Batch mode (10+ files) follows the same prompt rules as interactive mode.

Intent Detection

Start
  ↓
Analyze User Request
  ↓
  ├─→ Convert intent → Follow Conversion Workflow (below)
  │     (convert, migrate, translate, transform)
  │
  ├─→ Assess intent → Load ../assess-sas-migration/SKILL.md
  │     (assess, analyze, size, estimate, complexity, volume, LOE, readiness, heatmap)
  │
  └─→ Validate intent → Load validate-sas-conversion/SKILL.md
        (validate, verify, test conversion, check migration)

When to Use

• Convert SAS DATA steps to Snowflake SQL or stored procedures
• Translate PROC SQL to Snowflake SQL
• Migrate SAS macros to Snowflake scripting
• Convert PROC steps (SORT, MEANS, FREQ, TRANSPOSE, etc.)
• Validate converted code against test data

Prerequisites

• Active Snowflake connection (for validation)
• Target schema exists (or user confirms schema)

SQL-First Classification

TIER 1: Pure SQL (Default - ALWAYS Try First)

Use SQL for:

TIER 2: Stored Procedures (When Pure SQL Insufficient)

Use Snowflake Scripting when:

Stored Procedure Pattern:

CREATE OR REPLACE PROCEDURE process_sas_logic(p_input STRING, p_output STRING)
RETURNS STRING
LANGUAGE SQL
AS
$$
DECLARE
  v_row_count INTEGER;
BEGIN
  -- Use CTEs and window functions where possible
  CREATE OR REPLACE TABLE IDENTIFIER(:p_output) AS
  WITH ranked AS (
    SELECT *,
      ROW_NUMBER() OVER (PARTITION BY group_col ORDER BY sort_col) AS rn,
      SUM(amount) OVER (PARTITION BY group_col ORDER BY sort_col 
                        ROWS UNBOUNDED PRECEDING) AS running_total
    FROM IDENTIFIER(:p_input)
  )
  SELECT * FROM ranked WHERE some_condition;
  
  SELECT COUNT(*) INTO :v_row_count FROM IDENTIFIER(:p_output);
  RETURN 'Processed ' || v_row_count || ' rows';
END;
$$;

TIER 3: PySpark via SCOS (LAST RESORT ONLY)

Use PySpark with Snowpark Connect (SCOS) ONLY when SQL/Stored Procedures CANNOT work:

⚠️ These are SQL, NOT PySpark:

• ARRAY iteration → CASE expressions
• RETAIN → window functions
• FIRST./LAST. → ROW_NUMBER()
• KEY= lookup → LEFT JOIN
• %DO loops → GENERATOR

See references/pyspark-fallback.md for full SCOS notebook setup template and patterns.

Before using PySpark, ask: "Can this be done with window functions, CTEs, or a stored procedure?"

Critical Conversion Rules

Load references/conversion-rules.md at Steps 5-6 (code generation and self-check). Contains the rules governing semantic correctness.

Key rules summary (always in context):

• NEVER generate converter scripts — LLM reads and converts directly
• NEVER truncate or use "similar pattern" shortcuts
• SAS missing (.) = NULL with different comparison semantics
• MERGE != JOIN (many-to-many risk, IN= flags, source precedence)
• WORK tables = TEMPORARY, bare name, no schema prefix
• Snowflake Scripting: :var only in SQL statements, SELECT INTO, named exceptions only
• Migrated-source type traps: cast join keys to a common type, TRY_TO_DOUBLE VARCHAR measures, TO_DATE() VARCHAR date columns
• Validation anti-joins must use master reference tables (never derived/subset)
• Output column count must match SAS; never silently drop columns
• Consolidation must preserve all output tables

Known conflict — NOT adopted (pending verification): An external ruleset claims $VAR session-variable syntax fails inside stored procedures and that GETVARIABLE() is mandatory. This is not adopted here — the verified guidance in references/common-patterns.md (Platform Constraints) is that $VAR works inside an EXECUTE AS CALLER procedure body, with GETVARIABLE('VAR') as an equivalent alternative. GETVARIABLE() is genuinely required only when the variable name is computed at runtime (you can write GETVARIABLE('VAR' || i) but never $VAR || i). Do not switch to mandatory GETVARIABLE() for the static case unless re-verified empirically on Snowflake.

Batch Mode (10+ Files)

When 10+ files detected, load references/batch-mode.md for batch-specific rules, context window recovery, and Step 8a compilation with stub tables.

Context Budget Management

The LLM should minimize context usage by:

1. Only load references relevant to the current step — never pre-load all references at Step 2
2. Do NOT re-read completed files — use conversion_state.json to skip already-converted files
3. Write state to disk before each step transition — enables resume without context replay
4. For batch mode, process in groups of 10-20 files — write .sql to disk between groups
5. When context exceeds 60% consumed: finish current file, write state, inform user
6. On resume: read ONLY conversion_state.json + the reference files needed for the current step

Step-to-reference mapping (load ONLY these at each step):

Unload after use: After completing a step, the reference files loaded for that step can be considered "consumed" — their content is materialized in the output artifacts. Do not re-read them unless the step needs to be re-executed.

Conversion State Tracker (conversion_state.json)

A persistent JSON file written incrementally throughout the workflow to track per-file progress. Enables resume from ANY step across context windows. See references/state-tracker-schema.md for the full JSON schema, enforcement rules, and valid current_step values.

Location: <output_dir>/conversion_state.json

Key fields: metadata.current_step, files.<name>.status, gates.*, trace_progress, execution_progress, compilation_env

Resume behavior (Step 1 pre-check): If conversion_state.json exists, read it and offer Resume/Restart.

File status progression: classified → converted → self_checked → compiled → validated → complete

Enforcement rule: State writes are BLOCKING PREREQUISITES for step transitions. The NEXT step CANNOT begin until the state file reflects the current step's completion. Context pressure does NOT exempt state writes.

Conversion Workflow

Step 1: Gather Input

Pre-check: If <output_dir>/conversion_state.json exists, a prior session has in-progress conversion. Read the state file and offer:

• Resume from current_step (skip completed files/steps)
• Restart fresh (delete state file, begin from scratch)

Ask user:

1. SAS code source (file path, directory, or paste)
2. Target schema (e.g., DATABASE.SCHEMA)
3. Migration mode:
   • 1:1 Direct (default) — Each SAS file converts to one .sql file. PySpark notebook generated ONLY for true Tier 3 edge cases (HASH objects, CALL EXECUTE, statistical procs).
   • Optimize & Consolidate — Merges related SAS files into fewer .sql files where dependencies allow. Same SQL-first tiering applies.

⚠️ STOP: Confirm understanding of SAS code.

MANDATORY STATE WRITE (Step 1 → Step 2 transition): Immediately after gathering input, write conversion_state.json with initial metadata:

{
  "metadata": {
    "output_dir": "<path>",
    "target_schema": "<DATABASE.SCHEMA>",
    "migration_mode": "<1:1 or consolidate>",
    "created_at": "<ISO timestamp>",
    "last_updated": "<ISO timestamp>",
    "current_step": "1",
    "total_files": 0,
    "validation_scope": "pending_selection",
    "pre_approved_compilation": false,
    "pre_approved_validation": null
  }
}

This ensures metadata survives context loss between Steps 1 and 3. Step 2 CANNOT begin until this write is confirmed on disk.

MANDATORY CHECKPOINT (every transition): Alongside each conversion_state.json write throughout the workflow, append one line to <output_dir>/checkpoint_log.jsonl (create it here at Step 1 with the first STARTED line). This append-only audit trail records what happened and when — see references/checkpoint-logging.md for the schema and the per-checkpoint list. Appending the checkpoint line is a BLOCKING prerequisite to advancing, exactly like the state write.

Step 2: Load References

Always load: references/common-patterns.md (Variable Binding Rules)

Deferred load (do NOT load at Step 2 — loaded when needed):

• workflows/validation-pipeline.md → loaded after Step 6a (single validation orchestrator)
• workflows/steps-8-10-post-conversion.md → loaded at Step 10 only (report generation)
• templates/conversion-report.md → loaded at Step 10
• references/schema-inference.md → loaded at Phase 1 (synthetic data gen)
• references/synthetic-data-rules.md → loaded at Phase 1
• references/validation-execution.md → loaded at Phase 2 (LLM trace)
• references/snowflake-compile.md → loaded at Phase 3 (Snowflake compilation)
• references/snowflake-execution.md → loaded at Phase 4 (Snowflake Tier 2/3 execution)
• references/state-tracker-schema.md → loaded on resume or when first writing state
• references/checkpoint-logging.md → loaded once at Step 1 (governs append-only checkpoint_log.jsonl written at every checkpoint)
• references/batch-mode.md → loaded only when 10+ files detected
• references/classification-logic.md → loaded at Step 3 if detailed pseudocode needed
• references/comparison-rules.md → loaded at Phase 4 (result comparison)
• references/auto-validation.md → loaded at Phase 2 (pipeline orchestration)
• references/e2e-orchestration-test.md → loaded only when Phase 5 (Integration & E2E) runs
• references/consolidation-patterns.md → loaded only in Optimize & Consolidate mode
• references/multi-block-orchestration.md → loaded at Step 5 only for multi-block programs (3+ dependent blocks, macro %DO loops, &&var&i indexed vars, &SYSERR gating, FILEEXIST/trigger-file gates)
• references/pyspark-fallback.md → loaded only when Tier 3 blocks detected
• references/mermaid-diagrams.md → loaded only at Step 10 (DAG generation)

Then load based on constructs (Step 5 only):

Step 2.5: External Source Resolution

Scan SAS code for external database references:

1. LIBNAME statements with engine keywords: sqlsvr, oracle, teradata, odbc, oledb, postgres, mysql, mssql
2. PROC SQL CONNECT TO statements: connect to sqlsvr, connect to oracle, etc.
3. Any DSN=, authdomain=, qualifier=, schema= parameters

For each external reference found, extract:

• Engine type (sqlsvr, oracle, etc.)
• DSN / qualifier (maps to source database name)
• Schema (maps to source schema name)
• Tables referenced via this LIBNAME (scan for LIBNAME.table patterns)

Prompt user ONCE with all external sources:

External data sources detected in SAS code:
| LIBNAME | Engine | DSN/Qualifier | Schema | Tables Referenced |
|---------|--------|---------------|--------|-------------------|
| EUDB    | sqlsvr | EUDB          | dbo    | Table1, Table2    |

These tables are sourced from an EXTERNAL database (not Snowflake).
They will eventually be migrated to Snowflake.

Please provide the target Snowflake location for these tables:
a) Use target schema already specified: {TARGET_SCHEMA}
b) Provide a different DATABASE.SCHEMA for these external tables: ___
c) Use placeholder names: {PLACEHOLDER_DB}.{PLACEHOLDER_SCHEMA}.{table}

Store mapping in conversion_state.json under source_mappings (see state-tracker-schema.md).

During Step 5 (Generation): Use the mapping to resolve external table references. If LIBNAME EUDB with qualifier=ChargemasterEnforcement_Prod schema=dbo references dbo.MyTable, convert to the user-provided TARGET_DB.TARGET_SCHEMA.MYTABLE.

If NO external sources detected: Skip this step silently. No prompt needed.

Step 3: Classify Each Block (with Dependency Tracking)

Assessment Auto-Discovery (no prompt): A prior /assess-sas-migration run may have produced an assessment.json. Search these candidate locations in order and use the FIRST readable match (if several match, pick the one with the most recent modification time):

1. <output_dir>/assessment.json
2. <source_dir>/assessment.json
3. <source_dir>/assessment_output/assessment.json (the assess CLI's default --output ./assessment_output)
4. <output_dir>/../assessment_output/assessment.json
5. a shallow glob (depth ≤ 2) for assessment.json under <source_dir> and <output_dir>

A match is valid only if it parses as JSON and contains metadata.generated_at and a files[] array; otherwise treat it as not found (record reason: "unreadable").

• If found: seed per-file primary_tier / tier_distribution and the dependency graph from it to skip re-scanning. Note which path was used. If metadata.generated_at is older than the newest source-file modification time, set stale: true (still consume — the reconciliation below will surface any drift).
• If NONE found: proceed silently with fresh classification. Do NOT prompt for an assessment file. Record assessment.consumed: false, reason: "not_found" in state.

Record the outcome in conversion_state.json under assessment (see references/state-tracker-schema.md).

Still verify the dependency map is current — source files may have changed since assessment.

CRITICAL: Before classifying, build a dependency map:

1. Track which tables/datasets each block CREATES (output tables)
2. Track which tables/datasets each block READS (input tables)
3. Track macro variables set (CALL SYMPUT, %LET, INTO :var) and where they are consumed
4. Identify inter-block dependencies: if block N creates table X and block M reads table X, note the dependency
5. For repeated macro invocations: track parameter combinations separately — do NOT assume same behavior

Input Table Existence Check (optional, requires active Snowflake connection):

After building the dependency map, identify all TRUE external inputs — tables that appear in READ lists but are NOT created by any block in this conversion:

1. Collect unique source tables from all blocks' READ lists
2. Subtract tables that appear in any block's CREATES list (these are intermediate/temp tables)
3. The remainder = true external inputs that must pre-exist in Snowflake
4. If a Snowflake connection is active, verify these tables exist: SHOW TABLES LIKE '<name>' IN SCHEMA <target_schema>
5. If tables are NOT found, prompt user ONCE:

   These source tables are referenced but not found in {TARGET_SCHEMA}:
   - TABLE_A (used in blocks 1, 3)
   - TABLE_B (used in block 2)
   
   Options:
   a) Provide the correct DATABASE.SCHEMA for these tables
   b) They will be created before this script runs (proceed with current names)
   c) They were already mapped in Step 2.5 (use those mappings)
   d) Skip check (proceed as-is, may fail at compilation)
   

6. Store any user-provided mappings in conversion_state.json under source_mappings

If no Snowflake connection available: Skip silently. Compilation (Phase 4) will surface missing tables later.

For EACH block, apply SQL-First classification using the quick-reference table below. For detailed classification pseudocode and confidence assessment logic, load references/classification-logic.md.

Block counting (must match the assessment CLI): Follow the canonical ../references/block-tiering-spec.md. Enumerate each DATA/PROC step inside a macro as its own block, and exclude DI Studio / DataFlow boilerplate (macros named etls_*, rcset, rcsetds; content markers etls_, perfinit, log4sas, armsubsys, sas data integration studio) from both the block count and tiering. This keeps the conversion's block count and per-file tier identical to what /assess-sas-migration reports.

Quick Classification:

Confidence: LOW = nested macros, %INCLUDE with dynamic path, external LIBNAME. MEDIUM = INTCK/INTNX, NOTSORTED. HIGH = everything else.

Assessment Reconciliation (only if assessment.json was consumed): After computing this conversion's own per-file block count and tier, compare them against the assessment as a baseline. Both skills follow the same canonical ../references/block-tiering-spec.md, so a current assessment should match; a mismatch signals a stale assessment (source changed since it ran) or a bug — it is informational and does NOT block conversion.

For each file present in BOTH this conversion's scope and the assessment, compare:

• blocks (countable block count)
• primary_tier
• tier_distribution (T1 / T2 / T3 counts)

Also note coverage deltas: files in the assessment but not in this conversion's scope, and vice versa. Record the result in conversion_state.json under assessment.reconciliation (files_compared, files_matched, files_mismatched, and a mismatches list of {file, field, assessment, convert}). Present the summary in Step 4.

PREREQUISITE FOR STEP 4: Write conversion_state.json with initial entries for all files (status: "classified", tier, block count). Set current_step: "3". Also persist the dependency map per file: "dependencies": {"creates": [...], "reads": [...]}, and the assessment object (discovery outcome + reconciliation, or consumed: false). Verify the file exists on disk before presenting block analysis. Step 4 CANNOT begin until this write is confirmed.

Step 4: Present Block Analysis

## Block Analysis: script_name.sas

| Block | Type | Tier | Lines | Creates | Reads | Reason |
|-------|------|------|-------|---------|-------|--------|
| 1 | PROC SQL | 🔷 SQL | 10-25 | staging_data | source_table | Direct SQL mapping |
| 2 | DATA step | 🔷 SQL | 27-45 | summary | staging_data | Window functions |
| 3 | DATA step | 🔶 Stored Proc | 47-80 | flagged | summary | RETAIN + FIRST./LAST. |
| 4 | DATA step | 🔴 PySpark | 82-95 | lookup | ext_file | HASH object |

**Dependencies:** Block 2 depends on Block 1 (staging_data). Block 3 depends on Block 2 (summary).
**Output:** script_name.sql (all blocks as SQL/SP, block 4 commented if truly needs Python)

Assessment reconciliation (only when assessment.json was consumed in Step 3): Present a compact comparison so drift from the assessment baseline is visible. Omit this section entirely when no assessment was consumed.

## Assessment Reconciliation  (baseline: <assessment source_path>, generated <generated_at>)

| File | Assessment (blocks / tier) | Conversion (blocks / tier) | Match? |
|------|----------------------------|----------------------------|--------|
| a.sas | 22 / TIER_1_SQL | 22 / TIER_1_SQL | ✅ |
| b.sas | 22 / TIER_2_SP  | 20 / TIER_1_SQL | ⚠️ blocks, tier |

**Summary:** 8 matched, 1 mismatched. (⚠️ assessment is STALE — generated before the latest
source edit; re-run `/assess-sas-migration` to refresh.)  [show the stale note only if `stale: true`]
Coverage: 0 files in assessment but not in this conversion; 0 the other way.

Mismatches are informational (source may legitimately have changed) and do not block conversion.

⚠️ STOP: Review classification. If user disagrees with PySpark assignment, attempt SQL/Stored Proc alternative.

Permission Check (when Tier 2 blocks exist):

If any blocks are classified as Tier 2 (Stored Procedure), prompt:

Some blocks require stored procedures (CREATE PROCEDURE).
Does your Snowflake role have CREATE PROCEDURE privilege on the target schema?
- Yes → proceed with stored procedure generation
- No → fall back to PySpark/SCOS notebook for these blocks (preserves logic fidelity)
- Unsure → proceed with stored procedures; compilation (Phase 4) will catch permission errors

If user selects "No":

• Reclassify affected blocks from Tier 2 to Tier 3 (PySpark/SCOS notebook)
• Generate a .ipynb notebook for those blocks using the SCOS template from references/pyspark-fallback.md
• Tier 1 (SQL) blocks remain as .sql output
• Update conversion_state.json: set metadata.sp_permission: false

If user selects "Unsure":

• Proceed with Tier 2 stored procedures as normal
• Phase 4 (Snowflake Compilation) will surface permission errors
• At that point, offer the same PySpark/SCOS fallback

STATE WRITE on reclassification: If the user reclassifies a file's tier (e.g., Tier 3 → Tier 2), update conversion_state.json immediately with the revised tier before proceeding to Step 5.

Step 5: Generate Conversion

For TIER 1 (SQL) blocks:

• Use references/function-mappings.md
• Generate clean SQL with CREATE OR REPLACE TABLE
• Use explicit column lists (not SELECT *) when ROW_NUMBER() helpers are used
• WORK datasets → CREATE OR REPLACE TEMPORARY TABLE (no schema prefix)

For TIER 2 (Stored Procedure) blocks:

• Follow Variable Binding Rules from references/common-patterns.md
• Use CTEs and window functions inside the procedure
• Include proper EXCEPTION handling (named exceptions ONLY)
• Use SELECT INTO :var (NEVER LET := SELECT)
• Persist cross-block variables via EXECUTE IMMEDIATE SET

For TIER 3 (PySpark/SCOS) blocks:

• ONLY for HASH objects, CALL EXECUTE, DO UNTIL with external state, or PROC REG/GLM
• Use Snowpark Connect (SCOS) template from TIER 3 section
• Generate .ipynb notebook with SCOS setup cell
• Keep PySpark scope minimal - only the blocks that truly need it

Generation Rules:

• Convert EVERY column derivation — no "similar pattern" shortcuts
• Convert EVERY branch in conditional logic — no skipped branches
• Preserve the exact output table count from the SAS block
• Preserve all CALL SYMPUT/SYMPUTX assignments
• For PROC FORMAT: use CASE expressions for simple value mappings, lookup tables for complex ranges
• For Oracle/DB2 passthrough (CONNECT USING): strip vendor wrappers, convert vendor-specific functions (DECODE→COALESCE, NVL→COALESCE, TO_DATE format strings) to Snowflake native SQL
• For %INCLUDE without source: generate CALL to stored procedure stub with MANUAL_REVIEW_REQUIRED
• For external macros without source: generate parameterized stub comment with inferred behavior
• NEVER delegate conversion to a subagent that writes a converter script — if using subagents for context management, each subagent must read SAS files and write SQL directly (not write a program that writes SQL)

PREREQUISITE FOR STEP 6: As each file's .sql is written to disk, update its entry in conversion_state.json to status: "converted". Set current_step: "5". Step 6 CANNOT begin until all converted files are reflected in the state file.

Step 6: Post-Generation Self-Check (MANDATORY before compilation)

Before submitting to Snowflake for compilation, verify the generated SQL for:

1. No qualified WORK/temp table names — temp tables must be bare names, never DATABASE.SCHEMA.TABLE
2. TEMPORARY keyword preserved — every CREATE OR REPLACE for a WORK dataset must include TEMPORARY
3. No RAISE USING MESSAGE syntax — only named exceptions with RAISE
4. No unresolved SAS macro variables — no &var or &&var in output
5. No :var in scripting control flow — :var only in SQL statements, not in IF/WHILE/assignment
6. Cross-block variable persistence — if a variable set in block N is used in block N+1, it must be persisted via SET session variable
7. No SELECT * leaking helper columns — ROW_NUMBER() dedup patterns must use explicit column lists
8. All output tables accounted for — count matches SAS block's output tables
9. No vendor-specific SQL syntax remaining — no DECODE, NVL, CONNECT TO, DISCONNECT FROM
10. No "similar pattern" / "same as above" shortcuts — every derivation must be explicit
11. All validation source tables preserved — every source table in the SAS validation block appears in the converted SQL validation query; no sources silently dropped
12. Output table count matches SAS — every CREATE TABLE/INSERT INTO target in the converted SQL maps to a SAS output dataset; count(DISTINCT output tables in SQL) >= count(DISTINCT output datasets in SAS)
13. Output column count matches SAS — column count of each converted output table >= column count of the corresponding SAS output dataset; no columns silently dropped
14. No files dropped from prior version (batch/re-run mode) — if re-running a conversion, verify the new output directory has >= the file count of the previous version; any reduction must be explicitly justified by consolidation
15. Validation reference tables match SAS — for each anti-join validation check, the reference table in the converted SQL must match the reference table in the SAS source; a master table (e.g., CUSTOMER_MASTER) must not be replaced with a derived/subset table (e.g., CUSTOMER_SUBSET)
16. Referential checks not downgraded to value checks — if SAS checks whether a key EXISTS in a reference table (anti-join), the converted SQL must also use an anti-join, not a simple WHERE value IS NULL OR value = 0 check
17. No unresolved stored procedure references — if the converted SQL calls a stored procedure (e.g., CALL SP_NAME()), that SP must either be defined in the output files or explicitly flagged as MANUAL_REVIEW_REQUIRED
18. No invented elements — every variable, parameter, column name, and filter condition in the output must trace to the source SAS code; nothing fabricated or "assumed helpful" (see conversion-rules.md Anti-Hallucination rule)
19. No session variables in VALUES() — $VAR is invalid in INSERT ... VALUES(...); use INSERT ... SELECT instead (see common-patterns.md Platform Constraints)
20. EXECUTE AS CALLER on procs using session vars/temp tables — any procedure that reads/sets a session variable or shares temp tables across the orchestration chain must be EXECUTE AS CALLER, never owner's rights
21. Reserved-word columns quoted — SAS column names that are Snowflake reserved words (DESC, TYPE, VALUE, LOCATION, NAME, FILE, STATUS, etc.) must be double-quoted
22. XLSX I/O uses stored proc + CALL — PROC IMPORT/EXPORT for XLSX must generate a Python stored procedure + CALL (never COPY INTO/INFER_SCHEMA on a native .xlsx); CSV/pipe stays native SQL
23. Type-safe joins on migrated keys — when joining key columns from tables migrated from different sources, both sides are cast to a common type (::VARCHAR) to avoid silent NUMBER-vs-VARCHAR zero-row failures (see conversion-rules.md → Migrated-Source Type Traps)
24. Numeric aggregation guards VARCHAR measures — SUM/AVG/etc. on measure columns from migrated tables are wrapped in TRY_TO_DOUBLE/TRY_TO_NUMBER when the column may be VARCHAR
25. VARCHAR date columns wrapped in TO_DATE — date-range comparisons on migrated *_DT columns wrap the column in TO_DATE(col, fmt), not just the compared value

If any check fails, fix the SQL before proceeding to compilation.

PREREQUISITE FOR STEP 6a: After self-check passes for each file, update conversion_state.json with self_check_passed: true. Set current_step: "6". Step 6a CANNOT begin until all files show self_check_passed: true.

Step 6a: Artifact Presence Verification (HARD GATE)

Before proceeding to Step 7, verify these artifacts exist on disk:

• All converted .sql files written to <output_dir>/
• Count of output .sql files matches expected count from Step 4 classification

Enforcement:

1. Check each artifact via filesystem read
2. If ANY missing:
   • Print: "BLOCKED: Cannot proceed to Step 7. Missing: [list of missing files]"
   • Update conversion_state.json: set gates.step_6a_artifacts: "BLOCKED"
   • Attempt to generate missing files (re-run Step 5 for the specific missing file)
   • If generation fails: surface to user with specific remediation action
   • DO NOT proceed until gate passes
3. If all present:
   • Update conversion_state.json: set gates.step_6a_artifacts: "PASSED"
   • Proceed to Step 7

Step 6b: State Persistence (automatic — no separate step needed)

The conversion_state.json file has been incrementally updated throughout Steps 3-6a. At this point it contains the full classification, conversion status, and gate results. If context is exhausted here, the next session can resume from the earliest incomplete step by reading this file (see Step 1 pre-check).

Validation Decision Gate (MANDATORY — cannot be silently skipped)

Load workflows/validation-pipeline.md — the single authoritative validation orchestrator.

After Step 6a passes, present the two-tier consent gate (validation runs end-to-end by default once approved — the user does NOT hand-pick phases):

• Tier-1 (zero cost): "Run local validation (synthetic data + LLM trace)?" → Yes runs Phase 1 + Phase 2 automatically.
• Tier-2 (Snowflake credits): after Phase 2, ONE combined prompt: "Run full Snowflake validation end-to-end (compile + execute + integration/E2E orchestration)?" → Yes runs Phase 3 + Phase 4 + Phase 5 automatically, no further per-phase prompts.

Phases:

• Phase 1: Synthetic Data (always runs under Tier-1) — with range-join/unit alignment (see synthetic-data-rules.md)
• Phase 2: LLM Trace (0 credits)
• Phase 3: Snowflake Compilation (minimal credits)
• Phase 4: Snowflake Execution — Tier 2/3 procedures (moderate credits)
• Phase 5: Integration & End-to-End Orchestration Test (moderate credits) — generates a reusable orchestration harness, runs the full pipeline in DAG order, asserts terminal tables non-empty

Each phase has a HARD ARTIFACT GATE that verifies output files exist on disk before allowing the next phase to begin. At each gate, append a line to checkpoint_log.jsonl. See workflows/validation-pipeline.md for full protocol.

Phase-specific reference loading:

PREREQUISITE: The user's tier consent decisions must be recorded in conversion_state.json under validation_selections (tier1_approved, tier2_approved).

TRANSITION GATE: Steps 7-10 Are NOT Optional

⛔ The conversion workflow is NOT complete after Step 6. Steps 7, 8, 9, and 10 are MANDATORY continuations — not optional quality gates:

• Validation Pipeline: Load workflows/validation-pipeline.md. Present the two-tier consent gate. Once a tier is approved, its phases execute sequentially and automatically (no per-phase prompts). At every checkpoint, append a line to checkpoint_log.jsonl.
  • Phase 1 (Synthetic Data): Runs under Tier-1. Zero credits. HARD GATE on source_table_ddl.sql + synthetic_data.sql.
  • Phase 2 (LLM Trace): Runs under Tier-1. Zero credits. HARD GATE on tests/<file>/expected/*.csv.
  • Phase 3 (Snowflake Compilation): Runs under Tier-2. Minimal credits. HARD GATE on compilation_results.json.
  • Phase 4 (Snowflake Execution Tier 2/3): Runs under Tier-2. Moderate credits. HARD GATE on snowflake_execution_results.json.
  • Phase 5 (Integration & E2E Orchestration): Runs under Tier-2. Moderate credits. Generates reusable harness (orchestration/sp_e2e_pipeline.sql, task_dag.sql, adf_pipeline.sql, testing/setup_test_data.sql, testing/expected_results.sql). HARD GATE on e2e_test_results.json.
• Step 10: Generate conversion_report.md with ALL sections. Generate conversion_report.docx (best effort).

DO NOT present final output to the user until Step 10 is complete.

If context window is nearly exhausted after Step 6, save all converted files to disk. The conversion_state.json file already captures progress — the next session will resume from the earliest incomplete step.

Load workflows/steps-8-10-post-conversion.md for Step 10 (Report Generation) only. Steps 8 and 9 are now handled by the modular validation pipeline.

POST-STEP-10 ARTIFACT GATE (HARD GATE)

After Step 10, verify these artifacts exist on disk:

Required artifacts:

• conversion_state.json with current_step: "complete" (the state tracker is the primary proof of workflow completion)
• checkpoint_log.jsonl (append-only audit trail; must contain at least the Step 1 STARTED line and a final complete line)
• compilation_results.json (even if compilation skipped, file must exist with status)
• source_table_ddl.sql (required in batch mode)
• synthetic_data.sql (required in batch mode)
• e2e_test_results.json + orchestration/sp_e2e_pipeline.sql (required when Phase 5 ran)
• conversion_report.md with all 6 mandatory sections

Best-effort artifacts:

• conversion_report.docx (if missing, print: "DOCX not generated. Install with: pip install python-docx OR brew install pandoc")

Enforcement:

1. Check each required artifact via filesystem read
2. If ANY required artifact missing:
   • Print: "BLOCKED: Conversion incomplete. Missing: [list]"
   • Update conversion_state.json: set gates.step_10_final_artifacts: "BLOCKED"
   • Attempt to generate missing artifacts (re-run the

…(truncated)