Point-in-Time (PIT) tables are the performance engine of the business vault. They pre-compute which satellite row was current for each hub key at each as-of date — so downstream queries never have to scan full satellite history.
What a PIT Contains
- Hub hash key — which entity
- As-of date — which date the snapshot is for
- Satellite hash keys + load datetimes — one pair per satellite, pointing to the row that was current on that date
PIT Models in This Pipeline
pit_customer — sat_customer_details + sat_customer_contact pit_product — sat_product pit_order — sat_order_status + sat_order_financials pit_order_line — sat_order_line
How the PIT Query Works
For each hub key and each as-of date, the PIT finds the latest satellite row where load_datetime ≤ as_of_date. This is a point-in-time lookup — "what did this customer look like on March 15?" answered by a single row join, not a window function.
TRUNCATE + INSERT Pattern
PITs use the pit_incremental materialization from automate_dv. On each run, rows for the new date range are truncated and re-inserted. This ensures idempotency — re-running the same dates produces the same result.
The stage_tables_ldts Fix
A critical detail: the stage_tables_ldts parameter determines the "last safe load datetime." We point this to hub tables instead of staging views. Hubs always have data, so the PIT always has a valid reference point — even when staging returns zero rows for a given load.