CampusLearn
← BIT 405 Business Intelligence and Data Warehousing

ETL Pipelines and Data Quality

Notes

Moving and transforming data Extract, transform and load, or ETL, is a process for collecting source data, applying rules and placing prepared records into a target repository. Extraction may use complete snapshots or incremental changes since a recorded checkpoint. Transformation standardises formats, maps codes, derives measures and validates business rules. Loading must respect dependencies between dimensions and facts. Staging areas preserve intermediate records for inspection and controlled reprocessing. Pipelines should record execution identifiers, source boundaries and row counts so operators can distinguish a successful technical run from one that silently omitted important business records. Quality controls and recovery Data quality concerns accuracy, completeness, consistency, timeliness, validity and uniqueness. Validation rules should distinguish missing values from genuine zeros and reject impossible dates or amounts. Deduplication requires a documented business key rather than simply deleting records that look similar. Quarantined records are held for investigation instead of entering trusted reporting tables. Idempotent loading produces the same intended result when a batch is retried, preventing duplicated facts after partial failure. Reconciliation compares counts and control totals across stages. Assign data owners to resolve recurring source problems instead of repeatedly repairing symptoms only within the warehouse. Practical example A Thika agribusiness cooperative extracts 1,000 delivery records for a daily batch. Validation identifies 20 duplicate delivery identifiers and 30 additional records with missing farmer codes. The accepted count is 1,000 − 20 − 30 = 950, and the acceptance rate is 950 ÷ 1,000 = 95%. Staff quarantine the 50 rejected records with reason codes and compare accepted kilogram totals against source records that passed validation. A retry uses the batch identifier and delivery business key to avoid loading the same 950 deliveries twice. Corrected exceptions enter a later controlled batch with a documented audit trail.

Quiz

1. What is the acceptance rate in the example?

2. What should happen to records with missing required farmer codes?

3. Why is idempotent loading important?

Sign in to submit and securely grade your quiz.