Time-Zone & Temporal Data Engineering: UTC, DST, Bitemporal Tables & Calendar Dimensions

Temporal data engineering is the unglamorous discipline of getting when right — and it breaks more production pipelines than any other single category of bug, because time looks like a number and behaves like a legal system. A timestamp is not just an integer on an axis: it can be a wall-clock reading that only means something paired with a place, an absolute instant that is the same everywhere, or a duration that has to survive a daylight-saving jump. The moment your data crosses a border — an order placed in Tokyo, aggregated in a warehouse in Virginia, and reported to a finance team on a fiscal calendar in London — every one of those meanings has to be pinned down, or your "daily revenue" quietly counts the same hour twice, your "sign-ups per day" shifts by a day for half the world, and your reconciliation report disagrees with itself depending on when someone ran it.

This guide is the senior walkthrough for the four hard problems underneath that: UTC and DST correctness — why you store an absolute instant and convert to a time zone only at the edge, and why the spring-forward gap and fall-back overlap wreck naive wall-clock arithmetic; bitemporal modelling — separating valid time (when a fact was true in the world) from transaction time (when your database learned it) so you can answer "what did we believe then" as well as "what is true now"; the calendar dimension — a precomputed date dimension carrying ISO weeks, fiscal periods, holidays, and business-day flags so reports join instead of re-deriving date math; and the temporal query patterns that tie them together — point-in-time as-of joins, slowly-changing-time windowing, and zone-aware backfills. Each section pairs a teaching block with a Solution-Tail interview answer — code across Postgres, Snowflake, and BigQuery, a step-by-step trace, an output table, then a concept-by-concept breakdown of why it works.

When you want hands-on reps immediately after reading, drill the date & time practice library →, rehearse rolling and windowed logic on the time-series practice library →, and sharpen the modelling axis with the system design practice library →.

---

On this page

• Why time is hard in temporal data engineering
• UTC & DST correctness — TIMESTAMPTZ vs TIMESTAMP
• Bitemporal tables — valid time vs transaction time
• Calendar dimensions — fiscal calendars, weeks, holidays
• Temporal patterns — as-of joins, SCD windows, backfills
• Cheat sheet — temporal data engineering
• Frequently asked questions
• Practice on PipeCode

---

1. Why time is hard in temporal data engineering

Store UTC, convert at the edges — the offset-versus-zone distinction is where pipelines break

The one-sentence invariant: the whole of temporal data engineering is deciding, for every timestamp, whether it is an instant (an absolute point on the universal timeline, stored in UTC), a wall-clock reading (a local civil time that only means something paired with a zone), or an interval/duration, and then storing instants in UTC, converting to a local zone only at the display edge, and never confusing a fixed offset like -05:00 with a zone like America/New_York — because a zone is a rule set with a whole history of daylight-saving transitions, and losing that rule is how the same query returns different answers across a DST boundary. Get the model right at ingestion and the rest is joins; get it wrong and every downstream aggregate inherits the error.

The three ways time enters a pipeline — and each needs a different type.

• Instant (absolute). The moment an event happened, identical everywhere on Earth. This is what you store — as UTC — for events, logs, and any "when did X occur." In Postgres it is TIMESTAMPTZ; in BigQuery it is TIMESTAMP.
• Wall-clock (civil / local). A calendar-and-clock reading like "2024-03-10 02:30 in Denver." It is meaningless without a zone, and some wall-clock readings do not exist or are ambiguous. Store these as naive types (TIMESTAMP / DATETIME) only when the value is genuinely a local intention (a scheduled 9am meeting) rather than an instant.
• Interval / duration. A length of time ("90 minutes"). Adding a duration to an instant is unambiguous; adding it to a wall-clock time across a DST boundary is not — "1 day after" and "24 hours after" can differ by an hour.

Offset versus zone — the distinction that causes silent corruption.

• An offset is a number. -05:00 says "five hours behind UTC" and nothing else. It cannot tell you what the offset will be next July, because it carries no rule.
• A zone is a rule set. America/New_York is -05:00 in winter and -04:00 in summer, and the dates those switches happen have changed over the decades. The IANA/tzdata database encodes that whole history.
• Storing an offset loses the rule. If you persist "2024-06-01 12:00-05:00" for New York, you have already recorded the wrong offset (it should be -04:00 in June) — and you can never recover the zone from the offset. Store the UTC instant plus the zone name when you need local rendering.

The two DST hazards every temporal system must survive.

• The spring-forward gap. When clocks jump 02:00 → 03:00, local times like 02:30 never existed. A naive "add 1 day" or a literal '2024-03-10 02:30' in the local zone is undefined behaviour.
• The fall-back overlap. When clocks fall 02:00 → 01:00, local times like 01:30 happen twice. Bucketing events by local hour double-counts the repeated hour unless you group by the UTC instant.
• The rule. Do arithmetic and grouping on UTC instants; project to local wall-clock only for display, and treat gap/overlap as first-class edge cases, not afterthoughts.

What interviewers listen for.

• Do you say store instants in UTC and convert at the edge unprompted? — senior signal.
• Do you distinguish an offset from a zone, and know that a zone carries a DST history? — required answer.
• Do you know TIMESTAMPTZ is a UTC instant and TIMESTAMP is a naive wall clock, not "with vs without a stored zone"? — senior signal.
• Do you reach for a bitemporal model when asked about corrections and audit, and a date dimension when asked about fiscal/holiday reporting? — senior signal.

Worked example — the store-UTC-convert-at-the-edge decision

Detailed explanation. The single most useful reflex in temporal engineering is a fixed pipeline shape: convert to a UTC instant at ingestion, store UTC, do all math and grouping in UTC, and convert to a local zone only when a human reads it. Walk through classifying the timestamps in a global orders pipeline and deciding the storage type for each.

• The inputs. An event instant (order placed), a user's intended local time (a scheduled delivery window), and a duration (SLA to fulfil).
• The tension. Store everything local and cross-zone aggregates are wrong; store everything UTC and you lose a genuinely-local intention.
• The rule. Instants → UTC; genuine wall-clock intentions → naive local + a zone column; durations → intervals.

Question. For each field, name the correct storage type and where the zone conversion happens.

Input.

Field	Meaning	Storage type	Convert where
placed_at	instant the order was placed	UTC instant (TIMESTAMPTZ)	display only
delivery_local	customer's intended local window	naive (TIMESTAMP) + zone	at scheduling
sla	time allowed to fulfil	interval / seconds	never
order_date	business day for reporting	DATE derived in a chosen zone	at rollup

Code.

-- Canonical temporal table: store the INSTANT in UTC, keep the zone for rendering,
-- store genuine local intentions as naive + zone, durations as intervals.
CREATE TABLE orders (
    order_id       bigint PRIMARY KEY,
    placed_at      timestamptz  NOT NULL,           -- absolute instant, stored as UTC
    origin_zone    text         NOT NULL,           -- IANA name, e.g. 'Asia/Tokyo'
    delivery_local timestamp,                        -- a WALL-CLOCK intention (no zone baked in)
    delivery_zone  text,                             -- the zone that intention is in
    fulfil_sla     interval     NOT NULL DEFAULT '48 hours'
);

-- The business "order date" is DERIVED at query time in a chosen reporting zone,
-- never stored as a naive guess:
SELECT order_id,
       (placed_at AT TIME ZONE 'UTC')::date                     AS utc_date,
       (placed_at AT TIME ZONE 'America/New_York')::date        AS ny_business_date
FROM orders;

Step-by-step explanation.

1. placed_at timestamptz stores the order instant. Postgres converts whatever the client sends into UTC on the way in, so two orders placed at the same real moment in Tokyo and New York compare equal — the property every cross-zone aggregate depends on.
2. origin_zone is stored as an IANA name, not an offset, so you can always re-render the instant in the origin's local time with the correct DST rule for that date — impossible if you had stored only +09:00.
3. delivery_local is a genuine wall-clock intention ("deliver at 9am their time"), so it is a naive timestamp paired with delivery_zone. Storing it as UTC would freeze a 9am into whatever offset was current when the row was written and drift across the next DST switch.
4. fulfil_sla is an interval — a duration — because "within 48 hours" is instant arithmetic (placed_at + fulfil_sla) that is unambiguous, whereas "2 days later, same wall time" would need calendar arithmetic in the delivery zone.
5. The business order_date is derived, not stored: the same instant yields utc_date and ny_business_date that can differ by a day. Choosing the reporting zone at rollup — instead of guessing it at ingestion — is what keeps "orders per day" consistent for every consumer.

Output.

Timestamp kind	Store as	Reason
Event instant	UTC timestamptz	comparable across zones
Local intention	naive + zone name	survives DST, keeps intent
Duration	interval	unambiguous instant math
Reporting day	derived in a chosen zone	consistent rollups

Rule of thumb. Convert to a UTC instant at ingestion, store UTC plus the zone name, and project to local wall-clock only at the display or rollup edge. Store a value as naive-local only when it is a genuine wall-clock intention, and store durations as intervals so instant arithmetic stays unambiguous.

Worked example — why an offset column is a latent bug

Detailed explanation. A tempting shortcut is to store a timestamp with its numeric offset (2024-06-01 12:00-05:00) and call it "timezone-aware." It is not: an offset is a frozen number, and the moment a DST transition changes the true offset, the stored value is wrong and unrecoverable. Contrast an offset column with a UTC-instant-plus-zone design.

• The offset design. One column holding local time + a numeric offset captured at write time.
• The bug. The offset is only correct for the instant it was captured; it cannot answer "what is New York's offset next winter."
• The fix. Store the UTC instant and the zone name; compute the offset on demand from tzdata.

Question. Show how an offset-only design returns the wrong local time across a DST boundary, and how the instant-plus-zone design stays correct.

Input.

Design	Stored	Can recompute future/past local time?
Offset column	12:00-05:00	no — offset is frozen
Instant + zone	17:00Z + America/New_York	yes — from tzdata
Naive only	12:00	no — zone unknown
UTC only (no zone)	17:00Z	instant yes, local no

Code.

-- WRONG: an offset frozen at write time. In June, New York is -04:00, not -05:00,
-- so this row already encodes the wrong local wall clock and can never be fixed.
-- (text illustrates the trap; the value is internally just an instant.)
SELECT TIMESTAMP WITH TIME ZONE '2024-06-01 12:00:00-05:00' AS frozen_offset;
--   -> 2024-06-01 17:00:00+00  (an instant of 17:00Z, but 12:00 -05 was never NY-in-June)

-- RIGHT: store the true UTC instant + the ZONE NAME, derive local time with the rule.
WITH e AS (
  SELECT TIMESTAMPTZ '2024-06-01 16:00:00+00' AS occurred_at,   -- the real instant
         'America/New_York'::text             AS zone
)
SELECT occurred_at,
       occurred_at AT TIME ZONE zone                    AS local_wall_clock, -- 12:00 (‑04 applied)
       to_char(occurred_at AT TIME ZONE zone, 'TZ')     AS note              -- correct DST rule
FROM e;

Step-by-step explanation.

1. The first query pins an offset of -05:00 onto a June New York time. New York in June is actually -04:00 (EDT), so the literal 12:00-05:00 corresponds to 17:00Z — an instant that does not represent noon in New York at all. The mistake is baked in permanently.
2. Because an offset carries no rule, there is no function that can take -05:00 and tell you "but in June it should have been -04:00" — you have thrown away the zone, keeping only a snapshot of it.
3. The instant-plus-zone design stores occurred_at as a true UTC instant (16:00Z) and the zone name separately, so all the DST history in tzdata is available at query time.
4. occurred_at AT TIME ZONE 'America/New_York' applies the correct rule for that date — -04:00 in June — and returns 12:00, the actual New York wall clock. Ask for a December instant and the same expression applies -05:00 automatically.
5. The lesson: a zone is data plus rules, an offset is only data. Persist the instant and the zone name; treat the offset as a derived value you compute when rendering, never as a thing you store and trust.

Output.

Query	Result	Correct?
'2024-06-01 12:00-05:00'	instant 17:00Z	wrong intent (NY June is -04)
16:00Z AT TIME ZONE 'America/New_York'	12:00 local	yes (rule applied)
December instant, same zone expr	-05:00 applied	yes (auto)
offset re-derived from tzdata	correct per date	yes

Rule of thumb. Never persist a timestamp as "local time plus a frozen offset." Store the UTC instant and the IANA zone name, and compute the offset from the zone whenever you render — the zone carries the DST rules that an offset silently discards.

Worked example — the senior "why is time hard" monologue

Detailed explanation. Temporal interviews often open loosely ("we have timestamps from all over the world — how do you store them?") and escalate toward DST, corrections, and fiscal reporting. The candidates who name the UTC invariant, offset-vs-zone, DST edge cases, bitemporal history, and the date dimension before being asked score highest.

• Ambiguous opener. "Events come in from every region. How do you store the time?"
• Follow-up 1. "A report shows the same hour twice one night in November. Why?" — probes DST overlap.
• Follow-up 2. "Finance says a past number changed. How do you explain both values?" — probes bitemporal.
• Follow-up 3. "They want it by fiscal week. How?" — probes the calendar dimension.

Question. Draft a 5-minute senior temporal answer that pre-empts all four follow-ups.

Input.

Signal	Weak answer	Senior answer
Storage	"store local time"	"store the UTC instant + zone name"
DST overlap	"off-by-one, weird"	"group by UTC; local hour repeats in fall-back"
Corrections	"update the row"	"bitemporal: valid time + transaction time"
Fiscal reporting	"compute in the query"	"join a date dimension with fiscal columns"

Code.

Senior temporal answer template (5 minutes)
============================================

Minute 1 — the storage invariant
  "Every event is stored as a UTC instant (TIMESTAMPTZ / BigQuery TIMESTAMP)
   plus the origin zone NAME. I convert to local only at the display edge.
   An offset is not a zone — a zone carries the DST rules."

Minute 2 — DST correctness
  "Arithmetic and grouping happen on UTC instants. The fall-back overlap makes
   a local hour occur twice and the spring-forward gap makes one not exist, so
   'group by local hour' double-counts or drops rows — I bucket by UTC and
   render local only for humans."

Minute 3 — history and corrections
  "When a past fact changes, I don't overwrite. A bitemporal table keeps valid
   time (when it was true) separate from transaction time (when we knew it), so
   I can answer both 'what is true now' and 'what did we believe last month'."

Minute 4 — calendar / fiscal reporting
  "Fiscal weeks, holidays, and business days live in a date dimension — one row
   per day with ISO week, fiscal 4-4-5 period, and holiday flags — so reports
   JOIN the calendar instead of re-deriving fragile date math per query."

Minute 5 — the patterns that tie it together
  "Facts attach to the dimension version valid at their instant via an as-of
   join; slowly changing attributes are windowed with LEAD; and backfills
   reprocess by UTC partition using the correct historical offset."

Step-by-step explanation.

1. Minute 1 states the invariant — UTC instant plus zone name, convert at the edge — which frames every later answer and signals you have run a global pipeline, not a single-region app.
2. Minute 2 pre-empts the DST follow-up by naming the overlap and gap explicitly and giving the fix (group by UTC), which is the single most common temporal bug in analytics.
3. Minute 3 reaches for bitemporal before the corrections question fully lands, showing you separate "true in the world" from "known to the system" — the vocabulary that distinguishes a data engineer from an app developer.
4. Minute 4 answers fiscal reporting with a date dimension rather than ad-hoc EXTRACT math, which is what makes fiscal calendars, holidays, and business-day SLAs tractable at scale.
5. Minute 5 closes on the patterns (as-of join, SCD windowing, zone-aware backfill) that connect the pieces, so the interviewer sees an end-to-end mental model rather than four disconnected tricks.

Output.

Grading criterion	Weak score	Senior score
Names the UTC invariant	rare	mandatory
Explains DST gap/overlap	rare	senior signal
Reaches for bitemporal	rare	senior signal
Uses a date dimension for fiscal	occasional	mandatory
Ties it together with patterns	rare	senior signal

Rule of thumb. The senior temporal answer is a 5-minute monologue: store UTC instants plus zone names, do math in UTC because of DST, model corrections bitemporally, report fiscal/holiday logic through a date dimension, and connect them with as-of joins and zone-aware backfills. Rehearse it once; it pre-empts every follow-up.

Senior interview question on the temporal contract for a global pipeline

A senior interviewer often opens with: "Events arrive from every region of the world into your warehouse, and downstream you need consistent daily and fiscal reporting, the ability to explain why a historical number changed, and correct behaviour across daylight-saving transitions. Define the temporal contract: how you store timestamps, how you avoid DST double-counting, how you model corrections, and how fiscal reporting works — and why each choice is unavoidable rather than a preference."

Solution Using UTC instants, a bitemporal store, a date dimension, and UTC-based rollups

-- 1. Ingestion contract: store the UTC INSTANT + the origin ZONE NAME (never an offset).
CREATE TABLE stg_events (
    event_id     bigint PRIMARY KEY,
    occurred_at  timestamptz NOT NULL,          -- UTC instant (comparable across zones)
    origin_zone  text        NOT NULL           -- IANA name, keeps the DST rule
);

-- 2. Rollups group by the UTC instant, then project to a chosen reporting zone —
--    so the fall-back overlap can never double-count a repeated local hour.
CREATE VIEW daily_events AS
SELECT (occurred_at AT TIME ZONE 'America/New_York')::date AS business_date,
       count(*) AS events
FROM stg_events
GROUP BY 1;                                       -- date derived from the instant, not stored

-- 3. History that survives corrections: a BITEMPORAL fact keeps valid + transaction time.
CREATE TABLE fct_metric_bt (
    metric_key   text,
    value_num    numeric,
    valid_from   date        NOT NULL,           -- when the fact was TRUE in the world
    valid_to     date        NOT NULL DEFAULT 'infinity',
    tx_from      timestamptz NOT NULL DEFAULT now(),  -- when we KNEW it
    tx_to        timestamptz NOT NULL DEFAULT 'infinity'
);
-- "what is true now"      -> WHERE tx_to = 'infinity' AND valid_from <= d AND valid_to > d
-- "what we believed at S" -> WHERE tx_from <= S AND tx_to > S AND valid_from <= d AND valid_to > d

-- 4. Fiscal/holiday reporting JOINS a date dimension instead of re-deriving date math.
CREATE TABLE dim_date (
    date_key      int  PRIMARY KEY,              -- yyyymmdd surrogate key
    full_date     date NOT NULL,
    iso_year      int, iso_week int,
    fiscal_qtr    text,                           -- 4-4-5 period label
    is_business_day boolean
);
SELECT d.fiscal_qtr, count(*)
FROM daily_events e
JOIN dim_date d ON d.date_key = to_char(e.business_date,'YYYYMMDD')::int
GROUP BY 1;

Step-by-step trace.

Decision	Naive design	Temporal contract
Timestamp storage	local time / offset	UTC instant + zone name
Daily rollup	group by local hour	group by UTC, project to zone
DST overlap	hour counted twice	impossible (bucket by instant)
Corrections	overwrite the row	bitemporal close-and-insert
Fiscal reporting	EXTRACT per query	join dim_date fiscal columns
Audit	none	full via transaction time

After the rollout, every event lands as a UTC occurred_at plus an IANA origin_zone; daily and fiscal rollups group by the instant and project to a chosen reporting zone, so a repeated November local hour can never be double-counted; historical corrections close the old transaction-time interval and insert a new row, so both "true now" and "believed then" are answerable; and fiscal, ISO-week, and business-day logic is a join to dim_date rather than fragile per-query date arithmetic. The naive design's four latent bugs — offset drift, DST double-count, lost audit, and re-derived fiscal math — are structurally eliminated.

Output:

Metric	Naive design	Temporal contract
Cross-zone comparability	broken (offsets)	exact (UTC instants)
DST double-count risk	present	zero (group by UTC)
Explain a changed number	impossible	as-of query on tx time
Fiscal-week reporting	ad-hoc, error-prone	one dimension join
Audit trail	none	complete (transaction time)

Why this works — concept by concept:

• UTC instant plus zone name — storing the absolute instant makes every cross-zone aggregate comparable, and keeping the IANA zone name (not an offset) preserves the DST rules needed to re-render local time correctly for any date.
• Group by UTC, project at the edge — doing rollups on the instant and converting to a reporting zone only at the end means the fall-back overlap and spring-forward gap can never corrupt a count, because the repeated or missing local hour maps to a single unambiguous UTC bucket.
• Bitemporal valid + transaction time — separating when a fact was true from when the database knew it lets a correction be recorded as history rather than an overwrite, so "what is true now" and "what did we believe then" are both first-class queries with a full audit.
• Date dimension for fiscal/holiday logic — precomputing ISO weeks, fiscal 4-4-5 periods, holidays, and business-day flags once and joining them replaces brittle, inconsistent per-query date arithmetic with a single governed source of calendar truth.
• Cost — one instant column plus a zone name, a bitemporal close-and-insert per correction, and one dimension join per report, versus a lifetime of offset-drift bugs, DST double-counts, and un-explainable number changes. The eliminated cost is the class of silent temporal corruption itself — O(1) correct-by-construction storage instead of O(bugs) discovered in production.

Date & time
Topic — date-time
Date & time problems on UTC storage and zone conversion

Practice →

Design Topic — design Design problems on temporal contracts and time modelling

Practice →

---

2. UTC & DST correctness — TIMESTAMPTZ vs TIMESTAMP

TIMESTAMPTZ stores an instant; TIMESTAMP stores a wall clock — pick the right one and convert at the boundary

The mental model in one line: the most misunderstood fact in temporal data engineering is that TIMESTAMPTZ does not store a time zone — in Postgres it stores a normalised UTC instant and merely converts on input/output using the session zone, while TIMESTAMP (without time zone) stores a naive wall clock with no zone at all — so the correct type depends on whether the value is an instant (use TIMESTAMPTZ) or a local intention (use TIMESTAMP plus a separate zone), and AT TIME ZONE is the one operator that moves between them: applied to a TIMESTAMPTZ it yields the local wall clock in a zone, and applied to a naive TIMESTAMP it interprets that wall clock as being in a zone and yields the instant — with the DST gap and overlap as the two edge cases that make naive local arithmetic unsafe. Every warehouse has its own spelling of this, but the semantics are the same everywhere.

TIMESTAMPTZ vs TIMESTAMP — what each actually stores.

• TIMESTAMPTZ = an instant. Postgres reads the input, converts it to UTC using the client's zone, and stores UTC. On output it renders in the session TimeZone. No zone is kept per row — the name "with time zone" is historically misleading.
• TIMESTAMP = a naive wall clock. No conversion happens on input or output; it is exactly the calendar-and-clock digits you gave it. Comparing two of them from different zones is meaningless.
• The choice rule. If the value answers "when did this happen," use TIMESTAMPTZ. If it answers "what local time did someone intend," use TIMESTAMP and store the zone alongside.
• DATE and TIME. A DATE is zone-free by definition; deriving it from an instant requires choosing a zone first (instant AT TIME ZONE z)::date).

AT TIME ZONE — one operator, two directions.

• Instant → local wall clock. ts_tz AT TIME ZONE 'America/New_York' returns a naive TIMESTAMP: the local reading of that instant in New York, correct DST rule applied.
• Wall clock → instant. ts_naive AT TIME ZONE 'America/New_York' returns a TIMESTAMPTZ: it interprets the naive value as New York local time and produces the absolute instant.
• The mnemonic. AT TIME ZONE removes zone info from a tz value and adds it to a naive value — it flips the type each time.
• Chaining. To re-zone an instant from New York rendering to Tokyo rendering you go through the instant, never through offsets.

Snowflake and BigQuery equivalents.

• Snowflake. TIMESTAMP_NTZ is the naive wall clock; TIMESTAMP_TZ stores the instant with an offset; TIMESTAMP_LTZ stores the instant and renders in the session zone. CONVERT_TIMEZONE(target, ts) and CONVERT_TIMEZONE(source, target, ntz) move between them.
• BigQuery. TIMESTAMP is the absolute instant (UTC-based); DATETIME is the naive civil time; DATE/TIME are the components. TIMESTAMP(datetime, zone) and DATETIME(timestamp, zone) convert, and TIMESTAMP_TRUNC(ts, DAY, zone) buckets by local day.
• The common core. Every engine has an instant type, a naive type, and a convert-with-zone function — learn the triple per engine and the semantics transfer.

The DST edge cases senior engineers pre-empt.

• Fall-back overlap double-count. Grouping events by local hour counts the repeated 01:00–02:00 twice. Mitigation: group by the UTC instant (date_trunc('hour', occurred_at) on the timestamptz), project to local only for labels.
• Spring-forward gap. A literal local time in the missing hour is undefined; different engines error or shift. Mitigation: never construct timestamps from naive local values in the gap; work forward from instants.
• Non-24-hour days. A "local day" is 23 or 25 hours long on transition days. Mitigation: define daily buckets by the instant range that maps to the local day, not by adding 24 hours.

Common interview probes on UTC and DST.

• "Does TIMESTAMPTZ store a time zone?" — no; it stores a UTC instant and converts on I/O.
• "How do you convert an instant to a local day?" — (instant AT TIME ZONE zone)::date.
• "Why did a nightly count double one hour in November?" — grouped by local hour across the fall-back overlap; group by UTC.
• "Snowflake type for a wall-clock with no zone?" — TIMESTAMP_NTZ.

Worked example — TIMESTAMPTZ vs TIMESTAMP divergence in Postgres

Detailed explanation. The fastest way to internalise the distinction is to insert the same literal into both a TIMESTAMPTZ and a TIMESTAMP column under a non-UTC session and watch them diverge. Do it with a +09:00 literal under a New York session.

• The setup. SET TimeZone = 'America/New_York'; insert '2024-06-01 12:00:00+09:00' into both types.
• The tz column. Normalises to a UTC instant and renders in the session zone.
• The naive column. Keeps the digits — but which digits depends on how the literal was parsed.

Question. Show what each column stores and renders, and why only the TIMESTAMPTZ value is safe to compare across zones.

Input.

Column type	Input literal	Stored meaning
timestamptz	2024-06-01 12:00:00+09:00	instant 03:00Z
timestamp	2024-06-01 12:00:00+09:00	naive 12:00 (offset ignored)
session zone	America/New_York	affects tz rendering only
comparison	across zones	tz-column only

Code.

SET TimeZone = 'America/New_York';

CREATE TEMP TABLE t (id int, tz timestamptz, naive timestamp);
INSERT INTO t VALUES (1, '2024-06-01 12:00:00+09:00', '2024-06-01 12:00:00+09:00');

SELECT
  tz,                                   -- rendered in the SESSION zone (New York)
  tz AT TIME ZONE 'UTC'   AS tz_as_utc, -- the underlying instant
  naive                                 -- the naive column: offset was discarded on input
FROM t;

--        tz              |      tz_as_utc      |        naive
-- -----------------------+---------------------+---------------------
--  2024-05-31 23:00:00-04 | 2024-06-01 03:00:00 | 2024-06-01 12:00:00

Step-by-step explanation.

1. The timestamptz input 12:00+09:00 is an instant of 03:00Z. Because the session zone is New York (-04:00 in June), Postgres renders it as 2024-05-31 23:00:00-04 — a different wall clock, the same instant. Storage is UTC; display is session-zoned.
2. tz AT TIME ZONE 'UTC' peels the rendering away and shows the stored instant, 03:00Z — proving the column holds an absolute point, not a local reading.
3. The timestamp (naive) column discards the offset on input and keeps 12:00. It does not represent an instant at all; it is just the digits, now unmoored from +09:00.
4. Only the timestamptz value is safe to compare or aggregate across zones, because only it is normalised to UTC. Two events at the same real moment in different zones land on the same timestamptz and different naive values.
5. The practical takeaway: pick timestamptz for events, and be aware that rendering depends on the session TimeZone — pin the session zone (or convert explicitly with AT TIME ZONE) in reporting so output is deterministic.

Output.

Expression	Value	Meaning
tz (NY session)	2024-05-31 23:00-04	instant, local render
tz AT TIME ZONE 'UTC'	2024-06-01 03:00	the instant itself
naive	2024-06-01 12:00	digits, offset dropped
cross-zone compare	tz-column only	naive is unsafe

Rule of thumb. Use TIMESTAMPTZ for anything that is an instant and remember it stores UTC and renders in the session zone, so pin or convert the zone explicitly in reports. Use naive TIMESTAMP only for genuine wall-clock intentions, and never compare naive timestamps that came from different zones.

Worked example — convert at the edge across Postgres, Snowflake, and BigQuery

Detailed explanation. The same "instant → local business day" conversion recurs in every engine; knowing the three spellings makes you portable. Convert a UTC instant to a local calendar date and to a local-day bucket in Postgres, Snowflake, and BigQuery.

• The task. Given a UTC instant, get the New York business date and truncate to the local day.
• Postgres. AT TIME ZONE then cast/date_trunc.
• Snowflake / BigQuery. CONVERT_TIMEZONE / DATETIME and TIMESTAMP_TRUNC(..., zone).

Question. Write the instant-to-local-day conversion in all three engines and note where the zone is applied.

Input.

Engine	Instant type	Convert function	Local-day bucket
Postgres	timestamptz	AT TIME ZONE z	date_trunc('day', ts AT TIME ZONE z)
Snowflake	TIMESTAMP_TZ/LTZ	CONVERT_TIMEZONE(z, ts)	DATE_TRUNC('day', CONVERT_TIMEZONE(z, ts))
BigQuery	TIMESTAMP	DATETIME(ts, z)	TIMESTAMP_TRUNC(ts, DAY, z)
all	UTC in, local out	zone applied once	at the edge

Code.

-- Postgres: AT TIME ZONE turns the instant into a naive local wall clock.
SELECT
  (occurred_at AT TIME ZONE 'America/New_York')::date              AS ny_date,
  date_trunc('day', occurred_at AT TIME ZONE 'America/New_York')   AS ny_day_start
FROM stg_events;

-- Snowflake: CONVERT_TIMEZONE(target, instant) renders the instant in the target zone.
SELECT
  TO_DATE(CONVERT_TIMEZONE('America/New_York', occurred_at))            AS ny_date,
  DATE_TRUNC('day', CONVERT_TIMEZONE('America/New_York', occurred_at))  AS ny_day_start
FROM stg_events;

-- BigQuery: TIMESTAMP is a UTC instant; pass the zone to the truncation/extraction.
SELECT
  DATE(occurred_at, 'America/New_York')                    AS ny_date,
  TIMESTAMP_TRUNC(occurred_at, DAY, 'America/New_York')    AS ny_day_start
FROM stg_events;

Step-by-step explanation.

1. In Postgres, occurred_at AT TIME ZONE 'America/New_York' converts the stored UTC instant into the naive local wall clock, and casting to ::date gives the New York calendar date — the zone is applied exactly once, at this edge.
2. Snowflake's CONVERT_TIMEZONE('America/New_York', occurred_at) takes an instant (TIMESTAMP_TZ/LTZ) and returns the local rendering; TO_DATE/DATE_TRUNC then extract the day. The two-argument form is the instant-to-zone direction.
3. BigQuery keeps the instant as TIMESTAMP and passes the zone into DATE(...) and TIMESTAMP_TRUNC(..., DAY, zone), so the truncation happens in local time even though the value is stored in UTC.
4. In all three, the zone appears exactly once and only at the conversion edge; the stored column stays a UTC instant. This is the "convert at the edge" invariant made concrete per engine.
5. The portability lesson: memorise the instant type, the convert-with-zone function, and the truncate-with-zone function for each engine, and the same logical pipeline transfers without re-reasoning about semantics.

Output.

Engine	ny_date	Zone applied at
Postgres	AT TIME ZONE → ::date	the cast edge
Snowflake	CONVERT_TIMEZONE → TO_DATE	the convert edge
BigQuery	DATE(ts, zone)	the extract edge
all	same local date	once, at the edge

Rule of thumb. Learn the per-engine triple — instant type, convert-with-zone function, truncate-with-zone function — and always apply the zone once, at the conversion edge, over a stored UTC instant. The logical pipeline (store UTC, convert late) is identical across Postgres, Snowflake, and BigQuery; only the spelling changes.

Worked example — local-day bucketing that survives the DST fall-back

Detailed explanation. The classic November incident: a nightly "events per local hour" report shows 25 hours and double-counts one of them. It happens because grouping by local hour maps the repeated fall-back hour onto one bucket. Build a bucketing that groups by the UTC instant and only labels in local time.

• The bug. GROUP BY date_trunc('hour', ts AT TIME ZONE zone) collapses the two 01:xx local hours.
• The fix. Group by date_trunc('hour', ts) on the instant; derive the local label separately.
• The transition. New York fell back 2024-11-03: 01:59 EDT → 01:00 EST.

Question. Produce an hourly count over the fall-back night that keeps the two 1am hours distinct.

Input.

Approach	Group key	Fall-back hour
Group by local hour	local wall clock	merged (double-count)
Group by UTC instant	UTC hour	two distinct buckets
Label	local render of UTC bucket	shows 01 EDT & 01 EST
Result	25 correct buckets	no double-count

Code.

-- WRONG: buckets by LOCAL hour → the two 01:xx hours on 2024-11-03 merge into one.
SELECT date_trunc('hour', occurred_at AT TIME ZONE 'America/New_York') AS local_hour,
       count(*)
FROM stg_events
GROUP BY 1 ORDER BY 1;   -- 24 rows: the repeated 01:00 hour is double-counted

-- RIGHT: bucket by the UTC INSTANT, then attach a local label for display only.
SELECT
  date_trunc('hour', occurred_at)                               AS utc_hour,     -- the true bucket
  date_trunc('hour', occurred_at) AT TIME ZONE 'America/New_York' AS local_label, -- 01 EDT vs 01 EST
  count(*)
FROM stg_events
WHERE occurred_at >= '2024-11-03 04:00:00+00'   -- covers the NY fall-back window
  AND occurred_at <  '2024-11-04 05:00:00+00'
GROUP BY 1 ORDER BY 1;   -- 25 distinct hourly buckets, none double-counted

Step-by-step explanation.

1. The wrong query groups on occurred_at AT TIME ZONE 'America/New_York' truncated to the hour. Both the 01:xx EDT and 01:xx EST readings render as local 01:00, so their events land in one bucket — the double-count.
2. The correct query groups on date_trunc('hour', occurred_at) over the instant. The two 1am local hours are 05:00Z and 06:00Z — two different UTC hours — so they stay separate.
3. The local label is derived from the UTC bucket (... AT TIME ZONE 'America/New_York') purely for display, so the report can show "01:00 EDT" and "01:00 EST" as two rows without letting the label drive the grouping.
4. Because the fall-back day has 25 hours, the correct output has 25 hourly rows; the naive local grouping has 24 and silently folds one hour's traffic into another — exactly the kind of error that survives code review because the total still "looks plausible."
5. The same principle covers spring-forward: that local day has 23 hours, and grouping by the instant simply produces 23 buckets with no missing-hour math, whereas naive local arithmetic would try to bucket an hour that never existed.

Output.

Night	Local-hour grouping	UTC-instant grouping
Normal day	24 rows	24 rows
Fall-back (Nov)	24 rows (double-count)	25 rows (correct)
Spring-forward (Mar)	24 rows (phantom hour)	23 rows (correct)
Double-count risk	present	none

Rule of thumb. Bucket time series by the UTC instant (date_trunc on the timestamptz) and derive the local label from the bucket only for display. Grouping by local wall-clock hours double-counts the fall-back overlap and invents a spring-forward hour — group by the instant and the 23- and 25-hour days take care of themselves.

Senior interview question on UTC storage and DST-safe daily metrics

A senior interviewer might ask: "Your global product reports daily active users by each user's local day, and on the November DST night the numbers look wrong. Design the storage and the query: what type each timestamp is, how you convert to a local business day, how you avoid double-counting the fall-back hour, and how the same logic ports from Postgres to Snowflake or BigQuery."

Solution Using TIMESTAMPTZ storage, edge conversion, and UTC-instant bucketing

-- 1. Storage: the event instant is a UTC timestamptz; the user's zone is a name.
CREATE TABLE fct_activity (
    user_id     bigint,
    occurred_at timestamptz NOT NULL,           -- UTC instant
    user_zone   text        NOT NULL            -- IANA name, e.g. 'America/New_York'
);

-- 2. Daily active users by each user's LOCAL day: convert at the edge, per row's zone.
SELECT
  (occurred_at AT TIME ZONE user_zone)::date AS local_day,   -- zone applied once, at the edge
  count(DISTINCT user_id)                     AS dau
FROM fct_activity
GROUP BY 1
ORDER BY 1;
-- The DATE is derived from the instant in the user's own zone, so a user in Tokyo and a
-- user in New York are each attributed to THEIR local day — never a single global day.

-- 3. Hourly detail that survives the fall-back overlap: bucket by the UTC INSTANT.
SELECT date_trunc('hour', occurred_at)                                  AS utc_hour,
       date_trunc('hour', occurred_at) AT TIME ZONE 'America/New_York'  AS local_label,
       count(*)                                                          AS events
FROM fct_activity
GROUP BY 1 ORDER BY 1;      -- 25 buckets on the fall-back night, no double-count

-- 4. Same logic, other engines (portability):
--    Snowflake: TO_DATE(CONVERT_TIMEZONE(user_zone, occurred_at))
--    BigQuery : DATE(occurred_at, user_zone)   /   TIMESTAMP_TRUNC(occurred_at, HOUR, zone)

Step-by-step trace.

Layer	Component	Purpose
Storage	timestamptz + zone name	comparable instant, keep DST rule
Local day	AT TIME ZONE user_zone → ::date	per-user local attribution
Distinct users	count(DISTINCT user_id)	DAU per local day
Hourly detail	date_trunc('hour', occurred_at)	bucket by instant
DST safety	group by UTC, label local	no overlap double-count
Portability	CONVERT_TIMEZONE / DATE(ts, z)	same logic per engine

After deployment, each activity row stores a UTC occurred_at and the user's IANA user_zone; DAU is computed by converting the instant to that user's local date at the edge, so global users are attributed to their own days rather than a single arbitrary one; and hourly detail buckets by the UTC instant so the fall-back night correctly shows 25 distinct hours with local labels. The identical pipeline runs on Snowflake and BigQuery by swapping only the conversion function, and the November anomaly disappears because no grouping is ever done on ambiguous local hours.

Output:

Metric	Naive (local grouping)	UTC-instant design
DAU attribution	one global day	each user's local day
Fall-back hour	double-counted	two distinct buckets
Spring-forward hour	phantom bucket	23 correct buckets
Cross-zone comparability	broken	exact (UTC instants)
Portability	rewritten per engine	one logic, three spellings

Why this works — concept by concept:

• TIMESTAMPTZ as the instant — storing UTC makes the event comparable across every zone, and keeping the user's IANA zone name lets each row be projected to its own local day with the correct DST rule.
• Convert at the edge — applying AT TIME ZONE once, at the grouping step, keeps the stored value canonical (UTC) and localises only for the derived business date, which is exactly what per-user daily metrics require.
• Bucket by the UTC instant — grouping hourly detail on date_trunc over the instant maps the repeated fall-back local hour to two separate UTC hours, so the overlap cannot double-count and the spring-forward gap cannot invent a bucket.
• One logic, three engines — because Postgres, Snowflake, and BigQuery all model an instant type plus a convert-with-zone function, the same store-UTC-convert-late pipeline ports by swapping only the function name.
• Cost — one instant column, one zone name, and a single edge conversion per query, versus recurring DST incidents and per-engine rewrites. The eliminated cost is the whole family of local-grouping bugs — O(1) correct bucketing by instant instead of O(transitions) special cases patched after each DST night.

Date functions
Topic — date-functions
Date function problems on time-zone conversion and truncation

Practice →

Data validation Topic — data-validation Data validation problems on DST edge cases and timestamp checks

Practice →

---

3. Bitemporal tables — valid time vs transaction time

Valid time is when a fact was true in the world; transaction time is when the database knew it

The mental model in one line: a bitemporal table stamps every row on two independent time axes — valid time (valid_from / valid_to: the period during which the fact was true in the real world) and transaction time (tx_from / tx_to: the period during which the database believed that fact) — so instead of overwriting a row when something changes, you close the old row's transaction-time interval and insert a new one, giving you the power to answer both "what is true now" and "what did we believe as of last month" from the same table, with a complete audit and the ability to record retroactive corrections without destroying history. SCD Type 2 is just the valid-time-only special case; adding transaction time is what makes corrections auditable.

The two time axes — and why you need both.

• Valid time (effective / business time). The period a fact was true in the modelled world: "the price was $9 from Jan 1 to Mar 31." It can be set in the past or future and is what business users mean by "as of."
• Transaction time (system / knowledge time). The period the database held that belief: "we recorded that $9 on Jan 2 and superseded it on Apr 10." It is append-only and machine-controlled.
• Bitemporal = both. A row carries [valid_from, valid_to) and [tx_from, tx_to). The pair lets you reconstruct the database as it was believed at any past moment about any past or future real-world period.
• The half-open interval convention. Use [from, to) (inclusive start, exclusive end) with 'infinity' for open intervals, so adjacent periods meet without overlap and predicates are unambiguous.

As-of queries — the whole point of bitemporal.

• Current belief, current state. tx_to = 'infinity' AND valid_from <= d AND valid_to > d — "what is true now for date d."
• Historical belief. tx_from <= S AND tx_to > S AND valid_from <= d AND valid_to > d — "what we believed at system time S about date d," which reproduces a report exactly as it ran then.
• Two crosshairs. Every bitemporal question is a point on the 2-D grid: pick a valid-time coordinate and a transaction-time coordinate.
• Reproducibility. Because transaction time is append-only, an old report can be re-run bit-for-bit — the audit and reconciliation superpower.

Corrections versus ordinary updates.

• An ordinary update overwrites. It destroys the previous value and any ability to explain a change — fatal for finance, compliance, and reconciliation.
• A bitemporal correction is close-and-insert. Set tx_to = now() on the superseded row and insert a new row with the corrected value, tx_from = now(), tx_to = 'infinity' — the old belief is retained, just no longer current.
• Retroactive correction. If the valid period was wrong too, the new row carries the corrected valid interval, so "what should have been true" and "what we used to think" both survive.
• Late-arriving facts. A fact learned today about last week is inserted with a past valid_from and tx_from = now() — valid time and transaction time diverge, which is normal and expected.

The failure modes senior engineers pre-empt.

• Overlapping valid intervals. Two current rows covering the same valid period give ambiguous "as-of" answers. Mitigation: an exclusion constraint or a merge step that guarantees non-overlap per key.
• Overwriting instead of closing. A plain UPDATE erases the audit. Mitigation: only ever close-and-insert; make the base table append-mostly.
• Confusing the two axes. Filtering a "believed-then" question with tx_to = 'infinity' returns the current belief, not the historical one. Mitigation: always state both coordinates explicitly.

Common interview probes on bitemporal.

• "Difference between valid time and transaction time?" — when a fact was true vs when the DB knew it.
• "How do you record a correction without losing history?" — close the old tx interval, insert a new row.
• "How do you reproduce last month's report exactly?" — as-of query on tx_from/tx_to at that system time.
• "How is this different from SCD2?" — SCD2 is valid-time only; bitemporal adds transaction time for audit.

Worked example — bitemporal DDL and the first insert

Detailed explanation. Start by modelling a product price as a bitemporal fact and inserting the initial belief. The schema carries both interval pairs and a non-overlap guarantee. Insert "$9, valid all of Q1, known from Jan 2."

• The grain. One row per (product_id, valid period, tx period).
• The intervals. [valid_from, valid_to) and [tx_from, tx_to), 'infinity' for open ends.
• The guard. A constraint (or discipline) preventing overlapping current valid periods per product.

Question. Create a bitemporal price_bt table and insert the initial $9 Q1 price known as of Jan 2.

Input.

Column	Role
product_id	business key
price_cents	the fact
valid_from / valid_to	when the price was true
tx_from / tx_to	when we believed it

Code.

CREATE TABLE price_bt (
    product_id  int          NOT NULL,
    price_cents int          NOT NULL,
    valid_from  date         NOT NULL,
    valid_to    date         NOT NULL DEFAULT 'infinity',   -- half-open [from, to)
    tx_from     timestamptz  NOT NULL DEFAULT now(),
    tx_to       timestamptz  NOT NULL DEFAULT 'infinity',
    CHECK (valid_from < valid_to),
    CHECK (tx_from   < tx_to)
);

-- Initial belief: product 1 costs $9 for all of Q1 2024, recorded on Jan 2.
INSERT INTO price_bt (product_id, price_cents, valid_from, valid_to, tx_from, tx_to)
VALUES (1, 900, DATE '2024-01-01', DATE '2024-04-01',
        TIMESTAMPTZ '2024-01-02 09:00+00', 'infinity');

-- Current belief about any Q1 date:
SELECT price_cents
FROM price_bt
WHERE product_id = 1
  AND valid_from <= DATE '2024-02-15' AND valid_to > DATE '2024-02-15'
  AND tx_to = 'infinity';                          -- -> 900

Step-by-step explanation.

1. The table carries four temporal columns: valid_from/valid_to for the real-world period and tx_from/tx_to for the belief period, each as a half-open interval so adjacent rows meet exactly at a boundary without overlapping.
2. valid_to/tx_to default to 'infinity', the idiom for "still true / still believed," which makes "the current open row" simply tx_to = 'infinity'.
3. The two CHECK constraints enforce that each interval is non-empty and correctly ordered — a cheap guard against inverted or zero-length periods that would corrupt as-of answers.
4. The insert records a single coherent belief: $9, valid across Q1, believed from Jan 2 onward with no end (tx_to = 'infinity'). Valid time is a date range; transaction time is a timestamptz because "when we knew it" is an instant.
5. The current-belief query fixes both coordinates: a valid-time point (2024-02-15 inside the valid interval) and the current transaction-time slice (tx_to = 'infinity'), returning $9 — the template every later as-of query specialises.

Output.

Query coordinates	Predicate	Result
valid 2024-02-15, now	valid ∋ d AND tx_to = inf	900
valid 2024-05-01, now	outside valid interval	none
any date, tx < 2024-01-02	before we knew it	none
valid 2024-01-01, now	boundary (inclusive start)	900

Rule of thumb. Model a bitemporal fact with two half-open interval pairs — [valid_from, valid_to) for real-world truth and [tx_from, tx_to) for database belief — default the to columns to 'infinity', and treat tx_to = 'infinity' as "the current belief." Enforce ordered, non-empty intervals with CHECK constraints from day one.

Worked example — a retroactive correction and two as-of queries

Detailed explanation. Now the interesting case: on Apr 10 you learn the Q1 price was actually $12, not $9, retroactive to the whole quarter. A bitemporal correction closes the old belief and inserts the new one, and two as-of queries then return different answers depending on the transaction-time coordinate. Apply the correction and query both beliefs.

• The correction. Close the $9 row's tx_to at Apr 10; insert a $12 row valid across Q1, believed from Apr 10.
• As-of now. Q1 price is $12 (current belief).
• As-of Mar 1. Q1 price was believed to be $9 (historical belief) — reproduces the old report.

Question. Record the retroactive $12 correction, then query the Q1 price as believed now and as believed on Mar 1.

Input.

Step	valid period	tx period	price
original	Q1	[Jan 2, ∞) → closed at Apr 10	900
correction	Q1	[Apr 10, ∞)	1200
as-of now	Feb 15	tx_to = ∞	1200
as-of Mar 1	Feb 15	tx ∋ Mar 1	900

Code.

-- Correction learned on 2024-04-10: Q1 price was really $12, retroactive to all of Q1.
-- 1) CLOSE the transaction-time interval of the now-superseded $9 belief.
UPDATE price_bt
SET tx_to = TIMESTAMPTZ '2024-04-10 12:00+00'
WHERE product_id = 1 AND price_cents = 900 AND tx_to = 'infinity';

-- 2) INSERT the corrected belief: same valid period (Q1), new belief from Apr 10.
INSERT INTO price_bt (product_id, price_cents, valid_from, valid_to, tx_from, tx_to)
VALUES (1, 1200, DATE '2024-01-01', DATE '2024-04-01',
        TIMESTAMPTZ '2024-04-10 12:00+00', 'infinity');

-- As-of NOW: what do we believe today the Q1 price was?
SELECT price_cents FROM price_bt
WHERE product_id = 1
  AND valid_from <= DATE '2024-02-15' AND valid_to > DATE '2024-02-15'
  AND tx_to = 'infinity';                                  -- -> 1200

-- As-of MAR 1: what did we believe on 2024-03-01 the Q1 price was? (reproduces old report)
SELECT price_cents FROM price_bt
WHERE product_id = 1
  AND valid_from <= DATE '2024-02-15' AND valid_to > DATE '2024-02-15'
  AND tx_from <= TIMESTAMPTZ '2024-03-01 00:00+00'
  AND tx_to   >  TIMESTAMPTZ '2024-03-01 00:00+00';        -- -> 900

Step-by-step explanation.

1. The correction does not touch the valid-time interval of the old row — the price really was in effect across Q1. It closes only the transaction-time interval: tx_to moves from 'infinity' to Apr 10, meaning "we believed $9 up until Apr 10."
2. The new row carries the same valid interval (Q1) but the corrected price_cents = 1200 and a transaction-time interval starting Apr 10 with tx_to = 'infinity' — the current belief.
3. The as-of-now query fixes transaction time at tx_to = 'infinity' and a valid-time point of Feb 15, returning $12: today we believe the Q1 price was $12.
4. The as-of-Mar-1 query fixes transaction time at Mar 1 (tx_from <= Mar 1 < tx_to), which lands on the old row whose belief interval was [Jan 2, Apr 10), returning $9 — an exact reproduction of the report as it ran on Mar 1, before anyone knew about the correction.
5. This is the reconciliation superpower: the same table, queried at two transaction-time coordinates, explains both numbers and why they differ — impossible with an overwrite, trivial bitemporally.

Output.

Question	tx coordinate	Result
Q1 price, believed now	tx_to = ∞	1200
Q1 price, believed Mar 1	tx ∋ Mar 1	900
Q1 price, believed Apr 30	tx ∋ Apr 30	1200
audit of the change	both rows present	full history

Rule of thumb. Record a correction as a close-and-insert on the transaction-time axis, leaving valid time to describe real-world truth. Then every "why did the number change" question is a pair of as-of queries at two transaction-time coordinates — and old reports reproduce exactly because transaction time is append-only.

Worked example — SCD2 as the valid-time-only special case

Detailed explanation. Analysts often already know Slowly Changing Dimension Type 2; showing that SCD2 is just bitemporal minus transaction time connects the two and clarifies when you need the second axis. Model a customer's tier history as SCD2, then note what transaction time would add.

• SCD2. One valid-time interval per version, a is_current flag, no transaction time.
• What it answers. "What tier was the customer on date d" — a valid-time as-of.
• What it cannot answer. "What did we believe their tier was, last month" — needs transaction time.

Question. Model a customer tier SCD2 with valid-time intervals, and state the one question it cannot answer.

Input.

Aspect	SCD2 (valid-time only)	Bitemporal
Intervals	valid_from/valid_to	+ tx_from/tx_to
Current row	is_current = true	tx_to = 'infinity'
Answers "state on date d"	yes	yes
Answers "belief at time S"	no	yes

Code.

-- SCD2 tier history: valid-time intervals only (the classic dimension pattern).
CREATE TABLE dim_customer_tier (
    customer_id int,
    tier        text,
    valid_from  date NOT NULL,
    valid_to    date NOT NULL DEFAULT 'infinity',
    is_current  boolean GENERATED ALWAYS AS (valid_to = 'infinity') STORED
);

INSERT INTO dim_customer_tier (customer_id, tier, valid_from, valid_to) VALUES
  (7, 'silver', DATE '2023-01-01', DATE '2024-01-01'),
  (7, 'gold',   DATE '2024-01-01', 'infinity');

-- Valid-time as-of: what tier on 2023-06-01?  -> 'silver'
SELECT tier FROM dim_customer_tier
WHERE customer_id = 7
  AND valid_from <= DATE '2023-06-01' AND valid_to > DATE '2023-06-01';

-- The question SCD2 CANNOT answer: "on 2023-06-01, what did we BELIEVE the tier was?"
-- There is no transaction-time axis, so a backdated correction would overwrite history.

Step-by-step explanation.

1. The SCD2 table has only the valid-time pair plus a derived is_current flag. Each row is one contiguous period during which the tier was in effect, and the open interval (valid_to = 'infinity') marks the current version.
2. The valid-time as-of query — valid_from <= d < valid_to — answers "what tier on date d," returning silver for a mid-2023 date. This is exactly the current-belief bitemporal query with the transaction-time predicate dropped.
3. What SCD2 cannot do is reproduce a past belief: if you later discover the customer was actually gold since November 2023 and backdate it, you must overwrite or insert without an audit of when the change was recorded.
4. Adding tx_from/tx_to turns the dimension bitemporal, so the backdated correction becomes a close-and-insert and "what did we believe on date S" becomes answerable — the upgrade path from SCD2 to full bitemporality.
5. The senior framing: use SCD2 when you only need "state over real-world time" (most dimensions), and add transaction time when corrections must be auditable and reproducible — finance, pricing, regulatory, or anything reconciled against past reports.

Output.

Question	SCD2	Bitemporal
Tier on 2023-06-01	silver	silver
Current tier	gold	gold
Belief on 2023-06-01	unanswerable	answerable
Audit of a backdated fix	lost	retained

Rule of thumb. Reach for SCD2 (valid-time only) for ordinary dimensions where you just need state over real-world time, and upgrade to bitemporal by adding a transaction-time interval whenever corrections must be auditable and past reports reproducible. Bitemporal is SCD2 plus the "when did we know it" axis.

Senior interview question on bitemporal pricing and corrections

A senior interviewer might ask: "Model a product price table that has to answer two different questions for finance — 'what price did we actually charge on this date' and 'what price should we have charged after the correction' — while keeping a full audit of every change and letting any past invoice run be reproduced exactly. Design the schema, the correction workflow, and the as-of queries, and explain why an ordinary update is disqualified."

Solution Using a bitemporal price table, close-and-insert corrections, and as-of queries

-- 1. Bitemporal price: valid time = when the price applied; tx time = when we believed it.
CREATE TABLE price_bt (
    product_id  int NOT NULL,
    price_cents int NOT NULL,
    valid_from  date NOT NULL,
    valid_to    date NOT NULL DEFAULT 'infinity',
    tx_from     timestamptz NOT NULL DEFAULT now(),
    tx_to       timestamptz NOT NULL DEFAULT 'infinity',
    CHECK (valid_from < valid_to AND tx_from < tx_to)
);

-- 2. Correction workflow (close-and-insert) — NEVER an in-place UPDATE of the value.
--    Learned 2024-04-10 that Q1 price should have been $12, not the charged $9.
UPDATE price_bt SET tx_to = now()
 WHERE product_id = 1 AND tx_to = 'infinity'
   AND valid_from = DATE '2024-01-01';                     -- close the old belief
INSERT INTO price_bt (product_id, price_cents, valid_from, valid_to)
VALUES (1, 1200, DATE '2024-01-01', DATE '2024-04-01');    -- new belief, tx_from = now()

-- 3a. "What did we CHARGE on 2024-02-15?" -> the belief in force AT invoice time (Mar 1).
SELECT price_cents FROM price_bt
WHERE product_id = 1
  AND valid_from <= DATE '2024-02-15' AND valid_to > DATE '2024-02-15'
  AND tx_from <= TIMESTAMPTZ '2024-03-01 00:00+00'
  AND tx_to   >  TIMESTAMPTZ '2024-03-01 00:00+00';        -- -> 900 (what we charged)

-- 3b. "What SHOULD we have charged?" -> the current belief.
SELECT price_cents FROM price_bt
WHERE product_id = 1
  AND valid_from <= DATE '2024-02-15' AND valid_to > DATE '2024-02-15'
  AND tx_to = 'infinity';                                  -- -> 1200 (corrected)

-- 4. Non-overlap guard so "as-of" is never ambiguous for a product's CURRENT beliefs.
ALTER TABLE price_bt ADD CONSTRAINT no_overlap
  EXCLUDE USING gist (
    product_id WITH =,
    daterange(valid_from, valid_to) WITH &&
  ) WHERE (tx_to = 'infinity');

Step-by-step trace.

Step	Action	Temporal effect
Charge	insert $9, Q1, believed Jan 2	valid=Q1, tx=[Jan 2, ∞)
Discover error	learn $12 on Apr 10	correction needed
Close	tx_to = now() on $9 row	tx=[Jan 2, Apr 10)
Insert	$12, Q1, tx_from = now()	valid=Q1, tx=[Apr 10, ∞)
Charged?	as-of tx = Mar 1	900
Should be?	as-of tx = ∞	1200

After the workflow, the table holds both beliefs: the $9 row with a closed transaction-time interval [Jan 2, Apr 10) and the $12 row with an open interval [Apr 10, ∞), both valid across Q1. "What did we charge" is an as-of query at the invoice's transaction time (Mar 1) returning $9; "what should we have charged" is the current belief returning $12; and the exclusion constraint guarantees at most one current price per product per valid period, so as-of answers are never ambiguous. An ordinary UPDATE is disqualified because it would erase the $9 belief entirely, making the charged amount un-explainable and the March invoice run un-reproducible.

Output:

Requirement	Overwrite (UPDATE)	Bitemporal
"What we charged"	lost	900 (as-of tx)
"What we should charge"	1200 only	1200 (current)
Full audit of the change	none	both rows retained
Reproduce March invoice	impossible	exact (as-of tx)
Ambiguous as-of	possible	prevented (exclusion)

Why this works — concept by concept:

• Two independent time axes — valid time describes when the price applied and transaction time describes when we believed it, so a retroactive correction changes belief without rewriting real-world truth, and both coordinates are queryable.
• Close-and-insert corrections — closing the superseded row's transaction-time interval and inserting a new one keeps the old belief as history, which is precisely what makes "what we charged" and "what we should have charged" both answerable.
• As-of queries — fixing a transaction-time coordinate reproduces the database exactly as it was believed at that instant, so any past report or invoice run replays bit-for-bit — the audit and reconciliation property finance requires.
• Non-overlap exclusion constraint — restricting overlap checks to current beliefs (tx_to = 'infinity') guarantees a single unambiguous price per product per valid period without blocking the historical rows that must overlap in transaction time.
• Cost — one extra interval pair and a close-and-insert per correction, versus an un-auditable overwrite that loses money-relevant history. The eliminated cost is every un-explainable number and un-reproducible report — O(1) as-of lookups over append-only history instead of O(∞) forensic reconstruction after the fact.

Date & time
Topic — date-time
Date & time problems on valid-time and as-of queries

Practice →

Date arithmetic Topic — date-arithmetic Date arithmetic problems on interval overlap and history

Practice →

---

4. Calendar dimensions — fiscal calendars, weeks, holidays

A date dimension precomputes every calendar fact so joins replace fragile date arithmetic

The mental model in one line: a calendar dimension (a.k.a. date dimension) is a table with exactly one row per calendar date, keyed by a yyyymmdd integer surrogate, that precomputes every calendar attribute a report might need — ISO year and week, fiscal quarter and period under whatever fiscal calendar the business uses (often 4-4-5), day-of-week, weekend and business-day flags, holiday markers — so that fiscal, weekly, and business-day reporting becomes a simple JOIN on the date key instead of a thicket of EXTRACT, date_trunc, and hand-rolled holiday logic re-derived (inconsistently) in every query. Build it once, test it once, and every downstream query inherits a single source of calendar truth.

Why a date dimension exists.

• Precompute once, join everywhere. Fiscal periods, ISO weeks, and holiday flags are computed a single time when the dimension is built, so no report re-implements them — and they cannot drift apart between queries.
• A join is faster and clearer than arithmetic. JOIN dim_date USING (date_key) reads better and optimises better than nested EXTRACT/CASE expressions scattered across the codebase.
• A home for business rules. "Fiscal year starts Feb 1," "week starts Monday," "these are company holidays" are business decisions that belong in one governed table, not in a hundred WHERE clauses.
• Conformed dimension. The same dim_date conforms across every fact table, so "Q3" means the same thing in sales, finance, and ops.

Grain and key.

• Grain: one row per date. The dimension spans a fixed range (e.g. 2015-01-01 to 2035-12-31) with exactly one row per day — dense, no gaps.
• Surrogate key: yyyymmdd integer. 20240311 is compact, human-readable, sortable, and joins as an integer; the real full_date sits alongside it.
• A special "unknown" row. A sentinel key (e.g. -1 or 19000101) handles missing/NULL fact dates so joins stay inner and metrics do not silently drop rows.
• Time-of-day is separate. A dim_time (one row per minute/second of a day) handles intraday grain; keep it out of dim_date.

Week and fiscal definitions — the details that bite.

• ISO-8601 week. Weeks start Monday; week 1 is the week containing the first Thursday (equivalently, containing Jan 4). The ISO week-year can differ from the calendar year for a few days each January/December — store iso_year alongside iso_week.
• US/other week. Some businesses start weeks on Sunday and number differently; encode the chosen convention explicitly rather than assuming the engine default.
• Fiscal 4-4-5. Many retailers split each quarter into months of 4, 4, and 5 weeks (13 weeks), giving 52-week years with an occasional 53-week year — so fiscal "months" do not align to calendar months and must be precomputed.
• Fiscal year offset. A fiscal year may start in February, July, or October; the dimension encodes fiscal_year, fiscal_qtr, and fiscal_period under that offset.

Holidays and business days.

• A holiday table drives a flag. Maintain holidays (per country/region) in their own table and derive is_holiday on dim_date; holidays are policy, not arithmetic.
• Business-day flag. is_business_day = NOT is_weekend AND NOT is_holiday gives instant working-day filters.
• Net working days via join. Counting business days between two dates becomes a SUM(is_business_day) over the date range — no recursive weekday math.
• Business-day offsets. "3 business days after" is a windowed lookup over the ordered business days, again a join/window, not bespoke logic.

Common interview probes on the date dimension.

• "Why a date dimension instead of EXTRACT in the query?" — precompute once, conform, no drift, join beats arithmetic.
• "What's the key?" — a yyyymmdd integer surrogate, with full_date alongside.
• "How do ISO weeks work?" — Monday start, week 1 contains the first Thursday; store iso_year too.
• "How do you count business days?" — SUM(is_business_day) over the range via the dimension.

Worked example — date dimension DDL and generate_series populate

Detailed explanation. Build a dim_date in Postgres by generating a dense date range and computing each attribute once. Cover the surrogate key, ISO week, day-of-week, and weekend flag.

• The range. generate_series over the desired span, one row per day.
• The key. to_char(d,'YYYYMMDD')::int.
• The attributes. iso_year, iso_week, dow, is_weekend, plus month/quarter.

Question. Create and populate a dim_date with a yyyymmdd key, ISO week columns, and a weekend flag.

Input.

Column	Expression
date_key	to_char(d,'YYYYMMDD')::int
iso_year	EXTRACT(isoyear FROM d)
iso_week	EXTRACT(week FROM d)
is_weekend	EXTRACT(isodow FROM d) IN (6,7)

Code.

CREATE TABLE dim_date (
    date_key    int  PRIMARY KEY,          -- yyyymmdd surrogate
    full_date   date NOT NULL UNIQUE,
    year        int, quarter int, month int, day int,
    iso_year    int, iso_week int,
    day_of_week int,                        -- 1=Mon .. 7=Sun (ISO)
    is_weekend  boolean
);

INSERT INTO dim_date
SELECT
    to_char(d, 'YYYYMMDD')::int                          AS date_key,
    d::date                                              AS full_date,
    EXTRACT(year    FROM d)::int,
    EXTRACT(quarter FROM d)::int,
    EXTRACT(month   FROM d)::int,
    EXTRACT(day     FROM d)::int,
    EXTRACT(isoyear FROM d)::int                         AS iso_year,
    EXTRACT(week    FROM d)::int                          AS iso_week,   -- ISO week number
    EXTRACT(isodow  FROM d)::int                          AS day_of_week, -- 1=Mon..7=Sun
    EXTRACT(isodow  FROM d) IN (6, 7)                     AS is_weekend
FROM generate_series(DATE '2015-01-01', DATE '2035-12-31', INTERVAL '1 day') AS g(d);

-- Use it: monthly orders by joining, not computing dates in the fact query.
SELECT dd.year, dd.month, count(*) AS orders
FROM fct_orders f
JOIN dim_date dd ON dd.date_key = f.order_date_key
GROUP BY 1, 2 ORDER BY 1, 2;

Step-by-step explanation.

1. generate_series(start, end, '1 day') produces one timestamp per day across the whole span, giving the dense, gap-free grain a date dimension requires — every date in range exists exactly once.
2. to_char(d,'YYYYMMDD')::int builds the 20240311-style surrogate key: compact, sortable, and joinable as an integer, while full_date keeps the real date for range predicates.
3. EXTRACT(isoyear ...) and EXTRACT(week ...) compute the ISO-8601 week-year and week number — note iso_year is stored separately because in early January or late December it can differ from the calendar year.
4. EXTRACT(isodow ...) returns 1–7 with Monday as 1, so IN (6,7) cleanly flags Saturday and Sunday without depending on locale — a common source of off-by-one weekend bugs.
5. Downstream, monthly (or weekly, or fiscal) reporting is a JOIN dim_date and a GROUP BY on precomputed columns — the fact query never touches date arithmetic, so every report agrees on what "month 3" or "ISO week 11" means.

Output.

full_date	date_key	iso_year	iso_week	is_weekend
2024-03-11	20240311	2024	11	false
2024-03-16	20240316	2024	11	true
2021-01-01	20210101	2020	53	false
2025-12-29	20251229	2026	1	false

Rule of thumb. Build dim_date once with generate_series, key it on a yyyymmdd integer, and store iso_year next to iso_week because the ISO week-year diverges from the calendar year at the January/December boundary. Then every report joins the dimension instead of re-deriving calendar math.

Worked example — a 4-4-5 fiscal calendar rollup

Detailed explanation. Retail finance rarely reports on calendar months; the 4-4-5 fiscal calendar splits each quarter into 4-, 4-, and 5-week "months" of exactly 13 weeks. Because these do not align to calendar months, they must be precomputed in the dimension. Add fiscal columns and roll up by fiscal period.

• The structure. 52-week year (occasionally 53), quarters of 13 weeks, periods of 4/4/5 weeks.
• The anchor. A fiscal-year start date; weeks numbered from there.
• The columns. fiscal_year, fiscal_period (1–12), fiscal_qtr.

Question. Add 4-4-5 fiscal columns to the dimension and report revenue by fiscal period.

Input.

Fiscal concept	Rule
Year length	52 weeks (53 in leap week years)
Quarter	13 weeks (4+4+5)
Period within quarter	weeks 1–4 → P1, 5–8 → P2, 9–13 → P3
Anchor	fiscal-year start date

Code.

-- Add fiscal columns computed from a fiscal-year anchor (e.g. FY starts first Sunday of Feb).
ALTER TABLE dim_date
  ADD COLUMN fiscal_year int,
  ADD COLUMN fiscal_week int,
  ADD COLUMN fiscal_period int,        -- 1..12  (the 4-4-5 "month")
  ADD COLUMN fiscal_qtr int;           -- 1..4

WITH anchored AS (
  SELECT date_key, full_date,
         -- weeks since the fiscal-year anchor; +1 so the first week is week 1
         (full_date - DATE '2024-02-04') / 7 + 1 AS fweek     -- anchor: FY2024 start
  FROM dim_date
  WHERE full_date >= DATE '2024-02-04' AND full_date < DATE '2025-02-02'
)
UPDATE dim_date d SET
  fiscal_year   = 2024,
  fiscal_week   = a.fweek,
  fiscal_qtr    = ((a.fweek - 1) / 13) + 1,
  -- within a 13-week quarter, weeks 1-4 -> P1, 5-8 -> P2, 9-13 -> P3, mapped to 1..12
  fiscal_period = ((a.fweek - 1) / 13) * 3
                  + CASE WHEN ((a.fweek - 1) % 13) < 4 THEN 1
                         WHEN ((a.fweek - 1) % 13) < 8 THEN 2
                         ELSE 3 END
FROM anchored a
WHERE d.date_key = a.date_key;

-- Report: revenue by fiscal period — a join + group by, no calendar-month assumption.
SELECT dd.fiscal_year, dd.fiscal_period, sum(f.revenue_cents) AS revenue
FROM fct_orders f
JOIN dim_date dd ON dd.date_key = f.order_date_key
GROUP BY 1, 2 ORDER BY 1, 2;

Step-by-step explanation.

1. The fiscal columns are added to the same dim_date, so fiscal reporting is just more precomputed attributes — no separate calendar table and no per-query fiscal math.
2. fweek counts whole weeks since the fiscal-year anchor (here the FY2024 start, a first-Sunday-of-February convention). Integer division by 7 buckets each date into its fiscal week; +1 makes weeks 1-based.
3. fiscal_qtr = ((fweek - 1) / 13) + 1 groups the 52 weeks into four 13-week quarters, because a 4-4-5 quarter is always 4+4+5 = 13 weeks — the defining property of the calendar.
4. fiscal_period maps the week's position within its quarter ((fweek-1) % 13) to the 4-4-5 shape: the first four weeks are period 1, the next four period 2, the last five period 3, then offset by the quarter to land in 1–12.
5. The rollup is a plain join and GROUP BY fiscal_period; because the periods were precomputed, a "P3 is five weeks" fact is baked into the dimension and every fiscal report agrees, instead of each analyst re-deriving 4-4-5 boundaries (and getting them subtly different).

Output.

fiscal_week	fiscal_qtr	fiscal_period
1–4	1	1
5–8	1	2
9–13	1	3
14–17	2	4

Rule of thumb. Precompute 4-4-5 fiscal periods in the date dimension from a single fiscal-year anchor, because fiscal "months" are 4- or 5-week blocks that never align to calendar months. Then fiscal reporting is a join on fiscal_period, and every report inherits the same, tested period boundaries.

Worked example — business-day and net-working-day math via the dimension

Detailed explanation. SLAs and finance love "business days": "resolve within 3 business days," "net working days in the period." With a holiday table feeding an is_business_day flag, both become joins and sums instead of recursive weekday logic. Add holidays and compute net working days.

• The holiday source. A dim_holiday table (date + region).
• The flag. is_business_day = NOT is_weekend AND NOT is_holiday.
• Net working days. SUM(is_business_day) over an inclusive date range.

Question. Derive is_business_day from weekends and a holiday table, and count net working days between two dates.

Input.

Piece	Value
Holiday table	dim_holiday(holiday_date, region)
Business-day rule	NOT is_weekend AND NOT is_holiday
Net working days	SUM(is_business_day) over [start, end]
Business-day offset	window over ordered business days

Code.

CREATE TABLE dim_holiday (holiday_date date, region text, name text,
                          PRIMARY KEY (holiday_date, region));
INSERT INTO dim_holiday VALUES
  (DATE '2024-01-01','US','New Year'),
  (DATE '2024-01-15','US','MLK Day'),
  (DATE '2024-02-19','US','Presidents Day');

-- Derive the business-day flag on the dimension (US region shown).
ALTER TABLE dim_date ADD COLUMN is_holiday boolean DEFAULT false;
UPDATE dim_date d SET is_holiday = true
FROM dim_holiday h WHERE h.holiday_date = d.full_date AND h.region = 'US';

ALTER TABLE dim_date ADD COLUMN is_business_day boolean
  GENERATED ALWAYS AS (NOT is_weekend AND NOT is_holiday) STORED;

-- Net working days in Jan 2024 (weekends + holidays excluded): a SUM, not weekday math.
SELECT sum(is_business_day::int) AS net_working_days
FROM dim_date
WHERE full_date BETWEEN DATE '2024-01-01' AND DATE '2024-01-31';   -- -> 21

Step-by-step explanation.

1. Holidays live in their own dim_holiday table keyed by date and region, because holidays are policy (they change, they vary by country) and do not belong in arithmetic — they are data to be maintained.
2. The UPDATE ... FROM dim_holiday sets is_holiday on each matching dimension row, joining the policy table into the precomputed calendar for a chosen region.
3. is_business_day is a GENERATED ALWAYS column: NOT is_weekend AND NOT is_holiday, computed and stored once, so every query gets an instant working-day filter without re-evaluating the rule.
4. Net working days becomes SUM(is_business_day::int) over an inclusive date range — January 2024 has 23 weekdays minus 2 holidays (New Year, MLK) = 21 — with no recursive weekday counting or holiday joins in the reporting query.
5. The same flag powers "3 business days after date d" as a window over the ordered business days (row_number() on WHERE is_business_day), so SLA arithmetic is also a dimension lookup rather than bespoke logic scattered across services.

Output.

Query	Result
Weekdays in Jan 2024	23
minus holidays (New Year, MLK)	21
SUM(is_business_day) Jan	21
business days Jan 1–7	4 (New Year + weekend excluded)

Rule of thumb. Keep holidays in a maintained dim_holiday table, derive is_business_day as a generated flag on the date dimension, and compute working-day metrics as SUM(is_business_day) over a range. Business-day math becomes a join and a sum — never recursive weekday logic re-implemented per query.

Senior interview question on a fiscal, holiday-aware date dimension

A senior interviewer might ask: "Finance needs reporting by fiscal 4-4-5 period, marketing needs ISO-week cohorts, and ops needs business-day SLAs that respect company holidays — all consistent across every fact table. Design a date dimension that serves all three: its grain and key, how you encode ISO weeks and the fiscal calendar, how holidays and business days are maintained, and why this beats computing dates in each query."

Solution Using a conformed date dimension with ISO, fiscal, and business-day columns

-- 1. One conformed dimension, one row per day, yyyymmdd surrogate key.
CREATE TABLE dim_date (
    date_key   int PRIMARY KEY,                 -- yyyymmdd
    full_date  date NOT NULL UNIQUE,
    iso_year   int, iso_week int,               -- marketing: ISO-week cohorts
    fiscal_year int, fiscal_period int, fiscal_qtr int,  -- finance: 4-4-5
    is_weekend boolean, is_holiday boolean,
    is_business_day boolean                      -- ops: SLA base
);
-- + a sentinel row for unknown/NULL fact dates so joins stay inner:
INSERT INTO dim_date (date_key, full_date) VALUES (19000101, DATE '1900-01-01');

-- 2. Populate calendar + ISO columns from a dense generate_series (as in the earlier example),
--    fiscal columns from a fiscal-year anchor (4-4-5), holidays from dim_holiday.
--    is_business_day = NOT is_weekend AND NOT is_holiday (generated/stored).

-- 3. Marketing: weekly cohorts by ISO week — a join, consistent across every fact.
SELECT dd.iso_year, dd.iso_week, count(DISTINCT f.user_id) AS signups
FROM fct_signup f JOIN dim_date dd ON dd.date_key = f.signup_date_key
GROUP BY 1, 2;

-- 4. Finance: revenue by fiscal period.  Ops: business-day SLA compliance.
SELECT dd.fiscal_year, dd.fiscal_period, sum(f.revenue_cents) AS revenue
FROM fct_orders f JOIN dim_date dd ON dd.date_key = f.order_date_key
GROUP BY 1, 2;

-- SLA: were tickets resolved within 3 business days? (net business days via the dimension)
SELECT t.ticket_id,
       (SELECT sum(is_business_day::int) FROM dim_date
        WHERE full_date > o.full_date AND full_date <= r.full_date) AS biz_days
FROM tickets t
JOIN dim_date o ON o.date_key = t.opened_date_key
JOIN dim_date r ON r.date_key = t.resolved_date_key;

Step-by-step trace.

Consumer	Needs	Dimension column(s)
Marketing	ISO-week cohorts	iso_year, iso_week
Finance	fiscal 4-4-5 periods	fiscal_year, fiscal_period, fiscal_qtr
Ops	business-day SLAs	is_business_day
All	conformed meaning	one dim_date, one grain
Data quality	no dropped rows	sentinel 19000101 row

After building the conformed dim_date, marketing groups signups by iso_week, finance groups revenue by fiscal_period, and ops counts is_business_day between open and resolve to check a 3-business-day SLA — all from the same dimension, so "ISO week 11," "fiscal P3," and "a business day" mean exactly one thing everywhere. A sentinel 19000101 row absorbs NULL/unknown fact dates so inner joins never silently drop rows, and because every calendar attribute is precomputed and tested once, no report re-derives (and mis-derives) date logic.

Output:

Requirement	Per-query date math	Conformed dimension
ISO-week cohorts	inconsistent across queries	one iso_week column
Fiscal 4-4-5	re-derived, error-prone	precomputed fiscal_period
Business-day SLA	recursive weekday logic	SUM(is_business_day)
Consistency	drifts between teams	conformed, single source
NULL dates	rows dropped	sentinel row keeps them

Why this works — concept by concept:

• One conformed dimension — a single dim_date at one-row-per-day grain, joined by every fact table, guarantees that ISO weeks, fiscal periods, and business days mean the same thing across marketing, finance, and ops instead of drifting per query.
• Precomputed calendar attributes — computing ISO week-year, 4-4-5 fiscal periods, and holiday flags once at build time turns every report into a join on tested columns, eliminating the re-derivation errors that plague ad-hoc EXTRACT/CASE logic.
• Business-day flag from a holiday table — deriving is_business_day from weekends plus a maintained holiday policy makes working-day and SLA math a SUM over a range, not recursive weekday counting scattered across services.
• Sentinel unknown row — a reserved 19000101 key for NULL/unknown fact dates keeps joins inner and metrics complete, so bad or missing dates surface as a known bucket rather than silently vanishing.
• Cost — one build-time population of a dense table versus per-query calendar arithmetic everywhere. The eliminated cost is the drift and re-derivation tax — O(1) join to precomputed truth instead of O(queries) re-implementations of ISO weeks, fiscal periods, and holidays.

Date functions
Topic — date-functions
Date function problems on calendars, weeks, and fiscal periods

Practice →

Data transformation Topic — data-transformation Data transformation problems on date dimensions and rollups

Practice →

---

5. Temporal patterns — as-of joins, SCD windows, backfills

As-of joins, SCD windowing, and zone-aware backfills — the temporal patterns in every pipeline

The mental model in one line: three patterns recur across all of temporal data engineering — the as-of (point-in-time) join attaches each fact to the version of a dimension that was valid at the fact's instant rather than the current one; slowly-changing-time windowing derives a version's valid_to from the next version's valid_from with LEAD, guarding against gaps and overlaps; and the zone-aware backfill reprocesses history by UTC partition using the correct historical offset and tz-database version, because both DST rules and the tz data itself change over time — and underneath all three sits the event-time-vs-processing-time distinction and a watermark for late-arriving data. Master these and most temporal requirements reduce to one of them.

The as-of (point-in-time) join.

• What it is. Join a fact at time t to the one dimension row whose validity interval contains t — "the price when the order was placed," not the price now.
• Why it matters. Joining to the current dimension row retroactively rewrites history; point-in-time correctness is mandatory for finance, ML features, and reproducible reporting.
• How to write it. A LATERAL subquery taking the latest version at or before t (ORDER BY valid_from DESC LIMIT 1), or a range predicate valid_from <= t < valid_to; Snowflake and DuckDB have a native ASOF JOIN.
• The ML overlap. "Feature values as of the label time" is the same as-of join — getting it wrong causes label leakage.

Slowly-changing-time windowing.

• Deriving valid_to. Given rows stamped only with valid_from, compute each version's end as the next version's start: LEAD(valid_from) OVER (PARTITION BY key ORDER BY valid_from), defaulting the last to 'infinity'.
• Gap and overlap guards. Adjacent intervals must meet exactly (valid_to(prev) = valid_from(next)); validate with a window check so no date falls in two versions or none.
• Change detection. Only emit a new version when a tracked attribute actually changes (IS DISTINCT FROM the previous row), collapsing no-op updates.
• Half-open intervals. Keep [valid_from, valid_to) so the boundary date belongs to exactly one version.

Zone-aware backfills.

• Reprocess by UTC partition. Historical partitions are keyed by UTC; a backfill reruns whole UTC days so results are deterministic and idempotent regardless of local calendars.
• Historical offsets matter. Converting an old instant to local must use the offset in force then — tzdata encodes past DST-rule changes, so "local time in 2007" differs from today's rule.
• tz-database versioning. Governments change DST rules; a backfill run months later can differ if the tz database was updated. Pin/record the tzdata version for reproducibility.
• Idempotency. Backfills must be safe to re-run — overwrite the target UTC partition atomically rather than appending, so a retry does not double-count.

Event time vs processing time and watermarks.

• Event time. When the event actually happened (from the payload) — what business logic should use.
• Processing time. When the pipeline saw it — always ≥ event time, and the two diverge for late/out-of-order data.
• Watermark. A moving "we have probably seen all events up to t" bound that lets windows close while tolerating bounded lateness; late events past the watermark go to a correction/side path.
• Late-arriving facts. These are exactly the bitemporal case: a past valid_from with a tx_from of now.

Common interview probes on temporal patterns.

• "What's an as-of join?" — join to the dimension version valid at the fact's time, not the current one.
• "How do you derive SCD2 valid_to?" — LEAD(valid_from) per key, last = 'infinity'.
• "Why do backfills key on UTC?" — deterministic, idempotent partitions independent of local calendars.
• "Event time vs processing time?" — when it happened vs when we saw it; watermark bounds lateness.

Worked example — an as-of (point-in-time) join

Detailed explanation. Attach each order to the price that was in effect when it was placed, not the current price. Show the portable LATERAL form and the native ASOF JOIN.

• The fact. orders(order_id, placed_at).
• The dimension. price_bt(product_id, price_cents, valid_from, valid_to).
• The join. The price version whose valid interval contains placed_at.

Question. Join each order to the price valid at its placed_at, using a portable LATERAL and a native ASOF JOIN.

Input.

Order	placed_at	Valid price then
A	2024-02-15	$9 (Q1 belief in force)
B	2024-05-10	$12 (Q2 price)
join key	product_id	+ time containment
correctness	version at t, not now	point-in-time

Code.

-- Portable: LATERAL picks the ONE price version valid at the order's instant.
SELECT o.order_id, o.placed_at, p.price_cents
FROM orders o
JOIN LATERAL (
  SELECT price_cents
  FROM price_bt
  WHERE product_id = o.product_id
    AND valid_from <= o.placed_at::date
    AND valid_to   >  o.placed_at::date
    AND tx_to = 'infinity'                 -- current belief about that past state
  ORDER BY valid_from DESC
  LIMIT 1
) p ON true;

-- Snowflake / DuckDB native ASOF JOIN: "match the most recent price at or before placed_at".
SELECT o.order_id, o.placed_at, p.price_cents
FROM orders o
ASOF JOIN price_bt p
  MATCH_CONDITION (o.placed_at >= p.valid_from)
  ON o.product_id = p.product_id;

Step-by-step explanation.

1. The LATERAL subquery runs once per order and selects the single price row whose valid interval contains placed_at — valid_from <= t < valid_to — so each order sees the price as it was then, not the current price.
2. ORDER BY valid_from DESC LIMIT 1 makes the intent explicit even if intervals were merely valid_from-stamped: take the most recent version at or before the order instant.
3. tx_to = 'infinity' selects the current belief about that historical price; swapping in a transaction-time predicate would instead reproduce what a report believed at some past run — the two axes compose cleanly.
4. The native ASOF JOIN expresses the same "most recent match at or before" semantics in one clause via MATCH_CONDITION, which the engine optimises far better than a correlated subquery at scale.
5. The failure this prevents is joining orders straight to the current price row: order A placed in Q1 would wrongly show $12, silently rewriting history — the exact bug that corrupts revenue restatements and ML training labels.

Output.

Order	placed_at	Naive (current price)	As-of price
A	2024-02-15	1200 (wrong)	900
B	2024-05-10	1200	1200
correctness	—	history rewritten	point-in-time
scale	—	—	ASOF optimised

Rule of thumb. Join facts to dimensions with an as-of (point-in-time) join — the version whose validity interval contains the fact's instant — using a LATERAL ... ORDER BY valid_from DESC LIMIT 1 for portability or a native ASOF JOIN at scale. Never join a historical fact to the current dimension row; that retroactively rewrites the past.

Worked example — SCD2 valid-time windowing with LEAD

Detailed explanation. You receive dimension snapshots stamped only with valid_from; you need proper [valid_from, valid_to) intervals with no gaps or overlaps, emitting a version only when a tracked attribute changes. Build it with LEAD and change detection.

• The input. raw_tier(customer_id, tier, changed_at) snapshots.
• The window. LEAD(changed_at) per customer gives each version's end.
• The dedupe. Emit a row only when tier IS DISTINCT FROM the previous.

Question. Turn valid_from-only snapshots into non-overlapping SCD2 intervals, collapsing no-op changes.

Input.

Step	Technique
End of a version	LEAD(changed_at) per key
Last version	default to 'infinity'
Collapse no-ops	tier IS DISTINCT FROM lag(tier)
Interval shape	[valid_from, valid_to)

Code.

WITH deduped AS (   -- keep a row only when the tracked attribute actually changes
  SELECT customer_id, tier, changed_at
  FROM (
    SELECT customer_id, tier, changed_at,
           lag(tier) OVER (PARTITION BY customer_id ORDER BY changed_at) AS prev_tier
    FROM raw_tier
  ) s
  WHERE tier IS DISTINCT FROM prev_tier            -- drop consecutive duplicates
),
windowed AS (       -- derive valid_to from the NEXT version's start
  SELECT customer_id, tier,
         changed_at AS valid_from,
         COALESCE(
           lead(changed_at) OVER (PARTITION BY customer_id ORDER BY changed_at),
           'infinity'::timestamptz
         ) AS valid_to
  FROM deduped
)
SELECT * FROM windowed ORDER BY customer_id, valid_from;

-- Gap/overlap check: adjacent intervals must meet exactly (valid_to = next valid_from).
SELECT customer_id, valid_to,
       lead(valid_from) OVER (PARTITION BY customer_id ORDER BY valid_from) AS next_from
FROM windowed
QUALIFY valid_to <> next_from;   -- any row here is a bug (gap or overlap)

Step-by-step explanation.

1. The inner lag(tier) compares each snapshot to the previous one for the same customer; WHERE tier IS DISTINCT FROM prev_tier keeps only genuine changes, collapsing repeated identical snapshots into one version (and correctly handling NULLs via IS DISTINCT FROM).
2. In windowed, changed_at becomes each version's valid_from, and LEAD(changed_at) supplies the next version's start as this version's valid_to, producing contiguous half-open intervals.
3. COALESCE(lead(...), 'infinity') handles the final, still-open version by ending it at 'infinity' — the "current" row.
4. The half-open [valid_from, valid_to) convention means the boundary instant belongs to exactly one version, so an as-of join at precisely valid_to lands on the next version, never both.
5. The QUALIFY check asserts the invariant that each valid_to equals the following valid_from; any row it returns is a gap or overlap — a cheap, automatable data-quality test that catches windowing bugs before they reach a report.

Output.

customer	tier	valid_from	valid_to
7	silver	2023-01-01	2024-01-01
7	gold	2024-01-01	infinity
(no-op snapshot)	—	collapsed	—
gap/overlap check	—	returns 0 rows	valid

Rule of thumb. Build SCD2 intervals with LEAD(valid_from) to derive each valid_to, default the open version to 'infinity', and emit a version only when a tracked attribute IS DISTINCT FROM the previous. Always add a valid_to = next valid_from check — non-overlap and no-gaps is the invariant that makes as-of joins correct.

Worked example — a zone-aware, idempotent backfill

Detailed explanation. You must reprocess a year of history into local-day aggregates. Doing it by local calendar is non-deterministic across DST and non-idempotent; doing it by UTC partition with the correct historical offset is both. Build a backfill that overwrites whole UTC partitions.

• The unit. One UTC day per partition, reprocessed atomically.
• The conversion. Local day derived from the instant with the historical zone rule.
• The safety. Overwrite (delete+insert) the target partition so re-runs do not double-count.

Question. Backfill local-day revenue idempotently by reprocessing UTC partitions with correct historical offsets.

Input.

Concern	Wrong way	Right way
Partition unit	local day	UTC day
Offset	today's rule	historical rule (tzdata)
Re-run	append (double-count)	overwrite partition
Determinism	varies by DST	stable

Code.

-- Idempotent backfill of ONE UTC partition: delete-then-insert so re-runs don't double-count.
-- :d is a UTC date, e.g. DATE '2024-03-10' (a spring-forward day in the US).
BEGIN;

DELETE FROM agg_local_day_revenue
WHERE utc_partition = :d;                          -- clear the target partition first

INSERT INTO agg_local_day_revenue (utc_partition, local_day, region, revenue_cents)
SELECT :d AS utc_partition,
       -- local day from the instant using the HISTORICAL rule in tzdata for that zone:
       (occurred_at AT TIME ZONE region_zone)::date AS local_day,
       region,
       sum(amount_cents)                            AS revenue_cents
FROM fct_payments
WHERE occurred_at >= (:d)::timestamptz              -- [d 00:00Z, d+1 00:00Z): a whole UTC day
  AND occurred_at <  (:d + 1)::timestamptz
GROUP BY 2, 3;

COMMIT;

-- Reproducibility note: record the tz database version the run used, because DST rules
-- (and thus AT TIME ZONE results for historical dates) change between tzdata releases.
-- SELECT setting FROM pg_settings WHERE name = 'timezone_abbreviations';  -- + tzdata pkg ver

Step-by-step explanation.

1. The partition unit is a UTC day: the WHERE occurred_at >= d AND < d+1 bounds a fixed 24-hour instant range regardless of any local calendar, so the set of rows in a partition is deterministic and does not shift with DST.
2. DELETE ... WHERE utc_partition = :d before the insert makes the backfill idempotent — re-running the same UTC day overwrites rather than appends, so a retry after a failure cannot double-count.
3. The local day is derived with occurred_at AT TIME ZONE region_zone, which applies the DST rule in force on that historical date from tzdata — so a March 2007 date uses the pre-2007-rule offset if applicable, not today's.
4. Because a single UTC day can straddle two local days (and a spring-forward/fall-back day is 23/25 local hours), grouping by the derived local_day inside a UTC partition correctly attributes each instant, and adjacent UTC partitions together cover every local day exactly once.
5. The reproducibility caveat is real: tzdata is versioned and governments change DST rules, so a backfill re-run months later can differ unless you pin/record the tz database version — a subtle correctness detail senior engineers call out.

Output.

Property	Local-day backfill	UTC-partition backfill
Determinism across DST	no	yes
Idempotent re-run	double-counts	overwrites (safe)
Historical offset	often wrong	correct (tzdata)
Reproducible months later	drifts	pinned tzdata version

Rule of thumb. Backfill by whole UTC partitions with a delete-then-insert per partition so re-runs are idempotent, derive local calendars from the instant using the historical tzdata rule, and record the tz-database version. UTC partitioning makes reprocessing deterministic; the historical offset makes it correct.

Senior interview question on an end-to-end temporal architecture

A senior interviewer might ask: "Design the temporal backbone for a global analytics platform: how events are ingested and stored, how facts join to dimensions correctly for historical reporting, how slowly changing attributes are versioned, and how you backfill a year of history safely across DST — tying together UTC storage, bitemporal history, the date dimension, and the as-of/SCD/backfill patterns."

Solution Using UTC ingestion, a bitemporal store, a date dimension, and as-of serving

-- 1. Ingest: store the UTC instant + zone name; partition facts by UTC day.
CREATE TABLE fct_events (
    event_id   bigint,
    occurred_at timestamptz NOT NULL,          -- UTC instant
    origin_zone text NOT NULL,
    utc_day    date GENERATED ALWAYS AS ((occurred_at AT TIME ZONE 'UTC')::date) STORED
) PARTITION BY RANGE (utc_day);                 -- deterministic, idempotent backfill unit

-- 2. Dimensions are bitemporal/SCD2 with non-overlapping valid intervals (LEAD-windowed).
--    Facts join to the version valid at occurred_at — an AS-OF join, not the current row.
SELECT e.event_id, e.occurred_at, d.attr_value
FROM fct_events e
JOIN LATERAL (
  SELECT attr_value FROM dim_attr_scd
  WHERE key = e.entity_key
    AND valid_from <= e.occurred_at AND valid_to > e.occurred_at
  ORDER BY valid_from DESC LIMIT 1
) d ON true;

-- 3. Reporting joins the conformed date dimension for fiscal/ISO/business-day rollups.
SELECT dd.fiscal_period, count(*) AS events
FROM fct_events e
JOIN dim_date dd ON dd.date_key = to_char(e.occurred_at AT TIME ZONE 'America/New_York','YYYYMMDD')::int
GROUP BY 1;

-- 4. Backfill: reprocess whole UTC partitions idempotently, historical offsets from tzdata.
BEGIN;
  DELETE FROM agg_daily WHERE utc_day = :d;
  INSERT INTO agg_daily
  SELECT :d, (occurred_at AT TIME ZONE origin_zone)::date AS local_day, count(*)
  FROM fct_events
  WHERE utc_day = :d
  GROUP BY 2;
COMMIT;

Step-by-step trace.

Layer	Component	Responsibility
Ingest	timestamptz + zone, UTC partition	comparable instants, safe backfill unit
History	bitemporal / SCD2 dimensions	versioned truth, corrections
Join	as-of (LATERAL / ASOF)	point-in-time correctness
Reporting	conformed dim_date	fiscal / ISO / business-day rollups
Backfill	per-UTC-partition overwrite	idempotent, historically correct
Lateness	event time + watermark	bounded late-arriving handling

After deployment, every event is stored as a UTC instant plus its zone and partitioned by UTC day; facts join to the version valid at their instant via an as-of join, so historical reports are point-in-time correct; slowly changing attributes are LEAD-windowed into non-overlapping intervals; fiscal, ISO-week, and business-day rollups are joins to a conformed dim_date; and backfills reprocess whole UTC partitions with delete-then-insert and historical tzdata offsets, so a year of history can be reprocessed deterministically and idempotently even across DST transitions. Late-arriving events are handled as bitemporal inserts past the watermark rather than as silent drops.

Output:

Metric	Ad-hoc temporal handling	Temporal backbone
Cross-zone comparability	broken	exact (UTC instants)
Historical join correctness	current-row leakage	as-of point-in-time
Attribute versioning	overlaps/gaps	LEAD-windowed, checked
Fiscal/ISO reporting	re-derived per query	conformed dimension join
Backfill safety	double-counts, DST drift	idempotent, tzdata-correct

Why this works — concept by concept:

• UTC ingestion and partitioning — storing instants and partitioning by UTC day makes facts comparable across zones and gives backfills a deterministic, idempotent unit that never shifts with local calendars or DST.
• As-of point-in-time joins — attaching each fact to the dimension version valid at its instant keeps historical reporting and ML features correct, instead of leaking today's dimension values into yesterday's facts.
• LEAD-windowed SCD with checks — deriving valid_to from the next version and asserting no gaps or overlaps produces trustworthy version intervals, which is the precondition for as-of joins to be unambiguous.
• Conformed date dimension — routing fiscal, ISO-week, and business-day logic through one dim_date keeps every rollup consistent and turns calendar reporting into a join rather than re-derived arithmetic.
• Cost — UTC storage, one as-of join per fact, LEAD windowing, a dimension join, and per-partition backfills, versus a permanent stream of zone bugs, history-rewriting joins, and double-counting reprocesses. The eliminated cost is the entire class of temporal defects — O(1) correct-by-construction patterns instead of O(incidents) discovered downstream.

Time series
Topic — time-series
Time-series problems on as-of joins and point-in-time correctness

Practice →

Optimization
Topic — optimization
Optimization problems on windowing and backfill efficiency

Practice →

---

Cheat sheet — temporal data engineering

• Store UTC, convert at the edge. Every event is an instant stored in UTC (TIMESTAMPTZ / BigQuery TIMESTAMP) plus the origin zone name. Do all math and grouping in UTC; project to a local zone only for display or rollup. A genuine wall-clock intention (a scheduled local time) is stored naive plus a zone; durations are intervals.
• Offset ≠ zone. -05:00 is a frozen number; America/New_York is a rule set with a DST history (IANA/tzdata). Never persist "local time + offset" — you cannot recover the zone from an offset. Store the instant and the zone name; derive the offset on render.
• TIMESTAMPTZ vs TIMESTAMP. TIMESTAMPTZ stores a UTC instant and renders in the session zone (it does not keep a per-row zone); TIMESTAMP is a naive wall clock. AT TIME ZONE flips between them: on a tz value it yields local wall clock; on a naive value it yields the instant. Snowflake: TIMESTAMP_LTZ/TZ/NTZ. BigQuery: TIMESTAMP vs DATETIME.
• DST gap and overlap. Spring-forward makes a local hour not exist (23-hour day); fall-back makes one happen twice (25-hour day). Group time series by the UTC instant (date_trunc('hour', ts)), and label local only for display — grouping by local hour double-counts the overlap and invents the gap.
• Bitemporal = valid time + transaction time. [valid_from, valid_to) = when a fact was true; [tx_from, tx_to) = when the DB believed it. Corrections are close-and-insert (set old tx_to = now(), insert new), never overwrite — so "what is true now" and "what did we believe at S" are both as-of queries. SCD2 is the valid-time-only special case.
• As-of queries. Current belief: tx_to = 'infinity' AND valid_from <= d AND valid_to > d. Historical belief: tx_from <= S AND tx_to > S AND valid_from <= d AND valid_to > d. An exclusion constraint on daterange(valid_from, valid_to) where tx_to = 'infinity' keeps current beliefs non-overlapping.
• Date/calendar dimension. One row per day, yyyymmdd integer surrogate key, dense (no gaps), plus a sentinel 19000101 row for unknown/NULL fact dates. Precompute iso_year/iso_week, fiscal_year/fiscal_period/fiscal_qtr, is_weekend, is_holiday, is_business_day — reports join, never re-derive.
• ISO weeks & fiscal 4-4-5. ISO week starts Monday; week 1 contains the first Thursday; store iso_year (it diverges from the calendar year in Jan/Dec). 4-4-5: quarters of 13 weeks (4+4+5), 52-week years with occasional 53rd week, computed from a fiscal-year anchor — fiscal "months" never align to calendar months.
• Business days. Keep holidays in a maintained dim_holiday(date, region); derive is_business_day = NOT is_weekend AND NOT is_holiday. Net working days = SUM(is_business_day) over a range; "N business days after" = a window over ordered business days. Never recursive weekday math.
• As-of (point-in-time) join. Attach a fact to the dimension version valid at its instant — LATERAL (... WHERE valid_from <= t AND valid_to > t ORDER BY valid_from DESC LIMIT 1) or a native ASOF JOIN. Never join a historical fact to the current dimension row (it rewrites history and leaks ML labels).
• SCD2 windowing. Derive valid_to with LEAD(valid_from) per key (last = 'infinity'); emit a version only when a tracked attribute IS DISTINCT FROM the previous; assert valid_to = next valid_from (no gaps/overlaps) as a data-quality check.
• Zone-aware backfills. Reprocess by whole UTC partitions with delete-then-insert (idempotent); derive local calendars using the historical tzdata offset for the date; record the tz-database version (DST rules change between releases). Use event time for logic, processing time for arrival, and a watermark to bound lateness — late facts are bitemporal inserts, not drops.

Frequently asked questions

What is temporal data engineering?

Temporal data engineering is the practice of modelling, storing, and querying time correctly across a data platform — which is much harder than it looks because a timestamp can mean three different things (an absolute instant, a local wall-clock reading, or a duration) and each needs different handling. In practice it covers four disciplines: storing instants in UTC and converting to a time zone only at the edges (so cross-zone aggregates are comparable and DST transitions do not corrupt counts); modelling history bitemporally with separate valid-time and transaction-time axes (so corrections are auditable and past reports reproducible); building a calendar/date dimension that precomputes ISO weeks, fiscal periods, and holidays (so reporting joins instead of re-deriving fragile date math); and applying the recurring query patterns — point-in-time as-of joins, slowly-changing-time windowing, and zone-aware backfills. Getting these right at ingestion prevents an entire class of silent, downstream-only bugs.

TIMESTAMPTZ vs TIMESTAMP — which do I store?

Store TIMESTAMPTZ (Postgres) or TIMESTAMP (BigQuery) — the instant type — for anything that answers "when did this happen": events, logs, transactions. Despite the name, Postgres TIMESTAMPTZ does not keep a per-row time zone; it normalises the input to a UTC instant and renders it in the session zone, which is exactly what makes two events from different zones comparable. Use the naive type — TIMESTAMP without time zone (Postgres) or DATETIME (BigQuery), Snowflake TIMESTAMP_NTZ — only when the value is a genuine local intention like "a 9am meeting in the user's city," and then store the IANA zone name alongside it so you can resolve it to an instant when needed. The rule of thumb: instants → the tz/instant type in UTC; local intentions → naive plus a zone name; durations → intervals. Convert between instant and local wall clock with AT TIME ZONE (Postgres), CONVERT_TIMEZONE (Snowflake), or TIMESTAMP/DATETIME(..., zone) (BigQuery), always at the edge.

Why store UTC instead of local time?

Because UTC is the one representation that is unambiguous and comparable everywhere. A local time is meaningless without a zone, and even with a zone some local times do not exist (the spring-forward gap) or occur twice (the fall-back overlap), so arithmetic and grouping on local values silently drop or double-count rows. Storing the UTC instant means two events that happened at the same real moment compare equal regardless of where they originated, daily and hourly aggregates bucket correctly, and you can always project to any zone's local time on demand using the IANA rules. Crucially, you should also keep the origin zone name (not a numeric offset), because an offset is a frozen snapshot that cannot reconstruct future or past local times across DST changes, whereas the zone name carries the full rule history. Store UTC, keep the zone name, convert only at the display or rollup edge — that single discipline eliminates the majority of time-zone bugs.

What is a bitemporal table (valid time vs transaction time)?

A bitemporal table records every fact on two independent time axes. Valid time (valid_from/valid_to) is when the fact was true in the real world — "the price was $9 for all of Q1." Transaction time (tx_from/tx_to) is when your database believed it — "we recorded $9 on Jan 2 and superseded it on Apr 10." Keeping both lets you answer two different questions from one table: "what is true now" (current belief) and "what did we believe as of some past date" (which reproduces an old report exactly). The key operational difference from an ordinary table is that you never overwrite: a correction closes the old row's transaction-time interval (tx_to = now()) and inserts a new row, so the previous belief survives as auditable history. This is what makes bitemporal modelling essential for finance, pricing, and compliance — you can explain why a number changed and replay any past run. SCD Type 2 is the special case that tracks valid time only; adding transaction time is what buys you the audit.

Do I need a calendar / date dimension?

If you do any reporting by week, fiscal period, or business day, yes. A date dimension is a table with one row per calendar date, keyed by a yyyymmdd integer, that precomputes every calendar attribute once — ISO year and week, fiscal year/quarter/period (often on a 4-4-5 calendar), day-of-week, weekend, holiday, and business-day flags. The payoff is that fiscal, weekly, and business-day reporting becomes a JOIN on the date key instead of EXTRACT/CASE/holiday logic re-derived (and subtly mis-derived) in every query. It also conforms meaning across the whole platform: "fiscal Q3," "ISO week 11," and "a business day" mean exactly one thing in sales, finance, and ops because they come from a single tested table. Add a sentinel "unknown" row so NULL fact dates do not silently drop from inner joins, and keep holidays in their own maintained table feeding the is_business_day flag. For a small single-region app you might skip it; for any serious analytics platform it is standard.

How do I handle DST gaps and overlaps?

Do all arithmetic and grouping on UTC instants, and treat the gap and overlap as explicit edge cases. The fall-back overlap makes a local hour occur twice, so grouping a time series by local hour double-counts it; group by the UTC instant (date_trunc('hour', ts) on the timestamptz) and derive the local label only for display — the two 1am local hours become two distinct UTC buckets. The spring-forward gap makes a local hour never exist, so never construct a timestamp from a naive local value in the missing hour; work forward from instants instead. Remember that a "local day" is 23 or 25 hours on transition days, so define daily buckets by the instant range that maps to the local day rather than by adding 24 hours. And when backfilling historical data, convert instants to local time using the tzdata rule in force on that date (offsets and even DST rules change over time), reprocess by UTC partition for idempotency, and record the tz-database version for reproducibility.

Practice on PipeCode

• Drill the date & time practice library → for the UTC-storage, zone-conversion, and as-of query problems that make TIMESTAMPTZ, DST, and bitemporal reasoning concrete.
• Work the date functions practice library → and date arithmetic practice library → for the truncation, extraction, interval, and calendar-math primitives behind every date dimension and rollup.
• Rehearse windowed and rolling logic on the time-series practice library → for the point-in-time as-of joins and slowly-changing-time patterns that temporal pipelines live on.
• Stack the prerequisites against PipeCode's broader 450+ data-engineering catalogue to anchor the UTC/DST, bitemporal, date-dimension, and backfill patterns against real graded inputs — SQL across Postgres, Snowflake, and BigQuery.

Lock in temporal data engineering muscle memory

Docs explain `TIMESTAMPTZ` and `AT TIME ZONE`. PipeCode drills explain the decision — when to store the UTC instant versus a local intention, why grouping by the UTC instant defeats the DST fall-back double-count, when a `bitemporal` close-and-insert beats an overwrite, and when a `date dimension` join has to replace re-derived calendar math. Pipecode.ai is Leetcode for Data Engineering — temporal practice tuned for the production trade-offs senior data engineers actually face.

Practice date & time problems →
Practice time-series problems →