Migrated from SAS? Prove the numbers survived.

The converter finished. The validation didn't.

A practitioner SAS→Databricks migration demo documented the failure mode precisely: the SAS CASE mapped line-of-credit (LOC) exposure to risk weight 1.00; a plausible-looking conversion mapped LOC → 0.75, matching the other revolving-credit products. The result silently overstated capital relief. The reconciliation control caught it: rwa_risk_weight_parity | FAIL | LOC=0.75 (source expects 1.00). “Looks reasonable” review would have shipped it.

So validation is a ladder, not a check — six layers, cheapest first. (1) Code inventory coverage: every .sas program, macro, format catalog, and scheduled job mapped to a migrated artifact; orphaned code is the classic scope surprise. (2) Row-count reconciliation per table and partition. (3) Schema/type contract: character→STRING, 8-byte numeric→DOUBLE or DECIMAL(p,s), SAS date→DATE, datetime→TIMESTAMP, .→NULL. (4) Row-content comparison with exceptAll. (5) Aggregate parity within a declared epsilon. (6) Gate enforcement: CHECK constraints provide range sanity, while a blocking business-rule parity gate catches plausible-but-wrong values such as LOC 0.75 instead of 1.00.

Databricks' own migration playbook recommends the same shape: when Databricks runs migrations itself, the test pattern is schema, counts, and row contents — with exceptAll as the Spark-native comparison that needs no join key. And the migration toolkit (Lakebridge, the evolved BladeBridge offering) ships a Reconcile module that computes row/column/schema comparisons between source and target.

Setup

-- Ground truth: the SAS extract, as landed
CREATE OR REPLACE TABLE sas_risk_extract AS
SELECT CAST(loan_id AS INT) AS loan_id,
       CAST(product AS STRING) AS product,
       CAST(exposure AS DECIMAL(12,2)) AS exposure,
       CAST(risk_weight AS DECIMAL(4,2)) AS risk_weight,
       CAST(booking_date AS DATE) AS booking_date
FROM VALUES
  (101, 'TERM', 250000.00, 1.00, DATE '2026-09-30'),
  (102, 'LOC',  180000.00, 1.00, DATE '2026-09-30'),
  (103, 'TERM', NULL,       0.50, DATE '2026-09-30')
  AS t(loan_id, product, exposure, risk_weight, booking_date);

-- The converter's output (note the LOC risk weight)
CREATE OR REPLACE TABLE migrated_risk AS
SELECT loan_id, product, exposure,
       CAST(CASE WHEN product = 'LOC' THEN 0.75 ELSE risk_weight END
            AS DECIMAL(4,2)) AS risk_weight,
       booking_date
FROM sas_risk_extract;

Layer 1 — code inventory coverage

Map every SAS artifact to its migrated counterpart before validating data. This runnable inventory covers a program, macro, format catalog, and scheduled job; the zero-row query is the blocking completeness check.

CREATE OR REPLACE TEMP VIEW migration_inventory AS
SELECT * FROM VALUES
  ('program',        'credit_risk.sas',  'notebooks/credit_risk',  'migrated'),
  ('macro',          'risk_weight',      'functions/risk_weight',  'migrated'),
  ('format catalog', 'product_formats',  'ref/product_formats',    'migrated'),
  ('scheduled job',  'daily_risk_batch', 'jobs/daily_risk_batch',  'migrated')
  AS t(artifact_type, sas_artifact, migrated_counterpart, status);

SELECT * FROM migration_inventory WHERE status <> 'migrated';
-- Expected: 0 rows

Layer 2 — row counts (the smoke alarm)

SELECT 'sas' AS src, COUNT(*) AS n FROM sas_risk_extract
UNION ALL
SELECT 'migrated', COUNT(*) FROM migrated_risk;
-- 3 / 3 — passes

Layer 3 — schema and type contract

Inspect both tables, then make names, order, and declared types a hard contract. The assertion must pass before any positional content comparison runs.

# Inspect both tables, then assert names, order, and types
spark.sql("DESCRIBE TABLE sas_risk_extract").show(truncate=False)
spark.sql("DESCRIBE TABLE migrated_risk").show(truncate=False)

column_spec = [
    ("loan_id", "int"),
    ("product", "string"),
    ("exposure", "decimal(12,2)"),
    ("risk_weight", "decimal(4,2)"),
    ("booking_date", "date"),
]

for table_name in ["sas_risk_extract", "migrated_risk"]:
    actual = [(f.name, f.dataType.simpleString())
              for f in spark.table(table_name).schema.fields]
    assert actual == column_spec, f"{table_name}: {actual} != {column_spec}"
# Expected: both assertions pass

Layer 4 — exceptAll names the divergent rows

df.exceptAll is the multiset difference: rows in the left frame that are not in the right, duplicates kept. Spark resolves exceptAll columns by position, not by name — a reordered column list silently compares the wrong columns. Canonicalize order and types first, assert schema equality, then run both directions. Zero rows both ways plus equal counts means multiset equality, with no join key required.

from pyspark.sql import functions as F

column_spec = [
    ("loan_id", "INT"),
    ("product", "STRING"),
    ("exposure", "DECIMAL(12,2)"),
    ("risk_weight", "DECIMAL(4,2)"),
    ("booking_date", "DATE"),
]
expected_names = [name for name, _ in column_spec]

sas_raw = spark.table("sas_risk_extract")
mig_raw = spark.table("migrated_risk")
assert sas_raw.columns == expected_names
assert mig_raw.columns == expected_names
assert sas_raw.schema == mig_raw.schema

canonical = [F.col(name).cast(dtype).alias(name)
             for name, dtype in column_spec]
sas = sas_raw.select(*canonical)
mig = mig_raw.select(*canonical)
assert sas.schema == mig.schema

sas_only = sas.exceptAll(mig)  # ground truth missing from migrated
mig_only = mig.exceptAll(sas)  # migrated missing from ground truth

print(sas_only.count(), mig_only.count())  # 1, 1
sas_only.show()  # the LOC row at risk_weight 1.00
mig_only.show()  # the LOC row at risk_weight 0.75

Layer 5 — aggregate parity within epsilon

Exact for keys, IDs, dates, and categorical mappings. For money and statistical measures, declare a tolerance: 8-byte doubles can differ in last bits across engines, so fail above 0.01 instead of demanding bit-equality.

-- weighted exposure per product; tolerance 0.01
SELECT product, ROUND(SUM(exposure * risk_weight), 2) AS weighted_exposure
FROM sas_risk_extract GROUP BY product;
-- TERM 250000.00 / LOC 180000.00

SELECT product, ROUND(SUM(exposure * risk_weight), 2) AS weighted_exposure
FROM migrated_risk GROUP BY product;
-- TERM 250000.00 / LOC 135000.00  → 45,000.00 off. FAIL.

Semantic spot checks

The SAS epoch

SAS dates are days since 1960-01-01; datetimes are seconds since midnight 1960-01-01. Spot-check converted DATE/TIMESTAMP columns against known SAS numerics — the off-by-epoch bug is a classic.

-- SAS date 21915 must render as 2020-01-01 (SAS epoch: 1960-01-01)
SELECT DATE '1960-01-01' + 21915 AS sas_21915;  -- 2020-01-01

-- the classic off-by-epoch mistake (Unix 1970-01-01):
SELECT DATE '1970-01-01' + 21915 AS wrong_epoch;  -- 2030-01-01

The missing-value filter trap

SAS . converts to NULL on ingest, but the semantics don't carry over. In SAS, a missing numeric is lower than any other numeric value, so WHERE exposure < 0 selected the missing row. In Spark, NULL fails the predicate's three-valued logic.

SELECT loan_id FROM sas_risk_extract WHERE exposure < 0;
-- 0 rows: the NULL row fails three-valued logic

Layer 6 — enforce sanity and business-rule parity gates

The range CHECK is a write-time sanity guard, but LOC 0.75 still falls between 0 and 2. The business-rule parity gate is what catches the planted 0.75-vs-1.00 divergence and makes “fails loudly” true.

-- Sanity guard: rejects out-of-range values, but LOC 0.75 passes
ALTER TABLE migrated_risk
  ADD CONSTRAINT risk_weight_sane CHECK (risk_weight BETWEEN 0 AND 2);

-- Runnable stand-in for the governed product→risk-weight reference
CREATE OR REPLACE TEMP VIEW expected_weights AS
SELECT * FROM VALUES
  ('TERM', CAST(1.00 AS DECIMAL(4,2))),
  ('LOC',  CAST(1.00 AS DECIMAL(4,2)))
  AS t(product, expected_risk_weight);

-- Business-rule parity gate: expected zero rows
SELECT m.loan_id, m.product, m.risk_weight, e.expected_risk_weight
FROM migrated_risk AS m
JOIN expected_weights AS e USING (product)
WHERE m.risk_weight <> e.expected_risk_weight;
-- Returns loan 102: LOC 0.75 vs 1.00 → FAIL

-- Pipeline-level sanity guard (Lakeflow):
-- CONSTRAINT risk_weight_sane EXPECT (risk_weight BETWEEN 0 AND 2)
--   ON VIOLATION FAIL UPDATE

Expectations are per-row true/false SQL expressions with warn (default), drop, or fail policies; warn/drop expectations expose tracking metrics in the pipeline event log. FAIL UPDATE stops and rolls back the update — tracking metrics for the failed update are not recorded, so use the expectation-violation error details for diagnosis.

1. 1 — Inventory: map every .sas program, macro library, format catalog, and scheduled job to a migrated counterpart; fail on any status other than migrated.
2. 2 — Row counts: reconcile per table and partition, SAS vs Delta — the smoke alarm. Run first, trust last.
3. 3 — Schema/type contract: assert column names, order, and declared types before comparing content.
4. 4 — Content: canonicalize order and types, then run sas.exceptAll(migrated) and the reverse as a blocking control.
5. 5 — Aggregates within epsilon: exact for keys, IDs, dates, and mappings; a declared tolerance (e.g. 0.01 for financials) for money and statistical measures.
6. 6 — Gates: keep CHECK constraints as sanity guards, and fail CI or the pipeline on non-zero business-rule or bidirectional parity results.
7. Semantic spot checks: verify SAS date numerics against the 1960-01-01 epoch; re-validate every inequality filter after .→NULL conversion.

Use this when

Validating any SAS→Databricks conversion — Lakebridge/Converter-assisted, LLM-assisted, or hand-rewritten — where SAS-side ground-truth extracts exist to compare against.

Avoid this when

No SAS ground truth exists: that's acceptance testing, a different discipline — write business-acceptance tests instead of parity checks. Tables too large for full exceptAll: use stratified samples by business key and document the sampling. The conversion intentionally changed grain: define the new contract first — parity against the old grain is meaningless.