CODEWITHSUNDEEP
snowflake-cloud-data-platformsnowflake-schemadata-ingestion
Allanh
Allanh

Reputation: 507

Is it possible to know last time a Row was updated in a table or view in Snowflake?

I’m working on an implementation of a view in Snowflake. I want to know when was the last time that a single row was updated. Is there a column from Snowflake or maybe a way to do that? For example. I have a user table with ID and NAME columns and a row just like this:

ID = 1
NAME = Allan

If I update the name from Allan to Juan I would like to know exactly when that happened for that row. There is a way to do that in the view?

I would like to add that "last update" column as a part of the view.

Upvotes: 0

Views: 519

Answers (2)

SnowPro_Engineer
SnowPro_Engineer

Reputation: 1

@SimeonPilgrim appears to have the correct answer but it may also be worthwhile adding an UPDATED_BY, INSERTED_TIMESTAMP as well as the UPDATED_TIMESTAMP columns so you can better track changes in your table as is standard practice in S&P IHS Markit EDM (formerly CADIS) (ETL package used in financial services).

Something like this:

CREATE OR REPLACE TABLE T_EMPLOYEE(
    EMPLOYEE_ID INTEGER,
    TITLE VARCHAR(50),
    FIRST_NAME VARCHAR(100),
    LAST_NAME VARCHAR(100),
    SALARY DECIMAL(10, 2),
    INSERTED_TIMESTAMP TIMESTAMP DEFAULT CURRENT_TIMESTAMP(),
    UPDATED_TIMESTAMP TIMESTAMP DEFAULT CURRENT_TIMESTAMP(),
    UPDATED_BY VARCHAR(100) DEFAULT CURRENT_USER()
    );

INSERT INTO T_EMPLOYEE (EMPLOYEE_ID, TITLE, FIRST_NAME, LAST_NAME, SALARY)
VALUES (1, 'DR.', 'Carrie', 'Madej', 100000);

SELECT * FROM T_EMPLOYEE;

Upvotes: 0

Suraj Ganiga
Suraj Ganiga

Reputation: 31

Row level modification timestamp is not available in the information_schema views currently.

However, there is a column LAST_ALTERED in the SNOWFLAKE.ACCOUNT_USAGE.TABLES view that gives the Date and time when the table was last altered by a DDL or DML operation.

For the specific requirement you have shared as part of the example, you may consider implementing a stream.

Standard stream (also known as Delta stream) should do. The catch with Standard streams is it performs a join on inserted and deleted rows in the change set to provide the row level delta. As a net effect, for example, a row that is inserted and then deleted between two transactional points of time in a table is removed in the delta (i.e. is not returned when the stream is queried).

If there is a requirement to track each and every single modification to every row, then we could consider implementing a combination of two streams - a Standard stream and an Append Only stream.

An append-only stream exclusively tracks row inserts. Update, delete, and truncate operations are not captured by append-only streams. For instance, if 10 rows are initially inserted into a table, and then 5 of those rows are deleted before advancing the offset for an append-only stream, the stream would only record the 10 inserted rows.

Reference: https://docs.snowflake.com/en/user-guide/streams-intro

Upvotes: 2

Related Questions