Translating legacy SQL to Snowflake and designing automated verification systems — because I'm not going to find out about a broken join from a controller three weeks after go-live.
Legacy → cloud-native
I don't let the team just port the schema over. I make three explicit calls about how the data will actually move, and hold the line on each of them through the build.
Bulk history load
Every wave does a full historical load into Snowflake first, validated before anyone flips on the incremental feed.
Incremental / CDC dual-run
I run both platforms in parallel and reconcile daily, so I can prove the target is current before anyone depends on it.
Code conversion
I size T-SQL-to-Snowflake-Scripting conversion as its own workstream — it will be the bulk of the engineering effort.
Before anyone writes a mapping row, this is the picture I put in front of Steering and Data Engineering both — one diagram, so nobody is building against a different mental model of how data actually moves.
This is the one document where I get the legacy schema, the new schema, and the actual business meaning to agree in a single row. Every cell closes a question I don'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 have to agree before code gets 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 isn't just data types. I own re-mapping the access model too: legacy AD-group table permissions become Snowflake role-based access control, with column-level masking policies I require 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 who's 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 decide early that "migrated" isn't a status I'll accept — only "reconciled" is. And because Snowflake accepts but doesn't enforce PK/FK constraints, integrity checks have 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 starts means I can'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 |
The judgment call
What I Decide Here
Decision: Once I confirm that Snowflake doesn't enforce runtime PK/FK constraints, I halt sprint coding already underway to build PK/FK verification directly into the automated test suite instead of trusting the schema.
Friction: The architects balk — two weeks of added design overhead when the build calendar is already tight isn't an easy sell.
Outcome: It catches what the schema never would have — and keeps silent duplicate records out of the financial reports during execution, which is exactly where they'd surface first.