MSCEWI5003

PERIOD DEFINITION IS NOT SUPPORTED IN SNOWFLAKE.

This is a deprecated version of the SnowConvert documentation, please visit the official site HERE.

Severity

Medium

Description

DB2 temporal tables do not have a functional equivalent in Snowflake. When an application-period or system-period temporal table declaration is found in the CREATE TABLE columns, that column is commented out from the resulting script. The behavior of the SELECT statement will differ from Snowflake due that temporal tables are not part of the Snowflake solution and this causes the result to be different if the Select statement is migrated partially, see the example below for more information about this.

Select Query

SELECT 
  ID,
  Start,
  END
FROM 
  timetable 
FOR system_time as of '2022-05-09-16.20.17.0';

Result

ID	START	END
1001	19:45.3	22:39.5
1002	19:45.5	22:39.6
1003	19:45.6	22:39.8
1004	19:45.7	00:00.0
1005	19:45.8	00:00.0
1006	19:46.0	00:00.0
7	16:21.8	00:00.0

If the Select statement is migrated partially we get a very different result as shown below. For this reason, the complete Select statement will be commented out with this EWI

Select Query

SELECT 
  ID,
  Start,
  END
FROM 
  timetable 
-- FOR system_time as of '2022-05-09-16.20.17.0';

Result

ID	START	END
2001	22:39.5	00:00.0
2002	22:39.6	00:00.0
2003	22:39.8	00:00.0
1004	19:45.7	00:00.0
1005	19:45.8	00:00.0
1006	19:46.0	00:00.0
7	16:21.8	00:00.0

Code example

DB2

Create table

CREATE TABLE T1(
COL1 DATE,
COL2 DATE,
PERIOD SYSTEM_TIME (COL1, COL2)
)

Select Query

SELECT 
  ID,
  Start,
  END
FROM 
  timetable 
FOR system_time as of '2022-05-09-16.20.17.0';

Snowflake

Create Table

CREATE TABLE PUBLIC.T1(
COL1 DATE,
COL2 DATE
-- MSC-WARNING - MSCEWI5003 - PERIOD DEFINITION IS NOT SUPPORTED IN SNOWFLAKE.
-- PERIOD SYSTEM_TIME (COL1, COL2)
)

Select Query

-- ** MSC-ERROR - MSCEWI5003 - PERIOD SPECIFICATION IS NOT SUPPORTED IN SNOWFLAKE. **
--SELECT
--  ID,
--  Start,
--  END
--FROM
--   timetable 
--FOR system_time AS OF '2022-05-09-16.20.17.0'";

Recommendations

• Snowflake allows the storage of historical table data for up to 90 days, to know more about this see Understanding & Using Time Travel.

• If you need more support, you can email us at [email protected]