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:

Unsupported Snowflake Data Types

These Snowflake data types are not currently supported in RelationalAI:

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

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:

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

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 = 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 = 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.