Context

AcmeCloud, a B2B SaaS company, needs a unified customer data pipeline across Salesforce (CRM), Stripe (billing), and Zendesk (support). Today, each system is queried independently and analysts manually reconcile account IDs, causing inconsistent revenue, churn, and support-health reporting.

You need to design a production-grade pipeline that integrates these disparate systems into a single analytics model in Snowflake. The company wants both scheduled batch ingestion for full-system consistency and near-real-time updates for key customer events.

Scale Requirements

• Sources: Salesforce (~8M accounts/contacts records), Stripe (~120M charge/invoice/payment events), Zendesk (~45M tickets/comments)
• Change volume: ~2M source updates/day, with 150 events/sec peak from webhooks
• Latency targets: <15 minutes for webhook-driven updates, <4 hours for daily full reconciliation
• Storage: ~12 TB raw historical data, growing 400 GB/month
• Consumers: 60 BI users, 8 downstream data products, 3 finance dashboards

Requirements

1. Ingest data from REST APIs, bulk exports, and webhooks from all three systems.
2. Normalize disparate schemas and map records to a canonical customer_id despite missing or conflicting identifiers.
3. Support both incremental loads and backfills without creating duplicates.
4. Build analytics-ready tables for customer 360, MRR, failed payments, open support escalations, and churn risk inputs.
5. Implement data quality checks for schema drift, null keys, duplicate events, and referential integrity.
6. Orchestrate dependencies so raw ingestion, staging transforms, and marts run reliably and can be replayed.
7. Expose lineage, freshness, and load status for stakeholders.

Constraints

• AWS is the required cloud; Snowflake is already the warehouse.
• Team size is 3 data engineers; operational complexity should stay moderate.
• Budget for new infrastructure is limited to $18K/month.
• Compliance: PII must be encrypted at rest, and deletion requests must propagate within 72 hours.