In ETL pipeline design, what are the most critical aspects you focus on to track data lineage, and can you write an SQL query to implement a Slowly Changing Dimension (SCD) Type 2?

In an ETL pipeline, tracking data lineage is critical for auditability, debugging, and compliance. Key practices include: 1) Capturing source metadata (file name, timestamp, schema version) in a staging table; 2) Logging transformation steps and any errors to a central audit log; 3) Using a metadata store or catalog to map source columns to target columns; 4) Implementing change data capture (CDC) to record inserts, updates, and deletes; and 5) Versioning the pipeline code and configuration. For a Slowly Changing Dimension (SCD) Type 2, you maintain historical records by adding effective‑from and effective‑to timestamps and a current flag. A typical SQL implementation is:

INSERT INTO dim_customer (customer_id, name, phone, email, address, eff_from, eff_to, is_current)

SELECT

src.customer_id,
src.name,
src.phone,
src.email,
src.address,
CURRENT_TIMESTAMP AS eff_from,
NULL AS eff_to,
TRUE AS is_current

FROM

staging_customer src

LEFT JOIN

dim_customer tgt

ON

src.customer_id = tgt.customer_id
AND tgt.is_current = TRUE

WHERE

tgt.customer_id IS NULL
OR tgt.name <> src.name
OR tgt.phone <> src.phone
OR tgt.email <> src.email
OR tgt.address <> src.address;

-- Mark previous record as historical

UPDATE dim_customer

SET eff_to = CURRENT_TIMESTAMP, is_current = FALSE

WHERE customer_id = :customer_id

AND is_current = TRUE

AND (name <> :name OR phone <> :phone OR email <> :email OR address <> :address);