Late-arriving data during a warehouse migration is handled by agreeing, per source, how late data may arrive, keeping a reprocessing window open on both old and new platforms, and reconciling by business date rather than load date. Most “mismatches” found in parallel runs are not migration errors but late records that landed on one platform before the other.
We at DWHPro lead migrations from Teradata to cloud platforms, drawing on more than twenty years of Teradata experience. Late-arriving data is one of the least glamorous topics in a migration and one of the most common reasons parallel runs fail to converge. It deserves a decision at programme level, not a workaround in a load job.
What “late-arriving” means in practice
Three situations cause most of the trouble:
- Late facts. A sale from Monday arrives on Wednesday, because a store system was offline or a partner delivers files with a delay.
- Early facts, late dimensions. The transaction arrives before the customer, product or location it refers to. Something must hold the fact until the reference data catches up.
- Back-dated corrections. Returns, cancellations and price corrections change figures for periods the business considered closed.
Your Teradata warehouse already handles all three, often through rules built up over many years and documented nowhere. That is the core risk: the rules exist, but only in the code and in the heads of a few people.
Why a migration makes it worse
During a migration you have two platforms, two load schedules and two views of “today”. Late data exposes every difference between them:
- Different cut-off times. If the old platform loads at 02:00 and the new one at 03:00, a record arriving at 02:30 lands on different days. Daily totals disagree, and both are correct.
- Different handling of unknown references. One platform parks orphan facts, the other assigns them to an “unknown” member. Totals match; drill-downs do not.
- Replays and duplicates. When a late file is resent or a pipeline replays after a failure, rows can arrive twice. Snowflake standard tables and BigQuery do not enforce primary keys, so the target accepts duplicates unless the pipeline prevents them.
- Missed deletes. Incremental loads that select changed rows by timestamp may not see deleted or cancelled records at all. Google documents this for its BigQuery Data Transfer Service for Teradata: incremental transfers do not sync rows deleted in Teradata.
- Closed periods. Finance closes a month on the old platform; a correction then arrives and is applied only on the new one.
How to manage it
1. Make the rules explicit before you migrate them
For each critical source, document how late data can arrive, how far back corrections may reach, and what happens to facts without matching reference data. This is analysis work on the existing Teradata workload, and it is where the migration either becomes predictable or does not.
2. Agree a lateness window per source
Ask the data owners: how many days back can a record still change? Three days for point-of-sale data and ninety for supplier invoices is a normal outcome. That window defines how far back both platforms must reprocess, and how far back reconciliation must look.
3. Reconcile by business date, with a settling period
Compare figures by the date the business event happened, not by when it was loaded, and only once the lateness window has passed. A daily total compared on the morning after will often differ; the same total compared after the window closes should not.
4. Align cut-offs during the parallel run
Where possible, feed both platforms from the same delivery at the same cut-off. If that is not possible, record which deliveries each platform has processed so differences can be explained in minutes rather than days.
5. Decide how replays and corrections are applied
Every pipeline needs a defined behaviour when the same record arrives again or a correction replaces an earlier version. On Teradata this logic is often embedded in UPDATE and DELETE patterns; see migrating deletes and updates from Teradata to Snowflake for what changes on the target.
6. Plan cutover around the lateness window
At cutover, late data for periods before the switch still arrives. Decide which platform receives it, and keep the old warehouse able to process it until the window has closed or the data has been reconciled across.
What to insist on
| Question for your team or partner | A good answer looks like |
|---|---|
| How late can data arrive, per source? | A documented window agreed with the data owner |
| How are duplicates from replays prevented? | A defined rule per table, tested with a deliberate replay |
| How are deletes and cancellations captured? | Named mechanism, plus periodic full comparison where incrementals miss them |
| When is a reconciliation difference a defect? | Only after the lateness window has passed and cut-offs are aligned |
| Who handles late data for pre-cutover periods? | A named platform and owner, written into the cutover plan |
Acceptance criteria and validation levels are covered in our article on proving a Teradata migration before cutover.
In our Swiss retail Teradata to Snowflake migration, inventory, sales and supply-chain reporting had to produce the same business results after the move. Source-to-target comparisons included row counts, aggregates and business-logic checks, and discrepancies were investigated before cutover rather than explained away afterwards. No data loss was reported across the migrated datasets. Details are on our case studies page.
How DWHPro helps
We analyse how your Teradata workload handles late and corrected data today, turn those rules into explicit requirements for the target, and set up reconciliation that separates real defects from timing differences. See our Teradata to Snowflake migration service.
Common questions
Why do our parallel-run totals never match exactly?
Usually because the platforms have processed different deliveries or use different cut-offs. Compare by business date after the lateness window has passed; remaining differences are worth investigating.
Should late data be fixed in the new platform’s design?
Yes, but deliberately. Migrate the existing rules first so results can be proven equal, then improve them as a separate, visible change.
Does real-time ingestion solve late-arriving data?
No. Faster pipelines move data sooner after it arrives, but they cannot make a late source deliver earlier. The rules for late and corrected records are still needed.
Planning a Teradata-to-Snowflake migration?
Review the assessment, conversion, reconciliation and cutover work needed for a controlled transition.