Timezone Handling in Databricks Tables

Insights stores all event timestamps in the server timezone (IST / Asia/Kolkata), regardless of the store or region where the event originated. This applies consistently across all fact tables in Databricks, such as transactions, points awards, redemptions, slab upgrades, and more.

Key concepts

Time dimension keys

Each event in a fact table is associated with two time-based dimension keys that power all time-based analysis. For example, daily trend reports, time-of-day breakdowns, and chronological sequencing of customer journeys.

Both fields always reflect server time (IST), making cross-store and cross-region comparisons reliable.

How timezone is determined for each event

Every event in Insights is associated with a till, the point-of-sale terminal where the event was recorded. The timezone for that event is resolved by starting at the till level and working upward through the store hierarchy until a timezone is found.

This means every till in Insights always resolves to a valid timezone. If a till has no timezone configured, it inherits from its parent store, then from the zone above it, and so on up to the organisation default.

What this means for your data

When an event is recorded, Insights converts the raw timestamp from the till's local timezone into the server timezone (IST) before storing the event date and time keys.

This ensures that:

• All events across stores in different geographies are recorded on a single, consistent timeline.
• Sequential events such as a points earn followed by a redemption — always appear in the correct chronological order**, even when they happen across stores in different timezones.
• The dim_event_date_id and dim_event_time_id values always reflect server time (IST), making cross-store and cross-region comparisons reliable.

Example: Cross-region transaction sequence

Scenario: A customer earns points at a store in India (IST, UTC+5:30) and redeems them 3 hours later at a store in Dubai (GST, UTC+4:00).

Both events are correctly ordered and reflect the true 3-hour gap between them.

Availability and configuration

Timezone handling in Insights is config-driven and not enabled by default. It must be turned on per organisation using two configurations.

Organisation-level feature flag

🚧

Important

Enabling this config applies to new data only. Existing historical data in Databricks is not automatically updated. If you need historical data to reflect the corrected timezone values, a full re-sync is required. Discuss this with your Capillary account team before enabling, especially if you sync Databricks data to an external data warehouse.

Source column timezone config

Not every fact table requires timezone conversion. Only the specific tables and columns where timestamps are stored in a local timezone rather than the server timezone. This is managed through a per-column configuration maintained by Capillary.

When onboarding a new data source, Capillary identifies which columns need timezone conversion and registers them in this configuration. You do not need to manage this directly.

Steps to enable

📘

Notes

• New organisations onboarded to Insights after this feature was introduced will have timezone handling configured from the start. A re-sync is only relevant for existing organisations enabling the feature on pre-existing data.
• In future, timezone handling will be extended to additional fact tables that do not currently have till_id directly available. These tables will be enriched with store and till information before timezone conversion is applied.