Introduction
I'm currently involved in a migration project from Azure SQL Database to Snowflake and I would like to share something that I've learned regarding the conversion of datatypes from Azure SQL Database. For this blogpost I used the Migration guide. So you'll see most of the same conversions in this blogpost, but with some small changes. For instance, in the migration guide "Datetime" is the Snowflake alternative for the SQL Database "Datetime" and that is not true. It is Timestamp_NTZ.
Yet another thing I've added is that, although Snowflake doesn't use the SQL Server datatype, it recognizes the SQL Datatype and turns it into a Snowflake datatype. For instance, Snowflake understands "Datetime" and it will automatically convert into Timestamp_NTZ datatype. So, I've added the supported column : "Is the SQL Server datatype supported by Snowflake or not?" For instance, "Bit" is not recognized by Snowflake. I don't know why, but most of the SQL Server datatypes are recognized and converted into Snowflake datatypes.
This is also applicable for SQL Server and Azure Synapse
Azure SQL Database and Snowflake Datatypes
Here is the list of the Azure SQL Database datatypes that are supported or not and the Snowflake equivalent.
| SQL SERVER | Snowflake | Comments | |
| Supported | Preferred | |
BIGINT | Y | NUMBER | Precision and scale not to be specified when using Numeric. |
BINARY | Y | BINARY | Snowflake: maximum length is 8 MB. |
BIT | N | BOOLEAN | Recommended: Use NUMBER if migrating value-to-value to capture the actual BIT value. Use Boolean in Snowflake if the desired outcome is to restrict to Ternary Logic (three valued): TRUE, FALSE or NULL (UNKNOWN). |
CHAR | Y | VARCHAR(1) | Any set of strings that is shorter than the maximum length is not space-padded at the end. |
DATE | Y | DATE | Default in SQL Server is YYYY-MM-DD. |
DATETIME | Y | TIMESTAMP_NTZ | SQL Server datetime is not ANSI or ISO 8501 compliant. Storage size is 8 bytes. Accuracy is rounded to increments of .000 .003 or .007. |
DATETIME2 | N | TIMESTAMP_NTZ | Snowflake: TIMESTAMP with no time zone time zone is not stored. DATETIME2 has a default precision of up to 7 digits Snowflake has TIMESTAMP_NTZ with the precision of 9 digits. |
DATETIMEOFFSET | N | TIMESTAMP_LTZ | SMALLDATETIME is not ANSI or ISO 8601 compliant. It has a fixed 4 bytes storage space. TIMESTAMP_LTZ Up to 34 7 in precision scale. |
DECIMAL | Y | NUMBER | Default precision and scale are (38,0). |
FLOAT | Y | FLOAT | Snowflake uses double-precision (64-bit) IEEE 754 floating point numbers. |
IMAGE | N | N/A | Use NVARCHAR, VARCHAR, or VARBINARY instead. |
INT | Y | NUMBER | Precision and scale not to be specified when using Numeric. |
MONEY | N | NUMBER | Money has a range of 19 digits with a scale of 4 digits, so NUMBER(19,4) can be used. |
NCHAR | Y | VARCHAR(1) | CHAR is used on fixed-length-string data |
NTEXT | N | VARCHAR | This data type will be discontinued on SQL Server. Use NVARCHAR, VARCHAR, or VARBINARY instead. |
NUMERIC | Y | NUMBER | Default precision and scale are (38,0). |
NVARCHAR | Y | VARCHAR | NVARCHAR’s string length can range from 1–4000. |
REAL | Y | FLOAT | The ISO synonym for REAL is FLOAT(24). |
SMALLDATETIME | N | TIMESTAMP_NTZ | SMALLDATETIME is not ANSI or ISO 8601 compliant. It has a fixed 4 bytes storage space. |
SMALLINT | Y | NUMBER | Default precision and scale are (38,0). |
SMALLMONEY | N | NUMBER | NUMBER with precision of 10 digits, with a scale of 4, so NUMBER(10,4) can be used. |
TEXT | Y | VARCHAR | This data type will be discontinued on SQL Server. Use NVARCHAR, VARCHAR, or VARBINARY instead. |
TIME | Y | TIME | SQL Server has a precision of 7 nanoseconds. Snowflake has precision of 9 nanoseconds |
TIMESTAMP | Y | TIMESTAMP_NTZ | Use DATETIME2 or CURRENT_TIMESTAMP function. |
TINYINT | Y | NUMBER | Default precision and scale are (38,0). |
UNIQUEIDENTIFIER | N | STRING | Not Supported. |
VARBINARY | Y | BINARY | Snowflake: maximum length is 8 MB. |
VARCHAR | Y | VARCHAR | Any set of strings that are shorter than the maximum length is not space-padded at the end. |
Final thoughts
This blogpost is a list of Azure SQL Database to Snowflake datatypes conversions
Hennie de Nooijer
Geen opmerkingen:
Een reactie posten