Access historical injected data of COPY INTO command

[N_M]

Dear Community,

I'm using the COPY INTO command to automate the staging of files that I get in an S3 bucket into specific delta tables (with some transformation on the fly).

The command works smoothly, and files are indeed inserted only once (writing idempotency works fine). The documentation says that filenames are written in a form of key:value in some RockDB.

The fact is that I need to access the (new) staged filenames in the workflow, and the idea is to look into the metadata or transactional logs rather than in the table itself (that is huge). Unfortunately, the table history does not contain this information. So my questions are:

• is it possible to access the inserted filenames history metadata?
• how long is the retention period of such information? (I'm asking because apparently SNOWFLAKE has the same COPY INTO command with identical features, but the documentation clearly says that historical information is stored for 64 days, after that, they are forgotten and COPY INTO will re-stage the files, if found...)

Can you help me?

[N_M]

hi @Retired_mod 

thanks for your detailed answer. As you said, unfortunately this doesn't solve my issue, given that what you post is about Snowflake COPY INTO, and not Databricks one.

Unless this can used also in the databricks version, and I didn't get it.

Moreover, regarding the retention period, I quite don't get what you wrote. Snowflake has 64 or 14 retention days period?