The Automated, No-Code Data Stack
Learn how Astera Data Stack can simplify and streamline your enterprise’s data management.
Get Extended Snowflake Integration with Astera Centerprise
What is Snowflake?
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 assets.
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 data applications, like Salesforce, SQL Server, SAP HANA, and more without writing any code or switching platforms. Extract, load, and transform data easily to and from Snowflake through this data integration tool!
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 modern 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 ingestion 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 object, 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
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 and entered its account credentials.
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: 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 target database, enabling you to consolidate and write information in the cloud data warehouse or data lake.
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
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 data 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 engineering, management, and storage.
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.
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 storage integrations can help you improve your business and technology 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 Snowflake 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 securely 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 storage 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 securely connect to the database and use its data to fuel their end processes.
Download the trial version of Astera Centerprise and experience first-hand our platform’s integration capabilities.
Authors:
Tehreem Naeem
