SnowConvert AI - Redshift - Data types

Current Data types conversion for Redshift in SnowConvert AI.

Snowflake supports most basic SQL data types (with some restrictions) for use in columns, local variables, expressions, parameters, and any other appropriate/suitable locations.

Numeric Data Types

RedshiftSnowflakeNotes
INTINTSnowflake’s INT is an alias for NUMBER.
INT2SMALLINTSnowflake’s INT2 is an alias for NUMBER.
INT4INTEGERSnowflake’s INT4 is an alias for NUMBER.
INT8INTEGERSnowflake’s INT8 is an alias for NUMBER.
INTEGERINTEGERSnowflake’s INTEGER is an alias for NUMBER.
BIGINTBIGINTSnowflake’s BIGINT is an alias for NUMBER.
DECIMALDECIMALSnowflake’s DECIMAL is an alias for NUMBER.
DOUBLE PRECISIONDOUBLE PRECISIONSnowflake’s DOUBLE PRECISION is an alias for FLOAT.
NUMERIC​NUMERICSnowflake’s NUMERIC is an alias for NUMBER.
SMALLINTSMALLINTSnowflake’s SMALLINT is an alias for NUMBER.
FLOATFLOATSnowflake uses double-precision (64 bit) IEEE 754 floating-point numbers.
FLOAT4FLOAT4Snowflake’s FLOAT4 is an alias for FLOAT.
FLOAT8FLOAT8Snowflake’s FLOAT8 is an alias for FLOAT.
REALREAL​Snowflake’s REAL is an alias for FLOAT.

Character Types

RedshiftSnowflakeNotes
VARCHARVARCHARVARCHAR holds Unicode UTF-8 characters. If no length is specified, the default is the maximum allowed length (16,777,216).
CHARCHARSnowflake’s CHAR is an alias for VARCHAR.
CHARACTERCHARACTERSnowflake’s CHARACTER is an alias for VARCHAR.
NCHARNCHARSnowflake’s NCHAR is an alias for VARCHAR.
BPCHARVARCHARBPCHAR data type is not supported in Snowflake. VARCHAR is used instead. For more information please refer to SSC-FDM-PG0002.
NVARCHARNVARCHARSnowflake’s NVARCHAR is an alias for VARCHAR.
CHARACTER VARYINGCHARACTER VARYINGSnowflake’s CHARACTER VARYING is an alias for VARCHAR.
NATIONAL CHARACTERNCHARSnowflake’s NCHAR is an alias for VARCHAR.
NATIONAL CHARACTER VARYINGNCHAR VARYINGSnowflake’s NCHAR VARYING is an alias for VARCHAR.
TEXTTEXTSnowflake’s TEXT is an alias for VARCHAR.
NAME (Special character type)VARCHARVARCHAR holds Unicode UTF-8 characters. If no length is specified, the default is the maximum allowed length (16,777,216).

Note

When the MAX precision argument is present in the Redshift data types, they are transformed to the default max precision supported by Snowflake.

Boolean Types

RedshiftSnowflakeNotes
BOOLBOOLEAN
BOOLEANBOOLEAN

Binary Data Types

RedshiftSnowflakeNotes
VARBYTEVARBINARYVARBINARY is synonymous with BINARY.
VARBINARYVARBINARYVARBINARY is synonymous with BINARY.
BINARYBINARYThe maximum length is 8 MB (8,388,608 bytes)
BINARY VARYINGBINARY VARYINGBINARY VARYING is synonymous with BINARY.

Warning

The maximum length for binary types in Redshift is 16 MB (16,777,216 bytes), however in Snowflake it is 8 MB (8,388,608 bytes). Please consider this reduction in the maximum length.

Date & Time Data Types

RedshiftSnowflakeNotes
DATEDATEDATE accepts dates in the most common forms (such as YYYY-MM-DD and DD-MON-YYYY)
TIMETIMEStoring times in the form of HH:MI:SS. Time precision can range from 0 (seconds) to 9 (nanoseconds). The default precision is 9.
TIMETZTIMETime zone not supported for time data type. For more information please refer to SSC-FDM-0005.
TIME WITH TIME ZONETIMETime zone not supported for time data type. For more information please refer to SSC-FDM-0005.
TIME WITHOUT TIME ZONETIMESnowflake supports a single TIME data type for storing times in the form of HH:MI:SS.
TIMESTAMPTIMESTAMPTimestamp precision can range from 0 (seconds) to 9 (nanoseconds).
TIMESTAMPTZTIMESTAMP_TZTIMESTAMPTZ internally stores UTC time together with an associated _time zone offset.
TIMESTAMP WITH TIME ZONETIMESTAMP_TZTIMESTAMPTZ internally stores UTC time together with an associated _time zone offset.
TIMESTAMP WITHOUT TIME ZONETIMESTAMP_NTZTIMESTAMP_NTZ internally stores “wallclock” time with a specified precision.
INTERVAL YEAR TO MONTHVARCHARThe interval data type is not supported by Snowflake. Transformed to VARCHAR. With the --UseIntervalDatatype preview flag, preserved as native INTERVAL YEAR TO MONTH. See Interval Data Types.
INTERVAL DAY TO SECONDVARCHARThe interval data type is not supported by Snowflake. Transformed to VARCHAR. With the --UseIntervalDatatype preview flag, preserved as native INTERVAL DAY TO SECOND. See Interval Data Types.

Other data types

RedshiftSnowflakeNotes
GEOMETRYGEOMETRYThe coordinates are represented as pairs of real numbers (x, y). Currently, only 2D coordinates are supported.
GEOGRAPHYGEOGRAPHYThe GEOGRAPHY data type follows the WGS 84 standard.
HLLSKETCHN/AData type not supported in Snowflake. For more information please refer to SSC-EWI-RS0004.
SUPERVARIANTCan contain a value of any other data type, including OBJECT and ARRAY values.
  1. SSC-FDM-PG0002: Bpchar converted to varchar.
  2. SSC-FDM-0005: TIME ZONE not supported for time data type.
  3. SSC-EWI-0036: Data type converted to another data type.
  4. SSC-EWI-RS0004: HLLSKETCH data type not supported in Snowflake.

INTERVAL DAY TO SECOND Data Type

Description

INTERVAL DAY TO SECOND specify an interval literal to define a duration in days, hours, minutes, and seconds. (RedShift SQL Language Reference Interval data type)

By default, there is no equivalent for this data type in Snowflake and it is transformed to VARCHAR.

Note

Preview Feature: When the --UseIntervalDatatype preview flag is enabled, Redshift INTERVAL columns are preserved as native Snowflake INTERVAL types. See the Interval Data Types translation reference for complete transformation details.

Grammar Syntax 


 INTERVAL day_to_second_qualifier [ (fractional_precision) ]

day_to_second_qualifier:
{ DAY | HOUR | MINUTE | SECOND | DAY TO HOUR | DAY TO MINUTE | DAY TO SECOND |
HOUR TO MINUTE | HOUR TO SECOND | MINUTE TO SECOND }

Warning

The use of the Interval data type is planned for implementation in future updates.

Sample Source Patterns

Interval Day to Second in Create Table

Input
Redshift

 CREATE TABLE interval_day_to_second_table
(
	interval_day_col1 INTERVAL DAY TO HOUR,
	interval_day_col2 INTERVAL DAY TO SECOND(4)
);

INSERT INTO interval_day_to_second_table(interval_day_col1) VALUES ( INTERVAL '1 2' DAY TO HOUR );
INSERT INTO interval_day_to_second_table(interval_day_col2) VALUES ( INTERVAL '1 2:3:4.56' DAY TO SECOND(4));
Output
Snowflake

 CREATE TABLE interval_day_to_second_table
(
	interval_day_col1 VARCHAR !!!RESOLVE EWI!!! /*** SSC-EWI-0036 - INTERVAL DAY TO HOUR DATA TYPE CONVERTED TO VARCHAR ***/!!!,
	interval_day_col2 VARCHAR !!!RESOLVE EWI!!! /*** SSC-EWI-0036 - INTERVAL DAY TO SECOND(4) DATA TYPE CONVERTED TO VARCHAR ***/!!!
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"redshift"}}'
;

INSERT INTO interval_day_to_second_table(interval_day_col1) VALUES ('1days, 2hours');

INSERT INTO interval_day_to_second_table(interval_day_col2) VALUES ('1days, 2hours, 3mins, 4secs, 56ms');

The Interval value is transformed to a supported Snowflake format and then inserted as text inside the column. Since Snowflake does not support Interval as a data type, it is only supported in arithmetic operations. To use the value, it needs to be extracted and used as an Interval constant (if possible).

Original Redshift value: INTERVAL '1 2:3:4.567' DAY TO SECOND

Value stored in Snowflake column: '1days, 2hours, 3mins, 4secs, 56ms'

Value as Snowflake Interval constant: INTERVAL '1days, 2hours, 3mins, 4secs, 56ms'

Retrieving data from an Interval Day to Second column

Input
Redshift

 SELECT * FROM interval_day_to_second_table;
Result
interval_day_col1interval_day_col2
1 days 2 hours 0 mins 0.0 secsNULL
NULL1 days 2 hours 3 mins 4.56 secs
Output
Snowflake

 SELECT * FROM
interval_day_to_second_table;
Result
interval_day_col1interval_day_col2
1d, 2hNULL
NULL1d, 2h, 3m, 4s, 56ms

Known Issues

1. Only arithmetic operations are supported

Snowflake Intervals have several limitations. Only arithmetic operations between DATE or TIMESTAMP and Interval Constants are supported, every other scenario is not supported.

  1. SSC-EWI-0036: Data type converted to another data type.

INTERVAL YEAR TO MONTH Data Type

Description

INTERVAL YEAR TO MONTH specify an interval data type to store a duration of time in years and months. (RedShift SQL Language Reference Interval data type)

There is no equivalent for this data type in Snowflake, it is currently transformed to VARCHAR.

Grammar Syntax 


 INTERVAL {YEAR | MONTH | YEAR TO MONTH}

Warning

The use of the Interval data type is planned for implementation in future updates.

Sample Source Patterns

Interval Year To Month in Create Table

Input:
Redshift

 CREATE TABLE interval_year_to_month_table
(
	interval_year_col1 INTERVAL YEAR,
	interval_year_col2 INTERVAL MONTH,
 	interval_year_col3 INTERVAL YEAR TO MONTH
);

INSERT INTO interval_year_to_month_table(interval_year_col1) VALUES ( INTERVAL '12' YEAR);
INSERT INTO interval_year_to_month_table(interval_year_col2) VALUES ( INTERVAL '5' MONTH);
INSERT INTO interval_year_to_month_table(interval_year_col3) VALUES ( INTERVAL '1000-11' YEAR TO MONTH );
Output
Snowflake

 CREATE TABLE interval_year_to_month_table
(
	interval_year_col1 VARCHAR !!!RESOLVE EWI!!! /*** SSC-EWI-0036 - INTERVAL YEAR DATA TYPE CONVERTED TO VARCHAR ***/!!!,
	interval_year_col2 VARCHAR !!!RESOLVE EWI!!! /*** SSC-EWI-0036 - INTERVAL MONTH DATA TYPE CONVERTED TO VARCHAR ***/!!!,
	interval_year_col3 VARCHAR !!!RESOLVE EWI!!! /*** SSC-EWI-0036 - INTERVAL YEAR TO MONTH DATA TYPE CONVERTED TO VARCHAR ***/!!!
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"redshift"}}'
;

INSERT INTO interval_year_to_month_table(interval_year_col1) VALUES ('12year, 0mons');

INSERT INTO interval_year_to_month_table(interval_year_col2) VALUES ('0year, 5mons');

INSERT INTO interval_year_to_month_table(interval_year_col3) VALUES ('1000year, 11mons');

The Interval value is transformed to a supported Snowflake format and then inserted as text inside the column. Since Snowflake does not support Interval as a data type, it is only supported in arithmetic operations. To use the value, it needs to be extracted and used as an Interval constant (if possible).

Original Redshift value: INTERVAL '1-2' YEAR TO MONTH

Value stored in Snowflake column: '1y, 2m'

Value as Snowflake Interval constant: INTERVAL '1y, 2m'

Retrieving data from an Interval Year To Month column

Input
Redshift

 SELECT * FROM interval_year_to_month_table;
Result
interval_year_col1interval_year_col2interval_year_col2
12 years 0 monsNULLNULL
NULL0 years 5 monsNULL
NULLNULL1000 years 11 mons
Output
Snowflake

 SELECT * FROM
interval_year_to_month_table;
Result
interval_year_col1interval_year_col2interval_year_col2
12 y 0 mmNULLNULL
NULL0 y 5 mmNULL
NULLNULL1000 y 11 mons

Known Issues

1. Only arithmetic operations are supported

Snowflake Intervals have several limitations. Only arithmetic operations between DATE or TIMESTAMP and Interval Constants are supported, every other scenario is not supported.

Numeric Format Models

Description

These are the different Numeric Formats supported by Redshift and its equivalent in Snowflake.

RedshiftSnowflakeComments
00
99
. (period), D. (period), D
, (comma), (comma)
CCCurrently there is no equivalent for Century Code in Snowflake.
FMFM
PRCurrently there is no equivalent for this format in Snowflake.
SSExplicit numeric sign.
L$Currency symbol placeholder.
GG
MIMIMinus sign (for negative numbers)
PLSCurrently there is no equivalent for plus sign in Snowflake. So it is translated to the explicit numeric sign.
SGSExplicit numeric Sign in the specified position.
RNCurrently there is no equivalent for Roman Numerals in Snowflake.
THCurrently there is no equivalent for Ordinal suffix in Snowflake

Sample Source Patterns

Uses in To_Number function

Input:
Redshift

 select to_number('09423', '999999999') as multiple_nines
    , to_number('09423', '00000') as exact_zeros
    , to_number('123.456', '999D999') as decimals
    , to_number('123,031.30', 'FM999,999D999') as fill_mode
    , to_number('$ 12,454.88', '$999,999.99') as currency
;
Results
multiple_ninesexact_zerosdecimalsfill_modecurrency
94239423123.456123031.301254.88
Output
Snowflake

 select to_number('09423', '999999999') !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR ''999999999'' NODE ***/!!! as multiple_nines
    , to_number('09423', '00000') !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR ''00000'' NODE ***/!!! as exact_zeros
    , to_number('123.456', '999D999') !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR ''999D999'' NODE ***/!!! as decimals
    , to_number('123,031.30', 'FM999,999D999') !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR ''FM999,999D999'' NODE ***/!!! as fill_mode
    , to_number('$ 12,454.88', '$999,999.99') !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR ''$999,999.99'' NODE ***/!!! as currency
;
Results
multiple_ninesexact_zerosdecimalsfill_modecurrency
94239423123.456123031.30012454.88
Input:
Redshift

 select to_number('$ 12,454.88', 'FML99G999D99') as currency_L
    , to_number('123-', '999S') as signed_number_end
    , to_number('+12454.88', 'PL99G999D99') as plus_sign
    , to_number('-12,454.88', 'MI99G999D99') as minus_sign
    , to_number('-12,454.88', 'SG99G999D99') as signed_number
;
Results
currency_Lsigned_number_endplus_signminus_signsigned_number
12454.8-1231254.88-12454.88-12454.88
Output:
Snowflake

 select to_number('$ 12,454.88', 'FML99G999D99') !!!RESOLVE EWI!!! /*** SSC-EWI-0006 - 'FML99G999D99' FORMAT MAY FAIL OR MAY HAVE A DIFFERENT BEHAVIOR IN SNOWFLAKE. ***/!!! as currency_L
    , to_number('123-', '999S') !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR ''999S'' NODE ***/!!! as signed_number_end
    , to_number('+12454.88', 'PL99G999D99') !!!RESOLVE EWI!!! /*** SSC-EWI-0006 - 'PL99G999D99' FORMAT MAY FAIL OR MAY HAVE A DIFFERENT BEHAVIOR IN SNOWFLAKE. ***/!!! as plus_sign
    , to_number('-12,454.88', 'MI99G999D99') !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR ''MI99G999D99'' NODE ***/!!! as minus_sign
    , to_number('-12,454.88', 'SG99G999D99') !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR ''SG99G999D99'' NODE ***/!!! as signed_number
;
Results
currency_Lsigned_number_endplus_signminus_signsigned_number
12454.8-1231254.88-12454.88-12454.88

Uses in To_Char function

Input:
Redshift

 select to_char(-123, '999S') as signed_number
    , to_char(12454.88, 'FM99G999D99') as decimal_number
    , to_char(-12454.88, '99G999D99') as negative
    , to_char(-12454.88, 'MI99G999D99') as minus_sign
    , to_char(+12454.88, 'PL99G999D99') as plus_sign
    , to_char(09423, '999999999') as multiple_nines
    , to_char(09423, '00000') as exact_zeros
;
Results
signed_numberdecimal_numbernegativeminus_signplus_signmultiple_ninesmultiple_ninesexact_zerosexact_zeros
’123-’’12,454.88’‘-12,454.88’’12454.88’‘-12,454.88’’09423’’09423’
Output:
Snowflake

 select
    TO_CHAR(-123, '999S') as signed_number,
    TO_CHAR(12454.88, 'FM99G999D99') as decimal_number,
    TO_CHAR(-12454.88, '99G999D99') as negative,
    TO_CHAR(-12454.88, 'MI99G999D99') as minus_sign,
    TO_CHAR(+12454.88, 'S99G999D99') as plus_sign,
    TO_CHAR(09423, '999999999') as multiple_nines,
    TO_CHAR(09423, '00000') as exact_zeros
;
Results
signed_numberdecimal_numbernegativeminus_signplus_signmultiple_ninesmultiple_ninesexact_zerosexact_zeros
’123-’’12,454.88’‘-12,454.88’’12454.88’‘-12,454.88’’09423’’09423’

Unsupported format

The following format is not supported, for which it will be marked with an EWI.

Input:

 SELECT to_char(123031, 'th999,999')
Output:

 SELECT
TO_CHAR(123031, 'th999,999') !!!RESOLVE EWI!!! /*** SSC-EWI-0006 - th999,999 FORMAT MAY FAIL OR MAY HAVE A DIFFERENT BEHAVIOR IN SNOWFLAKE. ***/!!!

Known Issues

1. Using numeric signs inside the number not supported.

When any numeric sign format (MI, SG or PL) is used inside the number, instead of at the start, or at the end of the number is not supported in snowflake

Example


 select to_number('12,-454.88', '99GMI999D99')
  • SSC-EWI-0006: The current date/numeric format may have a different behavior in Snowflake.