Data is loading again. How do you prevent data from being duplicated if the same file is loaded multiple times? What mechanisms can you implement to delete or ignore duplicate data after it has been loaded?

To make an ingestion pipeline idempotent and avoid duplicate rows, you can use a combination of unique identifiers, watermarking, and deduplication logic. First, ensure each record has a natural or surrogate key (e.g., a UUID or a composite of business fields). When loading a file, compute a hash of the key or the entire row and store it in a staging table. Use a MERGE statement (or UPSERT) to insert new rows and update existing ones based on the key. If you only want to insert new rows, use a NOT EXISTS check or a LEFT JOIN to the target table. In Snowflake, you can also leverage the TIME TRAVEL feature to keep a history of changes and roll back if needed. For large datasets, consider partitioning by a date column and using a deduplication window (e.g., keep only the latest record per key within a day). Finally, schedule the job to run only when new files are detected (e.g., using S3 event notifications) and maintain a manifest table that records processed file names and timestamps to avoid reprocessing the same file. This approach keeps the pipeline idempotent, ensures no duplicate data, and allows easy rollback if a bad load occurs.