AdvancedSQL · 25 min

Date Arithmetic: Advanced

Date arithmetic at platform scale is the seam where data platforms get hardest to design well. The SQL is settled in the first two minutes; the rest of the interview is about bitemporal modeling, event-time versus processing-time, watermark design, calendar versioning, and out-of-order event handling. These are the failure modes that ship to production at billion-row scale and break the consumer's trust in the dashboard. The conversation is no longer about writing dates correctly; it is about designing the system that handles dates correctly under load.

Distinguish business time from system time and model bitemporal data accordingly

Design watermark strategies for date-aware incremental loads with late-arriving data

Version calendar tables with SCD-2 semantics for retroactive fiscal changes

Architect out-of-order event handling and reconciliation pipelines

Generating Date Spines and Filling Gaps

Daily Life

Interviews

Spot date arithmetic needs: "last 30 days," "same day last year," "business days only," "fiscal quarter."

The interviewer at this depth rarely asks 'compute monthly revenue.' They ask 'design the platform that produces monthly revenue, where transactions can arrive out of order from three upstream systems, the company restructured its fiscal calendar mid-year, the regulator requires us to be able to reproduce any historical report exactly as it was originally produced, and the dashboard refreshes every fifteen minutes.' Each clause in that question hides a date-arithmetic decision that the platform team owns. Bitemporal models for the regulatory reproduction. Watermark policies for the out-of-order events. Versioned calendars for the fiscal restructure. Incremental refresh patterns for the fifteen-minute cadence.

The four platform-level date concerns

Four concerns recur across every platform-team conversation about dates. First: bitemporal modeling. Every row in a reporting table has two times: when the event happened in the business (business time) and when the system observed it (system time). Reports that confuse the two produce restated numbers without anyone noticing. Second: watermarking. Date-aware incremental pipelines decide how to handle data that arrives after the watermark; the choice between dropping, reprocessing, or holding is a design call with operational consequences. Third: calendar versioning. Fiscal calendars change. The query that ran correctly last year may produce wrong numbers this year because the company restructured. SCD-2 on the calendar table is the discipline. Fourth: out-of-order events. Streaming pipelines and CDC streams emit events that arrive late or out of sequence; the platform decides how to incorporate them into already-published aggregates.

business time vs system time. Required for audit reproducibility and regulated reporting.
event-time vs processing-time watermarks; lateness tolerance is the contract with downstream.
SCD-2 on dim_calendar so fiscal restructures preserve historical reproducibility.
policy choice: republish, arrival-period, corrections, drop. Each fits a different workload.

You are being tested on platform-grade date design when you hear:

▸"reproduce the report as it was on date X"
▸"the upstream sends events out of order; how do we handle it"
▸"the fiscal calendar changed mid-year; the old reports should still be correct"
▸"late arrivers in this hourly aggregate; what's the policy?"
▸"event time versus processing time; pick one and defend it"
▸"how do we audit a dashboard reading from last quarter"

The seam between event time and processing time

Event time is when something happened in the world. Processing time is when the system observed it. These can be hours, days, or weeks apart for late-arriving data. A transaction that happened on January 31 but was inserted into the warehouse on February 3 has event time = January 31 and processing time = February 3. Aggregates by event time put it in January's total; aggregates by processing time put it in February's. Both are correct depending on the question; mixing them in the same query produces wrong numbers.

•Aggregate by event time when

Reporting numbers must match the business reality (transactions per calendar day they actually happened)
Late arrivers need to update historical totals
The consumer is finance, reconciling against external records
Reports are restated as late data arrives

✓Aggregate by processing time when

Reporting numbers must be stable once published
The consumer is operations, tracking system throughput
Historical totals never change; the published number is the published number
Audit and reconciliation must be reproducible to a point-in-time

Why these matter at platform scale

Each of the four concerns produces a specific failure mode at scale. Confusing event time and processing time produces dashboards that disagree with finance reconciliations. Watermarks set too aggressively drop late-arriving data; set too conservatively, they delay every downstream consumer. Calendar versioning failures produce restated historical reports without an audit trail. Out-of-order handling failures produce double-counts or omissions in already-published aggregates. The staff conversation moves between these concerns constantly; the candidate at this level is reading each question for which concern it touches and naming the design choice.

Calendar Tables and Holiday-Aware Math

Daily Life

Interviews

Write date manipulation using the three core functions and handle dialect differences (DATEADD vs INTERVAL).

Bitemporal data modeling is the technique for reporting tables that must be reproducible. Every row carries two times: business_time (when the event happened in the business) and system_time (when the row was inserted into the warehouse). The model lets the platform answer two distinct questions: what is true about the business as of business time T (the as-of-business-time query), and what did the platform believe was true as of system time S (the as-of-system-time query).

The bitemporal table

	'CREATE TABLE fct_transactions (
	transaction_id BIGINT,
	customer_id BIGINT,
	amount NUMERIC,
	business_time TIMESTAMPTZ,
	system_time TIMESTAMPTZ,
	is_current BOOLEAN,
	superseded_at TIMESTAMPTZ
	)'
	/* A bitemporal fact table with business and system time */
	/* when the transaction occurred */
	/* when the row was inserted */
	/* TRUE if this is the latest version */
	/* NULL if current; set when corrected */

The two reproducibility queries

Two query shapes operate on a bitemporal table. The as-of-business-time query asks: 'what was the transaction's correct value at business time T?' This is the standard reporting query, with corrections applied. The as-of-system-time query asks: 'what did the system believe was the transaction's value as of system time S?' This is the reproducibility query, used for audit and regulatory reporting. The two queries can produce different numbers for the same transaction if a correction was applied between the business event and the audit date.

The two reproducibility queries:

▸As-of-business-time: 'what was true on date X' (uses is_current = TRUE)
▸As-of-system-time: 'what did the system believe on date X' (uses system_time bounds)
▸Both queries answer different questions on the same data
▸Audit reproduction uses the second; standard reporting uses the first

	SELECT *
	FROM fct_transactions
	WHERE business_time :: DATE = '2024-01-15' AND is_current = TRUE ;


	SELECT *
	FROM fct_transactions
	WHERE business_time :: DATE = '2024-01-15' AND system_time <= '2024-01-31' AND(superseded_at IS NULL OR superseded_at > '2024-01-31') ;

The audit use case

Regulated industries (financial services, healthcare, ad tech) must reproduce historical reports exactly. A regulator asks: 'show me the January report exactly as it was when you published it on February 5, 2024.' The as-of-system-time query with system_time <= '2024-02-05' returns the data the system held at that moment, before any subsequent corrections. The bitemporal model is what makes this reproduction possible; without it, every correction overwrites history and the reproduction is impossible.

At Plaid in 2022, the data platform team designed the financial reporting tables as bitemporal from day one because the company knew regulator reproducibility would matter once revenue crossed certain thresholds. The decision added complexity to every pipeline (every write went through a 'supersede the old, insert the new' pattern), but two years later when the first regulator inquiry came in, the team produced the audit reports in days instead of months. The design call was: pay the complexity tax in writes to amortize the future audit cost. The runbook line was 'every fact table that contributes to a regulated metric is bitemporal; the audit-time reproducibility tax is the table's contract.'

Maintaining the bitemporal table

Corrections to a bitemporal table do not update in place. They insert a new row with the corrected values and a new system_time, and they supersede the old row by setting its superseded_at and clearing its is_current. The pattern: the new row becomes current; the old row is preserved as history. The platform never overwrites; it always appends and supersedes. State this when describing the design: 'every write to a bitemporal table is an insert plus a supersede; we never update in place. The history is always available because nothing is destroyed.'

Cost of bitemporal modeling

Bitemporal tables grow faster than their equivalent unitemporal tables because every correction adds a row. The growth factor is roughly 1 + correction_rate; for a table with 5% corrections, the bitemporal version is 5% larger than the as-of-current version. Storage is cheap; the platform pays the storage tax to gain reproducibility. The query cost is higher because most queries need is_current = TRUE filters, which add a predicate to every read. The cost is justified for tables that need audit trails; it is unjustified for tables that don't. State the trade-off: 'bitemporal is for audit-requiring tables, not every table; the storage and query overhead is real and only worth paying for the audit use case.'

Timezone-Correct Cohorting at Scale

Daily Life

Interviews

Pull date parts with EXTRACT, handle fiscal year offsets, and explain ISO week numbering.

Date-aware incremental pipelines need a watermark: the boundary that separates 'data we have already processed' from 'data still to process.' The watermark is conceptually simple but operationally hard because the choice between event time and processing time watermarks determines how the platform handles late-arriving data. The platform decision is which time axis the watermark advances on, and how late the platform tolerates data being before it is dropped.

Event-time vs processing-time watermarks

A processing-time watermark advances with wall-clock time. At hour T, all events processed before hour T are in; all events processed at or after hour T are not yet. This watermark is monotonic and simple; late-arriving data is included in the next hour's batch regardless of its event time. The dashboards aggregate by event time but the watermark advances on processing time; late data updates historical totals when it arrives. An event-time watermark advances with the largest event_time seen so far. The platform decides that no more events with event_time < watermark are expected, and any that do arrive after the watermark has advanced are 'late' and may be dropped. This watermark gives stable aggregates per window but requires the platform to commit to a lateness threshold.

The lateness threshold

	/* An hourly aggregate with an event-time watermark and 1-hour lateness tolerance */
	/* Pseudo-pattern: the watermark advances when (max(event_time) - 1 hour) clears the window boundary */
	SELECT
	DATE_TRUNC('hour', event_time) AS event_hour,
	COUNT(*) AS event_count,
	SUM(amount) AS hourly_revenue
	FROM events
	WHERE event_time >= '2024-01-15 10:00 UTC'
	AND event_time < '2024-01-15 11:00 UTC'
	GROUP BY /* For the watermark to advance past 11:00, we need to see event_time >= 12:00 / / arriving (after which events with event_time < 11:00 are considered late) */ DATE_TRUNC(
	'hour',
	event_time
	)

The lateness threshold is the design call. 'We tolerate up to 1 hour of lateness' means the platform holds the 10:00 hour's aggregate open until events with event_time >= 12:00 arrive (indicating that 11:00 has fully drained). 'We tolerate up to 24 hours of lateness' means the platform holds for a day. Longer thresholds give more correct aggregates but delay every downstream consumer; shorter thresholds publish faster but may drop or reprocess data. State the choice in terms of the business: 'we use 6-hour lateness because the dashboard's freshness requirement is 6 hours; data more than 6 hours late is reprocessed in a daily correction job.'

Handling late data

Three policies for events that arrive after the watermark. Policy one: drop. The event is too late to include in any aggregate; the dashboard accepts the small loss. This is the simplest policy and the right call for high-volume telemetry where individual events do not matter. Policy two: reprocess. The aggregate window is reopened, the late event is included, and the aggregate is republished. This is the right call for financial data where every transaction must be counted. Policy three: separate corrections pipeline. Late events are routed to a corrections table; a separate batch job reconciles the corrections against the published aggregates daily. This is the right call when the volume of late events is low and audit trail matters.

high-volume telemetry; per-event accuracy less important than dashboard freshness.
financial transactions; every event must be counted; historical totals restate.
audit-requiring tables; main pipeline publishes stable, corrections reconcile daily.

•Drop late data when

Per-event accuracy is less important than dashboard freshness
The volume of late events is low (telemetry, click streams)
Recomputing the aggregate is more expensive than the lost signal
The consumer is operations or product, not finance

✓Reprocess or correct when

Per-event accuracy is critical (financial transactions, regulated reporting)
The volume of late events is high enough to affect totals materially
The downstream consumer requires reconcilable totals
The platform can afford the operational cost of republishing aggregates

Streaming engines and watermarks

Spark Structured Streaming, Flink, and Beam all implement watermark semantics with engine-specific syntax. The concepts are the same across engines; the API names differ. .withWatermark('event_time', '1 hour') on Spark sets the lateness tolerance. WATERMARK FOR event_time AS event_time - INTERVAL '1' HOUR on Flink does the same. Naming the engine and the watermark policy is the move: 'this uses Spark with a 1-hour event-time watermark; late events past 1 hour are dropped and a daily corrections job catches them.'

Out-of-order events are not bugs in the source; they are a property of distributed systems. Multiple producers, network delays, retries, and clock skew all produce events that arrive after later-event-time events. The platform's policy is the contract with the consumer; making the policy explicit is the move.

Partition Pruning on Date Columns

Daily Life

Interviews

Convert between UTC and local time, explain why comparing timestamps across zones requires explicit conversion.

Fiscal calendars are not static. Companies restructure their fiscal years (Apple moved its fiscal year-end from June to September in the 1980s and again later; many retailers shift to 4-4-5 calendars as they grow). When the calendar changes, the historical reports under the old calendar are still valid for the period they cover; the new calendar applies only going forward. The naive approach (update the calendar table in place) breaks historical reproducibility. The right approach is a versioned calendar with SCD-2 semantics.

The SCD-2 calendar table

	CREATE TABLE dim_calendar_versioned(DATE DATE, fiscal_year INT, fiscal_quarter INT, fiscal_month INT, is_business_day BOOLEAN, effective_from TIMESTAMPTZ, effective_to TIMESTAMPTZ, is_current BOOLEAN) ;


	SELECT *
	FROM dim_calendar_versioned
	WHERE DATE = '2024-03-15' AND effective_from <= '2024-04-01' AND(effective_to IS NULL OR effective_to > '2024-04-01') ;

How the fiscal restructure is recorded

When the fiscal calendar changes, the platform inserts new rows into dim_calendar_versioned with new effective_from dates and supersedes the old rows by setting their effective_to. Historical queries that reference an older effective_from still find the old mapping; queries for current and future dates find the new mapping. The same calendar table serves both historical reproducibility and current reporting; the version filter is what selects the right one.

How SCD-2 calendar versioning preserves audit:

▸New rows inserted with new effective_from
▸Old rows superseded by setting effective_to
▸Historical queries with report_date find old mapping
▸Current queries find new mapping
▸Same table serves both; the version filter selects

The composite key trick

Some calendar tables avoid SCD-2 in favor of a composite key (date, version_id). Each version is a complete copy of the calendar; queries reference the version explicitly. This is simpler than SCD-2 (no effective_from/to columns to maintain) but uses more storage (every version replicates the full calendar). For calendars that change rarely (once every few years), the composite-key approach is fine. For calendars that change often (small companies that revise their fiscal model annually), SCD-2 is more efficient.

Joining to the versioned calendar

	SELECT
	cal.fiscal_year,
	cal.fiscal_quarter,
	SUM(t.amount) AS revenue
	FROM transactions t
	JOIN dim_calendar_versioned cal
	ON cal.date = t.txn_date :: DATE AND cal.effective_from <= : report_date AND(cal.effective_to IS NULL OR cal.effective_to > : report_date)
	GROUP BY cal.fiscal_year, cal.fiscal_quarter ;

The join now has two predicates per row: the date match and the effective-version filter. The report date parameter selects which version of the fiscal mapping applies. A query running on April 1, 2024 with :report_date = '2024-04-01' uses whatever fiscal mapping was effective on that date; a query running on June 1 with :report_date = '2024-04-01' (asking for a historical reproduction) still uses the April-1 effective version. State this when designing the platform: 'every reporting query that joins dim_calendar carries a report_date parameter; the version filter is what makes historical reports reproducible.'

The audit reproducibility property

Combine bitemporal facts with versioned calendars and the platform has full audit reproducibility. The fact table's system_time captures what the system knew; the calendar's effective_from captures what fiscal mapping was applied. To reproduce a historical report, set the report_date to the publication date; both filters apply the version of the data and the version of the calendar that were live at that moment. This is the design that lets a regulator ask 'show me the report as you published it on February 5' and the platform produces the exact answer. Without versioning, the report cannot be reproduced because the calendar (or the data) has moved on since publication.

•Update-in-place calendar

Simplest to maintain; one row per date
Historical queries use the current fiscal mapping for old dates
Reports run today against old dates produce different numbers than reports run last year
No audit trail of fiscal changes; restructures are lost

✓Versioned (SCD-2) calendar

Multiple rows per date over time; effective ranges via from/to columns
Historical queries use the fiscal mapping that was effective at the report date
Reports run today against old dates produce the same numbers as the original reports
Full audit trail of fiscal changes; restructures are preserved

Calendar versioning is rare in startups and common in enterprises. The platform team's decision to add versioning is a forward bet that fiscal changes will happen and audit reproducibility will matter. The bet pays off the first time the company restructures or the first time a regulator asks for a reproduction. State the trade-off when designing: 'versioning adds complexity in exchange for future-proofing against fiscal changes and audit requirements; the right call depends on company stage and regulatory exposure.'

Storing UTC vs Local: The Design Call

Daily Life

Interviews

Create a date spine using recursive CTE or GENERATE_SERIES and LEFT JOIN to fill calendar gaps in sparse data.

The most operationally difficult class of date-arithmetic problems in production: out-of-order events. A transaction with event_time = January 31 arrives in the warehouse on February 3; the January report has already published; the February pipeline is running. Where does the January 31 transaction land? Whose totals get restated? How does the platform handle the discrepancy? These are the failure modes the interviewer is reading the question for.

Four policies for out-of-order events

Policy one: include retroactively, republish. The platform reopens the January aggregate, includes the late transaction, and republishes the January number. Consumers see a restated value. This is the right call for financial reporting where the historical number must reflect business reality. Policy two: include in the arrival period. The transaction is counted as part of February's aggregate (because it arrived in February). The historical January number is stable, but February's number includes a late-January transaction. This is the right call for operational metrics where the published number must not change after publication. Policy three: separate corrections table. Late transactions are routed to a corrections table; a reconciliation job runs daily to align corrections with the canonical aggregates. This is the right call when both historical accuracy and publication stability matter. Policy four: drop. The transaction is too late to include in any aggregate; the platform accepts the loss. This is the right call for high-volume telemetry where individual events do not affect downstream decisions.

Late event arrives Feb 3 with event_time Jan 31January aggregate reopenedNew transaction included in January totalJanuary number republished, restating the prior valueConsumers see the restatement (they must handle it)

•Republish (policy 1)

Historical totals are restated when late data arrives
Right for financial reporting and reconciliation against external sources
Consumers must handle restatement
Downside: published numbers are not stable; cached dashboards drift

✓Include in arrival period (policy 2)

Historical totals are immutable once published
Right for operational metrics and SRE dashboards
Consumers see stable historical numbers
Downside: arrival-period totals include events that did not happen then; harder to reconcile

Implementing policy 1 with bitemporal data

Bitemporal modeling makes policy 1 natural. A late transaction is inserted with its true event_time as business_time and the current wall-clock as system_time. The is_current row for that business_time is superseded; the new row becomes current. As-of-business-time queries pick up the new row; as-of-system-time queries against the old system_time still show the old number. Reports run before the late arrival have stable numbers via system_time; reports run after restate the business_time number. Both reproducibility paths are preserved.

Implementing policy 2 with append-only facts

Policy 2 is the default for append-only fact tables. The late event is inserted with its event_time and a processing_time set to wall-clock; downstream aggregates run on processing_time so the event lands in the arrival period. Historical aggregates on event_time are not recomputed because the dashboard reads them by processing_time. The trade-off: business consumers asking 'what was January revenue?' see a number that excludes January 31's late transaction. The platform's documentation has to be clear about which time axis the dashboard uses.

The reconciliation pipeline (policy 3)

Policy 3 splits the workload. The main aggregate pipeline runs on processing_time and produces stable published numbers. A separate corrections pipeline runs daily, identifies events whose event_time is in a prior published window, and produces a corrections report. The two outputs are reconciled in the BI layer: 'as-published value' (from the main pipeline) and 'reconciled value' (from the main plus corrections). Consumers see both numbers and can pick which one to trust. This is the most operationally complex policy but produces the most flexible output.

Closing call-back: the question from s0

Return to the question that opened the lesson: 'design the platform that produces monthly revenue, where transactions arrive out of order from three upstream systems, the fiscal calendar changed mid-year, the regulator requires historical reproducibility, and the dashboard refreshes every fifteen minutes.' The full answer integrates everything: bitemporal facts for regulator reproducibility, an event-time watermark with a 24-hour lateness tolerance for out-of-order handling, an SCD-2 calendar for the fiscal change, an incremental refresh pipeline with reconciliation for the fifteen-minute cadence. The query that produces the monthly number is eight lines; the platform behind it is the rest of the conversation. The conversation moves between SQL, schema, pipeline policy, and audit trail; the depth that gets remembered at the debrief is which layers you name.

❯❯❯PUTTING IT ALL TOGETHER

> You are in a data engineering interview at a regulated fintech. The interviewer asks: 'Design the platform that produces monthly revenue, where transactions arrive out of order from three upstream sources, the fiscal calendar restructured in Q3, the regulator requires historical reproducibility, and the dashboard refreshes every fifteen minutes.'

You frame the four concerns: bitemporal facts for audit, watermarking for out-of-order, SCD-2 calendar for the fiscal restructure, incremental refresh for the cadence. Each is a deliberate platform-design choice.

Bitemporal: fact table has business_time and system_time; corrections supersede via is_current and superseded_at. Audit queries filter by system_time; business queries filter by is_current.

Watermarking: event-time watermark with 24-hour lateness tolerance. Events past 24 hours route to a corrections pipeline that runs daily.

Calendar: dim_calendar_versioned with effective_from/effective_to. Joins use the report_date parameter to pick the right fiscal version. Historical reports reproduce because the version filter selects the calendar that was effective at publication.

Refresh: incremental pipeline reads since the last watermark; the corrections pipeline runs in parallel; both write to bitemporal facts.

Follow-up: 'What's the policy for late arrivers past 24 hours?' You say: 'Corrections pipeline. Routes to a corrections table; daily reconciliation produces an 'as-published vs reconciled' view; consumers pick which to trust based on their use case.'

Closing: 'The query that produces the monthly number is eight lines. The platform behind it is bitemporal modeling, watermark policy, calendar versioning, and reconciliation, each defended by a specific business or regulatory requirement.'

KEY TAKEAWAYS

Every reporting row carries two times: business time, when the event happened, and system time, when the platform observed it. A transaction dated January 31 and loaded February 3 belongs to January by business time and to February by processing time; mixing the axes in one query produces numbers that disagree with finance.

Bitemporal tables never update in place. A correction inserts a new row and supersedes the old by setting superseded_at and clearing is_current. That is what lets you answer a regulator asking for the January report exactly as published on February 5, at a storage cost of roughly 1 plus the correction rate.

The lateness threshold is a business decision, not a default. Six hour tolerance means the window stays open six hours before publishing; longer is more correct and delays every consumer, shorter publishes faster and drops or reprocesses data.

Pick one late data policy and state it: drop for high volume telemetry, reopen and republish for financial data where every transaction must be counted, or route late events to a separate corrections pipeline reconciled in the BI layer as as-published versus reconciled.

Version the calendar with SCD-2 effective_from and effective_to columns and pass a report_date into every join, because a fiscal restructure updated in place destroys historical reproducibility.

The integrated answer to the opening question is bitemporal facts for reproducibility, an event time watermark with an explicit lateness tolerance for out of order arrivals, an SCD-2 calendar for the fiscal change, and an incremental refresh with reconciliation for the fifteen minute cadence.

Every data engineering question involves dates; most candidates fumble timezone math

Category: SQL
Difficulty: advanced
Duration: 25 minutes
Challenges: 0 hands-on challenges

Topics covered: Generating Date Spines and Filling Gaps, Calendar Tables and Holiday-Aware Math, Timezone-Correct Cohorting at Scale, Partition Pruning on Date Columns, Storing UTC vs Local: The Design Call

Lesson Sections

Generating Date Spines and Filling Gaps (concepts: sqlDateVsTimestamp)
The four platform-level date concerns Four concerns recur across every platform-team conversation about dates. First: bitemporal modeling. Every row in a reporting table has two times: when the event happened in the business (business time) and when the system observed it (system time). Reports that confuse the two produce restated numbers without anyone noticing. Second: watermarking. Date-aware incremental pipelines decide how to handle data that arrives after the watermark; the choice between
Calendar Tables and Holiday-Aware Math (concepts: sqlTimestampType)
The bitemporal table The two reproducibility queries Two query shapes operate on a bitemporal table. The as-of-business-time query asks: 'what was the transaction's correct value at business time T?' This is the standard reporting query, with corrections applied. The as-of-system-time query asks: 'what did the system believe was the transaction's value as of system time S?' This is the reproducibility query, used for audit and regulatory reporting. The two queries can produce different numbers f
Timezone-Correct Cohorting at Scale (concepts: sqlExtract)
Date-aware incremental pipelines need a watermark: the boundary that separates 'data we have already processed' from 'data still to process.' The watermark is conceptually simple but operationally hard because the choice between event time and processing time watermarks determines how the platform handles late-arriving data. The platform decision is which time axis the watermark advances on, and how late the platform tolerates data being before it is dropped. Event-time vs processing-time waterm
Partition Pruning on Date Columns (concepts: sqlComplexPatterns)
Fiscal calendars are not static. Companies restructure their fiscal years (Apple moved its fiscal year-end from June to September in the 1980s and again later; many retailers shift to 4-4-5 calendars as they grow). When the calendar changes, the historical reports under the old calendar are still valid for the period they cover; the new calendar applies only going forward. The naive approach (update the calendar table in place) breaks historical reproducibility. The right approach is a versioned
Storing UTC vs Local: The Design Call (concepts: sqlRecursiveCte)
The most operationally difficult class of date-arithmetic problems in production: out-of-order events. A transaction with event_time = January 31 arrives in the warehouse on February 3; the January report has already published; the February pipeline is running. Where does the January 31 transaction land? Whose totals get restated? How does the platform handle the discrepancy? These are the failure modes the interviewer is reading the question for. Four policies for out-of-order events Policy one