Understanding Delays in Unified Transaction Tables Due to Ingestion Offsets

Overview

Customers may sometimes notice that Unified_Transactions and Unified_Itemized_Transactions tables appear to be missing the most recent days of data.
This can raise concerns about data freshness, campaign reporting, or downstream activations.

In many cases, this behavior is expected and is caused by intentional ingestion offsets in the data pipeline.

This article explains why these delays happen, how to verify them, and when action is needed.

Symptoms

You may observe one or more of the following:

  • Unified transaction tables appear to stop updating after a certain date

  • Recent transaction days seem missing or delayed

  • Source files show as successfully delivered and processed

  • Daily workflows complete without errors

  • Campaigns or downstream systems appear to lag behind real-world activity

Why This Happens

Amperity transaction ingestion often uses date offsets to ensure data completeness and reliability.

Common offset scenarios include:

  • Courier offset: The ingestion job is configured to process files from a prior day

  • File offset: Source systems generate files that already contain a date delay

  • Combined effect: When both offsets exist, data in unified tables may appear 1–2 days behind

This design helps prevent:

  • Partial-day files

  • Late-arriving transactions

  • Incomplete ingestion due to delayed file delivery

Even though the data looks delayed, ingestion is working as intended.

How to Validate Data Availability

To confirm whether data is truly missing or simply delayed:

  1. Check the latest transaction timestamp

    • Review the most recent order_datetime in unified transaction tables

  2. Compare source → domain → unified tables

    • Verify that data flows consistently across pipeline stages

  3. Review orchestration status

    • Ensure daily workflows are completing successfully without errors

  4. Account for configured offsets

    • Add courier and file offsets together to understand the expected delay

If the latest date aligns with the configured offsets, the data is considered healthy.

When Action Is Required

You may want to take action if:

  • The delay exceeds the expected offset window

  • Source files are present but not reflected after the offset period

  • Orchestrations show failures or partial runs

  • Downstream use cases require lower latency data

In these cases, contact Support to review:

  • Courier configuration

  • File naming and delivery timing

  • Pipeline dependencies

  • Downstream query logic

Configuration Considerations

Reducing or removing offsets can make data appear more current, but it may also:

  • Increase the risk of missing late files

  • Introduce partial transaction days

  • Affect reporting consistency

Any changes should be evaluated carefully based on business requirements.

Best Practices

  • Align expectations with configured ingestion offsets

  • Use offsets for high-volume or late-arriving transaction data

  • Monitor trends over several days, not a single run

  • Confirm data completeness before pausing or restarting campaigns

  • Document offsets for internal reporting teams

Summary

If Unified transaction tables appear delayed, it does not always indicate missing data.
In many cases, courier and file offsets are working as designed to ensure accurate and complete ingestion.

Understanding these offsets helps prevent unnecessary troubleshooting and ensures confidence in transaction data freshness.

Applies To

  • Unified_Transactions

  • Unified_Itemized_Transactions

  • Transaction ingestion pipelines

  • Courier configuration

  • Downstream reporting and activation workflows