Skip to content

Snowflake Data Type Conversions

This guide covers the Snowflake data types that are supported by RelationalAI and shows how they are represented.

Introduction

A RAI data stream synchronizes your data from a Snowflake database to a RAI database. It originates from a Snowflake object, which can be a table (opens in a new tab) or a view (opens in a new tab), and targets a base relation in a RAI database.

Most data types in Snowflake (opens in a new tab) directly map to their corresponding data types in RelationalAI. For instance, VARCHAR in Snowflake is mapped to String in RelationalAI. A summary of the supported Snowflake data types is given below. For details on Snowflake data types that are not supported in RelationalAI, see Unsupported Snowflake Data Types.

See the example below to learn more about the data type conversions.

Supported Snowflake Data Types

The following table shows which Snowflake data types are supported in RelationalAI:

CategorySnowflake Data TypeRelationalAI Data TypeDetails
StringVarcharString-
BinaryBinaryStringSnowflake Binary type does not match to any RelationalAI data type. Therefore, the system converts it to String.
NumericNumberInt or FixedDecimalSee Numeric Data Types for details on how Snowflake precision matches RelationalAI bit sizes.
NumericFloatFloatBy default, Float bit width is 64 bits.
LogicalBooleanBooleanRepresented as boolean_true or boolean_false.
DateDateDate-
TimeTimeStringSnowflake Time type does not match to any RelationalAI data type. Therefore, the system converts it to String. See Time Periods to learn more about RelationalAI time-related data types.
TimeTimestamp_ltzDateTime-
TimeTimestamp_ntzDateTime-
TimeTimestamp_tzDateTime-

Unsupported Snowflake Data Types

🔎

Semi-structured data types (opens in a new tab) and geospatial data types (opens in a new tab) are not currently supported and cannot be synchronized with RelationalAI.

These Snowflake data types are not currently supported in RelationalAI:

CategorySnowflake Data Type
Semi-structuredVariant
Semi-structuredObject
Semi-structuredArray
GeospatialGeography
GeospatialGeometry

Attempting to synchronize a Snowflake object that contains any of these unsupported types results in an error like the following:

Error example from Snowflake

Numeric Data Types

The Snowflake data type Number encompasses all numeric fixed-point data types. It provides optional precision and scale parameters. See Snowflake’s Data Types for Fixed-point Numbers (opens in a new tab) for more details.

The following table summarizes how the Snowflake numeric data types for fixed-point numbers are represented in RelationalAI:

Snowflake Data TypeRelationalAI Data TypeDetails
Number, Int, Integer, Bigint, Smallint, Tinyint, and ByteintIntBy default, Int bit width is 64 bits.
Number(precision, scale), Decimal, Dec, and NumericFixedDecimalUp to 38 decimals using 128 bit width.

Depending on the precision value of Number, these are its conversions to FixedDecimal:

Number PrecisionFixedDecimal Bit SizeFixedDecimal Decimals
0 - 280 - 2
3 - 4160 - 4
5 - 9320 - 9
10 - 18640 - 18
19 - 381280 - 38
🔎

Only the precision parameter of Number is used for its conversion. The scale parameter is not involved.

Here’s an example of a Snowflake table containing data types for fixed-point numbers:

CREATE OR REPLACE TABLE numeric_types(
    num1 NUMBER,
    num2 NUMBER(10,1),
    num3 DECIMAL(20,2),
    num4 NUMERIC(30,3),
    num5 INT,
    num6 INTEGER
)
AS SELECT * FROM VALUES(
    12345, 1.1, 2.22, 3.333, 4, 5
);

You can create a RAI data stream to synchronize this Snowflake table numeric_types with a base relation rai_numeric_types within your working RAI database rai_db:

CALL RAI.create_data_stream('sf_db.sf_schema.numeric_types', 'rai_db', 'rai_numeric_types');

After the data stream is fully synchronized, querying rai_numeric_types in RelationalAI gives this output:

// read query
 
def output = ::std::display::table[rai_numeric_types]
 

Note that the row ID is a SHA1 data type.

Example

Consider the following Snowflake table containing all the supported data types:

CREATE OR REPLACE TABLE data_types(
    v VARCHAR,
    bin BINARY,
    n NUMBER,
    f FLOAT,
    b BOOLEAN,
    d DATE,
    t TIME,
    ts1 TIMESTAMP_LTZ,
    ts2 TIMESTAMP_NTZ,
    ts3 TIMESTAMP_TZ
)

You can insert some data:

INSERT INTO data_types VALUES(
    'text', 
    TO_BINARY('bin'::STRING, 'UTF8'), 
    10, 
    10.10, 
    true, 
    '2023-10-12', 
    '10:00:00', 
    '2023-10-12 10:00:00', 
    '2023-10-12 10:00:00', 
    '2023-10-12 10:00:00'
);

Now, you can create a RAI data stream to synchronize this Snowflake table data_types with a base relation rai_data_types within your working RAI database rai_db:

CALL RAI.create_data_stream('sf_db.sf_schema.data_types', 'rai_db', 'rai_data_types');

After the RAI data stream is fully synchronized, querying rai_data_types in RelationalAI gives this output:

// read query
 
def output = ::std::display::table[rai_data_types]
 

Note that the row ID is a SHA1 data type.

See also

For more information on managing data using the RelationalAI Integration Services, see the RAI Data Streams guide.

Was this doc helpful?