Translating legacy SQL to Snowflake and designing robust automated verification systems — because I wasn't going to find out about a broken join from a controller three weeks after go-live.
Legacy → cloud-native
I didn't let the team just port the schema over. I made three explicit calls about how the data would actually move, and held the line on each of them through the build.
Bulk history load
I had every wave do a full historical load into Snowflake first, validated before anyone flipped on the incremental feed.
Incremental / CDC dual-run
I ran both platforms in parallel and reconciled daily, so I could prove the target was current before anyone depended on it.
Code conversion
I sized T-SQL-to-Snowflake-Scripting conversion as its own workstream — I knew going in it would be the bulk of the engineering effort.
Before anyone wrote a mapping row, this is the picture I put in front of Steering and Data Engineering both — one diagram, so nobody was building against a different mental model of how data actually moved.
This is the one document where I forced the legacy schema, the new schema, and the actual business meaning to agree in a single row. Every cell closed a question I didn't want reopened during UAT.
Without a mapping row that names the transformation and the owner, ambiguity doesn't disappear — it just gets resolved by whoever writes the SQL, quietly, weeks later. This is the single place engineers and the business had to agree before code got written.
| Source (SQL Server) | Type · grain | Transformation | Target (Snowflake) | Validation | Owner |
|---|---|---|---|---|---|
| FactOpportunity.CloseAmt | MONEY · per opp / snapshot | MONEY→NUMBER(19,4); FX to USD on close date; null rejected, not defaulted | FACT_OPPORTUNITY.CLOSE_AMOUNT_USD | Sum + row-count reconcile; FK to DIM_ACCOUNT resolves | Sales Finance SME |
| FactOpportunity.CloseDate | DATETIME · per opp | DATETIME→TIMESTAMP_NTZ; timezone normalized | FACT_OPPORTUNITY.CLOSE_DATE | Min/max date parity per period | Sales Finance SME |
| DimAccount.AcctId | IDENTITY · per account | IDENTITY→sequence; surrogate key re-seeded & remapped | DIM_ACCOUNT.ACCOUNT_KEY | Distinct-count parity; no orphan facts | Data Governance |
I also built an interactive deep-dive on this mapping — where each cell explains what it carries and what I owned as TPM: open the mapping deep-dive →
Schema translation wasn't just data types. I owned re-mapping the access model too: legacy AD-group table permissions became Snowflake role-based access control, with column-level masking policies I required on every PII field — SSN, email, phone — for anyone outside Finance and Compliance.
| Report | Consumers | My call | Why |
|---|---|---|---|
| Revenue by Segment | Sales Finance, Exec | Rebuild | High-value; worth re-modeling for Snowflake performance |
| Pipeline Snapshot | Sales Ops | Migrate | Re-point to new source; logic unchanged |
| Legacy Quota v3 (copy) | — none in 12 mo | Retire | Duplicate of active report; no recent access |
Risks I don't write down are risks I only remember after they've become issues. A live register with a named owner per line is what turns "we should probably watch that" into someone actually watching it.
| # | Risk | Severity | How I mitigated it | Owner |
|---|---|---|---|---|
| R1 | Source data-quality gaps surface during load | High | I had discovery profile the data and agree cleansing rules before build started | Data Eng |
| R2 | Financial metric fails reconciliation post-load | High | I set zero-tolerance sum parity and required grain stated on both sides of every mapping row | Me / SME |
| R3 | T-SQL code conversion underestimated | Medium | I sized conversion as its own workstream and evaluated an accelerator tool | Data Eng |
| R4 | SME availability during design & UAT | Medium | I named owners per domain in the RACI and booked their time in advance | Me |
| R6 | Dual-run sync drift between platforms | Low | Daily reconciliation during dual-run with drift alerts | Data Eng |
Setting the bar before anyone wrote a join
I decided early that "migrated" wasn't a status I'd accept — only "reconciled" was. And because Snowflake accepts but doesn't enforce PK/FK constraints, I knew integrity checks had to move out of the schema and into the test suite, not stay assumed.
Snowflake won't tell you your keys are broken — it just lets you build on top of a bad join. Writing the tolerance down before build started meant I couldn't quietly loosen the bar later to hit a date.
| Test | What it proves | Tolerance I set |
|---|---|---|
| Row-count parity | No rows dropped or duplicated in transit | 0 |
| Sum / metric parity | Financial totals match — revenue to the penny | 0 on financials |
| Checksum / hash | Row-level values identical, not just aggregates | 0 |
| Referential integrity | No orphan facts — the check Snowflake won't run itself | 0 orphans |
| Report parity | Old dashboard vs. new, same inputs, same output | visual sign-off |
Scar tissue
What I Actually Decided Here
Decision: Once I realized Snowflake doesn't enforce runtime PK/FK constraints, I halted the sprint coding that was already underway to build PK/FK verification directly into the automated test suite instead of trusting the schema.
Friction: The architects balked — two weeks of added design overhead when we were already behind on the build calendar wasn't an easy sell.
Outcome: It caught what the schema never would have — and it kept silent duplicate records out of the financial reports during execution, which is exactly where they would have surfaced first.