Data Pipeline Builder

Design ETL/data pipeline architectures for processing data from multiple sources at scale.

**Data Sources:** [APIS / DATABASES / FILES (CSV/JSON) / MESSAGE QUEUES / STREAMING PLATFORMS]
**Processing Volume:** [MB/day / GB/day / TB/day]
**Latency Requirement:** [BATCH (daily/hourly) / NEAR-REAL-TIME (<5 min) / REAL-TIME (<1 sec)]

**Pipeline Architecture Components:**

**Source Connectors:**
- API connector with OAuth2 authentication, pagination handling, and rate limit management
- Database CDC (Change Data Capture) connector for real-time sync
- File ingestion handler supporting CSV, JSON, Parquet formats with schema inference
- Message queue consumer (Kafka/RabbitMQ/SQS) with dead letter queue setup

**Data Validation Rules:**
- Schema validation against defined contracts (Avro/JSON Schema)
- Data type enforcement and null handling strategy
- Business rule validation (date ranges, value constraints, referential integrity)
- Duplicate detection using hash-based deduplication
- Anomaly detection thresholds (sudden volume spikes, missing fields)

**Transformation Logic:**
- Data cleaning: null imputation strategies, standardization of formats, normalization
- Enrichment: joining with reference data, geocoding, sentiment analysis
- Aggregation: rolling windows, group-by summaries, running totals
- Masking/anonymization for PII compliance (GDPR/CCPA)
- Custom transformation functions with version control

**Error Handling & Reliability:**
- Retry mechanism with exponential backoff (3 retries: 1s, 10s, 60s)
- Dead letter queue for records that fail all retry attempts
- Alerting on pipeline failures (Slack/PagerDuty integration)
- Data quality score tracking per run with trend monitoring
- Manual replay capability for failed batches

**Schema Evolution:**
- Schema registry for version control and compatibility checking
- Forward/backward compatibility rules for schema changes
- Automatic migration scripts for data format updates
- Deprecation warnings for old schema versions

**Monitoring & Observability:**
- Pipeline run dashboard with success/failure metrics
- Data freshness monitoring (how stale is the latest record)
- Volume tracking (records processed per hour/day with anomaly detection)
- End-to-end latency measurement from source to destination
- Cost tracking per pipeline run

**Destination Options:**
- Cloud data warehouse: BigQuery, Snowflake, Redshift with partitioning strategy
- Data lake: S3/GCS with Parquet/ORC columnar format and partition layout
- Operational database: PostgreSQL/MongoDB for real-time application data
- Analytics layer: dbt models for transformation and documentation

How to Customize It

Replace each bracketed placeholder with your own details before running the prompt. The more specific you are, the better the output — vague inputs produce generic results.

• [APIS / DATABASES / FILES (CSV/JSON) / MESSAGE QUEUES / STREAMING PLATFORMS]Swap in your apis or databases or files (csv or json) or message queues or streaming platforms.
• [MB/day / GB/day / TB/day]Swap in your mb or day or gb or day or tb or day.
• [BATCH (daily/hourly) / NEAR-REAL-TIME (<5 min) / REAL-TIME (<1 sec)]Swap in your batch (daily or hourly) or near-real-time (<5 min) or real-time (<1 sec).

When to Use It

• Consolidating scattered CSVs, APIs, and databases into a warehouse for analytics.
• Designing event processing for product analytics or ML feature generation.
• Replacing a tangle of cron jobs with an orchestrated pipeline that can actually be debugged when it fails.

Tips for Better Results

• 1Be truthful about latency needs — "real-time" requested reflexively is the most expensive word in data engineering.
• 2Ask for the failure story explicitly: what happens when a source is down, data arrives malformed, or a step must be re-run for last Tuesday.
• 3Idempotency and backfill are where pipelines earn their keep; make the design answer how re-processing works before you build.