SCHEMA PROPAGATION CASCADE DEPTH (SPCD)

GMI-IT-042IT OperationsIntegration-Required

Definition

Schema Propagation Cascade Depth (SPCD) measures the gap between the depth of actual downstream impact and the depth the AI analyzer predicted for a schema change.

The Measurement Gap

A retail technology company. The data platform team uses an AI-driven schema impact analyzer integrated with their dbt project. When a platform engineer changes a column in a source table, the analyzer scans the immediate dbt dependencies, generates rename patches, runs the local test suite, and produces a "safe to deploy" recommendation.

On a Wednesday at 11:00 AM, a platform engineer renames customer_segment to customer_tier in the source schema. The analyzer finds three dbt models that consume the column, generates the rename patches, runs the test suite, marks the change safe. The engineer approves. Deployment goes out clean.

At 2:00 PM, the marketing analytics team is in a fire. The personalization model that drives their email campaigns is producing degraded recommendations. At 4:00 PM, the demand forecasting team reports their weekly forecast pipeline is broken. By the end of the day, the incident bridge has identified the actual cascade: source table feeds three dbt models, those models feed two reverse-ETL syncs that push into Salesforce and a CDP, the CDP feeds the personalization service, the personalization service feeds the recommendation API, the recommendation API feeds three marketing campaigns. The AI analyzer saw one hop downstream. The actual impact reached four hops downstream. The patch fixed the dbt models. Nothing else was patched, because the analyzer did not see anything else.

The legacy metrics on the team's dashboard show the change as a success.

Schema validation pass rate: 100%. The new schema is structurally valid.

Test suite pass rate: 100%. The dbt test suite ran clean against the patched models.

AI impact analyzer "safe to deploy" rate: still 96% over the trailing quarter. Analyzer keeps recommending most changes as safe, and most of them are.

Pipeline success rate: every downstream pipeline ran successfully on the changed data; they just ran successfully on data that no longer meant what it used to mean.

Data lineage depth: the lineage platform shows the full graph depth, but lineage depth is not the same as cascade depth. The graph might be eight hops deep while the analyzer only traverses one.

Mean time to detect schema-related incidents: low for the cases that surface within the day, but cascade-depth incidents often surface days or weeks later when the downstream consumer notices their numbers are wrong.

These metrics were designed when schema changes went through manual review and the reviewer's job included thinking about downstream impact. The reviewer mentally walked the graph. They knew which consumers used the column. They knew which transformations depended on the column's meaning. The cascade-depth check was implicit in the review. With AI impact analysis, the cascade-depth check became explicit in code, and the code only walks as far as it was configured to walk.

There is no metric that tells you whether an AI-assessed schema change is safe across the full multi-hop propagation path, which means schema changes that pass immediate-neighbor impact analysis break systems three or four hops downstream.

Formula

Schema Propagation Cascade Depth (SPCD) measures the gap between the depth of actual downstream impact and the depth the AI analyzer predicted for a schema change. In plain language, every time a schema change causes a problem downstream, SPCD asks: how many transformation hops deeper than the analyzer predicted did the problem reach? Zero means the prediction matched reality. A positive number means the analyzer underpredicted. Higher numbers mean the analyzer is systematically missing deeper cascades.

The formula:
SPCD = Max(Actual_affected_hops) − Predicted_affected_hops

Where:

  • Actual_affected_hops = the farthest downstream transformation hop where the schema change caused failure, degraded output, or required adaptation
  • Predicted_affected_hops = the number of hops the AI impact analyzer flagged as in-scope at the time of the changeUnits are an integer count of hops. Zero means the prediction matched. Positive means underprediction. Negative is technically possible (overprediction) but you almost never see it: the failure mode in practice is consistently directional. A worked example. The retail tech company runs SPCD over a quarter. They had 18 schema change events during the quarter. After post-incident analysis on each one:

  • 12 changes: SPCD = 0 (predicted impact matched actual; the change either had no downstream effect or the analyzer caught the full scope)

  • 4 changes: SPCD = 1 (the change reached one hop deeper than predicted)
  • 2 changes: SPCD = 3 (the change reached four hops deep when only one was predicted)

For the two changes with SPCD = 3, those are the cascades the analyzer fundamentally missed. The team's mean SPCD is (12×0 + 4×1 + 2×3) / 18 = 10/18 = 0.56. Per the catalog guidance (any SPCD > 3 triggers extension of the analyzer's graph traversal depth) both of the deep cascades trigger immediate action. Three edge cases need explicit handling. First, only count events where actual impact exceeded prediction; cases where actual impact was at or below prediction contribute SPCD = 0 to the average but are not the focus of the metric. Second, the transformation graph must be mapped through lineage: without a complete graph you cannot determine actual cascade depth. Third, the minimum sample is 15 schema change events per quarter with measurable downstream impact; if your team makes fewer schema changes than that, lengthen the measurement window. Cross-references. Upstream of SPCD is

Semantic Data Quality Gap (SDQG): semantic correctness and schema correctness are related, and a pipeline with high SDQG often has weak schema discipline that contributes to deep cascades. Downstream is

Downstream Impact Propagation Index (DIPI): cascades that go undetected at the source surface as consumer incidents that DIPI counts. SPCD also feeds Change Risk Scoring Accuracy (CRSA, GMI-IT-012), because schema changes are a category of change whose risk is systematically underestimated when the impact analyzer's graph traversal depth is shallow. Here is the scenario where ignoring a cross-reference will mislead you. A team reports mean SPCD = 0.4 (low: healthy) and concludes their analyzer is well-calibrated. Then they look at DIPI for schema-related consumer incidents specifically and find it at 55%. The reconciliation is uncomfortable: most schema changes have shallow cascades, but the small fraction with deep cascades produces a disproportionate share of consumer impact. The mean masks the tail. When reading SPCD, always look at the distribution, not just the mean. A single SPCD = 5 event matters more than 20 SPCD = 0 events.

Proposed formula, pending validation pilots.

At a Glance

Category
AI-Era IT Operations
← RUNBOOK VALIDITY SCORE (RVS)SEGMENTATION COLLATERAL DAMAGE RATE (SCDR) →

Assess Your Organization

The AI-GPS Assessment evaluates your AI governance maturity and recommends KPIs like this one for your implementation roadmap.

Take the Free AI-GPS Assessment