When we built the Snowflake adapter for Ajilius, around two years ago, we saw Snowflake as a Data Warehouse target.
We’re now seeing many Snowflake customers using its capabilities as a Data Lake as well as a Data Warehouse. Customers are landing their data in one Snowflake database, then migrating and structuring it for analysis and reporting.
We have responded to this shift by implementing cross-database queries for data ingestion by Ajilius.
Previously, customers could extract data from one Snowflake database, then reload it to another. If your Ajilius instance was running on-premise, this carried a significant overhead as the data was extracted, transferred to your Ajilius instance, transferred back to the Snowflake host, and reloaded.
Now, a new load type of “Cross-Database” can be selected for a table. When the table load job is executed, we create a three-part name query instead of an extract/load query.
Here’s how it works.
Create a data source in the normal way. Let’s use the SNOWFLAKE_SAMPLE_DATA that comes with a new account:
After refreshing metadata, we can see the tables and columns from the demonstration database. Now we can load the CALL_CENTER table:
Set the characteristics of the table. The Load Method will default to Bulk Import, which uses the Snowflake PUT/COPY method. Change this to Cross-Database Query:
Now, when you generate scripts for this table, you’ll see that a cross database query is used instead of a PUT/COPY. Note that some columns have been dropped in order to show the full query.
Of course, incremental loads are also supported for cross-database loads.
Ajilius. Faster loads for Snowflake-Snowflake.