Snowflake Unistore: Uniting Transactional and Analytical Data
Written by
Paul Narup, Senior Consultant
Published
May 20, 2024
Snowflake functionality can be overwhelming. And when you factor in technology partners, marketplace apps, and APIs, the possibilities become seemingly endless. As an experienced Snowflake partner, we understand that customers need help sifting through the possibilities to identify the functionality that will bring them the most value.
Designed to help you digest all that’s possible, our Snowflake Panorama series shines a light on core areas that will ultimately give you a big picture understanding of how Snowflake can help you access and enrich valuable data across the enterprise for innovation and competitive advantage.
OLTP vs. OLAP
Traditional relational database technology has historically been siloed into transactional (OLTP) or analytical (OLAP) systems.
OLTP, or Online Transaction Processing, is focused on managing day-to-day data operations as fast as possible and allowing operations from concurrent users. OLTP is often used for CRUD (create, read, update, delete) applications or other apps requiring transactional data. For example, banking systems would use OLTP for transfers or payments requiring real-time processing.
OLAP, or Online Analytical Processing, is designed to handle the complex queries and analysis required for reporting and business intelligence needs. These datasets are often much larger than transactional datasets as they contain much more historical data. Data is often stored in a star or snowflake schema to increase query performance.
How Unistore Works
Hybrid Tables allow for faster single-row operations like traditional transactional databases (OLTP) using included indexes and Snowflake enforcing primary keys. Primary keys, foreign keys, and unique constraints are all enforced on hybrid tables. These are not enforced on standard snowflake tables.
Unistore using hybrid tables can make sense in various business scenarios:
When systems require random reads (transactional) vs. reading a large range of data (analytical)
When systems require random writes (transactional) vs. large sequential writes (analytical – bulk loading)
Fetching all records for a customer (transactional) vs. receiving aggregations for that customer (analytical)
The Business Value of Unistore
By using Snowflake’s Unistore workload, your organization’s security and compliance needs can be simplified as only one system is needed. You’ll also be able to eliminate costs for the transactional system (hosting, maintaining, and backing up).
Additionally, most companies have an ETL process to move data from their transactional system to an analytical system because reporting on a transactional system can be slow and bog down the application it is supporting. Since Unistore allows for transactional and analytical to be stored in the same system, this ETL process can be simplified or removed—reducing complexity, latency, and maintenance issues for data teams.
Ready to unlock the full potential of data and AI?
Book a free consultation to learn how OneSix can help drive meaningful business outcomes.
