Understanding BigQuery Partition Dates and Late-Arriving Events with Segment – Twilio Support

Objective

This article explains how Segment determines partition dates in Google BigQuery tables, why there may be gaps between event timestamps and ingestion dates, and best practices for querying late-arriving or backfilled events. It also clarifies the differences between the loaded_at, received_at, timestamp, and original_timestamp fields, and provides recommendations for analytics and audience use cases. This guidance is intended for users syncing event data from Segment to BigQuery and seeking clarity on data partitioning and querying strategies.

Product

Segment Storage Destinations

Environment

Segment Console

User Account Permission/Role(s) Required

Segment workspace member with access to warehouse destinations

Procedure

Understanding BigQuery Partitioning with Segment
- By default, Segment’s BigQuery integration partitions raw event tables by ingestion time, using the received_at field (mapped to BigQuery’s _PARTITIONTIME).
Why Gaps Occur Between Event Timestamps and Ingestion Dates
- Re-syncs or Backfills: Historical data replays or manual re-syncs can cause older events to be ingested on a new date.
- Offline Buffering: Mobile apps or SDKs may store events while offline and send them to Segment much later.
- Batch Deliveries: Some server processes deliver logs in batches, causing delays between event occurrence and ingestion.
Best Practices for Querying Events
- Filtering only by partition date (received_at/_PARTITIONTIME) can miss late-arriving or backfilled events.
- For regular operations, add a 3–7 day buffer to your partition filter to capture late-arriving data.
- If you observe gaps exceeding this buffer, consider removing or expanding the partition filter and rely on the timestamp field for your business logic.
- For most queries, use the received_at field for reliability and performance, but use the timestamp field when analyzing user behavior or building audience segments.
Can I Partition by Event Timestamp Instead?
- Segment does not currently support configuring BigQuery raw event tables to partition by the timestamp field. Partitioning is managed automatically by Segment and defaults to received_at for reliability.
Differences Between Timestamp Fields

Field	Description	How It Is Generated	Vulnerability / Reliability
`original_timestamp`	The raw timestamp recorded on the client device or server when the tracking call was originally invoked (or manually passed).	Recorded locally by the tracking SDK (e.g., mobile device or browser) at the moment of the event.	Unreliable. Subject to client clock skew, wrong device time settings, network delays, or offline queuing.
`timestamp`	The adjusted event time. Calculated by Segment to represent when the event actually occurred after correcting for client clock skew.	Calculated as: `timestamp = receivedAt - (sentAt - originalTimestamp)`	Best for event ordering. Adjusts for incorrect user device clocks while preserving true action time.
`received_at`	The exact UTC time when Segment’s API servers received the incoming event payload.	Timestamped directly on Segment’s ingestion server clock.	100% Server-reliable. Immune to client clock skew, but may lag behind `timestamp` if events were queued offline.
`loaded_at`	The time Segment loaded the record into your data warehouse table.	Timestamped by the warehouse loader pipeline when writing rows into SQL/Redshift/Snowflake/BigQuery.	ETL/Pipeline metadata. Represents batch processing time, not user action time.

Recommendations:

Create custom views in BigQuery: You can create custom views over your raw tables to group or filter data by event occurrence time. This allows you to tailor queries for your analytics and audience use cases without affecting the underlying warehouse syncs.
Consult Segment Professional Services: If you require a more customized solution or guidance on advanced data modeling, consider reaching out to Segment Professional Services for expert assistance.

Additional Information