We have a separate application (lets call it APP-X) that is turning over a snowflake task to our application. APP-X currently has an AWS s3 external staging setup in their Snowflake database - where every csv file received by APP-X are uploaded to external stage. APP-X then runs a Snowflake COPY INTO statement to load each of the csv file data (from s3 staging area) into the corresponding Snowflake tables. APP-X currently does this as a manual process. Each of the APP-X tables has a “LOAD_DATE” field (same value across all records) which is derived from a column with the same name from the respective csv file.
What I like about COPY INTO is that it has a built-in feature to prevent duplicate data loading.
Now that the process is being turned over to our application, our team architect suggests using python script to automate the tasks above. I have no problem automating the uploading csv files to s3 bucket (aws credentials were provided to us) via external staging in the python script, but the issue lies in automating the COPY INTO because, while APP-X DBAs granted us USER privileges to directly execute COPY INTO on their database /tables, the DBAs would not provide us the DB access credentials which are required in automating the Snowflake database connection via python scripting.
Questions:
Right now I’m toying with the idea of CLONING the APP-X tables from their database to ours, and replace the COPY INTO with logic to check if the “LOAD DATE” value already exists in the tables to avoid data duplication. I’d like to think that this logic should sufficiently ensure data integrity, but would like to hear Snowflake experts’ opinion on this as well?
Also, with regards to CLONING - the requirement also calls for copying all the existing data in the APP-X tables. I believe CREATE TABLE - CLONE would automatically create a copy of these data (some of them have millions of rows) - is that right? Would it be possible to clone the tables from the APP-X database to our application database? Is there something I need to request from APP-X DBAs to make this happen?