Optimize Your Hierarchical Data with Snowflake, Part Four: Data Visualization
Using Snowflake to Maximize the Value of Data in Hierarchies
The Snowflake Data Cloud offers virtually limitless storage and compute capacity. Combined with native support for extremely performant storage and processing of hierarchical data structures, customer chose Snowflake for fast and reliable root cause and impact analysis across complex data models and processes.
This is Part Four of a series on handling hierarchichal data in Snowflake, here’s what we’ve covered so far:
- Part One: Understanding Hierarchical Data
A look at forms of hierarchical data and a real-life example of how the inability to process hierarchical data resulted in loss of life and a company’s bankruptcy - Part Two: Challenges in Analyzing Hierarchical Data
A description of why analyzing hierarchical data is so difficult and unreliable for legacy data warehouse systems - Part Three: Using Snowflake to Maximize the Value of Data in Hierarchies
Example showing how Snowflake enables you to cost-effectively and efficiently analyze data in a complex hierarchy - Part Four: The Grand Finale — Visualizing the data
What does it take to analyze this data using Tableau?
The Grand Finale: Let’s visualize our data in Tableau!
How does Tableau or any other Enterprise BI tool handle a data structure in Snowflake like this:
Tableau can handle JSON data (https://www.tableau.com/blog/your-json-data-ready-analysis-tableau-101-59543) but the data we have in this table is neither pure JSON nor relational.
It’s a very flexible way of modelling data for efficiency of processing it within Snowflake. BI tools are not best suited for this approach.
Additionally, BI analysts are likely going to not be very proficient with this data modelling, either.
Bottom line — this model is neither good for classical BI tools nor for their users. So, what do we do about it?
Remember that Snowflake storage is cheap and you can automate your data engineering pipelines using, for instance Snowflake’s tasks or dynamic tables.
We’ll create and materialize a new view on this data that will be suitable for any BI tool and analyst. This will be the final step in our data engineering pipeline and it can be automated and triggered to run as soon as our hierarchy has changed, or on schedule.
Here’s a Snowflake Stored Procedure code snippet that just exactly that:
I’ve chosen JavaScript to implement this, but as you know Snowflake’s programability covers just about any modern language for working with data: Java, Python, Scala, JavaScript and, of course, SQL.
This stored procedure (from now on “SP”) will take our fancy modelled table and make a new table from it. That new table will be more suitable for the BI tools.
Here’s the what the SP produces:
The new table will get a column per depth/level of the hierarchy. And it will do that dynamically — you don’t have to know the depth or contents of the hierarchy.
Obviously, this new table is a hell to maintain. Imagine adding a hierarchy here, for instance. So, we’ll swear to never ever try to change or update this table. In fact, we should never give update privileges for this table to any role. This is a static, read-only table only meant to be created for reading in by the BI tools.
This is the format that Tableau loves and understands. Here’s how to build a dashboard to explore costs aggregated on different levels and cost centers:
I hope you enjoyed reading this series of articles. Let me know if you found it useful and what other topics you’d like to hear from me about
Views and opinions expressed in this article are my own and do not represent that of my place of work. I expressly disclaim any liability or loss incurred by any person who acts on the information, ideas or strategies discussed in my stories on Medium.com. While I make every effort to ensure that the information I’m sharing is accurate, I welcome any comments, suggestions, or correction of errors.
