Warehouses, Lakehouses and Scan Cost

Scenario

Example company: product analytics moved to a cloud data warehouse that bills by bytes scanned per query (as BigQuery's on-demand pricing does) or by compute time (as most others do). A popular dashboard tile shows "events in the last 24 hours" and refreshes every five minutes for 200 viewers.

What you see in production

• The warehouse bill triples in a month; one query pattern accounts for most of it.
• Each refresh reads the whole three-year events table, about 9 TB, to return one number.
• Queries are slow at peak because they compete for the same compute.

The naive code

SELECT COUNT(*) FROM events
WHERE CAST(event_time AS DATE) >= CURRENT_DATE - 1;    -- table not partitioned; SELECT * habits elsewhere

Reproduce it

The model estimates bytes read by the tile in three table layouts. Columnar storage reads only the columns a query uses; partitioning by date lets the engine skip old partitions entirely.

days = 3 * 365
rows_per_day = 20_000_000
columns = {"event_time": 8, "user_id": 8, "event_type": 12, "properties_json": 400}   # bytes per value
row_bytes = sum(columns.values())

def tb(n_bytes):
    return n_bytes / 1e12

layouts = {
    "row store, no partitions": days * rows_per_day * row_bytes,
    "columnar, no partitions": days * rows_per_day * columns["event_time"],
    "columnar, partitioned by day": 2 * rows_per_day * columns["event_time"],   # today + yesterday
}

refreshes_per_month = 12 * 24 * 30          # every 5 minutes
for name, scanned in layouts.items():
    print(f"{name:<30} {tb(scanned):8.4f} TB per query, {tb(scanned) * refreshes_per_month:9.1f} TB per month")

row store, no partitions         9.3732 TB per query,   80984.4 TB per month
columnar, no partitions          0.1752 TB per query,    1513.7 TB per month
columnar, partitioned by day     0.0003 TB per query,       2.8 TB per month

Root cause

The query couldn't prune: the table wasn't partitioned by date, and wrapping the partition column in a function (CAST(event_time AS DATE)) can stop engines from pruning even when it is. The dashboard multiplied the cost by refreshing far more often than the data changed.

The fix

• Store analytical data in columnar formats (every warehouse does; for files, Parquet) and select only needed columns, never SELECT * in production queries.
• Partition large tables by the column most queries filter on (usually event date) and filter on that column directly. See BigQuery partitioned tables.
• Cluster (sort) within partitions by frequent filter columns (user, account) so engines skip blocks, as with Snowflake clustering keys.
• Pre-aggregate hot dashboard metrics into small tables updated by the pipeline, and cache dashboard results for as long as the data's freshness allows.
• Set budgets and per-query byte limits where the warehouse supports them.

Warehouse or lakehouse? A warehouse (Snowflake, BigQuery, Redshift) manages storage and compute for you. A lakehouse keeps data as open-format files (Parquet) in object storage with a table format such as Apache Iceberg or Delta Lake, queried by several engines. Choose a warehouse for simplicity; a lakehouse when you need open formats, very large volumes or multiple engines on the same data.

Trade-offs

• Partitions that are too small (hourly on a small table) create overhead and many tiny files.
• Pre-aggregations add pipeline code and can drift from raw data; test them against the source.

Detect it in production

• Review the warehouse's query history for the most expensive query patterns weekly.
• Alert on daily spend versus budget, and on queries scanning more than a threshold.

Key points

• Cost and speed depend on how much data a query reads.
• Use columnar storage, partition by the main filter column, cluster by secondary filters, and avoid SELECT *.
• Pre-aggregate hot metrics and cache dashboards to their real freshness.
• Warehouse for simplicity; lakehouse (Iceberg/Delta) for open formats and multiple engines.