Enterprise Data Platform

Architecture

Technical problems and solutions

1. Problem

   A daily full load of a large document partner table took too long for an analytics layer that needs refreshing during the day.

   Solution

   Incremental extraction joined to the document header and filtered by creation or change date, with explicit columns and append-only staging.

   Diagnosis and approach documented

2. Problem

   An UPSERT over millions of rows is expensive even when few of them change.

   Solution

   Compute the delta first and MERGE only that set, optimizing the target Delta table by its business keys.

   Approach documented

3. Problem

   An incremental load based on header dates can miss changes that do not touch those dates.

   Solution

   A periodic full reconciliation alongside the daily incremental load.

   Risk identified and mitigation defined

Code sample

-- 1. Extract only documents created or changed since the last watermark
SELECT p.document_id, p.item_id, p.partner_role, p.customer_id
FROM erp.document_partners AS p
JOIN erp.document_headers AS h
  ON h.document_id = p.document_id
WHERE p.partner_role IN ('SHIP_TO', 'BILL_TO')
  AND (h.created_on >= :watermark OR h.changed_on >= :watermark);

-- 2. Apply the small delta to the warehouse table
MERGE INTO wh.document_partners AS t
USING stg.document_partners_delta AS s
  ON  t.document_id  = s.document_id
  AND t.item_id      = s.item_id
  AND t.partner_role = s.partner_role
WHEN MATCHED THEN UPDATE SET t.customer_id = s.customer_id
WHEN NOT MATCHED THEN
  INSERT (document_id, item_id, partner_role, customer_id)
  VALUES (s.document_id, s.item_id, s.partner_role, s.customer_id);