In this article I want to explain how Snowflake’s new hybrid table support and Unistore architecture opens up exciting new options for both Martech and Adtech use cases.

Hybrid tables

Since its release in 2015, many Snowflake-based data warehousing solutions have involved all OLTP being performed on external transactional databases such as Postgres, MySQL, or SQL Server, with period ETLs into (or out of) a centralised Snowflake data warehouse. Only once the data is in Snowflake (or similar DWH platform) can OLAP be performed on the transactional data. Similarly, performing OLTP on Snowflake OLAP data products involved ETLs from Snowflake into a transactional database. While this solution works well for the most part, it is not without its caveats. Namely:

Data latency between the two systems.
Maintenance of ETL pipelines.
Cost of executing ETL pipelines including request charges on the source database or Snowflake, data processing costs of a compute instances such as an EC2 instance, and data egress charges.

In aid of this, Snowflake has been rapidly developing a range of new features to enable workloads that go beyond the OLAP that Snowflake was initially intended for in 2015, bypassing the need for disparate data sources and complex ETLs. Amongst these new features are Hybrid Tables. While not a novel concept in the world of research (first described by Dr. Hasso Plattner in 2009), in the world of industry, Hybrid Tables are a recent development. On Snowflake, they were first made available in private preview in 2022 and later released into GA in Q4 last year – making now a great time to dive into the implications of this technology in the Snowflake ecosystem and the wider community.

Hybrid tables enable OLTP to be performed within Snowflake and integrated seamlessly with OLAP in a “Unistore” workload, facilitating a range of OTLP use cases from serving transactional data at high concurrency to a web application to business-critical financial transaction systems. The architecture satisfies all requirements of such systems, including entity/referential integrity and high-concurrency point read/write throughput. At the same time, analytic workloads can be carried out just like any standard snowflake table, completely asynchronously (without interruption to) the ongoing high-concurrency transactional processes that keep the application running at low latency.

Columnar vs. Row Storage Architectures (OLAP vs. OLTP)

In standard Snowflake tables, data is organised into compressed immutable columnar files in object storage (S3, Azure Blob, GCS), with one file per column per micro-partition (a logical and physical clustering of records) and each micro-partition containing tens of thousands to millions of records. Snowflake maintains micro-partition metadata including the range of values stored in each of the columnar files. When a query or DML operation with a filter predicate (e.g. ‘where’ clause or ‘join’ condition) is executed against the table, Snowflake does a lookup against the micro-partition metadata such that only files whose value ranges overlap with the filter predicate are downloaded to the warehouse to be scanned. This type of ‘pruning’ works exceptionally well for OLAP workloads which typically involve operations over large spans of a column – such as joins or aggregation. This is for a few reasons:

The process of clustering and pruning described above greatly reduces the amount of data that needs to be scanned, particularly when using predicates over natural dimensions of the data such as dates.
Columnar compression greatly reduces the volume of data being transferred over the network when table data is being copied over from object storage to the compute warehouse.
Columnar storage means only the columns specified in the select clause are transferred.
Warehouses cache table data and interim results such as outputs from joins to expedite sequences of similar queries.

Since the columnar files are immutable, when DML operations are executed against the table, all micro-partitions containing affected records are locked for the duration of the operation – even if only a single record is being updated. This means that if process A is updating a single record when process B executes a DML operation on a single record out of the thousands or millions of records that happen to reside in the same micro-partition, it cannot do so until process A has:

Copied the files containing possibly millions of values from object storage to the warehouse
Decompressed the files
Scanned the files
Processed the files to update the record
Compressed the new created files containing the updated field (remember the columnar files are immutable).
Copied the new file over to object storage (S3)

While this process is efficient on OLAP ‘bulk’-type workloads, as they do not tend to have many concurrent low-volume writes, this architecture would result in poor performance for OLTP-type workloads which typically have high volumes of concurrent random point (single/few records) writes. Similarly for point-read operations, working with columnar files containing thousands to millions of values is also extremely inefficient.

On the other hand, traditional OLTP-optimised databases such as MySQL and Postgres use a row store in which records are stored completely independently of one another. As a result, they have the following properties:

Row-level locking, as opposed to micro-partition level locking, gives this architecture far greater efficiency when dealing with many concurrent random point read/write operations – especially on smaller tables.
Only the relevant records are retrieved during read/write operations, as opposed to the whole micro-partition. When a single operation only deals with a small number of records (as is typical in OLTP), the result is that the volume of data transfer is far smaller due to the reduced redundancy. It should be noted that when dealing with many records per operation (as is typical in OLAP), the columnar compression and column separation outweighs the redundancy.

Enforcement of entity and referential integrity are another essential requirement for guaranteeing data correctness in OLTP. While Snowflake allows users to ‘define’ primary keys on standard tables, this is only descriptive as there is no built-in functionality to enforce primary key uniqueness or referential integrity on standard Snowflake tables. On the other hand, traditional row-store-based databases such as MySQL or Postgres have entity and referential constraints built into them. This means that any operation that tries to break these constraints, such as deleting a record whose primary key is a foreign key in another tables – resulting in an ‘orphaned’ record, would be blocked and result in an error. I spent some time working in a data acceptance testing team for a migration over to Snowflake and found this to be a recurring issue in the presentation layer for an Adobe Campaign Monitor backend.

Snowflake Unistore

Hybrid Tables combine both columnar and row storage architectures into a single logical database object. With the row store as the primary storage, Hybrid Tables satisfy the referential and entity integrity requirements of OLTP and are well optimised for typical OLTP high-concurrency point read/write workloads. Asynchronously, a secondary columnar object storage is maintained. This is identical to that of Snowflake’s standard tables, meaning more Snowflake-typical OLAP workloads involving large scans can be done directly on the table without interruption or performance impact on the ongoing OLTP.

While this may sound complex, Snowflake users only get a single view of the logical Hybrid Table, even though it comprises two underlying data structures. When a query is executed against the Hybrid Table, the query optimiser automatically chooses on which data structure the operation will take place.

Optimised Bulk Loading

Snowflake users have the option of using optimised bulk loading to load data into Hybrid Tables. This method is significantly faster and most cost-effective than loading data into Hybrid Tables incrementally (depending on your solution) when dealing with loading large volumes of data into the table. At the time this was written, optimised bulk loading only kicks in on the initial load into the table – i.e., even if the table is empty but there were records that were deleted, optimised bulk loading will not be used.

Until recently, a limitation of Snowflake Hybrid Tables was that bulk loading could not be used in conjunction with foreign keys as it was only supported by CTAS statements. Only in January 2025, Snowflake announced support for optimised bulk loading with INSERT INTO and COPY INTO statements (provided the table has always been empty), whereby the user can define the foreign keys in the CREATE TABLE statement and then bulk load into it. Further, Snowflake have announced that they intend to add optimised bulk loading for incremental batch loads in the future.

Consistency and Latency Between Row and Columnar Stores

Users can choose between a session-based or global consistency model via the “READ_LATEST_WRITES = true/false” option. If set to false, the default, users can expect data staleness of up to 100ms between sessions (from row-store to columnar-store), and zero staleness within the session. If set to true, there is no data staleness however the latency of operations on the row store may increase by a few milliseconds (according to the Snowflake doc). Ultimately this depends on use case, but in most OLAP scenarios 100ms is negligible.

Cost

Snowflake uses the same compute pricing model regardless of table type. Accounts are charged based same per-second billing of the compute warehouse used for processing on the Hybrid Table. Generally, OLTP should be done on a Hybrid Table using a multi-cluster XS warehouse and scaled ‘out’ rather than ‘up’ with workload – meaning increasing the MAX_CLUSTER_COUNT parameter rather than the warehouse size when defining or altering the warehouse.

Snowflake charges users $40 per compressed TB (at the time this was written, depending on the Snowflake Edition). Users should expect to incur an additional storage cost due to the dual data structure architecture. The secondary columnar storage cost is the same as it would be if it were a standard Snowflake table. In addition to this, users must pay the cost of the row storage – which tends to be higher due to the lack of columnar compression. Therefore, users can expect to pay more than double the storage cost of a standard Snowflake table of equal size, given that they are paying for the combined storage cost of the two architectures.

The solutions that a hybrid-table-based methodology would be replacing would typically involve:

An RDS (or Azure/GCP equivalent) instance hosting the application database – these can be quite expensive.
ETL costs for:
- Execution of extraction queries on the source database.
- Costs of running a compute instance (or lambda function) to execute extraction queries and possibly perform some data transformation.
- Data egress costs of moving data from the RDS instance to the compute instance and onto the Snowflake stage.
- Snowflake compute costs for ingesting the data.

Use Case

With recent improvements in Snowflake’s Hybrid Table technology and release into general availability, the number of organisations incorporating Hybrid Tables into their solutions has grown rapidly.

We have implemented Hybrid tables for use in a real time personalisation system for a ticketing platform. We needed to provide recommendations & promotions within the user visit pre and post ticket purchase. These promotions were part of a Retail Media implementation where we managed by a graph that allowed 3^rd party organisers/promoters to select audiences and define on site promotions for their events. By serving promotion treatment from Hybrid Tables we reduced lookup latency. It also allowed us to maintain unified governance of data . All sensitive data was kept within Snowflake & we avoided data wrangling and synch with 3^rd party data stores. Event transactions from these promotions (impressions/clicks) captured in hybrid data was available for instant analysis via the Unistore & OLAP tables. The performance of Snowflake was key as we had hundreds of concurrent reporting users across the 3^rd party advertiser base.

Snowflake is an excellent environment for generating recommendations for customers – especially with its latest efforts with Snowpark ML and Snowflake Cortex. Precomputed treatments can be bulk generated based on feeds from feeds as well as customer browsing behaviour. A Task (basically Snowflake’s cron job service) can schedule ‘optimised bulk loads’ into the Hybrid Table using a CTAS statement. Single-customer recommendations, which are point-read operations, can then be queried by the web application’s business logic, serving fresh OLAP-generated recommendation data at the “double digit millisecond” latency required by such applications. What really makes this a great use case is that previously you would have had to load precomputed treatments back into some relational database system on a much less frequent basis. With this solution, promotional treatments can be generated and refreshed on a much more frequent basis with much lower latency.

Key Limitations

In their documentation, Snowflake go into detail about the current limitations of Hybrid Tables. Here, I’m just going to outline the three most important limitations that you should consider if you are thinking about implementing Snowflake Hybrid Tables as part of your solution.

AWS Only

At the rate of new features being added to Hybrid Tables, it’s likely these will come to Azure and GCP at some point, however I could not find any mention of any plans to add this technology outside of AWS.

Data Quality & Constraints

This should go without saying but the level of data quality required in a Snowflake Hybrid Table is higher than what is required from a standard snowflake table. Referential integrity, primary keys, uniqueness constraints, and stricter constraints on COPY INTO statements must all be adhered to (like any traditional RDBMS like Postgres) so you may need to do significantly more data processing and cleaning to get the data into a format that is acceptable for OLTP.

Quotas and Throttling

While Hybrid Tables have continuously seen significant improvements in their performance in the last year or so, the number of read/write operations per second is still capped at a quota of only 8,000 operations per second in a balanced 80/20 read/write workload. By contrast, under optimal conditions, typical OLTP-type systems such as Postgres can handle millions of requests per second. If your sole requirement is to maximise performance under and OLTP workload, then Snowflake Hybrid Tables are a long way off in this regard. With this, it is important to note that transactional databases seen decades of incremental optimisations, whereas Hybrid Tables are a relatively new development and are likely to continue to see rapid improvement in the next couple of years.

Conclusion

At present, the Snowflake Unistore is not a one-for-one replacement for transactional databases in all OLTP workloads, and deciding whether this technology is a good fit for your organisation’s solution will require careful consideration of their strengths and limitations – especially regarding throughput. With that being said, even in their infancy, Hybrid Tables have already seen adoption by a range of organisations with a variety of use cases – speaking to the potential unlocked by the ability to seamlessly integrate OLAP with OLTP in a single environment, and with low/zero staleness between the two. While the concept of Unistore has existed for some 15 years, only recently have we seen platforms such as Snowflake, BigQuery, and Power BI include the technology as part of their offerings. Although there are still some limitations, rapid advancements in the technology, combined with growing adoption by organisations, indicate that Unistore and Hybrid Tables may see much more widespread use in the future.