Target data type support & mapping for Snowflake sources
The table below maps the data types of a Showflake source to the data types of a target such as Azure Synapse, BigQuery, Databricks, and so on when you create an application using a wizard with Auto Schema Creation, perform an initial load using Database Reader with Create Schema enabled, or run the schema conversion utility, or when Striim schema evolution creates or alters target tables.
If a table contains a column of a geospatial type (GEOGRAPHY or GEOMETRY), Snowflake Reader cannot capture UPDATE or DELETE operations. This is a limitation of Snowflake's Change Streams (see Guides > Data Loading > Streams > Introduction to Streams > Types of Streams and Compilation errors when select from a Stream : Invalid argument types for function 'EQUAL_NULL': (GEOMETRY, GEOMETRY).
If your screen is not wide enough to display the whole table, click in a cell and drag left to view the right-hand columns.
Snowflake Data Type | Azure Synapse | BigQuery | Databricks | Db2 for z/OS | Fabric Mirror | MariaDB | MySQL | Oracle | PostgreSQL | Snowflake | Spanner | Spanner PG Dialect | SQL Server | Yellowbrick |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
ARRAY | varchar(8000) | string | string | varchar(250) | string | text | text | VARCHAR2(200) | character varying | VARCHAR | STRING(MAX) | text | varchar(8000) | character varying |
BINARY | binary | bytes(p) | binary | blob | bytes | Not supported | Not supported | BLOB | bytea | BINARY | BYTES(p) | Not supported | binary | character varying(40000) |
BINARY(p) | binary(p), if (p) <= 8000 binary, if (p) > 8000* | bytes, if (p) > 9223372036854775807* bytes(p), if (p) <= 9223372036854775807 | binary | binary(255), if (p) <= 127 blob, if (p) > 127* | bytes | binary(p), if (p) <= 255 | binary(p), if (p) <= 255 | BLOB | bytea, if (p) <= 2147483647 | BINARY, if (p) <= 8388608 BINARY, if (p) > 8388608* | BYTES(MAX), if (p) > 10485760* BYTES(p), if (p) <= 10485760 | bytea, if (p) <= 1048576 | binary(p), if (p) <= 8000 binary, if (p) > 8000* | character varying(40000) |
BOOLEAN | BIT | boolean | boolean | Not supported | boolean | bool | bool | CHAR(5) | boolean | BOOLEAN | BOOL | boolean | BIT | boolean |
DATE | date | date | date | date | date | date | date | date | date | date | DATE | date | date | date |
DOUBLE | float(53) | float64 | double | double | double | double | double | double precision | double precision | double precision | FLOAT64 | float8 | float(53) | double precision |
GEOGRAPHY | varchar(8000) | string | string | varchar(250) | string | text | text | VARCHAR2(200) | character varying | VARCHAR | STRING(MAX) | text | varchar(8000) | character varying |
GEOMETRY | varchar(8000) | string | string | varchar(250) | string | text | text | VARCHAR2(200) | character varying | VARCHAR | STRING(MAX) | text | varchar(8000) | character varying |
NUMBER | numeric(p,s) | bignumeric | decimal(p,s) | numeric | decimal | decimal(p,s) | decimal(p,s) | number(p,s) | numeric(p,s) | numeric(p,s) | NUMERIC | numeric | numeric(p,s) | decimal(p,s) |
NUMBER(p,0) | numeric(p,s), if (p) <= 38, if (s) <= 38 | numeric, if (p) <= 29, if (s) <= 9 | decimal(p,s), if (p) <= 38, if (s) <= 37 | numeric(p,s), if (p) <= 31, if (s) <= 30 | decimal, if (p) <= 38 | decimal(p,s), if (p) <= 65, if (s) <= 30 | decimal(p,s), if (p) <= 65, if (s) <= 30 | number(p,s), if (p) <= 38, if (s) <= 127 | numeric(p,s), if (p) <= 1000, if (s) <= 1000 | numeric(p,s), if (p) <= 38, if (s) <= 37 | NUMERIC, if (p) <= 38, if (s) <= 9 | numeric, if (p) <= 131072, if (s) <= 16384 | numeric(p,s), if (p) <= 38, if (s) <= 38 | decimal(p,s), if (p) <= 38, if (s) <= 38 |
NUMBER(p,s) | varchar(8000), if (s) > 38* varchar(8000), if (p,s) > 38* numeric(p,s), if (p) <= 38, if (s) <= 38 | string, if (s) > 38* string, if (p,s) > 39* numeric, if (p) <= 29, if (s) <= 9 bignumeric, if 29 <= (p) <= 39, if 9 <= (s) <= 38 | string, if (p,s) > 38* decimal(p,s), if (p) <= 38, if (s) <= 37 string, if (s) > 37* | numeric(p,s), if (p) <= 31, if (s) <= 30 numeric, if (p,s) > 31, if (s) > 30* | string, if (p,s) > 38* decimal, if (p) <= 38 | TEXT, if (p,s) > 65, if (s) > 30* decimal(p,s), if (p) <= 65, if (s) <= 30 | TEXT, if (p,s) > 65, if (s) > 30* decimal(p,s), if (p) <= 65, if (s) <= 30 | number, if (s) > 127* number, if (p,s) > 38* number(p,s), if (p) <= 38, if (s) <= 127 | double precision, if (s) > 1000 double precision, if (p,s) > 1000 numeric(p,s), if (p) <= 1000, if (s) <= 1000 | VARCHAR, if (s) > 37* numeric(p,s), if (p) <= 38, if (s) <= 37 VARCHAR, if (p,s) > 38* | STRING(MAX), if (p,s) > 308, if (s) > 15* NUMERIC, if (p) <= 38, if (s) <= 9 | numeric, if (p) <= 131072, if (s) <= 16384 | varchar(8000), if (s) > 38* varchar(8000), if (p,s) > 38* numeric(p,s), if (p) <= 38, if (s) <= 38 | character varying, if (s) > 38* character varying, if (p,s) > 38* decimal(p,s), if (p) <= 38, if (s) <= 38 |
OBJECT | varchar(8000) | string | string | clob | string | json | json | clob | json | VARIANT | STRING(MAX) | jsonb | varchar(max) | character varying |
TIME | time | time | string | time | time-millis | time | time | VARCHAR2(150) | time | time | STRING(150) | varchar(150) | time | time |
TIME(p) | time | time | string | time | time-millis | time | time | VARCHAR2(150) | time | time | STRING(150) | varchar(150) | time | time |
TIMESTAMPLTZ | datetime2 | timestamp | timestamp | timestamp | local-timestamp-millis | timestamp | timestamp | timestamp with local time zone | timestamp without time zone | timestamp | TIMESTAMP | timestamptz | datetime2 | timestamp |
TIMESTAMPLTZ(p) | datetime2 | timestamp | timestamp | timestamp | local-timestamp-millis | timestamp | timestamp | timestamp with local time zone | timestamp without time zone | timestamp | TIMESTAMP | timestamptz | datetime2 | timestamp |
TIMESTAMPNTZ | datetime2 | timestamp | timestamp | timestamp | timestamp-millis | datetime | datetime | timestamp | timestamp without time zone | timestamp | TIMESTAMP | timestamptz | datetime2 | timestamp |
TIMESTAMPNTZ(p) | datetime2 | timestamp | timestamp | timestamp | timestamp-millis | datetime | datetime | timestamp | timestamp without time zone | timestamp | TIMESTAMP | timestamptz | datetime2 | timestamp |
TIMESTAMPTZ | datetimeoffset | timestamp | timestamp | timestamp with time zone | string | timestamp | timestamp | timestamp with time zone | timestamp with time zone | timestamp with time zone | TIMESTAMP | timestamptz | datetimeoffset | timestamptz |
TIMESTAMPTZ(p) | datetimeoffset | timestamp | timestamp | timestamp with time zone | string | timestamp | timestamp | timestamp with time zone | timestamp with time zone | timestamp with time zone | TIMESTAMP | timestamptz | datetimeoffset | timestamptz |
VARCHAR | varchar(p) varchar(8000) | string | string | clob(p) varchar(p) | string | longtext varchar(p) | longtext varchar(p) | clob VARCHAR2(p) | character varying(p) | VARCHAR(p) | STRING(p) STRING(MAX) | text varchar(p) | varchar(p) varchar(max) | character varying(p) |
VARCHAR(p) | varchar(p), if (p) <= 8000 varchar(8000), if (p) > 8000* | string | string | varchar(p), if (p) <= 4045 clob(p), if 4045 <= (p) <= 2147483647 | string | longtext, if (p) > 2147483647* longtext, if 65535 <= (p) <= 2147483647 varchar(p), if (p) <= 65535 | longtext, if (p) > 2147483647* longtext, if 65535 <= (p) <= 2147483647 varchar(p), if (p) <= 65535 | clob, if (p) > 4000* VARCHAR2(p), if (p) <= 4000 | character varying(p), if (p) <= 10485760 character varying, if (p) > 10485760* | VARCHAR, if (p) > 16777216* VARCHAR(p), if (p) <= 16777216 | STRING(MAX), if (p) > 2621440 STRING(p), if (p) <= 2621440 | varchar(p), if (p) <= 1048576 text, if (p) > 1048576* | varchar(max), if (p) > 8000* varchar(p), if (p) <= 8000 | character varying, if (p) > 64000* character varying(p), if (p) <= 64000 |
VARIANT | varchar(8000) | string | string | clob | string | LONGTEXT | LONGTEXT | clob | text | VARCHAR | STRING(MAX) | text | varchar(max) | character varying |
*When using the schema conversion utility, these mappings appear in converted_tables_with_striim_intelligence.sql.