Data Warehouse Dialect Transpilation Report

Role: Senior Data Platform Architect specializing in enterprise cloud warehouse migrations and automated SQL dialect modernization.

Context

• Source SQL Dialect: {{source_dialect}}
• Target Data Platform: {{target_platform}}
• Legacy Transformation Logic: {{legacy_sql_scripts}}
• Partitioning & Clustering Strategy: {{partition_clustering_strategy}}
• Daily Ingestion Volume: {{data_volume_gb}}
• Concurrency & User SLA: {{concurrency_requirements}}

Task

Evaluate legacy transformation pipelines to produce a comprehensive dialect modernization and transpilation report that ports legacy SQL into target-optimized native syntax with compute-efficiency guarantees.

Method

1. Deconstruct {{legacy_sql_scripts}} to identify proprietary functions, session-level variables, and non-ANSI SQL constructs from {{source_dialect}}.
2. Map source procedural constructs (cursors, dynamic SQL, temporary tables) to declarative ELT patterns native to {{target_platform}}.
3. Align join, aggregation, and filtering logic with the specified {{partition_clustering_strategy}} to avoid full table shuffles.
4. Rewrite queries utilizing modern native features such as micro-partitioning, QUALIFY filters, or materialized CTEs.
5. Model compute credit consumption and slot contention based on {{data_volume_gb}} and {{concurrency_requirements}}.
6. Formulate a regression test matrix validating numeric precision, timestamp time zone handling, and null collation parity.
7. Outline a phased deployment plan highlighting cutover dependencies and rollback triggers.

Constraints

• MUST write target SQL adhering strictly to modern native idioms of {{target_platform}}.
• MUST explicitly document data type mapping divergences (e.g., FLOAT vs. NUMERIC scale).
• MUST NOT introduce non-deterministic functions that invalidate micro-partition pruning.
• All transpiled code must be production-ready and free of syntax place-fillers.

Output format

1. Migration Scope & Dialect Discrepancy Overview (table & summary)
2. Transpiled Native SQL Pipeline (Complete modernized SQL block)
3. Storage & Partitioning Optimization Architecture (150 words)
4. Compute & Concurrency Scaling Model (Projected consumption table)
5. Quality Assurance & Semantic Validation Checklist (5 test cases)

Self-review

• Verify zero deprecated or legacy {{source_dialect}} syntax remnants in target code.
• Ensure partitioning alignment prevents skew during high-volume joins on {{data_volume_gb}}.
• Check that timezone and timestamp semantics match across dialect boundaries.