Online Transaction Processing (OLTP) databases work well for transactional data requiring real-time updates but are slow and inefficient for analytic use cases. However, Online Analytical Processing (OLAP) databases are very good at analytical queries but can’t be used for transactional data. What if we could do both in the same database? That’s exactly what Snowflake Data Cloud aimed to do when they created UNISTORE, a.k.a. Hybrid tables.
In this blog, we’ll explain what Hybrid tables are, what use cases they can solve for you and your organization, and some tips and limitations of Hybrid tables.
What are Hybrid Tables?
Unlike standard Snowflake tables, built primarily for analytical use cases, Hybrid tables were created to mimic an OLTP database to be used for lightweight transactional processes.
Like a relational database, Hybrid tables enforce unique constraints for required primary keys and include indexes to retrieve data faster. Enforcing the primary keys also allows the use of referential integrity constraints to better define the relationship between primary and foreign keys.Â
The benefit is that Hybrid tables can be used alongside standard tables to combine transactional and analytical data to create deeper insights into your business, all while your data never leaves Snowflake.
Top Use Cases for Hybrid Tables
Customer Relationship Management (CRM)
Hybrid tables can store customer data, interactions, and transactions in a Customer Relationship Management (CRM) platform. This will allow you to centralize customer data and interactions across multiple channels, such as phone calls, emails, and chat messages.Â
Using Hybrid tables for CRM gives you the added benefit of a gold mine of information about interactions with your customers already in Snowflake from which to derive insights.Â
With this data, you could:
Analyze purchase history and engagement metrics to understand customer value and create targeted marketing and sales campaigns based on it
Pinpoint friction points in your customer’s interactions with your business to streamline or simplify the process
Create an analysis of your sales funnel and even use Cortex to forecast sales in the future
Financial Applications
Financial applications require real-time data so customers can access their up-to-date accounts at any time of the day. That’s why they’re a perfect use case for Hybrid tables. Constant withdrawals, deposits, and transfers can be reflected instantly, providing a seamless experience for your customers.Â
Once again, you will find all your data residing within Snowflake, which lets you run an in-depth analysis without moving your data to another system.Â
With the financial data in Snowflake, you can easily:
Detect and prevent fraud by monitoring spending patterns or unusual access attempts
Analyze customer data and transactions to identify potential risks associated with money laundering or terrorist financing
Increase operational efficiency by analyzing data on branch traffic, customer service interactions, and transaction volume to identify areas for improvement
Supply Chain Management
Optimizing inventory levels, tracking product movement, and managing orders across a complex supply chain network heavily involve transactions, making them well suited for Hybrid tables. Hybrid tables’ real-time data will ensure accurate stock levels, order fulfillment, and shipment tracking, streamlining your supply chain process.
Now that your supply chain data is all within Snowflake, you can use analytics to:
Predict customer demand by analyzing historical sales data, seasonal trends, and marketing campaigns
Identify potential disruptions using supplier data, weather patterns, and geopolitical events that might impact the supply chain
Utilize sensor data and track shipments in real time to identify delays, bottlenecks, or unexpected events within the supply chain
Hybrid Tables Tips and Limitations
Tips
Begin with a proof of concept using Hybrid tables for a specific use case. That way, you can determine if Hybrid tables will work for you and your business.
For bulk data loading, use the
CREATE TABLE AS SELECT (CTAS)statement, which offers significant performance improvements compared to traditionalINSERTorCOPY INTOstatements.Hybrid tables require a pre-defined schema with primary and optional secondary indexes established during creation. Constraints cannot be defined after a table is created. Plan your schema to optimize performance and avoid dropping and recreating the tables later.
Limitations
Hybrid tables are currently a public preview feature and are only available for accounts based in certain Amazon Web Services (AWS) regions. They are not currently supported by accounts in Azure or Google Cloud Platform (GCP).
Certain Snowflake features are not currently supported for Hybrid tables, including cloning tables, using materialized views, data sharing, and working with streams or replication functionalities.
At the time this blog was written, hybrid tables had an account-level limit of 500GB of data storage. While this capacity can handle various use cases, very large datasets will have to use standard tables.
Closing
Hybrid tables can bridge the gap between OLTP and OLAP data, allowing you to create analytical queries from transactional data in one place. Although hybrid tables are a newer feature with some limitations, Snowflake continues to improve them daily.
Snowflake Hybrid Tables: Bridging the Gap
If you’re looking to streamline your data management and analytics, consider Snowflake Hybrid Tables. These tables seamlessly integrate OLTP and OLAP data, simplifying your data processes and enabling unified analytical queries.
