View Refresh Frequency

[noorbasha534]

Dear all

we have around 5000+ finished data products (aka views) in several schemas of unity catalog. One question that comes from business users frequently is - how frequently these get refreshed?

for that the answer is not simpler as the underlying tables are across several layers - bronze, silver (these in turn read from bronze), and these get processed by at least 200+ Databricks jobs defined with a refresh schedule.

I plan to query the lineage of these 5000+ views, reach the bronze and silver layer tables and report the refresh schedule on the top of it.

Has anyone done something similar? Is there an OOTB script to do so or...

Appreciate the mind share

[mark_ott]

No full OOTB script is available, but it is a common requirement. The task is generally solved by custom scripting:

• Extracting lineage from Unity Catalog API,

• Mapping those to ETL jobs and their schedules (using Databricks Jobs API),

• Combining that information for reporting.
  Both APIs are robust and scriptable, making the solution feasible but requiring development work

[saurabh18cs]

Hi @noorbasha534 

just a pseudocode:

for view in all_views:
lineage = get_lineage(view) # Use Unity Catalog API
base_tables = extract_base_tables(lineage)
refresh_times = []
for table in base_tables:
job = find_job_refreshing_table(table) # Custom logic/metadata
schedule = get_job_schedule(job) # Use Jobs API
refresh_times.append(schedule)
view_refresh = aggregate_refresh_times(refresh_times)
report[view] = view_refresh