How would you make an ETL pipeline auditable?

Short answer

Record what inputs were used, which code and parameters transformed them, what checks ran, and which outputs were produced, all tied to durable run identifiers.

Why

Begin with an ingestion manifest containing source identifiers, file hashes, retrieval timestamps and schema versions. Preserve immutable raw records where permitted, then separate parsing, normalization, QC and publication stages. Each run should log the software or workflow version, reference data versions, input and output counts, duplicates rejected, validation failures and operator decisions.

For sensitive research data, protect participant identifiers and log access without leaking secrets into the audit trail. Provide a way to trace a published record back to its original source and explain every revision. If a join silently multiplies rows, a row-count invariant and key uniqueness test should fail the run before publication.

Auditability is not achieved simply by saving console logs with no input provenance.

Common mistakes

Logging a success timestamp without recording source versions, transformations, QC failures or row counts

The interviewer might ask next

How would you reconstruct why one sample disappeared between raw ingestion and a released table?