Introduction
Why did I want integers instead of calendar dates?
My main goal when looking for an alternative to classic DATE columns (like valid_from and valid_to) in SCD2 tables was pure query and storage performance. Modern SQL engines like Databricks run historical time-travel queries using the BETWEEN operator. When you compare calendar dates, the database wastes precious CPU time decoding year, month, and day structures for every single row.
By switching to LONG INTEGER / BIGINT, checks happen natively at the processor level in just one clock cycle (1T). On top of that, Parquet files store MIN and MAX values in their metadata for each row group.
With clean integers, the engine can just read these tiny metadata bits and instantly skip files that don't match what you're looking for (Data Skipping), without wasting time reading gigabytes of useless data from the disk.
The First School of Thought: The YYYYMMDD Format (Pros & Cons)
At first, the obvious idea was to turn dates into simple integers using the visual YYYYMMDD format (for example, today would become the number 20260814). The clear upside is that it stays super readable for humans at a glance and it’s incredibly easy to generate in any ETL script. However, the major downside comes from how the math works in this format.
Time is no longer continuous; instead, you get these huge numerical "gaps." For instance, between the end of the year 20261231 and the first day of the next year 20270101, there’s a mathematical jump of 8,870 units, even though in reality, only a single day has passed. These artificial gaps ruin Parquet's binary compression efficiency and stretch out the MIN/MAX boundaries in the metadata. Because of this, BETWEEN queries end up scanning empty files, thinking those gaps contain actual data, which completely destroys the speed boost we were looking for.
The acronym SCD stands for Slowly Changing Dimensions. In databases and data warehouses, this is the standard concept used to manage and save the history of data that changes over time, but not very often (like a customer’s name, address, or phone number).
In an SCD2 table, to flag records that aren't closed yet—and will probably close at some point—we use a calendar date far out in the future, like January 1st, 5999. Since we don't have a universal symbol to semantically mean "infinity," we use a default placeholder value.
This is where I connected it to the Riemann Sphere, where the North Pole is mapped to the radian value of the transcendental number pi (3.14159...), which in this model represents infinity.
It’s important to say "in this model" because, on a standard Riemann Sphere, infinity is just a geometric point. The fact that we mapped it exactly to the pi angle in radians (on our semicircle) is just our own clever convention for calibrating time.
The Math Concept: The Riemann Sphere and Semicircle
In pure math, the Riemann Sphere (stereographic projection) is a geometric model used to map an infinite plan onto the surface of a 3D sphere, all to control and calculate the value of infinity.
When it comes to data storage, we simplify this model into a 2D unit semicircle (an arc ranging from 0 to pi radians). Here, the linear time axis is geometrically bent to create a closed and controlled metric space:
The South Pole (0 radians): Represents Point Zero—the absolute origin or the oldest reference date in the system (e.g., 1960-01-01).
The North Pole (pi, approx 3.14159... radians): Represents Infinity—the convergence point where all active time intervals meet and stop.
The Business Context: SCD Type 2 History Tracking
In modern data warehouses and lakehouses, dimension tables can grow to massive sizes. They use the SCD Type 2 (Slowly Changing Dimensions) algorithm to keep a full history of entity changes (like customers).
The system tracks each customer's state over continuous time intervals.
Every row has two native date columns: valid_from and valid_to.
These columns precisely lock down the duration (the closed interval) during which the customer's attributes didn't change at all.
Moving to the Riemann Semicircle: The Infinity Problem
In classic SCD2 modeling, a customer's current row (the one active right now) doesn't have a known end date. To handle this, data engineering uses a technical workaround (a hack): they flag the valid_to column with an arbitrary date far out in the future (e.g., 5999-01-01).
This future date is just a flat representation of infinity. The conceptual link to the Riemann Sphere happens when we swap this arbitrary date with a fixed angular value at the North Pole (pi radians).
Instead of time stretching out linearly over thousands of fictional years, it climbs up a circular arc where the value 5999-01-01 geometrically collapses right at the top of the semicircle, getting the fixed value of pi. Basically, for every calendar day between 1960-01-01 and 5999-01-01, we calculate the angle theta in radians. We are doing a mathematical transformation between calendar days and infinity.
Every single calendar day in the SCD2 table gets a unique theta angle on this arc, and this angle stays strictly within the [0, pi] range.
On this vertical axis, to respect the standard mathematical direction (counter-clockwise), time moves up along the RIGHT side of the circular arc.
The Start (0 radians): We start from the bottom, at the South Pole (6 o'clock position), which represents the date 1960-01-01.
The Transit (The Equator): Time moves forward by climbing up the right side of the circle (passing the 3 o'clock position), covering the years 2020, 2022, and today, 2026.
The Anchor (π radians): All current, active records climb all the way to the top and stop permanently at the North Pole (12 o'clock position), where infinity lives (5999-01-01).
Time never goes backward and it never uses the left half of the circle. It just climbs up in a neat, orderly way, like a continuous slope on the right side from the South Pole (0) to the North Pole (pi).
In this Riemann-inspired solution, we've turned time into a monotonically increasing angle (stable geometric steps). When data is written onto the right semicircle, customers whose records closed in the past get small, tightly packed numbers (e.g., 4838409093), while active customers all get the exact same fixed value at the very top: 314159265359.
When Parquet organizes these integers on the disk, it groups similar numbers into separate Row Groups, because the numbers grow naturally and don't have those massive thousands-of-years gaps like the YYYYMMDD format does.
The Technical Implementation (12-Digit PI as a BIGINT)
To completely get rid of floating-point numbers (FLOAT/DOUBLE) which cause hardware rounding errors (IEEE 754), the radian angle on the arc is multiplied by a scaling factor of 10^11 (a 1 followed by 11 zeros).
When a record is currently active (at infinity), its angle is exactly pi radians. The infinity value at the North Pole becomes a clean integer, expressed as the first 12 digits of pi: 314159265359.
For any other calendar day, it’s a simple fraction: we calculate what percentage of the total time has passed, and then multiply that percentage by the maximum angle value (pi * 10^11).
Defining the model as a linear projection on a semicircle (half a circle) gives a massive advantage to Parquet's storage algorithm. Since the theta angle grows strictly linearly and in perfect order day by day (without looping back to the other side of the circle or making 3D jumps), your table values are monotonically increasing.
Columnar formats like Parquet love this and optimize data storage heavily. A column with monotonically increasing numbers lets compression algorithms work perfectly.
The Ingestion Formula (Calendar Date / DATE -> Long Integer / BIGINT)
To map any calendar date onto this semicircle, we use a simple rule of three based on the total number of days between the start of history (1960-01-01) and infinity (5999-01-01).
To make sure both ends of the interval are properly included (so that a row valid for just one single day has a duration of exactly 1 geometric step), we strictly apply the formula DATE_DIFF + 1.
Base values for the calculation formulas:
South Pole (0 radians): DATE '1960-01-01'
North Pole (Infinity): DATE '5999-01-01', which is our constant 314159265359
Denominator (DATE_DIFF + 1): 1475216 (the number of days + 1 between the North and South Pole)
SELECT (DATE '5999-01-01' - DATE '1960-01-01') + 1 AS day_difference FROM dual;
The value of a single day (The step on the arc): 212958
The calculation formula:
-- Ingestion START (Milestone 1960-01-01, denominator 1475216) CAST(ROUND( (DATE_DIFF('day', DATE '1960-01-01', valid_from) + 1) * (314159265359.0 / 1475216) ) AS BIGINT) AS riemann_valid_from -- Ingestion END CASE WHEN valid_to = DATE '5999-01-01' OR valid_to IS NULL THEN 314159265359 ELSE CAST(ROUND((DATE_DIFF('day', DATE '1960-01-01', valid_to) + 1) * (314159265359.0 / 1475216)) AS BIGINT) END AS riemann_valid_to
Architectural Advantages in Modern Ecosystems
Optimizing I/O via Data Skipping (Tight Metadata)
In Parquet files, every single Row Group saves the MIN and MAX values of each column right in its metadata.
The classic problem: The linear value 5999-01-01 artificially stretches out the MAX value of almost every file, forcing the query engine (Trino) to scan entire blocks of data for no reason.
The Riemann solution: Distributing time on an arc gets rid of the mathematical year-to-year gaps found in standard date formats. The MIN/MAX metadata becomes surgically precise. This allows Trino to do aggressive Data Skipping, ignoring up to 90% of the disk files during Time-Travel queries.
Superior Binary Compression (RLE & Bit-Packing)
Because time on the Riemann semicircle is a sequence of strictly monotonic integers (growing predictably day by day without random jumps), Parquet's Run-Length Encoding (RLE) compression algorithm works at peak efficiency. All current active records store the exact same number—314159265359. This lets Parquet compress them into a single, tiny binary instruction, drastically cutting down the disk footprint.
Execution Speed at the CPU Level
Querying active or historical rows comes down to a basic math comparison between integers (64-bit BIGINT) inside BETWEEN clauses or point-equality checks (= 314159265359). The evaluation runs natively on the processor's hardware registers in just one clock cycle (1T). This eliminates the software overhead that usually comes with parsing or decoding complex calendar date structures.
Critical Infrastructure Requirement: Monotonic Ordering on Write
For the Data Skipping mechanism (at the Parquet file and Row Group level) to work at maximum efficiency in a dimension table with 25 to 250 million rows, the physical layout of the data on the disk is critical. If active rows get chaotically mixed with historical ones, the MIN/MAX tags get contaminated, destroying Trino’s ability to isolate intervals.
To solve this at scale, the architecture proposes two implementation paths:
a) Monotonic ordering on write: Forcing the data to be physically sorted by the Riemann axis at injection time, ensuring natural alignment of data blocks.
OR
b) Horizontal partitioning via state segregation (Recommended for large volumes): Completely isolating active records from closed ones by storing them in two dedicated physical tables:
Table A (History): Contains only closed rows, where riemann_valid_from and riemann_valid_to values are strictly monotonic and compact, guaranteeing perfect Data Skipping for Time-Travel queries.
Table B (Active / Delta): Contains only current records anchored at the North Pole (314159265359).
To keep things completely transparent for end-users and BI tools, data reconciliation happens at the metadata level using a unified SQL View (UNION ALL). This abstraction layer provides a consolidated view of the dimension, but allows Trino to run point-in-time queries for the present directly against Table B—without touching a single byte of the 100-million-row history.
Thanks to this physical sorting, customers with old history and narrow intervals are isolated in the first files on the disk, while current active rows (with their MAX anchored at the North Pole) are naturally pushed to the final blocks. Only by ensuring this clean continuity on disk can Trino correctly compare tags and instantly skip irrelevant blocks during queries.
Conclusion
Beyond the abstract math foundation, this model is a strictly pragmatic solution to an old performance headache. We started from a harsh reality: managing time through rigid conventions (like the year 5999 or the YYYYMMDD format) introduces artificial gaps into data, fragments relational database indexes, and misleads modern query engines working on Parquet files.
By mapping time as a continuous angle on a semicircle, we did nothing more than adapt our business logic to the native way the hardware behind Databricks actually works. The result is a rock-solid system, immune to rounding errors or overflows, which compresses data to the max on disk and allows for aggressive skipping of useless reads during queries.
For the rest of the company and BI reports, all this complexity stays completely invisible behind a simple SQL View. It’s a clean, efficient design, and most importantly, it's ready to handle heavy production volumes without compromising on speed.
