MPP & Warehouse Architecture

A data warehouse stores data in an engine-managed, usually proprietary format with an enforced schema and integrated compute. A data lake stores raw files in object storage that many engines can read, with structure applied only at query time.

A cluster has a fixed budget of memory, CPU and I/O. Past a point, extra concurrency splits that budget so thinly that queries spill to disk and contend for the same resources, so queueing the excess finishes more work per hour.

An exchange moves rows between compute nodes over the network. The planner inserts one wherever an operator needs matching key values on the same node and the current data placement does not already guarantee that — joins, grouping, global sorts, and the final gather.

Resuming provisions fresh worker VMs whose memory and local SSD caches are empty, so the first query reads everything remotely from object storage. Subsequent runs hit warm local caches and skip most of that network traffic.

A lakehouse adds an open table-format metadata layer over lake files, so a directory of files behaves like a table: atomic commits, consistent snapshots for readers, schema enforcement and evolution, row-level updates and deletes, and statistics for pruning — while staying readable by many engines.

First confirm the slowdown is queue wait or resource contention, not a plan change. Then move ETL onto its own compute pool over the same shared storage — the only fix that removes interference outright. Priorities and memory caps reduce it but never eliminate it.

The shuffle key is skewed: a few values, often NULL or a placeholder, own most of the rows, so one node receives that whole bucket. Runtime equals the slowest node. Fix by isolating or salting the hot values, or by pre-aggregating before the shuffle.

Each compute node owns an exclusive subset of the table's rows and can read only that subset. Rows are placed by hashing a column, by round-robin, or by replicating the whole table everywhere. Every node then runs the same plan over its own share.

It compares network cost. Broadcasting sends the smaller input's bytes to every node, so cost scales with node count; redistributing sends each row of both inputs once. Broadcast wins when one side is small enough to fit in every node's memory.

A result cache stores finished query results in the shared service layer and can answer a repeat query without running compute at all. A local data cache stores raw column chunks on a cluster's own SSDs and still requires the cluster to execute the query.

Schema-on-write validates structure when data is loaded, so anything stored already conforms and bad rows are rejected at the source. Schema-on-read stores data as it arrives and interprets it at query time, so ingestion is cheap but each consumer bears the parsing and quality risk.

Adding clusters adds concurrency, so it removes queue wait when many queries arrive at once. It does not make an individual query faster: a single long scan runs on one cluster at the same speed, and new clusters start with cold caches.

Layer them: an admission-time check on estimated bytes scanned, a per-class statement timeout, caps on memory grant and spill, a result-size limit, and a spend monitor that suspends compute. Prefer demoting a query to killing it where possible.

Cluster memory is not one pool. Each worker gets a fraction of one node's RAM, divided again among concurrent queries, and a shuffle can hand one worker far more than its even share. That worker spills alone while the cluster looks half empty.

Per-byte-scanned billing wins on spiky, low-duty-cycle workloads whose queries prune well, because you pay nothing between queries. Provisioned compute wins when a cluster stays busy, when queries repeatedly scan the same large data, and when caching amortizes across many statements.

Keep data external when it is queried rarely, is large and cheap to retain, is still raw or evolving, or must stay readable by other engines. Load it when queries are frequent, latency-sensitive or highly concurrent, because loading buys layout control, statistics, clustering and caching.

Split by workload class and SLA, not by org chart. Give guaranteed isolation only where a latency or freshness commitment justifies its idle cost; let everything else share pools with priorities, chargeback by usage, and revisit as workloads change.

Scale-out only shrinks the local work. Redistribution volume is roughly constant in node count, broadcast volume grows with it, skewed keys stay on one node, and the final gather stays serial. Attribute the runtime per stage before buying capacity.

Forecast the two curves separately: storage cost tracks retained bytes, compute cost tracks queries and duty cycle. The trap is second-order coupling — unpruned queries and a working set that outgrows local caches turn storage growth into compute growth.

Decide on how many engines must read the data, how much governance and performance you need out of the box, and how much platform work you can staff. Warehouse-managed storage buys integration and predictability; open formats buy engine choice and exit options, paid for in operational ownership.

Because a FIFO queue serves in arrival order: a 20-minute query holding a slot blocks everything behind it, however cheap. This is head-of-line blocking, and the fix is separate admission classes with reserved capacity for short queries.

Federation lets an analytical engine query a live operational database directly, avoiding a pipeline and giving current data. It costs you: the analytical engine cannot prune or parallelise inside the remote system, large pulls cross the network, and heavy queries add load to a production database.

In a separate transactional metadata service. It records which immutable files make up the current version of each table, their statistics, and the commit order, so a write is a metadata swap and every compute cluster sees one consistent view.