I’m moving a small batch pipeline (S3 → Glue → Snowflake) to run every 15 minutes and want to keep spend lean without compromising data integrity — idempotent loads, schema enforcement, and deduping late CDC rows. If you’ve done similar, did partitioned Parquet with object versioning plus MERGE in Snowflake cover it for you, or did Glue bookmarks/Kafka offsets end up more reliable in practice?
I run a 15‑min S3→Glue→Snowflake feed; partitioned Parquet + S3 versioning + MERGE covered it, but we added a load_id and used a staging subquery with ‘ROW_NUMBER() over (partition by key order by updated_at desc) = 1’ so late CDC rows dedupe cleanly. We kept Glue bookmarks only to reduce reads — correctness lived in Snowflake — and if you handle deletes, carry a soft‑delete flag into the MERGE (belt‑and‑suspenders); ref: MERGE | Snowflake Documentation.
Quick tip: during COPY into your Snowflake staging, persist “METADATA$FILENAME” and “METADATA$FILE_ROW_NUMBER” (or S3 version_id) and hash them as an idempotency_key; then MERGE by business key ordered on event_ts/seqno to handle late CDC — every row gets a luggage tag you can ignore on reruns. @bcook54’s approach works, but Glue bookmarks got flaky for us when partitions were rewritten; a tiny S3 manifest per batch (and recording it in a loads table) kept spend low and made retries deterministic.
And one simple thing that kept our 15‑min S3→Glue→Snowflake loads idempotent was computing a deterministic ‘upsert_key’ in Glue (hash of business_id + updated_at) and storing it in Parquet, then MERGE on that so retries are no‑ops. Small caveat: S3 versioning on lots of tiny partitions got pricey, so we added a lifecycle rule to expire noncurrent versions after 3 days. If you expect schema drift, MATCH_BY_COLUMN_NAME in MERGE helps, and a quick COPY with VALIDATION_MODE=RETURN_ERRORS catches type mismatches before the write.
We used a Snowflake external table + stream with a Task to MERGE, which acted like a turnstile for “idempotent loads” — the stream remembers file versions, so a retry won’t double-apply. We also write a tiny per-window manifest and trigger ALTER EXTERNAL TABLE REFRESH to cut refresh lag and keep schema aligned; link: https://docs.snowflake.com/en/user-guide/streams-external-tables. If you prefer staying in Glue, at least commit a _SUCCESS + manifest per window so Snowflake only ingests complete sets.