I tested datasets ranging from 1 million to 50 billion records. For small tables like 1 million rows or queries, you can achieve crazy-fast execution times, like 10 ms, using effective data pruning.

Instead of benchmarking some industry standards, I just decided to compare real-life scenarios like:

Getting a single record
Getting the aggregate for the whole dataset
Getting the aggregate of one month (to test file pruning)
Joining with a dimension

What is the Reyden engine behind RT Lakehouse?

It’s a new super-fast, sub-second, high-concurrency Spark-compatible SQL warehouse for Unity Catalog tables, written from scratch in Rust and C++ (so Photon’s still there, just without Java) using the most modern hardware from Azure.

An additional advantage, besides being super fast, is that, unlike a classic SQL Warehouse, it can handle thousands of concurrent queries.

What’s the biggest difference between RT Lakehouse and SQL Warehouse (and where SQL Warehouse still shines)

RT Lakehouse is optimized for simple processing of small and medium datasets. For really large datasets (> 100 GB) on which you perform JOINS with other tables, classic SQL Warehouse/Spark is still faster thanks to the distributed way it handles data. For those big datasets, a good new approach is to create Materialized Views powered by Enzyme and consume them later with RT Lakehouse to avoid Joins on RT Lakehouse.

How RT Lakehouse looks

As of today, it’s available only to selected customers. You’ll find it in SQL Warehouse, under “Type”. Autoscaling was not yet available during my tests. In the SQL editor, we can choose RT Lakehouse like any other SQL Warehouse or compute.

What’s the pricing of RT Lakehouse

That part really surprised me. RT Lakehouse, small-sized, costs the same amount of DBUs as a small-sized SQL warehouse. Additionally, there is currently a promotion for RT Lakehouse (30% off).

There is, however, some additional surcharge for XL warehouses, which are around 10% more expensive than SQL warehouses.

So, should you choose RT Lakehouse or Lakebase?

RT Lakehouse is still based on the columnar Delta format, so retrieving a single row should still be faster in a transactional database, since in RT we need to get the whole parquet file, which is usually 1 GB. To achieve few-ms queries, the data layout has to work (to get just one file) so that we can count on milliseconds results. In a row-level database, you can easily achieve a few milliseconds (in this case, it’s columnar). Of course, getting a single record is probably a minor use case, and anything that requires multiple rows or aggregation can be much faster with a columnar format.

RT Lakehouse processes Parquet from Delta/Iceberg formats crazy fast, but it’s not the only feature of RT. Soon we’ll have additional functionalities that will enable us to create real-time architectures, but in this opportunity we’re testing classic Spark SELECTs.

How to get the MS latency by using columnar format from cloud storage

During tests, I learned a few things:

ANALYZE and OPTIMIZE FULL are really important, or just using predictive optimization.
Cache is also important for large tables, as disabling cache or restarting RT Lakehouse can degrade performance (even if our record is new or not in cache, it still helps, since metadata, etc., are cached).
Some functions, like CAST or CURRENT_TIMESTAMP, can slow down retrieval.
To achieve millisecond responses, the number of columns can also be important, as each additional column can add milliseconds.
For complicated queries involving JOINS, it is better to create a MATERIALIZED VIEW first.
And data layout is most important.

Data Layout

Data Layout is the most relevant answer. But while this is also true for Spark and the standard SQL Warehouse, it’s often an underrated feature. Why? We get data as fast as we can, with efficient data pruning.

An example per date is quite classic (CLUSTER BY DATE), as this way we prune other dates.

But what about high-cardinality columns? After years of using classic Hive partitioning, it affected our mindset as we got used to the idea that partitioning has to be per date, etc. But it doesn’t have to be. CLUSTER BY ID for unique values is working amazingly, as this way our dataset will be Z-ordered by ID, and each parquet (our liquid partition) will have stats regarding the min and max values of IDs.

If you don’t know your query pattern, set CLUSTER BY AUTO, and low latency will come.

Test results

Although I managed to get results like 13 ms, I decided to compare the most typical results. I always follow the same approach: stop, start, and warm up with EXPLAIN (except for the CACHE test) before running any given query. In real life, you can get better results; in my experience, warehouses need a long warm-up time 😉 (run at least a dozen commands), and over time, more metadata and data are cached.

My comment and Future of SQL Warehouse and Spark

Results are amazing. I tested SELECT only, but imagine doing MERGE with that SELECT. CTAS is already supported, and there’s even more to come. I think it will be the new gold standard and will replace the current SQL Warehouse. As there is no Java anymore, I’m not sure what the future of open-source Spark will be; maybe, during some of the summits in a year or two, Databricks will open-source a new Native engine written in Rust.

TL; DR

Across 19 benchmark tests spanning 1 million to 50 billion records, RT Lakehouse beat standard SQL Warehouse in every scenario except large-table joins, in some cases by more than 90%. A single-record lookup on a 50-billion-row table went from 66.2 seconds to 0.33 seconds.

Pricing matches a small SQL Warehouse (plus a 30% launch promo), so the speed gain doesn't come with a cost penalty. The trade-off: for joins on datasets above 100 GB, classic SQL Warehouse still wins, and pre-building a Materialized View is the workaround.

Bottom line: for point lookups, aggregations, and filtered scans at any scale, RT Lakehouse is now the default answer.

Repo with test dataset: https://github.com/hubert-dudek/medium/tree/main/topics/202607/databricks_transaction_benchmark_bundle

I benchmarked the new Lakehouse RT: It’s fast, cheap, and open-format