SAP ECC to Databricks Lakehouse: Enterprise CDC Architecture on Azure

Enterprise Lakehouse architecture migrating SAP ECC hosted on Oracle DB to Azure Databricks Delta Lake, processing 5 Million CDC records per day in 1-hour incremental batches with automated PySpark schema drift detection and quarantine workflows.

Overview

Modernizing legacy SAP Enterprise Resource Planning (SAP ECC 6.0) workloads into a cloud-native analytical platform requires balancing source system stability, low-latency Change Data Capture (CDC), data governance, and strict cost controls.

In this enterprise deployment for a global industrial manufacturer, core financial and operational SAP tables—such as General Ledger (BSEG/BKPF), Purchasing (EKKO/EKPO), and Sales Orders (VBAK/VBAP) hosted on an underlying Oracle Database—were modernized into an Azure Databricks Lakehouse using Delta Lake format.

The system processes 5 Million CDC records per day arriving in 1-hour batch windows (~208,000 records per hourly batch), enabling near-real-time executive analytics while protecting SAP production performance.

---

Architecture & Tech Stack

Layer	Technology & Architectural Pattern
Source System	SAP ECC 6.0 on Oracle DB with SAP Operational Data Provisioning (ODP) & OData services
Ingestion & Orchestration	Azure Data Factory (ADF) with native SAP CDC Connector writing Parquet to ADLS Gen2
Storage Engine	Azure Data Lake Storage (ADLS Gen2) Landing & Databricks Delta Lake (Medallion Architecture)
Processing Engine	Azure Databricks PySpark running on Databricks Serverless Compute for Jobs
Schema Management	Auto Loader (addNewColumns) for Bronze; custom PySpark Schema Inspector & alert trigger for Silver
Data Quality	Multi-tier validation routing non-conforming rows to _quarantine Delta tables with Azure Monitor alerting
Optimization	Delta Lake Liquid Clustering (CLUSTER BY), Z-Ordering, and automated VACUUM / OPTIMIZE maintenance

---

Ingestion Architecture Trade-offs

Extracting change vectors from SAP ECC without destabilizing the production Oracle database is a common enterprise challenge. Three primary architectural patterns were evaluated before selecting the production standard:

Evaluation Metric	Option 1: ADF SAP CDC Connector (Chosen)	Option 2: Azure Synapse Pipelines	Option 3: Custom PySpark OData Reader
SAP Integration Level	Deep native integration with SAP ODP framework & delta queues (ODQ)	Native ODP support, but requires Synapse workspace management overhead	Surface-level HTTP OData API ingestion without native SAP transaction safety
Source Load Impact	Very Low: Delta extraction occurs in SAP ODQ memory buffer outside primary Oracle OLTP tables	Very Low: Identical ODP framework usage as ADF	High: Frequent heavy REST HTTP polling directly against SAP Application Servers
CDC Management	Automated delta subscriber state management, pointer tracking, and error recovery	Automated delta subscriber tracking within Synapse pipelines	Custom state management required in external database to store low-watermark tokens
Operational Overhead	Serverless ADF triggers with zero compute infrastructure management	Requires dedicated Synapse Integration Runtimes and linked services	Requires custom PySpark code maintainability and error handling for SAP HTTP timeouts
Verdict & Selection	Implemented Enterprise Standard: Lowest risk, fully supported by SAP/Microsoft, built-in retry logic.	Rejected due to duplicate workspace footprint when Databricks is the primary analytics engine.	Rejected due to high maintainability risk and lack of transactional delta pointer guarantees.

---

Medallion Lakehouse & Schema Strategy

The Lakehouse implementation follows the Medallion pattern (Landing → Bronze → Silver → Gold) to isolate raw staging from enterprise analytical data assets.

1. Landing to Bronze Schema Evolution

Raw CDC data extracted from SAP ODP arrives in ADLS Gen2 as compressed Parquet files. Databricks Auto Loader (cloudFiles) ingests these micro-batches directly into Bronze Delta tables:

# Ingesting SAP raw CDC streams with automated schema evolution
bronze_df = (
    spark.readStream.format("cloudFiles")
    .option("cloudFiles.format", "parquet")
    .option("cloudFiles.schemaLocation", "/mnt/telemetry/schemas/sap_bseg")
    .option("cloudFiles.schemaEvolutionMode", "addNewColumns")
    .load("abfss://landing@storagelake.dfs.core.windows.net/sap/bseg/")
)

(
    bronze_df.writeStream.format("delta")
    .option("checkpointLocation", "/mnt/telemetry/checkpoints/sap_bseg_bronze")
    .outputMode("append")
    .table("bronze_sap.bseg")
)

2. Bronze to Silver Schema Drift Alerting & Delta MERGE

To prevent unannounced SAP table enhancements (e.g. adding custom ZZ_ fields or modifying field lengths in SAP SE11) from silently degrading Silver analytics, a PySpark Schema Inspector validates batch structures prior to merging into Silver.

def process_silver_merge(microBatchDf, batchId):
    # 1. Schema Drift Inspection
    target_table = "silver_sap.bseg"
    target_schema = spark.table(target_table).schema
    source_schema = microBatchDf.schema
    
    new_columns = [
        field.name for field in source_schema 
        if field.name not in target_schema.fieldNames()
    ]
    if new_columns:
        # Trigger external webhook/alert to Data Governance team
        send_schema_drift_alert(
            table=target_table, 
            new_cols=new_columns, 
            batch_id=batchId
        )
    
    # 2. Windowed Deduplication & CDC Change Application
    # ODQ_CHANGEMODE: 'I' = Insert, 'U' = Update, 'D' = Delete
    # ODQ_ENTITYCNTR: Sequential SAP change counter
    from pyspark.sql.window import Window
    from pyspark.sql.functions import col, row_number
    
    join_cond = (
        "target.MANDT = source.MANDT AND "
        "target.BUKRS = source.BUKRS AND "
        "target.BELNR = source.BELNR AND "
        "target.GJAHR = source.GJAHR AND "
        "target.BUZEI = source.BUZEI"
    )
    
    window_spec = (
        Window.partitionBy("MANDT", "BUKRS", "BELNR", "GJAHR", "BUZEI")
        .orderBy(col("ODQ_ENTITYCNTR").desc())
    )
    deduped_df = (
        microBatchDf
        .withColumn("rank", row_number().over(window_spec))
        .filter(col("rank") == 1)
    )
    
    # 3. Delta MERGE Execution with Merge Schema Option
    silver_delta_table = DeltaTable.forName(spark, target_table)
    
    (
        silver_delta_table.alias("target")
        .merge(
            deduped_df.alias("source"),
            join_cond
        )
        .whenMatchedDelete(condition="source.ODQ_CHANGEMODE = 'D'")
        .whenMatchedUpdateAll(condition="source.ODQ_CHANGEMODE != 'D'")
        .whenNotMatchedInsertAll(condition="source.ODQ_CHANGEMODE != 'D'")
        .execute()
    )

---

Custom Backfill & Re-sync Logic

When initializing a new SAP table or performing a historical data refresh, running full extracts through standard CDC streams causes downstream latency spikes. The architecture employs a Two-Phase Modular Pipeline:

Phase 1: Range-Chunked Historical Load

• Partitioned Extraction: ADF dynamically queries SAP ODP using parameterized key/date bounds (e.g. Fiscal Year ranges GJAHR BETWEEN 2018 AND 2024).
• Target Isolation: Historical chunks land in a temporary staging path in ADLS Gen2 and bypass standard Bronze streaming queues to avoid trigger lockups.
• Bulk Delta Initialization: PySpark executes parallel bulk writes directly into Bronze and Silver Delta tables, applying Delta Lake Liquid Clustering (CLUSTER BY MANDT, BUKRS, GJAHR) for optimal historical query performance.

Phase 2: ODP Pointer Re-sync & Deterministic Windowed Merge

• Delta Subscriber Initialization: Once historical backfill is complete, ADF initializes the SAP ODP Delta Subscription queue with a unique SubscriberName and ExtractionContext.
• Out-of-Order Catch-up Handling: During the catch-up phase, late-arriving or out-of-order CDC updates generated during the backfill window are resolved deterministically by comparing the ODQ_ENTITYCNTR sequence number against the existing Silver record. If the incoming record sequence is older than the current Silver state, the MERGE operation skips update application.

---

Data Quality & Quarantine Framework

To prevent malformed SAP records (such as unparseable packed decimals BCD, missing mandatory compound keys MANDT, or currency conversion anomalies) from halting production pipelines, a Multi-tier Data Quality (DQ) Engine was implemented:

PySpark DQ Rules & Quarantine Implementation

from pyspark.sql.functions import col, when, lit, concat_ws, current_timestamp

# Define mandatory validation rules
is_valid_pk = (
    col("MANDT").isNotNull() & 
    col("BUKRS").isNotNull() & 
    col("BELNR").isNotNull()
)
is_valid_amount = col("DMBTR").cast("decimal(15,2)").isNotNull()
is_valid_posting_date = col("BUDAT").rlike(r"^\d{8}$")

dq_evaluated_df = bronze_df.withColumn(
    "dq_status",
    when(~is_valid_pk, lit("INVALID_PRIMARY_KEY"))
    .when(~is_valid_amount, lit("MALFORMED_AMOUNT"))
    .when(~is_valid_posting_date, lit("INVALID_POSTING_DATE"))
    .otherwise(lit("PASSED"))
)

# Route invalid records to Quarantine table
quarantine_df = (
    dq_evaluated_df
    .filter(col("dq_status") != "PASSED")
    .withColumn("quarantined_at", current_timestamp())
)

(
    quarantine_df.write.format("delta")
    .mode("append")
    .saveAsTable("quarantine_sap.bseg_quarantine")
)

# Filter clean records for Silver Merge
clean_silver_df = (
    dq_evaluated_df
    .filter(col("dq_status") == "PASSED")
    .drop("dq_status")
)

---

Performance & Resource Cost Optimization

Processing 5 Million CDC records daily across 24 hourly execution cycles requires selecting the optimal Databricks compute runtime model to satisfy strict runtime SLAs (< 15 minutes) while controlling cloud expenditures.

Compute Infrastructure Options Evaluated

Compute Paradigm	Option 1: Databricks Serverless Jobs (Chosen)	Option 2: Automated Job Clusters (Provisioned)	Option 3: Always-On Interactive Cluster
Cluster Startup Latency	< 10 seconds (Instant serverless allocation)	3–5 minutes (Azure VM provision & Databricks init)	0 seconds (Pre-warmed running cluster)
Batch Processing SLA	6–8 minutes total execution time	11–14 minutes total (including VM startup time)	5–6 minutes total execution time
Resource Idle Cost	Zero idle cost: Billed strictly per second during active Spark execution	Zero idle cost, but incurs 3–5 min VM spin-up billing overhead per run	High: Pays continuous VM & DBU costs 24/7 during zero-traffic windows
Management Complexity	Zero management: Automated Spark configuration & autoscaling	Requires sizing node pools (Standard_D4ds_v5) & managing Spot policies	Requires auto-scaling tuning and cluster restart policies
Estimated Monthly Compute Cost	~$380 – $520 / month	~$650 – $850 / month	~$1,800 – $2,400 / month

Key Cost & Performance Levers Applied

1. Databricks Serverless Compute for Jobs: Eliminates cluster spin-up delays (~4 minutes saved per hourly batch run), directly decreasing DBU consumption and ensuring consistent < 8-minute completion SLAs.
2. Delta Liquid Clustering (CLUSTER BY): Replaced legacy rigid partition schemes with Liquid Clustering on key lookup dimensions (MANDT, BUKRS, BELNR). This reduced Delta MERGE read amplification by 64% and cut overall batch execution time by 3.5 minutes.
3. Automated Maintenance Jobs: Scheduled off-peak OPTIMIZE and VACUUM jobs (retaining 7-day history) during low-volume hours (02:00 UTC), keeping file sizes optimized around ~128MB without impacting peak hourly batch runs.

---

Key Learnings

• Decouple CDC Extraction from Processing: Using ADF with the SAP ODP connector keeps transactional pressure off the SAP Oracle database. Staging raw payload files in ADLS Gen2 guarantees safe replayability without re-querying SAP.
• Implement Schema Drift Detection at Silver, Not Bronze: Bronze tables should absorb source schema modifications smoothly using Auto Loader (addNewColumns). Silver tables should enforce strict validation and trigger alerts to maintain clean, reliable analytical data models.
• Serverless Compute Cuts Batch Pipeline Overhead: For hourly micro-batch workloads processing ~200K records per batch, Databricks Serverless Compute eliminates VM startup overhead, cutting compute costs by over 40% compared to provisioned job clusters.