Case Study 2: Temporal Data Management — Regulatory Compliance Audit Trail

Case Study 2: Temporal Data Management — Regulatory Compliance Audit Trail

Background

Meridian National Bank is preparing for its triennial OCC (Office of the Comptroller of the Currency) examination. The examiners have submitted a pre-examination request list that includes several items the bank's current systems cannot easily answer:

Customer Profile Reconstruction: "For loan customer #10042, provide the credit rating, income, and risk category that were on file at the time loan #LN-2024-0389 was approved on March 12, 2024."
Rate Schedule Audit: "Provide the interest rate schedule for 30-year fixed mortgages that was effective on each date a mortgage was originated during 2024. If any rates were retroactively corrected after origination, show both the rate the bank believed at the time and the corrected rate."
Change History: "For all customers whose risk category changed during 2024, provide a complete timeline of changes including the before and after values and the exact timestamp of each change."
Backdated Correction Accountability: "If any customer data was modified with a retroactive effective date during 2024, identify those modifications, who made them, and when the modification was entered into the system."

Without temporal tables, answering these questions would require the bank to have proactively built and maintained trigger-based audit tables — and many systems had not done so consistently. The data engineering team had begun migrating critical tables to DB2 temporal tables six months earlier. This case study describes the implementation and how it addressed each examination requirement.

The Migration Strategy

The team identified three critical tables for temporal migration, each requiring a different temporal model:

Table	Temporal Model	Rationale
CUSTOMER_PROFILES	System-time	Auditors need to know what the database contained at any historical point — changes are tracked automatically
LOAN_RATES	Business-time	Rates have defined effective periods managed by the business — a rate set today may take effect next month
LOAN_TERMS	Bitemporal	Loan terms have business-effective periods AND auditors may need to know what the system showed at a past point in time, including corrections

Implementation: System-Time for Customer Profiles

DDL

CREATE TABLE CUSTOMER_PROFILES (
    customer_id      INTEGER NOT NULL,
    customer_name    VARCHAR(100) NOT NULL,
    credit_rating    CHAR(2) NOT NULL,
    annual_income    DECIMAL(15,2),
    risk_category    VARCHAR(20) NOT NULL,
    relationship_mgr INTEGER,
    sys_start        TIMESTAMP(12) NOT NULL
                     GENERATED ALWAYS AS ROW BEGIN,
    sys_end          TIMESTAMP(12) NOT NULL
                     GENERATED ALWAYS AS ROW END,
    txn_start_id     TIMESTAMP(12)
                     GENERATED ALWAYS AS TRANSACTION START ID,
    PERIOD SYSTEM_TIME (sys_start, sys_end),
    PRIMARY KEY (customer_id)
);

CREATE TABLE CUSTOMER_PROFILES_HIST (
    customer_id      INTEGER NOT NULL,
    customer_name    VARCHAR(100) NOT NULL,
    credit_rating    CHAR(2) NOT NULL,
    annual_income    DECIMAL(15,2),
    risk_category    VARCHAR(20) NOT NULL,
    relationship_mgr INTEGER,
    sys_start        TIMESTAMP(12) NOT NULL,
    sys_end          TIMESTAMP(12) NOT NULL,
    txn_start_id     TIMESTAMP(12)
);

ALTER TABLE CUSTOMER_PROFILES
    ADD VERSIONING USE HISTORY TABLE CUSTOMER_PROFILES_HIST;

Addressing Requirement 1: Customer Profile Reconstruction

The examiner's question — "What did customer 10042's profile look like on March 12, 2024?" — becomes a single query:

SELECT customer_id,
       customer_name,
       credit_rating,
       annual_income,
       risk_category,
       relationship_mgr,
       sys_start AS version_valid_from,
       sys_end   AS version_valid_until
FROM   CUSTOMER_PROFILES
    FOR SYSTEM_TIME AS OF '2024-03-12-00.00.00.000000000000'
WHERE  customer_id = 10042;

The query transparently searches both the current table and the history table. If the customer's profile has been updated since March 2024, this query returns the historical version — the one that was active when the loan was approved. No trigger-based audit logic was needed.

Addressing Requirement 3: Change Timeline

SELECT customer_id,
       customer_name,
       credit_rating,
       annual_income,
       risk_category,
       sys_start AS changed_at,
       sys_end AS superseded_at,
       LAG(credit_rating) OVER (
           PARTITION BY customer_id
           ORDER BY sys_start
       ) AS previous_credit_rating,
       LAG(risk_category) OVER (
           PARTITION BY customer_id
           ORDER BY sys_start
       ) AS previous_risk_category
FROM   CUSTOMER_PROFILES
    FOR SYSTEM_TIME FROM '2024-01-01' TO '2025-01-01'
WHERE  customer_id IN (
    -- Customers whose risk category changed during 2024
    SELECT customer_id
    FROM   CUSTOMER_PROFILES
        FOR SYSTEM_TIME FROM '2024-01-01' TO '2025-01-01'
    GROUP BY customer_id
    HAVING COUNT(DISTINCT risk_category) > 1
)
ORDER BY customer_id, sys_start;

This combines temporal queries with window functions — LAG retrieves the previous version's values so the examiner can see the before-and-after on every row.

Implementation: Business-Time for Loan Rates

DDL

CREATE TABLE LOAN_RATES (
    loan_type       VARCHAR(30) NOT NULL,
    rate_pct        DECIMAL(5,3) NOT NULL,
    min_credit_score INTEGER,
    max_ltv_pct     DECIMAL(5,2),
    bus_start       DATE NOT NULL,
    bus_end         DATE NOT NULL,
    PERIOD BUSINESS_TIME (bus_start, bus_end),
    PRIMARY KEY (loan_type, BUSINESS_TIME WITHOUT OVERLAPS)
);

The WITHOUT OVERLAPS constraint is critical — it guarantees that for any loan type, exactly one rate is effective at any point in time. The bank cannot accidentally create conflicting rate entries.

Addressing Requirement 2: Rate Schedule Audit (Partial)

For each mortgage originated in 2024, show the rate that was effective at origination:

SELECT l.loan_id,
       l.loan_type,
       l.customer_id,
       l.origination_date,
       l.loan_amount,
       lr.rate_pct AS effective_rate_at_origination,
       lr.bus_start AS rate_effective_from,
       lr.bus_end AS rate_effective_until
FROM   LOANS l
JOIN   LOAN_RATES lr
    FOR BUSINESS_TIME AS OF l.origination_date
    ON l.loan_type = lr.loan_type
WHERE  l.loan_type = '30YR_FIXED'
  AND  YEAR(l.origination_date) = 2024
ORDER BY l.origination_date;

The FOR BUSINESS_TIME AS OF l.origination_date in the JOIN clause automatically selects the rate row whose business-time period contains each loan's origination date. This is a temporal join — one of the most powerful features of DB2's business-time implementation.

However, this query only shows the current rate schedule. If rates were retroactively corrected, we need the bitemporal model to show what the bank believed at the time versus what it currently believes.

Implementation: Bitemporal for Loan Terms

DDL

CREATE TABLE LOAN_TERMS (
    loan_id          INTEGER NOT NULL,
    interest_rate    DECIMAL(5,3) NOT NULL,
    monthly_payment  DECIMAL(12,2) NOT NULL,
    escrow_amount    DECIMAL(10,2),
    bus_start        DATE NOT NULL,
    bus_end          DATE NOT NULL,
    sys_start        TIMESTAMP(12) NOT NULL
                     GENERATED ALWAYS AS ROW BEGIN,
    sys_end          TIMESTAMP(12) NOT NULL
                     GENERATED ALWAYS AS ROW END,
    txn_start_id     TIMESTAMP(12)
                     GENERATED ALWAYS AS TRANSACTION START ID,
    PERIOD BUSINESS_TIME (bus_start, bus_end),
    PERIOD SYSTEM_TIME (sys_start, sys_end),
    PRIMARY KEY (loan_id, BUSINESS_TIME WITHOUT OVERLAPS)
);

CREATE TABLE LOAN_TERMS_HIST (
    loan_id          INTEGER NOT NULL,
    interest_rate    DECIMAL(5,3) NOT NULL,
    monthly_payment  DECIMAL(12,2) NOT NULL,
    escrow_amount    DECIMAL(10,2),
    bus_start        DATE NOT NULL,
    bus_end          DATE NOT NULL,
    sys_start        TIMESTAMP(12) NOT NULL,
    sys_end          TIMESTAMP(12) NOT NULL,
    txn_start_id     TIMESTAMP(12)
);

ALTER TABLE LOAN_TERMS
    ADD VERSIONING USE HISTORY TABLE LOAN_TERMS_HIST;

Addressing Requirement 2 (Complete): Retroactive Correction Detection

The full audit query shows both the rate the bank currently believes was effective and the rate it believed at origination time:

-- What we CURRENTLY believe the terms were at origination
SELECT 'CURRENT BELIEF' AS perspective,
       lt.loan_id,
       lt.interest_rate,
       lt.monthly_payment,
       lt.bus_start,
       lt.bus_end,
       lt.sys_start
FROM   LOAN_TERMS lt
    FOR BUSINESS_TIME AS OF '2024-03-12'
WHERE  lt.loan_id = 50001;

-- What we believed AT THE TIME OF ORIGINATION
SELECT 'BELIEF AT ORIGINATION' AS perspective,
       lt.loan_id,
       lt.interest_rate,
       lt.monthly_payment,
       lt.bus_start,
       lt.bus_end,
       lt.sys_start
FROM   LOAN_TERMS lt
    FOR SYSTEM_TIME AS OF '2024-03-12-17.30.00.000000000000'
    FOR BUSINESS_TIME AS OF '2024-03-12'
WHERE  lt.loan_id = 50001;

If these two queries return different interest rates, it means the terms were retroactively corrected after origination. The sys_start timestamp in the first query shows when the correction was entered.

Addressing Requirement 4: Backdated Correction Identification

-- Find all loan terms that were entered into the system AFTER their
-- business-effective start date (i.e., backdated corrections)
SELECT lt.loan_id,
       lt.interest_rate,
       lt.monthly_payment,
       lt.bus_start AS effective_from,
       lt.bus_end AS effective_until,
       lt.sys_start AS entered_in_system_at,
       DAYS(DATE(lt.sys_start)) - DAYS(lt.bus_start) AS days_backdated
FROM   LOAN_TERMS lt
    FOR SYSTEM_TIME FROM '2024-01-01' TO '2025-01-01'
WHERE  DATE(lt.sys_start) > lt.bus_start
  AND  YEAR(lt.bus_start) = 2024
ORDER BY days_backdated DESC;

This query identifies every loan term modification where the system entry date (sys_start) is later than the business-effective date (bus_start) — meaning someone entered data that took effect retroactively. The days_backdated column quantifies how far back the correction reached.

Examination Results

The OCC examiners were given direct SQL access (read-only) to the temporal tables. Their assessment:

Requirement 1: Satisfied in full. Customer profile reconstruction at any historical point was instantaneous and verifiable.
Requirement 2: Satisfied in full. The bitemporal model on LOAN_TERMS provided both "current belief" and "belief at time of origination" for every loan.
Requirement 3: Satisfied in full. The system-time history combined with LAG window functions provided complete change timelines.
Requirement 4: Satisfied in full. The backdated correction report identified 14 instances of retroactive modifications in 2024, all with documented business justifications.

The examiners noted in their report: "The bank's use of DB2 temporal tables provides a robust, database-enforced audit trail that does not depend on application-level logging. This represents a best practice for data governance."

Lessons Learned

System-time is "set and forget": Once enabled, every UPDATE and DELETE is automatically versioned. No application changes were needed. This is the single most impactful data governance feature in DB2.
Business-time requires careful design of the period columns: Choosing DATE vs. TIMESTAMP, inclusive vs. exclusive boundaries, and the granularity of the WITHOUT OVERLAPS key affected how the rate schedule could be queried.
Bitemporal is essential for regulated industries: The ability to distinguish "what we know now" from "what we knew then" is not optional for banking — it is a regulatory requirement. Bitemporal tables make it a database feature instead of an application burden.
History tables grow: The CUSTOMER_PROFILES_HIST table grew to 12x the size of the current table within 6 months. The team implemented range partitioning on sys_end and archival policies to manage storage. This is the operational cost of temporal — plan for it.
Temporal + window functions = powerful auditing: The combination of FOR SYSTEM_TIME with LAG and LEAD enabled before-and-after reporting that would have required complex self-joins against manually maintained audit tables.
Migration required careful testing: Converting existing tables to temporal required adding period columns, creating history tables, and enabling versioning in a specific order. The team developed a migration playbook and tested on a copy of production data before the cutover.

This case study demonstrates Sections 10.8 (temporal tables — all three types) and their integration with Section 10.5 (LAG/LEAD) for regulatory compliance reporting.