ETL Service for Snowflake

Estimated Reading Time:  5 minutes

Overview

Unqork now has an ETL (extract, transform, load) solution for Snowflake. This solution delivers a robust and seamless approach to extracting and analyzing your application data. Organizations seeking to survive disruption and gain a competitive edge must embrace a data-driven culture. Doing so lets you gain valuable insights into digital processes and business strategies.

With today’s advancements, the most common issue you face is the lack of access to high-quality and readily-available data. Traditional data warehouses are often considered inefficient for modern technology requirements. They also rely heavily on BI (business intelligence) connectors.

Leveraging Snowflake

Using Unqork's ETL solution lets you extract data and send it to Snowflake as frequently as 1-2 times per day. The results of this solution are:

• Higher data control and governance: Removing the rigid structure associated with BI connectors means customers gain more power and self-serviceability of the data. With this control, customers have full data ownership for analytics.

• Streamlined configurations: This solution provides greater flexibility by removing obstacles related to BI connectors. Without BI connector dependency, you can expect optimal query performance. There is no need to build BI views for each use case. This solution provides automatic daily sync updates to your customers’ Snowflake accounts.

• High scalability and data evolution: Snowflake provides an extensible solution that seamlessly adapts to modern technology. This solution also lets you evolve your data models. Efforts needed to rework BI connectors for setups, updates, data collections, or analytics no longer exist.

• Increased security and transparency: Customers don’t have to worry about the risks and challenges associated with traditional BI connectors. They can access, analyze, and monitor data without exposing it. They also don’t need to rely on built-in data security features like password generation.

Considerations and Requirements

Before you begin using this ETL solution, there are several requirements:

• Your organization must have a Snowflake account.

• Unqork shares a view and updates the data twice daily, at a minimum.

• The data written to the share will be as-is. Unqork is not responsible for the misuse of data. Unqork is also not responsible for administrating the read-only database beyond the initial creation of the share and the ongoing curation of additional data.

• Currently, we can only enable a limited number of clients.

• Access to the solution depends on a first-come, first-serve basis.

• You are responsible for security approval for extracting application data from Unqork.

Other considerations include additional costs associated with the solution. The price depends on your use case and the following considerations:

• The number of environments using the solution.

• The number of times the data updates daily.

  NOTE  The standard price structure updates the data twice per day.

• The size of the data. The price increases when the data exceeds 100 GB.

TIP  Contact your CSM representative for more information on pricing and using the solution.

Procedures For Onboarding

Here are a few notes about onboarding your Snowflake ETL service:

• You need a Snowflake account locator, a cloud provider, and a cloud provider region where your Snowflake account resides in.

• Unqork creates the share and primes it with an initial data load.

• Create a read-only database from the share in your Snowflake account. Then, Unqork shares a view and updates the data twice daily.

Out of Scope

Below is a list of limitations associated with the ETL solution:

• Unqork doesn't provide any Snowflake sub-accounts at this time.

• Unqork doesn't commit real-time updates on Snowflake data warehouses.

• Application data and submission records are in the form of JSON objects.

• Attachments for submissions are not available at this time. You cannot extract attachments through the ETL process.

• Snowflake provides various BI analytics applications. We encourage you to use these applications for analytics and insights.

TIP  For more information about querying JSON objects using Snowflake, see their https://docs.snowflake.com/en/.

How to Use the ETL Solution

After Unqork initiates the share and primes it with an initial data load, you must create a read-only database in your Snowflake account. To create the database, you must have Account Admin privileges. Once created, data loads occur one to two times per day.

Application data is written as-is to the Snowflake shared database. For example, data columns return deepnested JSON objects as-is. Use Snowflake’s powerful capabilities to query data with SQL (standard query language) to return specific fields and rows of data.

Accessing Application Data Using Snowflake

Use Snowflake’s powerful SQL capabilities to query data. In the image below, the SQL command queries the raw Unqork submissions data, limiting it to the first 10 submissions.

In the image below, the RAW_DATA is a deep-nested JSON object displayed as-is. But the FORM_ID column is formatted to a display a specific field. This SQL command casts, renames, and orders the data into 2 distinctive columns.

TIP  For more information on using SQL with Snowflake, see their command reference https://docs.snowflake.com/en/sql-reference-commands.html.