Timezone Handling in Databricks Tables

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

Each event in a fact table is associated with two time-based dimension keys:

These keys are what power all time-based analysis such as daily trend reports, time-of-day breakdowns, and chronological sequencing of customer journeys in Databricks.

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 used for that event is resolved from the till's location in the store hierarchy. Insights looks up the timezone starting at the till level and works upward until a timezone is found:

This means every till in Insights always resolves to a valid timezone. If a till does not have a timezone configured, the timezone is inherited 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

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 enabling

Timezone handling in Insights is a config-driven feature. It is not enabled by default and must be turned on per organisation. There are two configurations involved:

Organisation-Level Feature Flag

The master switch that enables timezone-aware processing for an organisation's data in Insights.

🚧

Important

Enabling this config applies to all new data going forward. 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. Please 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 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.