This is an automated email from the ASF dual-hosted git repository.

szehon-ho pushed a commit to branch branch-4.x
in repository https://gitbox.apache.org/repos/asf/spark.git


The following commit(s) were added to refs/heads/branch-4.x by this push:
     new cd3549a05172 [SPARK-58391][SDP] Validate AutoCDC sequencing-type and 
SCD2 track-history drift
cd3549a05172 is described below

commit cd3549a0517225d5b607fac89f6d4e54f3a98965
Author: Andreas Neumann <[email protected]>
AuthorDate: Fri Jul 31 22:34:08 2026 -0700

    [SPARK-58391][SDP] Validate AutoCDC sequencing-type and SCD2 track-history 
drift
    
    ### What changes were proposed in this pull request?
    
    Follow-up for AutoCDC SCD2 (SPARK-56249). Adds two configuration-drift 
validations for AutoCDC targets, closing gaps left by the existing key-column 
and SCD-type drift checks. Both are checked on an incremental run against 
values persisted when the auxiliary table was created, and both direct the user 
to a full refresh as the remedy.
    
      - **Sequencing-type drift** 
(`AUTOCDC_INVALID_STATE.SEQUENCING_TYPE_DRIFT`): the sequencing *expression* 
may legitimately change across runs (e.g. a new timestamp parse format), but 
its resolved result *type* must not — the target persists the sequencing type 
inside
      `_cdc_metadata` (and, for SCD2, in the `__START_AT` / `__END_AT` interval 
columns), so a changed type would make new events incomparable with the 
persisted history. Validated at the target level, before the target-table 
merge, for both SCD1 and SCD2.
      - **Track-history drift** (`AUTOCDC_INVALID_STATE.TRACK_HISTORY_DRIFT`, 
SCD2 only): the resolved SCD2 track-history column set defines what constitutes 
a run (a change in any tracked column opens a new historical record), so 
changing the set would reinterpret already-reconciled history. The effective 
set (an explicit `TRACK HISTORY ON` selection, or the default of every eligible 
non-key/non-framework column) is resolved via 
`Scd2BatchProcessor.computeTrackedHistoryColumns` — the sam [...]
    
    Supporting changes:
    - New auxiliary-table property `pipelines.autocdc.trackHistoryColumnNames` 
(JSON array; SCD2-only, absent for SCD1).
    - `AutoCdcAuxiliaryTableSpec` gains `expectedSequencingType` and 
`expectedTrackHistoryColumnNames`.
    - `DatasetManager` runs the target-level sequencing-type check before 
`materializeTable` evolves the target; the aux-level checks run in 
`materializeAuxiliaryTable`.
    - Reworded `AUXILIARY_TABLE_PROPERTY_MISSING` to speak of "the AutoCDC 
configuration" generally rather than "key columns" specifically, since more 
properties are now validated.
    
    ### Why are the changes needed?
    
    AutoCDC already rejects key-column and SCD-type drift on incremental runs, 
because those silently corrupt the persisted state. Sequencing type and (for 
SCD2) the track-history column set have the same property: keeping them 
constant is an invariant the reconciled on-disk state depends on, but neither 
was validated. Without these checks a changed sequencing type surfaces as an 
opaque type-mismatch mid-merge, and a changed track-history set silently 
reinterprets history. Both now fail f [...]
    
    ### Does this PR introduce _any_ user-facing change?
    
    Yes. An incremental AutoCDC run whose sequencing result type differs from 
the recorded type now fails with `AUTOCDC_INVALID_STATE.SEQUENCING_TYPE_DRIFT` 
(SQLSTATE 42000); an SCD2 run that changes its effective track-history column 
set now fails with `AUTOCDC_INVALID_STATE.TRACK_HISTORY_DRIFT`. Both messages 
name the conflicting vs recorded values and recommend a full refresh. 
Reordering the same track-history columns, or changing the sequencing 
expression while keeping its type, is un [...]
    
    ### How was this patch tested?
    
    New `AutoCdcConfigDriftSuite` (5 tests):
    - SCD1 and SCD2 sequencing-type drift each trigger `SEQUENCING_TYPE_DRIFT`.
    - Changing the sequencing expression while keeping the same type is allowed.
    - Changing the SCD2 `TRACK HISTORY ON` column set triggers 
`TRACK_HISTORY_DRIFT`.
    - Reordering the same track-history columns does not trigger drift.
    
    ### Was this patch authored or co-authored using generative AI tooling?
    
    Generated-by: Claude Code (Opus 4.8)
    
    Closes #57625 from anew/spark-58391-autocdc-config-drift.
    
    Authored-by: Andreas Neumann <[email protected]>
    Signed-off-by: Szehon Ho <[email protected]>
    (cherry picked from commit 9209369ed28a4bff80bb30ce9bed022cc8dccc7a)
    Signed-off-by: Szehon Ho <[email protected]>
---
 .../src/main/resources/error/error-conditions.json |  12 +-
 .../pipelines/graph/AutoCdcAuxiliaryTable.scala    | 166 +++++-
 .../sql/pipelines/graph/AuxiliaryTableSpec.scala   |  13 +-
 .../spark/sql/pipelines/graph/DatasetManager.scala | 152 +++++-
 .../apache/spark/sql/pipelines/graph/Flow.scala    |  56 +-
 .../graph/AutoCdcAuxiliaryTableSuite.scala         | 300 ++++++++++-
 .../pipelines/graph/AutoCdcConfigDriftSuite.scala  | 593 +++++++++++++++++++++
 .../AutoCdcScd1AuxiliaryTableDurabilitySuite.scala |   2 +-
 .../graph/AutoCdcScd1AuxiliaryTableSpecSuite.scala |   4 +-
 .../graph/AutoCdcScd2AuxiliaryTableSpecSuite.scala |   4 +-
 10 files changed, 1213 insertions(+), 89 deletions(-)

diff --git a/common/utils/src/main/resources/error/error-conditions.json 
b/common/utils/src/main/resources/error/error-conditions.json
index 4d3b6282a404..db2f36af4edd 100644
--- a/common/utils/src/main/resources/error/error-conditions.json
+++ b/common/utils/src/main/resources/error/error-conditions.json
@@ -256,7 +256,7 @@
       },
       "AUXILIARY_TABLE_PROPERTY_MISSING" : {
         "message" : [
-          "The internal auxiliary table is missing the required <propertyName> 
table property; cannot validate AutoCDC key columns. The auxiliary table 
metadata may be corrupted or have been modified externally. Perform a full 
refresh of the target table to recreate the auxiliary table."
+          "The internal auxiliary table is missing the required <propertyName> 
table property; cannot validate the AutoCDC configuration. The auxiliary table 
metadata may be corrupted or have been modified externally. Perform a full 
refresh of the target table to recreate the auxiliary table."
         ]
       },
       "KEY_SCHEMA_DRIFT" : {
@@ -268,6 +268,16 @@
         "message" : [
           "One or more AutoCDC flows writing to the target use SCD type 
<expectedScdType>, which is inconsistent with the SCD type recorded for it 
(recorded <recordedScdType>). AutoCDC does not support changing a target's SCD 
type across incremental pipeline runs. Correct the conflicting flow(s) or 
perform a full refresh of the target table."
         ]
+      },
+      "SEQUENCING_TYPE_DRIFT" : {
+        "message" : [
+          "One or more AutoCDC flows writing to the target use a sequencing 
expression of type <expectedSequencingType>, which is inconsistent with the 
sequencing type recorded for it (recorded <recordedSequencingType>). The 
sequencing expression may change across incremental pipeline runs, but its 
result type must not, so recorded sequencing values remain comparable. Correct 
the conflicting flow(s) or perform a full refresh of the target table."
+        ]
+      },
+      "TRACK_HISTORY_DRIFT" : {
+        "message" : [
+          "One or more AutoCDC flows writing to the target track history on 
columns [<expectedTrackHistoryColumns>], which are inconsistent with the 
track-history columns recorded for it (recorded 
[<recordedTrackHistoryColumns>]). AutoCDC does not support changing the SCD 
Type 2 track-history columns across incremental pipeline runs. Correct the 
conflicting flow(s) or perform a full refresh of the target table."
+        ]
       }
     },
     "sqlState" : "42000"
diff --git 
a/sql/pipelines/src/main/scala/org/apache/spark/sql/pipelines/graph/AutoCdcAuxiliaryTable.scala
 
b/sql/pipelines/src/main/scala/org/apache/spark/sql/pipelines/graph/AutoCdcAuxiliaryTable.scala
index 9f3dd2dba0d1..9251501887a3 100644
--- 
a/sql/pipelines/src/main/scala/org/apache/spark/sql/pipelines/graph/AutoCdcAuxiliaryTable.scala
+++ 
b/sql/pipelines/src/main/scala/org/apache/spark/sql/pipelines/graph/AutoCdcAuxiliaryTable.scala
@@ -27,8 +27,9 @@ import org.apache.spark.sql.AnalysisException
 import org.apache.spark.sql.catalyst.TableIdentifier
 import org.apache.spark.sql.catalyst.analysis.Resolver
 import org.apache.spark.sql.connector.catalog.{CatalogV2Util, Table => 
CatalogTable, TableCatalog}
-import org.apache.spark.sql.pipelines.autocdc.{AutoCdcReservedNames, 
Scd2BatchProcessor, ScdType}
-import org.apache.spark.sql.types.{LongType, StructField, StructType}
+import org.apache.spark.sql.pipelines.autocdc.{AutoCdcReservedNames, 
Scd1BatchProcessor,
+  Scd2BatchProcessor, ScdType}
+import org.apache.spark.sql.types.{DataType, LongType, StructField, StructType}
 
 /**
  * Helpers to construct and validate an AutoCDC flow's auxiliary table within 
the context of a
@@ -61,20 +62,29 @@ object AutoCdcAuxiliaryTable {
     s"${PipelinesTableProperties.pipelinesPrefix}autocdc.keyColumnNames"
 
   /**
-   * Serialize key column names to the JSON form stored at 
[[keyColumnNamesProperty]].
-   * Round-trips an empty list as `[]`; callers are expected to enforce a 
non-empty key set
-   * upstream.
+   * Table property recording the resolved SCD2 track-history column names as 
a JSON string array.
+   * These columns define an SCD2 run (a change in any of them opens a new 
historical record), so
+   * changing the set would reinterpret already-reconciled history. SCD2-only; 
absent for SCD1.
+   * Full-refresh is the only way to change it.
    */
-  private[graph] def serializeKeyColumnNames(names: Seq[String]): String = {
+  val trackHistoryColumnNamesProperty: String =
+    
s"${PipelinesTableProperties.pipelinesPrefix}autocdc.trackHistoryColumnNames"
+
+  /**
+   * A JSON string-array codec for column-name lists persisted as 
auxiliary-table properties (used
+   * for both [[keyColumnNamesProperty]] and 
[[trackHistoryColumnNamesProperty]]). Round-trips an
+   * empty list as `[]` -- both an empty key set and an empty track-history 
set are meaningful to
+   * some caller, so no non-empty invariant is assumed here.
+   */
+  private[graph] def serializeColumnNames(names: Seq[String]): String = {
     compact(JArray(names.map(JString(_)).toList))
   }
 
   /**
-   * Parse a [[keyColumnNamesProperty]] value. `None` if it is not a JSON 
array of strings.
-   * Round-trips an empty list as `[]`; callers are expected to enforce a 
non-empty key set
-   * upstream.
+   * Parse a value written by [[serializeColumnNames]]. `None` if it is not a 
JSON array of strings.
+   * Round-trips an empty list as `[]` (see [[serializeColumnNames]]).
    */
-  private[graph] def parseKeyColumnNames(raw: String): Option[Seq[String]] = {
+  private[graph] def parseColumnNames(raw: String): Option[Seq[String]] = {
     val parsed = try Some(parse(raw)) catch { case NonFatal(_) => None }
     parsed.flatMap {
       case JArray(elems) =>
@@ -163,7 +173,7 @@ object AutoCdcAuxiliaryTable {
       Map(scdTypePropertyKey -> ScdType.Type1.label) ++
       // Persist the AutoCDC key column names as a JSON list; immutable 
post-creation (full-refresh
       // is the only way to change it).
-      Map(keyColumnNamesProperty -> 
serializeKeyColumnNames(keyFields.map(_.name))) ++
+      Map(keyColumnNamesProperty -> 
serializeColumnNames(keyFields.map(_.name))) ++
       // Inherit the target's format so MERGE semantics line up. When 
unspecified, omit the provider
       // so the catalog falls back to its default.
       targetTable.format.map(TableCatalog.PROP_PROVIDER -> _)
@@ -174,7 +184,9 @@ object AutoCdcAuxiliaryTable {
       properties = scd1AuxiliaryTableProperties,
       targetTableIdentifier = targetTable.identifier,
       expectedKeyFields = keyFields,
-      expectedScdType = ScdType.Type1
+      expectedScdType = ScdType.Type1,
+      expectedSequencingType = inputAutoCdcFlow.sequencingType,
+      expectedTrackHistoryColumnNames = None
     )
   }
 
@@ -227,13 +239,27 @@ object AutoCdcAuxiliaryTable {
       StructField(Scd2BatchProcessor.deletedByBatchIdColName, LongType, 
nullable = true)
     val scd2AuxiliaryTableSchema = StructType(targetTableSchema.fields :+ 
deletedByBatchIdField)
 
+    // The effective track-history column set, resolved by the flow from its 
user-selected source
+    // schema (see [[AutoCdcMergeFlow.trackHistoryColumnNames]]) -- NOT 
recomputed here from the
+    // evolved target schema, which would keep tracking columns the flow no 
longer selects and would
+    // miss implicit (default / `* EXCEPT`) tracked-set changes. A change in 
this set reinterprets
+    // which transitions open a new historical record, so it is drift-checked.
+    val trackHistoryColumnNames = 
inputAutoCdcFlow.trackHistoryColumnNames.getOrElse(
+      throw SparkException.internalError(
+        "SCD2 AutoCDC flow is missing its resolved track-history column set."
+      )
+    )
+
     val scd2AuxiliaryTableProperties =
       // Record which SCD strategy this auxiliary table serves so downstream 
readers can identify it
       // without inspecting the schema.
       Map(scdTypePropertyKey -> ScdType.Type2.label) ++
       // Persist the AutoCDC key column names as a JSON list; immutable 
post-creation (full-refresh
       // is the only way to change it).
-      Map(keyColumnNamesProperty -> 
serializeKeyColumnNames(keyFields.map(_.name))) ++
+      Map(keyColumnNamesProperty -> 
serializeColumnNames(keyFields.map(_.name))) ++
+      // Persist the resolved track-history column names; a change 
reinterprets already-reconciled
+      // history, so it is immutable post-creation (full-refresh is the only 
way to change it).
+      Map(trackHistoryColumnNamesProperty -> 
serializeColumnNames(trackHistoryColumnNames)) ++
       // Inherit the target's format so MERGE semantics line up. When 
unspecified, omit the provider
       // so the catalog falls back to its default.
       targetTable.format.map(TableCatalog.PROP_PROVIDER -> _)
@@ -244,7 +270,9 @@ object AutoCdcAuxiliaryTable {
       properties = scd2AuxiliaryTableProperties,
       targetTableIdentifier = targetTable.identifier,
       expectedKeyFields = keyFields,
-      expectedScdType = ScdType.Type2
+      expectedScdType = ScdType.Type2,
+      expectedSequencingType = inputAutoCdcFlow.sequencingType,
+      expectedTrackHistoryColumnNames = Some(trackHistoryColumnNames)
     )
   }
 
@@ -373,6 +401,114 @@ object AutoCdcAuxiliaryTable {
     }
   }
 
+  /**
+   * Reject an incremental update to an existing AutoCDC target table whose 
sequencing type has
+   * drifted. The AutoCDC sequencing *expression* may legitimately change 
across runs (e.g. a new
+   * timestamp parse format), but its resolved result type must not: the 
target persists the
+   * sequencing type inside its `_cdc_metadata` struct (and, for SCD2, in the 
interval columns), so
+   * a changed type would make new events incomparable with the persisted 
history and would
+   * otherwise surface only as a generic CANNOT_MERGE_INCOMPATIBLE_DATA_TYPE 
during schema
+   * evolution. Runs against the target table (before its schema is evolved), 
not the auxiliary
+   * table. The remedy is a full refresh.
+   *
+   * @param existingTargetSchema the schema of the already-materialized target 
table.
+   * @param expectedScdType the SCD type of the incoming AutoCDC flow, which 
determines which inner
+   *                        `_cdc_metadata` field carries the recorded 
sequencing type.
+   * @param expectedSequencingType the resolved sequencing type of the 
incoming AutoCDC flow.
+   * @param resolver the session resolver, used to match the reserved column 
and inner field names
+   *                 the same case-aware way as every other schema lookup in 
this file.
+   */
+  private[graph] def validateNoTargetSequencingTypeDrift(
+      existingTargetSchema: StructType,
+      targetTableIdentifier: TableIdentifier,
+      expectedScdType: ScdType,
+      expectedSequencingType: DataType,
+      resolver: Resolver): Unit = {
+    // The sequencing type is embedded as an inner field of the reserved 
_cdc_metadata struct: for
+    // SCD1 the delete/upsert sequence fields, for SCD2 the recordStartAt 
field. Look the field up
+    // by name (not by position) so a future metadata field added at position 
0 cannot silently
+    // shift this to an unrelated type, and via the resolver so a 
case-differing hand-written target
+    // DDL resolves the same way it does everywhere else. If the metadata 
column is absent, not a
+    // struct, or lacks the expected inner field, this is not a recognizable 
AutoCDC target state;
+    // skip rather than misreport (schema evolution will surface any genuine 
incompatibility).
+    val sequencingFieldName = expectedScdType match {
+      case ScdType.Type1 => Scd1BatchProcessor.cdcUpsertSequenceFieldName
+      case ScdType.Type2 => Scd2BatchProcessor.recordStartAtFieldName
+    }
+    val recordedSequencingType: Option[DataType] = existingTargetSchema.fields
+      .find(f => resolver(f.name, AutoCdcReservedNames.cdcMetadataColName))
+      .map(_.dataType)
+      .collect { case s: StructType => s }
+      .flatMap(_.fields.find(f => resolver(f.name, sequencingFieldName)))
+      .map(_.dataType)
+
+    recordedSequencingType.foreach { recordedType =>
+      if (!recordedType.sameType(expectedSequencingType)) {
+        throw new AnalysisException(
+          errorClass = "AUTOCDC_INVALID_STATE.SEQUENCING_TYPE_DRIFT",
+          messageParameters = Map(
+            "tableName" -> targetTableIdentifier.unquotedString,
+            "expectedSequencingType" -> expectedSequencingType.sql,
+            "recordedSequencingType" -> recordedType.sql
+          )
+        )
+      }
+    }
+  }
+
+  /**
+   * Reject an existing SCD2 auxiliary table whose recorded track-history 
column set differs from
+   * `expected` (order-insensitive, resolver-aware). These columns define an 
SCD2 run - a change in
+   * any of them opens a new historical record - so changing the set would 
reinterpret already
+   * reconciled history. The remedy is a full refresh.
+   *
+   * `expected` is `None` for SCD1 (no track-history concept); the check is 
then a no-op.
+   */
+  private[graph] def validateNoTrackHistoryDrift(
+      existingAuxiliaryTable: CatalogTable,
+      targetTableIdentifier: TableIdentifier,
+      expectedTrackHistoryColumnNames: Option[Seq[String]],
+      resolver: Resolver): Unit = {
+    expectedTrackHistoryColumnNames.foreach { expectedNames =>
+      val rawRecorded = Option(
+        
existingAuxiliaryTable.properties().get(trackHistoryColumnNamesProperty)
+      ).getOrElse {
+        throw new AnalysisException(
+          errorClass = 
"AUTOCDC_INVALID_STATE.AUXILIARY_TABLE_PROPERTY_MISSING",
+          messageParameters = Map(
+            "tableName" -> targetTableIdentifier.unquotedString,
+            "propertyName" -> trackHistoryColumnNamesProperty
+          )
+        )
+      }
+      val recordedNames = parseColumnNames(rawRecorded).getOrElse {
+        throw new AnalysisException(
+          errorClass = 
"AUTOCDC_INVALID_STATE.AUXILIARY_TABLE_PROPERTY_MALFORMED",
+          messageParameters = Map(
+            "tableName" -> targetTableIdentifier.unquotedString,
+            "propertyName" -> trackHistoryColumnNamesProperty,
+            "rawValue" -> rawRecorded
+          )
+        )
+      }
+      // Set equality, resolver-aware: same arity and every expected name has 
a recorded
+      // counterpart. Order is irrelevant to run semantics.
+      val drifted =
+        recordedNames.length != expectedNames.length ||
+        expectedNames.exists(e => !recordedNames.exists(r => resolver(r, e)))
+      if (drifted) {
+        throw new AnalysisException(
+          errorClass = "AUTOCDC_INVALID_STATE.TRACK_HISTORY_DRIFT",
+          messageParameters = Map(
+            "tableName" -> targetTableIdentifier.unquotedString,
+            "expectedTrackHistoryColumns" -> expectedNames.mkString(", "),
+            "recordedTrackHistoryColumns" -> recordedNames.mkString(", ")
+          )
+        )
+      }
+    }
+  }
+
   /**
    * Read [[keyColumnNamesProperty]] off an existing auxiliary table and parse 
it into the ordered
    * list of recorded AutoCDC key column names.
@@ -391,7 +527,7 @@ object AutoCdcAuxiliaryTable {
         )
       )
     }
-    parseKeyColumnNames(rawKeyColumnNamesStr).getOrElse {
+    parseColumnNames(rawKeyColumnNamesStr).getOrElse {
       throw new AnalysisException(
         errorClass = 
"AUTOCDC_INVALID_STATE.AUXILIARY_TABLE_PROPERTY_MALFORMED",
         messageParameters = Map(
diff --git 
a/sql/pipelines/src/main/scala/org/apache/spark/sql/pipelines/graph/AuxiliaryTableSpec.scala
 
b/sql/pipelines/src/main/scala/org/apache/spark/sql/pipelines/graph/AuxiliaryTableSpec.scala
index 55cd19a1f8c4..71ad220c4cea 100644
--- 
a/sql/pipelines/src/main/scala/org/apache/spark/sql/pipelines/graph/AuxiliaryTableSpec.scala
+++ 
b/sql/pipelines/src/main/scala/org/apache/spark/sql/pipelines/graph/AuxiliaryTableSpec.scala
@@ -19,7 +19,7 @@ package org.apache.spark.sql.pipelines.graph
 
 import org.apache.spark.sql.catalyst.TableIdentifier
 import org.apache.spark.sql.pipelines.autocdc.ScdType
-import org.apache.spark.sql.types.{StructField, StructType}
+import org.apache.spark.sql.types.{DataType, StructField, StructType}
 
 /**
  * A specification for an internal auxiliary table whose lifecycle follows a 
materialized
@@ -70,6 +70,13 @@ sealed trait AuxiliaryTableSpec {
  *                             it already exists, in order (names and types).
  * @param expectedScdType      the SCD type the auxiliary table is expected to 
have recorded, if it
  *                             already exists.
+ * @param expectedSequencingType the resolved 
[[org.apache.spark.sql.types.DataType]] of the
+ *                             AutoCDC sequencing expression. The expression 
may change across runs
+ *                             but its result type must not, so the persisted 
interval/recordStartAt
+ *                             values stay comparable.
+ * @param expectedTrackHistoryColumnNames the resolved SCD2 track-history 
column names the auxiliary
+ *                             table is expected to have recorded, in order. 
`None` for SCD1 (which
+ *                             has no track-history concept), so the check is 
skipped there.
  */
 final case class AutoCdcAuxiliaryTableSpec(
     identifier: TableIdentifier,
@@ -77,4 +84,6 @@ final case class AutoCdcAuxiliaryTableSpec(
     properties: Map[String, String],
     targetTableIdentifier: TableIdentifier,
     expectedKeyFields: Seq[StructField],
-    expectedScdType: ScdType) extends AuxiliaryTableSpec
+    expectedScdType: ScdType,
+    expectedSequencingType: DataType,
+    expectedTrackHistoryColumnNames: Option[Seq[String]]) extends 
AuxiliaryTableSpec
diff --git 
a/sql/pipelines/src/main/scala/org/apache/spark/sql/pipelines/graph/DatasetManager.scala
 
b/sql/pipelines/src/main/scala/org/apache/spark/sql/pipelines/graph/DatasetManager.scala
index 4f96f2f70990..cf5f811d3461 100644
--- 
a/sql/pipelines/src/main/scala/org/apache/spark/sql/pipelines/graph/DatasetManager.scala
+++ 
b/sql/pipelines/src/main/scala/org/apache/spark/sql/pipelines/graph/DatasetManager.scala
@@ -24,7 +24,7 @@ import org.apache.spark.SparkException
 import org.apache.spark.internal.{Logging, LogKeys}
 import org.apache.spark.sql.AnalysisException
 import org.apache.spark.sql.catalyst.TableIdentifier
-import org.apache.spark.sql.catalyst.analysis.PersistedView
+import org.apache.spark.sql.catalyst.analysis.{NoSuchTableException, 
PersistedView}
 import org.apache.spark.sql.classic.SparkSession
 import org.apache.spark.sql.connector.catalog.{
   CatalogV2Util,
@@ -107,16 +107,30 @@ object DatasetManager extends Logging {
             if (tablesToMaterialize.keySet.contains(table.identifier)) {
               try {
                 val isFullRefresh = 
tablesToMaterialize(table.identifier).isFullRefresh
+                // Load the existing auxiliary table (if any) once here and 
thread the snapshot into
+                // both materializeTable (which uses it for AutoCDC 
config-drift validation) and
+                // materializeAuxiliaryTable (which uses it to decide 
evolve-vs-create). Nothing
+                // between them mutates the auxiliary table, so a single load 
is safe and avoids a
+                // redundant catalog round trip.
+                val auxiliaryTableSpecOpt =
+                  
resolvedDataflowGraph.auxiliaryTableSpecs.get(table.identifier)
+                val existingAuxiliaryTable = auxiliaryTableSpecOpt.flatMap { 
spec =>
+                  val (auxCatalog, auxId) =
+                    PipelinesCatalogUtils.resolveTableCatalog(context.spark, 
spec.identifier)
+                  loadTableIfExists(auxCatalog, auxId)
+                }
                 val (tableWithMaterializationMetadata, catalogTableEntity) = 
materializeTable(
                   resolvedDataflowGraph = resolvedDataflowGraph,
                   table = table,
                   isFullRefresh = isFullRefresh,
+                  auxiliaryTableSpecOpt = auxiliaryTableSpecOpt,
+                  existingAuxiliaryTable = existingAuxiliaryTable,
                   context = context
                 )
                 // Auxiliary tables' lifecycle should follow the table that it 
is complementary to.
                 // If this table has any auxiliary tables, validate the target 
can host them and
                 // materialize/full-refresh them accordingly.
-                
resolvedDataflowGraph.auxiliaryTableSpecs.get(table.identifier).foreach {
+                auxiliaryTableSpecOpt.foreach {
                   auxiliaryTableSpec =>
                     // If this table is an AutoCDC target table, as identified 
by being
                     // accompanied by an AutoCDC auxiliary table, additionally 
validate that the
@@ -134,6 +148,7 @@ object DatasetManager extends Logging {
                     materializeAuxiliaryTable(
                       auxiliaryTableSpec = auxiliaryTableSpec,
                       isFullRefresh = isFullRefresh,
+                      existingAuxiliaryTable = existingAuxiliaryTable,
                       context = context
                     )
                 }
@@ -270,6 +285,10 @@ object DatasetManager extends Logging {
    * @param resolvedDataflowGraph The resolved [[DataflowGraph]] used to infer 
the table schema.
    * @param table The table to be materialized.
    * @param isFullRefresh Whether this table should be full refreshed or not.
+   * @param auxiliaryTableSpecOpt The spec for the auxiliary table (if this 
table has one)
+   * @param existingAuxiliaryTable The already-loaded auxiliary table for this 
target (if it has one
+   *                               and it exists), used for AutoCDC 
config-drift validation. Loaded
+   *                               once by the caller and shared with 
[[materializeAuxiliaryTable]].
    * @param context The context for the pipeline update.
    * @return The materialized graph [[Table]] (with additional metadata set) 
paired with the loaded
    *         DSv2 handle of the just created/evolved table.
@@ -278,6 +297,8 @@ object DatasetManager extends Logging {
       resolvedDataflowGraph: DataflowGraph,
       table: Table,
       isFullRefresh: Boolean,
+      auxiliaryTableSpecOpt: Option[AuxiliaryTableSpec],
+      existingAuxiliaryTable: Option[V2Table],
       context: PipelineUpdateContext): (Table, V2Table) = {
     logInfo(log"Materializing metadata for table ${MDC(LogKeys.TABLE_NAME, 
table.identifier)}.")
     // Get the DSv2 catalog handler and identifier for the table.
@@ -330,9 +351,46 @@ object DatasetManager extends Logging {
       context.spark.sql(s"TRUNCATE TABLE ${table.identifier.quotedString}")
     }
 
+    val autoCdcAuxTableSpecOpt = auxiliaryTableSpecOpt.collect {
+        case autoCdcSpec: AutoCdcAuxiliaryTableSpec => autoCdcSpec
+      }
+
+    // For an incrementally-updated AutoCDC target, validate that the AutoCDC 
configuration recorded
+    // on the auxiliary table has not drifted, BEFORE anything is created or 
evolved this run. These
+    // checks read the auxiliary table, so they run whenever IT exists -- 
independent of whether the
+    // target exists. That matters when a user drops and recreates the target 
without dropping the
+    // internal auxiliary table: the target is then absent (so it is 
re-created below) but the stale
+    // auxiliary table survives, and `materializeAuxiliaryTable`'s additive 
evolve would otherwise
+    // silently overwrite the recorded key/SCD-type/track-history properties 
with this run's values.
+    // Running here turns that into one clear drift error (remedy: full 
refresh).
+    if (isTableIncrementallyUpdated) {
+      autoCdcAuxTableSpecOpt.foreach {
+        validateNoAutoCdcAuxConfigDrift(_, existingAuxiliaryTable, context)
+      }
+    }
+
     // Create the table if absent, otherwise evolve it (schema + properties).
     existingTableOpt match {
       case Some(existingTable) =>
+        // The sequencing-type check needs the existing target schema (the 
type is embedded in the
+        // target's `_cdc_metadata`), so it runs here, and BEFORE 
`evolveTable`: `evolveTable`
+        // ALTERs the target (additively) in place, so a check that ran 
afterwards would leave the
+        // target already mutated by a run it then rejects -- and the drift 
remedy could not undo
+        // that. Running first means a rejected run leaves the target 
untouched, and surfaces the
+        // change as an actionable SEQUENCING_TYPE_DRIFT rather than a generic
+        // CANNOT_MERGE_INCOMPATIBLE_DATA_TYPE from the schema merge.
+        if (isTableIncrementallyUpdated) {
+          autoCdcAuxTableSpecOpt.foreach { autoCdcSpec =>
+            AutoCdcAuxiliaryTable.validateNoTargetSequencingTypeDrift(
+              existingTargetSchema =
+                CatalogV2Util.v2ColumnsToStructType(existingTable.columns()),
+              targetTableIdentifier = autoCdcSpec.targetTableIdentifier,
+              expectedScdType = autoCdcSpec.expectedScdType,
+              expectedSequencingType = autoCdcSpec.expectedSequencingType,
+              resolver = context.spark.sessionState.conf.resolver
+            )
+          }
+        }
         evolveTable(
           catalog = catalog,
           tableIdentifier = identifier,
@@ -394,11 +452,15 @@ object DatasetManager extends Logging {
    *
    * @param auxiliaryTableSpec the spec describing the auxiliary table to 
create/evolve.
    * @param isFullRefresh whether the owning table is being fully refreshed.
+   * @param existingAuxiliaryTable the already-loaded auxiliary table (if it 
exists), loaded once by
+   *                               the caller and shared with the config-drift 
validation in
+   *                               [[materializeTable]] rather than re-loaded 
here.
    * @param context the context for the pipeline update.
    */
   private def materializeAuxiliaryTable(
       auxiliaryTableSpec: AuxiliaryTableSpec,
       isFullRefresh: Boolean,
+      existingAuxiliaryTable: Option[V2Table],
       context: PipelineUpdateContext): Unit = {
     // Get the DSv2 catalog handler and identifier for the aux table.
     val (catalog, auxiliaryTableIdentifier) =
@@ -434,30 +496,19 @@ object DatasetManager extends Logging {
         transforms = Seq.empty
       )
     } else {
-      loadTableIfExists(catalog, auxiliaryTableIdentifier) match {
-        case Some(existingAuxiliaryTable) =>
-          auxiliaryTableSpec match {
-            case autoCdcSpec: AutoCdcAuxiliaryTableSpec =>
-              // For AutoCDC auxiliary tables specifically, we persist 
metadata about the AutoCDC
-              // configuration that should be invariant for the flow's 
lifetime; i.e until it is
-              // full-refreshed. Validate these configurations remain 
invariant before attempting
-              // to evolve the auxiliary table's schema, to prevent corrupting 
the table.
-              AutoCdcAuxiliaryTable.validateNoKeyColumnDrift(
-                existingAuxiliaryTable = existingAuxiliaryTable,
-                targetTableIdentifier = autoCdcSpec.targetTableIdentifier,
-                expectedKeyFields = autoCdcSpec.expectedKeyFields,
-                resolver = context.spark.sessionState.conf.resolver
-              )
-              AutoCdcAuxiliaryTable.validateNoScdTypeDrift(
-                existingAuxiliaryTable = existingAuxiliaryTable,
-                targetTableIdentifier = autoCdcSpec.targetTableIdentifier,
-                expectedScdType = autoCdcSpec.expectedScdType
-              )
-          }
+      // Uses the auxiliary-table snapshot loaded by the caller (see 
materializeDatasets), rather
+      // than re-loading it here.
+      existingAuxiliaryTable match {
+        case Some(existingAuxTable) =>
+          // NOTE: AutoCDC configuration-drift validation (key columns, SCD 
type, sequencing type,
+          // track-history columns) intentionally runs in [[materializeTable]] 
BEFORE the target's
+          // schema is evolved, not here -- see 
`validateNoAutoCdcAuxConfigDrift`. Validating here
+          // would be too late: the target has already been ALTERed by the 
time an aux-owned check
+          // could reject the run.
           evolveTable(
             catalog = catalog,
             tableIdentifier = auxiliaryTableIdentifier,
-            existingTable = existingAuxiliaryTable,
+            existingTable = existingAuxTable,
             desiredSchema = auxiliaryTableSpec.schema,
             properties = auxiliaryTableSpec.properties,
             mergeWithExistingSchema = true
@@ -474,11 +525,62 @@ object DatasetManager extends Logging {
     }
   }
 
-  /** Loads the table at `identifier` from `catalog`, or `None` if it does not 
exist. */
+  /**
+   * Validate that an incrementally-updated AutoCDC flow's configuration has 
not drifted from what
+   * its auxiliary table recorded. Called from [[materializeTable]] before the 
target and auxiliary
+   * tables are created/evolved this run, so a rejected run leaves both 
untouched.
+   *
+   * Covers the checks that read the recorded configuration off the existing 
auxiliary table: key
+   * columns, SCD type, and track-history columns. Runs whenever the auxiliary 
table exists,
+   * independent of the target's existence (see the call site). If the 
auxiliary table does not
+   * exist yet (first AutoCDC run), all three are skipped -- there is no 
recorded configuration to
+   * drift from. The sequencing-type check is separate 
([[AutoCdcAuxiliaryTable]]
+   * `.validateNoTargetSequencingTypeDrift`) because it reads the target 
schema, not the aux table.
+   *
+   * @param autoCdcSpec the auxiliary-table spec carrying this run's expected 
AutoCDC configuration.
+   * @param existingAuxiliaryTableOpt the already-loaded auxiliary table (if 
it exists), shared with
+   *                                  the caller and 
[[materializeAuxiliaryTable]] to avoid a
+   *                                  redundant load.
+   * @param context the context for the pipeline update.
+   */
+  private def validateNoAutoCdcAuxConfigDrift(
+      autoCdcSpec: AutoCdcAuxiliaryTableSpec,
+      existingAuxiliaryTableOpt: Option[V2Table],
+      context: PipelineUpdateContext): Unit = {
+    val resolver = context.spark.sessionState.conf.resolver
+    existingAuxiliaryTableOpt.foreach { existingAuxiliaryTable =>
+      AutoCdcAuxiliaryTable.validateNoKeyColumnDrift(
+        existingAuxiliaryTable = existingAuxiliaryTable,
+        targetTableIdentifier = autoCdcSpec.targetTableIdentifier,
+        expectedKeyFields = autoCdcSpec.expectedKeyFields,
+        resolver = resolver
+      )
+      AutoCdcAuxiliaryTable.validateNoScdTypeDrift(
+        existingAuxiliaryTable = existingAuxiliaryTable,
+        targetTableIdentifier = autoCdcSpec.targetTableIdentifier,
+        expectedScdType = autoCdcSpec.expectedScdType
+      )
+      AutoCdcAuxiliaryTable.validateNoTrackHistoryDrift(
+        existingAuxiliaryTable = existingAuxiliaryTable,
+        targetTableIdentifier = autoCdcSpec.targetTableIdentifier,
+        expectedTrackHistoryColumnNames = 
autoCdcSpec.expectedTrackHistoryColumnNames,
+        resolver = resolver
+      )
+    }
+  }
+
+  /**
+   * Loads the table at `identifier` from `catalog`, or `None` if it does not 
exist. A single
+   * `loadTable` guarded by a `NoSuchTableException` catch, rather than a 
`tableExists` +
+   * `loadTable` pair: one catalog round trip instead of two, and no window 
where the table can
+   * disappear between the existence check and the load. A missing *namespace* 
is not treated as
+   * "table absent" -- it surfaces as its own `NoSuchDatabaseException` rather 
than being swallowed.
+   */
   private def loadTableIfExists(
       catalog: TableCatalog,
       identifier: Identifier): Option[V2Table] = {
-    Option.when(catalog.tableExists(identifier))(catalog.loadTable(identifier))
+    try Some(catalog.loadTable(identifier))
+    catch { case _: NoSuchTableException => None }
   }
 
   /**
diff --git 
a/sql/pipelines/src/main/scala/org/apache/spark/sql/pipelines/graph/Flow.scala 
b/sql/pipelines/src/main/scala/org/apache/spark/sql/pipelines/graph/Flow.scala
index 54525aab8712..856fd6a3923c 100644
--- 
a/sql/pipelines/src/main/scala/org/apache/spark/sql/pipelines/graph/Flow.scala
+++ 
b/sql/pipelines/src/main/scala/org/apache/spark/sql/pipelines/graph/Flow.scala
@@ -268,9 +268,6 @@ class AutoCdcMergeFlow(
     // AutoCDC flows require all key columns to be present in the 
user-selected source schema,
     // so that they survive into the target table where SCD reconciliation 
needs them.
     requireKeysPresentInSelectedSchema(selectedSchema)
-    // SCD2 flows may specify history-tracking columns; validate they resolve 
to eligible columns
-    // of the selected schema at construction time, rather than failing 
mid-stream on first batch.
-    requireTrackHistoryColumnsResolvableInSelectedSchema(selectedSchema)
     selectedSchema
   }
 
@@ -278,6 +275,38 @@ class AutoCdcMergeFlow(
   private[graph] val sequencingType: DataType =
     df.select(changeArgs.sequencing).schema.head.dataType
 
+  /**
+   * SCD2 only: the effective set of history-tracking column names for this 
flow, resolved from the
+   * [[userSelectedSchema]] (the user-selected source columns), not from the 
persisted/evolved
+   * target schema. This is the single source of truth for the tracked set:
+   *
+   *  - Resolving against [[userSelectedSchema]] means the tracked set follows 
the flow's own
+   *    selection. In particular, under default or `* EXCEPT` tracking, 
dropping a column from the
+   *    source (or from the `COLUMNS` selection) removes it from the tracked 
set -- rather than the
+   *    column lingering as tracked because it still exists in the (sticky) 
target schema.
+   *  - It is recorded on the auxiliary table and drift-checked across runs: a 
change to this set
+   *    reinterprets which transitions open a new SCD2 record, which cannot be 
applied to
+   *    already-reconciled history, so any change requires a full refresh (see
+   *    [[AutoCdcAuxiliaryTable.validateNoTrackHistoryDrift]]). Because the 
set is
+   *    selection-derived, adding or dropping a source column under default / 
`* EXCEPT` tracking
+   *    is such a change; this is an intended divergence from SCD1, where 
non-key schema evolution
+   *    needs no full refresh.
+   *
+   * Computing it here (rather than in the aux-table spec builder from the 
target schema) also
+   * validates the selection at construction time: an unresolvable or 
ineligible explicit
+   * `TRACK HISTORY ON` selection throws `AUTOCDC_COLUMNS_NOT_FOUND_IN_SCHEMA` 
here, before the
+   * first microbatch, rather than failing deep inside the SCD2 batch 
processor. `None` for SCD1.
+   */
+  private[graph] val trackHistoryColumnNames: Option[Seq[String]] =
+    changeArgs.storedAsScdType match {
+      case ScdType.Type2 =>
+        Some(Scd2BatchProcessor.computeTrackedHistoryColumns(
+          schema = userSelectedSchema,
+          changeArgs = changeArgs,
+          resolver = spark.sessionState.conf.resolver))
+      case ScdType.Type1 => None
+    }
+
   /**
    * Returns the augmented output schema of this flow, which can differ from 
the schema of the
    * source change-data-feed dataframe.
@@ -455,25 +484,4 @@ class AutoCdcMergeFlow(
       }
   }
 
-  /**
-   * Validate that this flow's [[ChangeArgs.trackHistorySelection]] (SCD2 
`TRACK HISTORY ON ...`)
-   * resolves against the user-selected source schema at construction time. 
Without this, an
-   * unresolvable or ineligible (key/framework) tracking column would only 
surface when the first
-   * microbatch runs reconciliation, deep inside the SCD2 batch processor.
-   *
-   * Delegates to [[Scd2BatchProcessor.computeTrackedHistoryColumns]] -- the 
same resolution used at
-   * runtime -- so the two can never diverge; it throws 
`AUTOCDC_COLUMNS_NOT_FOUND_IN_SCHEMA` on an
-   * unresolvable selection. `trackHistorySelection` is `None` for SCD1 
(enforced by [[ChangeArgs]])
-   * and for SCD2 flows that do not restrict tracking, in which case 
resolution is a no-op.
-   */
-  private def requireTrackHistoryColumnsResolvableInSelectedSchema(
-      selectedSchema: StructType): Unit = {
-    if (changeArgs.trackHistorySelection.isDefined) {
-      Scd2BatchProcessor.computeTrackedHistoryColumns(
-        schema = selectedSchema,
-        changeArgs = changeArgs,
-        resolver = spark.sessionState.conf.resolver
-      )
-    }
-  }
 }
diff --git 
a/sql/pipelines/src/test/scala/org/apache/spark/sql/pipelines/graph/AutoCdcAuxiliaryTableSuite.scala
 
b/sql/pipelines/src/test/scala/org/apache/spark/sql/pipelines/graph/AutoCdcAuxiliaryTableSuite.scala
index fb368198f72d..27ae39d9bc99 100644
--- 
a/sql/pipelines/src/test/scala/org/apache/spark/sql/pipelines/graph/AutoCdcAuxiliaryTableSuite.scala
+++ 
b/sql/pipelines/src/test/scala/org/apache/spark/sql/pipelines/graph/AutoCdcAuxiliaryTableSuite.scala
@@ -22,8 +22,15 @@ import scala.jdk.CollectionConverters._
 import org.apache.spark.SparkFunSuite
 import org.apache.spark.sql.AnalysisException
 import org.apache.spark.sql.catalyst.TableIdentifier
+import org.apache.spark.sql.catalyst.analysis.{caseInsensitiveResolution, 
caseSensitiveResolution}
 import org.apache.spark.sql.connector.catalog.{Table, TableCapability}
-import org.apache.spark.sql.pipelines.autocdc.ScdType
+import org.apache.spark.sql.pipelines.autocdc.{
+  AutoCdcReservedNames,
+  Scd1BatchProcessor,
+  Scd2BatchProcessor,
+  ScdType
+}
+import org.apache.spark.sql.types.{IntegerType, LongType, StringType, 
StructType}
 
 /**
  * Unit tests for the [[AutoCdcAuxiliaryTable]] companion object.
@@ -44,9 +51,9 @@ class AutoCdcAuxiliaryTableSuite extends SparkFunSuite {
   // concern -- SQL identifier quoting (backticks) is never part of the stored 
bytes.
 
   private def assertKeyColumnNamesRoundTrip(names: Seq[String]): Unit = {
-    val json = AutoCdcAuxiliaryTable.serializeKeyColumnNames(names)
+    val json = AutoCdcAuxiliaryTable.serializeColumnNames(names)
     assert(
-      AutoCdcAuxiliaryTable.parseKeyColumnNames(json).contains(names),
+      AutoCdcAuxiliaryTable.parseColumnNames(json).contains(names),
       s"round-trip failed: input=${names}, serialized=${json}"
     )
   }
@@ -59,19 +66,19 @@ class AutoCdcAuxiliaryTableSuite extends SparkFunSuite {
     override def properties(): java.util.Map[String, String] = props.asJava
   }
 
-  test("serializeKeyColumnNames/parseKeyColumnNames round-trip preserves plain 
ASCII names") {
+  test("serializeColumnNames/parseColumnNames round-trip preserves plain ASCII 
names") {
     assertKeyColumnNamesRoundTrip(Seq("id"))
     assertKeyColumnNamesRoundTrip(Seq("id", "region"))
     assertKeyColumnNamesRoundTrip(Seq("id", "region", "country"))
   }
 
-  test("serializeKeyColumnNames/parseKeyColumnNames round-trip preserves the 
empty list") {
+  test("serializeColumnNames/parseColumnNames round-trip preserves the empty 
list") {
     // Empty key sets are not user-reachable (AutoCdcMergeFlow rejects them 
upstream), but the
     // helpers themselves must round-trip a `[]` JSON array faithfully.
     assertKeyColumnNamesRoundTrip(Seq.empty)
   }
 
-  test("serializeKeyColumnNames/parseKeyColumnNames preserves names containing 
JSON-escaped " +
+  test("serializeColumnNames/parseColumnNames preserves names containing 
JSON-escaped " +
     "characters (quote, backslash, control chars)") {
     // JSON serializer must escape `"` -> `\"`, `\` -> `\\`, and control 
chars; the parser
     // must invert those escapes and yield the original literal bytes.
@@ -83,7 +90,7 @@ class AutoCdcAuxiliaryTableSuite extends SparkFunSuite {
     assertKeyColumnNamesRoundTrip(Seq("a\"b\\c\nd"))
   }
 
-  test("serializeKeyColumnNames/parseKeyColumnNames preserves names containing 
characters " +
+  test("serializeColumnNames/parseColumnNames preserves names containing 
characters " +
     "that JSON does not escape (single quote, dot, space, backtick)") {
     // JSON does not escape these, but they are common in real-world 
identifiers (especially
     // when users backtick-quote at the API boundary). They must flow through 
verbatim.
@@ -96,18 +103,18 @@ class AutoCdcAuxiliaryTableSuite extends SparkFunSuite {
     assertKeyColumnNamesRoundTrip(Seq("it's", "name with spaces", "a.b.c", 
"back`tick"))
   }
 
-  test("parseKeyColumnNames returns None for inputs that are not a JSON array 
of strings") {
+  test("parseColumnNames returns None for inputs that are not a JSON array of 
strings") {
     // None of these are a top-level JSON array of strings; the parser must 
reject every shape
     // with `None` so callers can surface a structured INTERNAL_ERROR with 
consistent wording.
-    assert(AutoCdcAuxiliaryTable.parseKeyColumnNames("not-json").isEmpty)
-    assert(AutoCdcAuxiliaryTable.parseKeyColumnNames("").isEmpty)
-    assert(AutoCdcAuxiliaryTable.parseKeyColumnNames("\"id\"").isEmpty)        
// bare string
-    assert(AutoCdcAuxiliaryTable.parseKeyColumnNames("null").isEmpty)
-    assert(AutoCdcAuxiliaryTable.parseKeyColumnNames("{\"id\": 1}").isEmpty)   
// object
-    assert(AutoCdcAuxiliaryTable.parseKeyColumnNames("[1, 2, 3]").isEmpty)     
// numbers
-    assert(AutoCdcAuxiliaryTable.parseKeyColumnNames("[\"id\", 1]").isEmpty)   
// mixed types
-    assert(AutoCdcAuxiliaryTable.parseKeyColumnNames("[\"id\", null]").isEmpty)
-    assert(AutoCdcAuxiliaryTable.parseKeyColumnNames("[[\"id\"]]").isEmpty)    
// nested array
+    assert(AutoCdcAuxiliaryTable.parseColumnNames("not-json").isEmpty)
+    assert(AutoCdcAuxiliaryTable.parseColumnNames("").isEmpty)
+    assert(AutoCdcAuxiliaryTable.parseColumnNames("\"id\"").isEmpty)        // 
bare string
+    assert(AutoCdcAuxiliaryTable.parseColumnNames("null").isEmpty)
+    assert(AutoCdcAuxiliaryTable.parseColumnNames("{\"id\": 1}").isEmpty)   // 
object
+    assert(AutoCdcAuxiliaryTable.parseColumnNames("[1, 2, 3]").isEmpty)     // 
numbers
+    assert(AutoCdcAuxiliaryTable.parseColumnNames("[\"id\", 1]").isEmpty)   // 
mixed types
+    assert(AutoCdcAuxiliaryTable.parseColumnNames("[\"id\", null]").isEmpty)
+    assert(AutoCdcAuxiliaryTable.parseColumnNames("[[\"id\"]]").isEmpty)    // 
nested array
   }
 
   test("validateNoScdTypeDrift accepts an auxiliary table whose recorded SCD 
type matches") {
@@ -156,4 +163,263 @@ class AutoCdcAuxiliaryTableSuite extends SparkFunSuite {
         "tableName" -> TableIdentifier("target", Some("ns"), 
Some("cat")).unquotedString,
         "propertyName" -> AutoCdcAuxiliaryTable.scdTypePropertyKey))
   }
+
+  private val targetIdent = TableIdentifier("target", Some("ns"), Some("cat"))
+
+  /** An auxiliary table stub recording the given track-history column names 
as JSON. */
+  private def auxTableWithTrackHistory(names: Seq[String]): Table =
+    auxTableWithProperties(Map(
+      AutoCdcAuxiliaryTable.trackHistoryColumnNamesProperty ->
+        AutoCdcAuxiliaryTable.serializeColumnNames(names)))
+
+  test("validateNoTrackHistoryDrift is a no-op when the expected column set is 
None") {
+    // A None expected set means the flow does not constrain track-history 
(SCD1, or an SCD2 flow
+    // whose default resolution has not been computed here); the validator 
must not even read the
+    // property. Passing an empty-properties table proves nothing is 
dereferenced.
+    AutoCdcAuxiliaryTable.validateNoTrackHistoryDrift(
+      existingAuxiliaryTable = auxTableWithProperties(Map.empty),
+      targetTableIdentifier = targetIdent,
+      expectedTrackHistoryColumnNames = None,
+      resolver = caseInsensitiveResolution)
+  }
+
+  test("validateNoTrackHistoryDrift accepts a recorded set that matches 
regardless of order") {
+    val existing = auxTableWithTrackHistory(Seq("name", "amount", "seq"))
+    // Same set, different order: must not throw.
+    AutoCdcAuxiliaryTable.validateNoTrackHistoryDrift(
+      existingAuxiliaryTable = existing,
+      targetTableIdentifier = targetIdent,
+      expectedTrackHistoryColumnNames = Some(Seq("seq", "name", "amount")),
+      resolver = caseInsensitiveResolution)
+  }
+
+  test("validateNoTrackHistoryDrift compares case-insensitively under the 
default resolver, " +
+    "even when the stored property names differ only in case") {
+    // Isolates the resolver-aware comparison: the stored property holds 
`Name`/`AMOUNT` while the
+    // expected set holds `name`/`amount`. In the end-to-end path both sides 
are normalized to
+    // actual schema field names before comparison, so only a direct unit test 
can exercise a
+    // genuine case difference reaching the resolver. Under the default 
resolver, no drift.
+    val existing = auxTableWithTrackHistory(Seq("Name", "AMOUNT"))
+    AutoCdcAuxiliaryTable.validateNoTrackHistoryDrift(
+      existingAuxiliaryTable = existing,
+      targetTableIdentifier = targetIdent,
+      expectedTrackHistoryColumnNames = Some(Seq("name", "amount")),
+      resolver = caseInsensitiveResolution)
+  }
+
+  test("validateNoTrackHistoryDrift throws TRACK_HISTORY_DRIFT under the 
case-sensitive resolver " +
+    "when the stored property names differ only in case") {
+    // The mirror of the case-insensitive test: with the case-sensitive 
resolver, `Name` and
+    // `name` are distinct, so the same-cardinality sets do not match and the 
validator drifts.
+    val existing = auxTableWithTrackHistory(Seq("Name", "amount"))
+    val ex = intercept[AnalysisException] {
+      AutoCdcAuxiliaryTable.validateNoTrackHistoryDrift(
+        existingAuxiliaryTable = existing,
+        targetTableIdentifier = targetIdent,
+        expectedTrackHistoryColumnNames = Some(Seq("name", "amount")),
+        resolver = caseSensitiveResolution)
+    }
+    checkError(
+      exception = ex,
+      condition = "AUTOCDC_INVALID_STATE.TRACK_HISTORY_DRIFT",
+      sqlState = "42000",
+      parameters = Map(
+        "tableName" -> targetIdent.unquotedString,
+        "expectedTrackHistoryColumns" -> "name, amount",
+        "recordedTrackHistoryColumns" -> "Name, amount"))
+  }
+
+  test("validateNoTrackHistoryDrift throws TRACK_HISTORY_DRIFT when the 
recorded set differs") {
+    val existing = auxTableWithTrackHistory(Seq("name"))
+    val ex = intercept[AnalysisException] {
+      AutoCdcAuxiliaryTable.validateNoTrackHistoryDrift(
+        existingAuxiliaryTable = existing,
+        targetTableIdentifier = targetIdent,
+        expectedTrackHistoryColumnNames = Some(Seq("amount")),
+        resolver = caseInsensitiveResolution)
+    }
+    checkError(
+      exception = ex,
+      condition = "AUTOCDC_INVALID_STATE.TRACK_HISTORY_DRIFT",
+      sqlState = "42000",
+      parameters = Map(
+        "tableName" -> targetIdent.unquotedString,
+        "expectedTrackHistoryColumns" -> "amount",
+        "recordedTrackHistoryColumns" -> "name"))
+  }
+
+  test("validateNoTrackHistoryDrift throws AUXILIARY_TABLE_PROPERTY_MISSING 
when the " +
+    "track-history property is absent") {
+    // An SCD2 aux table created before this property existed: the validator 
must surface a
+    // structured error (remedy: full refresh) rather than skipping the check.
+    val ex = intercept[AnalysisException] {
+      AutoCdcAuxiliaryTable.validateNoTrackHistoryDrift(
+        existingAuxiliaryTable = auxTableWithProperties(Map.empty),
+        targetTableIdentifier = targetIdent,
+        expectedTrackHistoryColumnNames = Some(Seq("name")),
+        resolver = caseInsensitiveResolution)
+    }
+    checkError(
+      exception = ex,
+      condition = "AUTOCDC_INVALID_STATE.AUXILIARY_TABLE_PROPERTY_MISSING",
+      sqlState = "42000",
+      parameters = Map(
+        "tableName" -> targetIdent.unquotedString,
+        "propertyName" -> 
AutoCdcAuxiliaryTable.trackHistoryColumnNamesProperty))
+  }
+
+  test("validateNoTrackHistoryDrift throws AUXILIARY_TABLE_PROPERTY_MALFORMED 
when the " +
+    "track-history property is not a JSON array of strings") {
+    val existing = auxTableWithProperties(Map(
+      AutoCdcAuxiliaryTable.trackHistoryColumnNamesProperty -> 
"not-a-json-array"))
+    val ex = intercept[AnalysisException] {
+      AutoCdcAuxiliaryTable.validateNoTrackHistoryDrift(
+        existingAuxiliaryTable = existing,
+        targetTableIdentifier = targetIdent,
+        expectedTrackHistoryColumnNames = Some(Seq("name")),
+        resolver = caseInsensitiveResolution)
+    }
+    checkError(
+      exception = ex,
+      condition = "AUTOCDC_INVALID_STATE.AUXILIARY_TABLE_PROPERTY_MALFORMED",
+      sqlState = "42000",
+      parameters = Map(
+        "tableName" -> targetIdent.unquotedString,
+        "propertyName" -> 
AutoCdcAuxiliaryTable.trackHistoryColumnNamesProperty,
+        "rawValue" -> "not-a-json-array"))
+  }
+
+  // 
===========================================================================================
+  // validateNoTargetSequencingTypeDrift
+  // 
===========================================================================================
+
+  private val meta = AutoCdcReservedNames.cdcMetadataColName
+
+  /** An SCD1 target schema whose `_cdc_metadata` carries sequence fields of 
`seqType`. */
+  private def scd1TargetSchema(seqType: org.apache.spark.sql.types.DataType): 
StructType =
+    new StructType()
+      .add("id", IntegerType, nullable = false)
+      .add(meta, new StructType()
+        .add(Scd1BatchProcessor.cdcDeleteSequenceFieldName, seqType)
+        .add(Scd1BatchProcessor.cdcUpsertSequenceFieldName, seqType))
+
+  /** An SCD2 target schema whose `_cdc_metadata` carries a recordStartAt 
field of `seqType`. */
+  private def scd2TargetSchema(seqType: org.apache.spark.sql.types.DataType): 
StructType =
+    new StructType()
+      .add("id", IntegerType, nullable = false)
+      .add(meta, new StructType()
+        .add(Scd2BatchProcessor.recordStartAtFieldName, seqType))
+
+  test("validateNoTargetSequencingTypeDrift accepts a matching SCD1 sequencing 
type") {
+    AutoCdcAuxiliaryTable.validateNoTargetSequencingTypeDrift(
+      existingTargetSchema = scd1TargetSchema(LongType),
+      targetTableIdentifier = targetIdent,
+      expectedScdType = ScdType.Type1,
+      expectedSequencingType = LongType,
+      resolver = caseInsensitiveResolution)
+  }
+
+  test("validateNoTargetSequencingTypeDrift accepts a matching SCD2 sequencing 
type") {
+    AutoCdcAuxiliaryTable.validateNoTargetSequencingTypeDrift(
+      existingTargetSchema = scd2TargetSchema(LongType),
+      targetTableIdentifier = targetIdent,
+      expectedScdType = ScdType.Type2,
+      expectedSequencingType = LongType,
+      resolver = caseInsensitiveResolution)
+  }
+
+  test("validateNoTargetSequencingTypeDrift throws SEQUENCING_TYPE_DRIFT when 
the recorded " +
+    "type differs (SCD1)") {
+    val ex = intercept[AnalysisException] {
+      AutoCdcAuxiliaryTable.validateNoTargetSequencingTypeDrift(
+        existingTargetSchema = scd1TargetSchema(LongType),
+        targetTableIdentifier = targetIdent,
+        expectedScdType = ScdType.Type1,
+        expectedSequencingType = IntegerType,
+        resolver = caseInsensitiveResolution)
+    }
+    checkError(
+      exception = ex,
+      condition = "AUTOCDC_INVALID_STATE.SEQUENCING_TYPE_DRIFT",
+      sqlState = "42000",
+      parameters = Map(
+        "tableName" -> targetIdent.unquotedString,
+        "expectedSequencingType" -> IntegerType.sql,
+        "recordedSequencingType" -> LongType.sql))
+  }
+
+  test("validateNoTargetSequencingTypeDrift throws SEQUENCING_TYPE_DRIFT when 
the recorded " +
+    "type differs (SCD2)") {
+    val ex = intercept[AnalysisException] {
+      AutoCdcAuxiliaryTable.validateNoTargetSequencingTypeDrift(
+        existingTargetSchema = scd2TargetSchema(LongType),
+        targetTableIdentifier = targetIdent,
+        expectedScdType = ScdType.Type2,
+        expectedSequencingType = IntegerType,
+        resolver = caseInsensitiveResolution)
+    }
+    checkError(
+      exception = ex,
+      condition = "AUTOCDC_INVALID_STATE.SEQUENCING_TYPE_DRIFT",
+      sqlState = "42000",
+      parameters = Map(
+        "tableName" -> targetIdent.unquotedString,
+        "expectedSequencingType" -> IntegerType.sql,
+        "recordedSequencingType" -> LongType.sql))
+  }
+
+  test("validateNoTargetSequencingTypeDrift is a silent no-op when 
_cdc_metadata is absent") {
+    // Not a recognizable AutoCDC target state: skip rather than misreport. A 
genuine
+    // incompatibility would surface later during schema evolution.
+    AutoCdcAuxiliaryTable.validateNoTargetSequencingTypeDrift(
+      existingTargetSchema = new StructType().add("id", IntegerType, nullable 
= false),
+      targetTableIdentifier = targetIdent,
+      expectedScdType = ScdType.Type2,
+      expectedSequencingType = IntegerType,
+      resolver = caseInsensitiveResolution)
+  }
+
+  test("validateNoTargetSequencingTypeDrift is a silent no-op when 
_cdc_metadata is not a " +
+    "struct") {
+    AutoCdcAuxiliaryTable.validateNoTargetSequencingTypeDrift(
+      existingTargetSchema = new StructType()
+        .add("id", IntegerType, nullable = false)
+        .add(meta, StringType),
+      targetTableIdentifier = targetIdent,
+      expectedScdType = ScdType.Type2,
+      expectedSequencingType = IntegerType,
+      resolver = caseInsensitiveResolution)
+  }
+
+  test("validateNoTargetSequencingTypeDrift is a silent no-op when 
_cdc_metadata is an empty " +
+    "struct") {
+    AutoCdcAuxiliaryTable.validateNoTargetSequencingTypeDrift(
+      existingTargetSchema = new StructType()
+        .add("id", IntegerType, nullable = false)
+        .add(meta, new StructType()),
+      targetTableIdentifier = targetIdent,
+      expectedScdType = ScdType.Type2,
+      expectedSequencingType = IntegerType,
+      resolver = caseInsensitiveResolution)
+  }
+
+  test("validateNoTargetSequencingTypeDrift resolves _cdc_metadata and the 
inner field via the " +
+    "resolver (case-insensitive)") {
+    // A hand-written target DDL may differ in case; under the default 
resolver the check still
+    // finds the metadata column and the recordStartAt field, so a real type 
drift is caught.
+    val upperCasedSchema = new StructType()
+      .add("id", IntegerType, nullable = false)
+      .add(meta.toUpperCase(java.util.Locale.ROOT), new StructType()
+        
.add(Scd2BatchProcessor.recordStartAtFieldName.toLowerCase(java.util.Locale.ROOT),
+          LongType))
+    val ex = intercept[AnalysisException] {
+      AutoCdcAuxiliaryTable.validateNoTargetSequencingTypeDrift(
+        existingTargetSchema = upperCasedSchema,
+        targetTableIdentifier = targetIdent,
+        expectedScdType = ScdType.Type2,
+        expectedSequencingType = IntegerType,
+        resolver = caseInsensitiveResolution)
+    }
+    assert(ex.getCondition == "AUTOCDC_INVALID_STATE.SEQUENCING_TYPE_DRIFT")
+  }
 }
diff --git 
a/sql/pipelines/src/test/scala/org/apache/spark/sql/pipelines/graph/AutoCdcConfigDriftSuite.scala
 
b/sql/pipelines/src/test/scala/org/apache/spark/sql/pipelines/graph/AutoCdcConfigDriftSuite.scala
new file mode 100644
index 000000000000..dda1e5a7455a
--- /dev/null
+++ 
b/sql/pipelines/src/test/scala/org/apache/spark/sql/pipelines/graph/AutoCdcConfigDriftSuite.scala
@@ -0,0 +1,593 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements.  See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License.  You may obtain a copy of the License at
+ *
+ *    http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.spark.sql.pipelines.graph
+
+import org.apache.spark.sql.execution.streaming.runtime.MemoryStream
+import org.apache.spark.sql.pipelines.autocdc.{ColumnSelection, ScdType, 
UnqualifiedColumnName}
+import org.apache.spark.sql.pipelines.utils.{ExecutionTest, 
TestGraphRegistrationContext}
+import org.apache.spark.sql.test.SharedSparkSession
+
+/**
+ * End-to-end tests covering AutoCDC configuration-drift validation for the 
sequencing result type
+ * (SCD1 and SCD2) and the SCD2 track-history column set, validated at flow 
execution-init time
+ * against the auxiliary table's recorded configuration (mirroring
+ * [[AutoCdcScd1KeyDriftSuite]] for keys).
+ *
+ * Guiding principle: guard the invariants that keep already-persisted state 
coherent, not the
+ * expressions themselves. The sequencing expression and delete condition may 
change across runs;
+ * the sequencing result *type* and the SCD2 track-history column *set* may 
not.
+ */
+class AutoCdcConfigDriftSuite
+    extends ExecutionTest
+    with SharedSparkSession
+    with AutoCdcGraphExecutionTestMixin {
+
+  import testImplicits._
+
+  private def targetName: String =
+    fullyQualifiedIdentifier("target", Some(catalog), 
Some(namespace)).unquotedString
+
+  /** SCD2 target DDL: user columns + the SCD2 framework columns (sequencing 
type long). */
+  private def createScd2Target(userCols: String): Unit = {
+    spark.sql(
+      s"CREATE TABLE $catalog.$namespace.target ($userCols, $scd2MetadataDdl)"
+    )
+  }
+
+  // 
===========================================================================================
+  // Sequencing type drift
+  // 
===========================================================================================
+
+  test("an SCD1 flow whose sequencing type differs from the recorded type 
triggers " +
+    "SEQUENCING_TYPE_DRIFT") {
+    spark.sql(
+      s"CREATE TABLE $catalog.$namespace.target " +
+      s"(id INT NOT NULL, seq_long BIGINT, seq_int INT, $scd1MetadataDdl)"
+    )
+
+    // Pipeline #1 sequences by a BIGINT column; aux records sequencingType = 
long.
+    val stream1 = MemoryStream[(Int, Long, Int)]
+    stream1.addData((1, 1L, 1))
+    runPipeline(singleAutoCdcFlowPipeline(
+      flowName = "flow_v1",
+      target = "target",
+      sourceDf = stream1.toDF().toDF("id", "seq_long", "seq_int"),
+      keys = Seq("id"),
+      sequencing = $"seq_long"))
+
+    // Pipeline #2 sequences by an INT column - type drift (int vs long), even 
though the
+    // expression (a different column) is otherwise a legal change.
+    val stream2 = MemoryStream[(Int, Long, Int)]
+    stream2.addData((1, 2L, 2))
+    val ctx2 = singleAutoCdcFlowPipeline(
+      flowName = "flow_v2",
+      target = "target",
+      sourceDf = stream2.toDF().toDF("id", "seq_long", "seq_int"),
+      keys = Seq("id"),
+      sequencing = $"seq_int")
+
+    val ex = intercept[RuntimeException] { runPipeline(ctx2) }
+    checkErrorInPipelineFailure(
+      failure = ex,
+      condition = "AUTOCDC_INVALID_STATE.SEQUENCING_TYPE_DRIFT",
+      sqlState = Some("42000"),
+      parameters = Map(
+        "tableName" -> targetName,
+        "expectedSequencingType" -> "INT",
+        "recordedSequencingType" -> "BIGINT"
+      )
+    )
+  }
+
+  test("an SCD1 flow that changes the sequencing expression but keeps the same 
type does NOT " +
+    "trigger drift") {
+    spark.sql(
+      s"CREATE TABLE $catalog.$namespace.target " +
+      s"(id INT NOT NULL, seq BIGINT, $scd1MetadataDdl)"
+    )
+
+    val stream1 = MemoryStream[(Int, Long)]
+    stream1.addData((1, 10L))
+    runPipeline(singleAutoCdcFlowPipeline(
+      flowName = "flow_v1",
+      target = "target",
+      sourceDf = stream1.toDF().toDF("id", "seq"),
+      keys = Seq("id"),
+      sequencing = $"seq"))
+
+    // A different expression over the same column, still yielding BIGINT: 
legal, no drift.
+    val stream2 = MemoryStream[(Int, Long)]
+    stream2.addData((1, 20L))
+    runPipeline(singleAutoCdcFlowPipeline(
+      flowName = "flow_v2",
+      target = "target",
+      sourceDf = stream2.toDF().toDF("id", "seq"),
+      keys = Seq("id"),
+      sequencing = $"seq" + 1L))
+  }
+
+  test("an SCD2 flow whose sequencing type differs from the recorded type 
triggers " +
+    "SEQUENCING_TYPE_DRIFT") {
+    createScd2Target("id INT NOT NULL, seq_long BIGINT, seq_int INT")
+
+    val stream1 = MemoryStream[(Int, Long, Int)]
+    stream1.addData((1, 1L, 1))
+    runPipeline(singleAutoCdcFlowPipeline(
+      flowName = "flow_v1",
+      target = "target",
+      sourceDf = stream1.toDF().toDF("id", "seq_long", "seq_int"),
+      keys = Seq("id"),
+      sequencing = $"seq_long",
+      scdType = ScdType.Type2))
+
+    val stream2 = MemoryStream[(Int, Long, Int)]
+    stream2.addData((1, 2L, 2))
+    val ctx2 = singleAutoCdcFlowPipeline(
+      flowName = "flow_v2",
+      target = "target",
+      sourceDf = stream2.toDF().toDF("id", "seq_long", "seq_int"),
+      keys = Seq("id"),
+      sequencing = $"seq_int",
+      scdType = ScdType.Type2)
+
+    val ex = intercept[RuntimeException] { runPipeline(ctx2) }
+    checkErrorInPipelineFailure(
+      failure = ex,
+      condition = "AUTOCDC_INVALID_STATE.SEQUENCING_TYPE_DRIFT",
+      sqlState = Some("42000"),
+      parameters = Map(
+        "tableName" -> targetName,
+        "expectedSequencingType" -> "INT",
+        "recordedSequencingType" -> "BIGINT"
+      )
+    )
+  }
+
+  // 
===========================================================================================
+  // Track-history drift (SCD2 only)
+  // 
===========================================================================================
+
+  test("an SCD2 flow that changes its explicit TRACK HISTORY column set 
triggers " +
+    "TRACK_HISTORY_DRIFT") {
+    createScd2Target("id INT NOT NULL, name STRING, amount INT, seq BIGINT")
+
+    // Pipeline #1 tracks history on `name` only.
+    val stream1 = MemoryStream[(Int, String, Int, Long)]
+    stream1.addData((1, "a", 10, 1L))
+    runPipeline(singleAutoCdcFlowPipeline(
+      flowName = "flow_v1",
+      target = "target",
+      sourceDf = stream1.toDF().toDF("id", "name", "amount", "seq"),
+      keys = Seq("id"),
+      sequencing = $"seq",
+      scdType = ScdType.Type2,
+      trackHistorySelection = Some(
+        ColumnSelection.IncludeColumns(Seq(UnqualifiedColumnName("name"))))))
+
+    // Pipeline #2 tracks history on `amount` - a different set.
+    val stream2 = MemoryStream[(Int, String, Int, Long)]
+    stream2.addData((1, "a", 20, 2L))
+    val ctx2 = singleAutoCdcFlowPipeline(
+      flowName = "flow_v2",
+      target = "target",
+      sourceDf = stream2.toDF().toDF("id", "name", "amount", "seq"),
+      keys = Seq("id"),
+      sequencing = $"seq",
+      scdType = ScdType.Type2,
+      trackHistorySelection = Some(
+        ColumnSelection.IncludeColumns(Seq(UnqualifiedColumnName("amount")))))
+
+    val ex = intercept[RuntimeException] { runPipeline(ctx2) }
+    checkErrorInPipelineFailure(
+      failure = ex,
+      condition = "AUTOCDC_INVALID_STATE.TRACK_HISTORY_DRIFT",
+      sqlState = Some("42000"),
+      parameters = Map(
+        "tableName" -> targetName,
+        "expectedTrackHistoryColumns" -> "amount",
+        "recordedTrackHistoryColumns" -> "name"
+      )
+    )
+  }
+
+  test("an SCD2 flow that reorders the same TRACK HISTORY columns does NOT 
trigger drift") {
+    createScd2Target("id INT NOT NULL, name STRING, amount INT, seq BIGINT")
+
+    val stream1 = MemoryStream[(Int, String, Int, Long)]
+    stream1.addData((1, "a", 10, 1L))
+    runPipeline(singleAutoCdcFlowPipeline(
+      flowName = "flow_v1",
+      target = "target",
+      sourceDf = stream1.toDF().toDF("id", "name", "amount", "seq"),
+      keys = Seq("id"),
+      sequencing = $"seq",
+      scdType = ScdType.Type2,
+      trackHistorySelection = Some(ColumnSelection.IncludeColumns(
+        Seq(UnqualifiedColumnName("name"), UnqualifiedColumnName("amount"))))))
+
+    // Same set, reversed order: run semantics are order-insensitive, so no 
drift.
+    val stream2 = MemoryStream[(Int, String, Int, Long)]
+    stream2.addData((1, "a", 20, 2L))
+    runPipeline(singleAutoCdcFlowPipeline(
+      flowName = "flow_v2",
+      target = "target",
+      sourceDf = stream2.toDF().toDF("id", "name", "amount", "seq"),
+      keys = Seq("id"),
+      sequencing = $"seq",
+      scdType = ScdType.Type2,
+      trackHistorySelection = Some(ColumnSelection.IncludeColumns(
+        Seq(UnqualifiedColumnName("amount"), UnqualifiedColumnName("name"))))))
+  }
+
+  test("an SCD2 flow with no TRACK HISTORY (default = all eligible columns) 
followed by an " +
+    "explicit selection of that same set does NOT trigger drift") {
+    createScd2Target("id INT NOT NULL, name STRING, amount INT, seq BIGINT")
+
+    // Pipeline #1 omits trackHistorySelection: the recorded set is the 
default, every eligible
+    // (non-key, non-framework) column, i.e. name, amount, seq.
+    val stream1 = MemoryStream[(Int, String, Int, Long)]
+    stream1.addData((1, "a", 10, 1L))
+    runPipeline(singleAutoCdcFlowPipeline(
+      flowName = "flow_v1",
+      target = "target",
+      sourceDf = stream1.toDF().toDF("id", "name", "amount", "seq"),
+      keys = Seq("id"),
+      sequencing = $"seq",
+      scdType = ScdType.Type2))
+
+    // Pipeline #2 explicitly lists that same default set: the drift check 
compares resolved sets,
+    // not user syntax, so an explicit restatement of the default must NOT 
drift.
+    val stream2 = MemoryStream[(Int, String, Int, Long)]
+    stream2.addData((1, "a", 20, 2L))
+    runPipeline(singleAutoCdcFlowPipeline(
+      flowName = "flow_v2",
+      target = "target",
+      sourceDf = stream2.toDF().toDF("id", "name", "amount", "seq"),
+      keys = Seq("id"),
+      sequencing = $"seq",
+      scdType = ScdType.Type2,
+      trackHistorySelection = Some(ColumnSelection.IncludeColumns(Seq(
+        UnqualifiedColumnName("name"),
+        UnqualifiedColumnName("amount"),
+        UnqualifiedColumnName("seq"))))))
+  }
+
+  test("an SCD2 flow with no TRACK HISTORY (default = all eligible columns) 
followed by an " +
+    "explicit subset triggers TRACK_HISTORY_DRIFT") {
+    createScd2Target("id INT NOT NULL, name STRING, amount INT, seq BIGINT")
+
+    // Pipeline #1 records the default set (name, amount, seq).
+    val stream1 = MemoryStream[(Int, String, Int, Long)]
+    stream1.addData((1, "a", 10, 1L))
+    runPipeline(singleAutoCdcFlowPipeline(
+      flowName = "flow_v1",
+      target = "target",
+      sourceDf = stream1.toDF().toDF("id", "name", "amount", "seq"),
+      keys = Seq("id"),
+      sequencing = $"seq",
+      scdType = ScdType.Type2))
+
+    // Pipeline #2 narrows to an explicit subset (name only): a real change to 
which transitions
+    // open a new record, so it must drift against the recorded default set.
+    val stream2 = MemoryStream[(Int, String, Int, Long)]
+    stream2.addData((1, "a", 20, 2L))
+    val ctx2 = singleAutoCdcFlowPipeline(
+      flowName = "flow_v2",
+      target = "target",
+      sourceDf = stream2.toDF().toDF("id", "name", "amount", "seq"),
+      keys = Seq("id"),
+      sequencing = $"seq",
+      scdType = ScdType.Type2,
+      trackHistorySelection = Some(
+        ColumnSelection.IncludeColumns(Seq(UnqualifiedColumnName("name")))))
+
+    val ex = intercept[RuntimeException] { runPipeline(ctx2) }
+    checkErrorInPipelineFailure(
+      failure = ex,
+      condition = "AUTOCDC_INVALID_STATE.TRACK_HISTORY_DRIFT",
+      sqlState = Some("42000"),
+      parameters = Map(
+        "tableName" -> targetName,
+        "expectedTrackHistoryColumns" -> "name",
+        "recordedTrackHistoryColumns" -> "name, amount, seq"
+      )
+    )
+  }
+
+  test("an SCD2 flow's EXCEPT-based TRACK HISTORY followed by an equivalent 
explicit include " +
+    "set does NOT trigger drift") {
+    createScd2Target("id INT NOT NULL, name STRING, amount INT, seq BIGINT")
+
+    // Pipeline #1 uses TRACK HISTORY ON * EXCEPT (amount): the resolved set 
is the eligible
+    // columns minus `amount`, i.e. name, seq.
+    val stream1 = MemoryStream[(Int, String, Int, Long)]
+    stream1.addData((1, "a", 10, 1L))
+    runPipeline(singleAutoCdcFlowPipeline(
+      flowName = "flow_v1",
+      target = "target",
+      sourceDf = stream1.toDF().toDF("id", "name", "amount", "seq"),
+      keys = Seq("id"),
+      sequencing = $"seq",
+      scdType = ScdType.Type2,
+      trackHistorySelection = Some(
+        ColumnSelection.ExcludeColumns(Seq(UnqualifiedColumnName("amount"))))))
+
+    // Pipeline #2 states the same set as an explicit include list: EXCLUDE 
and INCLUDE that
+    // resolve to the same set must not drift, since only the resolved set is 
recorded.
+    val stream2 = MemoryStream[(Int, String, Int, Long)]
+    stream2.addData((1, "a", 20, 2L))
+    runPipeline(singleAutoCdcFlowPipeline(
+      flowName = "flow_v2",
+      target = "target",
+      sourceDf = stream2.toDF().toDF("id", "name", "amount", "seq"),
+      keys = Seq("id"),
+      sequencing = $"seq",
+      scdType = ScdType.Type2,
+      trackHistorySelection = Some(ColumnSelection.IncludeColumns(
+        Seq(UnqualifiedColumnName("name"), UnqualifiedColumnName("seq"))))))
+  }
+
+  test("SCD2 track-history drift validation is resolver-aware: a case-only 
difference does NOT " +
+    "trigger drift under the default (case-insensitive) resolver") {
+    createScd2Target("id INT NOT NULL, name STRING, amount INT, seq BIGINT")
+
+    // Pipeline #1 records track-history on `name`.
+    val stream1 = MemoryStream[(Int, String, Int, Long)]
+    stream1.addData((1, "a", 10, 1L))
+    runPipeline(singleAutoCdcFlowPipeline(
+      flowName = "flow_v1",
+      target = "target",
+      sourceDf = stream1.toDF().toDF("id", "name", "amount", "seq"),
+      keys = Seq("id"),
+      sequencing = $"seq",
+      scdType = ScdType.Type2,
+      trackHistorySelection = Some(
+        ColumnSelection.IncludeColumns(Seq(UnqualifiedColumnName("name"))))))
+
+    // Pipeline #2 selects `NAME` (different case). The source DF column is 
still lowercase `name`
+    // so it resolves against the schema; only the tracking-column casing 
differs. Under the
+    // default case-insensitive resolver the two sets are equal, so there must 
be no drift.
+    val stream2 = MemoryStream[(Int, String, Int, Long)]
+    stream2.addData((1, "a", 20, 2L))
+    runPipeline(singleAutoCdcFlowPipeline(
+      flowName = "flow_v2",
+      target = "target",
+      sourceDf = stream2.toDF().toDF("id", "name", "amount", "seq"),
+      keys = Seq("id"),
+      sequencing = $"seq",
+      scdType = ScdType.Type2,
+      trackHistorySelection = Some(
+        ColumnSelection.IncludeColumns(Seq(UnqualifiedColumnName("NAME"))))))
+  }
+
+  test("an existing SCD2 aux table missing the trackHistoryColumnNames 
property requires a " +
+    "full refresh (AUXILIARY_TABLE_PROPERTY_MISSING)") {
+    // Back-compat guard: an SCD2 auxiliary table created before this change 
carries no
+    // trackHistoryColumnNames property. Track-history drift validation 
surfaces this as a
+    // structured AUXILIARY_TABLE_PROPERTY_MISSING (remedy: full refresh) 
rather than silently
+    // skipping the check. Simulate the pre-existing table by unsetting the 
property after the
+    // first run, then run again.
+    createScd2Target("id INT NOT NULL, name STRING, amount INT, seq BIGINT")
+
+    val stream = MemoryStream[(Int, String, Int, Long)]
+    def buildCtx(): TestGraphRegistrationContext =
+      singleAutoCdcFlowPipeline(
+        flowName = "auto_cdc_flow",
+        target = "target",
+        sourceDf = stream.toDF().toDF("id", "name", "amount", "seq"),
+        keys = Seq("id"),
+        sequencing = $"seq",
+        scdType = ScdType.Type2)
+
+    stream.addData((1, "a", 10, 1L))
+    runPipeline(buildCtx())
+
+    // Drop the property to mimic an aux table materialized before this change.
+    spark.sql(
+      s"ALTER TABLE ${auxTableNameFor("target")} " +
+      s"UNSET TBLPROPERTIES 
('${AutoCdcAuxiliaryTable.trackHistoryColumnNamesProperty}')"
+    )
+
+    stream.addData((1, "a", 20, 2L))
+    val ex = intercept[RuntimeException] { runPipeline(buildCtx()) }
+    checkErrorInPipelineFailure(
+      failure = ex,
+      condition = "AUTOCDC_INVALID_STATE.AUXILIARY_TABLE_PROPERTY_MISSING",
+      sqlState = Some("42000"),
+      parameters = Map(
+        "tableName" -> targetName,
+        "propertyName" -> AutoCdcAuxiliaryTable.trackHistoryColumnNamesProperty
+      )
+    )
+  }
+
+  // 
===========================================================================================
+  // Sequencing type: SCD2 expression-change symmetry with the SCD1 case above
+  // 
===========================================================================================
+
+  test("an SCD2 flow that changes the sequencing expression but keeps the same 
type does NOT " +
+    "trigger drift") {
+    createScd2Target("id INT NOT NULL, seq BIGINT")
+
+    val stream1 = MemoryStream[(Int, Long)]
+    stream1.addData((1, 10L))
+    runPipeline(singleAutoCdcFlowPipeline(
+      flowName = "flow_v1",
+      target = "target",
+      sourceDf = stream1.toDF().toDF("id", "seq"),
+      keys = Seq("id"),
+      sequencing = $"seq",
+      scdType = ScdType.Type2))
+
+    // A different expression over the same column, still yielding BIGINT: 
legal, no drift.
+    val stream2 = MemoryStream[(Int, Long)]
+    stream2.addData((1, 20L))
+    runPipeline(singleAutoCdcFlowPipeline(
+      flowName = "flow_v2",
+      target = "target",
+      sourceDf = stream2.toDF().toDF("id", "seq"),
+      keys = Seq("id"),
+      sequencing = $"seq" + 1L,
+      scdType = ScdType.Type2))
+  }
+
+  // 
===========================================================================================
+  // Intended divergence from SCD1: additive source-schema evolution under 
default / EXCEPT
+  // tracking changes the effective tracked set, and so requires a full 
refresh.
+  // 
===========================================================================================
+
+  test("adding a source column under default (all-column) tracking triggers 
TRACK_HISTORY_DRIFT") {
+    // The effective tracked set is derived from the flow's selected source 
schema, so under default
+    // tracking every selected non-key column is tracked. Adding a source 
column therefore changes
+    // the tracked set, which reinterprets which transitions open a new SCD2 
record and cannot be
+    // applied to already-reconciled history. Unlike SCD1 (where a new 
nullable column is absorbed
+    // by schema evolution), SCD2 requires a full refresh. Pins that intended 
divergence.
+    createScd2Target("id INT NOT NULL, name STRING, seq BIGINT")
+
+    // Run #1: source (id, name, seq); recorded tracked set = {name, seq}.
+    val stream1 = MemoryStream[(Int, String, Long)]
+    stream1.addData((1, "a", 1L))
+    runPipeline(singleAutoCdcFlowPipeline(
+      flowName = "flow_v1",
+      target = "target",
+      sourceDf = stream1.toDF().toDF("id", "name", "seq"),
+      keys = Seq("id"),
+      sequencing = $"seq",
+      scdType = ScdType.Type2))
+
+    // Run #2: source gains a nullable `city`; default tracking now resolves 
to {name, city, seq}.
+    val stream2 = MemoryStream[(Int, String, String, Long)]
+    stream2.addData((1, "a", "nyc", 2L))
+    val ctx2 = singleAutoCdcFlowPipeline(
+      flowName = "flow_v2",
+      target = "target",
+      sourceDf = stream2.toDF().toDF("id", "name", "city", "seq"),
+      keys = Seq("id"),
+      sequencing = $"seq",
+      scdType = ScdType.Type2)
+
+    val ex = intercept[RuntimeException] { runPipeline(ctx2) }
+    checkErrorInPipelineFailure(
+      failure = ex,
+      condition = "AUTOCDC_INVALID_STATE.TRACK_HISTORY_DRIFT",
+      sqlState = Some("42000"),
+      parameters = Map(
+        "tableName" -> targetName,
+        "expectedTrackHistoryColumns" -> "name, city, seq",
+        "recordedTrackHistoryColumns" -> "name, seq"))
+
+    // The drift check runs before the target's schema is evolved, so the 
rejected run must leave
+    // the target untouched: `city` must NOT have been added. (Were it added, 
the "correct the
+    // flow" remedy would then wedge the pipeline on a column-count mismatch 
during reconciliation.)
+    assert(
+      
!spark.table(s"$catalog.$namespace.target").schema.fieldNames.contains("city"),
+      "rejected run must not have evolved the target schema to add `city`")
+  }
+
+  test("dropping a source column under default (all-column) tracking triggers 
" +
+    "TRACK_HISTORY_DRIFT") {
+    // The mirror of the additive case: removing a selected column shrinks the 
default tracked set,
+    // which is likewise a tracked-set change requiring a full refresh.
+    createScd2Target("id INT NOT NULL, name STRING, city STRING, seq BIGINT")
+
+    // Run #1: source (id, name, city, seq); recorded tracked set = {name, 
city, seq}.
+    val stream1 = MemoryStream[(Int, String, String, Long)]
+    stream1.addData((1, "a", "nyc", 1L))
+    runPipeline(singleAutoCdcFlowPipeline(
+      flowName = "flow_v1",
+      target = "target",
+      sourceDf = stream1.toDF().toDF("id", "name", "city", "seq"),
+      keys = Seq("id"),
+      sequencing = $"seq",
+      scdType = ScdType.Type2))
+
+    // Run #2: `city` dropped from the source; default tracking now resolves 
to {name, seq}.
+    val stream2 = MemoryStream[(Int, String, Long)]
+    stream2.addData((1, "a", 2L))
+    val ctx2 = singleAutoCdcFlowPipeline(
+      flowName = "flow_v2",
+      target = "target",
+      sourceDf = stream2.toDF().toDF("id", "name", "seq"),
+      keys = Seq("id"),
+      sequencing = $"seq",
+      scdType = ScdType.Type2)
+
+    val ex = intercept[RuntimeException] { runPipeline(ctx2) }
+    checkErrorInPipelineFailure(
+      failure = ex,
+      condition = "AUTOCDC_INVALID_STATE.TRACK_HISTORY_DRIFT",
+      sqlState = Some("42000"),
+      parameters = Map(
+        "tableName" -> targetName,
+        "expectedTrackHistoryColumns" -> "name, seq",
+        "recordedTrackHistoryColumns" -> "name, city, seq"))
+  }
+
+  test("dropping the target (but not the auxiliary table) between runs still 
detects drift") {
+    // A user drops and recreates the target to reset it, but does not know to 
also drop the
+    // internal auxiliary table. On the next run the target is absent (so it 
is re-created), but the
+    // stale auxiliary table survives with the old recorded configuration. 
Drift validation reads
+    // the auxiliary table, so it must still fire regardless of the target's 
existence -- otherwise
+    // the aux table's additive evolve would silently overwrite the recorded 
track-history property
+    // with the new run's value. Mirrors 
AutoCdcScd1AuxiliaryTableDurabilitySuite's
+    // "auxiliary table is dropped between runs" case, with the two tables 
swapped.
+    createScd2Target("id INT NOT NULL, name STRING, amount INT, seq BIGINT")
+
+    // Run #1: track history on `name`; records trackHistoryColumnNames = 
[name] on the aux table.
+    val stream1 = MemoryStream[(Int, String, Int, Long)]
+    stream1.addData((1, "a", 10, 1L))
+    runPipeline(singleAutoCdcFlowPipeline(
+      flowName = "flow_v1",
+      target = "target",
+      sourceDf = stream1.toDF().toDF("id", "name", "amount", "seq"),
+      keys = Seq("id"),
+      sequencing = $"seq",
+      scdType = ScdType.Type2,
+      trackHistorySelection =
+        
Some(ColumnSelection.IncludeColumns(Seq(UnqualifiedColumnName("name"))))))
+
+    // Drop ONLY the target; the auxiliary table survives with its recorded 
config.
+    spark.sql(s"DROP TABLE $catalog.$namespace.target")
+    assert(spark.catalog.tableExists(auxTableNameFor("target")),
+      "auxiliary table should survive dropping the target")
+
+    // Run #2: track history on `amount` instead -- a changed tracked set.
+    // This will recreate the target.
+    val stream2 = MemoryStream[(Int, String, Int, Long)]
+    stream2.addData((1, "a", 20, 2L))
+    val ctx2 = singleAutoCdcFlowPipeline(
+      flowName = "flow_v2",
+      target = "target",
+      sourceDf = stream2.toDF().toDF("id", "name", "amount", "seq"),
+      keys = Seq("id"),
+      sequencing = $"seq",
+      scdType = ScdType.Type2,
+      trackHistorySelection =
+        
Some(ColumnSelection.IncludeColumns(Seq(UnqualifiedColumnName("amount")))))
+
+    val ex = intercept[RuntimeException] { runPipeline(ctx2) }
+    checkErrorInPipelineFailure(
+      failure = ex,
+      condition = "AUTOCDC_INVALID_STATE.TRACK_HISTORY_DRIFT",
+      sqlState = Some("42000"),
+      parameters = Map(
+        "tableName" -> targetName,
+        "expectedTrackHistoryColumns" -> "amount",
+        "recordedTrackHistoryColumns" -> "name"))
+  }
+}
diff --git 
a/sql/pipelines/src/test/scala/org/apache/spark/sql/pipelines/graph/AutoCdcScd1AuxiliaryTableDurabilitySuite.scala
 
b/sql/pipelines/src/test/scala/org/apache/spark/sql/pipelines/graph/AutoCdcScd1AuxiliaryTableDurabilitySuite.scala
index 422b95b3c267..5d93de6f9efe 100644
--- 
a/sql/pipelines/src/test/scala/org/apache/spark/sql/pipelines/graph/AutoCdcScd1AuxiliaryTableDurabilitySuite.scala
+++ 
b/sql/pipelines/src/test/scala/org/apache/spark/sql/pipelines/graph/AutoCdcScd1AuxiliaryTableDurabilitySuite.scala
@@ -317,7 +317,7 @@ class AutoCdcScd1AuxiliaryTableDurabilitySuite
         s"auxiliary table $auxName is missing the " +
         s"${AutoCdcAuxiliaryTable.keyColumnNamesProperty} property; got: 
${rows.toSeq}"
       ))
-    AutoCdcAuxiliaryTable.parseKeyColumnNames(prop.getString(1))
+    AutoCdcAuxiliaryTable.parseColumnNames(prop.getString(1))
       .getOrElse(fail(
         s"auxiliary table $auxName has a malformed " +
         s"${AutoCdcAuxiliaryTable.keyColumnNamesProperty} property: 
'${prop.getString(1)}'"
diff --git 
a/sql/pipelines/src/test/scala/org/apache/spark/sql/pipelines/graph/AutoCdcScd1AuxiliaryTableSpecSuite.scala
 
b/sql/pipelines/src/test/scala/org/apache/spark/sql/pipelines/graph/AutoCdcScd1AuxiliaryTableSpecSuite.scala
index 530cae7cfa58..226480ea919a 100644
--- 
a/sql/pipelines/src/test/scala/org/apache/spark/sql/pipelines/graph/AutoCdcScd1AuxiliaryTableSpecSuite.scala
+++ 
b/sql/pipelines/src/test/scala/org/apache/spark/sql/pipelines/graph/AutoCdcScd1AuxiliaryTableSpecSuite.scala
@@ -106,7 +106,7 @@ class AutoCdcScd1AuxiliaryTableSpecSuite extends 
PipelineTest with SharedSparkSe
     assert(spec.properties(AutoCdcAuxiliaryTable.scdTypePropertyKey) == 
ScdType.Type1.label)
     assert(spec.expectedKeyFields.map(_.name) == Seq("id"))
     assert(
-      AutoCdcAuxiliaryTable.parseKeyColumnNames(
+      AutoCdcAuxiliaryTable.parseColumnNames(
         
spec.properties(AutoCdcAuxiliaryTable.keyColumnNamesProperty)).contains(Seq("id")))
     assert(spec.identifier == 
AutoCdcAuxiliaryTable.identifier(targetIdentifier))
     assert(spec.targetTableIdentifier == targetIdentifier)
@@ -116,7 +116,7 @@ class AutoCdcScd1AuxiliaryTableSpecSuite extends 
PipelineTest with SharedSparkSe
     val spec = scd1AuxSpec(keys = Seq("id", "name"))
     assert(spec.expectedKeyFields.map(_.name) == Seq("id", "name"))
     assert(
-      AutoCdcAuxiliaryTable.parseKeyColumnNames(
+      AutoCdcAuxiliaryTable.parseColumnNames(
         
spec.properties(AutoCdcAuxiliaryTable.keyColumnNamesProperty)).contains(Seq("id",
 "name")))
     // The aux schema is the two keys followed by the metadata column, and 
nothing else.
     assert(
diff --git 
a/sql/pipelines/src/test/scala/org/apache/spark/sql/pipelines/graph/AutoCdcScd2AuxiliaryTableSpecSuite.scala
 
b/sql/pipelines/src/test/scala/org/apache/spark/sql/pipelines/graph/AutoCdcScd2AuxiliaryTableSpecSuite.scala
index d86f3ba89bdd..a56fed3e5805 100644
--- 
a/sql/pipelines/src/test/scala/org/apache/spark/sql/pipelines/graph/AutoCdcScd2AuxiliaryTableSpecSuite.scala
+++ 
b/sql/pipelines/src/test/scala/org/apache/spark/sql/pipelines/graph/AutoCdcScd2AuxiliaryTableSpecSuite.scala
@@ -139,7 +139,7 @@ class AutoCdcScd2AuxiliaryTableSpecSuite extends 
PipelineTest with SharedSparkSe
     assert(spec.properties(AutoCdcAuxiliaryTable.scdTypePropertyKey) == 
ScdType.Type2.label)
     assert(spec.expectedKeyFields.map(_.name) == Seq("id"))
     assert(
-      AutoCdcAuxiliaryTable.parseKeyColumnNames(
+      AutoCdcAuxiliaryTable.parseColumnNames(
         
spec.properties(AutoCdcAuxiliaryTable.keyColumnNamesProperty)).contains(Seq("id")))
     assert(spec.identifier == 
AutoCdcAuxiliaryTable.identifier(targetIdentifier))
     assert(spec.targetTableIdentifier == targetIdentifier)
@@ -149,7 +149,7 @@ class AutoCdcScd2AuxiliaryTableSpecSuite extends 
PipelineTest with SharedSparkSe
     val spec = scd2AuxSpec(keys = Seq("id", "name"))
     assert(spec.expectedKeyFields.map(_.name) == Seq("id", "name"))
     assert(
-      AutoCdcAuxiliaryTable.parseKeyColumnNames(
+      AutoCdcAuxiliaryTable.parseColumnNames(
         
spec.properties(AutoCdcAuxiliaryTable.keyColumnNamesProperty)).contains(Seq("id",
 "name")))
     // Both keys survive into the aux schema.
     assert(spec.schema.fieldNames.contains("id"))


---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]

Reply via email to