Actiknow
Data Engineering

Late-Arriving Data in a Warehouse: How to Stop Yesterday’s Numbers From Changing Without Explanation

Learn how to manage late-arriving warehouse data with event-time rules, ingestion watermarks, safe reprocessing, reconciliations and transparent KPI restatements.

Finance executive and data engineering manager reviewing historical revenue changes on an executive warehouse dashboard
Contents hide

The executive problem: yesterday’s revenue changed overnight

A leadership dashboard reports Monday revenue at $480,000 on Tuesday morning. By Thursday, Monday shows $493,000. Nobody changed the dashboard formula, and the pipeline logs are green. Finance asks which number is correct. The answer may be that both were accurate snapshots of what the warehouse knew at different times, but that explanation is not visible in the report.

Late-arriving data is an ordinary property of distributed business systems, not necessarily a processing failure. Payment settlement files arrive after transactions. Offline devices upload events when they reconnect. CRM users backdate opportunities. A supplier sends a corrected invoice after the reporting cutoff. The problem begins when reporting silently treats incomplete periods as final.

The goal is not to prevent historical numbers from ever changing. Some changes are legitimate and necessary. The goal is to make changes predictable, auditable and explainable, while ensuring that truly closed periods remain controlled. This requires a policy spanning ingestion, modeling, reporting and business ownership.

What is late-arriving data in a warehouse?

A record is late-arriving when it becomes available to a warehouse after the reporting period or processing window to which it belongs. It might be a new event for an earlier business date, an update to an existing event, a delayed deletion, or a correction to a dimension such as a customer’s historical region.

Consider an order placed at 23:55 on June 30, posted to the operational database at 00:03 on July 1, and ingested into the warehouse at 01:20. The order belongs to June by order date, but the warehouse only learns about it in July. A daily June 30 dashboard refreshed at midnight cannot include it. Whether it should later be restated is a business decision, not just a SQL decision.

Three distinct clocks matter: event time, the timestamp at which the business activity occurred; source update or posting time, when the source committed or revised the record; and warehouse ingestion time, when the data platform received it. A fourth clock, publication time, records when a certified metric became visible to users.

Data architect explaining event time, source update time, warehouse ingestion time and publication time

Do not use these clocks interchangeably. A record with a June event date and July ingestion date is not automatically a July sale. Conversely, a journal posted in July with an effective date in June may be governed by finance’s close and adjustment policy. Business definitions must specify which date controls each KPI.

Five ways late data quietly damages executive reporting

1. Incomplete daily totals are presented as final

A report displays yesterday’s orders without a completeness indicator. Users assume the number is settled, then interpret a normal late-file adjustment as a data defect. The fix is to label preliminary periods explicitly and publish an expected finalization time.

2. Incremental filters permanently skip old events

A pipeline reads only records with event_time greater than the maximum event_time already loaded. When a late record arrives with an older event_time, it falls below the filter and may never be processed. A successful job run does not prove that all eligible source records were included.

3. Corrections create duplicate transactions

A retry or backfill appends a corrected invoice rather than replacing the existing version. Revenue grows for the wrong reason. Reliable processing needs stable business keys, an explicit version or ordering rule, and idempotent upserts or scoped replacements.

4. Dimension changes rewrite the past unintentionally

A customer moves from East to West. If all historical sales join to the customer’s current region, last quarter’s regional performance changes even when no sale changed. Slowly changing dimensions and effective-date joins address a different problem from late fact ingestion, but both affect historical KPI trust.

5. Dashboards and exports disagree

A warehouse table may be corrected before a BI semantic model, cached dashboard or scheduled PDF refreshes. Two executives can receive different versions of the same metric. Publication needs a coordinated refresh sequence and an identifiable data version.

Start with a reporting contract, not a lookback interval

For each critical metric, document its business date, authoritative source, timezone, expected delivery cadence, allowed lateness, correction window, close rule, materiality threshold, owner and downstream consumers. This reporting contract should be readable by finance and operations as well as engineers.

For example, an ecommerce operations dashboard might use order-created time in the store’s business timezone and mark the last two days provisional. A finance revenue report might instead use ledger posting date and follow the approved month-end close. The same underlying order can legitimately appear in different periods under these two definitions.

Avoid declaring a universal 48-hour rule for all data. Card settlement, returns, advertising attribution, CRM changes and regulated finance reporting have different delay distributions. Measure them independently. Your business intelligence architecture should preserve these distinctions rather than forcing every dashboard onto a single simplistic timestamp.

Measure lateness before choosing a processing window

Calculate the delay between event_time and ingested_at for every relevant source. Report the median, 95th percentile, 99th percentile and maximum, segmented by source, day of week, country, file type and business process where useful. Also measure the percentage of records and financial value arriving after the initial reporting cutoff.

A sample measurement question is: Of all June 30 orders eventually received, what percentage arrived after July 1 at 08:00, and what share of revenue did they represent? Count-based and value-based lateness can differ substantially. One delayed high-value invoice may matter more than hundreds of small events.

Analytics engineer examining source data arrival delays and event to ingestion timing distributions

Compute these statistics on a sufficiently representative history, including month-end, weekends, holidays, outages and known source disruptions. If the source has no trustworthy ingestion timestamp, start capturing one in the raw landing layer now. Historical lateness before that instrumentation may be impossible to reconstruct precisely.

Keep event-time delays separate from source-to-warehouse delays. An offline app can produce an event days before it reaches its own backend. A warehouse connector cannot fix a delay that occurred upstream of the source system.

Use ingestion watermarks to detect new data

An ingestion watermark records the point through which source changes have been consumed. When a source offers a reliable monotonically advancing update timestamp or change sequence, the ingestion job can select changes since its last committed checkpoint. Store the checkpoint only after the batch is durably written and validated.

For change-data-capture streams, a log position or offset is often stronger than a business event timestamp. For API integrations, a provider’s updated_since field may help, but timestamp precision, sorting, pagination and deletion behavior must be tested. If source timestamps can be backdated, use a safety overlap or periodic reconciliation.

A watermark says how far the pipeline has processed; it does not prove that all events for a business day have arrived. The first is a technical progress signal. The second is a completeness claim that requires source knowledge, lateness evidence or explicit upstream completion signals.

Illustrative SQL: measure event-to-ingestion delay

SELECT CAST(event_time AS DATE) AS event_date,

       COUNT(*) AS records_received,

       SUM(CASE WHEN ingested_at > event_time + INTERVAL ‘1 day’

                THEN 1 ELSE 0 END) AS arrived_over_24h_late

FROM raw_orders

GROUP BY CAST(event_time AS DATE);

This is an illustrative pattern, not a drop-in query for every warehouse. Timestamp arithmetic and timezone handling vary by SQL dialect. It also measures only records currently present, so a source-completeness control is still needed.

Reprocess a measured lookback window safely

A common transformation strategy reprocesses a rolling set of business dates rather than processing only the newest date. If the source’s meaningful lateness is normally within four days, the mart may rebuild the last five or six days to allow for schedule boundaries. The precise interval should be based on evidence, business materiality and compute cost.

Reprocessing must replace complete business-date partitions or merge records using stable keys. Appending the same records again creates duplicates. When replacing a daily aggregate, select all source records for the affected whole day, not only those from the last 24 clock hours. A partial oldest day can overwrite a complete total with an incomplete one.

Data engineers reviewing a historical warehouse reprocessing workflow with stable keys and complete date partitions

For transaction-level facts, a MERGE keyed on source_system plus transaction_id can update changed records and insert new ones. For daily aggregates, a scoped delete-and-insert or partition overwrite can be simpler if performed atomically or behind a controlled publication boundary.

Illustrative dbt pattern: refresh whole business dates

{{ config(materialized=’incremental’, unique_key=’order_date’, incremental_strategy=’delete+insert’) }}

SELECT CAST(order_time AS DATE) AS order_date,

       COUNT(DISTINCT order_id) AS orders,

       SUM(net_amount) AS net_revenue

FROM {{ ref(‘stg_orders’) }}

{% if is_incremental() %}

WHERE CAST(order_time AS DATE) >= (

  SELECT DATEADD(day, -5, MAX(order_date)) FROM {{ this }}

)

{% endif %}

GROUP BY 1

This example is deliberately adapter-specific and simplified. Validate the supported incremental strategy, date arithmetic, source completeness, late updates, deletes and transaction semantics for your platform. The five-day window is illustrative, not a recommended default. The official dbt incremental model guidance explains how filters and materializations interact.

What happens after the lookback window closes?

A rolling window alone cannot catch every late record. An adjustment arriving three months later requires a separate exception path. Design a controlled historical correction workflow with an affected-date list, lineage impact analysis, scoped recomputation, validation, approval and release notes.

For high-volume pipelines, maintain a queue of affected business dates derived from newly ingested or updated records. Recompute those dates even if they fall outside the ordinary rolling window, subject to close-period rules. This can be more precise than repeatedly scanning months of history.

When the business period is closed, choose deliberately among three options: restate the original period, post an adjustment in the current period, or preserve the original published view while showing a corrected analytical view. Different financial and operational metrics may require different policies.

A controlled backfill should preserve a before-state, use an identifiable run ID, reconcile changed and unchanged periods, and coordinate downstream refreshes. For a more detailed operational procedure, see Actiknow’s safe data backfill playbook.

Separate current truth from what was known at the time

Executives sometimes need two answers: What is the corrected June revenue now, and what revenue did we report on July 1? A mutable current-state fact table answers the first question but cannot reliably reconstruct the second without historical publication records.

Use a published-metric snapshot or versioned reporting table for material KPIs. Store metric name, business period, dimensions, value, publication timestamp, data cutoff, model version and approval status. A restatement should produce a new version rather than silently destroying the prior published result.

Finance controller and bi reporting manager comparing original and revised kpi reports with publication dates

This is not the same as a slowly changing dimension. A Type 2 dimension preserves the historical validity of attributes such as region or account tier. A published KPI snapshot preserves what the organization communicated at a particular time. Both may be needed.

For audit-sensitive reporting, retain the source correction reference, change reason, authorizer and comparison of prior versus revised values. Storage costs are usually easier to justify when the retained data supports a concrete audit or decision requirement; retention must still comply with privacy and records policies.

Make provisional and final periods obvious in the dashboard

A dashboard should disclose its data cutoff, refresh time and completeness state. A simple banner can say: “Orders through 8:00 AM today; last three business days provisional.” A certified finance report may instead say: “October close approved November 5; subsequent adjustments shown separately.”

Do not confuse “last refreshed” with “data complete through.” A model refreshed at 09:00 can still contain source data only through 06:30. If the dashboard caches an earlier version, report both the warehouse certification time and the BI refresh time where the distinction matters.

Show a restatement note when a material previously published value changes. For example: “September 30 net revenue increased by $12,400 after delayed marketplace settlement records were received on October 3.” This is an illustrative communication example, not a client outcome.

For decision-critical reports, give users access to a change log that answers what changed, by how much, why, which periods were affected and whether the revised figures are approved. A footnote is often more useful than a hidden technical incident ticket.

Reconcile by business period and source, not just total rows

Reconciliation should compare source and warehouse control totals at the same effective cutoff and grain. Check counts, distinct transaction keys, amounts, refunds, cancellations, deletion flags and status transitions. A global row-count match can hide missing records offset by duplicates.

Maintain a daily completeness ledger containing source, business date, expected count or control total when available, observed count, late-arrival count, last source update, watermark, provisional/final status and exception owner. Where no source control total exists, label completeness as estimated rather than proven.

Finance analyst and data quality lead reviewing warehouse reconciliation totals, late arrival counts and reporting exceptions

A practical warehouse launch should also test month-end boundaries, timezone conversions, backdated source updates and historical joins. Actiknow’s warehouse reconciliation framework provides a complementary approach to validating counts, amounts, dimensions and exceptions before a reporting cutover.

Late-arriving dimensions need a different treatment

Sometimes a transaction arrives before its customer or product record. The fact should not disappear from totals simply because the descriptive dimension is missing. A controlled unknown or inferred dimension member can temporarily preserve the transaction, then be resolved when the attributes arrive.

If a dimension attribute is corrected retroactively, a historical fact may need to be assigned to the dimension version effective on the transaction date. Joining every fact to the latest dimension row can make prior periods change unexpectedly. The Kimball late-arriving dimension guidance describes inferred members and retroactive Type 2 changes.

Do not solve missing dimension records by dropping unmatched facts in an inner join. That can understate revenue while leaving the pipeline successful. Track unresolved dimension keys, their financial exposure and their aging, and make the remediation process explicit.

Monitor completeness, not merely successful jobs

A reliable operating dashboard should show source-to-warehouse lag, business-event lateness, records arriving outside the standard window, unresolved keys, reprocessed dates, reconciliation variance, restatement count, and time from correction to BI publication. Segment alerts by severity and business impact.

A pipeline can complete successfully while delivering incomplete data. Pair operational health with freshness and business checks, as described in Actiknow’s data pipeline observability guide.

Alert on meaningful exceptions rather than every expected late event. For example, a known 90-minute settlement delay may be normal, while a source that usually finishes by 07:00 but remains incomplete at 10:00 should trigger a targeted notification to the data owner and affected report users.

An implementation roadmap for the first 30 days

Week 1: define the business promise

Select two or three high-impact KPIs. Identify their authoritative sources, business-date rules, timezones, current publication schedules, stakeholders and historical discrepancies. Collect examples of unexplained revisions. Agree what “final” means, who can authorize a restatement and which exceptions require communication.

Week 2: instrument and measure

Capture source event, update and ingestion timestamps in raw data. Profile lateness distributions and missing records. Add freshness and reconciliation checks at meaningful grains. Identify transformations that filter on event time, overwrite partial days or append corrections without deduplication.

Week 3: implement controlled reprocessing

Introduce stable-key merges or full-date replacement, a measured lookback, an out-of-window correction queue and an audit table. Test late inserts, updates, deletes, duplicates, source outages, month-end boundaries and repeated runs. Confirm that re-running the same input yields the same output.

Week 4: publish and govern

Add provisional/final labels, cutoff timestamps and material-restatement notes to the BI layer. Run the old and new reports in parallel, reconcile differences, and obtain business sign-off. Document support ownership, escalation paths and a monthly review of late-data patterns and compute cost.

Executive scorecard: is the reporting process trustworthy?

A useful scorecard asks six questions.

  • Can every critical KPI state its business date and source?
  • Is the data cutoff visible?
  • Are provisional periods clearly labeled?
  • Can the team explain changes to a previously published number?
  • Are late records captured without duplication?
  • Can finance reproduce the number as it was originally published?

If the answer to several questions is no, adding more dashboard visualizations will not solve the underlying trust problem. Prioritize a narrow end-to-end reliability improvement for the most consequential metrics before expanding reporting complexity.

Common mistakes to avoid

Do not freeze all history simply to prevent questions. That hides legitimate corrections. Do not rebuild unlimited history on every run without measuring cost. Do not assume a green ingestion job means a business day is complete. Do not use load date as the business date unless that is the approved metric definition.

Do not choose a lookback window based only on the maximum delay observed once; outliers, contractual deadlines and materiality matter. Do not automatically revise closed financial periods. Do not overwrite published KPI snapshots if users must later reproduce the original figures.

Frequently asked questions

What is the difference between event time and ingestion time?

Event time records when the business action happened; ingestion time records when the warehouse received it. They answer different questions and should usually be retained separately.

Why do yesterday’s dashboard numbers change?

Late records, corrections, deletes, changing dimension assignments, revised business rules and delayed BI refreshes can all alter historical values. A change log should distinguish the cause.

Can a watermark prevent late-arriving data?

No. A reliable watermark helps capture new source changes without skipping them, but it cannot guarantee that all events belonging to a past business period have arrived.

How large should the incremental lookback window be?

Choose it from observed lateness, reporting commitments, financial materiality and compute cost. Maintain a separate correction path for records arriving beyond the normal window.

Should we always restate historical revenue?

No. Follow the organization’s approved accounting and reporting policy. Some changes belong in a prior period; others must be posted as current-period adjustments or disclosed separately.

What does idempotent processing mean?

Reprocessing the same input produces the same target state without multiplying rows or amounts. Stable keys and controlled merges or replacements are common techniques.

How can finance see the original number?

Store versioned published KPI snapshots with business period, value, data cutoff and publication time, plus a record of subsequent approved restatements.

Are late-arriving dimensions the same as late facts?

No. Late facts are delayed transactions; late dimensions are delayed or retroactively corrected descriptive context. They need related but distinct resolution logic.

Does dbt automatically solve late data?

No. dbt supports incremental strategies and snapshots, but teams must design filters, keys, lookback windows, tests and historical correction policies correctly.

How do we know a day is final?

Use an agreed finalization rule supported by source completion signals, lateness evidence, reconciliation and business approval. A job finishing is not sufficient evidence by itself.

What if a source does not expose reliable updated_at values?

Capture warehouse arrival timestamps, use overlap or full-source comparisons where feasible, investigate CDC or source audit logs, and schedule periodic reconciliations.

Who owns the decision to change a published KPI?

Engineering owns reliable processing and evidence; the accountable business or finance owner should approve the reporting rule and material restatement decision.

Conclusion: explain change instead of pretending it cannot happen

Late-arriving data is manageable when teams separate business time from processing time, capture reliable source changes, recompute affected periods safely, preserve published versions and communicate material revisions. The result is not a dashboard whose numbers never move. It is a reporting system in which every movement has a defensible reason.

If your executive reports regularly change without a clear explanation, Actiknow can help assess the underlying data integration, warehouse modeling and BI publication process. Contact Actiknow to discuss a focused reporting reliability review.