Columnar Storage and Vectorized Execution: Writing SQL That Aligns with How Analytical Databases Actually Process Your Queries

Introduction

You've written what looks like a clean, optimized query. You've added the right filters, avoided SELECT *, and structured your joins thoughtfully. You run it against 500 million rows in Snowflake, BigQuery, or Redshift — and it still takes two minutes. A colleague runs what looks like a nearly identical query and gets results in eight seconds. What's the difference?

Nine times out of ten, the answer lives in a layer most SQL practitioners never think about: the physical execution model. Analytical databases don't process your queries the way PostgreSQL processes a transaction. They store data in columns instead of rows, execute operations across batches of values simultaneously using CPU SIMD instructions, compress data in ways that let the engine skip reading it at all, and prune entire file segments before a single row is evaluated. When your SQL aligns with these mechanics, you get eight-second queries. When it fights against them, you get two-minute ones — and no amount of indexing or query restructuring will save you if you're working against the grain of the engine.

By the end of this lesson, you'll understand how columnar storage and vectorized execution actually work, and more importantly, how to write SQL that exploits these mechanisms rather than defeating them.

What you'll learn:

• How columnar storage physically organizes data and why that changes everything about scan performance
• How zone maps, min/max statistics, and bloom filters allow engines to skip reading data entirely
• How vectorized execution processes batches of values using CPU SIMD instructions, and why this matters for your expressions
• Which SQL patterns are "vectorization-friendly" and which force the engine into slow, scalar row-by-row fallback paths
• How to read execution plans in columnar engines to diagnose whether your query is actually benefiting from these optimizations
• Concrete rewriting strategies for common analytical query patterns that underperform

---

Prerequisites

This lesson is designed for experienced SQL practitioners. You should be comfortable with:

• Writing multi-table analytical queries with aggregations, window functions, and subqueries
• Reading basic query execution plans (EXPLAIN output)
• General familiarity with at least one analytical database (Snowflake, BigQuery, Redshift, DuckDB, Databricks, or similar)

If you want to sharpen your query profiling skills before diving in, Query Profiling and Statistics in SQL: Using EXPLAIN ANALYZE, Buffer Metrics, and Row Estimates to Diagnose Slow Queries provides the foundation you'll need to interpret the execution plan output we'll analyze in this lesson.

---

How Columnar Storage Actually Works

Let's start with the physical reality, because everything else builds from here.

Row Storage vs. Columnar Storage

In a traditional row-oriented database (PostgreSQL, MySQL, SQL Server heap tables), a row's data is stored contiguously on disk. A record for an order might look like this physically on disk:

[order_id=1001][customer_id=42][order_date=2024-01-15][status='shipped'][total=129.99]
[order_id=1002][customer_id=17][order_date=2024-01-15][status='pending'][total=44.50]
[order_id=1003][customer_id=42][order_date=2024-01-16][status='shipped'][total=299.00]

To answer SELECT SUM(total) FROM orders WHERE status = 'shipped', the engine must read every row into memory, including order_id, customer_id, and order_date — columns you never asked for — just to get to status and total. On a 500 million row table, that's an enormous amount of wasted I/O.

In a columnar store (Snowflake, BigQuery, Redshift, Parquet files read by Databricks or DuckDB), each column is stored independently:

-- order_id column file:
[1001][1002][1003][1004]...[1000000]

-- status column file:
['shipped']['pending']['shipped']['cancelled']...

-- total column file:
[129.99][44.50][299.00][89.99]...

To answer that same query, the engine reads exactly two column files: status and total. Everything else stays on disk. On a wide table with 50 columns, this can mean reading 4% of the data that a row store would read. That's not a minor optimization — it's a categorical difference in I/O volume.

Compression: Why Columnar Data Gets Dramatically Smaller

Columnar storage has a second major advantage beyond selective I/O: compression ratios that row stores can't match.

When values from the same column are stored together, they tend to be homogeneous in type and often in value distribution. Engines exploit this with column-specific encoding schemes:

Run-length encoding (RLE): If a status column contains 10 million consecutive 'shipped' values (after sorting), instead of storing the string 10 million times, the engine stores ('shipped', 10_000_000). Reads become trivially fast.

Dictionary encoding: For low-cardinality columns like country, status, or product_category, the engine builds a dictionary and stores integer codes instead of strings. A 20-character country name becomes a 1-byte integer. The dictionary for a column with 200 distinct values fits in CPU cache.

Delta encoding: For monotonically increasing columns like timestamps or sequential IDs, only the differences between consecutive values are stored. A column of Unix timestamps that increment by ~3600 seconds each row stores tiny integers instead of 10-digit numbers.

Bit packing: Integer columns with small ranges (like a rating column with values 1–5) get packed into the minimum number of bits required.

Key insight: Compression isn't just a storage optimization — it's a query execution optimization. When a column is dictionary-encoded, the engine can evaluate WHERE status = 'shipped' by looking up the dictionary code for 'shipped' (one comparison), then comparing integers instead of strings for the entire column. Integer comparison with SIMD instructions is orders of magnitude faster than string comparison.

Real-world columnar compression ratios of 5:1 to 20:1 are common. This means more data fits in memory, more data fits in CPU cache, and less time is spent waiting for I/O.

---

Zone Maps, Min/Max Statistics, and Bloom Filters

Columnar engines don't just read columns more efficiently — they often avoid reading data at all, using metadata structures to skip entire storage units.

Understanding Micro-partitions and Row Groups

Columnar engines divide data into physical storage units called micro-partitions (Snowflake), row groups (Parquet/Redshift), or file segments (BigQuery). A typical micro-partition might contain 100,000 to 1,000,000 rows.

For each micro-partition, the engine maintains metadata about each column:

• Minimum value
• Maximum value
• Count of distinct values (approximate)
• Count of nulls
• Bloom filter membership data (in some engines)

This metadata is read before any actual data, and it allows the engine to make binary decisions: "Does this partition possibly contain any rows matching my filter?" If the answer is no, the entire partition is skipped.

Seeing Zone Map Pruning in Action

Consider this scenario: you have 500 million events rows in a Snowflake table, partitioned naturally by event_timestamp as data was ingested. You run:

SELECT
    user_id,
    event_type,
    COUNT(*) AS event_count
FROM events
WHERE event_timestamp >= '2024-06-01'
  AND event_timestamp < '2024-07-01'
GROUP BY user_id, event_type;

Snowflake reads the zone map metadata for each micro-partition. A partition whose event_timestamp column has max value 2024-05-31 23:59:59 cannot contain any June 2024 rows. It's skipped entirely — no I/O, no decompression, no evaluation. In practice, for a table with data going back 3 years, this query might scan 4% of partitions rather than 100%.

Warning: Zone map pruning only works when the filter column has real correlation with the physical storage order of data. If your events table was loaded in random order, or if rows from all time periods were interleaved, zone map statistics won't help much because every partition will contain a mix of timestamps. This is why cluster keys in Snowflake and sort keys in Redshift exist — they control physical ordering to maximize pruning effectiveness.

When Bloom Filters Help

Some engines (Snowflake, Parquet-based systems with appropriate tooling) support bloom filter metadata for equality predicates on high-cardinality columns. A bloom filter is a probabilistic data structure that can definitively say "this value is NOT in this partition" (no false negatives) while occasionally saying a value is present when it isn't (false positives are possible).

For a query like:

SELECT *
FROM orders
WHERE customer_id = 7429834;

Even without ordering data by customer_id, a bloom filter on that column can often skip the majority of partitions because most partitions won't contain that specific customer. This is most valuable for high-cardinality equality lookups in fact tables.

Writing SQL That Enables Pruning

The practical implication: predicates that can be evaluated using metadata alone should be placed as early and cleanly as possible in your query.

Here's a query pattern that defeats pruning:

-- PROBLEMATIC: Function wrapping prevents zone map usage
SELECT *
FROM events
WHERE DATE_TRUNC('month', event_timestamp) = '2024-06-01';

The engine cannot use zone map statistics here. DATE_TRUNC('month', event_timestamp) is a derived value — the engine would have to compute it for every row to evaluate the predicate. The zone maps store min/max of the raw event_timestamp values, not the truncated version.

Here's the rewrite that enables pruning:

-- CORRECT: Raw column predicates enable zone map skipping
SELECT *
FROM events
WHERE event_timestamp >= '2024-06-01'
  AND event_timestamp < '2024-07-01';

Same logical result, but now the engine can prune partitions using min/max metadata before reading a single row.

Tip: This pattern applies to every transforming function applied to a filtered column: YEAR(date_col), LOWER(text_col), CAST(int_col AS VARCHAR). Any function that transforms the raw stored value before comparison will suppress zone map usage. Always filter on the raw stored value and transform the literal instead.

---

How Vectorized Execution Works

Once the engine has identified which partitions to scan, it needs to actually process the data. This is where vectorized execution enters the picture.

Scalar vs. Vectorized Processing

Traditional row-at-a-time processing (the "Volcano model" used by PostgreSQL and older OLTP engines) works like this:

For each row in the table:
    1. Fetch row from storage
    2. Evaluate WHERE clause predicates
    3. If passes, project selected columns
    4. Pass row to parent operator

This is conceptually clean but CPU-inefficient. Each iteration involves function call overhead, branch mispredictions, and poor CPU cache utilization.

Vectorized execution processes data in batches of values (typically 1,000 to 65,536 values per batch, depending on the engine). Instead of "process one row," the engine does "process a batch of 8,192 values from the total column."

For each batch of 8,192 values from column 'total':
    Apply addition to all 8,192 values simultaneously
    → Single SIMD instruction processes 4-8 values per CPU cycle

Modern CPUs have SIMD (Single Instruction, Multiple Data) instruction sets — AVX-512 on Intel/AMD can process 8 double-precision floats or 16 32-bit integers in a single instruction. Vectorized engines are architected to exploit this. DuckDB is a particularly good example — it was designed from the ground up around vectorized execution and routinely outperforms older column stores on analytical workloads because its SIMD utilization is so efficient.

Key insight: The performance gap between vectorized and scalar execution isn't marginal. For a simple aggregation on 100 million values, a vectorized engine can be 10x to 50x faster than a scalar engine — not because of algorithmic differences, but because of how efficiently it uses the CPU hardware you already have.

What Makes an Expression "Vectorization-Friendly"

Not all SQL expressions vectorize equally. The engine processes entire batches of values together, so anything that introduces per-row branching or variable-length processing degrades vectorization efficiency.

Vectorization-friendly patterns:

Arithmetic operations on numeric types:

-- Processes entire column batch with SIMD arithmetic instructions
SELECT revenue - cost AS profit
FROM sales;

Fixed-length comparisons:

-- Integer comparison: 16 values per SIMD instruction on AVX-512
WHERE customer_segment_id IN (1, 2, 3, 7);

Simple CASE expressions with few branches:

-- Engine can evaluate all branches as masks and blend results
CASE
    WHEN status_code = 1 THEN 'active'
    WHEN status_code = 2 THEN 'suspended'
    ELSE 'unknown'
END

Patterns that degrade vectorization:

Complex user-defined functions (UDFs) in Python or JavaScript:

-- Python UDF: Forces row-by-row execution, destroys vectorization
SELECT my_python_udf(revenue, cost) AS margin
FROM sales;

String functions that return variable-length results mid-pipeline:

-- Variable-length string ops fragment batch processing
SELECT SUBSTRING(description, POSITION('::' IN description) + 2)
FROM products;

Nested correlated subqueries:

-- Correlated subquery executes once per row, not per batch
SELECT
    order_id,
    (SELECT SUM(quantity) FROM order_items WHERE order_id = o.order_id) AS total_qty
FROM orders o;

You can learn more about why correlated subqueries are particularly painful and how to restructure them in Advanced SQL Anti-Patterns: Identifying and Refactoring Common Query Mistakes That Kill Performance at Scale.

---

Practical Query Patterns That Align with Columnar Execution

Now let's translate theory into concrete SQL patterns you can apply immediately.

Pattern 1: Aggressive Early Projection

Every column you include in your query costs I/O, decompression CPU time, and memory. In a columnar engine, this cost is paid per column you touch, not per table you access.

Before:

-- Reads all 34 columns from the orders table
SELECT *
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.order_date >= '2024-01-01'
  AND o.status = 'completed';

After:

-- Reads exactly 5 columns total across both tables
SELECT
    o.order_id,
    o.total_amount,
    o.order_date,
    c.customer_tier,
    c.region
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.order_date >= '2024-01-01'
  AND o.status = 'completed';

The * isn't just an aesthetic problem — it forces the engine to decompress and process every column in both tables. If orders has 34 columns and customers has 28, you're decompressing 62 columns when you need 5. That's reading 1,240% more compressed data than necessary.

Pattern 2: Push Filters Below Joins

Columnar engines have sophisticated query optimizers, but you can help them (and sometimes you have to) by ensuring filters are applied before joins rather than after.

Suboptimal pattern — join first, filter after:

SELECT
    p.product_name,
    SUM(oi.quantity * oi.unit_price) AS revenue
FROM order_items oi
JOIN orders o ON oi.order_id = o.order_id
JOIN products p ON oi.product_id = p.product_id
WHERE o.order_date >= '2024-01-01'
  AND p.category = 'Electronics';

Optimized pattern — filter before joining:

WITH recent_orders AS (
    SELECT order_id
    FROM orders
    WHERE order_date >= '2024-01-01'
),
electronics AS (
    SELECT product_id, product_name
    FROM products
    WHERE category = 'Electronics'
)
SELECT
    e.product_name,
    SUM(oi.quantity * oi.unit_price) AS revenue
FROM order_items oi
JOIN recent_orders ro ON oi.order_id = ro.order_id
JOIN electronics e ON oi.product_id = e.product_id
GROUP BY e.product_name;

Modern optimizers in Snowflake and BigQuery will often push predicates automatically, but the CTE pattern makes it explicit and ensures that even if the optimizer makes a different choice, your intent is unambiguous. For more on how CTEs help structure query intent, see Common Table Expressions (CTEs) for Cleaner SQL.

Pattern 3: Exploit Dictionary Encoding with IN Lists

When a low-cardinality string column is dictionary-encoded, filtering with an IN list is dramatically faster than LIKE patterns.

-- Slower: Pattern matching requires scanning raw string values or complex logic
WHERE product_category LIKE 'Elec%'
   OR product_category LIKE 'Comp%';

-- Faster: IN list can operate directly on dictionary codes (integer comparison)
WHERE product_category IN ('Electronics', 'Computers', 'Electrical Appliances');

With dictionary encoding, the IN list filter is first resolved to a set of integer dictionary codes — say {3, 7, 12} — and then the entire column batch is evaluated as a series of integer membership tests. SIMD instructions can evaluate 16 such tests simultaneously. The LIKE approach, even with optimization, requires substring matching against raw strings.

Tip: This same principle applies to join keys. When joining two tables on a low-cardinality string column (like country_code or status), the engine may use dictionary codes as join keys, effectively turning a string join into an integer join. If both sides use the same dictionary encoding, this can happen without ever decoding the strings at all.

Pattern 4: Avoid Functions on Filtered Columns

We covered this briefly in the zone maps section, but the principle extends beyond pruning to runtime evaluation as well.

-- Forces decompression and per-row function evaluation on 500M rows
WHERE LOWER(email_domain) = 'gmail.com';

-- Better: store pre-normalized data, or transform the literal
WHERE email_domain = 'gmail.com';  -- assuming data is already lowercase

-- If case normalization is truly necessary at query time
WHERE email_domain = LOWER('Gmail.Com');  -- transform the constant, not the column

Moving the function from the column to the literal means the function executes once (on the constant), not 500 million times.

For more complex string operations where transformation on the column is unavoidable, consider whether the computation can be pushed into a computed column or materialized view so the transformation is paid once at write time rather than on every query. String manipulation patterns in detail are covered in Filtering and Transforming Data with SQL String Functions: LIKE, REGEXP, SUBSTRING, and REPLACE.

Pattern 5: Window Functions Over Correlated Subqueries

Correlated subqueries execute row-by-row — they are the antithesis of vectorized execution. Window functions process partitions as batches and vectorize far more effectively.

Row-by-row correlated subquery:

SELECT
    s.sale_date,
    s.salesperson_id,
    s.amount,
    (
        SELECT SUM(s2.amount)
        FROM sales s2
        WHERE s2.salesperson_id = s.salesperson_id
          AND s2.sale_date <= s.sale_date
    ) AS running_total
FROM sales s;

This executes one subquery per row. On 10 million rows, that's 10 million separate scan operations.

Vectorized window function equivalent:

SELECT
    sale_date,
    salesperson_id,
    amount,
    SUM(amount) OVER (
        PARTITION BY salesperson_id
        ORDER BY sale_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total
FROM sales;

The window function scans the sales table once, partitions the data, and computes the running sum within each partition as a batch operation. Execution time difference on a 10 million row dataset: potentially 200x or more.

Window Functions: RANK, ROW_NUMBER, and LAG covers the fundamentals, and Advanced Window Frame Specifications: ROWS, RANGE, and GROUPS Clauses for Precise Rolling Calculations goes deeper on frame clauses that affect how these vectorize.

---

The Aggregation Pipeline: How Columnar Engines Group Data

Aggregation is one of the most common operations in analytical SQL, and understanding how columnar engines execute GROUP BY helps you write it more effectively.

Hash Aggregation and Memory Spilling

Most columnar engines use hash aggregation for GROUP BY. The engine:

1. Reads the relevant column batches
2. Maintains an in-memory hash table mapping group keys to running aggregation state
3. Streams column batches through the hash table, updating accumulators
4. Outputs the final hash table entries as results

The entire operation can be vectorized if the hash table fits in CPU cache. When you're grouping by millions of distinct values, the hash table exceeds cache capacity, and performance degrades because every hash table lookup causes a cache miss.

Practical implication: When possible, filter data aggressively before aggregating to reduce group cardinality.

-- Aggregating all 500M rows across all time, then filtering
-- The GROUP BY hash table must accommodate 45M distinct user-date pairs
SELECT
    user_id,
    DATE_TRUNC('day', event_timestamp) AS event_day,
    COUNT(*) AS events
FROM events
GROUP BY 1, 2
HAVING event_day >= '2024-01-01';  -- Filter AFTER aggregation

-- Filter first, aggregate less data
-- The GROUP BY only needs to handle 60 days × relevant users
SELECT
    user_id,
    DATE_TRUNC('day', event_timestamp) AS event_day,
    COUNT(*) AS events
FROM events
WHERE event_timestamp >= '2024-01-01'  -- Filter BEFORE aggregation
GROUP BY 1, 2;

The HAVING version aggregates all 500 million rows into all historical user-day combinations, then discards most of them. The WHERE version only aggregates the 60 days of data you care about.

Two-Phase Aggregation in Distributed Engines

In distributed columnar engines (Snowflake, BigQuery, Spark), aggregation is typically two-phase:

1. Local phase: Each compute node/worker aggregates its own partition of data (partial aggregates)
2. Global phase: Partial aggregates are shuffled by group key and merged into final results

This shuffle step (the network transfer of partial aggregates) is often the bottleneck for very high-cardinality GROUP BY operations. Understanding this helps interpret why some grouping patterns are slower than others:

-- Low-cardinality group key: shuffle transfers minimal data
-- 3 status values × 1 partial aggregate per node = tiny shuffle
GROUP BY status;

-- High-cardinality group key: shuffle can be expensive
-- 45M distinct user_ids × N nodes = massive shuffle
GROUP BY user_id;

When you must group by a high-cardinality key, ensure you've filtered the dataset as aggressively as possible before the aggregation, and consider whether intermediate rollup levels (from Multi-Level Aggregation with ROLLUP, CUBE, and GROUPING SETS) can serve your reporting needs with less cardinality.

---

Sort Keys, Cluster Keys, and Physical Ordering

We've established that zone map pruning depends on physical data ordering. Let's look at how to design for this deliberately.

Snowflake Cluster Keys

Snowflake automatically organizes data within micro-partitions based on ingestion order. For time-series data that arrives chronologically, event_timestamp effectively becomes a natural sort key, and temporal range queries prune extremely well.

For tables where ingestion order doesn't correlate with common query patterns, you can define explicit cluster keys:

-- Cluster by the most commonly filtered column(s)
ALTER TABLE orders CLUSTER BY (order_date, customer_region);

After clustering, queries filtered on order_date and/or customer_region will prune micro-partitions effectively. Snowflake's background Automatic Clustering service maintains this ordering as new data arrives.

Warning: Don't cluster by high-cardinality columns like order_id or user_id unless those are your primary filter patterns. Clustering improves point lookups on those columns, but it destroys the clustering effectiveness for temporal or categorical queries. Choose cluster keys based on your actual query filter patterns, not on what "seems like a good key."

Redshift Sort Keys

Redshift offers compound and interleaved sort keys. For most analytical workloads:

• Compound sort key: Optimal when your queries consistently filter on the leading columns of the sort key. First-column filter gets maximum pruning; subsequent columns provide progressively less benefit.
• Interleaved sort key: Theoretically better for multi-column filters with varying leading columns, but the maintenance cost during VACUUM is significant and the pruning benefit rarely justifies it on modern Redshift.

For most time-series analytical tables in Redshift, a single-column compound sort key on the primary time dimension (event_date, created_at) is the right default.

BigQuery Partitioning and Clustering

BigQuery uses a different model: explicit partitioning (which controls physical file organization) combined with clustering (which sorts within partitions).

-- BigQuery DDL: partitioned by day on event_timestamp,
-- clustered within each partition by user_segment and event_type
CREATE TABLE analytics.events
PARTITION BY DATE(event_timestamp)
CLUSTER BY user_segment, event_type
AS SELECT * FROM staging.raw_events;

Partition elimination in BigQuery is extremely aggressive. A query with WHERE DATE(event_timestamp) = '2024-06-15' will only read the partition file for that day — no zone map metadata needed, just a direct file selection. Clustering within the partition then enables range pruning at the block level within that file.

Tip: BigQuery charges by bytes scanned, making partition pruning a direct cost optimization, not just a performance one. Always filter on the partitioning column as a DATE() function or range filter on the timestamp column, and BigQuery's query planner will show you "Bytes processed after partition filter" vs "Total bytes" in the query details, letting you verify that pruning is actually occurring.

---

Reading Execution Plans for Columnar Engines

Theory is one thing — verifying that your query is actually exploiting columnar optimizations is another. Let's look at what to check.

Snowflake Query Profile

After running a query in Snowflake, the Query Profile tab shows a node graph of operators with timing and row count information. Key things to look for:

Partitions scanned vs. partitions total: If you see "Partitions scanned: 2,400 / 18,500 total," your zone map pruning is working. If you see "18,500 / 18,500," you have no pruning — check your WHERE clause for function-wrapped filter columns.

Bytes scanned: A sudden jump in bytes scanned for a join that you expected to be small often indicates a column projection problem — you may be reading more columns than necessary because of a SELECT * somewhere upstream.

Spillage to disk: Snowflake will show "Bytes spilled to local storage" or "Bytes spilled to remote storage" when aggregation hash tables or sort buffers exceed memory. This is a strong signal that you need to reduce cardinality or partition your computation.

BigQuery EXPLAIN and Job Details

BigQuery's execution details show slot consumption and stage-level bytes processed. When checking for vectorization alignment:

• "Input bytes" vs. "Output bytes" ratio in a filter stage: A high compression ratio here is good — it means filters are being applied early.
• Stage ordering: Check that filter stages appear before join stages in the execution tree. If a heavy join appears before a filter, the optimizer may be doing more work than necessary.

DuckDB EXPLAIN ANALYZE

DuckDB is particularly transparent about its vectorized execution. Running EXPLAIN ANALYZE shows:

EXPLAIN ANALYZE
SELECT
    product_category,
    SUM(revenue) AS total_revenue
FROM fact_sales
WHERE sale_date >= '2024-01-01'
GROUP BY product_category;

DuckDB's output shows actual row counts at each operator, timing per operator, and thread utilization. Look for the FILTER operator appearing high in the tree (close to the table scan), and check that HASH_GROUP_BY is handling manageable cardinality.

---

Hands-On Exercise

In this exercise, you'll analyze a poorly-performing analytical query, identify the specific columnar execution anti-patterns it contains, and rewrite it to maximize zone map pruning, minimize column I/O, and enable effective vectorization.

Scenario: You're analyzing e-commerce performance data in Snowflake. The following query runs on a fact_orders table (800 million rows, 42 columns) and takes 4 minutes 20 seconds:

-- ORIGINAL QUERY (slow)
SELECT
    *,
    YEAR(o.created_at) AS order_year,
    MONTH(o.created_at) AS order_month
FROM fact_orders o
JOIN dim_customers c ON o.customer_id = c.customer_id
JOIN dim_products p ON o.primary_product_id = p.product_id
WHERE YEAR(o.created_at) = 2024
  AND UPPER(c.customer_tier) = 'PREMIUM'
  AND p.category LIKE 'Elec%'
ORDER BY o.created_at DESC;

Your task: Before reading the solution, identify at least five specific problems with this query and how each one defeats columnar storage or vectorized execution mechanics.

Analysis:

1. YEAR(o.created_at) = 2024 — Function wrapping on the filter column prevents zone map pruning on created_at. Snowflake has min/max statistics on raw timestamp values, not on YEAR() derived values.

2. SELECT * — Forces decompression and I/O for all 42 columns across all three tables, even though the downstream consumer almost certainly needs far fewer.

3. UPPER(c.customer_tier) = 'PREMIUM' — Applies a per-row function to the filter column. Even with dictionary encoding, the engine can't use dictionary codes directly — it must decode, upcase, and compare each value.

4. LIKE 'Elec%' — Pattern matching on a low-cardinality categorical column. If category is dictionary-encoded (very likely), an exact IN list would resolve to integer comparisons instead of string pattern matching.

5. ORDER BY o.created_at DESC on 800 million rows (pre-filter result) — The sort is applied to the full join result. Even if filters reduce rows significantly, this sort may require a massive external merge sort with disk spilling.

6. YEAR() and MONTH() computed in the projection while also filtering on YEAR() — The same function is being computed twice: once in WHERE and once in SELECT. This is redundant computation.

Rewritten query:

-- OPTIMIZED QUERY
SELECT
    o.order_id,
    o.created_at,
    o.total_amount,
    o.status,
    o.primary_product_id,
    DATE_PART('year', o.created_at)   AS order_year,
    DATE_PART('month', o.created_at)  AS order_month,
    c.customer_tier,
    c.customer_region,
    c.lifetime_value_segment,
    p.product_name,
    p.category,
    p.brand
FROM fact_orders o
JOIN dim_customers c
    ON o.customer_id = c.customer_id
    AND c.customer_tier = 'premium'          -- Join condition + filter, assumes normalized casing
JOIN dim_products p
    ON o.primary_product_id = p.product_id
    AND p.category IN ('Electronics', 'Electrical', 'Electronic Accessories')
WHERE o.created_at >= '2024-01-01'
  AND o.created_at < '2025-01-01'            -- Raw column filter: enables zone map pruning
ORDER BY o.created_at DESC;

Changes made and why:

• Replaced YEAR(o.created_at) = 2024 with a range filter on the raw column — enables zone map pruning across micro-partitions
• Replaced SELECT * with explicit columns — drastically reduces I/O from 42-column reads to ~13 targeted columns
• Replaced UPPER(c.customer_tier) = 'PREMIUM' with c.customer_tier = 'premium' (assuming data is normalized) — enables dictionary code comparison
• Replaced LIKE 'Elec%' with IN (...) — exploits dictionary encoding for integer-level comparison
• Eliminated duplicate computation of YEAR() in the SELECT clause by using the already-filtered created_at column

In Snowflake, this rewrite typically reduces execution time by 70–85% on this pattern, depending on the effectiveness of zone map pruning on created_at.

---

Common Mistakes & Troubleshooting

"My query still scans all partitions even though I have a WHERE clause"

Check whether your filter column is actually stored in the table you think it is, or whether it's a join result. Zone maps only apply at the scan layer — after a join, the engine has no micro-partition metadata to exploit.

Also verify the filter is on the raw column value, not a derived expression. Use your engine's query profile to confirm "partitions scanned" is less than "partitions total."

"I added a cluster key but performance didn't improve"

Clustering takes time to take effect — Snowflake's background service must recluster existing data, which can take hours or days on large tables. Check the "Clustering Depth" metric in Snowflake to see how well-clustered the table currently is. A depth of 1–2 is excellent; 10+ means the table is not well-clustered for that key yet.

Also verify that your query's filter columns actually match the cluster key columns. Clustering on order_date doesn't help a query that only filters on customer_region.

"Window functions are slower than I expected"

Investigate whether the window function is causing a sort operation that spills to disk. In Snowflake's Query Profile, look for "Bytes spilled to local storage" in the WindowFunction or Sort nodes. This happens when the partition being sorted doesn't fit in memory.

Solutions: filter more aggressively before the window function, reduce the number of columns in the window function's input, or break very large partitions into smaller sub-queries using partitioning strategies.

"My UDF makes the query much slower than the equivalent built-in function"

This is expected behavior. Python and JavaScript UDFs in Snowflake and BigQuery execute in an external process with serialization overhead and no vectorization. They're row-by-row operations by design. For performance-critical paths, always prefer SQL built-in functions.

If you genuinely need custom logic, check whether it can be expressed as a combination of built-in functions, CASE expressions, or conditional aggregations that the engine can vectorize natively.

"EXPLAIN shows my filters in the wrong order"

Most mature query optimizers (Snowflake, BigQuery) will reorder predicates and push filters down automatically. If you see a filter appearing after a join in the plan, it may be because the optimizer has determined that the join is a hash build and the filter genuinely can't be pushed further — or it may be a case where explicitly restructuring your query with CTEs or subqueries forces the order you intend. Use the query profile timing per node to determine whether the reorder is actually causing a problem before optimizing it away.

---

The Edge Cases: When the Rules Break Down

Very Small Tables Don't Benefit from Columnar Optimization

For tables under a few hundred thousand rows, the overhead of columnar encoding, compression, and vectorized batch processing can actually be slower than a simple row store scan. The fixed overhead of reading metadata, decompressing column segments, and setting up vectorized operations isn't worth it for trivially small data.

Most columnar engines handle this automatically — they may default to full scans for small tables regardless of your query structure. Don't over-engineer filter logic for dimension tables with a few thousand rows; save that energy for the billion-row fact tables.

High-Selectivity Point Lookups Favor Row Stores

Columnar databases are optimized for low-selectivity analytical queries (scan 10% of rows, aggregate everything). For a query like SELECT * FROM orders WHERE order_id = 7439281 that returns exactly one row, a row store with a B-tree index will almost always beat a column store. The column store must decompress multiple column files to reconstruct a single row, while the B-tree lookup is a direct pointer to the row's physical location.

This is why OLTP workloads belong in row stores and OLAP workloads belong in column stores — they're not just different flavors of the same optimization; they're fundamentally different I/O access patterns.

Adaptive Execution Changes the Rules at Runtime

Modern engines like Spark 3.x with Adaptive Query Execution (AQE) and DuckDB change their execution plans at runtime based on actual observed statistics. This means the "optimal" query structure you write based on estimated statistics might be further optimized — or de-optimized — by adaptive decisions the engine makes partway through execution.

For troubleshooting adaptive execution behavior, always compare estimated vs. actual row counts in your execution plans. A large discrepancy (estimated 1,000 rows, actual 10 million rows) means the optimizer's initial plan was built on bad statistics, and adaptive re-planning kicked in. Understanding deterministic and non-deterministic functions is relevant here — volatile functions can make it impossible for the engine to build accurate statistics-based plans.

---

Summary & Next Steps

Columnar storage and vectorized execution aren't abstract concepts — they're the physical mechanisms that determine whether your analytical queries run in seconds or minutes. To consistently write SQL that aligns with these mechanics:

1. Project early and project narrowly. Specify exactly the columns you need. Every extra column costs decompression I/O.

2. Keep filter predicates on raw column values. Function-wrapped filter columns defeat zone map pruning and force per-row evaluation. Move transformations to the literal side.

3. Filter before you join and before you aggregate. Reduce the data volume at the earliest possible point in the pipeline.

4. Prefer built-in functions over UDFs for hot paths. Vectorized execution applies to built-in functions; UDFs typically execute row-by-row.

5. Understand your engine's physical ordering. Whether it's Snowflake cluster keys, Redshift sort keys, or BigQuery partitioning, aligning your primary filter columns with the physical storage order is the highest-leverage optimization available.

6. Use execution profiles to verify, not assume. Check partitions scanned, bytes read, and spill metrics after writing any significant query. The plan is a hypothesis; the profile is the evidence.

From here, deepen your understanding of how analytical databases handle increasingly complex operations. Aggregating Across Groups with SQL Window Functions: SUM, AVG, and COUNT OVER PARTITION BY explores the window function execution model in more depth, and Query Rewriting with Common Subexpression Elimination: CTEs, Derived Tables, and Optimizer Hints for Maximum SQL Performance shows how to restructure complex multi-step queries so the optimizer can apply these vectorized optimizations most effectively.

The engineers who consistently write fast analytical SQL aren't just writing "cleaner" SQL — they're writing SQL that narrates, to the execution engine, exactly what data it needs and when. Understanding the engine is what lets you do that.