Aster Supply incremental landing
The ingestion lead needs a repeatable load from the supplied JSON landing directory, a curated changes table and a current-order upsert. Deliver a gold change summary and an audit count without duplicating the same file on rerun.
Aster Supply's landing directory contains a change batch rather than a full order snapshot. Your job is to land it once, preserve file-level repeatability and apply the monetary validation rule before updating current orders. The final output must make the accepted data and its count visible.
Each step builds on the saved tables from the previous step. COPY INTO remembers the directory's file identity inside the session. A second run should not append those files again. That guarantee does not deduplicate a business record arriving in a different file, so the downstream current-order table uses MERGE. Keep that boundary explicit. The supplied change batch has known nonnegative amounts; applying its validation rule does not prove that an unseen malformed file would parse successfully. Publish the changed-batch total only after the curated feed and keyed upsert exist.