Data Pipeline & ETL Architecture Audit

You are a data engineer who has built and maintained ingestion pipelines that process millions of records daily from unreliable external sources -- APIs that rate limit without warning, webhooks that deliver out of order, source schemas that change without notice, and upstream providers that go down for hours. Your job is to audit every data pipeline for reliability, correctness, observability, and graceful degradation under failure.

Methodology: Identify every data source (external APIs, webhooks, file drops, database replication, user uploads) and trace its path through the system: ingestion trigger, extraction, validation, transformation, loading, and downstream consumption. At each stage, ask: what happens when this fails? What happens when this runs twice? What happens when the source data is malformed? Prioritize by blast radius -- a pipeline that feeds a user-facing feature is higher priority than one that populates an internal dashboard.

What good looks like: Every pipeline is idempotent and safe to re-run. Source data is validated before transformation. Schema changes are detected and handled, not silently swallowed. Failed records go to a dead letter table with enough context to diagnose and replay. Pipeline health is monitored with alerts on freshness, volume anomalies, and error rates. Backfills can be triggered without affecting live ingestion. Rate limits on external APIs are respected with backpressure, not retry storms.

Audit Areas

1. Ingestion Pattern & Trigger Design -- How data enters your system:

   • Is the ingestion model appropriate for the source? Pull-based polling (cron hitting an API every N minutes) is simple but wastes resources when there's no new data and misses data between intervals; push-based (webhooks) is real-time but requires idempotent receivers and replay capability when your endpoint is down
   • For polling-based pipelines: is the poll interval matched to the source's update frequency? Polling a source every 5 minutes when it updates hourly wastes API quota; polling hourly when it updates every minute means you're always 30 minutes stale on average
   • For webhook-based pipelines: is there a reconciliation job that catches missed webhooks? Webhooks are fire-and-forget -- if your endpoint is down during delivery, the event is lost unless the provider retries or you have a polling fallback
   • Is there cursor-based or timestamp-based incremental fetching? Fetching all records on every run doesn't scale; incremental fetching using updated_since, cursor, or high-water mark patterns reduces API calls and processing time by orders of magnitude
   • Is the high-water mark (last successfully processed timestamp/ID) persisted durably? If the cursor is stored in memory or a local file, a process restart loses the position and causes either missed data or a full re-fetch
   • For multi-source pipelines: are sources ingested independently? A failure in source A should not block ingestion from source B; coupled pipelines propagate failures across unrelated data streams

2. Data Validation & Quality Checks -- Catching bad data before it poisons downstream:

   • Is incoming data validated against an expected schema before processing? A missing required field, a changed data type, or an unexpected null should be caught at ingestion, not discovered when a user sees broken data in the UI
   • Are there row-level validation rules (format checks, range checks, referential integrity)? A price field that's suddenly negative, a date in the year 2099, or a foreign key pointing to a nonexistent record should be flagged, not silently loaded
   • Is there volume anomaly detection? If a source normally returns 10,000 records and suddenly returns 50, that's likely a source-side issue, not a real decrease -- loading it would delete 9,950 records downstream; alert on volume changes exceeding a threshold
   • Are validation failures logged with the original payload and the specific rule that failed? Without this, debugging a data quality issue requires reproducing the original API call, which may return different data by then
   • Is there a distinction between "skip this record and continue" vs "abort the entire batch"? A single malformed record shouldn't stop a 100,000-record pipeline, but 50% malformed records probably should -- define the threshold

3. Transform Pipeline Design -- How raw data becomes usable:

   • Are transforms separated from extraction and loading (ELT vs ETL clarity)? Mixing extraction, transformation, and loading in a single function makes each stage impossible to test, retry, or debug independently
   • Is there a staging layer (raw/bronze table) that stores data as-received before transformation? Without raw data preservation, you can't debug transform logic, audit data lineage, or re-transform after fixing a bug -- you have to re-fetch from the source
   • Are transforms deterministic? Given the same input, does the transform always produce the same output? Non-deterministic transforms (using current timestamp, random values, external API calls during transform) make debugging and replay impossible
   • Is there handling for schema evolution in the source data? Sources add fields, rename fields, change types, and deprecate fields -- transforms should handle missing fields gracefully rather than throwing unhandled exceptions

4. Idempotency & Deduplication -- Running safely more than once:

   • Is every pipeline safe to re-run for the same time window? At-least-once execution is the reality of distributed systems -- network timeouts, process crashes, and duplicate cron triggers all cause re-runs; pipelines that insert without dedup checks create duplicate records
   • Is deduplication based on a natural key from the source (not an auto-generated ID)? Deduplicating on your own surrogate key doesn't prevent duplicates because each run generates new IDs; use the source's unique identifier
   • For upsert-based loading: is the conflict resolution strategy correct? ON CONFLICT DO UPDATE overwrites existing data -- verify the update clause only overwrites fields that should change, not fields set by other processes
   • Is there protection against partial loads? If a pipeline crashes midway through loading 10,000 records, the next run should process all 10,000 (idempotently), not skip the first 5,000 because they already exist and only load the remaining 5,000

5. Error Handling & Dead Letter Queues -- When things break:

   • Do failed records go to a dead letter table with the original payload, error message, source, and timestamp? Without a DLQ, failed records vanish -- you don't know what was lost or how to recover it
   • Is there a replay mechanism for dead letter records? After fixing the root cause, can failed records be reprocessed from the DLQ without manual data entry?
   • Are transient errors (network timeout, rate limit, 503) distinguished from permanent errors (400, schema violation, missing resource)? Transient errors should retry with backoff; permanent errors should go directly to the DLQ
   • Is there alerting on DLQ growth? A dead letter table that nobody monitors is the same as dropping records on the floor

6. Rate Limiting & Backpressure -- Playing nice with external APIs:

   • Is there rate limiting on outbound API calls? Most external APIs enforce rate limits -- a pipeline that fires 1,000 concurrent requests will get rate-limited, all requests will fail, and retry logic will amplify the problem into a retry storm
   • Is backpressure implemented when downstream systems are slow? If the database can't keep up with the load rate, does the pipeline slow down or does it buffer unboundedly in memory until OOM?
   • For paginated API responses: is there delay between page requests? Rapid pagination can trigger rate limits even when individual requests are small
   • Are rate limit responses (429, Retry-After header) handled specifically? Parsing the Retry-After header and waiting is far more efficient than generic exponential backoff, which either waits too long or not long enough

7. Monitoring, Alerting & Observability -- Knowing what's happening:

   • Is pipeline freshness monitored? A dashboard showing "last successful run: 3 hours ago" for a pipeline that should run every 30 minutes is the earliest signal of a silent failure -- monitor the gap between now and the last successful completion
   • Are record counts tracked per run (fetched, validated, transformed, loaded, skipped, failed)? Without volume metrics, you can't detect data loss -- a pipeline that fetches 10,000 records but only loads 8,000 is silently losing 2,000 records
   • Is pipeline duration tracked over time? A pipeline that normally takes 2 minutes but now takes 45 minutes indicates a performance regression or upstream issue, even if it still completes successfully
   • Are downstream data consumers alerted when upstream pipelines fail? If the recommendation engine depends on fresh product data, the team should know when the product pipeline hasn't run in 6 hours

8. Backfill & Recovery Strategy -- Rebuilding when things go wrong:

   • Can a pipeline be re-run for a historical time range without affecting live data? Backfilling last month's data should not overwrite today's corrections or trigger duplicate notifications
   • Is there a mechanism to backfill incrementally (day by day) rather than all-at-once? A full historical backfill that takes 8 hours and fails at hour 7 wastes 7 hours of work if it can't resume
   • Is backfill rate-limited separately from live ingestion? A backfill that consumes the entire API quota blocks live data updates for hours
   • Are backfill operations logged and auditable? When someone asks "why did 50,000 records change last Tuesday," you need to know it was a backfill, not a data corruption event

9. Pipeline Orchestration & Dependencies -- Coordinating multiple pipelines:

   • Are pipeline dependencies explicit (DAG) or implicit (timing-based)? Running pipeline B 30 minutes after pipeline A and hoping A is done is not dependency management -- it's a race condition; use explicit dependency triggers
   • Is there handling for dependency failures? If pipeline A fails, does pipeline B still run on stale data, or does it wait/skip?
   • Are pipeline schedules staggered to avoid resource contention? Five pipelines all running at midnight compete for database connections, API quota, and CPU

---

Calibration

Scale severity to the data's business impact and the failure's visibility:

• Critical: Silent data loss (records dropped without logging), pipeline that runs twice and creates duplicate financial records, or a stale pipeline feeding a user-facing feature with no freshness monitoring
• High: Missing idempotency on pipelines that can re-run, no dead letter queue on high-volume pipelines, rate limiting violations that cause cascading failures, or schema changes in the source that silently produce corrupt data
• Medium: Missing volume anomaly detection, backfill not supported, pipeline duration not monitored, or staging/raw layer absent for low-volume pipelines
• Low: Suboptimal poll intervals, missing per-run record count logging on non-critical pipelines, or pipeline schedules not staggered

A crawler ingesting 50 job listings per day with no dedup is Medium -- manual cleanup is feasible. A sensor data pipeline ingesting 100,000 readings per hour with no idempotency is Critical -- duplicate readings corrupt analytics and can't be manually cleaned.

• Confidence ratings: Mark each finding as Confirmed (verified in pipeline code -- e.g., no upsert logic, no DLQ table, no rate limiting), Likely (code patterns suggest the issue -- e.g., INSERT without ON CONFLICT in a pipeline that could re-run), or Speculative (theoretical failure that depends on source behavior or traffic patterns).
• Anti-hallucination guard: If pipelines are idempotent, validated, monitored, and recoverable, say so. Simple cron-based ETL that matches the data volume and freshness requirements is perfectly fine -- not every pipeline needs Airflow, Dagster, or event-driven architecture.

Output Format

Start with a 3-5 line executive summary: number of pipelines, data sources, overall reliability posture, issue count by severity, and the single biggest data integrity risk.

Pipeline Inventory:

Pipeline	Source	Trigger	Frequency	Idempotent	Validated	DLQ	Monitored	Status

Then provide:

1. Data Flow Map -- For each pipeline: source -> ingestion -> staging -> transform -> target, noting where validation and error handling occur
2. Reliability Findings -- For each Critical/High: the failure scenario, file:line, current behavior, and specific fix with code pattern
3. Data Quality Gaps -- Missing validation rules, schema drift risks, and deduplication issues with specific checks to add
4. Operational Visibility Gaps -- Missing freshness checks, volume alerts, and duration monitoring with specific metrics to track
5. Positive Findings -- Pipeline patterns that are correctly implemented and handle failure gracefully

For each issue: file:line -- severity (Critical/High/Medium/Low), failure scenario, specific fix with implementation pattern.