SnowConvert AI - Teradata - Iceberg Tables Transformations¶

This section covers the transformation of tables into Snowflake-managed Iceberg tables, performed by SnowConvert AI when the conversion setting Table Translation is used.

Temporary tables¶

The temporary option is not supported in Iceberg tables, they will be preserved as temporary.

Teradata¶

CREATE VOLATILE TABLE myTable
(
  column1 NUMBER(15,0)
);
Copy

Snowflake¶

CREATE OR REPLACE TEMPORARY TABLE myTable
(
 column1 NUMBER(15,0)
)
;
Copy

Other tables¶

Other table types are going to be transformed into Iceberg tables.

Teradata¶

CREATE TABLE myTable
(
  column1 NUMBER(15,0)
);
Copy

Snowflake¶

CREATE OR REPLACE ICEBERG TABLE myTable (
  column1 NUMBER(15, 0)
)
CATALOG = 'SNOWFLAKE'
;
Copy

Data types¶

The following column data type conversions are applied to comply with the Iceberg tables type requirements and restrictions.

Note

Data types in the first column are the Snowflake data types that would normally be created if the table target is not Iceberg, while second column shows the data type generated for Iceberg tables.

Original target type

New target type

TIME(X)
TIMESTAMP(X)
DATETIME(X)
TIMESTAMP_LTZ(X)
TIMESTAMP_NTZ(X)
TIME
TIMESTAMP
DATETIME
TIMESTAMP_LTZ
TIMESTAMP_NTZ
where X != 6

TIME(6)
TIMESTAMP(6)
DATETIME(6)
TIMESTAMP_LTZ(6)
TIMESTAMP_NTZ(6)
TIME(6)
TIMESTAMP(6)
DATETIME(6)
TIMESTAMP_LTZ(6)
TIMESTAMP_NTZ(6)

VARCHAR(X)
STRING(X)
TEXT(X)
NVARCHAR(X)
NVARCHAR2(X)
CHAR VARYING(X)
NCHAR VARYING(X)

VARCHAR
STRING
TEXT
NVARCHAR
NVARCHAR2
CHAR VARYING
NCHAR VARYING

CHAR[(n)]
CHARACTER[(n)]
NCHAR[(n)]

VARCHAR
VARCHAR
VARCHAR

NUMBER
DECIMAL
DEC
NUMERIC
INT
INTEGER
BIGINT
SMALLINT
TINYINT
BYTEINT

NUMBER(38,0)
DECIMAL(38,0)
DEC(38,0)
NUMERIC(38,0)
NUMBER(38,0)
NUMBER(38,0)
NUMBER(38,0)
NUMBER(38,0)
NUMBER(38,0)
NUMBER(38,0)

FLOAT
FLOAT4
FLOAT8

DOUBLE
DOUBLE
DOUBLE

VARBINARY[(n)]

BINARY[(n)]

PARTITION BY¶

The following PARTITION BY cases are supported:

PARTITION BY name¶

Left as is.

Teradata¶

CREATE TABLE myTable
(
  customerName VARCHAR(30),
  areaCode INTEGER
)
PARTITION BY areaCode;
Copy

Snowflake¶

CREATE OR REPLACE ICEBERG TABLE myTable (
  customerName VARCHAR,
  areaCode NUMBER(38, 0)
)
PARTITION BY (areaCode)
CATALOG = 'SNOWFLAKE'
;
Copy

PARTITION BY CASE_N (equality over single column)¶

When the CASE_N function follows this pattern:

PARTITION BY CASE_N(
  column_name = value1,
  column_name = value2,
  ...
  column_name = valueN)
Copy

It will be transformed to a PARTITION BY column_name.

Teradata¶

CREATE TABLE myTable
(
  customerName VARCHAR(30),
  weekDay VARCHAR(20)
)
PARTITION BY CASE_N(
weekDay =  'Sunday',
weekDay =  'Monday',
weekDay =  'Tuesday',
weekDay =  'Wednesday',
weekDay =  'Thursday',
weekDay =  'Friday',
weekDay =  'Saturday',
 NO CASE OR UNKNOWN);
Copy

Snowflake¶

CREATE OR REPLACE ICEBERG TABLE myTable (
  customerName VARCHAR,
  weekDay VARCHAR
)
PARTITION BY (weekDay)
CATALOG = 'SNOWFLAKE'
;
Copy

PARTITION BY RANGE_N¶

PARTITION BY RANGE_N is transformed when it matches one of these patterns:

Numeric range¶

Pattern:

RANGE_N(columnName BETWEEN x AND y EACH z) -- x, y and z must be numeric constants.
Copy

This case will be changed with a BUCKET partition transform.

Teradata¶
CREATE TABLE myTable
(
  customerName VARCHAR(30),
  totalPurchases INTEGER
)
PARTITION BY RANGE_N(totalPurchases BETWEEN 5 AND 200 EACH 10);
Copy
Snowflake¶
CREATE OR REPLACE ICEBERG TABLE myTable (
  customerName VARCHAR,
  totalPurchases NUMBER(38, 0)
)
PARTITION BY (BUCKET(20, totalPurchases))
CATALOG = 'SNOWFLAKE'
;
Copy

Datetime range¶

Pattern:

RANGE_N(columnName BETWEEN date_constant AND date_constant EACH interval_constant) -- Interval qualifier must be YEAR, MONTH, DAY or HOUR
Copy

This case will be changed with the YEAR, MONTH, DAY or HOUR partition transforms.

Teradata¶
CREATE TABLE myTable
(
  customerName VARCHAR(30),
  purchaseDate DATE
)
PARTITION BY RANGE_N(purchaseDate BETWEEN DATE '2000-01-01' AND '2100-12-31' EACH INTERVAL '1' MONTH);
Copy
Snowflake¶
CREATE OR REPLACE ICEBERG TABLE myTable (
  customerName VARCHAR,
  purchaseDate DATE
)
PARTITION BY (MONTH(purchaseDate))
CATALOG = 'SNOWFLAKE'
;
Copy

Known Issues¶

1. Unsupported data types¶

Current Snowflake support for Iceberg tables does not allow data types like VARIANT or GEOGRAPHY to be used, tables with these types will be marked with an EWI.

2. Unsupported PARTITION BY cases¶

PARTITION BY cases different than the ones shown in this documentation will not be transformed, instead, the PARTITION BY clause will be commented out with a PRF.

Language: English