At Alteryx (formerly Trifacta), we lead the way in data engineering with our vision, product innovation, and execution. One of our goals is data democratization to enable secure access to data quickly and easily. As part of this journey, we enable the modern data stack to support ETL and ELT data architectures with an open and interactive approach to profile, prepare, and pipeline data in the cloud. Data transformation is a key aspect of this process. Today, we’re excited to announce SQL-based ELT for data transformations with Pushdown Optimization on Snowflake, the Data Cloud.
With this announcement, we’re deliver self-service data transformations on Snowflake for data practitioners looking for a no-code/low-code solution through a visual, intuitive, and unified interface. Additionally, for practitioners looking to work with code, this solution complements their technical acumen with advanced, complex data transformations with custom SQL scripts and queries. Designer Cloud empowers the modern data worker to use self-service mechanisms to transform raw data using guided transformation steps and convert these steps into SQL queries easily. This capability complements our ongoing pushdown capabilities with leading cloud data warehouses such as Google BigQuery that we shared in a previous blog post.
By leveraging this solution, data engineers can benefit from the massive boost to their productivity without rewriting code when new data needing transformation arrives. Decision-makers such as the CDO or the CTO can avail of the significant ROI without the need to retrain personnel for ongoing data transformations. Let’s learn more.
From Recipes on Designer Cloud to SQL on Snowflake
The key to the solution is to leverage the scale and efficiency of Snowflake within the AWS cloud ecosystem. With full pushdown on Snowflake, the data transformation logic also known as the wrangling logic is converted into SQL, and the transformations are executed directly on Snowflake. During transformations, the data stays within Snowflake, resulting in a secure solution that efficiently uses the compute resources in the cloud to deliver a complete data transformation solution strengthening the “T” in the ELT architecture.
Solution details
Commonly used transformations and functions including Aggregates, Joins, Unions, Filters, Math Functions, Date Functions, Merge and Split Functions, and many others are converted into equivalent SQL queries using native Snowflake functions within Snowflake. For any transformations or functions that are not natively supported by Snowflake, Designer Cloud uses Javascript User Defined Functions (UDFs) to achieve the desired results. These UDFs allow Javascript functions to be written and called as though they were SQL functions. The SQL generation is handled using Apache Calcite, an open-source framework to build databases and data management systems, that includes many components such as a SQL parser, a Query optimizer, a SQL generator, a SQL validator, and an API for building expressions in relational algebra.
The example below shows how the transformation step of merging two columns within a Designer Cloud recipe is converted into a SQL query in Snowflake.
Benefits
- Increased Productivity: Pushdown Optimization with Snowflake on AWS offers faster data transformations across entire Snowflake tables. This eliminates the need to write complex SQL statements or code, allowing data users to focus on the data, increasing their productivity by over 2x.
- Quicker Data Transformations with High-Quality: Designer Cloud offers faster data transformations of up to 20x, leveraging the power of Snowflake. With Pushdown Optimization, data loading and data transformation happen simultaneously delivering high-quality data, providing organizations and data professionals with the required insights for analytics and machine learning.
- Operations at scale: Designer Cloud’s support of Snowflake on AWS leverages the scale of the cloud enabling data transformations at any scale. From a few MB of data to exabytes of data, Designer Cloud provides intelligent data transformations helping data professionals understand their data at the most granular level, with the desired scale.
Get started today with a free trial of Designer Cloud.