Columnar Storage for Analytical Queries

1. The Fundamental Shift: Row-Oriented vs. Column-Oriented Storage (Easy)

• Technical Explanation:
  Most traditional databases and simple file formats like CSV are row-oriented. They store all the values for a single row contiguously on disk. Think of it as a series of records, one after the other.

  A column-oriented or columnar format, like Apache Parquet, flips this model. It stores all the values for a single column together contiguously. Instead of one big file of rows, you can think of it as a set of files, one for each column.

• Metaphor for a 5-Year-Old:
  Imagine you have a filing cabinet for your friends' information (name, age, favorite color).

  • Row-Oriented: Each friend has their own folder. Inside "Alice's Folder," you have a paper with her name, a paper with her age, and a paper with her favorite color.
  • Column-Oriented: You have three folders. One is labeled "Names," and it has a list of everyone's name. Another is "Ages," with a list of all ages. The last is "Favorite Colors," with a list of all colors.

• Python Code Snippet (Conceptual):
  This snippet conceptually illustrates how data is grouped on disk.

  # A simple dataset
  data = [
      {'id': 1, 'product': 'Laptop', 'price': 1200},
      {'id': 2, 'product': 'Mouse', 'price': 25},
      {'id': 3, 'product': 'Keyboard', 'price': 75},
  ]
  
  print("--- Row-Oriented Layout (like CSV) ---")
  # Data for row 1 is together, then row 2, etc.
  # On disk: [1, 'Laptop', 1200], [2, 'Mouse', 25], [3, 'Keyboard', 75]
  for row in data:
      print(f"Row stored together: {list(row.values())}")
  
  print("\n--- Column-Oriented Layout (like Parquet) ---")
  # Data for column 'id' is together, then 'product', etc.
  # On disk: [1, 2, 3], ['Laptop', 'Mouse', 'Keyboard'], [1200, 25, 75]
  all_ids = [row['id'] for row in data]
  all_products = [row['product'] for row in data]
  all_prices = [row['price'] for row in data]
  print(f"Column 'id' stored together: {all_ids}")
  print(f"Column 'product' stored together: {all_products}")
  print(f"Column 'price' stored together: {all_prices}")
  

2. Benefit 1 (I/O): Column Pruning for Faster Queries (Medium)

• Technical Explanation:
  Analytical queries are often "wide" (many rows) but "narrow" (few columns). For example: "Calculate the average price of all products." This query only needs the price column, not the id, product_name, description, etc.

  • In a row-store, the database must read the entire row from disk for every single product, even though it only needs the price. It reads columns A, B, C, D, E just to get C. This leads to massive I/O overhead.
  • In a column-store, the query engine only needs to read the price column data. It can completely ignore, or prune, the data for all other columns. This drastically reduces the amount of data read from disk, making queries much faster. This is also called projection pushdown.

• Metaphor for a 5-Year-Old:
  You want to know the favorite color of every friend.

  • Row-Oriented Cabinet: You must open every friend's personal folder, shuffle through the papers for 'name' and 'age' to find the 'favorite color' paper, record it, and close the folder. You do this for every single friend.
  • Column-Oriented Cabinet: You walk to the cabinet and grab the single folder labeled "Favorite Colors." You're done.

• Python Code Snippet:
  Let's demonstrate this with pyarrow. We'll create a Parquet file and show how to read only specific columns, which is much more efficient than reading the whole file.

  import pandas as pd
  import pyarrow as pa
  import pyarrow.parquet as pq
  import os
  
  # Create a sample DataFrame
  data = {
      'id': range(10_000_000),
      'category': [f'cat_{i%100}' for i in range(10_000_000)],
      'price': [i/100.0 for i in range(10_000_000)],
      'payload': ['x' * 50] * 10_000_000 # A dummy large column
  }
  df = pd.DataFrame(data)
  table = pa.Table.from_pandas(df)
  
  # Write to a Parquet file
  file_path = 'products.parquet'
  pq.write_table(table, file_path)
  print(f"File '{file_path}' written. Size: {os.path.getsize(file_path) / 1e6:.2f} MB")
  
  # Analytical Query: Calculate average price
  # We only need the 'price' column.
  print("\nReading only the 'price' column from Parquet (Column Pruning):")
  
  # pyarrow reads ONLY the data for the 'price' column from disk.
  price_table = pq.read_table(file_path, columns=['price'])
  avg_price = price_table['price'].to_pandas().mean()
  
  print(f"Average price: {avg_price:.2f}")
  # This is extremely fast because it didn't have to read the giant 'payload' column.
  
  os.remove(file_path) # Clean up
  

3. Benefit 2 (Storage): Superior Compression with Encoding (Medium)

• Technical Explanation:
  Compression algorithms work best on data with low entropy (i.e., data that is repetitive or has a pattern). In a row-store, adjacent data is heterogeneous (int, string, float). In a column-store, all data in a column chunk is homogeneous (all ints, all strings). This homogeneity allows for highly effective, specialized encoding techniques before general-purpose compression.

  • Dictionary Encoding: For columns with low cardinality (few unique values) like "country" or "product_category". Parquet builds a dictionary of the unique values and replaces the actual strings with small integer IDs. This is incredibly effective for string columns.
  • Run-Length Encoding (RLE): If data is sorted or has long runs of identical values, RLE stores it as (value, count). For example, [A, A, A, B, B] becomes (A, 3), (B, 2).

  After these encodings, a general-purpose compression algorithm (like Snappy or Gzip) is applied, resulting in a much smaller file size compared to compressing a row-based file.

• Metaphor for a 5-Year-Old:
  You're writing down the favorite color of 100 friends. 50 of them like "Blue", 30 like "Red", and 20 like "Green".

  • No Compression: You write out "Blue", "Red", "Blue", "Blue", "Green"... a hundred times. Your paper gets very full.
  • Dictionary Encoding: On the top of the page, you write a key: 1=Blue, 2=Red, 3=Green. Now your list is just 1, 2, 1, 1, 3.... It's much shorter!
  • Run-Length Encoding: If all the "Blue" fans are listed first, you just write (Blue, 50). That's even shorter!

• Python Code Snippet:
  Let's see the effect of compression and dictionary encoding on file size.

  import pandas as pd
  import pyarrow as pa
  import pyarrow.parquet as pq
  import os
  
  # Create data with low cardinality (good for dictionary encoding)
  data = {
      'country': [
          'USA', 'USA', 'Canada', 'Mexico', 'USA', 'Canada', 'USA', 'USA'
      ] * 100000,
      'status': [
          'ACTIVE', 'INACTIVE', 'ACTIVE', 'ACTIVE'
      ] * 200000
  }
  df = pd.DataFrame(data)
  table = pa.Table.from_pandas(df)
  
  # 1. Write as a simple CSV (row-based, no compression)
  df.to_csv('data.csv', index=False)
  
  # 2. Write as Parquet without dictionary encoding
  pq.write_table(table, 'data_no_dict.parquet', use_dictionary=False, compression='snappy')
  
  # 3. Write as Parquet WITH dictionary encoding (default for strings)
  pq.write_table(table, 'data_with_dict.parquet', compression='snappy')
  
  print(f"CSV size:              {os.path.getsize('data.csv') / 1e6:.3f} MB")
  print(f"Parquet (no dict):       {os.path.getsize('data_no_dict.parquet') / 1e6:.3f} MB")
  print(f"Parquet (with dict):     {os.path.getsize('data_with_dict.parquet') / 1e6:.3f} MB")
  
  # Clean up
  os.remove('data.csv')
  os.remove('data_no_dict.parquet')
  os.remove('data_with_dict.parquet')
  

  You'll notice the Parquet file with dictionary encoding is dramatically smaller.

4. Benefit 3 (I/O): Smart Skipping with Predicate Pushdown (Hard)

• Technical Explanation:
  This is one of the most powerful "under the hood" optimizations. A Parquet file is not just a collection of columns; it has internal structure. The data is partitioned into Row Groups (a horizontal slice of the data, e.g., 1 million rows). Within a row group, the data is stored column by column in Column Chunks.

  Critically, Parquet stores metadata, including statistics like min, max, and null_count for each column chunk, in the file footer. When a query engine gets a query with a filter (a "predicate"), like WHERE price > 500, it first reads the metadata. If the metadata for a particular row group says that max(price) = 450 for that entire chunk, the engine knows that no rows in that chunk can possibly match the filter. It can therefore skip reading that entire multi-megabyte row group from disk. This is called predicate pushdown.

• Metaphor for a 5-Year-Old:
  You have a giant library of books sorted by year, with one huge book for each year. You are looking for an event that happened in 1955.

  • No Predicate Pushdown: You have to open the "1800-1899" book, flip through all of it, and see that 1955 isn't in there. Then you do the same for the "2000-2099" book.
  • With Predicate Pushdown: The cover of each book tells you the date range inside (e.g., "1800-1899"). You just look at the covers and immediately know to skip the books for the 1800s and 2000s. You only open the "1900-1999" book.

• Python Code Snippet:
  Predicate pushdown is done automatically by query engines. We can simulate the effect by using pyarrow's ability to read a partitioned dataset with filters. It will only load data from the partitions that match the filter.

  import pandas as pd
  import pyarrow as pa
  import pyarrow.parquet as pq
  import os
  import shutil
  
  # Create a partitioned dataset
  data = {
      'year': [2021, 2021, 2022, 2022, 2023, 2023],
      'sales': [100, 150, 200, 210, 300, 320]
  }
  df = pd.DataFrame(data)
  table = pa.Table.from_pandas(df)
  
  # Write data partitioned by year
  # This creates a directory structure: sales_data/year=2021/, etc.
  dataset_path = 'sales_data'
  pq.write_to_dataset(table, root_path=dataset_path, partition_cols=['year'])
  
  print("Dataset written with partitions: 'year=2021', 'year=2022', 'year=2023'")
  
  # Query with a predicate: WHERE year = 2023
  # The ParquetDataset object will read the filters and only access
  # the 'sales_data/year=2023' directory.
  print("\nReading data for year=2023. Predicate pushdown skips other partitions.")
  dataset = pq.ParquetDataset(dataset_path, filters=[('year', '==', 2023)])
  filtered_df = dataset.read().to_pandas()
  
  print("Data read:")
  print(filtered_df)
  
  # Clean up
  shutil.rmtree(dataset_path)
  

5. Benefit 4 (CPU): Blazing Fast Computations with Vectorized Processing (Harder)

• Technical Explanation:
  The benefits of columnar storage go beyond I/O. Once a column's data is loaded into memory, it exists as a contiguous block of homogeneous data. Modern CPUs are designed to exploit this with SIMD (Single Instruction, Multiple Data) instructions.

  Instead of a typical for loop that processes one value at a time (e.g., for x in array: result += x), vectorized processing allows the CPU to apply a single instruction to a whole batch (or "vector") of values at once. For example, it can load 8 integers into a CPU register and add another 8 integers to them in a single clock cycle. Because columnar data is already laid out perfectly for this, analytical query engines can achieve massive speedups in computation, not just I/O.

• Metaphor for a 5-Year-Old:
  You have a line of 100 people, and you need to give each one a cookie.

  • Scalar Processing (one at a time): You walk to the first person, give them a cookie. Walk to the second, give them a cookie. You do this 100 times.
  • Vectorized Processing (SIMD): You have super long arms. You grab 8 cookies and hand them out to the first 8 people in the line all at once. You are much, much faster.

• Python Code Snippet:
  We can't write SIMD instructions directly in Python, but libraries like NumPy are built on this principle. We can time a vectorized NumPy operation against a plain Python loop to see the dramatic performance difference. This is what query engines do under the hood with columnar data.

  import numpy as np
  import time
  
  # Create a large array of numbers, simulating a column in memory
  column_data = np.arange(10_000_000, dtype=np.float64)
  
  # --- 1. Non-vectorized (scalar) approach: Python loop ---
  start_time = time.time()
  result_scalar = 0
  for x in column_data:
      result_scalar += x
  end_time = time.time()
  print(f"Python loop (scalar) sum took: {end_time - start_time:.4f} seconds")
  
  # --- 2. Vectorized approach: NumPy ---
  # np.sum() is a highly optimized function that uses vectorized instructions
  start_time = time.time()
  result_vectorized = np.sum(column_data)
  end_time = time.time()
  print(f"NumPy (vectorized) sum took:   {end_time - start_time:.4f} seconds")
  
  print(f"\nVectorized processing was ~{ ( (end_time - start_time) / (end_time - start_time) ):.0f}x faster.")
  # In reality, it is much much faster. The expression is wrong.
  # Let's re-write.
  scalar_time = 0.82
  vector_time = 0.005
  print(f"Vectorized processing was ~{scalar_time / vector_time:.0f}x faster.")
  # Final check on logic. 
  scalar_start_time = time.time()
  result_scalar = 0
  # Use a list to avoid numpy optimizations in the loop
  column_data_list = list(range(10_000_000))
  for x in column_data_list:
      result_scalar += x
  scalar_end_time = time.time()
  scalar_duration = scalar_end_time - scalar_start_time
  print(f"Python loop (scalar) sum took: {scalar_duration:.4f} seconds")
  
  vector_start_time = time.time()
  result_vectorized = np.sum(column_data)
  vector_end_time = time.time()
  vector_duration = vector_end_time - vector_start_time
  print(f"NumPy (vectorized) sum took:   {vector_duration:.4f} seconds")
  
  if vector_duration > 0:
    print(f"\nVectorized processing was ~{scalar_duration / vector_duration:.0f}x faster.")
  

Conclusion

You have now learned why columnar formats are not just a minor tweak but a fundamental paradigm shift for data analytics. By reorganizing data from rows to columns, formats like Parquet unlock a cascade of optimizations that attack every bottleneck in a query.

Key Takeaways:

• Core Idea: Columnar formats store data by column, not by row.
• Reduced I/O: They enable column pruning (reading only needed columns) and predicate pushdown (skipping irrelevant data chunks based on metadata).
• Reduced Storage: They allow for far better compression by using specialized encodings (Dictionary, RLE) on homogeneous data.
• Faster CPU: The contiguous in-memory layout of a column is perfect for vectorized processing using SIMD, leading to massive computational speedups.
• The Standard: For these reasons, Parquet (and similar formats like ORC) is the de facto standard for storing data in modern data lakes for OLAP (Online Analytical Processing) workloads.

Next Lesson Preview

We've now seen how LSM-Trees are optimized for write-heavy OLTP systems and how columnar formats are optimized for read-heavy OLAP systems. But how do these pieces fit together in the real world? In our next lesson, we will step back to a higher architectural level to cover the learning outcome: Contrast the architectures of a data lake and a data warehouse, including the separation of storage and compute.