AUTO CDC FROM SNAPSHOT schema evolution in history tracking columns

[carl_hsg]

Does the create_auto_cdc_from_snapshot_flow() allow for expansion of included columns in track_history_column_list or track_history_except_column_list without doing a full refresh? 

My intended flow is: 

1. Bronze - Managed ingestion of table (scd 1) using Dynamics 365 connector as an example of one source, current state table
2. Silver - MV as a staging table for initial transformation, current state table
3. Silver - MV as a conformed entity table consolidating different sources (conformed_customer, conformed_order etc.), current state table
4. Silver - Streaming table with history of 3. (SCD 2) for tables like conformed_customer_history based on specific columns

Since 3. is a current state table, create_auto_cdc_from_snapshot_flow() seems fitting. Documentation seems rather unclear whether or not schema evolution and specifically expanding tracking columns forces a full refresh of the target table, resulting in loss of historic data. 

As an example. Let's pretend we introduce the column region to the confomed_customer. Could we expand conformed_customer_history to include the region column, with existing rows being assigned NULL and future ones being inserted, as well as alter the tracking list to include the new region column for historization?

The D365 connector does provide SCD type 2 output, however, we want to be able and define which columns are tracked. Schema evolution for the connector is also in private preview and isn't relevant for us in current production. 
Going down the AutoLoader path for D365 is possible, but seeing as we conform different sources downstream with different setup create_auto_cdc_from_snapshot_flow() is most relevant. 

[data_pulse · Accepted solution]

@carl_hsg 

Yes, I tested expanding track_history_column_list on an existing SCD2 target and it did not require a full refresh in my test.

The part that seems unclear in the docs is what happens when you expand the tracking list or introduce a new column after the SCD2 target already contains history.

I did a quick validation too of that exact scenario with create_auto_cdc_from_snapshot_flow().

Started with: track_history_column_list=["name"]

then expanded it to: track_history_column_list=["name", "region"]

Existing SCD2 history was preserved and subsequent changes to region created new SCD2 versions without requiring a full refresh.

Also tested adding a completely new source column (segment). What I observed is below:

• existing SCD2 history was preserved
• no full refresh was required
• the new column was added to the target schema (schema evolved)
• older historical rows had NULL for the new column
• current rows picked up the new value
• after adding the new column (segment) to track_history_column_list, future changes to it created new SCD2 versions.

So for the scenario you described: adding region, keeping old history as NULL and then historizing future region changes, it worked as expected in my test.

Let me know if this helps and post here for any other questions on it.

[ivanvyd]

Adding a column and changing the history-tracking configuration need separate checks. Databricks describes adding columns as generally safe without a full refresh:

"Plan your schema to avoid changes that require a full refresh. Adding columns is generally safe, while modifying existing columns or partitioning schemes typically requires recomputing the table."

---

However, the snapshot API reference does not explicitly guarantee changing the tracking list on an already populated SCD2 target without rebuilding it. I wouldn't give an unconditional "yes" based on the documentation alone.

For region, the configuration is:

Add it to track_history_column_list, retaining your existing tracked columns. If you use track_history_except_column_list, keep region out of that list. The latter excludes columns from history tracking, not from the output table. The snapshot API reference explains both options.

Protect the history before making the change.

A full refresh clears the streaming target and its checkpoints. Your current-state materialized view alone cannot reconstruct the customer versions that have already disappeared from it. See the refresh documentation.

Add pipelines.reset.allowed=false to your existing target declaration, preserving its other settings and table properties:

from pyspark import pipelines as dp

dp.create_streaming_table(
    name="conformed_customer_history",
    table_properties={
        "pipelines.reset.allowed": "false",
        # Retain your other table properties here.
    },
)

This prevents full refreshes of that table. It does not enable schema evolution or make an incompatible change supported. The property is documented in the pipeline properties reference.

Test the transition on a non-production target that already contains history.

Check that previously closed versions remain unchanged with region=NULL. Pay particular attention to the first snapshot containing a non-null region: does it close the existing active version and create another, or populate the active version in place? Then change only region and check for a new version; repeat an unchanged snapshot and check that none is added.

That first snapshot is the important boundary in your example. Successful subsequent tracking would not, by itself, establish that introducing the column preserved the history exactly as you intended.