# Bitemporal AUTO CDC for Backdated Policy Endorsements

> Keep what a policy source had recorded, not only corrected history. Lakeflow Bitemporal AUTO CDC (Beta) for a backdated home policy endorsement, with SQL.

Published: 2026-10-23
Author: Sushruth Aeluguri
Tags: databricks, lakeflow, auto-cdc, bitemporal, insurance, delta
Canonical: https://www.techfabric.com/blog/bitemporal-auto-cdc-for-backdated-policy-endorsements

---

A kitchen fire is reported on policy HO-4471 for a loss on May 10. Ten days later, on May 20 at 2:35 p.m., the policy system records an accepted endorsement that raises the dwelling limit from $300,000 to $350,000, effective May 1. The endorsement is backdated, so the policy now says the higher limit was in force on the day of the fire.

That produces two legitimate questions. Which limit applies to the loss? $350,000, according to the policy as it stands today. Which limit had the policy source recorded for the loss date on May 15, before the endorsement existed? $300,000. A claims audit, a reserve review, and a dispute with the policyholder can each need one answer or the other, and a table that keeps only the corrected history cannot produce the second.

For an insurer, the cost of having only one answer shows up late, in a disputed claim, a reserve review, or an audit, where someone has to show what the company had on file at the time. Teams usually respond in one of two ways. They overwrite history and lose the evidence, or they hand-write `MERGE` logic with staging tables and window functions, which Databricks' own [CDC documentation](https://docs.databricks.com/aws/en/data-engineering/what-is-cdc) describes as hard to reason about and maintain as pipelines evolve. Keeping both answers costs a Beta feature and extra rows per fact, and the first decision for a team is which timestamp defines "what we had on file."

Lakeflow's bitemporal AUTO CDC mode moves that work into the pipeline definition. Engineers declare two sequencing columns, the platform maintains both timelines, and claims and audit teams get the earlier answer from a query instead of a reconstruction project.

The first step for a team is a decision rather than code. It has to settle which timestamp defines "what we had on file." Once that is settled, the pipeline change is two sequencing clauses and a storage mode. The costs are concrete. The feature is in Beta, and the history table holds several rows per logical fact. This article works through the endorsement above: it configures the flow, reads the intervals it generates, and queries the dwelling limit at two points in system time.

## Policy grain and the two timestamps

The grain is one row per policy term, location, and coverage. For HO-4471 that is term 2026, location 1, dwelling coverage, and the key is `(policy_number, term_id, location_id, coverage_code)`. The grain matters because AUTO CDC orders events per key. If the key were only `policy_number`, a change to personal property would be sequenced against the dwelling limit and the two coverages would overwrite each other's history.

Two timestamps travel with every event:

- `effective_at` is business time, the moment the limit applies to the risk. It drives `SEQUENCE BY`.
- `source_recorded_at` is the moment the policy administration system recorded the change. It drives `SYSTEM SEQUENCE BY`.

Policy systems usually expose an effective-date view on its own. Guidewire's [asOfDate parameter](https://docs.guidewire.com/cloud/is/202607/cloudapibf/cloudAPI/Basic-REST-operations/query-parameters/c_the-asOfDate-query-parameter.html), for example, retrieves a resource as of a business date. That gives you the business timeline, not what had been recorded by a given day, which is the claims audit's question.

I would sequence system time by the source's own recording timestamp and keep platform ingestion time as a separate column for lag monitoring. The distinction changes what an "as of" query means. If the May 20 event reaches the lakehouse on May 22, a query as of May 21 still returns $350,000, because the policy source had recorded it by then. That result describes the policy source. It does not describe what a report published from the platform on May 21 contained, and reconstructing that report would need its retained inputs and logic.

The source feed carries these two events:

| policy_number | term_id | location_id | coverage_code | limit_amount | effective_at | source_recorded_at |
|---|---|---|---|---|---|---|
| HO-4471 | 2026 | 1 | DWELLING | 300000.00 | 2026-01-01 00:00:00 | 2025-12-29 10:05:00 |
| HO-4471 | 2026 | 1 | DWELLING | 350000.00 | 2026-05-01 00:00:00 | 2026-05-20 14:35:00 |

The first event was recorded before the term began, so its system time precedes its business time, which is ordinary for a bound policy. Both timestamps must be sortable types and neither can be NULL, and I would store them in one time zone convention (UTC) so intervals compare cleanly.

The documentation says AUTO CDC expects [one distinct update per key at each sequencing value](https://docs.databricks.com/aws/en/data-engineering/what-is-cdc#automatically-process-cdc-feeds). A later correction that reuses an effective timestamp, such as a typo fix to the $350,000, is a case the documented examples (insert, update, out-of-order update, delete) do not walk through. I would run it through a test pipeline and read the rows it produces before relying on it.

## Configuring the bitemporal flow

Ordinary SCD Type 2 tracks a single sequence dimension. Fed `effective_at`, it would rewrite the history when the endorsement arrives: the $300,000 row would end at May 1, and nothing would remember that the source believed $300,000 until May 20. Delta table history is no substitute either. Table versions are retained subject to `VACUUM`, which makes them a poor home for history that auditors may ask about years later. Bitemporal mode stores both timelines as rows, so a retention setting does not erase an old belief.

Bitemporal AUTO CDC is in [Beta](https://docs.databricks.com/aws/en/release-notes/release-types). It runs on serverless pipelines or the Pro and Advanced editions, and Databricks' [announcement post](https://www.databricks.com/blog/taking-auto-cdc-next-level-solving-hardest-real-world-use-cases) advises pinning the pipeline to the `PREVIEW` channel while the feature is in Beta.

The source is an append-only Delta table, so corrections arrive as new events rather than as edits to old rows.

```sql
CREATE TABLE main.home_policy.policy_coverage_events (
  policy_number      STRING,
  term_id            INT,
  location_id        INT,
  coverage_code      STRING,
  limit_amount       DECIMAL(12,2),
  effective_at       TIMESTAMP,
  source_recorded_at TIMESTAMP
);

INSERT INTO main.home_policy.policy_coverage_events VALUES
  ('HO-4471', 2026, 1, 'DWELLING', 300000.00,
   TIMESTAMP'2026-01-01 00:00:00', TIMESTAMP'2025-12-29 10:05:00');
```

The pipeline is one streaming table and one flow:

```sql
CREATE OR REFRESH STREAMING TABLE coverage_limit_bt;

CREATE FLOW coverage_limit_bt_flow AS AUTO CDC INTO
  coverage_limit_bt
FROM stream(main.home_policy.policy_coverage_events)
KEYS (policy_number, term_id, location_id, coverage_code)
SEQUENCE BY effective_at
SYSTEM SEQUENCE BY source_recorded_at
STORED AS BITEMPORAL;
```

`STORED AS BITEMPORAL` is the exact clause (not `STORED AS SCD TYPE ...`), and it requires both sequencing clauses. In Python the same flow is `create_auto_cdc_flow` with `sequence_by="effective_at"`, `system_sequence_by="source_recorded_at"`, and `stored_as_scd_type="bitemporal"`. The target gains `__SYSTEM_START_AT` and `__SYSTEM_END_AT` next to the `__START_AT` and `__END_AT` columns that SCD Type 2 already produces, per the [bitemporal documentation](https://docs.databricks.com/aws/en/ldp/cdc-advanced#bitemporal-auto-cdc).

After the first pipeline update, the target holds one row:

| limit_amount | __START_AT | __END_AT | __SYSTEM_START_AT | __SYSTEM_END_AT |
|---|---|---|---|---|
| 300000.00 | 2026-01-01 00:00:00 | NULL | 2025-12-29 10:05:00 | NULL |

## What the backdated endorsement does to the history

On May 20 the policy system records the endorsement, and the feed appends it:

```sql
INSERT INTO main.home_policy.policy_coverage_events VALUES
  ('HO-4471', 2026, 1, 'DWELLING', 350000.00,
   TIMESTAMP'2026-05-01 00:00:00', TIMESTAMP'2026-05-20 14:35:00');
```

The event has an earlier business time than its system time, the same shape as the update in the documentation's worked example. After the next pipeline update, the target should contain three rows:

| # | limit_amount | __START_AT | __END_AT | __SYSTEM_START_AT | __SYSTEM_END_AT |
|---|---|---|---|---|---|
| 1 | 300000.00 | 2026-01-01 00:00:00 | NULL | 2025-12-29 10:05:00 | 2026-05-20 14:35:00 |
| 2 | 300000.00 | 2026-01-01 00:00:00 | 2026-05-01 00:00:00 | 2026-05-20 14:35:00 | NULL |
| 3 | 350000.00 | 2026-05-01 00:00:00 | NULL | 2026-05-20 14:35:00 | NULL |

```mermaid
%% caption: HO-4471 dwelling limit before and after the May 20 endorsement. Each box is one row with its business interval; the system-time groups show when the source believed it.
flowchart LR
  subgraph SYS1["System time: December 29 to May 20 14:35"]
    R1["Row 1: $300,000<br/>business January 1 to open"]
  end
  E(["Endorsement recorded May 20 14:35<br/>effective May 1, limit $350,000"])
  subgraph SYS2["System time: May 20 14:35 onward"]
    R2["Row 2: $300,000<br/>business January 1 to May 1"]
    R3["Row 3: $350,000<br/>business May 1 to open"]
  end
  R1 -- "closed in system time" --> E
  E -- "writes corrected history" --> R2
  E -- "writes new version" --> R3
  R2 -. "shares May 1 boundary" .- R3
```

Each row is a statement about what the source believed during a stretch of system time:

1. From December 29 until May 20 at 2:35 p.m., the source believed $300,000 applied from January 1 with no known end.
2. From May 20 onward, the source knows that the $300,000 limit applied only until May 1.
3. From May 20 onward, the source knows that $350,000 applies from May 1 with no known end.

The old belief is closed in system time, not deleted. Ordinary SCD Type 2 would keep only rows 2 and 3, which is the corrected business history without the record of what came before it. A single logical fact now occupies several physical rows, which is the storage cost of answering both questions.

## Reading the nullable interval endpoints

A NULL `__END_AT` means business validity has no known end. A NULL `__SYSTEM_END_AT` means the row is still believed true. Row 3, with both NULL, is the source's current belief about the current limit, and `__SYSTEM_END_AT IS NULL` selects the current belief across the whole table.

Intervals are half-open. Row 2 ends at May 1 at midnight and row 3 starts at the same instant, so a lookup must use `start <= t AND t < end` to land on exactly one version.

NULL handling is where hand-written lookups usually break. A comparison such as `t < __END_AT` evaluates to NULL when `__END_AT` is NULL, and the row is filtered out. A predicate without NULL handling therefore drops every open interval. Against the three rows above, it returns nothing at either timestamp in the next section. I prefer `IS NULL OR` to coalescing endpoints into a far-future sentinel date, since sentinels leak into joins and exports where someone eventually treats 9999-12-31 as real.

## Looking up the limit for the May 10 claim

The lookup applies one half-open check per timeline. `:loss_at` is the loss timestamp and `:known_as_of` is the system-time instant the reader selects:

```sql
WITH q AS (
  SELECT CAST(:loss_at AS TIMESTAMP)    AS loss_at,
         CAST(:known_as_of AS TIMESTAMP) AS known_as_of
)
SELECT p.limit_amount, p.__START_AT, p.__END_AT,
       p.__SYSTEM_START_AT, p.__SYSTEM_END_AT
FROM main.home_policy.coverage_limit_bt AS p
CROSS JOIN q
WHERE p.policy_number = 'HO-4471'
  AND p.term_id       = 2026
  AND p.location_id   = 1
  AND p.coverage_code = 'DWELLING'
  AND p.__START_AT <= q.loss_at
  AND (p.__END_AT IS NULL OR q.loss_at < p.__END_AT)
  AND p.__SYSTEM_START_AT <= q.known_as_of
  AND (p.__SYSTEM_END_AT IS NULL OR q.known_as_of < p.__SYSTEM_END_AT);
```

With `:loss_at = '2026-05-10 16:20:00'`, changing only the system-time instant changes the answer:

| :known_as_of | limit_amount | Row matched |
|---|---|---|
| 2026-05-15 09:00:00 | 300000.00 | 1 |
| 2026-05-21 09:00:00 | 350000.00 | 3 |

On May 15, row 3 does not exist yet, because its `__SYSTEM_START_AT` is May 20. Row 1 is open on both timelines, so it answers $300,000. On May 21, row 1's system interval closed on May 20, and the May 10 loss date falls inside row 3's business interval, so the answer is $350,000. Row 2 never matches a May 10 loss because its business interval ends on May 1.

Claim processing would apply the same predicates in a join from the claims table, using the policy keys and the claim's loss date. I would define them once in a governed view or SQL function so every analyst gets the same NULL handling and the same interval boundaries.

## When one timeline is enough

The decision criterion is whether anyone will need to reproduce what the source had recorded at an earlier point in time. If consumers only ever ask for the corrected history, ordinary SCD Type 2 is simpler, stores fewer rows, and is not in Beta. If a claims dispute, an audit, or a reserve review can ask the May 15 question after the endorsement lands, a single timeline has already lost the answer, and the cost of finding that out is high.

For a first test, I would load the two events above into a pipeline pinned to the `PREVIEW` channel, confirm the three rows match the table in this article, and run both lookups. Then add the same-date correction case and read its rows before it reaches a production feed.

## Frequently asked questions

### What is the difference between `SEQUENCE BY` and `SYSTEM SEQUENCE BY`?

`SEQUENCE BY` takes the business time column, when a fact applies in the real world, and produces `__START_AT` and `__END_AT`. `SYSTEM SEQUENCE BY` takes the column for when the system knew the event and produces `__SYSTEM_START_AT` and `__SYSTEM_END_AT`. Bitemporal mode requires both.

### Can I use platform ingestion time as the system timestamp?

The clause accepts any sortable, non-NULL column, so technically yes. The meaning changes, though. Ingestion time answers what the platform had received by a given moment, while the source's recording time answers what the policy system had recorded. For claim disputes about the policy itself I would use the source's recording time and keep ingestion time as a separate column.

### Is bitemporal AUTO CDC ready for production?

It is in Beta. Prove it in a test pipeline with your own event shapes first, pin the pipeline to the `PREVIEW` channel as Databricks' announcement advises, and read the generated rows for every correction pattern your source can produce before it reaches a production feed.