Ensure Data Quality in ETL | Dataford Interview Questions

Context

LedgerLoop, a B2B SaaS billing platform, loads customer, invoice, payment, and subscription data from PostgreSQL, Stripe, and Salesforce into Snowflake for finance and product reporting. The current pipeline uses nightly Airflow jobs and ad hoc SQL checks, but stakeholders regularly find duplicate invoices, null customer keys, and mismatched revenue totals.

You need to redesign the pipeline so data quality and integrity are enforced systematically across ingestion, transformation, and warehouse publishing.

Scale Requirements

Sources: PostgreSQL OLTP, Stripe API, Salesforce API, S3 CSV partner feeds
Volume: ~120M rows/day across all sources
Batch frequency: Hourly incremental loads, nightly full reconciliation
Latency target: Source to trusted warehouse tables in < 15 minutes for hourly loads
Storage: 25 TB historical raw data, 7-year finance retention
Data quality SLA: Critical tables must maintain 99.9% valid records and zero duplicate primary business keys in curated layers

Requirements

Design an ETL/ELT pipeline that validates schema, nullability, referential integrity, and duplicate business keys before publishing curated tables.
Support idempotent re-runs for failed hourly jobs without creating duplicate records.
Implement source-to-target reconciliation for row counts, invoice totals, and payment totals.
Quarantine bad records for triage while allowing valid records to continue downstream.
Add automated tests for raw, staging, and curated layers, including freshness and anomaly checks.
Define monitoring, alerting, and recovery for failed loads, late-arriving data, and schema drift.
Explain how you would backfill 6 months of invoice history safely.

Constraints

Existing stack is AWS + Snowflake; avoid introducing more than one major new platform.
Team size is 3 data engineers and 1 analytics engineer.
Finance data is SOX-relevant, so all quality checks and manual overrides must be auditable.
Budget allows moderate warehouse scaling, but compute-heavy full refreshes should be minimized.

Problem

Context

You need to redesign the pipeline so data quality and integrity are enforced systematically across ingestion, transformation, and warehouse publishing.

Scale Requirements

Sources: PostgreSQL OLTP, Stripe API, Salesforce API, S3 CSV partner feeds
Volume: ~120M rows/day across all sources
Batch frequency: Hourly incremental loads, nightly full reconciliation
Latency target: Source to trusted warehouse tables in < 15 minutes for hourly loads
Storage: 25 TB historical raw data, 7-year finance retention
Data quality SLA: Critical tables must maintain 99.9% valid records and zero duplicate primary business keys in curated layers

Requirements

Design an ETL/ELT pipeline that validates schema, nullability, referential integrity, and duplicate business keys before publishing curated tables.
Support idempotent re-runs for failed hourly jobs without creating duplicate records.
Implement source-to-target reconciliation for row counts, invoice totals, and payment totals.
Quarantine bad records for triage while allowing valid records to continue downstream.
Add automated tests for raw, staging, and curated layers, including freshness and anomaly checks.
Define monitoring, alerting, and recovery for failed loads, late-arriving data, and schema drift.
Explain how you would backfill 6 months of invoice history safely.

Constraints

Existing stack is AWS + Snowflake; avoid introducing more than one major new platform.
Team size is 3 data engineers and 1 analytics engineer.
Finance data is SOX-relevant, so all quality checks and manual overrides must be auditable.
Budget allows moderate warehouse scaling, but compute-heavy full refreshes should be minimized.

Problem

Context

You need to redesign the pipeline so data quality and integrity are enforced systematically across ingestion, transformation, and warehouse publishing.

Scale Requirements

Sources: PostgreSQL OLTP, Stripe API, Salesforce API, S3 CSV partner feeds
Volume: ~120M rows/day across all sources
Batch frequency: Hourly incremental loads, nightly full reconciliation
Latency target: Source to trusted warehouse tables in < 15 minutes for hourly loads
Storage: 25 TB historical raw data, 7-year finance retention
Data quality SLA: Critical tables must maintain 99.9% valid records and zero duplicate primary business keys in curated layers

Requirements

Design an ETL/ELT pipeline that validates schema, nullability, referential integrity, and duplicate business keys before publishing curated tables.
Support idempotent re-runs for failed hourly jobs without creating duplicate records.
Implement source-to-target reconciliation for row counts, invoice totals, and payment totals.
Quarantine bad records for triage while allowing valid records to continue downstream.
Add automated tests for raw, staging, and curated layers, including freshness and anomaly checks.
Define monitoring, alerting, and recovery for failed loads, late-arriving data, and schema drift.
Explain how you would backfill 6 months of invoice history safely.

Constraints

Existing stack is AWS + Snowflake; avoid introducing more than one major new platform.
Team size is 3 data engineers and 1 analytics engineer.
Finance data is SOX-relevant, so all quality checks and manual overrides must be auditable.
Budget allows moderate warehouse scaling, but compute-heavy full refreshes should be minimized.

Problem

Context

You need to redesign the pipeline so data quality and integrity are enforced systematically across ingestion, transformation, and warehouse publishing.

Scale Requirements

Sources: PostgreSQL OLTP, Stripe API, Salesforce API, S3 CSV partner feeds
Volume: ~120M rows/day across all sources
Batch frequency: Hourly incremental loads, nightly full reconciliation
Latency target: Source to trusted warehouse tables in < 15 minutes for hourly loads
Storage: 25 TB historical raw data, 7-year finance retention
Data quality SLA: Critical tables must maintain 99.9% valid records and zero duplicate primary business keys in curated layers

Requirements

Design an ETL/ELT pipeline that validates schema, nullability, referential integrity, and duplicate business keys before publishing curated tables.
Support idempotent re-runs for failed hourly jobs without creating duplicate records.
Implement source-to-target reconciliation for row counts, invoice totals, and payment totals.
Quarantine bad records for triage while allowing valid records to continue downstream.
Add automated tests for raw, staging, and curated layers, including freshness and anomaly checks.
Define monitoring, alerting, and recovery for failed loads, late-arriving data, and schema drift.
Explain how you would backfill 6 months of invoice history safely.

Constraints

Existing stack is AWS + Snowflake; avoid introducing more than one major new platform.
Team size is 3 data engineers and 1 analytics engineer.
Finance data is SOX-relevant, so all quality checks and manual overrides must be auditable.
Budget allows moderate warehouse scaling, but compute-heavy full refreshes should be minimized.