Oracle-Snowflake Connector

What is Oracle-Snowflake Connector ?

It’s a generic API tool developed by Database Consulting team enabling to connect from Oracle Database to Snowflake Cloud Data Warehouse.

It allows querying, modifying the data as well as creating and changing objects on the Snowflake without the need for an additional layer between Oracle database and Snowflake. It gives the opportunity to transparently incorporate data processing using state-of-the-art Snowflake solution as a part of the existing Data Warehousing processing or an enhancement to it. Oracle to Snowflake connector makes it possible to create POC comparing performance between running the logic on Snowflake and running it on Oracle without involving any additional tool. With a minimal effort, the existing code can be transformed into a version that enables running it on Snowflake instead of Oracle.

Oracle-Snowflake Connector is freeware under the licence.

Oracle-Snowflake Connector documentation.

How Oracle-Snowflake Connector can be used?

After installing the Oracle-Snowflake Connector in a couple of simple steps it can be used to perform several database operations on Snowflake and Oracle datasets in a transparent way

  • Select and return data from Snowflake to Oracle.
  • DML (update, delete, insert) data on the Snowflake db
  • Insert and Bulk collect insert from Oracle to the Snowflake db
  • Run JavaScript procedure stored on Oracle Database
  • Modify and create objects on the Snowflake db by operating on them from Oracle: DDL (create, drop, alter)
  • Enables query monitoring (status) on the Snowflake side

Examples of using Oracle-Snowflake Connector:

Case Study #1

The current state: A system primarily used to store and process user transactions is now expected to return on demand some basic back in time analysis for the client. Not only all the customer transactions should be processed and stored at the minimum time but also, the customer can perform some analysis on her/his account data which involve grouping and sorting huge amounts of historical data.

The solution:

Oracle is set up and treated as OLTP database, tuned for the high transaction volume with minimum response time. Snowflake is used to return to the client an output from the analysis which involves the aggregation of  high volume of historical data  (ie. sum of all the transactions from the last n years grouped by the categories).  The application uses Oracle and PL/SQL stored procedures as a single point of contact thanks to using Oracle-Snowflake Connector to get the results from Snowflake.

Case Study #2

The current state:

The current system is heavily loaded with batch jobs and regular reporting. Due to a switch in the company business scope arises a need to create new reporting which enables taking business decisions taking into consideration the switched perspective. The existing reporting needs to be maintained in order to keep the ongoing business running.

The new business challenge requires creating a set of reports using different granularity and grouping sets than the existing processing logic.

The solution:

The current processing stays untouched while new report gets developed using data transferred to the Snowflake Cloud Data Warehouse. An Oracle-Snowflake Connector is used to connect to Snowflake and return the result of aggregations. The new reporting can be developed as a part of the existing DWH solution and the results are stored and accessed by the application used by the business users. The change is transparent for the systems and applications used to publish and distribute reports among the business users.

Oracle-Snowflake Connector:

  • Provides SQL and PL/SQL API to connect to Snowflake directly from Oracle Database
  • Allows to run any valid Snowflake SQL query against Snowflake data directly from Oracle Database
  • Enable further procesing of Snowflake queries results using Oracle SQL and PL/SQL
  • Allows to run DML statements against Snowflake tables directly from Oracle Database (from within SQL as well as PL/SQL)
  • Allows to run single-row and bulk inserts into Snowflake tables directly from Oracle Database
  • Allows to run Snowflake JavaScript stored procedure from Oracle Database
  • Allows to run DDL in Snowflake from Oracle Database
  • Allows to run TCL in Snowflake from Oracle Database
  • Supports both synchronous and asynchronous Snowflake command execution from Oracle Database
  • Monitor asynchronous Snowflake queries statuses and fetch its results from Oracle Database