Six months after Part 1, Fernridge Market has 12 sellers on the same three feeds (CSV, JSON and Parquet), the pipeline runs every night, and last month it broke five times: a seller added columns and the data was silently dropped, four malformed rows aborted a whole night's load, a re-sent file paid sellers twice, a mid-month commission change was applied to the whole month, and the nightly job failed at 2 a.m. without anyone noticing.
In this advanced lab you rebuild the pipeline so each failure is handled or caught. You let a Snowpipe evolve its table when a Parquet file adds columns, dry-run a dirty CSV with VALIDATION_MODE, load the good rows and send the rejected ones to a quarantine table with VALIDATE, prove that reloads, re-sent files and hand replays can never double-count thanks to load metadata, file fingerprints and a stream-driven MERGE, monitor every load with COPY_HISTORY, and raise email alerts with a notification integration and a Snowflake alert. You track seller commission history as an SCD Type 2 dimension and quantify what the wrong join cost.
Then you put Data Metric Functions with expectations on the orders table and build a production task graph: a nightly root with shared CONFIG and automatic retries, parallel children, a quality gate that stops payouts when a rule fails, a fan-in, and a finalizer that logs every run and alerts on failure. You break it on purpose with a bad file, investigate, quarantine the rows and resume with EXECUTE TASK ... RETRY LAST. You finish with an incremental dynamic table chain for serving, monitored through its refresh and graph history, and reconcile it with the task graph. Every step was run end to end on a Snowflake trial account.