We have an environment where we process up to 10 million transactions per day and need to migrate the data from our transactional database to an analytical database like Snowflake nightly. The data is flaky, and for about 10 days a year we see higher bandwidth and throughput. How would you design the data pipeline to handle this scenario?

Designing a nightly data pipeline that moves up to 10 million transactions from a transactional system to Snowflake while handling flaky data and occasional spikes requires a robust, incremental, and fault‑tolerant architecture. A common pattern is to use CDC (Change Data Capture) or a lightweight log to capture only new or changed rows. The raw change events are written to an S3 data lake (or an equivalent object store) in a partitioned folder structure (e.g., /year/month/day). An Airflow DAG orchestrates the flow: a “capture” task writes the raw data, a “transform” task runs Spark or Glue to clean, deduplicate, and enrich the data, and a “load” task pushes the results into Snowflake using Snowpipe or a bulk load. For the 10 high‑throughput days, the pipeline can switch to a full load or a larger batch size, while for normal days it uses incremental loads. Schema drift is handled by a Glue crawler that updates the Snowflake table schema automatically. Data quality checks (null counts, range checks) are performed in the transform stage, and any failures are sent to a dead‑letter queue. Monitoring is built into Airflow and Snowflake (query history, storage metrics). The design is scalable, cost‑effective, and resilient to flaky data.