Skip to content
METRIC OAK

New: the 3-week Data Platform Assessment: fixed fee, priced roadmap. See what's included →

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

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 MERGE on the business key instead of INSERT.
  • Deduplicate the source in the same statement, keeping the latest row per key. A MERGE with 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_LTZ or TIMESTAMP_TZ is interpreted in the loading session’s TIMEZONE. If the loader’s service user has a different default from your analysts, data is shifted from day one.
  • TIMESTAMP_LTZ values cast to DATE use 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

  1. Run old and new in parallel for at least one month-end, on the same inputs.
  2. Reconcile beyond row counts. Compare row counts, distinct keys, sums of amounts and counts of NULL and empty values per column. Case and NULL problems show up in distinct counts and null counts, not in row counts.
  3. 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

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.

Frequently asked questions

Does Snowflake enforce primary keys and unique constraints?

Not on standard tables. Snowflake accepts PRIMARY KEY, UNIQUE and FOREIGN KEY definitions but only enforces NOT NULL. Loads that relied on a unique index to reject duplicates will insert them silently, so add explicit duplicate checks or MERGE logic. Hybrid tables are the exception and do enforce primary keys.

Are string comparisons case-sensitive in Snowflake?

Yes, by default. Coming from SQL Server's usual case-insensitive collation, WHERE status = 'open' stops matching 'OPEN'. Fix it per column with a case-insensitive collation, with UPPER() or ILIKE in queries, or by standardising case on load.

Why do some tables need quotes around their names after migration?

Snowflake stores unquoted identifiers in uppercase. A table created by a tool with a quoted lowercase name, such as "orders", can only be referenced with quotes from then on. Create objects without quotes, or set QUOTED_IDENTIFIERS_IGNORE_CASE deliberately before migration.

What's the biggest hidden cost in a migration to Snowflake?

Usually stored procedures and cursor loops that update rows one at a time. They run, but each update rewrites micro-partitions, so they get slower and cost more credits. Rewriting them as set-based MERGE or INSERT ... SELECT is where lift-and-shift estimates most often go wrong.

Have 30 minutes? Get a senior architect’s view of your data platform.

No sales deck. You talk to the engineer who would lead the work.

Book an architecture review
Hemant WalchaleYou talk to Hemant Walchale, founder145 published guidesProfiles: LinkedIn · Clutch · GoodFirms

Free, takes 20 seconds

Get your starter data platform roadmap

Pick your platform and your main goal. You get a 90-day plan written by senior data engineers: what to do first, next and after.

You’ll also get our monthly email (confirm it in your inbox). Unsubscribe any time. Privacy policy.