Convert SQL Server data types to Snowflake: the full mapping table, the verification query for every risky type, and the conversions that load cleanly and change your data

How does SQL Server to Snowflake data type mapping work?

Sort the columns into three piles. Pile one converts cleanly and needs no thought: DATE, TIME, DECIMAL, NUMERIC, MONEY, FLOAT, CHAR and VARCHAR all have honest equivalents. Pile two has no target at all, and the tooling will tell you: SnowConvert marks CURSOR, HIERARCHYID, GEOMETRY and TABLE as unsupported and leaves them for you to redesign. Pile three is the dangerous one. Those columns convert, load and behave differently, and nothing in the migration will mention it.

Pile three is invisible to the tooling for a structural reason. A converter can tell you that a type has no target, because that is a fact about its own mapping table. It cannot tell you that a column which converted perfectly well was carrying a constraint the target will not enforce, or that the sort order of a key column just changed. Those are facts about your data and your application, and the only place the evidence still exists is the source database you are about to switch off.

SQL Server to Snowflake data type mapping table

The target column is what SnowConvert publishes. Every row was checked against Microsoft Learn and the Snowflake SQL reference on 2 September 2026. Rows marked "see below" are the ones that look equivalent and are not.

Eight risky mappings, and the query that proves each one is safe

This is the part no mapping table carries. For each risky conversion below, there is a query you can run against SQL Server today, before you load anything, that tells you whether the risk is theoretical or whether it is already sitting in your data. Run all eight in an afternoon. They are the cheapest work in the entire migration.

SELECT c.name, t.name AS type FROM sys.columns c JOIN sys.types t
  ON c.user_type_id = t.user_type_id
WHERE t.name IN ('tinyint','smallint');

SELECT count(*) FROM dbo.Orders
WHERE CAST(OrderDate AS time) = '00:00:00.000'
  AND DATEPART(millisecond, OrderDate) = 0;

SELECT count(DISTINCT DATEPART(tzoffset, EventAt)) AS distinct_offsets
FROM dbo.Events;

SELECT lower(Email), count(*) FROM dbo.Customers
GROUP BY lower(Email) HAVING count(*) > 1;

SELECT TOP 20 OrderGuid FROM dbo.Orders ORDER BY OrderGuid;

SELECT max(DATALENGTH(Payload)) AS max_bytes FROM dbo.Documents;

SELECT t.name, i.name FROM sys.indexes i JOIN sys.tables t
  ON i.object_id = t.object_id
WHERE i.is_unique = 1;

SELECT c.name FROM sys.columns c JOIN sys.types t
  ON c.user_type_id = t.user_type_id
WHERE t.name IN ('real','float');

Snowflake publishes two SQL Server mappings and they disagree

The table above uses the SnowConvert Transact-SQL reference, because it is the most detailed public mapping for this route. Snowflake also publishes a second one: the data mapping page for its Openflow Connector for SQL Server, which applies to both the standard and the CDC variant of that connector. The two documents are both Snowflake\'s own, they describe the same conversion, and on eight column types they give different answers. Neither page acknowledges the other exists.

The one that should stop a plan is GEOGRAPHY. SnowConvert maps it to a real Snowflake GEOGRAPHY type. The Openflow connector maps it to TEXT and states that values of that type "are inserted as NULL". If you scope the migration from the reference, which is the document most teams read, and then execute it with Snowflake\'s own connector, every spatial value silently becomes NULL. A NULL column reads like a column nobody populated rather than a column that was discarded in transit, so this is the kind of thing found months later by someone asking why the store locator stopped working.

ROWVERSION is the clearest case of one document simply being better, and Microsoft settles it. SnowConvert emits BINARY(8); the connector emits TEXT. Microsoft\'s own rowversion page states the type "is just an incrementing number and does not preserve a date or a time", and that a non-nullable rowversion column "is semantically equivalent to a binary(8) column". SnowConvert matches the source vendor exactly. Either way the auto-increment behavior does not survive, so any optimistic concurrency check needs a real replacement.

Three more are worth knowing before you pick a tool. MONEY becomes NUMBER(38,4) under SnowConvert and an unqualified NUMBER under the connector, and NUMBER with no arguments defaults to NUMBER(38,0), which is zero decimal places on a currency column. XML and SQL_VARIANT become VARIANT under SnowConvert and TEXT under the connector, which is a real fork rather than an error: one gives you semi-structured querying, the other gives you a string to parse. And the connector collapses DATETIME, DATETIME2 and SMALLDATETIME into a bare TIMESTAMP_NTZ where SnowConvert preserves the precision as TIMESTAMP_NTZ(3) and TIMESTAMP_NTZ(7).

The practical rule is that the document you plan from and the tool you execute with have to be one decision rather than two. It is not a quirk of this route either. On the neighboring one, Snowflake\'s MySQL to Snowflake connector maps that database\'s BIT to a hexadecimal string while this connector maps SQL Server\'s BIT straight to BOOLEAN, and the two connectors answer a decimal overflow two different ways again. The full tool-by-tool comparison, including which vendor implemented which document, is on our SQL Server to Snowflake migration tools page.

Does Snowflake enforce primary keys and unique constraints?

No, not on standard tables. Snowflake accepts PRIMARY KEY, UNIQUE and FOREIGN KEY definitions, records them as metadata that the query optimizer can use, and does not enforce any of them. Its constraints page is explicit that it "doesn't enforce them, except for NOT NULL and CHECK constraints, which are always enforced". Hybrid tables do enforce the full set, which is one reason to consider them for the handful of tables where integrity genuinely matters.

That CHECK clause is worth reading twice, because the summary repeated across most of this category is that NOT NULL is the only enforced constraint, and Snowflake's own constraints overview page still presents it that way. CHECK being enforced on standard tables is what turns the integer problem from a loss into a chore. You lost four range guards when TINYINT, SMALLINT, INT and BIGINT all became NUMBER(38,0). You can rebuild every one of them, and it is an afternoon of DDL rather than an application redesign.

What happens to my primary key after migrating to Snowflake?

It becomes documentation. The column stays, the constraint definition stays, queries that reference the column keep working, and the guarantee that no two rows share a value is gone. Nothing errors at any point. The practical consequence shows up the first time a pipeline replays a batch: the duplicate rows land, the load reports success, and a report double counts. This is why every ingestion path into Snowflake should land in a staging table and MERGE on the business key rather than appending.

It is also the strongest argument for watching the shape of your tables rather than trusting the load status. Row counts that drift from their own trend, a distinct count that falls below the row count on a supposedly unique column, or a daily volume that doubles overnight are all detectable, and none of them raise an error on their own. Teams that monitor freshness, volume and schema changes on the warehouse catch this class of problem in hours. Teams relying on the pipeline's own success log catch it when someone questions a number in a board pack.

Why does my Snowflake join return fewer rows than SQL Server?

Almost always collation. Snowflake compares strings case sensitively by default, and its collation reference lists case sensitivity as the default specification. The standard SQL Server installation collation for a US English locale, SQL_Latin1_General_CP1_CI_AS, is case insensitive. So a join on an email address that matched [email protected] to [email protected] in SQL Server does not match them in Snowflake, and the row simply does not appear in the result.

There are two honest fixes and one bad one. The good options are to normalise the values during the load, usually lowercasing the join keys, or to declare a case insensitive collation on the specific columns that need it. The bad option is wrapping every join predicate in lower() at query time, which works and quietly prevents Snowflake from pruning micro-partitions on that column. Normalise once, on the way in.

How do I handle DATETIMEOFFSET in Snowflake?

Declare the target column as TIMESTAMP_TZ explicitly and never let a generated script emit a bare TIMESTAMP. Snowflake's TIMESTAMP_TYPE_MAPPING session parameter defaults to TIMESTAMP_NTZ, which stores wallclock time with no zone at all. Writing an offset aware value into it discards the offset, raises nothing, and leaves you with timestamps that are all present, all plausible and several hours wrong for any record that did not originate in UTC.

Before you decide, run the distinct offset count from the table above. Plenty of estates that store DATETIMEOFFSET turn out to write a single offset for every row, in which case normalising to UTC in a TIMESTAMP_NTZ column is simpler and cheaper than carrying zone information nobody uses. If the count is above one, you need TIMESTAMP_TZ and you need it in the DDL from the first load, because backfilling a zone you already discarded is not possible.

Should I use NUMBER or FLOAT for money in Snowflake?

NUMBER, always, and this is the one place worth overriding a mapping that is technically correct. Snowflake treats FLOAT, REAL and DOUBLE all as 64 bit IEEE 754, so a SQL Server real column mapped to FLOAT is a faithful conversion of a value that was already an approximation. Faithfully carrying an approximation into a warehouse where finance will sum it is how you end up explaining a four cent variance to an auditor.

Use NUMBER(38,2) for currency amounts and NUMBER(38,4) where the source was money or smallmoney, since Microsoft documents those as accurate to a ten-thousandth. The migration is the cheapest moment you will ever have to make this change, because you are rewriting the DDL anyway and nothing downstream has been built on the new column yet.

What data types does Snowflake not support from SQL Server?

Four have no target defined at all: CURSOR, HIERARCHYID, GEOMETRY and TABLE. SnowConvert marks each as unsupported rather than guessing, which is the right behavior. HIERARCHYID is usually re-expressed as a materialised path in a VARCHAR column, since the tree structure matters more than the type. GEOMETRY needs re-deriving as GEOGRAPHY in WGS84 or storing as text, depending on whether you actually do spatial work in the warehouse.

ROWVERSION deserves separate mention because it converts, to BINARY(8), and then does nothing. In SQL Server it is an auto incrementing counter the engine maintains for optimistic concurrency. Snowflake will not maintain it, so what arrives is a frozen byte string that looks like a version and never changes. If the application used it for concurrency control, that logic needs a real replacement, typically an updated_at timestamp or an explicit version integer the writer increments.

Do it in this order

Inventory the types first, with a single query against sys.columns joined to sys.types. Run the eight checks above while the source is still live. Convert the schema with SnowConvert and then read what it produced rather than accepting it, paying particular attention to the integer columns and anything time zone aware. Decide explicitly what happens to every unique index you are about to stop enforcing. Then load once, reconcile on values rather than counts, and only after that turn on continuous replication.

The full tool comparison for this route, including what each vendor bills by and the eight published mapping claims we could not verify, is on our SQL Server to Snowflake migration tools page, and the step by step cutover sequence is in the SQL Server to Snowflake migration guide. The equivalent treatment for the other commercial routes is in convert SQL Server T-SQL to PostgreSQL and convert MySQL data types to PostgreSQL. If SQL Server is staying alive alongside the warehouse, the ongoing case is covered in Snowflake ETL tools, and the budget model behind either program is in what a data migration really costs.