SSC-FDM-0017

WITH SYSTEM VERSIONING clause is not supported by Snowflake

Description

The WITH SYSTEM VERSIONING clause in ANSI SQL is used to enable system versioning for a table, allowing you to maintain a history of changes to the table's data over time. This clause is not supported by Snowflake.

Code Example

Input Code:

IN -> Greenplum_01.sql

CREATE TABLE t1 (
    ID INT PRIMARY KEY,
    Name VARCHAR(50),
    SysStartTime TIMESTAMP,
    SysEndTime TIMESTAMP
) WITH SYSTEM VERSIONING;

Output Code:

OUT -> Greenplum_01.sql

CREATE TABLE t1 (
    ID INT PRIMARY KEY,
    Name VARCHAR(50),
    SysStartTime TIMESTAMP,
    SysEndTime TIMESTAMP
)
----** SSC-FDM-0017 - WITH SYSTEM VERSIONING CLAUSE IS NOT SUPPORTED BY SNOWFLAKE. **
--WITH SYSTEM VERSIONING
                      ;

Recommendations

• You can use Time Travel in Snowflake, Time Travel enables accessing historical data (i.e. data that has been changed or deleted) at any point within a defined period. It serves as a powerful tool for performing the following tasks:

  • Restoring data-related objects (tables, schemas, and databases) that might have been accidentally or intentionally deleted.

  • Duplicating and backing up data from key points in the past.

  • Analyzing data usage/manipulation over specified periods of time.

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