Large-Scale Pivot Tables in Snowflake with Datameer
- John Morrell
- February 28, 2022
Pivot tables are an everyday operation all Excel users perform. Business analysts using Excel will often Pivot their data to explore it, see how results are different as sliced across certain attributes, and find unique aspects within the data. Pivot also provides a standardized way of grouping and organizing the data to present to other business users and management.
When the data is in a cloud data warehouse such as Snowflake, Pivot becomes a more daunting task. There are two options:
- Write complex SQL code, execute it directly against Snowflake, then download the results into Excel, or
- Download the source data from Snowflake into Excel or link their Excel sheet to the source data in a Snowflake table (if the DBA allows this) and Pivot the data in Excel
Both of these approaches create major problems:
- Pulling the data out of Snowflake into Excel creates extra copies of the data – a huge data governance headache and the need to manage additional data silos.
- Excel has data size and performance limitations which hinders the user’s ability to pivot on large data sets that might reside in Snowflake.
- Writing the complex SQL for Pivoting is not an everyday skill for business analysts and the queries could impact the cloud data warehouse performance and accidentally drive up costs.
Pivot in Datameer
The Datameer data modeling and transformation platform now offers Pivot in a highly similar way one would create pivot tables in Excel. For analysts familiar with SQL, Datameer’s Pivot works just like the SQL Pivot operators work, with one major caveat – with Datameer you don’t need to write complex code.
As seen in the screenshot image, Pivot in Datameer looks, works, and acts just like a Pivot Table wizard inside of Excel! It offers a user experience metaphor easily understood by data and business analysts familiar with Excel.
Datameer gives you an interactive preview of the results. This allows you to (a) make sure your Pivot is acting as you expected and (b) explore the data to see if there is anything interesting.
Datameer works directly on your Snowflake cloud data warehouse and thus leverages the power and scale of Snowflake. This allows your pivot data models created in Datameer to pivot extremely large datasets and turn them into useful, understandable sets of information.
Watch the demo video below to see how easy it really is to create and run large-scale pivot tables in Snowflake using Datameer.
Using Pivot in Datameer against your Snowflake data provides tremendous benefits and business value, including:
- The ability for any business analyst to create a pivot table directly on your Snowflake via a familiar, Excel-like wizard-style interface,
- Allowing users to interactively explore the data, see results, and change parameters until they find the results they seek,
- Eliminating the need to copy the source data into Excel keeping the data securely in Snowflake and promoting better data governance.
- Automatically generate the same pivot table as new data arrives via transformation pipeline jobs to see fresh results, and
- Pivot data on large-scale datasets into the millions of rows directly in Snowflake bypassing the data size and performance limitations of Excel.
To see a real-world example of using pilot tables with Datameer on Snowflake, read our earlier blog post, How to Pivot Data in Snowflake with Datameer.
Pivoting data is a standard, everyday use case for data and business analysts. A no-code data modeling and transformation platform such as Datameer makes it extremely easy for analysts to Pivot data in Snowflake without the risk of the analysts writing erroneous or unoptimized SQL code and eliminating additional data silos by exporting data into Excel.
Are you interested in learning more about Datameer and how it can make it faster and easier to perform complex transformations such as Pivot interactively and without writing a single line of code? Please visit our website or Sign up for your free trial today!