Hybrid Tables in Snowflake are a new type of table that can support transactional workloads. They store data in a row-oriented format, and allow fast single-row operations as well as efficient data loading and querying. Hybrid tables are part of the Unistore feature, which is currently in public preview.
What is Unistore?
Unistore is the feature that enables Hybrid Tables to exist. It’s a new workload that delivers a modern approach to working with transactional and analytical data together in a single platform.
The main motivation behind Unistore was to eliminate the need for data movement across different systems. Clients wanted to avoid having multiple copies of the same data in different solutions, and instead access their data on demand, and work with almost all their data in one place.
Unistore will support three different workloads:
- Snowflake is still primarily a data warehouse, where the storage is optimized for OLAP (OnLine Analytical Processing). Different entries within each micro-partition, and even JSON data within a VARIANT cell, are stored compressed in a columnar format, to allow this kind of optimization.
- The second workload already supported by Snowflake is the Data Lake. We can access structured and semi-structured data from cloud object storage through external tables. References can point to unstructured data, such as videos, images or PDFs.
- The new “Unistore” will include a third type of workload, optimized for OLTP (OnLine Transaction Processing), through these new “hybrid tables”.
Snowflake Hybrid Tables for Transactional use Cases
Performance is a key factor for any transactional application, especially for quick single-row operations. Snowflake created a new row-based storage engine to enable enterprise transactional applications to be built directly on Snowflake.
Creating a Hybrid Table is simple. You just need to make a table as you would normally do with any other Snowflake table. But for these transactional workloads, Hybrid Tables need a primary key and Snowflake will ensure that your applications’ primary keys are unique.
CREATE HYBRID TABLE Customers (
CustomerKey number(38,0) PRIMARY KEY,
Customername varchar(50)
);
Of course, your application needs more than one table. The data model of your application is determined by the connections between tables using primary keys and foreign keys. With Hybrid Tables, you can specify the connection using referential integrity constraints that are now enforced. Let’s see an example:
-- Create order table with foreign key referencing the customer table
CREATE OR REPLACE HYBRID TABLE Orders (
Orderkey number(38,0) PRIMARY KEY,
Customerkey number(38,0),
Orderstatus varchar(20),
Totalprice number(38,0),
Orderdate timestamp_ntz,
Clerk varchar(50),
CONSTRAINT fk_o_customerkey FOREIGN KEY (Customerkey) REFERENCES Customers(Customerkey),
INDEX index_o_orderdate (Orderdate)); -- secondary index to accelerate time-based lookups
Hybrid Tables will enforce constraints so that when you try to insert a record that breaks them, such as a new record with a duplicate primary key, Snowflake will give an error.
Analytics on Transactional Data
Building transactional applications directly on Snowflake is amazing by itself, but the power of Unistore goes beyond that. Unistore unleashes the full potential of your data by allowing you to do analytics directly on your transactional data.
You can achieve even more powerful insights if you do analytics on transactions as they occur. Imagine having a dashboard that reports weekly sales trends with a billion records from an orders table instantly included. Using Hybrid Tables, you just run the analytical query directly on your transactional data and the results are returned with the analytical performance you’d expect from Snowflake.
Get notified about Hybrid Tables release and features here.
