There is a dashboard query in your warehouse that scans two years of data to answer a question about last week. It runs every time someone opens the page, costs more than anyone has noticed, and is getting slower as the table grows. Nobody designed it to be slow. It became slow because the table was never laid out for the way it is queried.
This is more than one inefficient query. It is a failure of physical data layout and partition pruning.
Fast queries at scale are less about clever SQL and more about physical design: how data is partitioned, clustered, and stored so the engine can skip the data it does not need. Partition pruning, the engine reading only the partitions a query touches, is the single biggest lever, and it is set long before the query is written.
However, many teams tune queries and add compute when the real problem is that every query scans the whole table, and they pay for that layout on every run.
If you are a Head of Data or platform engineer responsible for warehouse performance and cost, the intent of this article is:
- Define partition pruning and why it dominates query performance
- Walk through the physical-design patterns that make queries fast
- Lay out the controls a production platform needs to keep them fast
To do that, let's start with the basics.
CTO Consolidated Six Observability Tools Into One
An observability consolidation playbook for CTOs paying the observability tax.
What Is Partition Pruning? The Basic Definition
At a high level, partition pruning is the query engine's ability to skip entire partitions of a table that a query's filters cannot match, so it scans only the relevant data instead of the whole table.
To compare:
If a table is a filing cabinet, a full scan is reading every drawer to find one folder. Partition pruning is labeling the drawers by date so you open only the one you need. The folders did not change; the labeling did.
Why Is Partition Pruning Necessary?
Issues that partition pruning addresses or resolves:
- Cutting the data scanned per query, which drives both latency and cost
- Keeping query performance stable as tables grow
- Reducing wasted compute spent reading irrelevant data
Resolved Issues by Partition Pruning
- Turns full-table scans into reads of only the relevant partitions
- Holds query latency steady even as the table grows large
- Lowers cost on scan-priced and compute-priced warehouses alike
Core Components of Fast Query Design
- Partitioning aligned to the most common filter columns
- Clustering or sort order within partitions for secondary filters
- File sizing and compaction tuned to the engine
- Query patterns that include the partition key in filters
- Monitoring of bytes scanned and pruning effectiveness
Modern Fast Query Tools and Techniques
- Partitioned tables in BigQuery, Snowflake micro-partitions, and Redshift sort keys
- Open table formats like Iceberg, Delta, and Hudi with partition and file-level statistics
- Clustering and Z-ordering to co-locate related rows
- Compaction jobs to fix small-file and skew problems
- Query profiling tools that report bytes scanned and partitions pruned
These techniques reflect a simple truth: at scale, performance is designed into the layout, not tuned into the query.
Other Core Issues They Will Solve
- Make warehouse cost predictable and tied to data actually needed
- Keep interactive dashboards responsive as history accumulates
- Reduce contention by freeing compute from wasted scans
In Summary: Partition pruning and physical design turn queries that scan everything into queries that read only what they need.
Importance of Partition Pruning in 2026
Layout-driven performance matters more as data volumes and warehouse bills have grown. Four reasons explain why it matters now.
1. Tables keep growing, and full scans scale with them.
A query that scans the whole table gets slower and more expensive every month. Pruning is what keeps performance flat as data accumulates.
2. Warehouse cost is now a scrutinized line item.
On scan-priced warehouses, bytes scanned is the bill. Poor layout is a recurring cost paid on every query, and it is now visible to finance.
3. Interactive and AI workloads demand low latency.
Dashboards and AI assistants that query the warehouse need fast responses. A full scan that was tolerable in a nightly job is not tolerable interactively.
4. Open table formats made layout a first-class lever.
Iceberg, Delta, and Hudi expose partitioning, statistics, and compaction directly, so physical design is now something data teams own and tune.
Traditional vs. Modern Fast Query Design
- Tune the SQL vs. design the physical layout
- Add compute to brute-force speed vs. scan less data
- Static partitioning set once vs. layout monitored and compacted
- Performance as an afterthought vs. layout designed for query patterns
In summary: Modern query performance is a property of physical design and maintenance, not of clever SQL alone.
Details About the Core Components of Fast Query Design: What Are You Designing?
Let's go through each layer.
1. Partitioning Layer
How the table is divided so the engine can skip data.
Partitioning decisions:
- Partition on the column queries filter by most, often a date
- Avoid over-partitioning that creates tiny files
- Ensure queries actually include the partition key
2. Clustering Layer
How rows are ordered within partitions for secondary filters.
Clustering decisions:
- Cluster or sort on common secondary filter columns
- Co-locate related rows to prune at the file level
- Re-cluster as data and query patterns shift
3. File Layout Layer
How data is physically stored in files.
File decisions:
- File sizes tuned to the engine's sweet spot
- Compaction to fix small-file proliferation
- Statistics maintained so the engine can prune
4. Query Pattern Layer
How queries are written to enable pruning.
Query decisions:
- Filters include the partition key, not just derived columns
- Avoid functions on the partition column that defeat pruning
- Select only needed columns on columnar stores
5. Monitoring Layer
How pruning effectiveness is tracked.
Monitoring decisions:
- Bytes scanned tracked per query
- Pruning rate monitored for regressions
- Expensive full-scan queries flagged
Benefits Gained from Layout Discipline
- Query latency that stays flat as tables grow
- Warehouse cost tied to data actually needed, not table size
- Headroom freed on shared compute by ending wasted scans
How It All Works Together
A table is partitioned on the column queries filter by most, usually a date, and clustered within partitions on common secondary filters. Files are sized and compacted so the engine's statistics let it prune at the file level. Queries are written to include the partition key so pruning actually triggers. Monitoring tracks bytes scanned and pruning rate, flagging the query that regresses to a full scan. The result is a query that reads last week's data by touching last week's partition, not two years of history, and stays fast as the years accumulate.
Common Misconception
Slow queries are fixed by writing better SQL or adding compute.
Slow queries at scale are usually fixed by changing the physical layout so the engine scans less. Better SQL and more compute help at the margins, but a query that scans the whole table is slow because of layout, and layout is where the durable fix lives.
Key Takeaway: Performance is designed into the table before the query is written. The fastest query is the one that never reads the data it does not need.
Real-World Fast Query Design in Action
Let's take a look at how layout discipline operates with a real-world example.
We worked with a company whose dashboards had become slow and expensive as their event table grew, with these constraints:
- Bring interactive query latency back under control
- Cut warehouse cost driven by full-table scans
- Keep performance stable as the table continued to grow
Step 1: Measure Bytes Scanned, Not Just Runtime
Find where the cost and latency actually come from.
- Bytes scanned profiled per query
- Full-scan queries identified
- The most common filter columns documented
Step 2: Partition on the Real Filter Column
Lay the table out around how it is actually queried.
- Partition on the dominant date filter
- Over-partitioning avoided
- Queries confirmed to include the partition key
Step 3: Cluster for Secondary Filters
Order rows within partitions for the next-most-common filters.
- Clustering on frequent secondary columns
- Related rows co-located for file-level pruning
- Re-clustering scheduled as patterns shift
Step 4: Fix File Layout
Tune file sizes and statistics so pruning works.
- Compaction to remove small files
- File sizes tuned to the engine
- Statistics maintained for pruning
Step 5: Monitor and Hold the Line
Keep queries fast as data and usage change.
- Bytes scanned and pruning rate monitored
- Regressions to full scans flagged
- Layout revisited as query patterns evolve
Where It Works Well
- Partitioning aligned to the dominant filter column
- Queries written to trigger pruning, not defeat it
- Bytes scanned monitored so regressions are caught
Where It Does Not Work Well
- Tuning SQL while the table still forces a full scan
- Over-partitioning that creates millions of tiny files
- Filters that wrap the partition column in a function and disable pruning
Key Takeaway: The query that stays fast at scale is the one whose table was laid out for how it is queried, with pruning monitored, not the one that was tuned after it got slow.
Common Pitfalls
i) Adding compute instead of scanning less
Throwing more warehouse compute at a full-scan query buys speed at recurring cost. Fix the layout so the query scans less.
- Profile bytes scanned first
- Partition and cluster for the query patterns
- Reserve added compute for genuinely large needs
ii) Over-partitioning
Too many partitions create small files and metadata overhead that hurt performance. Partition at a grain that matches query patterns.
iii) Defeating pruning in the query
Wrapping the partition column in a function or filtering only on a derived column stops the engine from pruning. Filter on the partition key directly.
iv) Never compacting
Streaming and frequent writes produce small files that erode pruning. Schedule compaction to keep file layout healthy.
Takeaway from these lessons: Most slow-query problems trace to physical layout, not SQL. Partition for the query pattern, keep files healthy, and monitor bytes scanned.
Fast Query Best Practices: What High-Performing Teams Do Differently
1. Design layout from query patterns
Partition and cluster around how the table is actually filtered. Layout follows the workload, not the schema diagram.
2. Measure bytes scanned, not just runtime
Bytes scanned is the cost and the cause. Profile it to find the queries that scan far more than they need.
3. Write queries that enable pruning
Filter on the partition key directly and avoid functions that defeat pruning. The best layout fails if queries do not use it.
4. Keep files healthy
Compact small files and maintain statistics so the engine can prune. Streaming workloads especially need this.
5. Monitor for regressions
Track pruning rate and flag queries that slip back to full scans. Performance erodes silently without monitoring.
Logiciel's value add is helping teams profile where scan cost comes from, redesign partitioning and clustering around real query patterns, and put the compaction and monitoring in place that keep queries fast as data grows.
Takeaway for High-Performing Teams: Focus on physical layout and monitoring. The durable fix for a slow query is reading less data, and that is a design decision, not a tuning trick.
Signals You Are Designing Fast Queries Correctly
How do you know the performance work is set up to succeed? Not in a single tuned query, but in the daily evidence the platform produces. Below are the signals that distinguish programs on the path from programs that look like progress.
The team measures bytes scanned. They can tell you how much data a query reads, not just how long it took, because the bytes are the cost.
Latency is flat as tables grow. Queries that read recent data stay fast because pruning keeps the scan bounded regardless of table size.
Layout follows query patterns. The team can explain why each large table is partitioned and clustered the way it is, in terms of how it is queried.
Files stay healthy. Compaction runs, and the team can show that small-file proliferation is under control.
Regressions get caught. A query that slips to a full scan is flagged by monitoring, not discovered on the bill.
Adjacent Capabilities and Connected Work
This work does not exist in isolation. Fast query design depends on, and feeds into, several adjacent capabilities. Building one without thinking about the others is the most common scoping mistake.
In most enterprise programs, query performance shares infrastructure with the data warehouse, the pipeline that writes the tables, and the cost-management process. It shares team capacity with data platform, analytics engineering, and the analysts who write the queries. And it shares leadership attention with whatever the next data or cost initiative is on the roadmap. Naming these adjacencies upfront helps the program scope realistically and helps leadership see the work as a portfolio rather than a one-off project.
The most common mistake in adjacent-capability scoping is treating each adjacency as someone else's problem. The pipeline that writes small files is your problem. The query patterns the analysts use are your problem. The cost monitoring that catches a runaway scan is your problem. Pretending otherwise pushes work to teams that did not plan for it, and the work returns to you later as a slow dashboard or a surprising bill. Own the adjacencies you depend on; partner with the teams that own them; share the timeline.
Conclusion
Fast queries at scale are designed, not tuned. The discipline that keeps a warehouse responsive and affordable is the same discipline behind any performance work: understand the workload, lay the data out for it, and monitor that the layout keeps working.
Key Takeaways:
- Partition pruning is the biggest lever for query speed and cost
- Performance is a property of physical layout, set before the query is written
- Layout, healthy files, and monitoring keep queries fast as data grows
Designing fast queries requires layout, query, and monitoring discipline. When done correctly, it produces:
- Latency that stays flat as tables grow
- Warehouse cost tied to data actually needed
- Headroom freed on shared compute
- Regressions caught before they reach the bill
Energy Platform Replatformed to Multi-Region Cloud
A migration playbook for VPs of Infrastructure responsible for resilience and regulatory geography.
What Logiciel Does Here
If your queries are getting slow and expensive, profile bytes scanned, partition and cluster for your real query patterns, and monitor pruning before you add more compute.
Learn More Here:
- Warehouse Cost Control: Query Patterns That Quietly Drain Budgets
- Iceberg, Delta, Hudi: A Practitioner Comparison of Open Table Formats
- S3 Data Lake Design: Partitioning, Tiering, and Cost Control at Scale
At Logiciel Solutions, we work with Heads of Data on warehouse performance, physical layout, and cost optimization. Our reference patterns come from production data platforms at scale.
Explore how to make your warehouse queries fast and affordable.
Frequently Asked Questions
What is partition pruning?
It is the query engine's ability to skip entire partitions of a table that a query's filters cannot match, so it scans only the relevant data. It is the single biggest lever for query latency and cost at scale.
Why is my query slow even though the SQL looks fine?
Usually because the table forces a full scan regardless of the SQL. If the table is not partitioned and clustered for how it is filtered, the engine reads everything. The durable fix is physical layout, not SQL tuning.
How do I choose a partition column?
Partition on the column your queries filter by most often, frequently a date. Make sure queries actually include that column in their filters, and avoid partitioning so finely that you create millions of tiny files.
Why does adding more compute not fix slow queries?
More compute speeds up a full scan but does not reduce the data scanned, so the cost and the underlying inefficiency remain and recur on every query. Scanning less through better layout is the durable fix.
What is the biggest mistake in query performance?
Tuning SQL or adding compute while leaving a layout that forces full scans. The fastest query is the one that never reads the data it does not need, and that comes from partitioning and clustering for the workload, plus keeping files healthy and monitoring pruning.
