Performance & Scale Engineering

A mental model of performance: what actually costs time and money

Before reaching for a tuning trick, internalize the cost structure of an analytical query. Unlike an OLTP point lookup — which is an index seek measured in microseconds — an analytical query is a bulk data-movement pipeline. Its cost is dominated not by CPU cleverness but by how many bytes move, and how far. Four terms account for nearly all of it:

Walk the four terms, biggest first:

• Scan — the dominant term. Reading bytes off object storage (S3) or local SSD is where most queries spend most of their time and where every serverless warehouse meters its bill. On a columnar lakehouse you are billed and slowed almost in proportion to bytes scanned. Cutting scan is the single highest-leverage thing you can do, and it is mostly a matter of layout (partitioning, clustering) and predicates (pruning, projection) — not faster hardware.
• Shuffle / network. A distributed join or a GROUP BY on a high-cardinality key forces the engine to re-partition data across workers by that key. Moving terabytes over the network is the second-biggest cost, and it is the one beginners never see because it is invisible in the SQL text.
• Join strategy. A broadcast join (ship the small side to every worker) is cheap; a shuffle join (re-partition both sides) is expensive. Choosing wrong — or letting the optimizer choose wrong because statistics are stale — turns a 2-second query into a 2-minute one.
• Spill. When an intermediate result does not fit in memory, the engine spills to disk. This is the cliff: a query that was humming along suddenly runs 10–100× slower. Spill is usually a symptom of one of the first three going unmanaged (scanning too much, joining unfiltered, aggregating high cardinality).

Query optimization: touch fewer bytes

Most "slow warehouse" tickets are not warehouse problems — they are queries asking the engine to read far more than the answer requires. The optimizer can only help if the data layout and the query give it something to prune. The core techniques, in rough order of payoff:

• Partition pruning. If the table is partitioned by event_date and the query filters on it, the engine skips entire partitions without reading them. A query over one day of a year-partitioned table reads ~1/365th of the bytes.
• Predicate pushdown. Filters are pushed down to the scan layer (and into Parquet/ORC footers and ClickHouse skip indexes) so rows are discarded before they are decoded, not after.
• Projection pushdown — i.e., stop writing SELECT *. On a columnar store, selecting 4 columns out of 40 reads ~10% of the bytes. SELECT * on a wide telemetry table is the most common self-inflicted cost in the building.
• Broadcast vs shuffle joins. Keep dimension tables small and let the optimizer broadcast them; keep statistics fresh so it makes the right call.
• Pre-aggregation. If a dashboard always asks for daily totals, do not make it re-scan raw rows every load — aggregate once and read the small result (this is the through-line into materialization and the rollup cascade).

A concrete before/after on GridDP's rollup table. Supply ops wants average utilization for one machine on one day. The naïve query and the layout-aware query return the same answer but scan ~100× different volumes:

-- Reads every column of every partition, then filters.
SELECT *
FROM telemetry_hourly
WHERE machine_id = 'm_8f21a'
  AND CAST(event_hour AS DATE) = DATE '2026-06-15';
-- ✖ event_hour wrapped in CAST() defeats partition pruning
-- ✖ SELECT * decodes all 18 columns
-- ✖ filter applied AFTER the scan

-- Filters on the bare partition column, selects only what's needed.
SELECT machine_id, avg(gpu_util_pct) AS avg_util
FROM telemetry_hourly
WHERE event_date = DATE '2026-06-15'   -- partition prune: 1 of 365 days
  AND machine_id = 'm_8f21a'           -- cluster/skip-index prune within day
GROUP BY machine_id;
-- ✔ partition pruning skips 364 days unread
-- ✔ clustering on machine_id skips most files within the day
-- ✔ projection reads 2 columns, not 18

Partitioning, clustering, Z-order & sort keys — in practice

Chapter 05 introduced these as storage concepts; here we choose concrete keys for GridDP and avoid the classic traps. The rule is simple to state and easy to get wrong: layout must mirror the query predicates. You partition and cluster on the columns people filter by, because those are the columns the engine can use to skip data.

• Partition key = the coarse, almost-always-filtered dimension. For every fact in the platform that is time-series or event-shaped, that is event_date. Nearly every analytical query has a time bound, so date partitioning prunes first and hardest.
• Cluster / sort key = the next-most-filtered, higher-cardinality dimension. For telemetry that is machine_id (or (machine_id, gpu_index)). Within a day's partition, clustering co-locates one machine's rows so the engine reads a handful of files instead of all of them. On Iceberg/Delta this is a sort order or Z-order; on Snowflake a clustering key; on ClickHouse the ORDER BY of the MergeTree.
• Z-order is for when two dimensions are both common filters (e.g. machine_id and gpu_index) and you want both to prune reasonably well from a single physical ordering. It is a multi-column locality trick, not a free lunch — it trades a little single-column locality for balanced two-column locality.

Materialization strategy: precompute the common, leave the rare on-demand

Every transformation in the platform sits somewhere on a spectrum from "compute it fresh every time someone asks" to "compute it once and store the answer." Moving right buys speed and cheaper reads; it costs storage, compute to refresh, and staleness. The art is putting each workload at the right point — not defaulting everything to one end.

A ClickHouse materialized view is the workhorse for keeping a hot aggregate current with zero orchestration — it fires on every insert into the source table and folds the new rows into a pre-aggregated target:

-- Target: an aggregating table keyed by the rollup grain.
CREATE TABLE telemetry_1min
(
  event_minute  DateTime,
  machine_id    String,
  gpu_index     UInt8,
  util_avg      AggregateFunction(avg, Float32),
  temp_max      AggregateFunction(max, Int16),
  power_avg     AggregateFunction(avg, Float32),
  samples       AggregateFunction(count, UInt8)
)
ENGINE = AggregatingMergeTree
PARTITION BY toDate(event_minute)          -- coarse: date
ORDER BY (machine_id, gpu_index, event_minute);  -- fine: cluster key

-- The MV: runs on every insert into raw telemetry, no scheduler needed.
CREATE MATERIALIZED VIEW telemetry_1min_mv TO telemetry_1min AS
SELECT
  toStartOfMinute(event_time) AS event_minute,
  machine_id, gpu_index,
  avgState(gpu_util_pct) AS util_avg,
  maxState(temp_c)       AS temp_max,
  avgState(power_w)      AS power_avg,
  countState(*)          AS samples       -- count drives downsampling decisions
FROM telemetry_raw
GROUP BY event_minute, machine_id, gpu_index;

For warehouse-side facts that grow append-only, the incremental model from chapter 07 is the right tool — process only the new rows, never the whole history:

{{ config(
    materialized='incremental',
    unique_key=['event_date','machine_id'],
    incremental_strategy='merge',
    partition_by={'field': 'event_date', 'data_type': 'date'},
    cluster_by=['machine_id']
) }}

SELECT
  event_date,
  machine_id,
  avg(gpu_util_pct) AS avg_util,
  max(temp_c)       AS max_temp,
  sum(rented_seconds) AS rented_seconds
FROM {{ ref('telemetry_hourly') }}
{% if is_incremental() %}
  -- only reprocess the trailing window, not all 365 days
  WHERE event_date >= (SELECT max(event_date) - interval '2' day FROM {{ this }})
{% endif %}
GROUP BY event_date, machine_id

Caching layers: answer without computing

Materialization avoids re-computing transformations; caching avoids re-running identical work. The two compound. GridDP has four caches stacked between a question and the storage layer, each with its own invalidation story:

• Warehouse result cache. Snowflake/BigQuery/Databricks return a byte-identical repeat query from cache for free, with no compute. It is automatic and invalidates the instant the underlying tables change. The catch: it only helps identical SQL, so a semantic layer that emits stable, normalized queries (ch. 08) dramatically raises the hit rate.
• BI extracts. Tools like Tableau/Power BI snapshot a query result into a local columnar extract refreshed on a schedule. Great for fast dashboards over slowly-changing data; the trade-off is explicit staleness equal to the refresh interval — fine for a daily exec dashboard, wrong for a live fleet-health view.
• Semantic-layer / metric cache. The metrics layer from chapter 08 caches computed metric values keyed by (metric, dimensions, time grain). This is the highest-value cache because it is shared across every consumer — a dashboard, a Slack bot, and an AI agent all asking for "GMV last week" hit one warm entry. Invalidate on the upstream model's freshness signal.
• CDN for embedded dashboards. When dashboards are embedded in a provider-facing portal, the rendered JSON/payload can sit behind a CDN with a short TTL — turning thousands of provider page-loads into edge hits instead of warehouse queries.

Taming the telemetry firehose — the headline scale problem

This is the problem the whole guide has pointed at since Start Here: ~150 M raw telemetry rows a day, growing linearly with the fleet. Chapter 01 warned never to route it through the warehouse; here is how. The mechanism is a rollup cascade — successive aggregations to coarser time grains, each with a TTL that expires the finer tier once the coarser one captures what matters.

Each tier is just a ClickHouse AggregatingMergeTree fed by a materialized view like the one above, chained: raw → 1min, 1min → 1hr, 1hr → 1day. The finer tiers carry detail for recent incident debugging; the coarse tiers carry the long history. TTL does the cleanup automatically:

-- Raw stays only as long as anyone debugs at 10s resolution.
ALTER TABLE telemetry_raw
  MODIFY TTL event_time + INTERVAL 7 DAY;          -- then auto-DELETE

-- 1-minute rollup: enough for a month of incident forensics.
ALTER TABLE telemetry_1min
  MODIFY TTL event_minute + INTERVAL 30 DAY;

-- Optional: downsample older 1-min data to colder/cheaper storage
-- before deleting (tiering, ch.05) rather than dropping outright.
ALTER TABLE telemetry_1min
  MODIFY TTL event_minute + INTERVAL 14 DAY TO VOLUME 'cold',
            event_minute + INTERVAL 30 DAY DELETE;

The payoff is structural. Operational dashboards ("is machine m_8f21a healthy right now?") hit the 1-minute tier in ClickHouse with sub-second latency. Analytics and ML ("utilization by GPU model by week, joined to GMV") read the per-GPU-hour and per-GPU-day rollups that have already flowed into the warehouse — a few thousand rows a day instead of 150 million. The expensive raw firehose never touches a SQL analyst, never inflates a warehouse bill, and never has to be scanned for a question a rollup can answer.

Cost/performance & FinOps

Performance and cost are the same lever viewed from two sides: bytes you don't scan are seconds you don't wait and dollars you don't spend. Beyond layout, the big FinOps levers are about elastic, right-sized, workload-isolated compute — tying back to the cost governance of chapter 11.

How the platform scales as the fleet & GMV grow

Capacity planning is not "buy a bigger warehouse." It is knowing the order in which things break, so you fix them in the order they hurt. As GridDP's fleet and GMV grow, three pressure points fail in a predictable sequence:

1. The firehose breaks first. Telemetry volume grows linearly and unavoidably with the GPU count — double the fleet, double the raw rows. This is the first wall, and the rollup cascade is the answer. If the cascade is in place, telemetry scale becomes a near-non-event; if it isn't, this is what falls over at ~2× today's fleet.
2. Concurrency breaks second. As headcount and embedded/AI consumers grow, more queries arrive at once. Symptoms: dashboards queueing, the AI agent timing out. Fixes: result + metric caching (raise the hit rate), workload isolation (heavy can't starve light), and scheduled scale-up during business hours.
3. The warehouse bill breaks third. The slow, creeping one. More data and more users mean more bytes scanned, and the monthly bill outruns revenue if layout discipline lapses. Fixes: the materialization and pruning levers above, auto-suspend, right-sizing, and spot for batch.

Takeaway

• Bytes scanned is the dominant cost term. Every lever in this chapter — pruning, projection, partitioning, clustering, materialization, rollups, caching — exists to touch fewer bytes. "Don't scan what you don't need."
• Layout must mirror predicates: partition coarse on event_date, cluster fine on machine_id; avoid over-partitioning and the small-files trap.
• Materialize the expensive and common; leave the rare on-demand. Views, incremental tables (ch.07), MVs, and rollups are points on one spectrum — place each workload deliberately.
• The rollup cascade is the headline scale move: 150 M raw rows/day become a few thousand queryable rows; raw telemetry never enters the warehouse, only pre-aggregated rollups do.
• Cost = performance from the other side: auto-suspend, right-size, isolate workloads, and run retry-safe batch on cheap interruptible compute — possibly the marketplace's own.
• Plan capacity in failure order: firehose, then concurrency, then bill — and fix in that order.

With the platform fast and affordable at scale, every piece is now on the table. Next we assemble them into one coherent blueprint with the full capacity math. → the end-to-end reference design