Data Vault on Snowflake: Performance Tuning with Keys

Snowflake continues to set the standard for Data in the Cloud by taking away the need to perform maintenance tasks on your data platform and giving you the freedom to choose your data model methodology for the cloud. You will expect the same relational capabilities for your data model as any other platform and Snowflake certainly delivers.

As your Data Vault grows a common concern is if the platform can continue to deliver on the performance you first achieved when you started your Data Vault journey on Snowflake. Yes, as we have seen in previous articles, the larger the tables are the more micro-partitions represent that table and we have looked at how constructing a PIT table can improve join performance around a hub or link table and how a PIT table can be used to fetch the current record for a business entity using Snowflake’s JoinFilter. The common theme in those three articles is pruning, being able to select fewer micro partitions to satisfy a query on Snowflake improves join performance, naturally.

In this series of articles, we will explore some of the key considerations (pun unintended) when building your data vault on Snowflake in the first place as well as the pros and cons of each decision. Armed with these facts you will be able to make an informed decision on the architecture patterns for your deployment on…