Why this matters
Incremental refresh lets BI Analysts refresh only the new and changed data instead of reprocessing the entire dataset. This drastically reduces refresh time, compute cost, and failures on large fact tables. You will use it when building sales dashboards, financial reports, marketing funnels, and any refresh-scheduled model that grows every day.
- Daily sales model: refresh last 7 days in minutes instead of hours.
- Finance ledger: preserve 5 years of history, refresh the latest month.
- Event data: capture late-arriving fixes by reprocessing a small overlap.
Concept explained simply
Incremental refresh splits your data into time-based partitions (for example, one per day). On each scheduled refresh, only the newest partitions (like the last 7 days) are refreshed, while older partitions stay untouched.
Short example
Instead of reloading 200 million rows, the system refreshes just yesterday and today. If historical data rarely changes, this is safe and fast.
Mental model
Think of your dataset as a filing cabinet:
- Each drawer = a partition (e.g., one per day).
- When you refresh, you only open the last few drawers (recent days).
- Sometimes you re-open a couple of recent drawers to catch late papers (late-arriving data).
Core building blocks
- Partitioning key: usually a date column (e.g., TransactionDate).
- Watermark: a filter using RangeStart/RangeEnd or parameters to push time filters to the source.
- Retention window: how much history to keep (e.g., 5 years).
- Refresh window: how many recent periods to reprocess each run (e.g., last 7 days).
- Overlap/change detection: reprocess a few recent periods or detect updates via a LastModified column to catch late-arriving changes.
- Query folding/predicate pushdown: the source must apply filters server-side; otherwise, performance suffers.
- Schedule: how often to refresh (daily, hourly).
- Validation: checks that data for recent periods matches expectations after refresh.
Worked examples
Example 1: Daily retail sales
Dataset: 5 years of sales with columns [OrderID, OrderDate, Amount, LastModifiedAt].
- Retention: keep 5 years.
- Refresh window: last 7 days.
- Change detection: reprocess last 2 days or use LastModifiedAt.
- Filter predicate: OrderDate between RangeStart and RangeEnd.
What happens on refresh?
The engine refreshes only the last 7 daily partitions, merging any late changes from the last 2 days. Older partitions remain untouched.
Example 2: Finance ledger with month-end adjustments
Dataset: 3 years of GL entries. Adjustments may occur up to 10 days after month end.
- Retention: 36 months.
- Refresh window: last 45 days (covers end-of-month plus adjustments).
- Partitioning: daily or monthly (daily gives finer control).
Why a longer refresh window?
Month-end entries and corrections often arrive late. A 45-day window safely reprocesses any affected days.
Example 3: Event logs with CDC (Change Data Capture)
Dataset: Append-mostly events; updates are rare but tracked via UpdatedAt.
- Retention: 2 years.
- Refresh window: 14 days.
- Change detection: UpdatedAt ensures updated rows within the window are reloaded.
Outcome
Daily refresh runs quickly (only recent partitions), while still capturing rare corrections because UpdatedAt triggers reprocessing in the window.
How to design an incremental refresh policy
- Pick the partitioning key: choose a reliable date (e.g., TransactionDate). Avoid computed or non-foldable columns.
- Define retention: how much history users actually need (e.g., 36 months).
- Set refresh window: how far back changes can occur (e.g., 14 days). Add a small overlap for safety.
- Ensure predicate pushdown: confirm your time filter is applied at the source (query folding/pushdown).
- Validate: compare row counts and key metrics before vs. after to ensure correctness.
Pre-flight checklist
- There is a stable date or LastModified column.
- The source can filter on that column efficiently (indexes/partitioning).
- Your query folds/pushes filters to the source.
- Refresh schedule matches business freshness needs.
- Monitoring is in place (row counts, totals, errors).
Exercises
Do these to lock in the concepts. The quick test is available to everyone; only logged-in users get saved progress.
Exercise 1 (mirrors Ex1 below)
Design an incremental refresh policy for a 200M-row daily fact table with columns: EventDate (date), UpdatedAt (datetime). Requirements: keep 5 years, refresh last 10 days, catch late updates for last 3 days.
- Deliverable: a short written policy (partitioning, retention, refresh window, overlap/change detection, schedule).
- Self-check: does the source filter on EventDate fold?
Exercise 2 (mirrors Ex2 below)
Estimate refresh time improvement if full refresh is 120 minutes and only 1% of rows are new/changed daily.
- Deliverable: a time estimate and assumptions.
- Self-check: did you include overhead time, not just pure data share?
Practice checklist
- I chose a stable time column for partitioning.
- I defined retention separately from refresh window.
- I planned for late-arriving data (overlap or change detection).
- I verified folding/pushdown.
- I added a simple validation plan after refresh.
Common mistakes and how to self-check
- Mistake: Using a non-foldable filter (e.g., wrapping the date in complex functions). Self-check: Inspect the query to ensure the date filter is pushed to the source; remove transformations that break folding.
- Mistake: No overlap for late-arriving data. Self-check: Ask domain owners how often back-dated changes happen; if unsure, add a small overlap (2–14 days).
- Mistake: Too-long refresh windows reprocess more than needed. Self-check: Compare incidents of late changes versus extra refresh time; right-size the window.
- Mistake: Retention set too short, losing history users need. Self-check: Confirm analytical use cases (year-over-year, seasonality) before trimming history.
- Mistake: Assuming refresh fixes bad source indexing. Self-check: Ensure the source has indexes/partitions on the filter column.
Quick self-audit before going live
- Folding confirmed with a simple date predicate.
- Indexes/partitions exist on the time column.
- Retention and refresh windows documented.
- Monitoring: compare row counts and key totals for the refreshed window.
- Fallback plan: how to run a one-time full refresh if needed.
Practical projects
- Retail dashboard acceleration: Implement incremental refresh on a sales fact. Measure refresh time before/after and document cost/time savings.
- Finance close reliability: Add a 10-day overlap window for EOM adjustments. Create a validation report comparing totals pre- and post-refresh.
- Event pipeline hardening: Use UpdatedAt to detect late changes within 14 days and produce a daily exceptions list (IDs changed in last 24h).
Who this is for
- BI Analysts and developers managing scheduled refresh models.
- Data modelers handling large, growing fact tables.
- Analysts responsible for data freshness SLAs.
Prerequisites
- Basic data modeling (facts/dimensions, date keys).
- Ability to write simple time filters in SQL or query editors.
- Understanding of scheduled refresh and source connectivity.
Learning path
- Before: Data modeling basics, query folding/pushdown fundamentals.
- Now: Incremental Refresh Concepts (this page) + exercises.
- Next: Partition strategies, change detection patterns, monitoring and alerting.
Next steps
- Complete the exercises and validate folding on your source.
- Pilot incremental refresh on a non-critical dataset.
- Roll out to priority models with documented SLAs.
Mini challenge
Your marketing events table has CampaignDate and UpdatedAt. Late corrections happen within 5 days. Users need 18 months of history.
- Propose: retention, refresh window, overlap/change detection, and schedule.
- Write one validation you will run after each refresh.
Progress & test
The quick test below is available to everyone. Only logged-in users get saved progress and streaks.