A user has 10 files in a stage containing new customer data. The ingest operation completes with no errors, using the following command:
COPY INTO my_table FROM @my_stage;
The next day the user adds 10 files to the stage so that now the stage contains a mixture of new customer data and updates to the previous data. The user did not remove the 10 original files.
If the user runs the same COPY INTO command what will happen?
Snowflake's COPY INTO command, by default, tracks and maintains metadata about files that have been ingested. This means that any previously loaded files will not be reloaded unless the FORCE option is explicitly specified. Therefore, in this case, only the data from the newly-added files will be appended to the table, ensuring that data from the original 10 files is not duplicated.
https://docs.snowflake.com/en/user-guide/data-load-considerations-load.html#executing-parallel-copy-statements-that-reference-the-same-data-files
D - only the new files will be appended B - SF doesn't know which customer is new (COPY doesn't care about the meaning of data, care about files (file names) which are new (no kept in metadata as loaded)
D - COPY command maintains historic load metadata with target table , so day 1 , 10 files will will not be loaded again
D is correct
D is correct answer
only the new files will be appended
https://community.snowflake.com/s/question/0D50Z00009T3JWXSA3/upload-data-to-table-and-avoid-duplicated-data-rows
Copy into not reload the same file more times if you don't set force = true https://docs.snowflake.com/en/sql-reference/sql/copy-into-table.html#reloading-files - Reloading Files Add FORCE = TRUE to a COPY command to reload (duplicate) data from a set of staged data files that have not changed (i.e. have the same checksum as when they were first loaded).
Once the files are copied into a table, they cannot be copied again in the next 64 days because of the files’ metadata. If this option is true, it loads all files, regardless of whether they’ve been loaded previously and have not changed since they were loaded. https://plazagonzalo.medium.com/snowflake-snowpro-data-loading-f88afaef7eb9#cf88
what s the difference between B) and D) the meaning is the same, only data from new files will be added...
B: the new files could contains both old and new data.
B Mentions only new customers from the new files meaning any duplicates in new files will be removed while loading which is not true. All the data from new files will be loaded as LOAD HISTORY is maintained by snowflake. So D is correct answer with this question.
D is correct Loading Older Files¶ This section describes how the COPY INTO <table> command prevents data duplication differently based on whether the load status for a file is known or unknown. If you partition your data in stages using logical, granular paths by date (as recommended in Organizing Data by Path) and load data within a short period of time after staging it, this section largely does not apply to you. However, if the COPY command skips older files (i.e. historical data files) in a data load, this section describes how to bypass the default behavior. https://docs.snowflake.com/en/user-guide/data-load-considerations-load#executing-parallel-copy-statements-that-reference-the-same-data-files