The PIT Table Is Its Own Bookmark
The Generated PIT Load Restarts From The Snapshot Table Itself
August 18, 2026
Every incremental load has a bookmark problem. Something has to remember how far the last run got, and whatever holds that memory becomes operational state you now have to protect, back up, and keep honest. For most warehouse teams the bookmark lives outside the data: a control table with a LastRunDate row, a scheduler variable, a config file someone edits after a backfill. It works until the day the bookmark and the data disagree.
Point-In-Time tables feel this harder than most objects, because they sit at the end of the chain. A PIT is a derived structure, an index of snapshot dates over a hub and its satellites that makes delivering data from a Data Vault fast enough for real query workloads. Derived means rebuildable, but rebuilding a wide PIT from scratch every night stops being funny at scale. So the load goes incremental, and now it needs a watermark.
BimlFlex generates PIT loads that answer the watermark question without asking anyone. The procedure reads its restart point out of the PIT table itself.
The Watermark Problem
An external watermark has one structural weakness: nothing ties it to the data it describes. Restore the warehouse from Tuesday's backup and the control table still says Thursday, so Wednesday's snapshots never get built. Clone production into a test environment and the bookmark points at rows the clone doesn't have. Reload a window by hand and forget to wind the control row back, and the next scheduled run politely skips everything you just staged.
The worst part is how it fails. Nothing errors. The load runs green, the scheduler is happy, and the PIT is quietly missing days. You find out when a business user asks why last week's numbers moved.
Any fix that adds a second piece of state (a reconciliation job, a checksum on the control table) just moves the problem. The fix that actually holds is to stop storing the watermark separately at all.
Three Dates, One Table
Open the stored procedure BimlFlex generates for a PIT table on SQL Server (it lands in your database as a flex_ procedure; you own it and can read every line) and the first thing it does is derive three dates. Trimmed to the mechanism, with illustrative names:
DECLARE @Lag INT = -1;
DECLARE @defaultKey BINARY(20) = (SELECT CAST(0x00 AS BINARY(20)));
DECLARE @min_from_date DATETIME2(7) = ISNULL(
(
SELECT MIN([RowEffectiveFromDate])
FROM [vault].[pit_Customer]
WHERE [Customer_HK] <> @defaultKey
AND [RowEffectiveFromDate] > CONVERT(DATETIME2(7), '0001-01-01')
), CONVERT(DATETIME2(7), '0001-01-01'));
DECLARE @cur_from_date DATETIME2(7) = ISNULL(
(
SELECT DATEADD(DD, @Lag, MAX([RowEffectiveFromDate]))
FROM [vault].[pit_Customer]
WHERE [Customer_HK] <> @defaultKey
AND [RowEffectiveFromDate] > @min_from_date
), CONVERT(DATETIME2(7), '0001-01-01'));
SET @cur_from_date = CASE
WHEN @cur_from_date < @min_from_date THEN DATEADD(MS, 1, @min_from_date)
ELSE @cur_from_date END;
Read it bottom to top and the design shows itself. @cur_from_date is the watermark: the newest snapshot date already in the PIT, walked back by the lag window. @min_from_date is the floor: the earliest real snapshot the table holds, ignoring the zero-key ghost row and anything sitting at the default date. The final clamp keeps the watermark from ever dropping below the floor; if it would, the procedure nudges it one millisecond past the earliest row instead.
There is no lookup against a control table because there is no control table. The watermark cannot drift from the data, because it is a query over the data. Restore the PIT from any backup, at any point in time, and the next run picks up from exactly where that copy of the table ends. The bookmark travels inside the book.
The Lag Window
That @Lag value comes from a setting you can see in the product: Pit Lag Days, in the Data Vault settings category, with a per-object override. It specifies how many days the PIT process goes back to look for changes to reprocess. The default is 1, and the generator refuses anything lower: set it to zero, a negative number, or something that doesn't parse as a number at all, and the emitted procedure uses 1. A PIT load with no overlap window is a foot-gun, so you can't configure one.
Why re-cover ground the last run already built? Because the newest snapshot rows are exactly the ones still in motion. Their effective-to dates depend on rows that hadn't arrived yet, the same way end dating works everywhere else in a Data Vault. Re-processing a day or two of overlap lets the load repair the tail of the table as new snapshots land behind it.
Re-processing is only safe if it can't double-load, and this is where the load pattern earns the overlap. The generated procedure applies changes through a MERGE keyed on the snapshot grain. A row that already exists matches, and gets updated only when its end date actually needs repair. A row that doesn't exist inserts. Run the procedure twice in a row and the second run finds every row already in place and does nothing. That's the whole idempotence story: overlap plus a grain-keyed MERGE means a re-run converges instead of accumulating.
First Run and Rebuild
Look at what happens when the PIT table is empty. MAX() returns NULL, ISNULL substitutes the default date (0001-01-01 unless you've changed the global default), and the load builds everything from the beginning of time. The first run and the thousandth run execute the same code path.
Which means a full rebuild is not a project. Truncate the PIT, execute the same procedure, done. No initial-load pipeline that diverges from the incremental one, no reset flag to remember, no runbook step that says "also update the control table." I'd argue this is the real payoff: the disaster-recovery path and the happy path are the same path, so the recovery path actually gets exercised every day.
The Same Rule Everywhere
The derivation is platform-portable because it only depends on the PIT table, and every platform has the PIT table. On SQL Server it's the T-SQL above. On Snowflake, BimlFlex generates a JavaScript stored procedure that runs the same two queries and carries the same lag:
var lag_days = -1;
On Databricks it's a notebook that derives the watermark with Spark SQL:
lag_days = -1
cur_from_date = spark.sql(cur_date_sql_stmt).collect()[0][0]
One small dialect difference is worth noticing. The T-SQL version clamps by moving the watermark a millisecond past the floor; the Snowflake and Databricks versions keep the date and tighten the comparison operator from >= to > instead. Same rule, expressed in whatever the platform does naturally. Snowflake teams tuning parallel Data Vault loading get the same restart semantics as an SSIS shop, and if you re-platform later, the watermark logic travels with the regenerated code because it never lived anywhere else.
What You Can Delete
Score the pattern by what it removes from your operational surface. No watermark store to back up in sync with the warehouse. No scheduler state to reset after a restore. No separate initial-load branch to keep tested. If the defaults don't fit an edge case, the generated procedure exposes documented extension points (DvPitLagSql among them) to override the lag logic per object, so the escape hatch exists without forking the pattern.
And it isn't a PIT-only trick. Bridge tables, the other query-assistance structure in a Data Vault, get the same treatment in their generated loads, down to an identical lag setting with an identical clamp to at least one day.
A watermark that restores with the data is the only kind that survives contact with operations. If you're evaluating how much of this bookkeeping a metadata-driven approach can absorb, the broader Data Vault automation guide covers where PIT and bridge generation fit in the full delivery chain. The snapshot table already knows where it stopped. The load just has to ask it.
