Real ETL Testing — Live Oracle Project

Source-to-target mapping and testing · Data warehouse testing · SQL-based validation

About this lab

A production ELT pipeline that ran against a live Oracle Autonomous Database: 541,909 Kaggle retail rows staged in Object Storage, loaded with DBMS_CLOUD, transformed into a star schema, and validated with 23 checks — all PASS. Read the real STM document, browse the real source, staging and warehouse schemas, and run SQL as the read-only tester.

45 min · Source-to-target mapping and testing · Data warehouse testing · SQL-based validation · Real-time project scenarios

Dataset: Online Retail (UCI)
Database: elt-retail-adb · Oracle Database 19c (Always-Free, 1 ECPU)
Bucket: kaggle-staging-bucket (bmp9etfqahrc)
Tester: TESTER_USER · read-only

Nothing here is seeded. Every row count, column and mapping rule was read from the live DWH_METRIC_CONTROL and DWH_ETL_AUDIT objects.
The tester account holds SELECT on 16 objects and nothing else — run an INSERT and watch ORA-01031 prove the read-only guarantee.
Fact + quarantine + excluded reconciles exactly to the cleaned input: 524,877 + 0 + 2,510 = 527,387. Nothing vanished.

Row waterfall — the pipeline in six numbers

Read from DWH_VW_ROW_PARITY. Every row is fact, quarantined or excluded — never silently dropped.

01Raw rows ingested (SRC_SCHEMA.RETAIL_RAW) — COPY_DATA from 23 Object Storage parts5,41,909
02After exact-duplicate removal — −5,268 full-row duplicates5,36,641
03After credit-note / adjustment removal — −9,254 InvoiceNo LIKE 'C%' / 'A%'5,27,387
04Unparseable rows quarantined — bad date or non-numeric qty/price → DWH_FACT_QUARANTINE0
05Non-positive qty/price excluded — bookkeeping lines → DWH_FACT_EXCLUDED (retained, not dropped)2,510
06FINAL FACT ROWS (DWH_FACT_SALES) — revenue 10,631,048.745,24,877

Source to Target Mapping (STM) Document

Document Information

Project Name	Online Retail Data Warehouse Project
Module	Online Retail Load (Kaggle → Oracle ADW)
Source System	Kaggle CSV via OCI Object Storage
Target System	Oracle Autonomous Database (elt-retail-adb, 19c)
Mapping Name	m_retail_raw_to_fact_sales
Session Name	s_m_retail_raw_to_fact_sales
Workflow Name	wf_retail_daily_load
Load Type	Full Load
Source Type	Oracle Table (landing, all-VARCHAR2)
Target Type	Oracle Table (typed fact)
Reject Tables	DWH_FACT_QUARANTINE, DWH_FACT_EXCLUDED
Version	1.0

Source Details

Database	Oracle Autonomous Database
Schema	SRC_SCHEMA
Table Name	RETAIL_RAW
Row Count	541,909
Origin	DBMS_CLOUD.COPY_DATA from 23 Object Storage parts (pre-authenticated URLs, credential_name => NULL)

Target Details

Database	Oracle Autonomous Database
Schema	DWH_SCHEMA
Table Name	DWH_FACT_SALES
Row Count	524,877
Grain	One row per invoice line (deduplicated, typed, positive measures only)

Source-to-Target Mapping

Seq	Source Column	Src Type	Len	Null?	Transformation Rule	Target Column	Tgt Type	Len	Null?
1	INVOICE_NO	VARCHAR2	30	No	Direct mapping. Exclude InvoiceNo LIKE 'C%' (credit notes) and 'A%' (adjustments) before load.	INVOICE_NO	VARCHAR2	30	No
2	STOCK_CODE	VARCHAR2	30	No	Direct mapping (TRIM).	STOCK_CODE	VARCHAR2	30	No
3	DESCRIPTION	VARCHAR2	4000	Yes	NVL(description, 'UNKNOWN DESCRIPTION'). 1,454 NULLs imputed, never dropped.	DESCRIPTION	VARCHAR2	4000	No
4	QUANTITY	VARCHAR2	30	No	TO_NUMBER(TRIM(quantity) DEFAULT NULL ON CONVERSION ERROR). Rows with Quantity ≤ 0 routed to DWH_FACT_EXCLUDED.	QUANTITY	NUMBER	—	No
5	INVOICE_DATE	VARCHAR2	40	No	TO_DATE(TRIM(invoice_date), 'YYYY-MM-DD HH24:MI:SS'). Unparseable → DWH_FACT_QUARANTINE.	INVOICE_TS	TIMESTAMP	—	No
6	— (derived)	—	—	—	TRUNC(invoice_ts); TO_CHAR parts for year, month, ISO week, day, hour.	INVOICE_DATE / INVOICE_MONTH / …	DATE / VARCHAR2	—	No
7	UNIT_PRICE	VARCHAR2	30	No	TO_NUMBER(TRIM(unit_price) DEFAULT NULL ON CONVERSION ERROR). Rows with UnitPrice ≤ 0 routed to DWH_FACT_EXCLUDED.	UNIT_PRICE	NUMBER	—	No
8	CUSTOMER_ID	VARCHAR2	30	Yes	Strip float suffix ('17850.0' → '17850'); blank/garbage → 'GUEST' bucket (132,185 rows, ~25% of revenue — bucketed, never dropped).	CUSTOMER_KEY	VARCHAR2	30	No
9	COUNTRY	VARCHAR2	100	No	NVL(TRIM(country), 'UNKNOWN').	COUNTRY	VARCHAR2	100	No
10	— (derived)	—	—	—	ROUND(Quantity × UnitPrice, 2). Total 10,631,048.74.	REVENUE	NUMBER	—	No
11	— (derived)	—	—	—	ROW_NUMBER() OVER (ORDER BY invoice_no, stock_code, invoice_ts).	SALES_ID	NUMBER	—	No

Business Rules

BR-001	INVOICE_NO must not be NULL
BR-002	Full natural row must be unique in fact (exact duplicates removed: 5,268)
BR-003	QUANTITY must be greater than 0 (1,336 negatives excluded to DWH_FACT_EXCLUDED)
BR-004	UNIT_PRICE must be greater than 0 (2,510 zero-price lines excluded to DWH_FACT_EXCLUDED)
BR-005	CUSTOMER_KEY must not be NULL ('GUEST' bucket instead of dropping 132,185 rows)
BR-006	REVENUE must equal Quantity × UnitPrice on every row (10,631,048.74 total)
BR-007	Credit-note invoices (InvoiceNo LIKE 'C%') must not enter the fact (9,254 removed)

Validation Rules

VR-001	Verify raw row count = 541,909 after load
VR-002	Verify no CSV header row leaked (invoice_no = 'InvoiceNo' count = 0)
VR-003	Verify fact + quarantine + excluded = cleaned (524,877 + 0 + 2,510 = 527,387)
VR-004	Verify revenue identical across fact, monthly, product and country grains
VR-005	Verify every fact stock_code exists in DWH_DIM_PRODUCT
VR-006	Verify every fact customer_key exists in DWH_DIM_CUSTOMER
VR-007	Verify date dimension is contiguous with no gaps (374 days)
VR-008	Verify country revenue shares sum to 100%

Error Handling

Condition	Action
INVOICE_DATE unparseable	Route to DWH_FACT_QUARANTINE
QUANTITY non-numeric	Route to DWH_FACT_QUARANTINE
UNIT_PRICE non-numeric	Route to DWH_FACT_QUARANTINE
QUANTITY ≤ 0	Route to DWH_FACT_EXCLUDED (bookkeeping, retained)
UNIT_PRICE ≤ 0	Route to DWH_FACT_EXCLUDED (bookkeeping, retained)
Exact duplicate row	Drop in S1 dedup (counted in audit)
InvoiceNo LIKE 'C%' / 'A%'	Drop in S2 credit filter (counted in audit)
Invalid data type after defensive cast	NULL via DEFAULT NULL ON CONVERSION ERROR → quarantine

Validation Results (live, 23/23 PASS)

Check	Category	Observed
01_RAW_ROWS_NONZERO	Row Parity	541909
02_DEDUP_REMOVED_DUPES	Row Parity	536641
04_FACT_ROW_ARITHMETIC	Row Parity	524877
05_REVENUE_MONTHLY_MATCHES_FACT	Metric Integrity	10631048.74
06_REVENUE_PRODUCT_MATCHES_FACT	Metric Integrity	10631048.74
07_REVENUE_COUNTRY_MATCHES_FACT	Metric Integrity	10631048.74
08_REVENUE_RECALC_MATCHES	Transformation Logic	10631048.74
09_NO_CREDIT_NOTES_IN_FACT	Transformation Logic	0
10_NO_NEGATIVE_QUANTITY	Transformation Logic	0
10B_NO_ZERO_OR_NEGATIVE_PRICE	Transformation Logic	0
10C_EXCLUSION_ACCOUNTED_FOR	Row Parity	527387
11_NO_NULL_KEYS_IN_FACT	Transformation Logic	0
12_CUSTOMER_ID_NORMALISED	Transformation Logic	0
13_GUEST_BUCKET_PRESENT	Transformation Logic	132185
14_DESCRIPTION_NULLS_HANDLED	Transformation Logic	0
15_DATE_DIM_CONTIGUOUS	Transformation Integrity	374
19_COUNTRY_SHARES_SUM_100	Metric Integrity	100.01
20_RFM_SEGMENTS_POPULATED	Transformation Logic	0 unassigned

Run something and the rows land here.