Is there any standard stored procedure to capture table refresh details in snowflake

Question

I am trying to log table refresh details in snowflake DWH Details include below Batch Date, Source Table Name, Target Table Name, rows loaded, timestamp, status, err.Message.

Is there any standard SQL\Snowflake stored procedure which can be useful as common one for entire DWH to trace\audit table refresh details and log them into single table.

I have the variables which captures Batchdate, target table name, source table name, etc... If I get standard stored procedure which can log start of the activity and end of the activity, that really helpful.

Regards, Srinivas

Answer 1

If you are looking for some ideas moving forward, here are a couple of things that can help you out:

1. Query History is useful, but hard to filter. If you use a query_tag in your batch processes, then you can reference query_history for information.
2. In addition, if you want to capture information as its running, you could use Streams and Tasks on your tables to capture counts of updates/inserts/deletes, etc. for each batch in the background.

There is no standard stored procedure that you can leverage within Snowflake to query this information, but there is a lot of data available in the snowflake.account_usage share.

Answer 2

Not sure what exactly you're trying to achieve here, but

• you can use last_altered on a table to see when the data was last modified
• you can filter the query_history view to see what queries modified the table: https://docs.snowflake.com/en/sql-reference/functions/query_history.html

Answer 3

You can take advantage of Snowflake Streams https://docs.snowflake.com/en/sql-reference/sql/create-stream.html

When you create a stream, you point it to a target table. So, your stream, records changes produces on the target table (INSERTS, UPDATES and DELETES) between two points in time.

You can use your stream as any table to select over it, to look for changes.

What's great about streams is that after a succesfully DML operation is done by using data from any stream, the stream is purged, so when you query against it, it'll be empty.

Use them free of guilty, since streams don´t duplicate your data, they just storage the offset and the CDC, so data remains on your table.

Some useful guides: it generates something related you need - Part 1: https://www.snowflake.com/blog/building-a-type-2-slowly-changing-dimension-in-snowflake-using-streams-and-tasks-part-1/ - Part 2: https://www.snowflake.com/blog/building-a-type-2-slowly-changing-dimension-in-snowflake-using-streams-and-tasks-part-2/