Storing UTC vs Local: The Design Call

Concepts covered: 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: 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: inc

About This Interactive Section

This section is part of the Date Arithmetic: Advanced lesson on DataDriven, a free data engineering interview prep platform. Each section includes explanations, worked examples, and hands-on code challenges that execute in real time. SQL queries run against a live database. Python runs in a sandboxed Docker container. Data modeling problems validate against interactive schema canvases. All content is framed around what data engineering interviewers actually test at companies like Meta, Google, Amazon, Netflix, Stripe, and Databricks.

How DataDriven Lessons Work

DataDriven combines four interview rounds (SQL, Python, Data Modeling, Pipeline Architecture) with adaptive difficulty and spaced repetition. Easy problems get harder as you improve. Weak concepts resurface until you master them. Your readiness score tracks progress across every topic interviewers test. Every lesson section ends with problems you solve by writing and running real code, not by picking multiple-choice answers.