I found an old design document recently, from work I did in 2014 on a commodities trading platform. It described a fix to an OLAP processing pipeline that had been timing out and leaving the trading desk without their numbers. Reading it back, what struck me was how little the thinking has changed. The technology is completely different now, but the diagnostic questions I was asking then are the same ones I ask today when a Spark job is slow, a dbt model is taking too long, or a Kafka consumer is falling behind.
That got me thinking about what actually transfers across platforms, and what doesn’t. Platform-specific tuning knowledge has a half-life of about three years. The underlying discipline does not.
I have been lucky enough to work several times during that period side by side with the Database Doctor himself, Thomas Kejser. Thomas spent years on the Microsoft SQL Server team and now builds query optimisers for a living. He has a framework I keep coming back to: databases are just loops over data structures. Every join is a nested loop with a matching strategy, every GROUP BY is a loop accumulating into a hash table, and every scan is a loop over storage blocks. The mysticism evaporates when you think about it this way. When something is slow, the question becomes specific: which loop is doing too much work?
In distributed systems the loops are the same, but data movement between them becomes the dominant cost. A Spark shuffle is a distributed sort-merge join, Kafka consumer processing is a loop over partitioned log segments, and Flink operators are loops with state. The diagnostic question extends naturally: which loop is doing too much work, and is data moving unnecessarily between loops?
The fix I found in that old document contained five principles. None of them were specific to OLAP or 2014. Here is what happened, and why it still matters.
The fix
The pipeline refreshed cube dimensions every cycle, a full ProcessUpdate against Analysis Services. Every dimension member was reprocessed whether it had changed or not. On a good day this took around 700 seconds. On a bad day it timed out.
The fix was to stop doing ProcessUpdate altogether.
I wrote a comparison step that checked maximum dimension keys in the cube against the source tables. If nothing had changed, processing was skipped entirely. If new members existed, the system generated custom XMLA containing inline data source views scoped to only the new rows, and used ProcessAdd instead. Partition creation was batched rather than sequential. The processing logic was extracted from the application into a standalone service with a producer/consumer queue, making it independently testable and deployable.
Processing time dropped from 700 seconds to under 100. The trading desk got their numbers on time. The approach remained in production for years.
1. Change what you do, not how fast you do it
The OLAP fix was not a tuning exercise. It was a redesign. ProcessUpdate was replaced with compare-then-ProcessAdd. The workload itself changed.
The leverage comes from eliminating work entirely, not optimising it. Kejser puts it well: “Aggregate before joining optimisation is often a powerful trick in analytical queries.” His TPC-H benchmarks show SQL Server recognising that a GROUP BY nation, year produces at most 175 rows, so it aggregates before joining to dimension tables, turning 245,000 joins into 175. The database changed what it was doing.
The same pattern appears everywhere. A Delta Lake MERGE in Databricks with matched/unmatched clauses replaces a full table overwrite, writing only the changed files. A BigQuery query against a partitioned table with a date filter scans gigabytes instead of terabytes, because the engine skips partitions that cannot contain relevant data. Snowflake’s metadata-only queries like SELECT COUNT(*) return instantly from micro-partition headers without scanning a single row. A dbt incremental model with a well-chosen filter processes thousands of rows instead of millions.
Change the question the system is answering, and the performance problem often disappears without touching configuration, sizing, or infrastructure.
The cost implications are direct. A BigQuery query scanning 5TB costs roughly 100 times more than one scanning 50GB against a properly partitioned table. In Snowflake, warehouse credits are consumed by compute time, so a query that runs in 2 seconds instead of 200 costs 1% of what it did before. The savings come from doing less, not from doing the same thing on better hardware.
2. Incremental over full
The OLAP system compared max keys to determine what had changed, then processed only the delta. This is the incremental principle: don’t reprocess what hasn’t changed.
Databricks Auto Loader tracks which files have been ingested and processes only new arrivals. Change Data Feed on Delta tables exposes row-level inserts, updates, and deletes so downstream consumers process changes rather than rescanning the full table. Kafka consumer groups track offsets per partition, so each consumer reads only from where it left off. Flink’s incremental checkpointing persists only the state that changed since the last checkpoint, with RocksDB’s LSM tree structure making this efficient even for large state.
In dbt, incremental materialisation is the most common application of this principle, and also the place where it goes wrong most often. A well-designed incremental model on a 100-million-row table processes 50,000 changed rows per run. But the break-even point matters. An incremental model with lookback windows for late-arriving data, schema change guards, and deduplication logic can cost more in developer time and operational complexity than a full refresh on tables under a million rows. The principle is “process the delta,” not “always be incremental.”
Incremental approaches also accumulate assumptions over time. The model assumes this column never changes, this key is unique, this source never backfills. When those assumptions break, the failure is usually silent. Data quality degrades without errors. A periodic full refresh as a reconciliation check is not a failure of the incremental approach. It is part of it.
3. Batch over sequential
The OLAP fix grouped partition creation into batch XMLA operations instead of creating partitions one at a time. Each individual operation was the same. The batching amortised the overhead.
The pattern appears at every layer. Kafka producer batching (controlled by linger.ms and batch.size) sends one network call per batch instead of one per message. The throughput difference between a producer with 100ms linger and one sending messages individually is often an order of magnitude. In Flink, mini-batch optimisation accumulates records before processing them through operators, reducing per-record function call overhead and enabling more efficient state access patterns.
In Spark, the difference between writing many small files and using coalesce to produce fewer, larger files can determine whether a downstream query takes seconds or minutes. Small files defeat columnar compression and generate excessive task scheduling overhead. The same applies to Snowflake micro-partitions and Delta Lake file compaction.
In BigQuery, batch loads are free. Streaming inserts cost money. If the data does not need to be queryable within seconds, the batch path is both faster for large volumes and cheaper.
The anti-pattern here is batching into latency. Kafka linger.ms set too high delays all messages for marginal throughput gains. Flink buffer timeout too large breaks SLA on event-time processing. Batch size is a dial, not a switch, and the right setting depends on whether you are optimising for throughput or latency. The two are in tension by design.
4. Decouple and isolate
The OLAP processing logic was extracted from the application into a standalone service with a ConcurrentQueue producer/consumer pattern. The extraction made the pipeline independently testable, deployable, and observable. It no longer competed with the application for resources.
Separation of compute and storage is the foundational architecture of every modern cloud data platform. Databricks, Snowflake, BigQuery, and Redshift all build on this principle. But decoupling applies at finer granularity too.
In Databricks, job clusters cost roughly 2.5 times less per DBU than all-purpose clusters. They spin up for a workload, execute, and terminate. The workload is isolated from other workloads and right-sized for what it needs. In Snowflake, warehouse separation prevents an ETL pipeline from contending with BI queries. The ETL warehouse can auto-suspend after processing completes while the BI warehouse stays available for interactive use.
In Kafka, the schema registry decouples producers from consumers. A producer can evolve its schema without breaking consumers, provided the evolution follows compatibility rules. Topic partitioning isolates consumer groups from each other. These are architectural choices, not configuration settings, and they determine how well the system handles growth.
The anti-pattern is isolating into waste. Ten idle extra-small Snowflake warehouses cost more than one shared medium warehouse with resource monitors. Isolation without utilisation monitoring is just distributed spending. The principle is to decouple workloads that interfere with each other, not to give every team its own infrastructure by default.
5. Measure before and after
The OLAP fix was documented with performance charts showing before and after times, including averages, minimums, and maximums across processing cycles. A feature toggle allowed safe rollback. The improvement was provable, not anecdotal.
Measurement is the principle people skip most often, and it is the one that makes all the others credible. Without measurement, you are guessing which loop is doing too much work. With it, you know.
Every platform provides the tools. Snowflake’s Query Profile shows exactly where time is spent: scanning, joining, sorting, spilling to disk. BigQuery’s INFORMATION_SCHEMA.JOBS exposes bytes processed, slot utilisation, and cache hit rates per query. The Spark UI in Databricks breaks execution into stages and tasks with detailed metrics on shuffle, spill, and serialisation. Kafka consumer lag monitoring tells you whether consumers are keeping up with producers and where the bottleneck sits.
The discipline is specific: change one thing, measure the impact, confirm the hypothesis. Optimising query execution time when the real cost is data scanning (BigQuery charges by bytes processed, not compute time) is measuring the wrong thing. Measuring warehouse uptime when the problem is auto-suspend timeout burning credits overnight is measuring the wrong thing.
Kejser’s observation applies here too: “Buying a larger server for your database may make it run slower, not faster.” Larger servers often have lower clock speeds. More Snowflake credits do not fix a query that scans every micro-partition because the clustering key is wrong. The measurement tells you where the actual lever is. Without it, you are spending money to feel productive.
The discipline, not the platform
These five principles are not new. “Change what you do” echoes Goldratt’s Theory of Constraints. “Measure before and after” is the scientific method applied to systems. The incremental and batching patterns are as old as database engineering itself. What matters is applying them together as a diagnostic discipline rather than reaching for platform-specific fixes.
The sequence matters too. Start by asking whether the system should be doing this work at all. From there, work through whether it can process only what changed, whether the operations can be batched, whether the workloads should be isolated. Measure to confirm. This order reflects the leverage each principle provides: changing what you do has a larger impact than batching, which has a larger impact than isolation, which has a larger impact than configuration tuning.
Platforms will continue to change. dbt, Snowflake, and Databricks are the current generation. Five years ago it was Hadoop, Pig, and Hive. Five years from now it will be something else. The engineers who navigate those transitions well are not the ones who memorised the current platform’s configuration parameters. They are the ones who can look at any system, understand what it is actually doing, and ask the right question: should it be doing that at all?
The best performance tuning I have ever done, on any platform, was the kind where I changed the question.