Self-service BI dashboard not refreshing in cloud - cache invalidation

BA/BI Snowflake
, , , , , , , ,
1

After our migration to Snowflake cloud, our self-service BI dashboards are showing stale data. Users report that dashboards display data from yesterday even though our ETL jobs have completed successfully this morning. The underlying tables have fresh data when I query them directly, but the BI dashboards don’t reflect the updates. Cache invalidation seems broken after the cloud migration. The dashboards used to refresh automatically every hour in our on-prem setup. Any ideas what changed in cloud deployment that would affect cache behavior?

2

Cloud deployments often have different caching layers. Check if your BI dashboard is using a materialized view or caching layer that needs explicit refresh. In cloud, automatic cache invalidation might not be triggered the same way as on-prem. You may need to add a cache refresh step to your ETL workflow.

3

That makes sense. Our ETL jobs write to Snowflake tables, but maybe the BI dashboard cache isn’t being notified. How do I set up automatic cache invalidation when ETL completes? Is there a Snowflake-specific API call or configuration setting?

4

I’ve seen this before. The issue is usually the ETL job completing successfully but not triggering the dashboard cache invalidation. In cloud deployments, the BI layer might be running in a different compute context that doesn’t automatically detect table changes. You need to explicitly call a cache refresh API or use Snowflake streams to notify the BI layer of data changes.

5

Here’s a comprehensive solution addressing all three focus areas:

Cache Invalidation Strategy: Implement explicit cache refresh after ETL completion. Add this as the final step in your ETL workflow:

  1. Use Snowflake task dependency to trigger cache refresh:

    • Create a task that runs after your ETL task completes
    • This task calls your BI platform’s cache invalidation API
    • Most BI tools (Tableau, Power BI, Looker) have REST APIs for this

  2. Alternatively, use Snowflake streams to detect changes:

    • Create a stream on your fact tables
    • BI dashboard queries the stream to detect new data
    • Dashboard auto-refreshes when stream shows changes

  3. Configure cache TTL appropriately:

    • For real-time dashboards: 5-15 minute TTL
    • For daily reports: 1-hour TTL aligned with ETL schedule
    • For executive dashboards: 4-hour TTL with manual refresh option

ETL Data Sync: Ensure proper synchronization between ETL and BI layers:

  1. Add explicit commit points in your ETL:

    • After data load completes, commit transaction explicitly
    • Wait for Snowflake to acknowledge commit before triggering BI refresh
    • Use SYSTEM$WAIT_FOR_TASK() if using Snowflake tasks

  2. Implement health check before cache invalidation:

    • Query row counts and max timestamps to verify fresh data
    • Only invalidate cache if verification passes
    • Log verification results for troubleshooting

  3. Handle ETL failures gracefully:

    • Don’t invalidate cache if ETL fails
    • Send alerts if dashboard data is more than 4 hours old
    • Provide fallback to previous day’s data if current ETL incomplete

Cloud Dashboard Refresh: Configure cloud-specific refresh mechanisms:

  1. Update dashboard connection settings:

    • Disable client-side caching in BI tool
    • Set query result cache to 0 or minimal value
    • Enable “always use fresh data” option if available

  2. Use Snowflake’s result cache strategically:

    • Disable result cache for operational dashboards
    • Enable result cache only for historical analysis dashboards
    • Set appropriate cache expiration aligned with ETL schedule

  3. Implement push-based refresh instead of pull-based:

    • ETL job actively triggers dashboard refresh via API
    • Don’t rely on dashboard polling for changes
    • Use webhooks or event-driven architecture

Why Cloud Differs from On-Prem: On-prem systems often had tightly coupled ETL and BI components running on the same server, allowing automatic change detection. Cloud deployments separate these concerns - ETL runs in Snowflake compute, BI runs in separate cloud service. This decoupling requires explicit orchestration.

Recommended Implementation: Add this final task to your ETL workflow:

  1. Verify data freshness (query max timestamp)
  2. Call BI platform cache invalidation API
  3. Wait for cache refresh completion
  4. Log success/failure for monitoring

This ensures your self-service BI dashboards always reflect the latest ETL data in cloud deployments.

6

You have a few options. One approach is to add a cache invalidation step at the end of your ETL job. Many BI tools expose a REST API to trigger cache refresh. Another option is to reduce the cache TTL (time to live) on your dashboards so they refresh more frequently. But the root issue is that cloud ETL and BI layers are more decoupled than on-prem, so you need explicit synchronization.

7

Check your dashboard refresh schedule settings. In cloud deployments, the default refresh interval might be different from what you had on-prem. Also verify that the BI service account has permission to read the latest data. Sometimes cloud security policies cache query results at the authentication layer, not just the BI layer.