Introducing Unistore, Snowflake’s New Workload for Transactional and Analytical Data
The Data Deluge: Drowning in Information, Thirsting for Insights
In today’s fast-paced business environment, many organizations still manage their transactional and analytical data in separate systems. This segregation results in data silos, slow data movement between systems, and delays in accessing the data.
Data silos pose significant challenges for implementing consistent security and governance controls, which can impact an organization’s operational efficiency, trust, and reputation. Moving data between systems can be a slow and painful process, often leading to substantial latency and increased complexity due to pipelines moving between systems. Furthermore, delays in data access can adversely impact innovation and development efforts, as teams require timely access to data for downstream use cases.
Enter Unistore: Revolutionizing Data Management with a Unified Approach
Snowflake, the Data Cloud company, recognized this critical need. They envisioned a world where transactional and analytical data coexist seamlessly, where insights flow freely, and informed decisions become the norm. This vision led to the creation of Unistore, a revolutionary new workload that shatters the limitations of traditional data management.
But what if there was a solution to bridge this gap? Imagine a single platform seamlessly integrating your transactional and analytical data, offering a holistic view of your business in real time. Picture the opportunities that arise:
● Uncovering Emerging Trends: Identify evolving customer preferences and market shifts ahead of the curve.
● Optimizing Operations: Streamline workflows and pinpoint inefficiencies by analyzing real-time data alongside historical performance metrics.
● Informed Decision-Making: Base your choices on a comprehensive understanding of your business landscape, rather than fragmented data points.
Welcome to a new era in data management, where your data not only exists but flourishes, revealing insights that hold the key to your success. In the upcoming sections, we’ll delve deeper into this transformative landscape, uncovering the strategies that unlock the true potential of your data. Stay tuned, as the revelations ahead may surprise you…
What is Unistore and How Does it Work?
Snowflake’s Unistore is a game-changer in the world of data management. But what exactly is Unistore, and how does it work? Well, think of Unistore as a Canonical Layer that seamlessly integrates transactional and analytical data within Snowflake’s platform. This means you can now run your operational workloads alongside your analytics without compromising performance.
Unistore achieves this by efficiently storing both types of data in a single table structure, eliminating the need for complex ETL processes or multiple copies of the same dataset. By blending OLTP and OLAP capabilities, businesses can now derive real-time insights from their operational data while also conducting complex analytics on the same platform.
With Unistore, companies can streamline their workflows, reduce costs associated with maintaining separate systems for transactional and analytical workloads, and ultimately make faster and more informed decisions based on unified data sources.
As part of Unistore, Snowflake introduced Hybrid Tables, which offer fast single-row operations and allow customers to build transactional business applications directly on Snowflake. Hybrid Tables, currently in public preview, enable customers to perform swift analytics on transactional
data for immediate context and join Hybrid Tables with existing Snowflake Tables for a holistic view across all data. Unistore and Hybrid Tables enable customers to build transactional
applications with the same simplicity and performance they’re used to with Snowflake, and a unified approach to data governance and security.
Unistore Architecture:
Hybrid Tables utilize a row store as their main data repository, ensuring efficient operational query handling. To provide excellent analytical query performance, Snowflake continuously replicates data to an analytical store in the background. With Hybrid Tables, Snowflake routes queries to the best data store depending on what will be most performant and efficient. Customers don’t need to worry about which type of query it is. e. This approach offers a unified perspective of your data, eliminating the need to manage infrastructure intricacies.
Exploring the Versatility of Hybrid Tables in Snowflake Let’s see some of the characteristics of Hybrid Table:
● Hybrid Storage: Data in Hybrid Tables are stored in row format in high-speed storage while continuously replicated to analytical storage for analytical queries.
● Transactional Capabilities: Supports fast, frequent selects, inserts, updates, and deletes with high concurrency, low latency, and high throughput.
● Analytical Capabilities: Enables complex queries and fast large-scale scans, transforming data into columnar representation for optimized performance.
● Primary Key Requirement: Hybrid Tables must have a primary key, which can be a single or composite key.
● Secondary Indexes: Allows creation of secondary indexes for non-primary keys to facilitate faster lookup, along with foreign keys for referential integrity constraints.
● ACID Transactions: Supports ACID transactions with a Read Committed isolation level. Multi-Statement Transactions: Enables multi-statement and cross-domain transactions within the same database, including interactions with regular Snowflake tables.
● Row-Level Locking: Provides row-level locking for Data Manipulation Language (DML), which unlocks high concurrency DML operations while ensuring transaction integrity.
● Warehouse Compatibility: Utilizes the same warehouse infrastructure while maintaining data isolation from the warehouse itself.
● Data Governance Features: Supports data governance features such as Data Masking for enhanced security and compliance.
● Conversion Process: Existing tables can be converted to hybrid tables using the CREATE OR REPLACE HYBRID TABLE command, preserving table structure and integrity.
● Management Commands: Utilize commands like SHOW HYBRID TABLES and SHOW INDEXES to manage and monitor hybrid table usage and indexing.
Jumpstart with Hybrid Tables Syntax
Getting started with Hybrid Tables is a breeze, offering a user-friendly approach to data management. These tables are backed by an innovative storage engine seamlessly integrated into Snowflake’s existing architecture. Leveraging familiar cloud services and query processing layers, Hybrid Tables ensure a unified experience while supporting the rich features Snowflake is renowned for.
To begin using Hybrid Tables, all you need to do is connect to Snowflake, choose a warehouse, and create a new table using the HYBRID keyword. It’s as simple as executing standard SQL commands, just like you would with any other table in Snowflake:
CREATE HYBRID TABLE Customers (
Customerkey number(38,0) PRIMARY KEY,
Customername varchar(50) UNIQUE
);With this straightforward process, you can seamlessly integrate Hybrid Tables into your data ecosystem, unlocking their full potential with ease and efficiency.
Comparison between Hybrid Tables and Standard Tables
Hybrid tables provide some additional features that are not supported by other Snowflake table types.
Our POC: Upgrading database migrations to Snowflake with Hybrid Tables
Embark on a journey with us as we delve into the transformative integration of Hybrid Tables into our database migration process. We have an in-house accelerator which can do Migrations from a set of source systems to Snowflake using Python & Streamlit that was used to do this POC.
Here’s a comparison of the state before, during, and after the integration of Hybrid Tables in our migration accelerator:
Before Implementation:
- Logging mechanism limited to basic file or database storage.
- Error tracking relied on manual monitoring or scattered logging solutions.
- Analytical insights into application performance and user interactions were challenging to obtain.
- Limited ability to track and analyze individual operations (like Fetch Metadata, Create DDLs, Perform Data Ingestion, Create Error & Audit Logs, etc) within migration processes.
- Lack of a centralized logging system for easy troubleshooting and monitoring.
After Implementation:
Optimized Logging and Error Tracking:
- Detailed Event Logs created from the application for each operation happening in the backend for each Migration which support high concurrency without compromising on performance and enforced referential integrity
- Seamless integration of Hybrid Tables into the application workflow enables real-time and comprehensive logging capabilities as.
- Enhanced error tracking system allows for proactive identification and resolution of issues, minimizing downtime and disruptions.
Advanced Analytical Insights:
- Dashboard powered by Event Logs created using Hybrid Tables offer additional analytical insights into application performance and user interactions.
- Monitoring of KPIs such as event runtime and execution time over migration duration provides valuable insights for optimization.
Streamlined Operation Tracking:
- Dedicated log tables for each operation enable granular tracking and analysis of migration processes, improving transparency and accountability. The enforced referential integrity ensures linking to Master Logs for each Migration without any redundancy during heavy concurrent workloads
- Detailed timestamps and status tracking enhance visibility into the progress and status of individual operations, facilitating efficient management.
How Does Unistore Help You Solve Important Problems?
Imagine a retail giant struggling to efficiently analyze both real-time transactional data and historical sales information. With Snowflake Unistore, this company seamlessly combines these two types of data in one platform. By utilizing Unistore’s Hybrid Tables, the retail giant can now run complex queries that require joining transactional and analytical data without compromising performance.
In another scenario, a financial institution benefits from Unistore by gaining real-time insights into market trends while still being able to access historical trading records instantly. These case studies showcase the versatility and power of Snowflake’s new feature in transforming how businesses handle their transactional and analytical data needs.
Unlocking an unlimited scope of possibilities, Unistore’s Hybrid Tables offer endless opportunities for diverse use cases across industries, providing businesses with the flexibility to innovate and adapt to evolving data requirements effortlessly
Understand cost for Hybrid Tables
When using hybrid tables, your account is charged based on three modes of consumption.
- Hybrid table storage: Cost for storage of hybrid tables depends on the amount of data that you are storing in hybrid tables. Storage cost is based on a flat monthly rate per gigabyte (GB). See Table 3(b) in the Snowflake consumption table, which covers unit pricing for hybrid table storage. Note that hybrid table storage is more expensive than traditional Snowflake storage.
- Virtual warehouse compute: Queries against hybrid tables are executed through virtual warehouses. The consumption rate of a warehouse is the same for querying hybrid tables as it is for standard tables. See Virtual warehouse credit usage.
- Hybrid table requests: Because they use serverless resources, hybrid tables consume additional credits on the underlying row storage clusters. Your consumption of serverless resources is based on the amount of data that is read from or written to storage clusters. See Table 5 in the Snowflake consumption table, which covers serverless resource unit pricing for hybrid table requests. Because hybrid tables store data in pages, even small read or write operations incur a minimum 4 KB of hybrid table request usage.
Future Developments and Potential Impact on the Industry
Get ready for a whirlwind tour of possibilities! Snowflake’s commitment to innovation suggests a future where Unistore evolves beyond its current capabilities. Here are some potential advancements to keep an eye on:
- AI-Powered Insights: Imagine Unistore harnessing the power of artificial intelligence to automatically identify trends, predict outcomes, and uncover anomalies hidden within your data. This could transform decision-making from a reactive process to a proactive one.
- Real-Time Everywhere: Unistore’s current real-time capabilities are impressive, but the future could see them become ubiquitous. Imagine a world where every data point, from sales figures to customer interactions, is analyzed in real-time, providing a constantly evolving picture of your business.
- Democratizing Data Science: Unistore’s user-friendly interface could be further simplified, allowing a wider range of users to access and analyze data. This “democratization” of data science could empower businesses to unlock insights from every corner of the organization.
Beyond the Horizon: Unistore’s Impact on Industries
Unistore’s influence won’t be confined to the realm of data management. Its impact has the potential to ripple across entire industries:
- Revolutionizing Retail: Imagine a future where Unistore helps retailers predict customer behavior with incredible accuracy, allowing them to optimize inventory, personalize marketing campaigns, and deliver a seamless shopping experience.
- Healthcare Transformation: Unistore’s real-time analytics could empower healthcare professionals to make faster and more informed decisions, leading to improved patient outcomes and personalized treatment plans.
- The Rise of the Citizen Data Scientist: With Unistore’s simplified tools, businesses across industries could cultivate a generation of “citizen data scientists” — employees empowered to analyze data and uncover valuable insights that were previously reserved for data specialists.
These are just a few glimpses into the future of Unistore and its potential impact. As technology continues to evolve, one thing is certain: the way we interact with, analyze, and leverage data is on the cusp of a transformative revolution. Unistore stands poised as a powerful catalyst in this exciting journey.
Conclusion
Snowflake’s Unistore represents a paradigm shift in data management, offering a seamless experience without the burden of a steep learning curve. By consolidating data management, storage, and processing onto a single platform, they streamline operations and enhance efficiency. The true essence of Hybrid Tables lies in their ability to centralize data, reinforcing governance protocols and curtailing costs associated with maintaining disparate infrastructures like MySQL, Postgres, MongoDB, and others. Moreover, by embracing Hybrid Tables, organizations unlock the full spectrum of benefits offered by the Snowflake Data Cloud, including its cloud-agnostic nature, multi-cloud adaptability, managed services, intuitive interface, and robust security and governance mechanisms.
As we conclude this discussion, stay tuned for our next blog where we will delve deeper into the world of Unistore with a detailed Guide on creating & using Hybrid Tables, the specifics of our implementation and the remarkable outcomes achieved. We’ll share the story of our own journey with Unistore, igniting curiosity and interest among readers eager to explore the transformative potential of this innovative solution.
References
https://www.snowflake.com/en/data-cloud/workloads/unistore/
https://www.snowflake.com/blog/simplify-application-development-hybrid-tables/ https://docs.snowflake.com/en/user-guide/tables-hybrid
Authors
Rajesh Rout
Nishu Singh
Jenish Karia
Edited and Reviewed by: Ripu Jain, Sr Data Cloud Architect, Snowflake
