Library nightly feeds include new acquisitions and updates to existing catalog items. Which load pattern should silver use?

Library nightly feeds that mix new acquisitions and updates to existing catalog items call for MERGE (upsert) into silver: match on catalog business keys, update changed titles or metadata, and insert rows that do not yet exist. MERGE keeps the catalog current without discarding history of keys you already curate and without blindly duplicating every feed row. That pattern is the standard curated load for slowly changing municipal reference-like collections.

COPY INTO silver catalog table from nightly feed to update existing rows and insert new acquisitions cannot update at row level — COPY INTO only inserts data from new files instead of matching on catalog business keys, so an item needing only an updated title keeps its stale row and gains a duplicate alongside it. Auto Loader to ingest nightly feed into silver catalog to update existing and insert new acquisitions never updates on its own — Auto Loader incrementally ingests files as they arrive, so without a downstream MERGE it only inserts and cannot apply matched updates to titles you already curate. MERGE the nightly feed into gold catalog table to update existing rows and insert new acquisitions hits the wrong target — gold serves aggregated consumption instead of maintaining one current-state curated silver table, leaving downstream still without a clean current catalog.

Exam caveat: MERGE/upsert targets a current-state curated table fed by an incremental nightly feed; COPY INTO inserts new files without matching to update existing rows, Auto Loader ingests incrementally without updating matched rows, and merging into gold upserts the consumption layer rather than one current silver catalog. COPY INTO-only ingestion belongs to immutable file intake for bronze, not current catalog silver that requires matched updates.

Operational check: Seed a catalog item, send a nightly feed that updates its title and adds a new ISBN, run MERGE, and confirm one updated row plus one inserted row. Ensure COPY INTO and Auto Loader-only loads that cannot update existing rows are not scheduled as the final load. Validate primary key uniqueness and that outdated titles do not remain after matched updates.