A new, exciting feature from the Snowflake Data Cloud was announced at Snowflake Summit 2022 that has generated a lot of buzz.
We’re talking about Hybrid tables, which enable a Unistore consisting of analytical and transactional workloads together on a single data platform.
It hasn’t been a year since this blog post came out about improving concurrency and latency around query improvements with details on how they did it.
One of the scenarios described there is the improvement for Point Lookups; which is a query to return a short list of records by key. Hybrid Tables are the next step to address single row results with better performance. We’re talking speed!
If the improvements mentioned before reached the neighborhood of 11 queries-per-second (QPS) and then enabling the Search Optimization Service multiplied that 6 times over for 50 QPS, Hybrid Tables turns this up to 11 with a goal of 1,000’s QPS.
To quote the famous racer Lightning McQueen: “Speed. Faster than fast, quicker than quick. I am Lightning.”
What Are Hybrid Tables in Snowflake?
When you look at the documentation, a Hybrid Table doesn’t look any different than any other table you create. Take for example the Syntax to create a Hybrid Table:
CREATE [ OR REPLACE ]
HYBRID TABLE [ IF NOT EXISTS ] <table_name>
( <col_name> <col_type>
[ { DEFAULT <expr>
| { AUTOINCREMENT | IDENTITY }
[ ( <start_num> , <step_num> )
| START <num> INCREMENT <num> ] } ]
/* AUTOINCREMENT (or IDENTITY) is supported only for numeric
data types (NUMBER, INT, FLOAT, etc.) */
[ NOT NULL ]
[ inlineConstraint ]
[ , <col_name> <col_type> [ ... ] ]
[ , outoflineIndex ]
[ , ... ] )
[ COMMENT = '<string_literal>' ]
This doesn’t look nearly as daunting when considering all the options for a standard table. See this CREATE TABLE Syntax for reference.
Other CREATE Syntax that can be used includes CREATE TABLE … AS SELECT (CTAS) and CREATE TABLE … LIKE, both of which use similar Syntax as a standard table.
“There are a few, uh, provisos, a, a couple of quid pro quos.” marvelously said by Robin Williams as Genie in the movie Aladdin. The same applies to Hybrid Tables.
Rule #1: A Hybrid Table must have a primary key constraint
Rule #2: Unique constraints build an underlying index, which results in additional data being stored
These rules are completely within reason when you consider the purpose of a Hybrid Table (more on that later).
An important feature (now available in the preview) will be combining Hybrid Tables with standard tables using queries such as INSERT SELECT or COPY TABLE AS SELECT.
Specifically for Hybrid Tables, transaction scope is limited to the database where the hybrid table resides.
So Why Do I Need Hybrid Tables?
I am glad you asked! Allow me to set the scene. You are shopping at your favorite store. They have a loyalty program which you signed up for years back. The checkout line is down to one register and there are three people in front of you. When you finally reach the front of the line, the cashier is kind enough to wish you a happy birthday and offers a discount on your purchase!
This raises your spirit as you walk out with your head held high.
Behind the scenes, the company established a Customer Data Platform (CDP) on Snowflake. This is the culmination of all the touch points the business has with the customer, be it Google Analytics for their website, an online survey you filled out for a previous purchase, or contacting support.
Additional data could be previous purchases which the business kept track of because of the loyalty program. Once gathered in a central database, the value of this data comes in the form of customer experience. As consumers, we love to see that personalized touch from time to time.
Back to the checkout line, the point-of-sale software has interfaced with our CDP and queried data for you in a Hybrid Table where speed and latency are optimal for returning data to the cashier allowing that extra special customer experience!
Future capabilities could include smart internet devices where we move beyond Internet-of-Things just feeding us data to now allowing these devices to request data through APIs to make decisions.
There is also the traditional Transactional Application to consider such as orders, invoices, and payments; though I’ll let you be the judge here.
In Summary
As the Hybrid Table feature matures, there will be many more use cases to consider than what I’ve described here. When your applications or partners need access to your data, and speed of performance is of the utmost importance, a Hybrid Table will answer the call with much higher concurrency and throughput than we have today. Use cases will take us “To Infinity and Beyond”.
Interested in implementing Hybrid Tables at your organization? As the Snowflake 2022 Partner of the Year, our Snowflake experts are passionate about the platform and happy to provide Snowflake guidance around costs, timeframe, logistics, and anything else under the sun.
