What is Snowflake Unistore and Hybrid tables?

Recently Snowflake introduced a Unistore and Hybrid tables in their June 2022 summit. This can be a game changer in the world of data where we separate transactional (OLTP) and Analytical (OLAP) systems. When we are talking about transactional systems it’s about databases whose response time is in milliseconds and able to run millions of transactions in few seconds to keep the business running. When we are talking about analytical system the use case is more about analyzing the business. Snowflake’s Unistore combines these two worlds in one. It’s difficult to imagine but interesting concept to explore how much and how fast it unlocks your data power.

Snowflake Unistore

Unistore is a single platform that unifies both transactional and analytical workloads. In most companies, the transactions happens in different data silos across the organization and these data points are moved to analytical solutions using ETL/ ELT tools. This has been the process for past many years and it continued even when organizations moved to cloud. The current process is time consuming, complex, latency infested, resource intensive, and expensive. In a nutshell, Snowflake is trying to extend its scalability capability to include transactional data activities.

Hybrid Tables

In order to provide abilities for both transactional and analytical purposes, “Hybrid Tables” will need to be created. As performance is mission-critical for any transactional solutions, Snowflake has built a completely new row-based storage engine. Please note while writing this post these feature is still in preview mode. Below is the syntax for Hybrid table.

CREATE HYBRID TABLE Item (
	Itemkey 	NUMBER(18,0) PRIMARY KEY,
	ItemName 	VARCHAR(512),
	Date		timestamp_ntz
);

CREATE HYBRID TABLE SalesItem (
	Saleskey 	NUMBER(18,0) PRIMARY KEY,
	Itemkey 	NUMBER(18,0),
	Price		NUMBER(18,2),
	Margin		NUMBER(18,2),
	Profit		NUMBER(18,2),
	Date		timestamp_ntz,
CONSTRAINT fk_itemkey FOREIGN KEY (Itemkey) REFERENCES Item(Itemkey)
);

Hybrid tables has all the features of transactional systems like primary keys, foreign keys, referential integrity, constraints, etc. The difference is that you can perform analytics directly on these tables to get insights quickly. Something like below.

SELECT I.ItemName AS Item, SUM(SI.Profit) AS TotalProfit
FROM Item I
INNER JOIN SalesItem SI ON I.Itemkey = SI.Itemkey
GROUP BY I.ItemName

Advantages of Unistore

• Single source of truth
• Real-time data availability
• Better data governance
• Existing features like RLS, Data Masking, Time travel, etc.

Final thoughts

Unistore platform and Hybrid tables sound really promising and disruptive in the data space. It will have a huge impact in all sections of the data – architectural design principles will need to re-written, real-time data may become a norm, data governance will be improved, possibly break data silos, and many more. The goal will shift from moving data to extracting value out of data quickly.