Working on loading of data into Snowflake (Online DWH). The first step is to have the database and tables created in snowflake.
After creating database, opened the worksheet to run create table query (quite straight forward)
CREATE TABLE [DATA].[BOLS]
(
ID INT,
BOLNUMBER VARCHAR(50),
ROWGUID UNIQUEIDENTIFIER,
STPNUMBER INT,
BB_ID SMALLINT,
ACCTNO VARCHAR(6),
CREATEDATE DATETIME
)
But when clicked on Run, got following error in result.
Like wise for other tables got below errors
SQL compilation error: Unsupported data type 'MONEY'
SQL compilation error: Unsupported data type 'bit'
Why it happened
Snowflake is a data platform and data warehouse that supports the most common standardized version of SQL: ANSI. This means that all of the most common operations are usable within Snowflake. Snowflake also supports all of the operations that enable data warehousing operations, like create, update, insert, etc.
However it's not true with the all the datatype used in SQL, that's what the error message say so 😉
What to do
You need to find out the equivalent datatypes in Snowflake for the ones you get error for and you will need to rewrite this datatype in Snowflake
Like in my case following were the equivalents to be used
- DECIMAL is the equivalent for MONEY datatype in Snowflake.
- STRING is the equivalent for UNIQUEIDENTIFIER datatype in Snowflake.
- BOOLEAN is the equivalent for BIT datatype in Snowflake.
