Select Flashback Query

Description

Oracle

The flashback query claused in Oracle retrieves past data from a table, view, or materialized view. In Oracle, the uses can include:

• Restoring deleted data or undoing an incorrect commit, comparing current data with the corresponding data at an earlier time, checking the state of transactional data at a particular time, and reporting generation tools to past data, among others. (Oracle Flashback query documentation).

Snowflake

The equivalent mechanism in Snowflake to query data from the past is the AT | BEGIN query. Notice that the only equivalent is for the AS OF statements.

Furthermore, Snowflake has complete "Time Travel" documentation that allows querying data to clone objects such as tables, views, and schemas. There are limitations on the days to access the past or deleted data (90 days before passing to Fail-safe status). For more information, review the Snowflake Time Travel Documentation.

Oracle syntax

{ VERSIONS BETWEEN
  { SCN | TIMESTAMP }
  { expr | MINVALUE } AND { expr | MAXVALUE }
| AS OF { SCN | TIMESTAMP } expr
}

Snowflake Syntax

SELECT ...
FROM ...
  {
   AT( { TIMESTAMP => <timestamp> | OFFSET => <time_difference> | STATEMENT => <id> | STREAM => '<name>' } ) |
   BEFORE( STATEMENT => <id> )
  }
[ ... ]

Notice that the query ID must reference a query executed within 14 days. If the query ID references a query over 14 days old, the following error is returned: Error: statement <query_id> not found. To work around this limitation, use the time stamp for the referenced query. (Snowflake AT | Before documentation)

Sample Source Patterns

The following data is used in the following examples to generate the query outputs.

Oracle

IN -> Oracle_01.sql

CREATE TABLE Employee (
    EmployeeID NUMBER PRIMARY KEY,
    FirstName VARCHAR2(50),
    LastName VARCHAR2(50),
    EmailAddress VARCHAR2(100),
    HireDate DATE,
    SalaryAmount NUMBER(10, 2)
);

INSERT INTO Employee VALUES (1, 'Bob', 'SampleNameA', '[e-mail]', TO_DATE('2023-01-15', 'YYYY-MM-DD'), 11111.00);
INSERT INTO Employee VALUES (2, 'Bob', 'SampleNameB', '[e-mail]', TO_DATE('2023-01-15', 'YYYY-MM-DD'), 11111.00);
INSERT INTO Employee VALUES (3, 'Bob', 'SampleNameC', '[e-mail]', TO_DATE('2022-03-10', 'YYYY-MM-DD'), 11111.00);
INSERT INTO Employee VALUES (4, 'Bob', 'SampleNameD', '[e-mail]', TO_DATE('2022-03-10', 'YYYY-MM-DD'), 11111.00);
INSERT INTO Employee VALUES (5, 'Bob', 'SampleNameE', '[e-mail]', TO_DATE('2022-03-10', 'YYYY-MM-DD'), 11111.00);

Snowflake

OUT -> Oracle_01.sql

CREATE OR REPLACE TABLE Employee (
       EmployeeID NUMBER(38, 18) /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/ PRIMARY KEY,
       FirstName VARCHAR(50),
       LastName VARCHAR(50),
       EmailAddress VARCHAR(100),
       HireDate TIMESTAMP /*** SSC-FDM-OR0042 - DATE TYPE COLUMN HAS A DIFFERENT BEHAVIOR IN SNOWFLAKE. ***/,
       SalaryAmount NUMBER(10, 2) /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/
   )
   COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
   ;

   INSERT INTO Employee
   VALUES (1, 'Bob', 'SampleNameA', '[e-mail]', TO_DATE('2023-01-15', 'YYYY-MM-DD'), 11111.00);

   INSERT INTO Employee
   VALUES (2, 'Bob', 'SampleNameB', '[e-mail]', TO_DATE('2023-01-15', 'YYYY-MM-DD'), 11111.00);

   INSERT INTO Employee
   VALUES (3, 'Bob', 'SampleNameC', '[e-mail]', TO_DATE('2022-03-10', 'YYYY-MM-DD'), 11111.00);

   INSERT INTO Employee
   VALUES (4, 'Bob', 'SampleNameD', '[e-mail]', TO_DATE('2022-03-10', 'YYYY-MM-DD'), 11111.00);

   INSERT INTO Employee
   VALUES (5, 'Bob', 'SampleNameE', '[e-mail]', TO_DATE('2022-03-10', 'YYYY-MM-DD'), 11111.00);

1. AS OF with TIMESTAMP case

Oracle

Query

IN -> Oracle_02.sql

SELECT * FROM employees
AS OF TIMESTAMP
TO_TIMESTAMP('2023-09-27 07:00:00', 'YYYY-MM-DD HH:MI:SS')
WHERE last_name = 'SampleName';

Snowflake

Query

OUT -> Oracle_02.sql

SELECT * FROM
employees
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0135 - DATA RETENTION PERIOD MAY PRODUCE NO RESULTS ***/!!!
AT (TIMESTAMP =>
TO_TIMESTAMP('2023-09-27 07:00:00', 'YYYY-MM-DD HH:MI:SS'))
WHERE last_name = 'SampleName';

2. AS OF with SCN case

Oracle

Query

IN -> Oracle_03.sql

SELECT * FROM employees
AS OF SCN
TO_TIMESTAMP('2023-09-27 07:00:00', 'YYYY-MM-DD HH:MI:SS')
WHERE last_name = 'SampleName';

Snowflake

Query

OUT -> Oracle_03.sql

SELECT * FROM
employees
!!!RESOLVE EWI!!! /*** SSC-EWI-0040 - THE STATEMENT IS NOT SUPPORTED IN SNOWFLAKE ***/!!!
AS OF SCN
TO_TIMESTAMP('2023-09-27 07:00:00', 'YYYY-MM-DD HH:MI:SS')
WHERE last_name = 'SampleName';

Known Issues

1. The option when it is using SCN is not supported.

2. The VERSION statement is not supported in Snowflake.

Related EWIS

1. SSC-EWI-0040: Statement Not Supported.

2. SSC-EWI-OR0135: Current of clause is not supported in Snowflake.

3. SSC-FDM-0006: Number type column may not behave similarly in Snowflake.

4. SSC-FDM-OR0042: Date Type Transformed To Timestamp Has A Different Behavior.