Predicate-level detail and query types in LISTING_ACCESS_HISTORY

Use cases

Imagine that you share data on the Snowflake Marketplace and want to understand precisely how consumers interact with your tables to optimize your shares. With this update, you can:

• Identify which consumer query types are executed, including data unloads.
• Analyze which columns are used in grouping, aggregation, and filtering clauses.
• Capture predicate values on date and timestamp columns to see how far back consumers query.

How to use

You can query the access history view to check the filter value ranges used by consumers on temporal columns.

select
  lah.consumer_account_organization,
  lah.consumer_account_name,
  sos.value:"objectName"::string as object_name,
  filters.value:"columnName"::string as column_name,
  min(filters.value:"columnValue"::date) as earliest_filter_value,
  max(filters.value:"columnValue"::date) as latest_filter_value,
  count(distinct lah.query_token) as n_queries
from SNOWFLAKE.DATA_SHARING_USAGE.LISTING_ACCESS_HISTORY as lah
join lateral flatten(input=>lah.share_objects_accessed) as sos
join lateral flatten(input=>sos.value, path=>'filterObjects') as filters
where true
  and lah.query_date >= dateadd('day', -7, current_date())
  and filters.value:"columnValue"::date is not null
group by 1,2,3,4
order by 1,2,3,4;

What happens in this code:

1. Queries the SNOWFLAKE.DATA_SHARING_USAGE.LISTING_ACCESS_HISTORY view to retrieve recent query history for shared data.
2. Uses the flatten function to extract accessed share objects and the contents of the filterObjects path.
3. Filters for records from the last 7 days where a valid date predicate value exists.
4. Returns the earliest and latest queried dates by consumer account, grouped by organization, account, object, and column.

Limitations and requirements

• This feature is generally available (GA).
• Latency for the view can be up to 2 days.
• Data is retained for 365 days (1 year).
• For queries predating the QUERY_TYPE column or unsupported types, values may return as <redacted> or NULL (such as when IS_APPLICATION is true).