Get the essential data observability guide
Download this guide to learn:
What is data observability?
4 pillars of data observability
How to evaluate platforms
Common mistakes to avoid
The ROI of data observability
Unlock now
Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.
Sign up for a free data observability workshop today.
Assess your company's data health and learn how to start monitoring your entire data stack.
Book free workshop
Sign up for news, updates, and events
Subscribe for free
Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.
Getting started with Data Observability Guide

Make a plan to implement data observability across your company’s entire data stack

Download for free
Book a data observability workshop with an expert.

Assess your company's data health and learn how to start monitoring your entire data stack.

Book free workshop

Stay Fresh: Two Ways to Track Update Times for Snowflake Tables and Views

Learn why data freshness is crucial for accurate decision-making and explore effective methods to determine the last update time for Snowflake tables and views.

May 14, 2023

Co-founder / Data and ML

May 14, 2023
Stay Fresh: Two Ways to Track Update Times for Snowflake Tables and Views

Ever experienced a delayed dashboard? Been frustrated by late data for that critical report? That's the sting of stale data, or rather, data that isn’t fresh.

The freshness of a table or view is how frequently it is updated relative to requirements. If a table is expected to be fresh to the hour, but hasn’t been updated in a day, then it is stale. Why is data freshness important? Because inaccurate or outdated information can lead to misguided decisions, muddled forecasting, and even regulatory non-compliance. 

Understanding when your Snowflake table or view was last updated isn't just a nice-to-know—it's a need-to-know. It's about ensuring your data is as fresh as your morning coffee, ready to power your day's insights and actions.

In this guide, we'll be equipping you with two vital tools in your data freshness arsenal: the MAX function and the LAST_UPDATED column. These are your hammer and screwdriver for ensuring your data is up-to-date and accurate, ready to power the decisions that matter.

Determining Last Update Time Using the MAX Function

If you have a timestamp column in your Snowflake table, one of the simplest ways to find the most recent update is to use the MAX function. The MAX function returns the maximum value of the specified column. For example, if you have a column named `timestamp_column` that is updated whenever a row is modified, you can use the following SQL to get the latest update time:


SELECT MAX(timestamp_column) AS last_update_time

FROM your_table_name;


Replace `your_table_name` with the name of your table, and you'll get the latest timestamp from the `timestamp_column` column.

This approach works equally well for both tables and views. However, remember that views in Snowflake are essentially saved queries. They don't store data themselves but reflect the data in the underlying tables. Therefore, the freshness of the data in a view is dependent on the freshness of the data in the underlying tables.

Two quick notes:
1. Also note that the precision of the timestamp column used with the MAX function can impact the accuracy of the last update time. If the precision is set to seconds, for example, multiple updates within the same second may not be accurately represented.

2. Using the `MAX` function on a large table can be resource-intensive and may impact performance. Consider using partitioning, clustering or materialized views to optimize this operation.


Leveraging the LAST_ALTERED Column

What if your table doesn't have a timestamp column? Don't worry, Snowflake has you covered. You can retrieve the last update time from the `LAST_ALTERED` column in the `information_schema.tables` or `information_schema.views` system view.

Here's an example:


SELECT table_name, last_altered

FROM information_schema.tables

WHERE table_schema = 'your_schema' AND table_name = 'your_table_name';


In this query, `your_schema` and `your_table_name` should be replaced with your schema and table name, respectively.

This approach provides system-level information, which can be especially useful if your table or view doesn't have a timestamp column. However, there are a couple of things to keep in mind:

1. The `last_altered` column reflects the last time the table structure (like adding a new column) was altered, not the last time the data within the table was updated.

2. This approach works well for tables, but for views, the `last_altered` timestamp may not reflect the latest data update time, as it only tracks changes to the view's structure or definition.

3. The freshness of a view is contingent upon the underlying tables' data freshness. A view does not hold any data, but instead, it represents the data residing in the base tables.

Wrapping Up

Understanding the freshness of your data in Snowflake is crucial for accurate and timely data analysis. With the MAX function and the `LAST_ALTERED` column, you can keep track of the last update time in your tables and views. Just remember that while these methods are robust, they have their nuances. 

Make sure to consider whether you're dealing with a table or a view, and whether you're interested in changes to the data or changes to the structure of the database object. Happy data tracking!

Want to track the freshness of Snowflake tables and views within minutes, then be alerted on anomalies with machine learning that accounts for trends and seasonalities? Get started with Metaplane for free or book a demo to learn more.

We’re hard at work helping you improve trust in your data in less time than ever. We promise to send a maximum of 1 update email per week.

Your email
Ensure trust in data

Start monitoring your data in minutes.

Connect your warehouse and start generating a baseline in less than 10 minutes. Start for free, no credit-card required.