CSV Column Structure Evolution
Hi all,
I'm stuck with a client's request.
Context: Ingestion of CSV files from a S3 bucket to a table in Snowflake. In addition, I need to add three columns, one with the current date, one with the name of the csv file, and one with the date retrieved from the csv file.
Issue: The client told me that the structure of the files could change in future, for example by adding one or more columns to the structure.
I already create a table with the desired columns and it works at the moment. In the future, some columns might be added:
- If they're added at the end, it stills work.
- But if they're added in the middle of the structure or in random positions, all the data will be shifted.
I tried with MATCH_BY_COLUMN_NAME but due to the three new columns that i have to create (cause in two of them I use metadata$filename), it doesn't work.
I also tried with schema evolution, but I obtain the same result as above.
Do you know if there is a solution to these problems or if you know some similar cases that have been resolved?
thank you :)