Forum Discussion

jubinsoni's avatar
jubinsoni
Icon for Resolver II rankResolver II
10 days ago
Solved

Best pattern for keeping a current-state table in Eventhouse from Eventstream CDC?

Hi all,

I'm streaming change data from Azure SQL Database into Eventhouse using an Eventstream CDC source. Ingesting the raw change events works fine. The question is how best to turn them into a queryable current-state table, the way you would with a Flink CDC or Debezium pipeline.

Setup:

  • Source: Azure SQL Database (CDC enabled), about 20 tables, mostly updates with occasional deletes
  • Eventstream → Eventhouse, raw change events land in one table per source table
  • Consumers: Real-Time Dashboards and some Activator rules that need the latest row per key

What I'm considering:

  1. A materialized view using arg_max(timestamp, *) by key over the raw table
  2. An update policy that flattens the change envelope into a typed table, with the materialized view on top of that
  3. Handling deletes by keeping a delete flag, then filtering it out in the view or a stored function

Questions:

  • Is option 2 (update policy, then materialized view) the recommended pattern, or is there a simpler built-in approach?
  • How are people handling deletes? Filtering the latest row by a delete flag works, but deleted keys keep taking up space. Is there a good retention or cleanup pattern?
  • What should I use as the ordering column so out-of-order events don't produce a stale "latest" row: the source LSN/commit timestamp, or ingestion time?
  • Any gotchas when the source schema changes (for example, a new column)?

Thanks!

eventhouse
eventstream
Need Help

4 Replies

  • v-abhinavmu's avatar
    v-abhinavmu
    Icon for Community Support rankCommunity Support
    8 days ago

    Hi jubinsoni​,

    Thanks for reaching out to the Microsoft Fabric Community forum.

    For the CDC transformation layer, DeltaFlow (Preview) transforms raw Azure SQL CDC events into analytics-ready tabular data, with automatic destination table management and schema evolution.

    For maintaining the current state, a materialized view using arg_max() is a documented pattern for returning the latest record per key. For deletes, the latest operation can be retained and deleted records filtered from the final result.

    This can be implemented as:
    Azure SQL CDC → Eventstream/DeltaFlow → Eventhouse → arg_max() materialized view

    For retention, a retention policy can be applied to control how long the historical CDC data is retained.

    For event ordering, DeltaFlow exposes __dbz_timestamp as the timestamp when the change event was captured.

    For more details, please refer to the below Official Microsoft Documentation:
    •    Azure SQL Database CDC Source in Fabric Eventstream - Microsoft Fabric | Microsoft Learn
    •    DeltaFlow output transformation (Preview) - Microsoft Fabric | Microsoft Learn
    •    Materialized views use cases - Kusto | Microsoft Learn
    •    Retention policy - Kusto | Microsoft Learn

    I hope this helps. Please feel free to reach out if you have any further questions.
    Thank you.

    • jubinsoni's avatar
      jubinsoni
      Icon for Resolver II rankResolver II
      7 days ago

      Hi v-abhinavmu, thanks for the pointers. I will try the arg_max() materialized view for the current state table and look at DeltaFlow for the CDC transformation, and share how it goes. 

      • v-abhinavmu's avatar
        v-abhinavmu
        Icon for Community Support rankCommunity Support
        6 days ago

        Hi jubinsoni​,

        Thanks for the update. Please share your findings once you’ve had a chance to test the approach, particularly how the arg_max() materialized view and DeltaFlow work for your scenario. Your results may also be helpful to others working with CDC and current-state data in Eventhouse.

        Thank you.

  • ShivekMaharaj's avatar
    ShivekMaharaj
    Icon for Community Champion rankCommunity Champion
    5 days ago

    Hi jubinsoni​,

    I think the arg_max() materialized view is the cleaner current-state pattern here.

    One distinction I would make is that an update policy alone does not behave like an UPSERT table.

    Update policies transform newly ingested rows and append them to the target table. They do not automatically remove the previous version of the same business key.

    Microsoft specifically documents an arg_max() materialized view for the “latest record per entity” scenario.

    So a pattern such as:

    raw CDC Eventhouse table
-> materialized view
-> summarize arg_max(ChangeTimestamp, *) by PrimaryKey

    is a good fit for the queryable current state.

    For deletes, keep the delete event/tombstone in the raw CDC table and let the materialized view select it as the latest row, then filter out rows where the latest operation is Delete.

    If you use DeltaFlow, Microsoft now exposes:

    __dbz_operation
__dbz_timestamp

    and update events are represented as Pre_Update and Post_Update rows.

    I would use Post_Update/Insert/Delete for the current-state logic rather than Pre_Update.

    For ordering, I would prefer the source CDC ordering metadata if your raw Debezium payload exposes it. If you use DeltaFlow, __dbz_timestamp is the documented capture timestamp, but if two changes for the same key can share the same timestamp, I would still look for an additional deterministic ordering field rather than relying on ingestion time alone.

    On schema changes, DeltaFlow can automatically update the destination Eventhouse schema when source columns are added or tables change.

    For a plain arg_max(* ) materialized view, Microsoft also supports autoUpdateSchema=true, but that is something I would test carefully before relying on it in production.

    So my preferred design would be:

    Azure SQL CDC
-> Eventstream + DeltaFlow
-> raw/history Eventhouse table
-> arg_max() materialized view
-> filter latest Delete operations out for the live/current-state view

    and retain the raw CDC table separately for audit/history.

    AI-assisted drafting: AI was used to help structure and phrase this response. I reviewed and validated the technical content before posting.