Walk one legacy Oracle workflow through its full ~18-day Databricks migration lifecycle

1-- LEGACY: Oracle 19c · ACME_DW.customer_rfm_segmentation
2-- Idioms touched on migration day:
3--   · explicit cursor + FETCH loop
4--   · GLOBAL TEMPORARY TABLE
5--   · MERGE INTO ... USING
6--   · CONNECT BY PRIOR (recursive hierarchy)
7--   · NVL / DECODE / ROWNUM / TO_CHAR(date,'YYYYMM')
8
9CREATE GLOBAL TEMPORARY TABLE tmp_rfm_scores (
10  customer_id NUMBER(18), run_ym CHAR(6),
11  last_order_dt DATE, recency_days NUMBER(6),
12  order_count NUMBER(8), gross_sales NUMBER(18,2),
13  r_score NUMBER(1), f_score NUMBER(1),
14  m_score NUMBER(1), rfm_cell CHAR(3)
15) ON COMMIT PRESERVE ROWS;
16
17CURSOR c_customers IS
18  SELECT c.customer_id,
19         NVL(MAX(o.order_dt), DATE '1900-01-01'),
20         COUNT(o.order_id),
21         NVL(SUM(o.gross_amount), 0)
22    FROM acme_dw.customers c
23    LEFT JOIN acme_dw.orders o
24      ON o.customer_id = c.customer_id
25     AND o.order_dt   >= ADD_MONTHS(p_run_date, -24)
26   WHERE c.status_cd IN ('A','H')
27     AND ROWNUM <= 5000000
28   GROUP BY c.customer_id
29   ORDER BY c.customer_id;
30
31OPEN c_customers;
32LOOP
33  FETCH c_customers INTO r_cust.customer_id, ...
34  EXIT WHEN c_customers%NOTFOUND;
35  -- stage into tmp_rfm_scores
36END LOOP;
37CLOSE c_customers;
38
39MERGE INTO acme_dw.mart_customer_rfm t
40USING ( SELECT customer_id, run_ym, rfm_cell,
41               DECODE(rfm_cell,'555','Champion','544','Loyal',
42                              '344','Promising','155','Hibernating','Other')
43          FROM tmp_rfm_scores
44         WHERE run_ym = TO_CHAR(p_run_date,'YYYYMM') ) s
45ON (t.customer_id = s.customer_id AND t.run_ym = s.run_ym)
46WHEN MATCHED THEN UPDATE SET t.rfm_cell = s.rfm_cell, ...
47WHEN NOT MATCHED THEN INSERT (...) VALUES (...);
48
49-- Tier rollup over the customer_tier hierarchy.
50SELECT LPAD(' ', 2*(LEVEL-1)) || t.tier_code,
51       COUNT(DISTINCT r.customer_id), SUM(r.gross_sales)
52  FROM acme_dw.customer_tier t
53  LEFT JOIN acme_dw.mart_customer_rfm r ON r.tier_code = t.tier_code
54 START WITH t.parent_tier IS NULL
55CONNECT BY PRIOR t.tier_code = t.parent_tier;