Snowflake Hybrid Tables 101—A Technical Deep Dive (2024)

Snowflake has long been recognized as a leading platform for Online Analytical Processing (OLAP). Since its public debut a decade ago, Snowflake has focused on delivering a cloud-based data warehouse solution that excels in handling large-scale analytical workloads. Its separation of compute and storage has been central to its ability to scale elastically, making it a go-to platform for data-driven organizations. However, as the demand for real-time, transactional data processing has grown, Snowflake has extended its focus beyond traditional OLAP workloads. In June 2022, Snowflake introduced Hybrid Tables as part of its broader Snowflake Unistore initiative, which is now available in public preview. Hybrid Tables are designed to bridge the gap between OLAP (Online Analytical Processing) and Online Transaction Processing (OLTP), enabling users to efficiently handle both analytical and transactional workloads within the same Snowflake platform.

In this article, we will cover everything you need to know about Snowflake Hybrid Tables—covering their architecture, key features, comparisons, benefits, limitations, and a detailed step-by-step guide to create and use them.

What Are Snowflake Hybrid Tables?

Snowflake Hybrid Tables, introduced as part of Snowflake Unistore, bridge the gap between transactional (OLTP) and analytical (OLAP) workloads on a single, unified platform. These tables support low-latency, high-concurrency transactional operations—such as inserts, updates, and deletes—while enabling real-time data interactions for applications needing immediate access to fresh data.

Hybrid Tables' distinguishing characteristic is its dual-storage architecture, which combines row-based storage for rapid transactional workloads and columnar storage optimized for analytical queries. This approach enables you to manage two sorts of workloads on the same dataset without the need to move or duplicate data. Snowflake dynamically shifts between row-based operations for transactions and columnar storage for analytics depending on the workload type, delivering performance tailored to each scenario.

Snowflake Hybrid Tables also fully support primary keys, foreign keys, unique constraints, and indexes to maintain data integrity and enable fast, transactional access. However, they have limitations compared to traditional Snowflake tables, particularly for large-scale analytical workloads. Operations such as long-range scans and complex queries may degrade performance due to the row-based storage design optimized for transactional processing. Also, Hybrid Tables do not currently support features such as materialized views, cloning, streams, data replication, time-travel, and other features, reflecting their primary focus on real-time, transactional workloads rather than large-scale analytical processing.

These tables integrate seamlessly with Snowflake's architecture. They provide centralized governance, scalable compute, and robust storage architecture, making them an excellent choice for workloads that require a combination of transactional and analytical processing.

Watch this video by Carl Perry for an in-depth exploration of Snowflake Unistore and Snowflake Hybrid Tables:

Architecture Overview of Snowflake Hybrid Tables

Snowflake Hybrid Tables are designed to integrate seamlessly into the existing Snowflake architecture, providing a robust solution for handling both operational and analytical workloads. This architecture leverages both row and columnar storage to optimize performance and efficiency for various types of queries. Here is a detailed exploration of the architecture, its components, and the benefits it offers.

Snowflake Hybrid Tables architecture provides several advantages:

1) Seamless Data Governance — Snowflake's platform features, such as data governance, work with Snowflake Hybrid Tables out of the box, guaranteeing compliance and security without additional overhead.

2) Mixed Workloads — Users can run hybrid workloads that combine operational (OLTP) and analytical (OLAP) queries, allowing for a more versatile data handling approach.

3) Native Query Execution — Snowflake Hybrid Tables can be joined with other Snowflake tables natively. This means that all queries are executed efficiently without the need for data federation, which can introduce latency and complexity.

4) Atomic Transactions — Users can execute atomic transactions across Snowflake Hybrid Tables and other Snowflake tables without needing to manage two-phase commits manually. This simplifies transaction management and enhances reliability.

The architecture of Snowflake Hybrid Table uses a row store as the primary data store for Hybrid Tables, which is crucial for operational query performance. Here’s how it works:

1) Direct Writes to Row Store

When data is written to a Snowflake Hybrid Table, it is immediately stored in the row format. This design is optimized for transactional workloads that require quick read/write operations.

2) Asynchronous Data Copying

Data is asynchronously copied from the row store into object storage. This process allows for large scans to be performed without impacting ongoing operational workloads, thus providing better workload isolation.

3) Columnar Caching for Analytics

Some data may be cached in a columnar format in the warehouse. This caching mechanism enhances performance for analytical queries by leveraging the efficiency of columnar storage during read operations.

4) Query Optimization

Users execute SQL statements against logical Hybrid Tables. The Snowflake query optimizer intelligently decides where to read data from—whether from the row store or cached columnar data—to ensure optimal performance.

Remember that while Hybrid Tables provide significant performance benefits, they also have some trade-offs:

Larger Storage Footprint — Due to their reliance on a row store as the primary storage mechanism, Snowflake Hybrid Tables typically occupy more storage space compared to standard Snowflake tables. This is primarily because standard Snowflake tables utilize columnar storage that often achieves higher compression rates.

Cost Evaluation — Organizations should evaluate the costs associated with using Hybrid Tables, especially when considering storage requirements and potential performance impacts on their workloads.

Unique Features of Snowflake Hybrid Tables

Snowflake Hybrid Tables come equipped with a range of features that make them uniquely suited for hybrid workloads:

1) Dual-Storage Architecture (Row + Columnar)

As we have already mentioned earlier, Snowflake Hybrid Tables support both row-based (for transactional operations) and columnar storage (for analytics).

2) Fast Single-Row Operations

Unlike traditional OLAP tables, Snowflake Hybrid Tables excel at single-row inserts, updates, and deletes. This capability is crucial for OLTP workloads where speed and low latency are paramount. Users can expect sub-millisecond latencies for these operations, making Snowflake Hybrid Tables suitable for real-time data processing scenarios.

3) Integrity Constraints

Snowflake Hybrid Tables support unique and referential integrity constraints—this means users can enforce primary key and foreign key relationships between tables to maintain data accuracy.

4) Row-Level Locking

Snowflake Hybrid Tables implement row-level locking mechanisms. This feature allows for higher concurrency by enabling multiple transactions to operate on different rows of the same table simultaneously. It significantly reduces contention and improves overall system throughput, especially in scenarios with many concurrent users or applications.

5) Snowflake Indexes for Data Retrieval

Users can create secondary indexes on specific columns to speed up queries. This improves the performance of both transactional workloads and analytical workloads by minimizing the time it takes to retrieve data.

6) Security and Governance

Snowflake Hybrid Tables integrate with Snowflake's existing security and governance features. This includes support for role-based access control, data encryption, and audit logging.

Snowflake Hybrid Tables can tackle a variety of use cases, thanks to these standout features.

Snowflake Hybrid Tables vs Snowflake Standard Tables

Snowflake Hybrid Tables have some unique capabilities, but to truly understand what they can do, it's a good idea to compare them against standard Snowflake tables. This comparison will show you where each type of table shines. ‘

Here's a detailed breakdown of the key differences between Snowflake Hybrid Tables vs Snowflake Standard Tables:

This comparison shows that both table types have their strong points, but they're suited for different jobs:

🔮 Snowflake Hybrid Tables excel in scenarios requiring both transactional and analytical capabilities. They're perfect for operational data stores, real-time analytics, and applications that need low-latency access to current data while also supporting analytical queries.

🔮 Standard Snowflake Tables remain the go-to choice for pure analytical workloads, especially those involving large-scale data processing, complex aggregations, and historical data analysis.

What Are the Benefits of Snowflake Hybrid Tables?

Snowflake Hybrid Tables offer a multitude of benefits that can significantly enhance an organization's data management capabilities. 

Let's explore these advantages in detail:

1) Unified Platform for OLTP and OLAP — Perhaps the most significant benefit of Snowflake Hybrid Tables is their ability to support both transactional and analytical workloads within a single platform.

2) Improved Data Freshness for Analytics — By storing transactional data directly in a format that's also suitable for analytics, Hybrid Tables reduce the latency between data ingestion and analytical availability. This near real-time access to operational data can lead to more timely insights and faster decision-making.

3) Cost-Efficient — Although Hybrid Tables may have a larger storage footprint compared to standard Snowflake tables, they can lead to overall cost savings by eliminating the need for separate OLTP and OLAP systems, reducing data duplication, and simplifying the data infrastructure.

4) Enhanced Data Consistency — The enforcement of primary key and foreign key constraints in Hybrid Tables ensures data integrity and consistency.

5) ACID Compliance — Snowflake Hybrid Tables maintain data integrity and reliability through full ACID compliance, ensuring that all transactions are handled consistently.

6) Improved Concurrency — The row-level locking mechanism in Hybrid Tables allows for higher concurrency compared to table-level locking like in Standard Snowflake Tables, allowing more users or applications to simultaneously interact with the data without experiencing contention issues, leading to improved overall system performance.

7) Improved Data Governance — With data residing in a single platform, it's easier to implement and enforce data governance policies.

8) Support for Agile Methodologies — The flexibility of Snowflake Hybrid Tables aligns well with agile development methodologies. Teams can iterate quickly on both transactional and analytical aspects of their data models without needing to switch between different systems or data formats.

Snowflake Hybrid Tables offer a lot of advantages that can help organizations modernize their data setup, boost efficiency, and get more from their data.

Step-by-Step Guide to Creating and Using a Snowflake Hybrid Table

Now that we've explored the features and benefits of Snowflake Hybrid Tables, let's dive into a practical guide on how to create and use them.

Prerequisites and Environment Setup

Before we begin, ensure that you have the following in place:

• Snowflake Account: Your Snowflake account must be set up in one of the AWS regions where Snowflake Hybrid Tables are available.
• Familiarity with Snowsight: Users should have a basic understanding of the Snowflake Snowsight interface.
• Hybrid Tables Enabled: Users must have a non-trial Snowflake account with Hybrid Tables enabled.
• Appropriate Permissions: Make sure you have the necessary roles and permissions to create and manage Snowflake Hybrid Tables. This typically includes the ability to create warehouses, databases, and schemas.
• Snowflake Environment: You should have access to a Snowflake worksheet or Snowflake CLI to execute SQL commands.
• Storage Integration (Optional): If you plan to load data from external sources like S3 or Azure Blob Storage, set up the appropriate storage integration.
• Some Sample Dataset: Prepare some sample data to load into your Snowflake Hybrid Table.

Step 1—Set Up Your Snowflake Environment

First, log into your Snowflake account and create a new SQL worksheet. This is where we'll execute our commands.

Next, if you don't already have a Snowflake Warehouse to use, create one with the following command:

CREATE WAREHOUSE IF NOT EXISTS hybrid_wh
WITH WAREHOUSE_SIZE = 'XSMALL'
AUTO_SUSPEND = 300
AUTO_RESUME = TRUE;

Now that you have created Snowflake Warehouse let's create a new database and schema to house your Snowflake Hybrid Table:

CREATE DATABASE IF NOT EXISTS hybrid_db;
USE DATABASE hybrid_db;
CREATE SCHEMA IF NOT EXISTS hybrid_schema;
USE SCHEMA hybrid_schema;

Step 2—Create the Snowflake Hybrid Table

Now, let's create our Hybrid Table. Here’s a basic syntax for creating a Hybrid Table using the CREATE HYBRID TABLE command:

CREATE OR REPLACE HYBRID TABLE <table_name> (
  id NUMBER PRIMARY KEY AUTOINCREMENT START 1 INCREMENT 1,
  col1 VARCHAR NOT NULL,
  col2 VARCHAR NOT NULL,
  col3 VARCHAR NOT NULL,
  col4 VARCHAR NOT NULL,
  col5 VARCHAR NOT NULL
  ...
  ...
  ...
  ...
);

In this example, we'll create a simple table to store customer info:

CREATE OR REPLACE HYBRID TABLE customers_table (
    customer_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100) UNIQUE,
    cust_signup_date DATE,
    cust_last_login TIMESTAMP
);

Note the use of the HYBRID keyword in the table creation statement. This is what distinguishes a Hybrid Table from a standard Snowflake table.

Step 4—Configure Table Properties

You can set additional table properties if needed like you can set properties such as time-travel duration and retention policy.

ALTER TABLE <table_name> SET DATA_RETENTION_TIME_IN_DAYS = 1;

Step 5—Load Data into Snowflake Hybrid Table

There are several ways to load data into your Hybrid Table. Let's explore a few options:

1) Using Snowflake INSERT statements:

INSERT INTO customers_table (customer_id, first_name, last_name, email, cust_signup_date, cust_last_login)
VALUES
(1, 'Elon', 'Musk', 'elon.musk@tesla.com', '2024-10-16', CURRENT_TIMESTAMP()),
(2, 'Jeff', 'Bezos', 'jeff.bezos@amazon.com', '2023-09-10', CURRENT_TIMESTAMP()),
(2, 'Mark', 'Zuck', 'mark@meta.com', '2023-09-05', CURRENT_TIMESTAMP());

2) Using Snowflake COPY command (if you have data in a stage):

COPY INTO customers_table
FROM @my_stage/customers.csv
FILE_FORMAT = (TYPE = CSV FIELD_OPTIONALLY_ENCLOSED_BY = '"')
ON_ERROR = 'ABORT_STATEMENT';

3) Using CREATE TABLE AS SELECT (CTAS):

If you have data in another table, you can use CTAS to create and populate your Hybrid Table in one go:

CREATE OR REPLACE HYBRID TABLE customers_table AS
SELECT * FROM existing_customer_table;

Remember, CTAS is the recommended method for bulk loading data into Snowflake Hybrid Tables due to its optimized performance.

Step 6—Adding Indexes to Snowflake Hybrid Table (Optional)

Hybrid Tables automatically create an index on the primary key, but you can add secondary indexes to improve query performance on other columns:

CREATE INDEX idx_signup_date ON customers_table (signup_date);

Step 7—Explore data loaded into the Snowflake Hybrid Table

Now that we've created and populated our Snowflake Hybrid Table, let's explore it:

1) View table properties and metadata:

SHOW TABLES LIKE 'customers_table';

2) Display information about the columns:

DESC TABLE customers_table;

3) List all the Hybrid Tables in your account:

SHOW HYBRID TABLES;

4) List all the indexes in your account:

SHOW INDEXES;

5) Query the data:

SELECT * FROM customers_table WHERE signup_date >= '2023-01-01';

What Are the Limitations of the Snowflake Hybrid Table?

Snowflake Hybrid Tables offer tons of benefits, but it's important to be aware of their limitations to make informed decisions about when and how to use them. Here's a comprehensive list of the limitations of Snowflake Hybrid Table:

1) Unsupported Features

Hybrid tables have a range of unsupported features that can restrict their usability:

• Cloning — Direct cloning of Hybrid Tables is unsupported, though databases and schemas containing Hybrid Tables can be cloned by ignoring them with the IGNORE HYBRID TABLES parameter.
• Clustering Keys — Unlike standard Snowflake tables, clustering keys cannot be applied to Snowflake Hybrid Tables. Data ordering is strictly by the primary key, which limits performance optimizations available in standard Snowflake tables.
• Data Sharing — Snowflake Hybrid tables cannot be shared across different Snowflake accounts, limiting collaborative data usage.
• Dynamic Tables and Materialized Views — These features are not compatible with Snowflake Hybrid Tables, reducing the flexibility for real-time analytics.
• Replication — Snowflake Hybrid tables cannot be replicated across regions, which makes disaster recovery and high-availability setups challenging.
• Fail-safe — Snowflake Hybrid tables do not support fail-safe mechanisms, which are essential for data recovery and redundancy.
• Snowpipe and Streams — Automated data ingestion via Snowpipe and change data capture through streams are not available for Snowflake Hybrid Tables.
• Time Travel and UNDROP — Snowflake Hybrid tables support Time Travel, but only using the TIMESTAMP parameter. Other clauses such as OFFSET, STATEMENT, and STREAM are not supported. This limitation, along with the inability to use the UNDROP command, reduces the flexibility in historical data recovery.
• Search Optimization Service — Snowflake Hybrid table does not support SOS, which limits the ability to optimize query performance for large-scale workloads.

2) Data Size and Throughput Limitations

• Data Size Limitations — Each Snowflake database can store a maximum of 500 GB of active data in Hybrid Tables. Exceeding this limit will block write operations until the size is reduced.
• Throughput Restrictions — Snowflake Hybrid tables are subject to throttling for both read and write operations, capped at approximately 1000 operations per second for a balanced workload (80% reads and 20% writes). Exceeding this limit leads to reduced throughput, potentially impacting performance under high concurrency or large-scale workloads.

3) Consistency and Constraints

Hybrid tables implement a session-based consistency model, which can introduce staleness for changes made outside of the current session. This staleness is typically less than 100ms but could impact certain time-sensitive operations. The consistency model also necessitates setting READ_LATEST_WRITES = true for absolute consistency, which incurs some latency overhead.

On top of that, constraints such as primary, unique, and foreign keys are enforced at the row level only, and they cannot be deferred. This means constraints must be defined at creation time and cannot be altered post-creation, limiting flexibility in schema evolution.

4) Data Loading and DML Limitations

For data loading, CREATE TABLE … AS SELECT is recommended as it is about 10 times faster for bulk loading than traditional methods like COPY or INSERT INTO … SELECT. However, these restrictions limit flexibility in loading strategies, especially for use cases where bulk operations are not practical.

Also, when performing DML operations, Hybrid Tables perform best when using INSERT, UPDATE, and DELETE commands, rather than MERGE, which may not be optimal for scenarios involving complex updates.

5) Data Type Snowflake Indexing Limitations

Certain Snowflake data types are not supported for primary or secondary indexing in Hybrid Tables, including geospatial types (GEOGRAPHY, GEOMETRY), semi-structured types (ARRAY, OBJECT, VARIANT), and vector types (VECTOR). Also, TIMESTAMP_TZ columns are not supported as primary or unique key columns, restricting the type of data that can be efficiently queried or indexed.

6) Transaction Scope and Transient Schemas

Transactions involving Hybrid Tables are restricted to a single database, and Hybrid Tables cannot be created in transient or temporary schemas. 

7) Security Features

Hybrid tables cannot be used in conjunction with Tri-Secret Secure, a critical security feature in some Snowflake accounts.

8) Geographical and Regional Availability

Hybrid tables are only available in select AWS regions. This restricts their usage for organizations that operate in non-supported AWS regions or use other cloud providers such as Azure or Google Cloud Platform (GCP). 

Snowflake Hybrid tables are accessible in the following AWS regions:

Note: Snowflake Hybrid Tables are also not supported in trail accounts and SnowGov regions.

To design your data architecture effectively, you need to know these limitations. Once you understand them, you can decide if Snowflake Hybrid Tables are right for your needs. They have significant advantages in some cases, but aren't always the best option.

Conclusion

Snowflake Hybrid Tables offer a unified solution for both transactional workloads (OLTP) and analytical workloads (OLAP), enabling efficient data management in a single platform. They support high-concurrency operations—such as inserts, updates, and deletes—while serving real-time data to applications and handling operational analytics on live data. Hybrid Tables are also capable of running complex queries on large datasets, making them ideal for modern data workloads that require both real-time and historical data processing.

In this article, we have covered:

• What are Snowflake Hybrid Tables?
• Architecture Overview of Snowflake Hybrid Tables
• Unique Features of Snowflake Hybrid Tables
• Differences between Snowflake Hybrid Tables and Standard Tables
• Benefits of Snowflake Hybrid Tables
• A step-by-step guide to creating and using a Snowflake Hybrid Table
• Limitations of Snowflake Hybrid Tables 

… and much more!

FAQs

What are Snowflake Hybrid Tables?

Snowflake Hybrid Tables are a specialized table type designed to support both OLTP and OLAP workloads efficiently. They combine row-based storage for fast transactional operations with columnar storage for analytical queries, all within a single table structure.

What workloads do Hybrid Tables support? 

Hybrid Tables support a wide range of workloads, including:

• High-concurrency transactional operations (inserts, updates, deletes)
• Real-time data serving for applications
• Operational analytics on current data
• Complex analytical queries on large datasets

Are there limitations on features for Hybrid Tables?

Yes, Hybrid Tables do not support all features available in standard Snowflake tables. Notably, features like Time Travel, Fail-Safe, and Cloning are unavailable for Hybrid Tables.

Can Hybrid Tables be integrated with standard Snowflake tables?

Yes, Hybrid Tables can be integrated with standard Snowflake tables. You can perform operations such as INSERT SELECT or COPY TABLE AS SELECT to combine data from both table types.

Do Hybrid Tables incur additional costs?

Yes, Hybrid Tables typically incur higher storage costs compared to standard tables. They also generate additional charges for requests due to their reliance on serverless resources for row storage operations.

Do Snowflake Hybrid Tables support referential integrity constraints?

Yes, Snowflake Hybrid Tables support referential integrity constraints, allowing you to enforce foreign key relationships between tables.

Can you perform bulk loading into Snowflake Hybrid Tables?

Yes, while Snowflake Hybrid Tables are optimized for small random writes, they can also handle bulk-loading operations effectively.

How does the query optimization work with Snowflake Hybrid Tables?

Queries against Snowflake Hybrid Tables are compiled and optimized within the same query engine as other Snowflake tables, allowing seamless execution without requiring separate processing layers.