Migration
Snowflake migration gotchas: what breaks without an error
The Snowflake behaviour differences that pass testing and break in production: unenforced primary keys, case-sensitive comparisons, identifier case, NULL vs empty string, timestamps and row-by-row procedures.

Hemant Walchale
· 5 min read
Most problems in a migration to Snowflake fail loudly: a function doesn’t exist, a data type won’t convert, a procedure won’t compile. Those get fixed during testing. The expensive problems are the ones that run without an error and return slightly different answers. They tend to surface weeks after cutover, in a finance report.
These are the behaviour differences we check on every migration to Snowflake, whatever the source.
The quick checklist
| Behaviour | What you may be used to | In Snowflake | What to do |
|---|---|---|---|
| Primary and unique keys | Enforced; duplicates rejected | Not enforced on standard tables (only NOT NULL is) |
Add duplicate checks; load with MERGE |
| String comparison | Case-insensitive (SQL Server default) | Case-sensitive | Collation per column, ILIKE, or normalise on load |
| Identifier case | Stored as written (PostgreSQL folds to lowercase) | Unquoted names stored in uppercase | Create objects without quotes |
| Empty string | Same as NULL (Oracle) |
'' and NULL are different |
Review IS NULL and NVL logic |
Plain TIMESTAMP |
One fixed type | Depends on TIMESTAMP_TYPE_MAPPING |
Always write TIMESTAMP_NTZ, _LTZ or _TZ explicitly |
| Row-by-row updates | Cheap | Each update rewrites micro-partitions | Rewrite cursors as set-based SQL |
| Session timezone | Server timezone | Per user or session TIMEZONE parameter |
Set TIMEZONE on service users |
1. Keys aren’t enforced
Snowflake lets you declare PRIMARY KEY, UNIQUE and FOREIGN KEY constraints, and BI tools read them, but standard tables only enforce NOT NULL. If an old load relied on a unique index to throw away duplicate rows, the same load in Snowflake will insert them, and every downstream sum doubles for the affected keys.
What to do:
- Load with
MERGEon the business key instead ofINSERT. - Deduplicate the source in the same statement, keeping the latest row per key. A
MERGEwith two source rows for one target row is nondeterministic, and Snowflake raises an error by default (ERROR_ON_NONDETERMINISTIC_MERGE). - Add a scheduled check per important table:
SELECT key, COUNT(*) ... GROUP BY key HAVING COUNT(*) > 1.
2. Comparisons are case-sensitive
SQL Server databases usually run a case-insensitive collation, so WHERE status = 'open' also matches 'Open' and 'OPEN'. Snowflake compares strings exactly by default. Nothing errors; the filter just returns fewer rows, and the joins on email address or customer code match less often.
What to do: find the columns where case varies (codes, emails, free-text statuses), then either standardise them on load with UPPER() or LOWER(), or give the column a case-insensitive collation, for example COLLATE 'en-ci'. Collations have performance and feature trade-offs, so prefer normalising on load for join keys.
3. Identifier case
Snowflake stores unquoted identifiers in uppercase: create table orders creates ORDERS. Tools that generate DDL with quoted lowercase names ("orders") create tables that can then only be referenced with the quotes. This is common with migration and replication tools and with dataframes written from Python.
Decide on one convention before the first table moves. Create objects without quotes, and check what your loading tools generate. Snowflake also has a QUOTED_IDENTIFIERS_IGNORE_CASE parameter, but changing it after objects exist can make some of them unreachable, so set it at the start or not at all.
4. NULL and empty strings (from Oracle)
Oracle treats an empty string as NULL. Snowflake doesn’t. Code that checks WHERE col IS NULL to find “missing” values will miss the empty strings that the source now delivers, and NVL(col, 'n/a') stops replacing them. Look for IS NULL, NVL, COALESCE and DECODE on text columns, and decide whether to convert '' to NULL on load. NULL_IF in a file format can do this for files.
5. Timestamps
Bare TIMESTAMP in DDL means whatever TIMESTAMP_TYPE_MAPPING says, which is TIMESTAMP_NTZ by default but can be changed at account, user or session level. Two other points catch teams out:
- A string without an offset loaded into
TIMESTAMP_LTZorTIMESTAMP_TZis interpreted in the loading session’sTIMEZONE. If the loader’s service user has a different default from your analysts, data is shifted from day one. TIMESTAMP_LTZvalues cast toDATEuse the reader’s session timezone, so daily totals can change depending on who runs the query.
Write the full type name in every DDL statement, set TIMEZONE explicitly on service users, and store event times in UTC.
6. Row-by-row procedures
Cursor loops that update one row at a time are normal in SQL Server and Oracle procedures. Snowflake runs them, but each update rewrites the micro-partitions holding that row, so a loop over 100,000 rows can turn into 100,000 small rewrites. It runs slower and burns credits without anyone noticing at first.
Find them early: search the source code for cursors, WHILE loops and FETCH. Rewrite each as a set-based MERGE or INSERT ... SELECT. This is usually the largest single line in a migration estimate, and the one most often left out.
How to catch these before cutover
- Run old and new in parallel for at least one month-end, on the same inputs.
- Reconcile beyond row counts. Compare row counts, distinct keys, sums of amounts and counts of
NULLand empty values per column. Case andNULLproblems show up in distinct counts and null counts, not in row counts. - Test reports, not just tables. Run the top 20 reports on both systems and compare the totals.
The short version
Snowflake’s differences are mostly well documented, but they don’t raise errors, so they slip past testing. Check key enforcement, case sensitivity, identifier case, empty strings, timestamp types and row-by-row procedures before you estimate, and reconcile on distinct and null counts, not just row counts. Platform-specific guides: SQL Server to Snowflake, Oracle to Snowflake, Teradata to Snowflake and Redshift to Snowflake. For a first estimate, try the migration estimator.
Sources
- Overview of constraints, Snowflake documentation
- Collation support, Snowflake documentation
- Identifier requirements, Snowflake documentation
- Date and time data types, Snowflake documentation
- MERGE, Snowflake documentation
- Hybrid tables, Snowflake documentation
Not sure where your own platform stands? Take the free 3-minute data platform scorecard, or see how we work on Snowflake. Need people to do this alongside your team? See data engineering consultants. Unfamiliar terms are explained in the data engineering glossary.