How I Reconstructed 115M Lost Audit Records Across 3 Countries Using SQL & SSIS

What do you do when a multinational financial services platform overwrites historical contract states in place—leaving leadership blind to churn, vintage, and arrears trends?

The Problem With "Current-State-Only" Databases

Many legacy transactional systems are designed purely to answer one question: "What does this contract look like right now?" When a policy status changes or an installment shifts, the row gets updated in place. The old state disappears.

In a regional engagement spanning Botswana, South Africa, and Zambia, that exact limitation prevented actuaries and executives from running historical retention and arrears migration analyses. Worse, each branch compiled Excel exports differently, producing conflicting numbers in executive meetings.

Finding the Breadcrumbs in Flattened Audit Trails

Before accepting that the historical states were gone for good, I dug into the legacy system's flattened audit logs. The raw logs weren't structured for analytical querying—they captured field-level change events rather than clean point-in-time snapshots. Trying to query 115M+ audit rows dynamically inside an analytical cube would have timed out or crushed the source database.

Why We Chose Physical Snapshot Tables

Instead of computing history on the fly, we designed a dedicated relational snapshot layer orchestrated by 30+ SSIS packages:

• Incremental Extraction: Pulled operational deltas and audit events via ODBC without overloading daytime transactional workloads.
• State Reconstruction in T-SQL: Applied ordered window functions and effective-date logic to replay contract changes chronologically and materialize clean monthly/daily snapshot tables.
• Semantic Layer & Country RLS: Fed those snapshots into compartmentalized SSAS Tabular Models with DAX time-intelligence, dynamic Pula FX conversions, and strict Row-Level Security (RLS) so each country only saw its own governed data.

Lessons That Carry Over Into AI Engineering

Working on billion-row, multi-country systems taught me discipline that applies directly to modern AI workflows: never trust unversioned state, always separate raw ingestion from business logic, and automate nightly validation so you catch anomalies before stakeholders ever open a dashboard.

WITH OrderedEvents AS (
    SELECT
        ContractKey,
        CountryCode,
        StatusCode,
        EffectiveAt,
        ROW_NUMBER() OVER (
            PARTITION BY ContractKey, EOMONTH(EffectiveAt)
            ORDER BY EffectiveAt DESC, AuditLogId DESC
        ) AS rn
    FROM stg.ContractAuditTrail
)
INSERT INTO dw.FactContractMonthlySnapshot (ContractKey, CountryCode, StatusCode, MonthEnd)
SELECT ContractKey, CountryCode, StatusCode, EOMONTH(EffectiveAt)
FROM OrderedEvents
WHERE rn = 1;

You can read the full architectural breakdown in the Enterprise Case Study.