Dynamic Table Refresh Cost Optimizer

Status: In Progress

This POC is currently under active development. The core analysis queries are complete; the Streamlit UI is being built.

What It Will Do

• Query SNOWFLAKE.ACCOUNT_USAGE.DYNAMIC_TABLE_REFRESH_HISTORY for all DTs
• Calculate credits consumed per refresh cycle
• Compare actual data change rate vs. configured target lag
• Surface DTs where the lag is too aggressive (refreshing when data hasn't changed)
• Recommend optimal TARGET_LAG values for each table

Core Analysis Query

SELECT
  TABLE_NAME,
  TARGET_LAG_SEC,
  AVG(REFRESH_ACTION) AS avg_action,          -- 'INCREMENTAL' vs 'FULL'
  AVG(CREDITS_USED)   AS avg_credits,
  COUNT(*)            AS refresh_count,
  -- Estimate wasted refreshes (no data change)
  SUM(CASE WHEN DATA_CHANGED = FALSE THEN 1 ELSE 0 END) AS empty_refreshes
FROM SNOWFLAKE.ACCOUNT_USAGE.DYNAMIC_TABLE_REFRESH_HISTORY
WHERE REFRESH_START_TIME >= DATEADD('day', -30, CURRENT_TIMESTAMP())
GROUP BY 1, 2
ORDER BY avg_credits DESC;

Expected Output

A Streamlit dashboard showing:

• Credit spend per dynamic table (bar chart)
• Recommended lag adjustments (table)
• Estimated monthly savings

Check back soon — expected completion: August 2026.