Data Types
Datafold facilitates data diffing by supporting a wide range of basic data types across major database systems like BigQuery, PostgreSQL, Redshift, Databricks, and Snowflake.
The tables below provide an overview of these types, categorized as numeric, string, boolean, date/time, etc., tailored to each system’s specifics, to help pinpoint compatible types and grasp their mappings across different databases. Note that data types not included in these tables are not currently supported by Datafold.
For cross-database diffing, it’s important to note that while the tables list the primary data types supported by Datafold, you can still diff data with types outside these lists. Datafold samples values and refines them further, often implicitly supporting a broad range of types. In scenarios where a type is not explicitly supported, it may be interpreted as Text(). While this is usually sufficient, be aware that it can lead to false positives in some cases.
Supported types for in-database diffing
BigQuery
| Category | Type |
|---|---|
| Numeric | INTEGER, INT64, FLOAT, FLOAT64, NUMERIC, BIGNUMERIC, BIGDECIMAL |
| Boolean | BOOLEAN, BOOL |
| String | STRING |
| Date/Time | DATE, DATETIME, TIMESTAMP, TIME |
| Additional | JSON |
PostgreSQL/Redshift
| Category | Type |
|---|---|
| Numeric | BIGINT, INT8, INTEGER, INT4, SMALLINT, INT2, REAL, FLOAT4, FLOAT8, NUMERIC, DOUBLE PRECISION |
| String | CHARACTER, CHAR, BCHAR, CHARACTER VARYING, VARCHAR, NAME, TEXT, CHARACTER VARYING |
| Boolean | BOOLEAN, BOOL |
| Date/Time | DATE, TIMESTAMPTZ, TIMESTAMP, TIME, TIMESTAMP WITH/OUT TIME ZONE, TIME WITH/OUT TIME ZONE |
| UUID | UUID |
Databricks
| Category | Type |
|---|---|
| Numeric | BIGINT, TINYINT, SMALLINT, INT, DOUBLE, DECIMAL, FLOAT, REAL |
| String | CHAR, STRING, VARCHAR |
| Boolean | BOOLEAN |
| Date/Time | TIMESTAMP, DATE |
Snowflake
| Category | Type |
|---|---|
| Numeric | NUMBER, FLOAT, REAL |
| String | TEXT, VARCHAR |
| Boolean | BOOLEAN |
| Date/Time | DATE, TIMESTAMP_LTZ, TIMESTAMP_NTZ, TIMESTAMP_TZ, TIME |
| Additional | BINARY, VARIANT, ARRAY, OBJECT |
Supported types for cross-database diffing
Note for cross-database diffing only: while the tables list the primary data types supported by Datafold, you can still diff data with types outside these lists. Datafold samples values and refines them further, often implicitly supporting a broad range of types. In scenarios where a type is not explicitly supported, it may be interpreted as Text(). While this is usually sufficient, be aware that it can lead to false positives in some cases.
BigQuery
| Category | Type |
|---|---|
| Dates | TIMESTAMP, DATETIME |
| Numbers | INT64, INT32, NUMERIC, BIGNUMERIC, FLOAT64, FLOAT32 |
| Text | STRING |
| Boolean | BOOL |
| JSON | JSON |
Databricks
| Category | Type |
|---|---|
| Numbers | INT, SMALLINT, TINYINT, BIGINT, FLOAT, DOUBLE, DECIMAL |
| Timestamps | TIMESTAMP |
| Text | STRING |
| Boolean | BOOLEAN |
MS SQL
| Category | Type |
|---|---|
| Timestamps | DATETIMEOFFSET, DATETIME, DATETIME2, SMALLDATETIME, DATE |
| Numbers | FLOAT, REAL, DECIMAL, MONEY, SMALLMONEY |
| Integers | INT, BIGINT, TINYINT, SMALLINT |
| Text | VARCHAR, CHAR, TEXT, NTEXT, NVARCHAR, NCHAR, BINARY, VARBINARY, XML |
| UUID | UNIQUEIDENTIFIER |
| Boolean | BIT |
| JSON | JSON |
MYSQL
| Category | Type |
|---|---|
| Dates | DATETIME, TIMESTAMP, DATE |
| Numbers | DOUBLE, FLOAT, DECIMAL, INT, BIGINT, MEDIUMINT, SMALLINT, TINYINT |
| Text | VARCHAR, CHAR, VARBINARY, BINARY, TEXT, MEDIUMTEXT, LONGTEXT, TINYTEXT |
| Boolean | BOOLEAN |
Oracle
| Category | Type |
|---|---|
| Numbers | NUMBER, FLOAT |
| Text | CHAR, NCHAR, NVARCHAR2, VARCHAR2 |
| Timestamps | DATE |
PostgreSQL and Redshift
| Category | Type |
|---|---|
| Timestamps | TIMESTAMPTZ, TIMESTAMP, TIMESTAMP WITH/OUT TIME ZONE, DATE |
| Numbers | DOUBLE PRECISION, REAL, DECIMAL, SMALLINT, INTEGER, NUMERIC, BIGINT |
| Text | CHARACTER, CHARACTER VARYING, VARCHAR, TEXT, JSON, JSONB |
| Boolean | BOOLEAN |
| UUID | UUID |
Snowflake
| Category | Type |
|---|---|
| Timestamps | TIMESTAMP_NTZ, TIMESTAMP_LTZ, TIMESTAMP_TZ, DATE |
| Numbers | NUMBER, FLOAT |
| Text | TEXT |
| Boolean | BOOLEAN |
Data Types
Datafold facilitates data diffing by supporting a wide range of basic data types across major database systems like BigQuery, PostgreSQL, Redshift, Databricks, and Snowflake.
The tables below provide an overview of these types, categorized as numeric, string, boolean, date/time, etc., tailored to each system’s specifics, to help pinpoint compatible types and grasp their mappings across different databases. Note that data types not included in these tables are not currently supported by Datafold.
For cross-database diffing, it’s important to note that while the tables list the primary data types supported by Datafold, you can still diff data with types outside these lists. Datafold samples values and refines them further, often implicitly supporting a broad range of types. In scenarios where a type is not explicitly supported, it may be interpreted as Text(). While this is usually sufficient, be aware that it can lead to false positives in some cases.
Supported types for in-database diffing
BigQuery
| Category | Type |
|---|---|
| Numeric | INTEGER, INT64, FLOAT, FLOAT64, NUMERIC, BIGNUMERIC, BIGDECIMAL |
| Boolean | BOOLEAN, BOOL |
| String | STRING |
| Date/Time | DATE, DATETIME, TIMESTAMP, TIME |
| Additional | JSON |
PostgreSQL/Redshift
| Category | Type |
|---|---|
| Numeric | BIGINT, INT8, INTEGER, INT4, SMALLINT, INT2, REAL, FLOAT4, FLOAT8, NUMERIC, DOUBLE PRECISION |
| String | CHARACTER, CHAR, BCHAR, CHARACTER VARYING, VARCHAR, NAME, TEXT, CHARACTER VARYING |
| Boolean | BOOLEAN, BOOL |
| Date/Time | DATE, TIMESTAMPTZ, TIMESTAMP, TIME, TIMESTAMP WITH/OUT TIME ZONE, TIME WITH/OUT TIME ZONE |
| UUID | UUID |
Databricks
| Category | Type |
|---|---|
| Numeric | BIGINT, TINYINT, SMALLINT, INT, DOUBLE, DECIMAL, FLOAT, REAL |
| String | CHAR, STRING, VARCHAR |
| Boolean | BOOLEAN |
| Date/Time | TIMESTAMP, DATE |
Snowflake
| Category | Type |
|---|---|
| Numeric | NUMBER, FLOAT, REAL |
| String | TEXT, VARCHAR |
| Boolean | BOOLEAN |
| Date/Time | DATE, TIMESTAMP_LTZ, TIMESTAMP_NTZ, TIMESTAMP_TZ, TIME |
| Additional | BINARY, VARIANT, ARRAY, OBJECT |
Supported types for cross-database diffing
Note for cross-database diffing only: while the tables list the primary data types supported by Datafold, you can still diff data with types outside these lists. Datafold samples values and refines them further, often implicitly supporting a broad range of types. In scenarios where a type is not explicitly supported, it may be interpreted as Text(). While this is usually sufficient, be aware that it can lead to false positives in some cases.
BigQuery
| Category | Type |
|---|---|
| Dates | TIMESTAMP, DATETIME |
| Numbers | INT64, INT32, NUMERIC, BIGNUMERIC, FLOAT64, FLOAT32 |
| Text | STRING |
| Boolean | BOOL |
| JSON | JSON |
Databricks
| Category | Type |
|---|---|
| Numbers | INT, SMALLINT, TINYINT, BIGINT, FLOAT, DOUBLE, DECIMAL |
| Timestamps | TIMESTAMP |
| Text | STRING |
| Boolean | BOOLEAN |
MS SQL
| Category | Type |
|---|---|
| Timestamps | DATETIMEOFFSET, DATETIME, DATETIME2, SMALLDATETIME, DATE |
| Numbers | FLOAT, REAL, DECIMAL, MONEY, SMALLMONEY |
| Integers | INT, BIGINT, TINYINT, SMALLINT |
| Text | VARCHAR, CHAR, TEXT, NTEXT, NVARCHAR, NCHAR, BINARY, VARBINARY, XML |
| UUID | UNIQUEIDENTIFIER |
| Boolean | BIT |
| JSON | JSON |
MYSQL
| Category | Type |
|---|---|
| Dates | DATETIME, TIMESTAMP, DATE |
| Numbers | DOUBLE, FLOAT, DECIMAL, INT, BIGINT, MEDIUMINT, SMALLINT, TINYINT |
| Text | VARCHAR, CHAR, VARBINARY, BINARY, TEXT, MEDIUMTEXT, LONGTEXT, TINYTEXT |
| Boolean | BOOLEAN |
Oracle
| Category | Type |
|---|---|
| Numbers | NUMBER, FLOAT |
| Text | CHAR, NCHAR, NVARCHAR2, VARCHAR2 |
| Timestamps | DATE |
PostgreSQL and Redshift
| Category | Type |
|---|---|
| Timestamps | TIMESTAMPTZ, TIMESTAMP, TIMESTAMP WITH/OUT TIME ZONE, DATE |
| Numbers | DOUBLE PRECISION, REAL, DECIMAL, SMALLINT, INTEGER, NUMERIC, BIGINT |
| Text | CHARACTER, CHARACTER VARYING, VARCHAR, TEXT, JSON, JSONB |
| Boolean | BOOLEAN |
| UUID | UUID |
Snowflake
| Category | Type |
|---|---|
| Timestamps | TIMESTAMP_NTZ, TIMESTAMP_LTZ, TIMESTAMP_TZ, DATE |
| Numbers | NUMBER, FLOAT |
| Text | TEXT |
| Boolean | BOOLEAN |