K/20X Labs · Production

Backfill 529 null consent_timestamps — Habeas Data critical

529 rows (6.3%) in consent_log have a null consent_timestamp. Colombian Habeas Data law (Ley 1581 de 2012) requires a documented timestamp for every consent record. These rows will fail the next SFC audit.

UPDATE consent_log SET consent_timestamp = created_at WHERE consent_timestamp IS NULL AND created_at IS NOT NULL;
-- Remaining nulls: escalate to operations team for manual backfill from paper forms.

529 rows affected

Normalize 19 city variants → 6 canonical Colombian cities

19 unique strings cluster into 6 canonical cities. rapidfuzz centroid similarity threshold 0.91. BOG / bogota / Bogt´a / bogot / Bogotá D.C. all map to Bogotá. Affects downstream city-level analytics and Habeas Data subject-location reporting.

UPDATE customers SET city = CASE
  WHEN lower(city) IN ('bog','bogota','bogt´a','bogot','bogotá d.c.','bogotá dc','santafe de bogota') THEN 'Bogotá'
  WHEN lower(city) IN ('mde','medellin','medellín') THEN 'Medellín'
  WHEN lower(city) IN ('cli','cali') THEN 'Cali'
  WHEN lower(city) IN ('baq','barranquilla','barranqilla') THEN 'Barranquilla'
  WHEN lower(city) IN ('ctg','cartagena','cartagena de indias') THEN 'Cartagena'
  WHEN lower(city) IN ('bga','bucaramanga') THEN 'Bucaramanga'
  ELSE city
END
WHERE city NOT IN ('Bogotá','Medellín','Cali','Barranquilla','Cartagena','Bucaramanga');

12,200 rows affected

Resolve 13,720 duplicate SKUs — ETL upsert logic broken

The ETL pipeline is inserting rows on conflict instead of updating them. This produces multiple product rows per SKU, corrupting inventory joins and order fulfillment downstream. The legacy_mysql.products → analytics_orders.dim_products lineage path is unreliable until fixed.

-- Step 1: Identify duplicates
SELECT sku, COUNT(*) as cnt FROM products GROUP BY sku HAVING cnt > 1;
-- Step 2: Keep most recent by created_at, remove older duplicates
DELETE p1 FROM products p1
INNER JOIN products p2 ON p1.sku = p2.sku AND p1.created_at < p2.created_at;
-- Step 3: Add unique constraint
ALTER TABLE products ADD UNIQUE INDEX uq_sku (sku);

13,720 rows affected

Handle 487 orphan transactions referencing deleted merchants

487 transaction rows reference a merchant_id that no longer exists in the merchants table (merchants deleted without cascade). These transactions are invisible in merchant-level reporting and may cause FK violations in downstream pipelines.

-- Option A: Archive deleted merchants
INSERT INTO merchants_archive SELECT * FROM merchants_deleted WHERE merchant_id IN (
  SELECT DISTINCT merchant_id FROM transactions t
  LEFT JOIN merchants m ON t.merchant_id = m.merchant_id
  WHERE m.merchant_id IS NULL
);
-- Option B: Null out orphan references (loses traceability)
UPDATE transactions SET merchant_id = NULL WHERE merchant_id NOT IN (SELECT merchant_id FROM merchants);

487 rows affected