2
0
During this post we will discuss how to allocate warehouse dynamically to load the files into Snowflake. Consider a scenario you have some defined tables in your snowflake database. As part of the feed process, we are getting files from respective source systems in same bucket. The size of each file is varying according to the nature of load. In current scenario the snowflake account is leveraging only XS warehouse for all files. Now Business has categorized the two different warehouses based on the business domain i.e. XS for Accessory related and S for Customer Invoices.
So as per the latest ask from business, we need to determine the size of file from the bucket and use the respective warehouse for ingestion process. Say for our POC if the size of file is less than 5MB use XS warehouse. In case the file is bigger in size then use S warehouse dynamically.
In addition to it, the next challenge is constructing the COPY statement and initially we thought we could develop multiple COPY commands w.r.t each Table in procedure. However, we have ‘N’ number of tables in our database and creating the separate COPY syntax for each table is not acceptable overall. The Table names in our database are aligned with the Filename, (of course excluding the File extension in table name). So, we need to construct the COPY command based on the filename we receive from the source system. Once the COPY command is finalized, determine the size of file and execute with the respective warehouse.
Technical Details:
Below procedure is created to cater this requirement:
- Extract the Table name from the File received in bucket.
- Construct the COPY command.
- Evaluate the size of file and allocate Warehouse dynamically.
We have following tables in Database and respective files in AWS bucket:
Procedure:
Call the Procedure:
Verify the Query history:
