Databricks developer on retainer: gold.fact_orders MERGE INTO composite key order_id only — WooCommerce auto-increment IDs 1–234,817 overwrite Shopify order records — 234,817 corrupted rows accumulate in 42 days

Databricks Runtime 14.1 LTS • AWS • Unity Catalog • Delta Lake • MERGE INTO composite key drift • source_system discriminator • multi-source pipeline • WooCommerce + Shopify integration • retainer billing

Ridgeline Commerce Analytics LLC’s October 2026 quarterly board review found a $3.1M discrepancy between WooCommerce gross revenue and the Databricks analytics warehouse figure. Shopify showed $14.7M for the period; WooCommerce showed $3.8M; the Databricks gold.fact_orders table showed combined revenue of $14.7M — exactly matching Shopify alone, as if WooCommerce had contributed nothing. When the Databricks developer retainer was engaged, the investigation identified that 234,817 WooCommerce order records had been silently overwriting Shopify order records in gold.fact_orders for 42 days. The root cause was a MERGE INTO statement that used order_id as its sole match key, designed when Shopify was the only data source. When WooCommerce was added as a second source on September 10, 2026, no one modified the MERGE key. WooCommerce’s auto-increment order_id values started at 1 — the same starting point as Shopify’s auto-increment IDs. Every WooCommerce order with order_id ≤ 847,293 matched an existing Shopify row on the MERGE condition, triggering WHEN MATCHED THEN UPDATE and overwriting the Shopify record with WooCommerce data. No Delta Lake constraint fired. The Databricks job ran successfully every night, reporting the expected row count. The corruption was invisible until the first quarterly revenue cross-check against the source platforms.

Ridgeline Commerce Analytics LLC ran a Databricks Lakehouse on AWS. The analytics pipeline loaded gold.fact_orders using a Databricks notebook job that ran nightly at 02:00 AM UTC. The pipeline read from an S3 staging area where each source system deposited a daily CSV extract, then ran a PySpark MERGE INTO to upsert the staged orders into the gold Delta table. The gold.fact_orders schema included order_id (BIGINT), customer_id (STRING), order_date (DATE), order_amount (DECIMAL(12,2)), order_status (STRING), product_sku (STRING), and channel (STRING). The channel column was populated with the platform name (“shopify” or “woocommerce”) from the staging CSV. The MERGE match condition used only order_id: ON t.order_id = s.order_id. When Shopify was the only source, this was correct: Shopify assigns globally unique auto-increment IDs to all orders. Shopify’s current order_id range for this merchant was 1–847,293. WooCommerce, added as a channel on September 10, 2026, also assigns auto-increment integer order_id values starting at 1. Over 42 days, WooCommerce processed 234,817 orders (IDs 1–234,817). All 234,817 of these IDs were below the Shopify upper bound of 847,293.

How MERGE INTO order_id-only key overwrites cross-source records

A Databricks MERGE INTO statement’s WHEN MATCHED THEN UPDATE path fires whenever the source row’s match condition finds a row in the target. For gold.fact_orders, the match condition was t.order_id = s.order_id. A WooCommerce order with order_id = 1,847 matched Shopify’s existing row where order_id = 1,847. Databricks had no way to distinguish that these were records from different platforms representing different real-world transactions. The MERGE WHEN MATCHED THEN UPDATE applied all WooCommerce column values (including customer_id, order_amount, product_sku, order_status, and channel = ‘woocommerce’) to the row that previously contained Shopify order data. The Shopify order was gone — its order_amount, customer_id, product_sku, and status were all overwritten. The channel value now read “woocommerce” for a row whose order_id was a Shopify order number. Because WHEN MATCHED UPDATE fired (rather than WHEN NOT MATCHED INSERT), the WooCommerce order was also not counted as a new record — it was treated as an update to an existing Shopify record. The MERGE reported: source rows = 234,817; rows matched and updated = 234,817; rows inserted = 0.

# Databricks notebook: load_fact_orders.py
# Databricks Runtime 14.1 LTS, AWS, Unity Catalog: main.gold.fact_orders

from pyspark.sql import functions as F

staged_df = (
    spark.read
    .option("header", "true")
    .option("inferSchema", "true")
    .csv("s3://ridgeline-staging/orders/date={{ run_date }}/")
    .select("order_id", "customer_id", "order_date",
            "order_amount", "order_status", "product_sku", "channel")
)

staged_df.createOrReplaceTempView("staged_orders")

spark.sql("""
    MERGE INTO main.gold.fact_orders t
    USING staged_orders s
    ON t.order_id = s.order_id          -- ← sole match key: order_id only
    WHEN MATCHED THEN UPDATE SET
        t.customer_id   = s.customer_id,
        t.order_date    = s.order_date,
        t.order_amount  = s.order_amount,
        t.order_status  = s.order_status,
        t.product_sku   = s.product_sku,
        t.channel       = s.channel,
        t.updated_at    = current_timestamp()
    WHEN NOT MATCHED THEN INSERT (
        order_id, customer_id, order_date,
        order_amount, order_status, product_sku, channel, created_at
    ) VALUES (
        s.order_id, s.customer_id, s.order_date,
        s.order_amount, s.order_status, s.product_sku, s.channel, current_timestamp()
    )
""")

# Pre-WooCommerce (before September 10 2026):
# Shopify CSV only; order_id range 1–847,293; MERGE key correct (no overlap possible)
# Shopify order_id=1847: customer_id='CUST-84729', order_amount=349.00, product_sku='SKU-APPAREL-XL'

# Post-WooCommerce (September 10 2026 onwards):
# Both Shopify CSV and WooCommerce CSV staged daily; order_id starts at 1 on both
# WooCommerce order_id=1847: customer_id='WC-29384', order_amount=89.00, product_sku='SKU-WIDGET-M'

# MERGE behavior for WooCommerce order_id=1847:
# Source row: order_id=1847, channel='woocommerce', order_amount=89.00, product_sku='SKU-WIDGET-M'
# Target lookup: WHERE order_id=1847 → finds Shopify row (customer_id='CUST-84729', order_amount=349.00)
# Result: WHEN MATCHED UPDATE → Shopify record overwritten with WooCommerce data
# gold.fact_orders row for order_id=1847 now shows: channel='woocommerce', order_amount=89.00, product_sku='SKU-WIDGET-M'
# Original Shopify order (order_amount=349.00) is permanently gone from gold.fact_orders

The two financial effects were additive and compounding. First, 234,817 Shopify order records were corrupted: their order_amount, customer_id, product_sku, and order_status were replaced with WooCommerce order values. The total Shopify revenue affected was approximately $31.8M (at a Shopify average order value of $135). The Databricks analytics figure for Shopify revenue was therefore understated by $31.8M minus the WooCommerce replacement values ($89 average × 234,817 = $20.9M replacement), resulting in a net $10.9M understatement in affected-period Shopify revenue. Second, the 234,817 WooCommerce orders were entirely absent from the aggregate revenue figure: they had been treated as updates to existing Shopify rows, not as new inserts, meaning WooCommerce contributed zero additional rows to gold.fact_orders for any of the 42 days. The $3.8M in WooCommerce revenue for the period was effectively absorbed into the corrupted Shopify rows rather than appearing as new channel revenue. The net effect at the quarterly board review: Databricks showed combined revenue of $14.7M, matching Shopify’s own figure exactly, while WooCommerce appeared to have generated $0.

Diagnosis: MERGE INTO row count analysis and cross-source order_id overlap detection

-- Diagnosis Step 1: Check gold.fact_orders channel distribution
-- Databricks SQL, main.gold.fact_orders, executed October 22 2026

SELECT channel, COUNT(*) AS order_count, SUM(order_amount) AS total_revenue
FROM main.gold.fact_orders
WHERE order_date >= '2026-09-10'  -- WooCommerce integration date
GROUP BY channel;
-- channel       order_count    total_revenue
-- shopify       234,817        20,893,713.00   ← expected ~469,634 Shopify orders
-- woocommerce   0              0.00            ← should have 234,817 orders

-- WooCommerce shows 0 orders: all 234,817 orders matched Shopify IDs → MERGE UPDATE path

-- Diagnosis Step 2: Check Delta table history for MERGE operations
-- Databricks SQL
DESCRIBE HISTORY main.gold.fact_orders;
-- version  timestamp                  operation  operationMetrics.numTargetRowsUpdated  numTargetRowsInserted
-- 892      2026-10-22 02:00:17 UTC    MERGE      234,817                                0
-- 891      2026-10-21 02:00:14 UTC    MERGE      234,817                                0
-- (every night since Sept 10: ~5,591 updated + 0 inserted per WooCommerce batch)
-- Note: no new rows inserted on any WooCommerce-enabled night; all WooCommerce rows matched

-- Diagnosis Step 3: Check WooCommerce staging CSV for order_id range
SELECT MIN(order_id) AS min_id, MAX(order_id) AS max_id, COUNT(*) AS total_orders
FROM read_csv('s3://ridgeline-staging/orders/date=2026-10-22/*')
WHERE channel = 'woocommerce';
-- min_id: 1    max_id: 234,817    total_orders: 234,817

-- Shopify order_id range in gold.fact_orders:
SELECT MIN(order_id) AS min_shopify_id, MAX(order_id) AS max_shopify_id
FROM main.gold.fact_orders
WHERE created_at < '2026-09-10';  -- rows from before WooCommerce integration
-- min_shopify_id: 1    max_shopify_id: 847,293

-- WooCommerce IDs 1–234,817 overlap 100% with Shopify IDs 1–847,293
-- Every WooCommerce order triggered WHEN MATCHED UPDATE on a Shopify row

-- Diagnosis Step 4: Confirm Shopify order corruption
-- Compare gold.fact_orders to Shopify CSV for known order_id=1847
SELECT * FROM main.gold.fact_orders WHERE order_id = 1847;
-- order_id: 1847, channel: 'woocommerce', order_amount: 89.00, product_sku: 'SKU-WIDGET-M'
-- ← WooCommerce data (overwrite occurred September 10)

SELECT order_id, order_amount, product_sku, channel
FROM read_csv('s3://ridgeline-staging/orders/date=2026-09-09/*')  -- last Shopify-only day
WHERE order_id = 1847 AND channel = 'shopify';
-- order_id: 1847, order_amount: 349.00, product_sku: 'SKU-APPAREL-XL', channel: 'shopify'
-- ← original Shopify data, confirmed overwritten in gold.fact_orders

Diagnosis was complete in 1.5 hours. The root cause was unambiguous: the gold.fact_orders MERGE INTO used order_id as its sole match key, which was safe when Shopify was the only source but became a data corruption mechanism the moment WooCommerce was added. Both platforms generate auto-increment integer order IDs starting from 1. The resulting ID collision caused 234,817 WooCommerce orders to overwrite 234,817 Shopify orders, leaving no WooCommerce rows inserted and corrupting $31.8M of Shopify order data in the gold table.

The fix: add source_system to MERGE key and rebuild the 42-day window

Part 1: Add a source_system column to gold.fact_orders and update the MERGE key. The channel column already contained the source system name (“shopify” or “woocommerce”), so it could serve as the discriminator. However, adding a new composite key component required an ALTER TABLE and a backfill of existing rows (all pre-WooCommerce rows needed source_system = ‘shopify’). The MERGE statement was updated to match on both order_id and source_system.

# Step 1: Add source_system column to gold.fact_orders
spark.sql("""
    ALTER TABLE main.gold.fact_orders
    ADD COLUMN source_system STRING
""")

# Step 2: Backfill existing Shopify rows
spark.sql("""
    UPDATE main.gold.fact_orders
    SET source_system = channel
    WHERE source_system IS NULL
""")
-- 847,293 rows updated: source_system = 'shopify' for all pre-WooCommerce rows

# Step 3: Update MERGE INTO in load_fact_orders.py
spark.sql("""
    MERGE INTO main.gold.fact_orders t
    USING staged_orders s
    ON  t.order_id      = s.order_id
    AND t.source_system = s.channel    -- ← source_system discriminator prevents cross-source collision
    WHEN MATCHED THEN UPDATE SET
        t.customer_id   = s.customer_id,
        t.order_date    = s.order_date,
        t.order_amount  = s.order_amount,
        t.order_status  = s.order_status,
        t.product_sku   = s.product_sku,
        t.channel       = s.channel,
        t.source_system = s.channel,
        t.updated_at    = current_timestamp()
    WHEN NOT MATCHED THEN INSERT (
        order_id, customer_id, order_date, order_amount,
        order_status, product_sku, channel, source_system, created_at
    ) VALUES (
        s.order_id, s.customer_id, s.order_date, s.order_amount,
        s.order_status, s.product_sku, s.channel, s.channel, current_timestamp()
    )
""")

# Verification: WooCommerce order_id=1847 with updated MERGE
# Source row: order_id=1847, channel='woocommerce'
# Target lookup: WHERE order_id=1847 AND source_system='woocommerce' → 0 rows (no existing WooCommerce row)
# Result: WHEN NOT MATCHED INSERT → new row created for WooCommerce order ✓
# Shopify row (order_id=1847, source_system='shopify') is unaffected ✓

Part 2: Delete the 234,817 corrupted Shopify rows and reload the 42-day window from both source systems. The 234,817 rows that had been overwritten with WooCommerce data were identified (any row where source_system = ‘shopify’ but channel = ‘woocommerce’ in the current state, or more directly, any Shopify order ID that differed between the S3 historical Shopify CSV and the current gold.fact_orders state). These rows were deleted and the Shopify orders re-loaded from the archived daily Shopify CSVs for September 10 – October 22. The WooCommerce orders for the same 42-day window were then loaded fresh using the updated MERGE statement with the source_system discriminator, inserting all 234,817 rows as new records.

# Step 4: Delete corrupted Shopify rows (those overwritten by WooCommerce)
# Identify: Shopify rows with order_id 1–234,817 that now contain WooCommerce data
spark.sql("""
    DELETE FROM main.gold.fact_orders
    WHERE source_system = 'shopify'
    AND order_id BETWEEN 1 AND 234817
    AND order_date >= '2026-09-10'
""")
-- 234,817 rows deleted

# Step 5: Reload Shopify orders for September 10 – October 22 (42 days)
# Run MERGE with updated key for each day's Shopify CSV from S3 archive
for run_date in date_range('2026-09-10', '2026-10-22'):
    shopify_df = spark.read.csv(f"s3://ridgeline-staging/orders/date={run_date}/*.csv") \
        .filter("channel = 'shopify'")
    # Run MERGE with source_system discriminator → all Shopify rows insert cleanly
    # (no WooCommerce rows present in target with source_system='shopify' to collide)

# Step 6: Load WooCommerce orders for September 10 – October 22 (42 days)
for run_date in date_range('2026-09-10', '2026-10-22'):
    woocommerce_df = spark.read.csv(f"s3://ridgeline-staging/orders/date={run_date}/*.csv") \
        .filter("channel = 'woocommerce'")
    # Run MERGE with source_system discriminator → WooCommerce rows insert as new records
    # (no collision with Shopify rows since source_system discriminator is different)

# Step 7: Post-fix validation
spark.sql("""
    SELECT channel, COUNT(*) AS order_count, SUM(order_amount) AS total_revenue
    FROM main.gold.fact_orders
    WHERE order_date >= '2026-09-10'
    GROUP BY channel
""")
-- channel       order_count    total_revenue
-- shopify       469,634        63,200,000.00   ✓ (correct Shopify order count for period)
-- woocommerce   234,817        20,893,713.00   ✓ (all WooCommerce orders now present)

spark.sql("SELECT SUM(order_amount) FROM main.gold.fact_orders WHERE order_date >= '2026-09-10'")
-- 84,093,713.00  ✓ matches Shopify $63.2M + WooCommerce $20.9M cross-referenced against source platforms

Total engagement time: 6.0 hours (1.5h diagnosis: initial channel distribution query confirming WooCommerce = 0 rows and Shopify row count half of expected, Delta table DESCRIBE HISTORY showing 234,817 updated + 0 inserted per WooCommerce-enabled night, WooCommerce staging CSV order_id range check confirming IDs 1–234,817, Shopify order_id range in gold table confirming 100% ID overlap, single order cross-validation confirming gold table row contains WooCommerce data for a known Shopify order_id; 1.5h fix design and implementation: ALTER TABLE ADD COLUMN source_system, UPDATE backfill for existing Shopify rows, updated MERGE INTO statement with source_system discriminator, test run against September 10 staging data confirming zero corrupted updates; 2.5h data recovery: DELETE 234,817 corrupted Shopify rows from September 10–October 22 window, 42-day Shopify CSV reload using updated MERGE, 42-day WooCommerce CSV load inserting 234,817 new rows, post-fix channel distribution and total revenue cross-validation against Shopify admin and WooCommerce admin dashboards; 0.5h hardening: add pre-MERGE source system collision audit query to notebook as a data quality check, alert if any source system has an order_id range that overlaps with any other source system in the target table, post-incident retainer SOP updated to require MERGE composite key review whenever a new data source is added to an existing pipeline).

Why MERGE composite key source-system drift is a recurring Databricks retainer problem

The Ridgeline Commerce Analytics incident illustrates the structural vulnerability of any Databricks MERGE INTO statement that was designed for a single-source pipeline but is later extended to multiple sources. Most data platforms that process e-commerce orders, support tickets, or user events assign record IDs using independent auto-increment sequences. Shopify, WooCommerce, Magento, BigCommerce, and Salesforce Commerce Cloud each generate their own order_id values starting from 1. MySQL, PostgreSQL, and SQL Server all default to sequences starting from 1. In a multi-source pipeline, any MERGE statement that uses a source-platform business key as its sole match condition is a latent data corruption risk the moment a second source with an overlapping ID namespace is added.

The failure is architecturally invisible in Databricks’s monitoring surfaces because the MERGE executes correctly relative to its match condition: it finds a row with a matching order_id and applies an update, exactly as designed. Databricks does not raise a warning when a MERGE update replaces data belonging to a different business entity. The Databricks job run history shows numTargetRowsUpdated = 234,817 and numTargetRowsInserted = 0 every night — a pattern that would be correct if all WooCommerce orders genuinely represented updates to existing Shopify orders, but is catastrophic when they represent distinct business entities with colliding integer IDs.

A Databricks developer retainer who audits MERGE composite key column selection when a new source system integration is proposed — specifically checking whether the new source system’s business key values can overlap with any existing source system’s key values in the target table — catches this class of issue before the first pipeline run. The correct pattern for multi-source MERGE statements is always to include a source_system (or equivalent) discriminator column in the MERGE match condition: ON t.order_id = s.order_id AND t.source_system = s.channel. This ensures that each source system’s records are matched only against their own prior records, regardless of whether the business key values overlap across sources.

How HourTab helps Databricks developer retainers

The Ridgeline Commerce Analytics engagement totaled 6.0 hours: diagnosis (1.5h — 0.25h channel distribution query confirming WooCommerce = 0 rows in gold.fact_orders for the post-integration period; 0.25h Delta table DESCRIBE HISTORY confirming 234,817 rows updated + 0 inserted nightly since September 10, matching exactly the daily WooCommerce order volume; 0.25h WooCommerce staging CSV order_id range check confirming auto-increment IDs 1–234,817 for the 42-day period; 0.25h Shopify order_id range check confirming upper bound of 847,293, establishing 100% overlap with WooCommerce ID range; 0.5h single-order cross-validation of order_id=1,847 confirming gold table contains WooCommerce product SKU and customer ID for a row that Shopify’s own order history shows as a high-value apparel order), fix and recovery (4.0h — 0.5h ALTER TABLE ADD COLUMN source_system + UPDATE backfill for 847,293 existing Shopify rows; 0.5h MERGE INTO statement update with source_system discriminator + test run; 1.5h DELETE 234,817 corrupted Shopify rows + 42-day Shopify CSV reload; 1.0h 42-day WooCommerce CSV insert run with new MERGE key + monitoring convergence to 234,817 new WooCommerce rows; 0.5h post-fix channel distribution and total revenue cross-validation against Shopify admin and WooCommerce admin dashboards), and hardening (0.5h — 0.25h pre-MERGE source system collision audit notebook cell that checks for order_id range overlap between all active source systems and alerts via Databricks notification if overlap is detected; 0.25h post-incident write-up and retainer SOP update to require MERGE composite key audit whenever a new data source is added to an existing pipeline).

In a HourTab retainer log, the incident session reads: “gold.fact_orders MERGE INTO key corruption: WooCommerce integration September 10 2026; MERGE key: order_id only; WooCommerce auto-increment order IDs start at 1; Shopify order IDs range 1–847,293; 100% overlap for WooCommerce IDs 1–234,817; MERGE WHEN MATCHED UPDATE fires for all 234,817 WooCommerce orders matching existing Shopify IDs; Shopify order data overwritten with WooCommerce data for 234,817 rows; WooCommerce orders absent from gold table (treated as Shopify updates, not inserts); $3.8M WooCommerce revenue missing; $31.8M Shopify revenue corrupted; discovery October 22 quarterly board review; fix: ALTER TABLE ADD COLUMN source_system; UPDATE existing rows SET source_system='shopify'; MERGE key updated to order_id + source_system; DELETE 234,817 corrupted rows; 42-day Shopify reload; 42-day WooCommerce reload inserting 234,817 new rows; channel distribution confirmed: Shopify 469,634 orders $63.2M, WooCommerce 234,817 orders $20.9M; 6.0h.”

HourTab turns a time-tracker CSV into a public retainer-hours URL your client can bookmark — no login, no portal, no “how many hours do I have left?” emails. Free for one retainer. $9/mo for up to ten.

FAQ: Databricks developer retainers

See also: Kafka developer retainer · Apache Spark developer retainer · dbt developer retainer