Columnar Storage

Unlike row-oriented storage (OLTP), where all fields of a single record are stored together, columnar storage writes all values for a single column or attribute sequentially. This means a query reading price and quantity columns only accesses the specific blocks containing that data, dramatically reducing I/O.

• Example: In a 1-billion row sales table, a SUM(revenue) query reads only the revenue column's contiguous blocks, skipping all other column data (e.g., customer_name, product_description).
• Contrast: Row-stores excel at reading/writing full records (e.g., SELECT * FROM orders WHERE order_id = 123).

Storing similar data types contiguously (e.g., all integers, all dates) enables highly effective column-specific compression. Techniques like run-length encoding (RLE), dictionary encoding, and delta encoding achieve compression ratios often 5-10x better than row-oriented formats.

• Run-Length Encoding: Compresses repeated consecutive values (e.g., region_id: 5,5,5,5 becomes 5x4).
• Dictionary Encoding: Replaces long string values with compact integer keys.
• Impact: Reduced storage costs and, critically, less data is read from disk into memory, accelerating query performance.

Modern analytical engines (e.g., Apache Arrow, DuckDB, Snowflake) leverage columnar layouts to perform vectorized processing. Instead of processing one row at a time, the engine loads a chunk of values from a single column into a CPU cache-friendly vector (e.g., 1024 integers) and applies operations (like a filter or sum) to the entire vector in a tight loop. This minimizes CPU branch mispredictions and leverages SIMD (Single Instruction, Multiple Data) instructions for parallel computation at the hardware level.

Columnar storage allows query optimizers to push filters (predicates) down to the storage layer before assembling full rows.

1. Predicate Pushdown: A query with WHERE status = 'shipped' scans only the status column, builds a bitmap of matching row positions, and uses it to selectively read other needed columns.
2. Late Materialization: The join of columns into full rows is deferred until the final step of the query, after aggregations and filters have been applied on the columnar data. This avoids the expensive cost of building intermediate row sets.

Columnar storage is implemented in several industry-standard, open-source file formats that are the backbone of modern data platforms:

• Apache Parquet: The most widely adopted columnar format, offering rich nested data support, efficient compression, and excellent integration with Hadoop/Spark ecosystems.
• Apache ORC: Optimized Row Columnar format, often used with Apache Hive, known for strong ACID transaction support.
• Commercial Implementations: Google BigQuery, Amazon Redshift, and Snowflake all use proprietary internal columnar formats to deliver their performance.

Columnar storage is not a universal solution. Its advantages come with specific trade-offs:

• Optimized For: Analytical queries, data warehousing, OLAP, aggregations, and scans over large subsets of columns.
• Poor For: Online Transaction Processing (OLTP), high-volume single-row inserts/updates/deletes, or queries that frequently select all columns (SELECT *).
• Write Amplification: Inserting a single row requires writing data to as many column files as there are columns, making writes slower than in row-stores. This is mitigated by batch writing.

These are Online Analytical Processing (OLAP) database management systems built from the ground up using a columnar storage architecture, not just a file format.

• Examples: Amazon Redshift, Google BigQuery, Snowflake, Vertica, and ClickHouse.
• Key Advantage: Beyond storage, they implement columnar-aware query executors, memory management, and indexing (e.g., zone maps) for superior scan performance on aggregations and filters.
• Differentiation: While they often use formats like Parquet for data exchange, their internal storage is proprietary and optimized for their specific execution engines.

Major cloud data warehouses use proprietary, optimized columnar formats internally, though they support open formats for ingestion.

• BigQuery: Uses Capacitor, its internal columnar format, which handles nested/repeated data efficiently and enables free BigQuery ML transformations.
• Snowflake: Uses an internal, compressed columnar format optimized for its virtual warehouse compute clusters and cloud storage layer.
• Redshift: Originally used a proprietary columnar format; Redshift Spectrum and modern features leverage Parquet/ORC directly from Amazon S3.

Table formats like Apache Iceberg, Delta Lake, and Apache Hudi add a management layer on top of columnar files (Parquet/ORC) to enable ACID transactions and advanced features in data lakes.

• Function: They manage collections of Parquet/ORC files as a single table, providing transactions, time travel, schema evolution, and hidden partitioning.
• Impact: They transform a collection of static columnar files into a dynamic, reliable data lakehouse. The underlying columnar storage provides the query performance, while the table format provides data management.

An open-source, columnar storage file format optimized for analytical workloads. It is the de facto standard for storing large datasets in data lakes and lakehouses.

• Core Features: Highly efficient data compression and encoding schemes (like dictionary and run-length encoding).
• Schema Evolution: Supports adding, removing, or modifying columns without breaking existing data.
• Wide Ecosystem: Native integration with major processing engines like Apache Spark, Dremio, and Presto.
• Use Case: The primary file format for persisting query results from a data warehouse or for training datasets in machine learning pipelines.

A modern data architecture that merges the flexibility and low-cost storage of a data lake with the structured data management and performance of a data warehouse.

• ACID Transactions: Ensures reliable, consistent reads and writes over object storage.
• Open Formats: Built on open standards like Apache Parquet, enabling vendor-agnostic data access.
• Unified Governance: Provides a single platform for BI, SQL analytics, and machine learning workloads.
• Key Technologies: Implemented via open table formats like Apache Iceberg, Delta Lake, and Apache Hudi.

An open-source table format for managing large, analytic tables in data lakes. It solves critical data lake challenges around reliability, performance, and schema evolution.

• Hidden Partitioning: Queries filter on data, not directory paths, preventing incorrect results from incorrect filters.
• Time Travel & Rollback: Query data as it existed at a specific point in time or roll back to a prior state.
• Schema Evolution: Supports safe, in-place changes like adding, dropping, or renaming columns.
• Performance: Uses metadata files and manifests for fast planning and efficient data skipping.

A centralized repository for storing, managing, and serving precomputed feature data for machine learning models. It ensures consistency between training and inference.

• Feature Registry: Catalog of defined features with metadata, lineage, and versioning.
• Dual Serving: Supports both offline/batch serving for model training and online/low-latency serving for real-time inference.
• Point-in-Time Correctness: Retrieves the correct feature values as they existed at a specific historical timestamp, preventing data leakage.
• Use Case: Critical for operationalizing ML, enabling teams to share, reuse, and monitor features.

A centralized registry that stores and manages metadata—such as schema, location, lineage, and access policies—for data assets within a data lake or lakehouse.

• Data Discovery: Enables users to search for datasets using technical and business metadata.
• Governance & Lineage: Tracks data provenance (where data came from) and impact analysis (what will break if this data changes).
• Abstraction Layer: Decouples data consumers from the physical storage layout (e.g., S3 paths).
• Examples: AWS Glue Data Catalog, Apache Hive Metastore, and embedded catalogs in lakehouse table formats.

An abstraction layer that provides a single, logical view of data distributed across multiple storage systems, databases, and formats.

• Simplified Access: Presents disparate data sources (e.g., S3, ADLS, HDFS, RDBMS) as a cohesive file system or database.
• Virtualization: Eliminates the need for complex ETL to centralize data; queries are federated to the source.
• Key Benefit: Enables analytics and AI workloads to access data wherever it resides without manual data movement.
• Implementation: Found in data virtualization platforms and lakehouse query engines like Dremio and Starburst.