Master High-Level Design (HLD) & Architectural Specification

SAP SuccessFactors to Azure Enterprise Historical Archive

Table of Contents

PART I: MASTER SYSTEM ARCHITECTURE & HIGH-LEVEL DESIGN (HLD)

1. Executive Summary & Migration Objective
2. Master End-to-End Architecture Diagram
3. Component Interaction & Network Topology Matrix
4. Layer-by-Layer Architectural Deep-Dive (Layers 1 to 5)
   • Layer 1: Source System Tier (SAP SuccessFactors)
   • Layer 2: Network Perimeter, Compute & Ingestion Tier
   • Layer 3: Relational Persistence Tier (Azure SQL Database)
   • Layer 4: Unstructured Document Vault Tier (Azure Blob Storage)
   • Layer 5: Audit, Reconciliation & Compliance Gate Tier
5. Core Architectural Scenarios & Decision Deep-Dives
   • Scenario A: Raw Data Backup to Blob Storage (Direct vs Staging Snapshot)
   • Scenario B: Staging-to-Archive Lifecycle Mechanics (stg to archive)
   • Scenario C: Control Database (ctrl) — Batch Tracking, Markers & State Machine
   • Scenario D: Dead-Letter Queue (DLQ), Quarantine & Defect Triage Workflow
   • Scenario E: End-to-End 5-Pillar Reconciliation Suite
   • Scenario F: End-to-End Monitoring, Observability & Telemetry

PART II: THE 3-TEAM DEDICATED SETUP & CONFIGURATION PLAYBOOKS

1. The SAP SuccessFactors SME Playbook
   • 6.1 The 4 Extraction Doors & Architecture
   • 6.2 Technical API User Provisioning & Inactive Leaver Visibility
   • 6.3 SAP Firewall Whitelisting for Azure NAT Egress IP
   • 6.4 OAuth2 Client Application Registration & Public Key Binding
   • 6.5 BCUI XML & Picklist Center Export Playbook
   • 6.6 Cutover Weekend Freeze Protocol & Delta Lock
2. The Azure Data Engineering & Infrastructure Playbook
   • 7.1 Hardened Network Perimeter & Egress Setup (CLI Scripts)
   • 7.2 Azure Key Vault & SAML OAuth Assertion Engine
   • 7.3 Dual-Sink Persistence: 10-Yr WORM Blob Storage & Azure SQL Database
   • 7.4 Master T-SQL Relational Schemas & DDL Deployment
   • 7.5 Date Parsing Function & Surrogate Key Seeding (-1 UNKNOWN)
   • 7.6 Python 16MB In-Flight Binary Streaming Worker Daemon
   • 7.7 Staging-to-Archive Transformation & SCD-2 Promotion Logic
   • 7.8 Control DB State Machine, Batch Leasing & Watermark Engine
   • 7.9 Central Log Analytics Workspace, Alerts & Live Operational Views
   • 7.10 Automated 5-Pillar Reconciliation Suite & Digital Cutover Certification
3. The CData Ingestion Specialist Playbook
   • 8.1 VM Sizing, Host Topology & OS Prerequisites
   • 8.2 Locking JVM Heap Memory (-Xms8g -Xmx8g -XX:+UseG1GC)
   • 8.3 CData Sync Daemon Installation & Service Hardening
   • 8.4 SAP SuccessFactors Source Connection Profile Configuration
   • 8.5 Azure SQL Database Destination Connection Profile
   • 8.6 Critical Job Configuration Parameters (Effective Dates & Leavers)
   • 8.7 Conductor Automation via CData REST API

PART III: CHRONOLOGICAL 13-PHASE CROSS-FUNCTIONAL EXECUTION RUNBOOK (ALL 3 TEAMS ALIGNED)

1. Master 13-Phase Cross-Functional Execution Roadmap (Day 1 to Day 135)
   • Phase 1: Project Initiation & Tenant Discovery (Days 1–5)
   • Phase 2: Cloud Foundation & Network Perimeter Setup (Days 6–12)
   • Phase 3: Key Vault, RSA 2048 & SAP OAuth Handshake (Days 13–18)
   • Phase 4: Dual-Sink Persistence Tier & Private Endpoints (Days 19–25)
   • Phase 5: Ingestion Compute & Workstation Provisioning (Days 26–32)
   • Phase 6: Relational Schemas, Control Plane & DDL Deployment (Days 33–42)
   • Phase 7: Unstructured Document Streaming Worker Setup (Days 43–50)
   • Phase 8: Orchestration, Governance & Monitoring Setup (Days 51–57)
   • Phase 9: Full Historical Extraction Waves Execution (Days 58–85)
   • Phase 10: Delta Synchronization & Cutover Weekend Execution (Days 86–95)
   • Phase 11: 5-Pillar Reconciliation Gate & Forensic Sign-Off (Days 96–100)
   • Phase 12: Resource Optimization & Downscaling Runbook (Days 101–105)
   • Phase 13: 30-Day Hypercare & SAP Decommissioning (Days 106–135)

PART IV: TROUBLESHOOTING RUNBOOKS, ADRs & GOVERNANCE SIGN-OFF

1. Operational Troubleshooting Decision Trees & Defect Runbooks
   • Runbook 1: Resolving SAP API Rate-Limiting / HTTP 429 Throttling
   • Runbook 2: In-Flight Worker Crash & Automatic Lease Recovery
   • Runbook 3: Dead-Letter Queue (DLQ) Defect Triage & Reprocessing
   • Runbook 4: Corrupted Binary Attachment Recovery
   • Runbook 5: Disaster Recovery Replay from Parquet Cold Backup
2. Wave Execution Plan & Dependency Matrix
3. Architectural Decision Records (ADR Summary)

Section 011. Executive Summary & Migration Objective

1.1 The Objective

The objective of this project is to extract 20+ years of historical human capital management (HCM) records from an enterprise SAP SuccessFactors SaaS tenant, transfer them securely across a hardened private Azure perimeter, and persist them permanently into an enterprise Azure archive.

Once this migration is completed and certified, the organization will safely decommission and terminate its SAP SuccessFactors subscription, saving millions in ongoing licensing fees while fulfilling all statutory, tax, employment law, and legal discovery obligations for the next 10 years.

1.2 The 7 Non-Negotiable Architectural Principles

1. Zero Data Loss ($0.00 & 0 Row Variance): Every single employee, historical job slice, compensation figure down to the exact cent, and binary file must be accounted for mathematically. "Accounted for" means present in the archive or held in the DLQ under triage. Money is stored and summed as DECIMAL(19,4) (never floating point) and reconciled per currency against control totals captured from SAP at extraction time.
2. Strictly Read-Only Source Integration: The extraction engine operates as an unprivileged, read-only guest. It never modifies, flags, updates, or deletes any record inside SAP SuccessFactors.
3. Immutability & WORM Legal Hold: Unstructured employee documents (contracts, passports, performance reviews) are stored under a hardware-enforced 10-year Write-Once-Read-Many (WORM) time lock. Once the policy is locked, no administrator can delete or overwrite them. An unlocked policy can still be removed, so locking is an explicit, irreversible step taken after the cutover certificate is issued (ADR-07).
4. Resilient, Non-Blocking Ingestion: Corrupted historical rows (invalid dates, truncated strings, missing parent keys) must never abort a batch. They land in a permissive staging area and route automatically to a Dead-Letter Queue (DLQ) for triage.
5. Idempotent Execution: Every pipeline step can be rerun multiple times without producing duplicate records, broken timelines, or corrupted counters.
6. Zero Public Network Ingress: All compute, database, and storage assets reside entirely inside private subnets behind Azure Private Endpoints. Ingestion egress exits exclusively through a single, static allowlisted NAT Gateway IP.
7. Complete Auditability & Traceability: Every promoted row and every stored document maintains a direct cryptographic trace back to its batch run, source timestamp, and extraction payload.

Section 022. Master End-to-End Architecture Diagram

The master diagram follows a record end to end through all five layers: out of SAP SuccessFactors through four extraction doors, across the private Azure perimeter, into the relational store and the document vault, and through the reconciliation gate that certifies the cutover.

Section 033. Component Interaction & Network Topology Matrix

Every connection in this architecture is tightly restricted, routed across private subnets, and monitored by Network Security Groups (NSGs).

Section 044. Layer-by-Layer Architectural Deep-Dive

Layer 1: Source System Tier (SAP SuccessFactors)

What We Achieve

An authoritative, legally authentic extraction of 20+ years of HR history without modifying the source tenant, triggering HR workflows, or degrading live system performance.

How We Achieve It (Sub-Layers)

1. Sub-Layer 1.1: Schema Catalog & Dictionaries (Door 1)
   • Endpoint: GET /odata/v2/$metadata + Business Configuration UI (BCUI) XML + Picklist Center CSV exports.
   • Why It Is Crucial: In SAP SuccessFactors, custom attributes have cryptic technical keys (customString7, customDate2), and dropdown selections are stored as arbitrary numeric Option IDs (41823, 10928). Without the dictionary, the database is an unreadable matrix of numbers.
   • Lifecycle Frequency (Required Only 3 to 4 Times in the Entire Project):
     1. Day 0 (Discovery): Initial crawl from DEV/QA or PROD to build baseline translation tables.
     2. STAGING Dry Run (Rehearsal): Re-exported from the fresh PROD clone to capture any tenant config updates.
     3. Cutover Weekend (Final Freeze): Final export right before freeze to capture any last-minute picklist options added by HR.
     4. Ad-Hoc: Only if client HR administrators alter the data model.
   • How It Works in Practice:
     • The Python script tools/metadata/load_dictionaries.py parses BCUI_DataModel.xml and loads meta.FieldDictionary: customString7 → "Union Membership Status".
     • It parses Picklist Center CSVs and loads meta.PicklistOption: Option ID 41823 → "Lateral Transfer" (Locale: en_US, External Code: TRANSFER).
     • Staged and promoted rows preserve both the translated label and the verbatim raw code side-by-side (event_reason = 'TRANSFER', event_reason_raw = '41823'). Nothing is ever lost or overwritten.
2. Sub-Layer 1.2: Tabular Relational Entities (Door 2)
   • Endpoint: GET /odata/v2/{Entity}?fromDate=1900-01-01&toDate=9999-12-31&$select=...
   • Mechanism: Extracted via CData Sync in cursor-paginated batches ($skiptoken / 1,000 records per page).
   • The Effective Dating Trap & Solution: Without query parameters, SAP returns only today's single active record. We enforce fromDate=1900-01-01&toDate=9999-12-31 on every effective-dated entity to unroll the full 20-year chronological timeline.
   • The Inactive Employees ("Vanishing Leavers") Trap & Solution: Standard queries silently exclude terminated workers. We configure the SAP technical user's Role-Based Permission (RBP) Target Population to "Everyone, including inactive users" and include the explicit query filter: userNav/status in 't','f','T','F','e','d'.
3. Sub-Layer 1.3: Document Vault (Door 3)
   • Mechanism: Handled as a two-phase decoupled extraction:
     • Phase A (The List): Extracted via CData from /odata/v2/Attachment to land metadata (attachmentId, fileName, fileSize, userId, lastModifiedDateTime) into stg.Attachment_landing.
     • Phase B (The Content): Fetched directly by the Python Streaming Worker in 16MB in-memory blocks using /odata/v2/Attachment({id})/$value (or streamed Base64 JSON parsing).
4. Sub-Layer 1.4: Independent Verification Channel (Door 4)
   • Endpoint: CompoundEmployee SOAP API.
   • Mechanism: Completely separate extraction channel used by an independent verification auditor to pull full hierarchical XML trees for a 10% stratified sample of employees. This sample is cross-checked against the 3NF tables in Azure SQL to prove that CData's OData extraction did not drop child records.

Layer 2: Network Perimeter, Compute & Ingestion Tier

What We Achieve

Complete network isolation, zero exposed public IP addresses, deterministic single-point egress for SAP allowlisting, and robust compute orchestration capable of running weeks of unattended extraction.

How We Achieve It (Sub-Layers)

1. Sub-Layer 2.1: Virtual Network Topology (VNET-SF-ARCHIVE)
   • Address space: 10.50.0.0/16. Subnet boundaries strictly isolate compute, private endpoints, management, and bastion hosts.
2. Sub-Layer 2.2: Outbound Egress Control
   • A dedicated Azure NAT Gateway (nat-sf-archive-prod) associates with snet-cdata and snet-aca.
   • All outbound traffic presents a single static IP address (PIP-NAT-SF-ARCHIVE) with 64,000 allocated SNAT ports, eliminating SNAT exhaustion during concurrent multi-table pulls.
3. Sub-Layer 2.3: Zero-Ingress Compute & Bastion Jumpbox
   • No virtual machine has a public IP address. System administrators connect through Azure Bastion inside an encrypted browser session. SSH keys and local administrator credentials are stored in Azure Key Vault.
4. Sub-Layer 2.4: Ingestion Compute Engines
   • CData Sync VM: Sized as Standard_D8s_v5 (8 vCPU, 32GB RAM). JVM memory is locked at -Xms8g -Xmx8g with Garbage-First Garbage Collector (-XX:+UseG1GC) to ensure deterministic memory allocation during multi-million-row pagination.
   • Python Binary Streaming Worker: Runs as a serverless container inside Azure Container Apps (ACA). Employs an in-memory 16MB chunk buffer to stream large binary attachments directly into Blob Storage without caching temporary files on disk.
   • Conductor Engine: Manages batch state progression, claims slice leases from the Control DB, triggers CData jobs via REST API, and sequences wave execution.
5. Sub-Layer 2.5: Private Link & DNS Isolation
   • Azure SQL, Azure Storage, and Azure Key Vault communicate solely through Private Endpoints on snet-private-endpoints (10.50.2.0/24). Public network access is disabled (publicNetworkAccess = 'Disabled').

Layer 3: Relational Persistence Tier (Azure SQL Database)

What We Achieve

A resilient landing buffer that never crashes on malformed data, a permanent normalized 3NF historical data warehouse with Slow Changing Dimension Type 2 (SCD-2) timelines, and a central control plane.

How We Achieve It (Sub-Layers)

1. Sub-Layer 3.1: Database Configuration & Sizing
   • Collation: Latin1_General_100_CI_AS_SC (Case-Insensitive, Accent-Sensitive, Supplementary Characters for full Unicode & Emoji support).
   • Storage Engine: Hyperscale / Business Critical with Transparent Data Encryption (TDE) and automated geo-redundant backups.
2. Sub-Layer 3.2: 5-Schema Structural Isolation
   • ctrl: Orchestration, batch leasing, watermarks, slice plans, state machines.
   • stg: Permissive raw ingestion landing heaps and exception DLQ.
   • archive: Clean 3NF historical data warehouse, SCD-2 timelines, and document linkage.
   • dq: Data quality rules, triage logs, and findings for HR review.
   • meta: Source $metadata snapshots, field dictionaries, picklist translations.
   • recon: Automated 5-pillar verification procedures, defect registers, and certificate ledgers.
3. Sub-Layer 3.3: Permissive Landing (NVARCHAR(MAX))
   • Every column in stg.*_landing is defined as NVARCHAR(MAX). Tables are unindexed heaps. Ingestion bulk copy never aborts due to data type mismatches, string truncations, or format shifts.
4. Sub-Layer 3.4: 3NF Relational History & SCD-2 Timeline Engine
   • Effective dating is modeled as a Slow Changing Dimension Type 2 (SCD-2) structure:
     • effective_from: Inclusive start date.
     • effective_to: Exclusive end date (active records point to 9999-12-31).
     • is_current: Fast bit index (1 = active current role, 0 = historical slice).
   • Foreign key integrity is strictly enforced: missing parent lookups route to an explicit -1 UNKNOWN surrogate key with a corresponding entry in dq.Finding. No orphaned records are ever discarded.
5. Sub-Layer 3.5: Metadata Translation & Verbatim Preservation
   • Every translated field preserves both the translated business text and the original source code side-by-side:
     • event_reason: 'TRANSFER' (human-readable).
     • event_reason_raw: '41823' (verbatim SAP picklist ID).

Layer 4: Unstructured Document Vault Tier (Azure Blob Storage)

What We Achieve

A 10-year tamper-proof legal document vault for scanned passports, contracts, and performance appraisals, combined with cold compressed historical backups and signed reconciliation certificates.

How We Achieve It (Sub-Layers)

1. Sub-Layer 4.1: Storage Architecture & Immutability
   • Type: General Purpose v2 (StorageV2) with Cool Access Tier default.
   • Immutability: Time-Based Retention WORM policy locked for 10 years (3,650 days) in production. Azure hardware enforces that no blob can be overwritten or deleted by any user or administrator until the retention window expires.
2. Sub-Layer 4.2: Four Purpose-Built Containers
   • attachments/: Master legal document archive.
   • parquet/: Raw tabular staging backup snapshots.
   • reconciliation/: Digital certificates of migration reconciliation.
   • manifests/: Sealed legal discovery and DSAR export packages.
3. Sub-Layer 4.3: 16MB Chunked Streaming Pipeline
   • The Python worker pulls file bytes from SAP in 16MB chunks via HTTPS, writes them to Azure Blob Storage using PutBlock / PutBlockList, and computes a SHA-256 checksum in flight.
   • Zero Disk Caching: The worker never writes binary files to a local file system (/tmp), preventing out-of-disk crashes during high-volume document extraction.
4. Sub-Layer 4.4: Relational 4-Way Linkage (SQL ⇄ Blob)
   • Every file is bound to its database record across 4 immutable links:
     • Link 1: SQL stores the exact container, blob path, version ID, and SHA-256 hash in archive.EmployeeAttachment.
     • Link 2: Blob system metadata stores attachmentrowid, sfattachmentid, and personidext.
     • Link 3: Blob path naming convention is strictly deterministic: {person_id}/{document_type}/{attachment_id}_{sha256}.{ext}.
     • Link 4: Cryptographic checksum: SHA-256 hash in the database matches the computed hash of the stored blob bytes.
5. Sub-Layer 4.5: Ephemeral SAS Retrieval Service
   • Blob storage URLs are never exposed to end users. The Retrieval API generates a 5-minute, read-only, user-delegated Shared Access Signature (SAS) URL only after validating the requesting user's Entra ID group membership in archive.EmployeeAttachment. Every access request is logged to an audit table.

Layer 5: Audit, Reconciliation & Compliance Gate Tier

What We Achieve

Forensic, mathematical verification that the Azure archive is an exact, uncorrupted replica of the source SAP system, providing external auditors and executive leadership with the legal backing required to terminate the SAP contract.

How We Achieve It (Sub-Layers)

1. Sub-Layer 5.1: Automated 5-Pillar Trust Suite
   • Executed via stored procedure recon.usp_RunCutoverPillars after each batch and cycle. Evaluates the 5 mathematical dimensions of data fidelity.
2. Sub-Layer 5.2: Independent Out-of-Band Auditor
   • A standalone Python engine extracts hierarchical XML trees for 10% of employees via the CompoundEmployee SOAP API. It independently parses job slices, compensation lines, and personal records, cross-verifying them against the normalized 3NF tables in Azure SQL.
3. Sub-Layer 5.3: Cryptographically Signed Reconciliation Certificate
   • When all pillars pass with zero variance, the engine generates a digitally signed JSON certificate (reconciliation/certificates/{batch}_recon_cert.json) containing record counts, financial sums, hash trees, and auditor timestamps. The certificate is stored under WORM retention.
4. Sub-Layer 5.4: Cutover Decision Gate
   • If any pillar fails (even by a single row, a single cent, or a single missing file), the gate halts automatically, registers an incident in recon.DefectLog, and alerts the engineering team. Decommissioning cannot proceed without signed certificates for all waves.

Section 055. Core Architectural Scenarios & Decision Deep-Dives

Scenario A: Raw Data Backup to Blob Storage (Direct vs Staging Snapshot)

The Problem & Strategic Question

Should we extract raw data from SAP SuccessFactors directly into Azure Blob Storage first, or should we ingest it into SQL staging and create snapshots? How long should raw data be kept, and what are the legal and operational implications?

Architectural Evaluation of Approaches

The Recommended Architecture: Staging-Committed Parquet Snapshots

We adopt Option 3 (Staging-Committed Parquet Snapshot):

1. Why Parquet with Zstandard Compression:
   • Compresses raw text strings by 80% to 90% (a 100GB staging table compresses to 10–15GB).
   • Columnar format allows direct querying via Azure Synapse serverless SQL, Databricks, or DuckDB without reloading the data into an active SQL database.
2. Storage Tiering & Lifecycle Management:
   • Stored in the Cold Tier of stsfarchiveprod001 under container parquet/. Cold Tier provides immediate read access (unlike Archive Tier, which requires hours of rehydration delay) at a fraction of Hot Tier cost.
   • An Azure Storage Lifecycle Management rule is applied:

Action: Delete Blob Condition: Creation Time > Hypercare Exit Date + 30 Days

1. GDPR Compliance (Data Minimization):
   • Retaining perpetual unencrypted raw copies of sensitive employee personal data violates GDPR Article 5(1)(c) (Data Minimisation).
   • Deleting the raw Parquet snapshots 30 days after successful hypercare exit satisfies data minimization mandates, while the cryptographic manifest hashes remain in the permanent WORM reconciliation/ container as legal proof of source fidelity.
2. Disaster Recovery Replay Procedure (Post-SAP Decommissioning): If a latent transformation logic defect is uncovered after SAP has been decommissioned, the affected entity is rebuilt using this exact runbook:
   Text5 linesCopy

   12345

   1. Identify affected batches via ctrl.BatchMaster.
   2. Execute tools/ops/restore_snapshot.py to load Parquet records into stg.<Entity>_landing.
   3. Deploy corrected promotion stored procedure (e.g., curated.usp_Promote_EmpJob).
   4. Execute promotion: idempotency ensures only affected rows and timelines update.
   5. Re-run recon.usp_RunCutoverPillars to verify 100% mathematical parity.

Scenario B: Staging-to-Archive Lifecycle Mechanics (stg to archive)

The Problem & Strategic Question

How do millions of raw string rows in the permissive staging table get cleaned, normalized, checked for business validity, translated from technical codes, promoted into 3NF historical tables, and finally purged from staging without loss?

The 6-Step Ingestion & Promotion Pipeline

Detailed Transformation Step Walkthrough

1. Handling Complex SAP Dates & Cleanse Transformations:

   • SAP OData v2 represents dates as strings like /Date(1451606400000)/ or /Date(-631152000000)/ (for birthdates before January 1, 1970).
   • Our T-SQL scalar function stg.fn_ParseODataDate parses both positive and negative epoch millisecond integers safely into DATETIME2(0) without arithmetic overflow errors.
   • Cleansing removes trailing whitespace, strips carriage returns, and unescapes XML/HTML entities (e.g., & → &).

2. Concrete Row Walk-Through: John Doe (Employee #10442) Over 20 Years:

   • Landed in stg.EmpJob_landing:
     • userId = "10442"
     • startDate = "/Date(1451606400000)/"
     • seqNumber = "2"
     • department = "D9901"
     • eventReason = "41823"
     • customString7 = "Y "
   • Step 1: Stamping & Cleaning (stg.usp_Triage_EmpJob):
     • Date parsed: /Date(1451606400000)/ → 2016-01-01.
     • Trailing whitespace trimmed: "Y " → "Y".
     • Fingerprint generated: SHA-256 of concatenated business attributes → 9c1f8a2e....
   • Step 2: Quality Gate & DLQ Check:
     • Valid user ID? Yes. Valid date? Yes. Parent employment exists in archive.Employment? Yes.
     • (If any check failed, the row would be quarantined into stg.MigrationExceptions and logged in dq.Finding; clean rows proceed).
   • Step 3: Translation & Foreign Key Resolution:
     • Department lookup: department = 'D9901' is checked against archive.Department. If found, department_id = 42. If Department D9901 was deleted 15 years ago, it is remapped to department_id = -1 (UNKNOWN), while preserving department_code_raw = 'D9901' and logging a minor finding. Zero rows are lost!
     • Event reason lookup: eventReason = '41823' looks up meta.PicklistOption → event_reason = 'TRANSFER', while preserving event_reason_raw = '41823'.
     • Custom attribute lookup: customString7 looks up meta.FieldDictionary → "Union Membership Status" = 'Y'.
   • Step 4: SCD-2 Timeline Assembly with LEAD():
     • The window function LEAD(startDate) OVER (PARTITION BY userId ORDER BY startDate, seqNumber) identifies the next promotion date.
     • effective_from = 2016-01-01.
     • If John is still active in this role, next_start_date is NULL → effective_to = '9999-12-31' and is_current = 1.
     • If John was later promoted on 2021-04-01, effective_to becomes '2021-03-31' and is_current = 0.
   • Step 5: Idempotent Upsert via MERGE:
     • Matches on (user_id, effective_from, seq_number). If the row already exists with the same SHA-256 fingerprint, it skips it entirely, guaranteeing zero duplicate rows or broken timelines on reruns.

3. Core SCD-2 T-SQL Implementation (curated.usp_Promote_EmpJob):

CREATE OR ALTER PROCEDURE curated.usp_Promote_EmpJob @BatchId BIGINT AS
BEGIN
    SET NOCOUNT ON;
    WITH CleanedSlices AS (
        SELECT 
            s.userId AS user_id,
            stg.fn_ParseODataDate(s.startDate) AS effective_from,
            CAST(s.seqNumber AS INT) AS seq_number,
            ISNULL(d.department_id, -1) AS department_id,
            s.department AS department_code_raw,
            ISNULL(p.option_label, 'UNKNOWN_REASON') AS event_reason,
            s.eventReason AS event_reason_raw,
            s.jobCode AS job_code,
            s.payGrade AS pay_grade,
            @BatchId AS batch_id,
            HASHBYTES('SHA2_256', CONCAT(s.userId, '|', s.startDate, '|', s.seqNumber, '|', s.department)) AS sha256_fingerprint,
            LEAD(stg.fn_ParseODataDate(s.startDate)) OVER (
                PARTITION BY s.userId ORDER BY stg.fn_ParseODataDate(s.startDate), CAST(s.seqNumber AS INT)
            ) AS next_start_date
        FROM stg.EmpJob_landing s
        LEFT JOIN archive.Department d ON s.department = d.department_code
        LEFT JOIN meta.PicklistOption p ON s.eventReason = p.option_id
        WHERE s.batch_id = @BatchId
    )
    MERGE archive.EmploymentHistory AS TARGET
    USING (
        SELECT 
            user_id, effective_from, seq_number, department_id, department_code_raw,
            event_reason, event_reason_raw, job_code, pay_grade, batch_id, sha256_fingerprint,
            ISNULL(DATEADD(DAY, -1, next_start_date), '9999-12-31') AS effective_to,
            CASE WHEN next_start_date IS NULL THEN 1 ELSE 0 END AS is_current
        FROM CleanedSlices
    ) AS SOURCE
    ON (TARGET.user_id = SOURCE.user_id AND TARGET.effective_from = SOURCE.effective_from AND TARGET.seq_number = SOURCE.seq_number)
    WHEN MATCHED AND TARGET.sha256_fingerprint <> SOURCE.sha256_fingerprint THEN
        UPDATE SET 
            TARGET.effective_to = SOURCE.effective_to,
            TARGET.is_current = SOURCE.is_current,
            TARGET.department_id = SOURCE.department_id,
            TARGET.event_reason = SOURCE.event_reason,
            TARGET.batch_id = SOURCE.batch_id,
            TARGET.sha256_fingerprint = SOURCE.sha256_fingerprint
    WHEN NOT MATCHED THEN
        INSERT (user_id, effective_from, effective_to, is_current, seq_number, department_id, department_code_raw, event_reason, event_reason_raw, job_code, pay_grade, batch_id, sha256_fingerprint)
        VALUES (SOURCE.user_id, SOURCE.effective_from, SOURCE.effective_to, SOURCE.is_current, SOURCE.seq_number, SOURCE.department_id, SOURCE.department_code_raw, SOURCE.event_reason, SOURCE.event_reason_raw, SOURCE.job_code, SOURCE.pay_grade, SOURCE.batch_id, SOURCE.sha256_fingerprint);
END;
GO

Scenario C: Control Database (ctrl) — Batch Tracking, Markers & State Machine

The Problem & Strategic Question

Extracting 20 years of data in one massive query will cause SAP API timeouts, connection resets, and memory crashes. If a failure happens 18 hours into an extraction, how do we prevent restarting from zero? How does the system track what is done, what is running, and what is next?

Architectural Composition & Data Preservation Patterns (ctrl, meta, recon, dlq)

To guarantee that data is never lost, corrupted, or overwritten, the architecture strictly segregates governance into 4 purpose-built schemas:

1. meta Schema (The Rosetta Stone Dictionary):
   • Made of: meta.SourceSnapshot (snapshot versioning), meta.FieldDictionary (BCUI field label mappings), meta.PicklistOption (dropdown code translations), and meta.DocumentClassMap (document retention classes).
   • How it preserves data (The Dual-Column Pattern): Clean 3NF tables in archive.* store translated business values and verbatim raw source codes side-by-side (e.g., event_reason = 'TRANSFER', event_reason_raw = '41823'). Even if translation dictionaries change decades later, the authentic source code is preserved forever.
2. ctrl Schema (The Master State Machine & Conductor):
   • Made of: ctrl.ExtractPlan (annual slice definitions), ctrl.BatchMaster (batch execution logs, timestamps, row metrics, manifest hashes), ctrl.EntityLease (distributed 30-minute worker locks), and ctrl.WatermarkStore (high-water marks for delta syncs).
   • How it preserves data: Records SAP's initial $count before extraction begins as an immutable audit invariant.
3. dlq Tier (stg.MigrationExceptions & dq.Finding):
   • Made of: stg.MigrationExceptions (the physical quarantine table) and dq.Finding (the business triage board).
   • How it preserves data (The Verbatim Preservation Pattern): Corrupted rows (invalid dates, truncated text, orphan parents) are never dropped. Their complete raw payload is stored verbatim as NVARCHAR(MAX) with error codes and batch IDs for HR review and automated reprocessing.
4. recon Schema (The Supreme Audit Court):
   • Made of: recon.CheckResult (pillar scores, expected vs actual values), recon.BlobInventory (daily SHA-256 catalog of all files), and recon.Certificate (ledger of cryptographically signed cutover seals).
   • How it preserves data: Serves as a read-only, tamper-evident audit ledger that cannot be altered or truncated.

Control DB Architecture & Schemas

The ctrl schema in Azure SQL serves as the authoritative state machine and conductor control plane for the entire migration.

Slice & Batch Execution Mechanics

1. Three-Tier Load Slicing:
   • Waves: Parents load before children (Wave 0: Meta → Wave 1: Org → Wave 2: Person → Wave 3: Employment → Wave 4: Job/Pay History → Wave 5: Custom → Wave 6: Attachments).
   • Slices: Large tables (like EmpJob with 20M rows) are split into annual slices based on lastModifiedDateTime (e.g., Slice 2015: 2015-01-01 to 2015-12-31).
   • Batches: Each slice execution is assigned a unique batch_id. If Slice 2015 fails, only Slice 2015 retries. The rest of the 19 years remain untouched.
2. The Batch State Machine: Every batch progresses through a strict sequential state machine:

   The 9-Stage Batch State Machine Automaton

   Every slice execution transitions through a strict, idempotent state machine ensuring zero data loss and fault-tolerant restarts.

   ACTIVE STATE: [LEASED](2/9)

   STAGE 01[PENDING]

   PASSED

   Conductor claims slice lease in ctrl.EntityLease

   Acquires 30-minute worker lease lock via ctrl.usp_AcquireLease. Worker sends heartbeat every 5 min.

   Lease Protocolctrl.usp_AcquireLease() -> GRANTED (Worker: VM-SF-INGEST-01)

   Query SAP $count baseline via HTTPS Door 2

   STAGE 02[LEASED]

   ACTIVE

   Query SAP $count baseline via HTTPS Door 2

   Queries OData v2 endpoint ($count) through Azure NAT Gateway to establish authoritative baseline row count.

   Door 2 BaselineOData $count: 4,821,904 rows expected in slice bracket

   CData Sync / Python pulls data in chunks

   STAGE 03[EXTRACTING]

   QUEUED

   CData Sync / Python pulls data in chunks

   CData daemon on vm-sf-ingest (8GB JVM locked heap) pulls 5,000-row pages with retry logic on HTTP 429.

   8GB Locked JVMThroughput: 85 rec/s · Page: 964 / 965 · Heap: 6.2 / 8.0 GB

   Verify landed_rows == source_count in stg heaps

   STAGE 04[LANDED]

   QUEUED

   Verify landed_rows == source_count in stg heaps

   Permissive unindexed heaps in stg schema with NVARCHAR(MAX) columns accept raw extracted rows without constraint violations.

   Heap Verificationstg.entity_landing: 4,821,904 rows landed (Zero loss verified)

   Evaluate DQ rules & route defects to DLQ

   STAGE 05[TRIAGED]

   QUEUED

   Evaluate DQ rules & route defects to DLQ

   Corrupted records or missing FK parents land in stg.MigrationExceptions with original raw JSON. Zero crashes.

   DLQ QuarantineDQ Audit: 4,821,892 Valid · 12 Quarantined in stg.MigrationExceptions

   SCD-2 merge into curated 3NF archive.* tables

   STAGE 06[PROMOTED]

   QUEUED

   SCD-2 merge into curated 3NF archive.* tables

   Deterministic T-SQL MERGE preserves valid_from and valid_to intervals with surrogate key resolution.

   3NF SCD-2 Mergearchive.EmpJob: 4,821,892 temporal intervals indexed and committed

   Verify 5-pillar mathematical parity

   STAGE 07[RECONCILED]

   QUEUED

   Verify 5-pillar mathematical parity

   Compares row counts, sum hashes, Door 4 SOAP audit records, and attachment counts. 0.00% variance required.

   0.00% Audit Gatereconciliation.usp_VerifyPillars: 5 / 5 Gates Green (Variance: 0.0000%)

   Write Parquet snapshot & record batch manifest

   STAGE 08[COMMITTED]

   QUEUED

   Write Parquet snapshot & record batch manifest

   Writes cold compressed Zstandard Parquet copy to parquet/ container and updates watermark state machine.

   Parquet ManifestBlob storage: parquet/2015/batch_4821.parquet written (SHA-256 sealed)

   Release worker lease & seal batch logs

   STAGE 09[COMPLETED]

   QUEUED

   Release worker lease & seal batch logs

   Lease released in ctrl.EntityLease. SHA-256 fingerprint written to immutable legal compliance manifest.

   Batch Sealedctrl.EntityLease: Lease released · Cycle status: COMPLETED_ARCHIVED
3. Distributed Concurrency & Lease Locks (ctrl.EntityLease):
   • Multiple worker nodes run concurrently. Before starting an entity or slice, a worker calls ctrl.usp_AcquireLease.
   • The procedure grants a 30-minute lease lock. The worker sends heartbeats every 5 minutes.
   • If a worker crashes, its lease expires automatically after 30 minutes, freeing the slice to be safely re-claimed by another worker without manual DBA intervention.

Scenario D: Dead-Letter Queue (DLQ), Quarantine & Defect Triage Workflow

The Problem & Strategic Question

In 20 years of legacy HR operations, data corruption is inevitable: employees assigned to deleted departments, invalid date formats, corrupted character encodings, or missing national identifiers. How do we ensure bad data never crashes the migration while maintaining full visibility and auditability?

DLQ Architecture & Isolation Pattern

Defect Severity & Classification Matrix

Scenario E: End-to-End 5-Pillar Reconciliation Suite

The Problem & Strategic Question

How do we provide irrefutable mathematical proof to internal compliance, external auditors, and legal counsel that every single record was migrated accurately and not a single byte was altered?

The 5 Pillars of Mathematical Truth

Why OData Cannot Audit OData: The Role of Door 4 CompoundEmployee SOAP API as Independent Auditor

In forensic financial and regulatory audits, an immutable principle applies: You cannot use the same extraction interface to verify its own correctness.

• If CData Sync or the SAP OData v2 engine has a bug (e.g., dropped records during cursor pagination, or skipped inactive leavers), querying OData a second time will simply repeat the identical flaw.
• The Independent Auditor Solution: We deploy an out-of-band auditor (tools/audit/compound_employee_auditor.py) that communicates through Door 4: CompoundEmployee SOAP API.
• What CompoundEmployee Delivers: CompoundEmployee is SAP's gold-standard native integration interface. It does not return flat tables; it returns an unflattened, complete hierarchical XML tree of an employee's entire lifecycle (<person>, <employment_information>, <job_information>, <compensation_information>).
• How It Orchestrates Verification:
  1. The auditor extracts the complete SOAP XML tree for a 10% stratified sample of employees (active, terminated, executive, hourly, expat).
  2. It independently parses job slices, salary lines, and personal records from the XML.
  3. It performs a direct cross-system parity comparison against the promoted 3NF SQL tables (archive.Person, archive.EmploymentHistory, archive.CompensationHistory).
  4. This mathematically proves that CData's OData extraction and SQL staging transformations did not drop a single historical record or alter a single effective date.

Cryptographic Reconciliation Certificate Structure

When all 5 pillars achieve 100% pass status, the system generates an immutable JSON certificate stored under WORM retention in stsfarchiveprod001/reconciliation/certificates/:

{
  "certificate_id": "CERT-SFMIG-PROD-20261120-WAVE4",
  "environment": "PROD",
  "execution_timestamp": "2026-11-20T14:32:00Z",
  "certified_by": "recon.usp_RunCutoverPillars_v2",
  "audit_results": {
    "pillar_1_row_count": {
      "source_odata_count": 18452109,
      "target_promoted_count": 18451890,
      "quarantined_dlq_count": 219,
      "mathematical_variance": 0,
      "status": "PASSED"
    },
    "pillar_2_financial_sum": {
      "source_payroll_sum_usd": 1428591024.52,
      "target_payroll_sum_usd": 1428591024.52,
      "variance_usd": 0.00,
      "status": "PASSED"
    },
    "pillar_3_referential_integrity": {
      "total_foreign_keys_checked": 18451890,
      "uncataloged_orphans": 0,
      "status": "PASSED"
    },
    "pillar_4_scd2_continuity": {
      "overlapping_date_slices": 0,
      "unauthorized_gaps": 0,
      "status": "PASSED"
    },
    "pillar_5_binary_hashes": {
      "total_attachments_certified": 482104,
      "sha256_checksum_failures": 0,
      "byte_length_mismatches": 0,
      "status": "PASSED"
    }
  },
  "overall_gate_status": "CERTIFIED_ZERO_DEFECTS",
  "digital_signature_sha256": "8f3b9c1d2e...4a5b6c7d8e"
}

Scenario F: End-to-End Monitoring, Observability & Telemetry

The Problem & Strategic Question

During an extraction that spans weeks, how do operations teams monitor real-time throughput, spot API throttling, detect hanging jobs, catch DLQ spikes, and prevent silent failures?

Multi-Tier Observability Architecture

Incident Triage & Automated Recovery Rules

Part IITHE 3-TEAM DEDICATED SETUP & CONFIGURATION PLAYBOOKS

The following three sections form the authoritative technical playbooks for the primary engineering squads executing this migration:

• Section 6: The SAP SuccessFactors SME Playbook (Source configuration, RBP, OAuth registration, BCUI/Picklist export, and freeze management).
• Section 7: The Azure Data Engineering & Infrastructure Playbook (Cloud networking, Key Vault, dual-sink persistence, DDL schemas, streaming worker, SCD-2 procs, control DB, and reconciliation).
• Section 8: The CData Ingestion Specialist Playbook (VM sizing, JVM heap locking, CData Sync installation, source/destination profiles, query filters, and REST automation).

Section 066. The SAP SuccessFactors SME Playbook

This playbook defines all responsibilities, configuration steps, and operational procedures that the SAP SuccessFactors Technical Lead and SME must execute in SAP Admin Center and Provisioning.

6.1 The 4 Extraction Doors & Architecture

SAP SuccessFactors exposes multiple distinct API interfaces. To prevent data corruption and extraction failures, each interface is dedicated to a specific operational requirement:

Door 1: Core Tabular OData v2

• Target Entities: PerPerson, EmpEmployment, EmpJob, EmpCompensation, FOCompany, FODepartment, Cust_*.
• Required Query Parameters: fromDate=1900-01-01;toDate=9999-12-31, $format=json, $skiptoken.
• Execution Engine: CData Sync running on vm-sf-cdata.

Door 2: Delta Sync OData v2

• Query Filter: $filter=lastModifiedDateTime ge datetimeoffset'2026-11-20T00:00:00Z'.
• Purpose: Captures records modified between the initial historical baseline snapshot and the final cutover weekend freeze.

Door 3: Binary Document Streaming

• Endpoint: https://api12.successfactors.eu/odata/v2/Attachment(<attachmentId>)/$value
• Protocol: HTTP GET with streaming binary octet-stream response.
• Execution Engine: Azure Container Apps Python 16MB Streaming Daemon (zero local disk caching).

Door 4: CompoundEmployee SOAP XML API

• Endpoint: https://api12.successfactors.eu/sfapi/v1/soap?wsdl
• Protocol: SOAP 1.1 / 1.2 XML payload returning deep hierarchical employee trees.
• Purpose: Purely audit and verification. It bypasses OData relational joins to validate that the SQL database faithfully represents the root SAP employee graph.

6.2 Technical API User Provisioning & Inactive Leaver Visibility

To execute automated extractions without interactive authentication, the SAP SME must provision a dedicated technical integration user.

Step 1: Create the Technical User

1. Log in to SAP SuccessFactors Admin Center as an Administrator.
2. Navigate to Admin Center → Tools → Manage Users → Add New User (or use Basic Import).
3. Populate user attributes:
   • User Name: SF_MIG_TECH_USER
   • First Name: Azure Migration
   • Last Name: Technical User
   • Email: sf-migration-svc@company.com
   • Status: Active
4. Navigate to Password & Login Policy Settings. Ensure SF_MIG_TECH_USER is assigned to an API Exemption Group (exempt from SSO redirect, MFA prompts, and password expiry policies).

Step 2: Configure Role-Based Permissions (RBP)

1. Navigate to Admin Center → Manage Permission Roles → Create New.
2. Role Name: RBP_SF_AZURE_MIGRATION_READONLY
3. Description: Read-only access to all Employee Central, Foundation, and MDF objects for Azure archive migration.
4. Under User Permissions, enable:
   • General User Permission:
     • SFAPI Web Service Access = Checked
     • OData API Access = Checked
   • Employee Central API:
     • Employee Central HRIS OData API (Read-only) = Checked
     • Employee Central Foundation OData API (Read-only) = Checked
   • Employee Data: Grant View (Read-Only) to all standard and custom HR blocks:
     • Personal Information, Biographical Information, National ID Card.
     • Employment Details, Job Information, Compensation Information.
     • Address Information, Emergency Contacts, Global Assignments.
   • Metadata Framework (MDF):
     • Admin Access to MDF OData API = Checked
     • Read access to all custom MDF entities (Cust_*) = Checked

6.3 SAP Firewall Whitelisting for Azure NAT Egress IP

SAP SuccessFactors SaaS environments reject API requests from untrusted IP ranges.

1. Obtain the dedicated Azure NAT Gateway Static Public IP address from the Azure Infrastructure Engineer (PIP-NAT-SF-ARCHIVE, e.g., 20.105.42.18).
2. Log in to SAP SuccessFactors Admin Center.
3. Navigate to Company Settings → Password & Login Policy.
4. Click Set IP Restrictions.
5. Add the static IP:
   • IP Range: 20.105.42.18 to 20.105.42.18 (Subnet Mask 255.255.255.255)
   • Description: Azure Archive Migration NAT Egress Gateway
6. Click Save Changes. Verify connectivity using a curl test from the Azure Jumpbox.
7. Confirm with SAP Support that the IP restriction applies to API traffic (OData and SOAP) on your tenant's data-centre API host, not only to UI logins. Then prove it in both directions: a call from the NAT IP succeeds, and the same call from any other IP is rejected.

6.4 OAuth2 Client Application Registration & Public Key Binding

SAP SuccessFactors requires OAuth 2.0 with SAML 2.0 Bearer Assertion authentication.

Step-by-Step Registration in Admin Center:

1. Navigate to Admin Center → Manage OAuth2 Client Applications.
2. Click Register Client Application.
3. Configure the application:
   • Application Name: AZURE_MIGRATION_CDATA
   • Application URL: https://company.successfactors.com
   • Description: Production Azure Archive Migration Pipeline
   • X.509 Certificate: Open sfmig-oauth-cert.cer (provided by Azure Infra SME) in a text editor. Copy and paste the entire block including -----BEGIN CERTIFICATE----- and -----END CERTIFICATE-----.
4. Click Register.
5. Copy the generated API Key (Client ID) (a 32-character base64 string). Securely provide this Client ID to the Azure DE and CData SME.

6.5 BCUI XML & Picklist Center Export Playbook

SAP SuccessFactors stores field labels and code values in two distinct administrative catalogs:

1. BCUI XML Data Model: Maps technical database column names (customString7, customDate2) to human-readable business labels ("Union Status", "Probation End Date").
2. Picklist Center: Maps numeric option IDs (90214, 1042) to human-readable text values ("Full-Time Regular", "Active").

Frequency & Operational Lifecycle Table

BCUI and Picklists are administrative master metadata that evolve slowly. They are exported exactly 3 to 4 times across the entire 135-day project lifecycle:

Step 1: Export BCUI XML Data Model

1. In Admin Center, search for Manage Business Configuration.
2. In the left navigation tree, select the root node.
3. Click Export in the top-right toolbar.
4. Save the file as BCUI_DataModel.xml.
5. Verify that elements like <hris-field id="customString7"> contain <label>Union Representative Status</label>.
6. Commit the file to the project repository under config/metadata/bcui_datamodel.xml.

Step 2: Export Picklist Center Master Dictionaries

1. In Admin Center, search for Picklist Center.
2. Click Export All Picklists.
3. Select both Legacy Picklists and MDF Picklists.
4. Download the resulting zip file and unpack into config/metadata/picklists/.
5. Hand over files to the Azure Data Engineer to execute the parsing and seeding stored procedure (meta.usp_LoadPicklists).

6.6 Cutover Weekend Freeze Protocol & Delta Lock

On Day 86 (Cutover Weekend), the SAP SME executes the authoritative freeze protocol to guarantee data immutability during final delta extraction:

1. System Broadcast Banner: Post a global login message in SAP Admin Center: "System Freeze in Progress for Historical Data Migration. Write access suspended until Monday 08:00."
2. Revoke HR Write Permissions: Temporarily clone active HR Administrator permission roles, remove Edit/Insert/Delete permissions, and leave only View permissions.
3. Inbound Feed Suspension: Coordinate with HR Ops to suspend scheduled payroll and recruitment inbound integrations (e.g., Workday, ADP, Greenhouse feeds).
4. Maintain Technical User: Ensure SF_MIG_TECH_USER retains unrestricted read-only access.
5. Delta Window Sign-Off: Formally notify the CData and Azure DE leads that the tenant is frozen and ready for the final delta extraction wave.

Section 077. The Azure Data Engineering & Infrastructure Playbook

This playbook provides all production scripts, Azure CLI commands, T-SQL DDL schemas, streaming worker code, and operational configurations required by the Azure Data Engineering and Cloud Infrastructure Squads.

7.1 Hardened Network Perimeter & Egress Setup (CLI Scripts)

Execute the following bash script from an authenticated Azure Cloud Shell or administrative jumpbox:

#!/usr/bin/env bash
set -euo pipefail

# Configuration Variables
export RG="RG-HR-SF-ARCHIVE-PROD"
export LOCATION="westeurope"
export VNET_NAME="VNET-SF-ARCHIVE"

echo "[1/5] Creating Dedicated Resource Group..."
az group create --name $RG --location $LOCATION \
  --tags Environment=PROD Project=SF-Archive DataClassification=HighlyRestricted

echo "[2/5] Creating Virtual Network (10.50.0.0/16)..."
az network vnet create \
  --resource-group $RG \
  --name $VNET_NAME \
  --address-prefixes 10.50.0.0/16 \
  --location $LOCATION

echo "[3/5] Creating Subnets..."
az network vnet subnet create --resource-group $RG --vnet-name $VNET_NAME --name snet-cdata --address-prefixes 10.50.1.0/24
az network vnet subnet create --resource-group $RG --vnet-name $VNET_NAME --name snet-private-endpoints --address-prefixes 10.50.2.0/24
az network vnet subnet create --resource-group $RG --vnet-name $VNET_NAME --name AzureBastionSubnet --address-prefixes 10.50.3.0/24
az network vnet subnet create --resource-group $RG --vnet-name $VNET_NAME --name snet-aca --address-prefixes 10.50.4.0/24
az network vnet subnet create --resource-group $RG --vnet-name $VNET_NAME --name snet-management --address-prefixes 10.50.5.0/24

echo "[4/5] Deploying Static Egress NAT Gateway..."
az network public-ip create \
  --resource-group $RG \
  --name PIP-NAT-SF-ARCHIVE \
  --location $LOCATION \
  --allocation-method Static \
  --sku Standard \
  --tier Regional

az network nat gateway create \
  --resource-group $RG \
  --name nat-sf-archive-prod \
  --location $LOCATION \
  --public-ip-addresses PIP-NAT-SF-ARCHIVE \
  --idle-timeout-in-minutes 10

az network vnet subnet update --resource-group $RG --vnet-name $VNET_NAME --name snet-cdata --nat-gateway nat-sf-archive-prod
az network vnet subnet update --resource-group $RG --vnet-name $VNET_NAME --name snet-aca --nat-gateway nat-sf-archive-prod

echo "[5/5] Deploying Azure Bastion Host..."
az network public-ip create --resource-group $RG --name pip-bastion-prod --location $LOCATION --sku Standard
az network bastion create \
  --resource-group $RG \
  --name bas-sf-archive-prod \
  --public-ip-address pip-bastion-prod \
  --vnet-name $VNET_NAME \
  --location $LOCATION

STATIC_IP=$(az network public-ip show --resource-group $RG --name PIP-NAT-SF-ARCHIVE --query ipAddress -o tsv)
echo "PERIMETER DEPLOYMENT COMPLETE. STATIC EGRESS IP FOR SAP WHITELISTING: $STATIC_IP"

7.2 Azure Key Vault & SAML OAuth Assertion Engine

Step 1: Deploy Key Vault with Private Endpoint

az keyvault create \
  --resource-group $RG \
  --name kv-sf-archive-prod-01 \
  --location $LOCATION \
  --enable-rbac-authorization true \
  --public-network-access Disabled

az network private-endpoint create \
  --resource-group $RG \
  --name pe-vault \
  --vnet-name $VNET_NAME \
  --subnet snet-private-endpoints \
  --private-connection-resource-id $(az keyvault show --name kv-sf-archive-prod-01 --query id -o tsv) \
  --group-id vault \
  --connection-name conn-pe-vault

Step 2: Generate RSA 2048-Bit Signing Certificate

# Generate 2048-bit RSA Private Key and Self-Signed X.509 Certificate
openssl req -x509 -newkey rsa:2048 \
  -keyout sfmig-oauth-key.pem \
  -out sfmig-oauth-cert.cer \
  -days 730 \
  -nodes \
  -subj "/CN=sfmig-prod-oauth/O=Enterprise/OU=HR-Data-Migration"

# Securely import Private Key into Azure Key Vault
az keyvault secret set \
  --vault-name kv-sf-archive-prod-01 \
  --name sfmig-oauth-key \
  --file sfmig-oauth-key.pem

echo "Private key stored in Key Vault. Hand over 'sfmig-oauth-cert.cer' to SAP SME."

Step 3: Automated SAML Bearer Token Generation Function

The Conductor and worker instances call this Python function to acquire short-lived OAuth bearer tokens:

import os, datetime, jwt, requests
from azure.identity import DefaultAzureCredential
from azure.keyvault.secrets import SecretClient

def get_sap_oauth_token(client_id: str, company_id: str, user_id: str, token_url: str, kv_url: str) -> str:
    # 1. Fetch RSA Private Key from Azure Key Vault via Managed Identity
    credential = DefaultAzureCredential()
    secret_client = SecretClient(vault_url=kv_url, credential=credential)
    private_key_pem = secret_client.get_secret("sfmig-oauth-key").value

    # 2. Build SAML 2.0 Assertion Claims
    now = datetime.datetime.now(datetime.timezone.utc)
    payload = {
        "iss": client_id,
        "sub": user_id,
        "aud": "www.successfactors.com",
        "iat": int(now.timestamp()),
        "exp": int((now + datetime.timedelta(minutes=10)).timestamp())
    }

    # 3. Cryptographically Sign JWT with RS256
    assertion = jwt.encode(payload, private_key_pem, algorithm="RS256")

    # 4. Exchange Assertion for OAuth2 Bearer Access Token
    response = requests.post(
        token_url,
        data={
            "client_id": client_id,
            "grant_type": "urn:ietf:params:oauth:grant-type:saml2-bearer",
            "assertion": assertion,
            "company_id": company_id
        },
        timeout=15
    )
    response.raise_for_status()
    return response.json()["access_token"]

7.3 Dual-Sink Persistence: 10-Yr WORM Blob Storage & Azure SQL Database

export STORAGE_ACCOUNT="stsfarchiveprod001"
export SQL_SERVER="sql-sf-archive-prod"
export SQL_DB="sqldb-sf-archive-prod"

echo "[1/4] Creating Azure Storage Account (Cool Tier default)..."
az storage account create \
  --name $STORAGE_ACCOUNT \
  --resource-group $RG \
  --location $LOCATION \
  --sku Standard_ZRS \
  --kind StorageV2 \
  --access-tier Cool \
  --allow-blob-public-access false \
  --public-network-access Disabled

echo "[2/4] Creating Storage Containers & Applying 10-Year WORM Policy..."
az storage container create --account-name $STORAGE_ACCOUNT --name attachments --auth-mode login
az storage container create --account-name $STORAGE_ACCOUNT --name parquet --auth-mode login
az storage container create --account-name $STORAGE_ACCOUNT --name reconciliation --auth-mode login
az storage container create --account-name $STORAGE_ACCOUNT --name manifests --auth-mode login

# Apply a 3,650-day (10 years) time-based retention policy to attachments/ and reconciliation/.
# immutability-policy is a management-plane command: it takes --resource-group, not --auth-mode.
# The policy is created UNLOCKED, which still allows it to be deleted. Phase 11 locks it (ADR-07).
for CONTAINER in attachments reconciliation; do
  az storage container immutability-policy create \
    --resource-group $RG \
    --account-name $STORAGE_ACCOUNT \
    --container-name $CONTAINER \
    --period 3650 \
    --allow-protected-append-writes false
done

# Run ONLY after the cutover certificate is issued: locking is irreversible.
# for CONTAINER in attachments reconciliation; do
#   ETAG=$(az storage container immutability-policy show --resource-group $RG --account-name $STORAGE_ACCOUNT \
#            --container-name $CONTAINER --query etag -o tsv)
#   az storage container immutability-policy lock --resource-group $RG --account-name $STORAGE_ACCOUNT \
#     --container-name $CONTAINER --if-match "$ETAG"
# done

az network private-endpoint create \
  --resource-group $RG --name pe-blob --vnet-name $VNET_NAME --subnet snet-private-endpoints \
  --private-connection-resource-id $(az storage account show --name $STORAGE_ACCOUNT --query id -o tsv) \
  --group-id blob --connection-name conn-pe-blob

echo "[3/4] Creating Azure SQL Database (GP_Gen5_8, Latin1_General_100_CI_AS_SC)..."
az sql server create \
  --resource-group $RG --name $SQL_SERVER --location $LOCATION \
  --enable-public-network false \
  --ad-admin-login "AAD_SQL_ADMIN_GROUP" \
  --ad-admin-object-id "00000000-0000-0000-0000-000000000000"

az sql db create \
  --resource-group $RG --server $SQL_SERVER --name $SQL_DB \
  --service-objective GP_Gen5_8 \
  --zone-redundant true \
  --collation Latin1_General_100_CI_AS_SC

az network private-endpoint create \
  --resource-group $RG --name pe-sql --vnet-name $VNET_NAME --subnet snet-private-endpoints \
  --private-connection-resource-id $(az sql server show --name $SQL_SERVER --query id -o tsv) \
  --group-id sqlServer --connection-name conn-pe-sql

echo "DUAL-SINK PERSISTENCE TIER PROVISIONED SUCCESSFULLY."

7.4 Master T-SQL Relational Schemas & DDL Deployment

Connect to sqldb-sf-archive-prod via Azure Bastion and execute the complete DDL suite:

-- ============================================================================
-- 1. SCHEMA INITIALIZATION
-- ============================================================================
CREATE SCHEMA ctrl;    -- Batch markers, lease control, state machines
GO
CREATE SCHEMA stg;     -- Unindexed landing heaps, DLQ exception quarantine
GO
CREATE SCHEMA archive; -- Curated 3NF historical tables, SCD Type 2 dimensions
GO
CREATE SCHEMA dq;      -- Data quality findings, validation triage logs
GO
CREATE SCHEMA meta;    -- Picklists, BCUI labels, OData $metadata catalog
GO
CREATE SCHEMA recon;   -- 5-Pillar reconciliation results and audit scores
GO

-- ============================================================================
-- 2. CONTROL SCHEMA (ctrl)
-- ============================================================================
CREATE TABLE ctrl.BatchMaster (
    batch_id INT IDENTITY(1,1) PRIMARY KEY,
    entity_name VARCHAR(128) NOT NULL,
    slice_key VARCHAR(128) NOT NULL,
    status VARCHAR(32) NOT NULL DEFAULT 'PENDING',
    source_count INT NULL,
    landed_count INT NULL,
    promoted_count INT NULL,
    dlq_count INT NULL DEFAULT 0,
    started_at DATETIME2(3) NOT NULL DEFAULT SYSUTCDATETIME(),
    completed_at DATETIME2(3) NULL,
    error_message NVARCHAR(MAX) NULL,
    -- The 9-stage automaton (Scenario C), the transient TRIAGING state, and FAILED
    CONSTRAINT CK_Batch_Status CHECK (status IN ('PENDING','LEASED','EXTRACTING','LANDED','TRIAGING','TRIAGED',
                                                 'PROMOTED','RECONCILED','COMMITTED','COMPLETED','FAILED'))
);
CREATE NONCLUSTERED INDEX IX_BatchMaster_EntityStatus ON ctrl.BatchMaster(entity_name, status);

CREATE TABLE ctrl.EntityLease (
    entity_name VARCHAR(128) NOT NULL,
    slice_key VARCHAR(128) NOT NULL,
    worker_id VARCHAR(128) NOT NULL,
    lease_granted DATETIME2(3) NOT NULL DEFAULT SYSUTCDATETIME(),
    lease_expiry DATETIME2(3) NOT NULL,
    status VARCHAR(32) NOT NULL DEFAULT 'ACTIVE',
    PRIMARY KEY (entity_name, slice_key)
);

CREATE TABLE ctrl.ExtractPlan (
    plan_id INT IDENTITY(1,1) PRIMARY KEY,
    entity_name VARCHAR(128) NOT NULL,
    slice_key VARCHAR(128) NOT NULL,
    filter_clause NVARCHAR(512) NOT NULL,
    status VARCHAR(32) NOT NULL DEFAULT 'PENDING',
    retry_count INT NOT NULL DEFAULT 0,
    last_error NVARCHAR(MAX) NULL
);

-- ============================================================================
-- 3. STAGING SCHEMA (stg)
-- ============================================================================
CREATE TABLE stg.EmpJob_landing (
    _row_id BIGINT IDENTITY(1,1),
    batch_id INT NOT NULL,
    _ingested_at DATETIME2(3) NOT NULL DEFAULT SYSUTCDATETIME(),
    userId NVARCHAR(MAX) NULL,
    startDate NVARCHAR(MAX) NULL,
    seqNumber NVARCHAR(MAX) NULL,
    endDate NVARCHAR(MAX) NULL,
    company NVARCHAR(MAX) NULL,
    department NVARCHAR(MAX) NULL,
    division NVARCHAR(MAX) NULL,
    jobCode NVARCHAR(MAX) NULL,
    eventReason NVARCHAR(MAX) NULL,
    emplStatus NVARCHAR(MAX) NULL,
    customString7 NVARCHAR(MAX) NULL
    -- Intentionally unindexed heap for maximum ingestion throughput
);

CREATE TABLE stg.MigrationExceptions (
    exception_id BIGINT IDENTITY(1,1) PRIMARY KEY,
    batch_id INT NOT NULL,
    entity_name VARCHAR(128) NOT NULL,
    source_record_id VARCHAR(128) NULL,
    raw_payload NVARCHAR(MAX) NOT NULL,
    error_code VARCHAR(64) NOT NULL,
    error_message NVARCHAR(1024) NOT NULL,
    resolution_status VARCHAR(32) NOT NULL DEFAULT 'NEW',
    reprocessed_flag BIT NOT NULL DEFAULT 0,
    created_at DATETIME2(3) NOT NULL DEFAULT SYSUTCDATETIME()
);

-- ============================================================================
-- 4. CURATED 3NF ARCHIVE SCHEMA (archive)
-- ============================================================================
CREATE TABLE archive.Person (
    person_id BIGINT IDENTITY(1,1) PRIMARY KEY,
    source_person_id VARCHAR(64) NOT NULL UNIQUE,
    first_name NVARCHAR(128) NULL,
    last_name NVARCHAR(128) NOT NULL,
    date_of_birth DATE NULL,
    gender_code VARCHAR(32) NULL,
    gender_label NVARCHAR(128) NULL,
    created_at DATETIME2(3) NOT NULL DEFAULT SYSUTCDATETIME()
);

CREATE TABLE archive.Department (
    department_id INT IDENTITY(1,1) PRIMARY KEY,
    department_code VARCHAR(64) NOT NULL UNIQUE,
    department_name NVARCHAR(256) NOT NULL,
    is_active BIT NOT NULL DEFAULT 1
);

CREATE TABLE archive.Division (
    division_id INT IDENTITY(1,1) PRIMARY KEY,
    division_code VARCHAR(64) NOT NULL UNIQUE,
    division_name NVARCHAR(256) NOT NULL,
    is_active BIT NOT NULL DEFAULT 1
);

CREATE TABLE archive.Employment (
    employment_id BIGINT IDENTITY(1,1) PRIMARY KEY,
    person_id BIGINT NOT NULL FOREIGN KEY REFERENCES archive.Person(person_id),
    user_id VARCHAR(64) NOT NULL UNIQUE,
    original_start_date DATE NOT NULL,
    termination_date DATE NULL,
    is_active BIT NOT NULL,
    created_at DATETIME2(3) NOT NULL DEFAULT SYSUTCDATETIME()
);

CREATE TABLE archive.EmploymentHistory (
    job_history_id BIGINT IDENTITY(1,1) PRIMARY KEY,
    employment_id BIGINT NOT NULL FOREIGN KEY REFERENCES archive.Employment(employment_id),
    seq_number INT NOT NULL DEFAULT 1,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    valid_from DATETIME2(0) NOT NULL,
    valid_to DATETIME2(0) NOT NULL,
    is_current BIT NOT NULL,
    company_code VARCHAR(64) NOT NULL,
    company_name NVARCHAR(256) NULL,
    department_code VARCHAR(64) NOT NULL,
    department_name NVARCHAR(256) NULL,
    job_code VARCHAR(64) NOT NULL,
    job_title NVARCHAR(256) NULL,
    event_reason VARCHAR(64) NULL,
    union_status_code VARCHAR(64) NULL,
    union_status_label NVARCHAR(256) NULL,
    is_intraday_superseded BIT NOT NULL DEFAULT 0,  -- a later seqNumber on the same date owns the timeline
    _batch_id INT NOT NULL,
    created_at DATETIME2(3) NOT NULL DEFAULT SYSUTCDATETIME(),
    CONSTRAINT UQ_EmpHist_BusinessKey UNIQUE (employment_id, start_date, seq_number)
);
CREATE NONCLUSTERED INDEX IX_EmpHist_EmpValidity ON archive.EmploymentHistory(employment_id, valid_from, valid_to);

CREATE TABLE archive.CompensationHistory (
    comp_history_id BIGINT IDENTITY(1,1) PRIMARY KEY,
    employment_id BIGINT NOT NULL FOREIGN KEY REFERENCES archive.Employment(employment_id),
    start_date DATE NOT NULL,
    pay_component VARCHAR(64) NOT NULL,
    amount DECIMAL(19,4) NOT NULL,          -- exact decimal, never FLOAT: Pillar 2 is cent-for-cent
    currency_code CHAR(3) NOT NULL,
    frequency_code VARCHAR(32) NULL,
    _batch_id INT NOT NULL,
    created_at DATETIME2(3) NOT NULL DEFAULT SYSUTCDATETIME(),
    CONSTRAINT UQ_CompHist_BusinessKey UNIQUE (employment_id, start_date, pay_component, currency_code)
);

CREATE TABLE archive.EmployeeAttachment (
    attachment_id BIGINT PRIMARY KEY,
    user_id VARCHAR(64) NOT NULL,
    file_name NVARCHAR(256) NOT NULL,
    file_extension VARCHAR(32) NOT NULL,
    file_size_bytes BIGINT NOT NULL,
    content_type VARCHAR(128) NOT NULL,
    sha256_hash CHAR(64) NOT NULL,          -- computed in flight by the streaming worker
    source_sha256 CHAR(64) NOT NULL,        -- declared by SAP for the same file (Pillar 5 compares the two)
    blob_storage_url NVARCHAR(1024) NOT NULL,
    access_tier VARCHAR(32) NOT NULL DEFAULT 'Cool',
    ingestion_status VARCHAR(32) NOT NULL DEFAULT 'COMMITTED',
    uploaded_at DATETIME2(3) NOT NULL DEFAULT SYSUTCDATETIME()
);

-- ============================================================================
-- 5. METADATA & RECONCILIATION SCHEMAS (meta, recon)
-- ============================================================================
CREATE TABLE meta.BcuiFieldMapping (
    mapping_id INT IDENTITY(1,1) PRIMARY KEY,
    entity_name VARCHAR(128) NOT NULL,
    technical_field VARCHAR(128) NOT NULL,
    business_label NVARCHAR(256) NOT NULL,
    data_type VARCHAR(64) NOT NULL,
    is_custom BIT NOT NULL,
    imported_at DATETIME2(3) NOT NULL DEFAULT SYSUTCDATETIME(),
    CONSTRAINT UQ_Bcui_Field UNIQUE (entity_name, technical_field)
);

CREATE TABLE meta.PicklistOption (
    option_id BIGINT PRIMARY KEY,
    picklist_id VARCHAR(128) NOT NULL,
    external_code VARCHAR(128) NOT NULL,
    locale VARCHAR(16) NOT NULL DEFAULT 'en_US',
    label NVARCHAR(512) NOT NULL,
    status VARCHAR(32) NOT NULL,
    updated_at DATETIME2(3) NOT NULL DEFAULT SYSUTCDATETIME()
);

-- Control totals captured from SAP at extraction time ($count per entity, sum per currency,
-- attachment count). The gate compares the archive against these, never against itself.
CREATE TABLE recon.SourceControlTotal (
    cycle_name VARCHAR(64) NOT NULL,
    entity_name VARCHAR(128) NOT NULL,
    measure VARCHAR(64) NOT NULL,           -- 'ROW_COUNT', 'AMOUNT_SUM:<currency>', 'ATTACHMENT_COUNT'
    value DECIMAL(28,4) NOT NULL,
    captured_at DATETIME2(3) NOT NULL DEFAULT SYSUTCDATETIME(),
    CONSTRAINT PK_SourceControlTotal PRIMARY KEY (cycle_name, entity_name, measure)
);

CREATE TABLE recon.CheckResult (
    check_id INT IDENTITY(1,1) PRIMARY KEY,
    cycle_name VARCHAR(64) NOT NULL,
    pillar_number INT NOT NULL,
    pillar_name VARCHAR(128) NOT NULL,
    entity_name VARCHAR(128) NOT NULL,
    expected_value NVARCHAR(128) NOT NULL,
    actual_value NVARCHAR(128) NOT NULL,
    variance NVARCHAR(128) NOT NULL,
    status VARCHAR(32) NOT NULL,
    evaluated_at DATETIME2(3) NOT NULL DEFAULT SYSUTCDATETIME()
);
GO

7.5 Date Parsing Function & Surrogate Key Seeding (-1 UNKNOWN)

Step 1: OData Date Parsing Scalar Function

SAP SuccessFactors returns dates in either ISO strings (2021-03-15) or Microsoft JSON Epoch format (/Date(1577836800000)/). Deploy this deterministic parsing function:

CREATE OR ALTER FUNCTION stg.fn_ParseODataDate (@RawVal NVARCHAR(MAX))
RETURNS DATE
WITH SCHEMABINDING
AS
BEGIN
    IF @RawVal IS NULL OR LTRIM(RTRIM(@RawVal)) = ''
        RETURN NULL;

    -- Case 1: Microsoft Epoch milliseconds /Date(1577836800000+0000)/
    IF @RawVal LIKE '/Date(%)/'
    BEGIN
        DECLARE @EpochStr VARCHAR(64) = SUBSTRING(@RawVal, 7, CHARINDEX(')', @RawVal) - 7);
        -- Strip a timezone offset, searching from position 2: a leading '-' is the sign of a
        -- pre-1970 epoch, not an offset (treating it as one turns every pre-1970 date into 1970-01-01).
        DECLARE @OffsetIdx INT = PATINDEX('%[+-]%', SUBSTRING(@EpochStr, 2, 64));
        IF @OffsetIdx > 0
            SET @EpochStr = SUBSTRING(@EpochStr, 1, @OffsetIdx);

        DECLARE @EpochMs BIGINT = TRY_CAST(@EpochStr AS BIGINT);
        IF @EpochMs IS NULL RETURN NULL;

        -- Add whole days (floored), not seconds: seconds overflow INT after 2038, which
        -- would fail every row carrying SAP's 9999-12-31 end date.
        DECLARE @Parsed DATE = DATEADD(DAY, CAST(FLOOR(@EpochMs / 86400000.0) AS INT), CAST('1970-01-01' AS DATE));
        -- Pre-1900 values are SAP placeholders (/Date(-62135596800000)/ = 0001-01-01): send to the DLQ
        RETURN CASE WHEN @Parsed < '1900-01-01' THEN NULL ELSE @Parsed END;
    END

    -- Case 2: Standard ISO Date (YYYY-MM-DD)
    DECLARE @Iso DATE = TRY_CONVERT(DATE, LEFT(@RawVal, 10), 23);
    RETURN CASE WHEN @Iso < '1900-01-01' THEN NULL ELSE @Iso END;
END;
GO

Step 2: Seed Surrogate Dimensions with -1 UNKNOWN

Legacy data from 2004 frequently references decommissioned cost centers or departments. To prevent foreign key rejections while guaranteeing audit traceability, seed all dimensional tables:

-- Seed Unknown Dimension Row in Department Master
SET IDENTITY_INSERT archive.Department ON;
INSERT INTO archive.Department (department_id, department_code, department_name, is_active)
VALUES (-1, 'UNKNOWN', 'Legacy Unmapped Department', 0);
SET IDENTITY_INSERT archive.Department OFF;

-- Seed Unknown Dimension Row in Division Master
SET IDENTITY_INSERT archive.Division ON;
INSERT INTO archive.Division (division_id, division_code, division_name, is_active)
VALUES (-1, 'UNKNOWN', 'Legacy Unmapped Division', 0);
SET IDENTITY_INSERT archive.Division OFF;
GO

7.6 Python 16MB In-Flight Binary Streaming Worker Daemon

Deploy this zero-disk streaming worker into Azure Container Apps to stream attachments directly from SAP Door 3 to Azure Blob Storage:

#!/usr/bin/env python3
"""
tools/worker/attachment_streamer.py
Streams binary documents directly from SAP Door 3 into Azure Blob Storage WORM Vault.
Uses 16MB in-memory chunking with in-flight SHA-256 calculation and zero disk I/O.
"""
import os, hashlib, base64, requests, pyodbc
from azure.storage.blob import BlobServiceClient

CHUNK_SIZE = 16 * 1024 * 1024  # 16 MB In-Memory Chunk
BLOB_CONN_STR = os.environ["AZURE_STORAGE_CONNECTION_STRING"]
SQL_CONN_STR = os.environ["SQL_CONNECTION_STRING"]

def stream_attachment(attachment_id: int, user_id: str, file_name: str, oauth_token: str):
    blob_service_client = BlobServiceClient.from_connection_string(BLOB_CONN_STR)
    blob_name = f"{user_id}/{attachment_id}_{file_name}"
    blob_client = blob_service_client.get_blob_client(container="attachments", blob=blob_name)

    sap_url = f"https://api12.successfactors.eu/odata/v2/Attachment({attachment_id})/$value"
    headers = {"Authorization": f"Bearer {oauth_token}"}

    sha256 = hashlib.sha256()
    block_ids = []
    total_bytes = 0

    with requests.get(sap_url, headers=headers, stream=True, timeout=60) as resp:
        resp.raise_for_status()
        content_type = resp.headers.get("Content-Type", "application/octet-stream")
        block_index = 0

        for chunk in resp.iter_content(chunk_size=CHUNK_SIZE):
            if chunk:
                sha256.update(chunk)
                total_bytes += len(chunk)
                block_id = base64.b64encode(f"block-{block_index:06d}".encode()).decode()
                blob_client.stage_block(block_id=block_id, data=chunk)
                block_ids.append(block_id)
                block_index += 1

        # Commit all staged blocks atomically into Azure Blob Storage
        blob_client.commit_block_list(block_ids)

    calculated_hash = sha256.hexdigest()
    blob_url = blob_client.url

    # Record permanent metadata into Azure SQL Database
    with pyodbc.connect(SQL_CONN_STR) as conn:
        with conn.cursor() as cursor:
            cursor.execute("""
                INSERT INTO archive.EmployeeAttachment (
                    attachment_id, user_id, file_name, file_extension,
                    file_size_bytes, content_type, sha256_hash, blob_storage_url, ingestion_status
                ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, 'COMMITTED')
            """, attachment_id, user_id, file_name, file_name.split('.')[-1],
                 total_bytes, content_type, calculated_hash, blob_url)
            conn.commit()

    print(f"COMMITTED: {file_name} ({total_bytes} bytes) -> {blob_url} [SHA-256: {calculated_hash}]")

if __name__ == "__main__":
    print("Attachment Streaming Worker Ready.")

7.7 Staging-to-Archive Transformation & SCD-2 Promotion Logic

Deploy the master stored procedure that transforms unindexed stg.EmpJob_landing heaps into clean, temporally continuous Slowly Changing Dimension Type 2 (SCD-2) slices:

CREATE OR ALTER PROCEDURE curated.usp_Promote_EmpJob
    @BatchId INT
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    BEGIN TRANSACTION;

    -- 1. Verify Batch State
    IF NOT EXISTS (SELECT 1 FROM ctrl.BatchMaster WHERE batch_id = @BatchId AND status = 'LANDED')
    BEGIN
        RAISERROR('Batch %d is not in LANDED status.', 16, 1, @BatchId);
        ROLLBACK TRANSACTION;
        RETURN;
    END

    UPDATE ctrl.BatchMaster SET status = 'TRIAGING' WHERE batch_id = @BatchId;

    -- 2. Triage & Quarantine into the DLQ. Every row that will not be promoted lands here
    --    with a reason: nothing is allowed to disappear silently.
    ;WITH Classified AS (
        SELECT
            l.userId, l.startDate, l.seqNumber, l.company,
            ROW_NUMBER() OVER (PARTITION BY l.userId, l.startDate, l.seqNumber ORDER BY l._row_id) AS dup_rank,
            emp.employment_id
        FROM stg.EmpJob_landing l
        LEFT JOIN archive.Employment emp ON emp.user_id = l.userId
        WHERE l.batch_id = @BatchId
    )
    INSERT INTO stg.MigrationExceptions (batch_id, entity_name, source_record_id, raw_payload, error_code, error_message)
    SELECT
        @BatchId,
        'EmpJob',
        ISNULL(userId, 'UNKNOWN'),
        CONCAT('userId=', userId, '|startDate=', startDate, '|seqNumber=', seqNumber, '|company=', company),
        CASE
            WHEN userId IS NULL OR LTRIM(RTRIM(userId)) = '' THEN 'ERR_NULL_USER_ID'
            WHEN stg.fn_ParseODataDate(startDate) IS NULL THEN 'ERR_INVALID_START_DATE'
            WHEN TRY_CAST(ISNULL(seqNumber, '1') AS INT) IS NULL THEN 'ERR_INVALID_SEQ'
            WHEN dup_rank > 1 THEN 'ERR_DUPLICATE_KEY'
            ELSE 'ERR_MISSING_PARENT'   -- Employment (Wave 3) not loaded yet: reprocess once it lands
        END,
        'Failed baseline schema validation rules'
    FROM Classified
    WHERE userId IS NULL OR LTRIM(RTRIM(userId)) = ''
       OR stg.fn_ParseODataDate(startDate) IS NULL
       OR TRY_CAST(ISNULL(seqNumber, '1') AS INT) IS NULL
       OR dup_rank > 1
       OR employment_id IS NULL;

    -- 3. Upsert this batch's slices on the business key (employee, start date, sequence).
    --    Validity columns are placeholders here; step 4 derives them from the FULL history.
    ;WITH ParsedSource AS (
        SELECT
            emp.employment_id,
            stg.fn_ParseODataDate(l.startDate) AS start_date,
            TRY_CAST(ISNULL(l.seqNumber, '1') AS INT) AS seq_number,
            l.company, l.department, l.jobCode, l.eventReason, l.customString7,
            ROW_NUMBER() OVER (PARTITION BY l.userId, l.startDate, l.seqNumber ORDER BY l._row_id) AS dup_rank
        FROM stg.EmpJob_landing l
        INNER JOIN archive.Employment emp ON l.userId = emp.user_id  -- rows without a parent went to the DLQ
        WHERE l.batch_id = @BatchId
          AND stg.fn_ParseODataDate(l.startDate) IS NOT NULL
          AND TRY_CAST(ISNULL(l.seqNumber, '1') AS INT) IS NOT NULL
    )
    MERGE INTO archive.EmploymentHistory AS target
    USING (
        SELECT
            s.employment_id, s.seq_number, s.start_date,
            s.company AS company_code,
            COALESCE(d.department_code, '-1') AS department_code,
            s.jobCode AS job_code,
            s.eventReason AS event_reason,
            s.customString7 AS union_status_code,
            p_opt.label AS union_status_label
        FROM ParsedSource s
        LEFT JOIN archive.Department d ON s.department = d.department_code
        LEFT JOIN meta.PicklistOption p_opt ON s.customString7 = p_opt.external_code AND p_opt.picklist_id = 'UNION_STATUS'
        WHERE s.dup_rank = 1
    ) AS source
    ON target.employment_id = source.employment_id
   AND target.start_date = source.start_date
   AND target.seq_number = source.seq_number
    WHEN MATCHED THEN
        UPDATE SET
            target.company_code = source.company_code,
            target.department_code = source.department_code,
            target.job_code = source.job_code,
            target.event_reason = source.event_reason,
            target.union_status_code = source.union_status_code,
            target.union_status_label = source.union_status_label,
            target._batch_id = @BatchId
    WHEN NOT MATCHED BY TARGET THEN
        INSERT (
            employment_id, seq_number, start_date, end_date, valid_from, valid_to,
            is_current, company_code, department_code, job_code, event_reason,
            union_status_code, union_status_label, _batch_id
        ) VALUES (
            source.employment_id, source.seq_number, source.start_date, '9999-12-31',
            CAST(source.start_date AS DATETIME2(0)), '9999-12-31 23:59:59',
            1, source.company_code, source.department_code, source.job_code, source.event_reason,
            source.union_status_code, source.union_status_label, @BatchId
        );

    DECLARE @PromotedCount INT = @@ROWCOUNT;

    -- 4. Rebuild SCD-2 validity windows with LEAD() over each employee's ENTIRE history, not
    --    just this batch: a delta row arriving months later must close the slice an earlier
    --    batch left open. When several events share one start date, only the highest
    --    seqNumber owns the timeline; the others are kept for audit as zero-length slices.
    ;WITH Ordered AS (
        SELECT
            h.job_history_id,
            h.start_date,
            LEAD(h.start_date) OVER (PARTITION BY h.employment_id ORDER BY h.start_date, h.seq_number) AS next_start
        FROM archive.EmploymentHistory h
        WHERE h.employment_id IN (
            SELECT emp.employment_id FROM stg.EmpJob_landing l
            JOIN archive.Employment emp ON emp.user_id = l.userId
            WHERE l.batch_id = @BatchId)
    )
    UPDATE h SET
        is_intraday_superseded = CASE WHEN o.next_start = o.start_date THEN 1 ELSE 0 END,
        valid_from = CAST(o.start_date AS DATETIME2(0)),
        valid_to = CASE
                       WHEN o.next_start IS NULL THEN CAST('9999-12-31 23:59:59' AS DATETIME2(0))
                       WHEN o.next_start = o.start_date THEN CAST(o.start_date AS DATETIME2(0))
                       ELSE DATEADD(SECOND, -1, CAST(o.next_start AS DATETIME2(0)))
                   END,
        end_date = CASE
                       WHEN o.next_start IS NULL THEN CAST('9999-12-31' AS DATE)
                       WHEN o.next_start = o.start_date THEN o.start_date
                       ELSE DATEADD(DAY, -1, o.next_start)
                   END,
        is_current = CASE WHEN o.next_start IS NULL THEN 1 ELSE 0 END
    FROM archive.EmploymentHistory h
    JOIN Ordered o ON o.job_history_id = h.job_history_id;

    -- 5. Finalize Batch Accounting
    DECLARE @DlqCount INT = (SELECT COUNT(*) FROM stg.MigrationExceptions WHERE batch_id = @BatchId);

    UPDATE ctrl.BatchMaster
    SET status = 'PROMOTED',
        promoted_count = @PromotedCount,
        dlq_count = @DlqCount,
        completed_at = SYSUTCDATETIME()
    WHERE batch_id = @BatchId;

    COMMIT TRANSACTION;
    PRINT CONCAT('Batch ', @BatchId, ' successfully promoted. Promoted: ', @PromotedCount, ', DLQ: ', @DlqCount);
END;
GO

7.8 Control DB State Machine, Batch Leasing & Watermark Engine

Deploy stored procedures for atomic worker leasing and state transitions:

-- Atomic Lease Acquisition Procedure
CREATE OR ALTER PROCEDURE ctrl.usp_AcquireLease
    @EntityName VARCHAR(128),
    @WorkerId VARCHAR(128),
    @LeaseDurationMinutes INT = 30,
    @AssignedSliceKey VARCHAR(128) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    BEGIN TRANSACTION;

    -- Find next pending slice or expired active lease
    SELECT TOP (1) 
        @AssignedSliceKey = p.slice_key
    FROM ctrl.ExtractPlan p WITH (UPDLOCK, ROWLOCK)
    LEFT JOIN ctrl.EntityLease l ON p.entity_name = l.entity_name AND p.slice_key = l.slice_key
    WHERE p.entity_name = @EntityName
      AND (
          p.status = 'PENDING' 
          OR (p.status = 'IN_PROGRESS' AND l.lease_expiry < SYSUTCDATETIME())
      )
    ORDER BY p.plan_id ASC;

    IF @AssignedSliceKey IS NOT NULL
    BEGIN
        -- Upsert Active Lease
        MERGE INTO ctrl.EntityLease AS target
        USING (SELECT @EntityName AS entity_name, @AssignedSliceKey AS slice_key) AS source
        ON target.entity_name = source.entity_name AND target.slice_key = source.slice_key
        WHEN MATCHED THEN
            UPDATE SET 
                worker_id = @WorkerId,
                lease_granted = SYSUTCDATETIME(),
                lease_expiry = DATEADD(MINUTE, @LeaseDurationMinutes, SYSUTCDATETIME()),
                status = 'ACTIVE'
        WHEN NOT MATCHED THEN
            INSERT (entity_name, slice_key, worker_id, lease_granted, lease_expiry, status)
            VALUES (@EntityName, @AssignedSliceKey, @WorkerId, SYSUTCDATETIME(), DATEADD(MINUTE, @LeaseDurationMinutes, SYSUTCDATETIME()), 'ACTIVE');

        UPDATE ctrl.ExtractPlan
        SET status = 'IN_PROGRESS'
        WHERE entity_name = @EntityName AND slice_key = @AssignedSliceKey;
    END

    COMMIT TRANSACTION;
END;
GO

7.9 Central Log Analytics Workspace, Alerts & Live Operational Views

-- Live Operational Monitoring View
CREATE OR ALTER VIEW ctrl.vw_BatchMonitoring
AS
SELECT 
    b.batch_id,
    b.entity_name,
    b.slice_key,
    b.status,
    b.source_count,
    b.landed_count,
    b.promoted_count,
    b.dlq_count,
    DATEDIFF(SECOND, b.started_at, COALESCE(b.completed_at, SYSUTCDATETIME())) AS duration_seconds,
    CASE 
        WHEN b.landed_count > 0 THEN CAST((CAST(b.dlq_count AS FLOAT) / b.landed_count) * 100 AS DECIMAL(5,2))
        ELSE 0.00 
    END AS dlq_error_percentage,
    b.started_at,
    b.completed_at
FROM ctrl.BatchMaster b;
GO

-- Dead-Letter Queue Triage Summary View
CREATE OR ALTER VIEW ctrl.vw_DLQSummary
AS
SELECT 
    entity_name,
    error_code,
    resolution_status,
    COUNT(*) AS defect_count,
    MIN(created_at) AS earliest_defect,
    MAX(created_at) AS latest_defect
FROM stg.MigrationExceptions
GROUP BY entity_name, error_code, resolution_status;
GO

7.10 Automated 5-Pillar Reconciliation Suite & Digital Cutover Certification

Deploy the master reconciliation evaluation stored procedure:

CREATE OR ALTER PROCEDURE recon.usp_RunCutoverPillars
    @CycleName VARCHAR(64)
AS
BEGIN
    SET NOCOUNT ON;

    DELETE FROM recon.CheckResult WHERE cycle_name = @CycleName;

    -- PILLAR 1: Population Completeness. SF $count == promoted + quarantined, per entity.
    -- Expected comes from recon.SourceControlTotal (captured from SAP), not from the pipeline's own counters.
    INSERT INTO recon.CheckResult (cycle_name, pillar_number, pillar_name, entity_name, expected_value, actual_value, variance, status)
    SELECT @CycleName, 1, 'Population Completeness', 'EmpJob',
           CAST(src.expected AS VARCHAR(32)), CAST(tgt.actual AS VARCHAR(32)), CAST(tgt.actual - src.expected AS VARCHAR(32)),
           CASE WHEN tgt.actual = src.expected THEN 'PASS' ELSE 'FAIL' END
    FROM (SELECT CAST(value AS BIGINT) AS expected FROM recon.SourceControlTotal
          WHERE cycle_name = @CycleName AND entity_name = 'EmpJob' AND measure = 'ROW_COUNT') src
    CROSS JOIN (SELECT (SELECT COUNT_BIG(*) FROM archive.EmploymentHistory)
                     + (SELECT COUNT_BIG(*) FROM stg.MigrationExceptions WHERE entity_name = 'EmpJob') AS actual) tgt;

    -- PILLAR 2: Financial Exact Cents, per currency (a single cross-currency total can hide offsetting errors).
    INSERT INTO recon.CheckResult (cycle_name, pillar_number, pillar_name, entity_name, expected_value, actual_value, variance, status)
    SELECT @CycleName, 2, 'Financial Comp Parity', CONCAT('CompensationHistory:', COALESCE(src.currency, tgt.currency)),
           CAST(ISNULL(src.total, 0) AS VARCHAR(40)), CAST(ISNULL(tgt.total, 0) AS VARCHAR(40)),
           CAST(ISNULL(tgt.total, 0) - ISNULL(src.total, 0) AS VARCHAR(40)),
           CASE WHEN ISNULL(tgt.total, 0) = ISNULL(src.total, 0) THEN 'PASS' ELSE 'FAIL' END
    FROM (SELECT SUBSTRING(measure, 12, 3) AS currency, value AS total FROM recon.SourceControlTotal
          WHERE cycle_name = @CycleName AND entity_name = 'EmpCompensation' AND measure LIKE 'AMOUNT_SUM:%') src
    FULL JOIN (SELECT currency_code AS currency, SUM(amount) AS total FROM archive.CompensationHistory GROUP BY currency_code) tgt
      ON tgt.currency = src.currency;

    -- PILLAR 3: Referential Integrity. Every slice points at a real department (-1 UNKNOWN included).
    DECLARE @Orphans INT = (
        SELECT COUNT(*) FROM archive.EmploymentHistory h
        LEFT JOIN archive.Department d ON d.department_code = h.department_code
        WHERE d.department_code IS NULL AND h.department_code <> '-1');
    INSERT INTO recon.CheckResult (cycle_name, pillar_number, pillar_name, entity_name, expected_value, actual_value, variance, status)
    VALUES (@CycleName, 3, 'Referential Integrity', 'EmploymentHistory', '0', CAST(@Orphans AS VARCHAR(16)), CAST(@Orphans AS VARCHAR(16)),
            CASE WHEN @Orphans = 0 THEN 'PASS' ELSE 'FAIL' END);

    -- PILLAR 4: SCD-2 Timeline Continuity: no overlaps, no gaps, no inverted windows, exactly one current slice.
    DECLARE @Overlaps INT = (
        SELECT COUNT(*) FROM archive.EmploymentHistory a
        JOIN archive.EmploymentHistory b
          ON a.employment_id = b.employment_id AND a.job_history_id < b.job_history_id
         AND a.is_intraday_superseded = 0 AND b.is_intraday_superseded = 0
         AND a.valid_from <= b.valid_to AND a.valid_to >= b.valid_from);
    DECLARE @Gaps INT = (
        SELECT COUNT(*) FROM (
            SELECT valid_to, LEAD(valid_from) OVER (PARTITION BY employment_id ORDER BY start_date, seq_number) AS next_from
            FROM archive.EmploymentHistory WHERE is_intraday_superseded = 0) x
        WHERE next_from IS NOT NULL AND valid_to <> DATEADD(SECOND, -1, next_from));
    DECLARE @Inverted INT = (SELECT COUNT(*) FROM archive.EmploymentHistory WHERE valid_to < valid_from);
    DECLARE @BadCurrent INT = (
        SELECT COUNT(*) FROM (SELECT employment_id FROM archive.EmploymentHistory
                              GROUP BY employment_id HAVING SUM(CAST(is_current AS INT)) <> 1) x);
    INSERT INTO recon.CheckResult (cycle_name, pillar_number, pillar_name, entity_name, expected_value, actual_value, variance, status)
    VALUES (@CycleName, 4, 'SCD-2 Timeline Integrity', 'EmploymentHistory', '0',
            CONCAT('overlaps=', @Overlaps, ' gaps=', @Gaps, ' inverted=', @Inverted, ' bad_current=', @BadCurrent),
            CAST(@Overlaps + @Gaps + @Inverted + @BadCurrent AS VARCHAR(16)),
            CASE WHEN @Overlaps + @Gaps + @Inverted + @BadCurrent = 0 THEN 'PASS' ELSE 'FAIL' END);

    -- PILLAR 5: Binary Hash Parity. Hash computed in flight == hash SAP declared, and every
    -- source attachment is either stored or quarantined.
    DECLARE @Mismatched INT = (SELECT COUNT(*) FROM archive.EmployeeAttachment WHERE sha256_hash <> source_sha256);
    DECLARE @Accounted BIGINT = (SELECT COUNT_BIG(*) FROM archive.EmployeeAttachment)
                              + (SELECT COUNT_BIG(*) FROM stg.MigrationExceptions WHERE entity_name = 'Attachment');
    DECLARE @SourceAttachments BIGINT = (SELECT CAST(value AS BIGINT) FROM recon.SourceControlTotal
                                         WHERE cycle_name = @CycleName AND entity_name = 'Attachment' AND measure = 'ATTACHMENT_COUNT');
    INSERT INTO recon.CheckResult (cycle_name, pillar_number, pillar_name, entity_name, expected_value, actual_value, variance, status)
    VALUES (@CycleName, 5, 'Cryptographic Attachment Parity', 'EmployeeAttachment',
            CAST(@SourceAttachments AS VARCHAR(32)), CONCAT(@Accounted, ' accounted, ', @Mismatched, ' hash mismatches'),
            CAST(@Accounted - ISNULL(@SourceAttachments, -1) AS VARCHAR(32)),
            CASE WHEN @Mismatched = 0 AND @Accounted = @SourceAttachments THEN 'PASS' ELSE 'FAIL' END);

    -- A pillar with no source control total fails rather than passing vacuously
    IF NOT EXISTS (SELECT 1 FROM recon.CheckResult WHERE cycle_name = @CycleName AND pillar_number = 1)
        INSERT INTO recon.CheckResult (cycle_name, pillar_number, pillar_name, entity_name, expected_value, actual_value, variance, status)
        VALUES (@CycleName, 1, 'Population Completeness', 'EmpJob', 'missing', 'n/a', 'n/a', 'FAIL');

    SELECT * FROM recon.CheckResult WHERE cycle_name = @CycleName ORDER BY pillar_number, entity_name;
END;
GO

Section 088. The CData Ingestion Specialist Playbook

This playbook provides all configuration settings, Linux commands, JVM optimization parameters, and query flags required by the CData Ingestion Specialist Squad.

8.1 VM Sizing, Host Topology & OS Prerequisites

The extraction engine runs on a dedicated Ubuntu virtual machine placed in the private subnet:

8.2 Locking JVM Heap Memory (-Xms8g -Xmx8g -XX:+UseG1GC)

Configure JVM environment variables in /opt/cdatasync/service.env:

# Create and edit CData service environment file
sudo tee /opt/cdatasync/service.env > /dev/null << 'EOF'
# JVM HEAP LOCK & G1GC OPTIMIZATION
JAVA_HOME=/usr/lib/jvm/java-17-openjdk-amd64
JAVA_OPTS="-Xms8g -Xmx8g -XX:+UseG1GC -XX:InitiatingHeapOccupancyPercent=45 -XX:G1ReservePercent=15 -XX:MaxGCPauseMillis=200 -Dfile.encoding=UTF-8 -Dsun.jnu.encoding=UTF-8"
EOF

8.3 CData Sync Daemon Installation & Service Hardening

Execute the following bash commands via Azure Bastion to deploy and register CData Sync as a hardened systemd daemon:

# 1. Install OpenJDK 17 and utility tools
sudo apt-get update && sudo apt-get install -y openjdk-17-jre-headless unzip curl jq

# 2. Extract CData Sync into /opt/cdatasync
sudo mkdir -p /opt/cdatasync
sudo unzip /tmp/cdata-sync.zip -d /opt/cdatasync
sudo chown -R azureuser:azureuser /opt/cdatasync

# 3. Create hardened systemd unit file
sudo tee /etc/systemd/system/cdatasync.service > /dev/null << 'EOF'
[Unit]
Description=CData Sync Ingestion Daemon (SF to Azure Archive)
After=network.target

[Service]
Type=simple
User=azureuser
Group=azureuser
WorkingDirectory=/opt/cdatasync
EnvironmentFile=/opt/cdatasync/service.env
ExecStart=/usr/bin/java $JAVA_OPTS -jar /opt/cdatasync/sync.jar -port 8181
Restart=always
RestartSec=10
LimitNOFILE=65536

[Install]
WantedBy=multi-user.target
EOF

# 4. Reload systemd, enable and start service
sudo systemctl daemon-reload
sudo systemctl enable cdatasync
sudo systemctl start cdatasync

# 5. Verify local service health
curl -s -k http://localhost:8181/api/health | jq .

8.4 SAP SuccessFactors Source Connection Profile Configuration

Configure the SAP SuccessFactors OData connector profile in CData Sync:

[SAP_SuccessFactors_Source]
AuthScheme = "OAuth2SAMLBearer"
URL = "https://api12.successfactors.eu/odata/v2"
CompanyId = "COMPANY_PROD"
User = "SF_MIG_TECH_USER"
OAuthClientId = "Yzg2NTQxZGY1..."
OAuthJWTCert = "/opt/cdatasync/certs/sfmig-oauth-key.pem"
OAuthJWTCertType = "PEMKEY_BLOB"
OAuthJWTSubject = "*"
OAuthTokenUrl = "https://api12.successfactors.eu/oauth/token"
OAuthJWTExpiry = "3600"

# Extraction Performance & Stability Flags
PageSize = "1000"
DataFormat = "JSON"
UseSkipToken = "true"
QueryPassthrough = "true"
Timeout = "300"
MaxRetries = "5"
RetryWaitTime = "5"

8.5 Azure SQL Database Destination Connection Profile

Configure the Azure SQL Database destination connection over Private Link TDS:

[Azure_SQL_Destination]
Server = "sql-sf-archive-prod.privatelink.database.windows.net"
Port = "1433"
Database = "sqldb-sf-archive-prod"
Schema = "stg"
Authentication = "ActiveDirectoryManagedIdentity"
# Alternative for Service Principal:
# Authentication = "ActiveDirectoryPassword"
# User = "sqladmin@company.com"

# Connection Performance & Security
Encrypt = "true"
TrustServerCertificate = "false"
ConnectionTimeout = "60"
CommandTimeout = "600"
BulkCopyBatchSize = "5000"
UseBulkCopy = "true"

8.6 Critical Job Configuration Parameters (Effective Dates & Leavers)

To guarantee that 100% of historical records are extracted without truncation, every CData Sync replication job must have the following parameters configured:

Annual Slicing Syntax for Massive Tables (EmpJob, EmpCompensation)

To prevent network timeouts on multi-million row entities, configure sliced replication jobs by calendar year:

-- CData Sync Job Query Definition for EmpJob 2018 Slice
SELECT 
    userId,
    startDate,
    seqNumber,
    endDate,
    company,
    department,
    division,
    jobCode,
    eventReason,
    emplStatus,
    customString7
FROM EmpJob
WHERE startDate >= '2018-01-01' 
  AND startDate < '2019-01-01'
  /* MANDATORY QUERY PASSTHROUGH PROPERTIES */
  OPTION (CustomProperties = 'fromDate=1900-01-01;toDate=9999-12-31;userNav/status in (''t'',''f'',''T'',''F'',''e'',''d'')');

8.7 Conductor Automation via CData REST API

The Azure Container Apps Conductor orchestrates CData Sync replication jobs programmatically via its authenticated REST API:

# 1. Trigger Replication Job for EmpJob_2018
curl -X POST -k https://localhost:8181/api/jobs/EmpJob_2018/run \
  -H "Authorization: Bearer $CDATA_API_TOKEN" \
  -H "Content-Type: application/json"

# 2. Poll Job Execution Status
curl -s -k https://localhost:8181/api/jobs/EmpJob_2018/status \
  -H "Authorization: Bearer $CDATA_API_TOKEN" | jq .

# Sample JSON Response:
# {
#   "jobName": "EmpJob_2018",
#   "status": "COMPLETED",
#   "rowsCopied": 142850,
#   "startTime": "2026-11-15T02:00:00Z",
#   "endTime": "2026-11-15T02:18:42Z",
#   "errors": 0
# }

Part IIICHRONOLOGICAL 13-PHASE CROSS-FUNCTIONAL EXECUTION RUNBOOK (ALL 3 TEAMS ALIGNED)

This part aligns the SAP SuccessFactors SME Squad, the CData Ingestion Squad, and the Azure Data Engineering & Infrastructure Squad into a single, cohesive, chronological 13-phase delivery roadmap spanning 135 calendar days from project inception to permanent SAP decommissioning.

Section 099. Master 13-Phase Cross-Functional Execution Roadmap (Day 1 to Day 135)

Phase 1: Project Initiation & Tenant Discovery (Days 1–5)

• Objective: Discover, catalog, and document all data models, custom objects, and picklist dictionaries directly from the source SAP tenant without making assumptions.
• SAP Team Actions:
  • Execute cURL or browser extraction to capture source OData $metadata catalog (https://api12.successfactors.eu/odata/v2/$metadata).
  • Navigate to Admin Center → Manage Business Configuration and export BCUI_DataModel.xml.
  • Navigate to Admin Center → Picklist Center and export all Legacy and MDF Picklists.
  • Deliver XML and CSV exports to the shared Git repository under config/metadata/.
• CData Team Actions:
  • Review the exported $metadata XML to identify entity relationships, multi-part keys, and date formats.
  • Catalog all entities with large transaction volumes requiring annual slicing (EmpJob, EmpCompensation).
• Azure Data Engineering & Infrastructure Team Actions:
  • Initialize the Git repository structure (infra/, sql/, tools/, config/).
  • Ingest $metadata into a local schema parser to generate target SQL table column definitions.
• Exit Gate: Complete baseline metadata catalog published; zero ambiguity regarding custom fields or picklist mappings.

Phase 2: Cloud Foundation & Network Perimeter Setup (Days 6–12)

• Objective: Stand up the hardened Azure Virtual Network, subnets, static NAT Gateway, and Bastion host with zero public ingress.
• SAP Team Actions:
  • Await static NAT egress IP from Azure Infra.
  • Verify that internal corporate change tickets are submitted to allowlist Azure egress in corporate firewalls.
• CData Team Actions:
  • Review VM subnet sizing (snet-cdata /24) and confirm host networking requirements.
• Azure Data Engineering & Infrastructure Team Actions:
  • Deploy Resource Group RG-HR-SF-ARCHIVE-PROD in West Europe.
  • Provision VNET-SF-ARCHIVE (10.50.0.0/16) and all 5 dedicated subnets (snet-cdata, snet-private-endpoints, AzureBastionSubnet, snet-aca, snet-management).
  • Allocate PIP-NAT-SF-ARCHIVE and deploy nat-sf-archive-prod with 64,000 SNAT ports.
  • Deploy Azure Bastion Host bas-sf-archive-prod.
  • Retrieve static public IP and formally transmit to SAP SME.
• Exit Gate: Virtual Network online; Bastion host accessible; static egress IP generated and verified.

Phase 3: Key Vault, RSA 2048 & SAP OAuth Handshake (Days 13–18)

• Objective: Configure passwordless, certificate-based OAuth 2.0 SAML Bearer assertion authentication between Azure and SAP.
• SAP Team Actions:
  • Receive sfmig-oauth-cert.cer public certificate from Azure Infra.
  • Navigate to Admin Center → Manage OAuth2 Client Applications and register AZURE_MIGRATION_CDATA.
  • Paste certificate contents and generate API Key (Client ID).
  • Navigate to Password & Login Policy → Set IP Restrictions and whitelist PIP-NAT-SF-ARCHIVE.
• CData Team Actions:
  • Place RSA private key on vm-sf-cdata in secure directory (/opt/cdatasync/certs/) with chmod 400.
  • Prepare CData connection string with AuthScheme=OAuth2SAMLBearer.
• Azure Data Engineering & Infrastructure Team Actions:
  • Provision Azure Key Vault kv-sf-archive-prod-01 with Private Endpoint.
  • Generate RSA 2048-bit keypair via OpenSSL; store private key in Key Vault secret sfmig-oauth-key.
  • Execute smoke test Python script from Jumpbox to verify that SAP token endpoint returns HTTP 200 with valid bearer token.
• Exit Gate: Automated script exchanges SAML assertion for SAP OAuth bearer token successfully; zero authentication errors.

Phase 4: Dual-Sink Persistence Tier & Private Endpoints (Days 19–25)

• Objective: Deploy Azure SQL Database (3NF Relational Store) and Azure Storage Account (10-Year WORM Immutable Vault) with complete private networking.
• SAP Team Actions:
  • Provide expected data volume estimates (total employee count, average job slices per employee, estimated PDF count).
• CData Team Actions:
  • Validate Private Link TDS connectivity from snet-cdata to Azure SQL Database on port 1433.
• Azure Data Engineering & Infrastructure Team Actions:
  • Deploy Azure Storage Account stsfarchiveprod001 (Standard_ZRS, Cool Tier default).
  • Create containers: attachments, parquet, reconciliation, manifests.
  • Create the 10-Year (3,650 days) immutability policy on attachments/ and reconciliation/ (unlocked until Phase 11, ADR-07).
  • Deploy Azure SQL Server sql-sf-archive-prod and Database sqldb-sf-archive-prod (GP_Gen5_8, Latin1_General_100_CI_AS_SC collation).
  • Establish Private Endpoints for both Blob Storage and SQL Server; disable all public network access.
• Exit Gate: Dual-sink persistence layer online; WORM policy created (unlocked) on attachments/ and reconciliation/; zero public ingress routes exist.

Phase 5: Ingestion Compute & Workstation Provisioning (Days 26–32)

• Objective: Provision CData Sync extraction VM, lock JVM heap memory, and configure high-throughput connection profiles.
• SAP Team Actions:
  • Verify that technical user SF_MIG_TECH_USER has been granted the target population: "Everyone, including inactive users".
• CData Team Actions:
  • Deploy vm-sf-cdata (Standard_D8s_v5, Ubuntu 22.04 LTS) into snet-cdata.
  • Lock JVM heap memory to -Xms8g -Xmx8g -XX:+UseG1GC in /opt/cdatasync/service.env.
  • Install CData Sync and configure systemd service cdatasync.service.
  • Configure SAP SuccessFactors source connection and Azure SQL destination connection.
• Azure Data Engineering & Infrastructure Team Actions:
  • Verify NSG rules for snet-cdata allowing outbound HTTPS (443) via NAT Gateway and inbound SSH (22) from Bastion.
  • Test CData REST API accessibility from the Conductor subnet.
• Exit Gate: CData Sync service running; JVM locked at 8GB; test query against PerPerson returns rows successfully.

Phase 6: Relational Schemas, Control Plane & DDL Deployment (Days 33–42)

• Objective: Deploy all 6 SQL database schemas, state machine tables, unindexed landing heaps, and SCD-2 promotion stored procedures.
• SAP Team Actions:
  • Perform mid-wave check on BCUI data model to confirm no custom field changes have been introduced.
• CData Team Actions:
  • Configure replication jobs for Wave 1 (Org Structure) and Wave 2 (Demographics) targeting stg.*_landing tables.
• Azure Data Engineering & Infrastructure Team Actions:
  • Execute master DDL script creating schemas: ctrl, stg, archive, dq, meta, recon.
  • Deploy stg.fn_ParseODataDate scalar function.
  • Seed -1 UNKNOWN surrogate dimension records in archive.Department, archive.Division, archive.Location.
  • Ingest BCUI XML and Picklist CSVs into meta.BcuiFieldMapping and meta.PicklistOption.
  • Deploy promotion stored procedures (curated.usp_Promote_EmpJob, ctrl.usp_AcquireLease, ctrl.usp_CommitBatch).
• Exit Gate: 100% of SQL tables, stored procedures, and metadata dictionaries deployed and verified in Azure SQL.

Phase 7: Unstructured Document Streaming Worker Setup (Days 43–50)

• Objective: Deploy and validate the zero-disk Python 16MB streaming worker daemon in Azure Container Apps.
• SAP Team Actions:
  • Supply 10 representative attachment IDs (varying file sizes from 100KB to 45MB) for end-to-end streaming validation.
• CData Team Actions:
  • Configure metadata extraction job for Attachment entity to populate stg.Attachment_landing with file IDs and user IDs.
• Azure Data Engineering & Infrastructure Team Actions:
  • Containerize attachment_streamer.py and deploy to Azure Container Apps (snet-aca).
  • Configure managed identity access to Key Vault and Blob Storage.
  • Execute test streaming run on the 10 sample files; verify in-flight SHA-256 calculation, PutBlock staging, and SQL metadata recording.
• Exit Gate: Sample attachments successfully streamed into attachments/ WORM vault; SHA-256 verified; zero disk usage on container.

Phase 8: Orchestration, Governance & Monitoring Setup (Days 51–57)

• Objective: Deploy Azure Container Apps Conductor, Log Analytics workspace, real-time telemetry dashboards, and alerting rules.
• SAP Team Actions:
  • Confirm SAP tenant API rate limits (max concurrent threads = 4, max requests/min = 1,200).
• CData Team Actions:
  • Connect CData Sync webhook and REST API to Conductor engine for automated batch lifecycle signaling.
• Azure Data Engineering & Infrastructure Team Actions:
  • Deploy Log Analytics Workspace law-sf-archive-prod and Azure Monitor alert rules.
  • Deploy views: ctrl.vw_BatchMonitoring and ctrl.vw_DLQSummary.
  • Deploy Azure Conductor orchestration daemon to manage the extraction state machine.
  • Execute full end-to-end dry-run with a 1,000-row synthetic employee slice.
• Exit Gate: Automated orchestration loop validated; alerts fire correctly on simulated errors; monitoring dashboard live.

Phase 9: Full Historical Extraction Waves Execution (Days 58–85)

• Objective: Sequentially execute the full 20-year historical extraction across all 8 waves.
• SAP Team Actions:
  • Monitor SAP tenant performance during business hours; notify migration leads of any system maintenance windows.
• CData Team Actions:
  • Run CData Sync jobs sequentially according to the Wave Execution Plan:
    • Wave 0 (Day 58): Metadata & picklist validation.
    • Wave 1 (Days 59–61): Org Master Data (FOCompany, FODepartment, FOCostCenter).
    • Wave 2 (Days 62–64): Demographics (PerPerson, PerPersonal, PerNationalId).
    • Wave 3 (Days 65–67): Employment Records (EmpEmployment, active and inactive leavers).
    • Wave 4 (Days 68–76): Career & Comp History (EmpJob, EmpCompensation in annual slices 2004–2024).
    • Wave 5 (Days 77–79): Custom MDF Objects (Cust_*).
    • Wave 6a (Day 80): Attachment list metadata into stg.Attachment_landing.
• Azure Data Engineering & Infrastructure Team Actions:
  • Trigger triage and promotion procedures (curated.usp_Promote_EmpJob) upon completion of each batch landing.
  • Export landed staging batches into compressed Parquet cold tier snapshots in parquet/.
  • Execute Wave 6b (Days 81–85): Python streaming workers download 500,000+ files into attachments/ WORM vault.
  • Monitor DLQ defect rates; remediate unmapped picklists with HR SME.
• Exit Gate: All 8 waves complete; 100% of historical records promoted or quarantined in DLQ; zero active worker leases.

Phase 10: Delta Synchronization & Cutover Weekend Execution (Days 86–95)

• Objective: Execute the final delta freeze, synchronize records modified during the migration window, and lock the source tenant.
• SAP Team Actions:
  • Hour 00 (Friday 18:00): Post maintenance banner; lock SAP tenant to read-only; revoke write roles; suspend external HR feeds.
  • Formally sign off on Tenant Freeze Confirmation.
• CData Team Actions:
  • Hour 02 (Friday 20:00): Trigger Door 2 Delta Extraction jobs across all modified entities (lastModifiedDateTime ge ...).
  • Monitor delta batch landing into staging tables; confirm zero pagination failures.
• Azure Data Engineering & Infrastructure Team Actions:
  • Hour 06 (Saturday 02:00): Execute staging triage and SCD-2 promotion for all delta batches.
  • Hour 09 (Saturday 05:00): Python streaming workers execute delta document sweep for newly uploaded files.
  • Hour 12 (Saturday 08:00): Initiate automated 5-Pillar Master Reconciliation Suite.
• Exit Gate: Final delta synced; source tenant frozen; zero uncommitted records in staging.

Phase 11: 5-Pillar Reconciliation Gate & Forensic Sign-Off (Days 96–100)

• Objective: Mathematically verify 0.00% variance across all 5 audit pillars, execute the independent SOAP audit, and sign the digital certificate.
• SAP Team Actions:
  • Co-review reconciliation reports with Internal Audit and Compliance Leads.
• CData Team Actions:
  • Confirm all CData jobs are halted and marked completed; export extraction audit logs.
• Azure Data Engineering & Infrastructure Team Actions:
  • Execute recon.usp_RunCutoverPillars @CycleName = 'FINAL_CUTOVER'.
  • Execute independent Python 10% SOAP CompoundEmployee verification script.
  • Generate and seal FINAL_CUTOVER_CERT.json in reconciliation/certificates/.
  • Convene executive steering committee; collect digital signatures from CIO, Lead Auditor, and SAP SME.
  • After DPO sign-off on the DPIA, lock the immutability policies on attachments/ and reconciliation/ (irreversible, ADR-07).
• Exit Gate: 5 Pillars 100% PASS (0 row variance, $0.00 comp variance, 0 timeline overlaps or gaps, 0 hash failures); cutover certificate sealed; WORM policies locked.

Phase 12: Resource Optimization & Downscaling Runbook (Days 101–105)

• Objective: Downscale database compute, decommission temporary ingestion components, and apply permanent Azure resource locks.
• SAP Team Actions:
  • Transition SAP support team to decommissioning handover.
• CData Team Actions:
  • Back up CData configuration repository; prepare vm-sf-cdata for deallocation.
• Azure Data Engineering & Infrastructure Team Actions:
  • Downscale Azure SQL Database from GP_Gen5_8 (8 vCore) to GP_Gen5_2 (2 vCore), reducing monthly compute costs by 75%.
  • Deallocate and delete vm-sf-cdata virtual machine.
  • Disassociate and delete NAT Gateway (nat-sf-archive-prod) and static public IP (PIP-NAT-SF-ARCHIVE).
  • Apply Azure CanNotDelete resource locks to sql-sf-archive-prod and stsfarchiveprod001.
• Exit Gate: Ongoing operational cost optimized; all ingestion infrastructure decommissioned; archive locked against deletion.

Phase 13: 30-Day Hypercare & SAP Decommissioning (Days 106–135)

• Objective: Support HR and Legal query operations during 30-day hypercare, issue formal SAP contractual termination, and purge staging Parquet snapshots.
• SAP Team Actions:
  • Day 135: Submit counter-signed Reconciliation Certificate to SAP Global Operations; formally terminate SaaS contract.
• CData Team Actions:
  • Stand down CData operational support.
• Azure Data Engineering & Infrastructure Team Actions:
  • Days 106–135: Monitor Retrieval API query performance and SAS download link generation for HR users.
  • Day 165: Azure Storage Lifecycle Management automatically purges temporary raw staging Parquet snapshots from parquet/ (Hypercare Exit + 30 Days), fulfilling GDPR data minimization requirements.
• Exit Gate: SAP contract legally terminated; historical archive fully self-sufficient; regulatory compliance achieved.

Part IVTROUBLESHOOTING RUNBOOKS, ADRs & GOVERNANCE SIGN-OFF

This part provides the operational runbooks for incident remediation, the detailed 8-wave execution matrix, the foundational Architectural Decision Records (ADRs), the multi-team RACI matrix, and the executive sign-off registry.

Section 1010. Operational Troubleshooting Decision Trees & Defect Runbooks

Runbook 1: Resolving SAP API Rate-Limiting / HTTP 429 Throttling

• Symptom: CData ingestion or Python streaming worker logs report HTTP 429 Too Many Requests or HTTP 503 Service Unavailable.
• Root Cause: Ingestion concurrency exceeded the SAP SuccessFactors API gateway quota (typically 4 concurrent connections or 1,200 requests/minute per tenant).
• Automated System Action: The Conductor catches HTTP 429, pauses extraction for that worker, and applies exponential backoff (30s, 60s, 120s, 240s).
• Engineer Resolution Protocol:
  1. Inspect the CData Sync dashboard or Docker logs to determine which entity is generating the spike.
  2. Reduce CData Sync concurrent worker threads from 4 to 2 in the connection advanced properties.
  3. Verify that the batch page size is strictly capped at 1000 records (never higher).
  4. Coordinate with the SAP SME to confirm that no other enterprise reporting tool or payroll extract is querying the SAP production tenant concurrently.
  5. Resume the job; the state machine will restart from the last committed $skiptoken without duplicating rows.

Runbook 2: In-Flight Worker Crash & Automatic Lease Recovery

• Symptom: A streaming container or CData worker terminates abruptly due to host node preemption or out-of-memory error.
• Root Cause: Unhandled hardware failure, network partition, or OOM crash.
• Automated System Action:
  1. The affected slice remains locked in ctrl.EntityLease with status = 'ACTIVE' until lease_expiry elapses (30 minutes).
  2. The next Conductor heartbeat calls ctrl.usp_AcquireLease. The procedure detects the expired lease (lease_expiry < SYSUTCDATETIME()), revokes the crashed worker's ownership, and reassigns the slice to a healthy running instance.
  3. The replacement worker queries the landing table for existing records under the assigned batch_id and resumes extraction.
• Engineer Resolution Protocol:
  1. Inspect container crash logs in Log Analytics (law-sf-archive-prod) using KQL:
     KQL4 linesCopy

     1234

     ContainerAppConsoleLogs_CL
     | where Log_s contains "OutOfMemory" or Log_s contains "SIGTERM"
     | order by TimeGenerated desc
     | take 50

  2. If memory exhaustion occurred, verify that JVM heap locking (-Xms8g -Xmx8g) is enforced and Python streaming workers are utilizing 16MB chunking rather than in-memory buffering.

Runbook 3: Dead-Letter Queue (DLQ) Defect Triage & Reprocessing

• Symptom: Dirty or malformed records land in stg.MigrationExceptions, triggering an alert if DLQ rate exceeds 0.1% of the batch.
• Root Cause: Legacy data quality issues dating back 15–20 years (null mandatory fields, unmapped custom picklist IDs, unexpected string date formats).
• Engineer Resolution Protocol:
  1. Query active unresolved exceptions grouped by error code:
     T-SQL8 linesCopy

     12345678

     SELECT 
         error_code,
         COUNT(*) AS defect_count,
         MIN(created_at) AS first_seen,
         MAX(created_at) AS last_seen
     FROM stg.MigrationExceptions
     WHERE resolution_status = 'NEW'
     GROUP BY error_code;

  2. For ERR_UNMAPPED_PICKLIST: Check meta.PicklistOption to identify if a new picklist code was introduced. If valid, execute meta.usp_LoadPicklists to update the master dictionary.
  3. For ERR_ORPHAN_PARENT: Verify that the record was promoted with -1 UNKNOWN in the foreign key column to preserve referential integrity.
  4. Once root cause is resolved, trigger the automated reprocessing stored procedure:
     T-SQL1 lineCopy

     EXEC stg.usp_ReprocessExceptions @ErrorCode = 'ERR_UNMAPPED_PICKLIST';

Runbook 4: Corrupted Binary Attachment Recovery

• Symptom: In-flight SHA-256 hash does not match expected metadata checksum, or SAP returns a 0-byte stream.
• Root Cause: Network packet drop, corrupted binary stream from SAP Door 3, or transient storage failure.
• Automated System Action:
  1. The Python worker discards staged blocks using blob_client.delete_blob().
  2. Worker retries download from SAP Door 3 up to 3 times with exponential backoff.
• Engineer Resolution Protocol:
  1. If all 3 retries fail, the worker flags the document as FAILED_CHECKSUM or MISSING_AT_SOURCE in archive.EmployeeAttachment.
  2. Data engineer queries missing files:
     T-SQL3 linesCopy

     SELECT attachment_id, user_id, file_name, ingestion_status 
     FROM archive.EmployeeAttachment 
     WHERE ingestion_status IN ('FAILED_CHECKSUM', 'MISSING_AT_SOURCE');

  3. For genuine missing files at source (deleted 10+ years ago in SAP), file a formal Data Quality Finding in dq.Finding for HR Legal sign-off.

Runbook 5: Disaster Recovery Replay from Parquet Cold Backup

• Symptom: A business logic defect is discovered in the SCD-2 promotion procedure 3 weeks post-cutover after SAP has been locked.
• Root Cause: Logic error in computing historical job slice intervals or picklist translations.
• Engineer Resolution Protocol:
  1. Identify affected batches in ctrl.BatchMaster (e.g., Batch #10042 to #10080).
  2. Execute the disaster recovery replay utility from the Jumpbox to restore raw staging data from the immutable Parquet cold tier:
     Shell4 linesCopy

     1234

     python3 tools/ops/restore_snapshot.py \
       --entity "EmpJob" \
       --batch-id 10042 \
       --container "parquet"

  3. The utility streams records from parquet/EmpJob/batch_10042.parquet directly back into stg.EmpJob_landing.
  4. Deploy the corrected promotion stored procedure via Git pull request.
  5. Rerun the idempotent promotion procedure:
     T-SQL1 lineCopy

     EXEC curated.usp_Promote_EmpJob @BatchId = 10042;

  6. Re-run recon.usp_RunCutoverPillars to verify that the historical timeline is mathematically continuous with 0 gaps and 0 overlaps.

Section 1111. Wave Execution Plan & Dependency Matrix

The extraction is executed across 8 discrete, sequential waves. Parent entities are 100% extracted, landed, triaged, and promoted before child entities begin:

Section 1212. Architectural Decision Records (ADR Summary)

ADR-01: Ingestion Engine Selection (CData Sync + Python vs Azure Data Factory)

• Context: Selecting the primary extraction mechanism for SAP SuccessFactors OData APIs.
• Decision: Utilize CData Sync for tabular entities and custom Python streaming workers for binary attachments, governed by an Azure Container Apps Conductor.
• Rationale: Azure Data Factory (ADF) generic OData connector lacks native handling for cursor-based $skiptoken pagination on large 20-year tables, experiences session dropouts on long-running extractions, and lacks in-memory chunked streaming for binary attachments.

ADR-02: Staging-Committed Parquet Backup vs Direct Raw Blob Dump

• Context: Selecting where and how to back up raw source records.
• Decision: Ingest raw rows into Azure SQL stg.*_landing heaps first, and export committed batches into Zstandard-compressed Parquet files in Azure Blob Cold Tier with automated lifecycle deletion 30 days after hypercare exit.
• Rationale: Eliminates the storage and GDPR minimization liabilities of perpetual raw dumps while maintaining full replayability during the critical cutover window.

ADR-03: Binary Document Streaming Pipe vs Local Disk Caching

• Context: Downloading 500,000+ scanned employee PDFs and contracts from SAP.
• Decision: Stream files directly from SAP into Azure Blob Storage in 16MB in-memory blocks using PutBlock / PutBlockList with in-flight SHA-256 calculation.
• Rationale: Prevents worker disk exhaustion crashes, flattens memory usage to <100MB regardless of file size, and ensures byte-for-byte cryptographic integrity.

ADR-04: Permissive Landing (NVARCHAR(MAX)) vs Strict Typing

• Context: Defining DDL for staging tables receiving legacy 20-year data.
• Decision: Declare every column in stg.*_landing as NVARCHAR(MAX) unindexed heaps.
• Rationale: Ingestion must never abort mid-run due to dirty legacy source data. Type casting and business validations occur in downstream triage procedures.

ADR-05: Ephemeral SAS Download Links vs Permanent Blob URLs

• Context: Delivering document downloads to HR and Legal users post-migration.
• Decision: Generate 5-minute, read-only, user-authenticated SAS tokens on demand via a secure Retrieval API.
• Rationale: Prevents token leakage, eliminates broken or stale stored links, and enforces mandatory audit logging for every file access.

ADR-06: Protecting Personal Data in the Archive (Status: Proposed)

• Context: The archive holds national IDs, dates of birth, salaries and passport scans for 20+ years of staff, and will be queried by HR and Legal long after SAP is gone.
• Decision: Encrypt national ID and date of birth columns with Always Encrypted (keys in Key Vault). Apply Dynamic Data Masking to compensation columns for non-privileged roles. Grant access only through Entra ID groups mapped to database roles (HR Read, Legal Discovery, Auditor), and enable SQL Auditing to Log Analytics for every read of archive.*.
• Rationale: TDE protects data at rest but not from over-privileged readers. Retention obligations justify keeping the data, not broad access to it.

ADR-07: When to Lock WORM, and the Erasure Conflict (Status: Proposed)

• Context: A locked time-based retention policy cannot be shortened or removed. Locking early would block corrective re-uploads during the waves. Locking never would leave the "no administrator can delete" promise untrue.
• Decision: Create the policies unlocked in Phase 4. The streaming worker never overwrites a blob already stored with a matching SHA-256, so reruns stay compatible with WORM. Lock attachments/ and reconciliation/ in Phase 11, immediately after the cutover certificate is issued.
• Rationale: Erasure requests during the retention window are handled under GDPR Art. 17(3)(b) (retention required by law). This must be recorded in the DPIA and signed off by the DPO before locking, because the lock removes the technical ability to comply with any later deletion order.

ADR-08: Archive Recovery Objectives (Status: Proposed)

• Context: After SAP is decommissioned, this archive is the only copy. The Parquet snapshots are deleted 30 days after hypercare.
• Decision: Proposed targets: RPO 24 hours, RTO 72 hours for the archive. Use Azure SQL long-term backup retention (weekly backups kept 10 years) on geo-redundant backup storage, and RA-GRS for the blob vault, with an annual restore drill.
• Rationale: Without stated objectives, the backup design cannot be tested or signed off. The figures above are a starting point for the business owner to confirm.

ADR-09: Reconcile Against Source Control Totals, Not the Pipeline's Own Counters

• Context: A gate that compares the archive to numbers the same pipeline produced can only catch some errors. Hardcoded expected totals cannot catch any.
• Decision: At extraction, write SAP's $count, per-currency amount sums and attachment counts into recon.SourceControlTotal. recon.usp_RunCutoverPillars compares the archive and DLQ against those totals. Door 4 (CompoundEmployee SOAP) audits a stratified sample as an additional blocking check.
• Rationale: Each pillar must be able to fail. The rehearsal suite (rehearsal/tests) plants a defect for each pillar and proves that pillar, and only that pillar, rejects it.