Data types
The tables below list the data types Prophecy supports depending on the SQL warehouse you use for processing.- Databricks
- BigQuery
- Snowflake
The data types you see in Prophecy correspond to data types in Databricks SQL.
| Data type | Description |
|---|---|
| Array | Represents values comprising a sequence of elements. |
| Bigint | Represents 8-byte signed integer numbers. |
| Binary | Represents byte sequence values. Only partially supported in the Prophecy UI. |
| Boolean | Represents true and false values. |
| Date | Represents values comprising year, month, and day, without a timezone. |
| Decimal | Represents numbers with maximum precision and fixed scale. |
| Double | Represents 8-byte double-precision floating point numbers. |
| Float | Represents 4-byte single-precision floating point numbers. |
| Integer | Represents 4-byte signed integer numbers. |
| Smallint | Represents 2-byte signed integer numbers. |
| String | Represents character string values. |
| Struct | Represents values with the structure described by a sequence of fields. |
| Timestamp | Represents values comprising year, month, day, hour, minute, and second, with a timezone. |
| Tinyint | Represents 1-byte signed integer numbers. |
| Variant | Represents semi-structured data. Only partially supported in the Prophecy UI. |
| Void | Represents the untyped NULL. Only partially supported in the Prophecy UI. |
These data types you see in Prophecy correspond to the data types in BigQuery.
| Data type | Description |
|---|---|
| Record | Represents a column with nested data. SQL type name: STRUCT |
| Array | Represents a list of non-array values of the same data type. SQL type name: ARRAY |
| BigNumeric | Represents a decimal value with precision of 76.76 digits. SQL type name: BIGNUMERIC SQL aliases: BIGDECIMAL |
| Numeric | Represents a decimal value with precision of 38 digits. SQL type name: NUMERIC SQL aliases: DECIMAL |
| Datetime | Represents a Gregorian date and a time, independent of time zone. SQL type name: DATETIME |
| Time | Represents a time of day, independent of a specific date and time zone. SQL type name: TIME |
| Date | Represents a Gregorian calendar date, independent of time zone. SQL type name: DATE |
| Timestamp | Represents an absolute point in time, with microsecond precision and no timezone context. SQL type name: TIMESTAMP |
| Boolean | Represents true and false values. SQL type name: BOOL SQL aliases: BOOLEAN |
| Float | Represents an approximate double-precision numeric value. SQL type name: FLOAT64 |
| Integer | Represents a 64-bit integer. SQL type name: INT64 SQL aliases: INT, SMALLINT, INTEGER, BIGINT, TINYINT, BYTEINT |
| Bytes | Represents variable-length binary data. SQL type name: BYTES |
| String | Represents variable-length character strings. SQL type name: STRING |
- JSON: Supported. Prophecy infers the JSON schema and displays nested fields in the UI.
- Geography: Serialized and stored as strings.
- Interval: Converted to a month-day-nano interval type for display and processing.
The data types you see in Prophecy correspond to the data types in Snowflake.
| Data type | Description |
|---|---|
| Array | Represents semi-structured arrays of values. |
| Binary | Represents variable-length binary data. |
| Boolean | Represents true and false values. |
| Date | Represents calendar dates without a time component. |
| Decimal | Represents fixed-point numbers with a specified precision and scale. |
| Float | Represents approximate numeric values. |
| Integer | Represents whole numbers. |
| Object | Represents semi-structured key-value data. |
| String | Represents variable-length character strings. |
| Time | Represents a time of day without a date. |
| Timestamp | Represents a date and time value. |
| Variant | Represents semi-structured data such as JSON, Avro, ORC, Parquet, or XML. |
Prophecy maps Snowflake SQL types to the simplified data types shown above.For example:
- Integer includes Snowflake integer types such as
NUMBER,DECIMAL,NUMERIC,INT,INTEGER,BIGINT,SMALLINT,TINYINT, andBYTEINTwhen they represent whole numbers. - Float includes approximate numeric types such as
FLOAT,FLOAT4,FLOAT8,DOUBLE,DOUBLE PRECISION, andREAL. - String includes character and text types such as
VARCHAR,CHAR,CHARACTER,STRING, andTEXT. - Timestamp includes
TIMESTAMP_NTZ,TIMESTAMP_LTZ, andTIMESTAMP_TZ.