Explain ETL Process | Dataford Interview Questions

Explain the ETL process.

Use the provided staging and reference tables to demonstrate extraction, transformation, validation, and preparation of load-ready rows. Include records marked READY, preserve data-quality issues with an explanatory status, and exclude records that are not ready.

Output

One row per extracted sale, with sale_id, customer_name, sale_date, amount, sale_month, and quality_status.
Include valid, missing-customer, invalid-amount, and negative-amount records. Sort by sale_date, then sale_id.

Output details

Sort sale_date and then sale_id in ascending order.
Format sale_month as YYYY-MM.
Replace missing customer_name values with UNKNOWN.
Set amount to NULL for nonnumeric text; preserve numeric values without rounding, including zero and negatives.
Label statuses exactly MISSING_CUSTOMER, INVALID_AMOUNT, NEGATIVE_AMOUNT, and VALID, prioritizing missing customer over amount issues.

Schema

staging_sales

Column	Type	Description
sale_idPK	INT	Source sale identifier
customer_id	VARCHAR(10)	Source customer identifier
sale_date	DATE	Date associated with the sale
amount_text	VARCHAR(30)	Sale amount received as text
source_status	VARCHAR(20)	Source processing status

customer_master

Column	Type	Description
customer_idPK	VARCHAR(10)	Reference customer identifier
customer_name	VARCHAR(100)	Reference customer name
customer_segment	VARCHAR(30)	Customer classification

Your question is Explain ETL Process. Start with the requirements and the two tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem