Get Extended Connectivity to Snowflake Database with Astera Centerprise

By |2020-07-17T05:42:12+00:00July 17th, 2020|

Snowflake is a relational SQL data warehouse, developed on the cloud. It is widely used by data professionals across various industries to store enterprise data and facilitate data sharing. Snowflake’s ability to process analytics data makes it suitable for business users looking to generate insights for better, quicker business decisions. The database is used as a central point for ETL and data pipelines in various data operations due to its unique architecture that enables independent scaling for storage, computing, and services.

As a cloud-based data warehousing solution, Snowflake is primarily used for higher-level reporting. To utilize this repository for operational tasks such as processing customer orders or sales operations, integration with other on-premise and cloud systems is essential. Once brought together in Snowflake, the data can provide a consolidated view of enterprise data.

Astera Centerprise offers seamless integration with the Snowflake database through a built-in connector in a drag-and-drop interface. You can easily connect to your backend enterprise systems and applications, like Salesforce, SQL Server, SAP HANA, and more, with Snowflake without writing any code or switching platforms.

In addition to connecting to this cloud data warehouse, Centerprise also enables business users to clean, standardize, and manipulate raw data so that higher quality data is available for sharing within and beyond the organization, in addition to reporting, analysis, and BI.

Enabling Code-Free Snowflake Integrations with Astera Centerprise

By offering native connectivity to Snowflake, Astera Centerprise makes it easier for the user to configure the database as a source or destination  for data retrieval and loading, respectively The connectivity is extended to support other functionalities in Centerprise, such as:

  • SQL Query Source – Using SELECT statements in SQL query or stored procedures, you can fetch data from the Snowflake database.
  • Database Lookup – This feature can lookup values from one or more Snowflake database tables and return them as output field(s) from the record in which the lookup value(s) matched the incoming ones.
  • SQL Query Lookup – It allows you to look up values in Snowflake using SQL query statements.
  • Pushdown Optimization – You can reduce process execution time by pushing down data to the Snowflake database. The processing load is handled by the database’s native computing capability instead of the Centerprise server, which optimizes performance.

The built-in Centerprise connector bridges the gap between the Snowflake database and disparate file formats, database systems, and online applications, allowing code-free and hassle-free integrations.

Here’s how Centerprise can help you leverage the data stored in Snowflake:

Simplifying Data Retrieval

By configuring the Snowflake database as a source object in Centerprise, you can retrieve data from it, process it according to the business requirements, and transfer it to your destination system.

Here’s a simple dataflow that reads customer data from Snowflake, filters it based on the Contact Title field, and writes it in an Excel file.

Figure 1: A dataflow that uses Snowflake at the source point

Figure 1: A dataflow that uses Snowflake at the source point

Configuring the source connector for Snowflake is as simple as dragging-and-dropping the database icon from the Toolbox and entering connection details. The screenshot below shows that we have selected the data provider as Snowflake <Beta> and entered its account credentials.

Figure 2: Configuring connection to the Snowflake database

Figure 2: Configuring connection to the Snowflake database

Once the connection is configured, you can select the required database table and specify the reading strategy in the Properties window. In Centerprise, you can either retrieve data at the same time or incrementally. Here, we have selected the Full Load option.

You can even divide the incoming data into partitions, and Centerprise will read it according to the number of partitions specified. Partitioned reading is used to restrict the amount of data scanned by each query, thus improving performance and reducing load on the database.

Figure 3: Configuring database reading options

Figure 3: Specifying database reading options

In this source database connector, you can also specify the parameters that should be assigned values from the config file. Upon running the dataflow, the corresponding property values will be overridden by the config parameters, without users having to make any changes manually.

After specifying the connection and properties, the dataflow is run, and Snowflake data is processed and stored in the Excel sheet.

Expediting Data Loading

The Snowflake database can also be configured as a destination object, enabling you to consolidate and write information in the cloud data warehouse.

To illustrate how Snowflake can be used as a destination endpoint, let’s take a simple dataflow:

Figure 4: A simple dataflow that uses Snowflake as a destination system

Figure 4: A simple dataflow that uses Snowflake as a destination system

This dataflow combines Order data from two different tables in the SQL Server and loads it in Snowflake. The configuration method for the database connector is identical to the source connector, except when the user wants to load data in bulk. By enabling the Amazon S3 Bucket for staging, you can take full advantage of your existing AWS account, and leverage S3 buckets for data management and storage.

Figure 5: Enabling Amazon S3 bucket

Figure 5: Enabling Amazon S3 bucket

The next step involves specifying the properties by selecting one of the existing tables from the database or creating a new one. In addition, you can specify how the mapping works for the input ports. Here, we have selected Individual Ports for Actions that inserts all the records individually for new database tables.

data loading options

Figure 6: Specifying destination database’s properties

Centerprise supports different database loading options, as you can see in the screenshot above so that you can select preferred one depending on the nature of your dataflow. In this dataflow, we are loading multiple rows of data into the database using the Bulk Insert with Batch Size option.

Once the properties have been specified, running the dataflow will load the processed data into the Snowflake database.

Unleash the Power of Snowflake Database with Astera Centerprise

Here’s how Snowflake integrations can help you improve your business operations:

Deliver Continuous Connectivity

Easily connect and ingest enterprise data from disparate on-premise and cloud sources to Snowflake on AWS, without manual coding. By offering support for a range of structured, unstructured, and semi-structured sources, Centerprise enables seamless connectivity between heterogeneous enterprise systems and the Snowflake database.

Enable Self-Service Integration

The easy-to-use visual UI, pre-defined transformations, and built-in connectors in Centerprise empower business users to execute integration jobs with minimal support from their IT teams.

Scale for Enterprise Needs

Orchestrate ETL flows to leverage Snowflake’s flexible architecture and concurrency, and transfer data securely across hybrid environments at the speed of your enterprise requirements.

Reach Decisions Faster

Generate insights from Snowflake data, and enable stakeholders and partners to easily access the information when they need  to make quicker data-driven decisions and streamline day-to-day business operations.

Automate Snowflake Integrations

Use the workflow automation and job scheduling capabilities in Centerprise to automate your Snowflake integrations and shorten your time-to-insight journey while reducing process execution time.

The elastic scalability of Snowflake makes it a prime choice for businesses looking for enterprise-grade cloud data warehouses. Using the native Snowflake connector in Astera Centerprise, business users can easily connect to the database and use its data to fuel their end-processes.

Download the trial version of Astera Centerprise 8 and experience first-hand our platform’s integration capabilities.