Data PlatformZoom level 3
Stage 5: time, the local time beside the instant
A timestamp in silver is an instant: one point in time, the same everywhere, held in UTC. The fifth stage leaves it that way. When a column asks for it, the stage adds a second column with the time a clock on the wall showed in a named time zone.
Both are needed. The instant is what makes two events comparable across plants. The wall-clock time is what a shift report needs, because a shift starts at six in the morning local time, not at a fixed UTC hour.
local_tz, the wall-clock time in that zone is worked out and written as text.<name>_local.A worked example
Suppose order_ts of the sales order line has local_tz: Asia/Kolkata. The two rows show the instant and the column the stage adds. India is five and a half hours ahead of UTC.
| order_id | order_ts (instant, UTC) | order_ts_local (text) |
|---|---|---|
| SO-1001 | 2026-11-09 00:00:00 | 2026-11-09T05:30:00 |
| SO-1002 | 2026-11-09 20:15:00 | 2026-11-10T01:45:00 |
The second row shows why the local column matters: the order was placed on the tenth by the plant's calendar and on the ninth by UTC.
The settings that drive it
One honest remark about the tyre example. None of the nine tyre dataset manifests has a columns list today, so every tyre column runs with the defaults. The settings below are read by the code and covered by the tests, and the example manifest lines on this page are typed for this page, not copied from the tyre manifests.
| Setting | What it means | Allowed values | Default |
|---|---|---|---|
columns[].local_tz | The time zone whose wall-clock time is added beside this timestamp. | A zone name such as Asia/Kolkata or Europe/Budapest | Empty. Nothing is added. |
processor_groups.timestamp_normalize | A dataset setting in the metadata store. The value disabled makes the stage runner skip this stage for the dataset. | The settings registry allows enabled, disabled, opt_in and dataset_decides. The plan acts only on disabled. | Not set, so the stage runs |
The code
src/dataplatform/stages/clean.py:151The whole stage.def time(df: DataFrame, ctx: StageCtx) -> Out:
"""Timestamps stay instants in UTC (the session's zone). A column with local_tz gets <name>_local beside it: the
wall-clock time in that zone, as text (ISO, no offset)."""
out = df
for c in ctx.plan.kept:
if c.logical_type == "timestamp" and c.local_tz:
# convert_timezone from UTC (the session zone) and the text in two pieces around a literal T: the same
# wall time on every engine (from_utc_timestamp and quoted letters in a pattern are not portable,
# docs/DUCKDB-SPIKE.md)
wall = F.convert_timezone(F.lit("UTC"), F.lit(c.local_tz), F.col(c.name))
local = F.concat(F.date_format(wall, "yyyy-MM-dd"), F.lit("T"), F.date_format(wall, "HH:mm:ss"))
out = out.withColumn(f"{c.name}_local", local)
return Out(out)- Which columns. Only kept contract columns whose type is timestamp and whose
local_tzis set. - Shift to the zone.
convert_timezonetakes the instant, read as UTC, and gives the wall-clock time in the named zone. The zone's rules for summer time are applied by the engine. - Write it as text. The result is formatted in two pieces, the date and the time, joined around a literal letter T. The outcome is a text such as
2026-11-09T05:30:00, with no offset at the end. - Add the column. The text goes into a new column named after the timestamp with
_localadded.
The local time is stored as text on purpose. If it were stored as a timestamp, every tool would treat it as an instant in UTC and shift it again when showing it. As text it means exactly what it says.
The comment in the code explains the two odd-looking choices. Another function for the same job and a pattern with a quoted letter do not behave the same on Spark and DuckDB, so the stage uses the forms that do.
The stage itself converts nothing to UTC. It relies on the session's time zone being UTC, which the local session factory sets, and on stage 4 having read the timestamps under that setting.
src/dataplatform/adapters/spark_silver.py:68The session's time zone is fixed to UTC where the local session is built. .config("spark.sql.session.timeZone", "UTC")How it is tested
tests/dataplatform/test_silver_stages.py has three cases. The input instant is always midnight UTC on 9 November 2026.
tests/dataplatform/test_silver_stages.py:162No setting, India, and Hungary. ("UTC kept, nothing added", {}, None),
("local time beside it", {"local_tz": "Asia/Kolkata"}, "2026-11-09T05:30:00"),
("another zone", {"local_tz": "Europe/Budapest"}, "2026-11-09T01:00:00"),tests/dataplatform/test_silver_stages.py:167The test also checks that the original timestamp is still there.def test_time(spark, case, settings, want):
p = plan(columns=[("id", "string"), ("at", "timestamp")], column_settings=[{"name": "at", **settings}])
df = run("project", frame(spark, "id string, at timestamp", [("a", ts(0))]), p).df
out = run("time", df, p).df
if want is None:
assert "at_local" not in out.columns, case
else:
assert rows(out, "at_local") == [(want,)], case
assert rows(out, "at")[0][0] is not None- No tyre dataset declares a
local_tz. The sales order line contract says the plant's local time is added besideorder_ts, but the two plants are in different zones, and the plant's zone is a fact of another dataset. The design notedocs/W1C-SILVER.mdrecords that a per-row zone belongs in gold. - One column can have one fixed zone only.
- The new column is not a contract column. It reaches silver among the other source columns.
- The stage does not check that the session's time zone really is UTC.