Retrieving and storing extraction dates from tables in Snowflake

Introduction

I've quite some tables in which an extraction timestamp is stored. The goal of this blogpost is to describe a way to retrieve dynamically a list of tables from the INFORMATION_SCHEMA and determine the Maximum Extraction date per table.

Some samples

Here are some sample tables with records.

CREATE  DATABASE IF NOT EXISTS DYNAMICSQL;

USE DYNAMICSQL;

--Sample table and sample data
CREATE OR REPLACE TABLE TABLE1 (EXTRACTTIMESTAMP TIMESTAMP_NTZ);
INSERT INTO TABLE1 (EXTRACTTIMESTAMP) VALUES ('2023-10-01'), ('2023-10-01'), ('2023-10-02');

--Sample table and sample data
CREATE OR REPLACE TABLE TABLE2 (EXTRACTTIMESTAMP TIMESTAMP_NTZ);
INSERT INTO TABLE2 (EXTRACTTIMESTAMP) VALUES ('2023-09-01'), ('2023-10-07'), ('2023-10-02');

--The log table where the maximum Extract Timestamp should be stored
CREATE OR REPLACE TABLE EXTRACTTIMESTAMP (TABLE_NAME STRING, MAXEXTRACTTIMESTAMP TIMESTAMP_NTZ);

The code

For this demo I've used a cursor in the DECLARE. I've also added a  OUTPUT variable for debugging. With this I can generate a string and I inspect this string to determine if the code is correct.

-- Truncate the table
TRUNCATE TABLE EXTRACTTIMESTAMP;

--Code block (can be used in a stored procedure)
DECLARE
   QUERY        STRING;
   OUTPUT       STRING DEFAULT '';
   c1           CURSOR FOR SELECT TABLE_NAME, TABLE_SCHEMA FROM INFORMATION_SCHEMA.TABLES 
                WHERE TABLE_SCHEMA NOT IN ('INFORMATION_SCHEMA') AND TABLE_NAME NOT IN ('EXTRACTTIMESTAMP');
BEGIN
   OPEN c1;

   FOR rec IN c1 DO
       QUERY := 'INSERT INTO EXTRACTTIMESTAMP (TABLE_NAME, MAXEXTRACTTIMESTAMP) SELECT ''' || rec.TABLE_NAME || ''', MAX(EXTRACTTIMESTAMP) FROM ' || rec.TABLE_NAME || ';';
     
       EXECUTE IMMEDIATE :QUERY;
       OUTPUT := OUTPUT || QUERY;
   END FOR;
   
   RETURN :OUTPUT;
END;

-- and the result....
SELECT * FROM EXTRACTTIMESTAMP;

Final thoughts

A small example on how to store values in a table in a dynamic SQL way.

Hennie