Cloud concept · Updated

Columnar Storage - Columnar storage organizes data by column rather than by complete row, allowing analytical workloads to read and compress selected fields efficiently.

In short

Columnar storage places values from the same field together, reducing unnecessary data reads when queries inspect a small subset of columns across many records. It underpins analytical databases and file formats such as Apache Parquet and Apache ORC, often improving query performance and lowering storage and scan costs compared with row-oriented representations.

Cloud Concepts Hub →

6 key aspects of Columnar Storage

Columnar storage combines selective reads, compression, and metadata-based skipping to optimize analytical workloads, provided that data layout and maintenance match access patterns.

01
Selective column reads

Compatible query engines read the columns needed for projections, filters, joins, and aggregations rather than every field. This is especially valuable for wide datasets where each query uses only a few attributes.

02
Efficient encoding and compression

Grouping values from the same field enables type-aware encodings and compression. Actual savings depend on value distribution, encoding choices, and compression settings.

03
Typed analytical file formats

Parquet and ORC store schemas and typed values alongside columnar data. When converting CSV or JSON, explicitly validate types, precision, timestamps, and null handling to avoid silently changing the meaning of the data.

04
Metadata-driven data skipping

Readers can use statistics to skip storage units that cannot satisfy supported filters. Sorting or clustering related values can improve skipping, while partitioning independently helps exclude entire groups of files.

05
Scan-oriented workload fit

Columnar storage is well suited to large aggregations, reporting, and feature extraction. Frequent small updates and full-row point lookups often favor other storage organizations.

06
Physical layout and lifecycle management

File size, partition granularity, compaction, and schema compatibility influence performance and operational cost. Measure representative workloads rather than assuming that changing formats alone will produce the desired savings.

Cloud services for Columnar Storage

A common architecture lands raw CSV or JSON in Amazon S3, then uses AWS Glue or Apache Spark on Amazon EMR to validate records and produce compressed Parquet or ORC. The AWS Glue Data Catalog exposes schemas and partitions to query engines. Amazon Athena queries the curated files directly, while Amazon Redshift can load data into its own columnar warehouse tables or query supported external datasets through Redshift Spectrum. Keep transformation and compaction costs in the cost model, and verify that downstream analytics and machine learning readers support the chosen format and schema.

Amazon RedshiftAmazon Redshift

Amazon Redshift uses columnar storage and compression for warehouse tables, supporting large analytical scans and aggregations. Distribution and sort choices also affect performance; columnar storage does not eliminate the need to design for query patterns.

Amazon AthenaAmazon Athena

Amazon Athena queries Parquet and ORC datasets in Amazon S3 and can benefit from column selection, partition pruning, and supported predicate pushdown. Its CREATE TABLE AS SELECT operations can convert query results into columnar datasets, reducing repeated scans of raw text data.

AWS GlueAWS Glue

AWS Glue ETL jobs can parse CSV or JSON, enforce transformations, and write Parquet or ORC datasets. The AWS Glue Data Catalog records table schemas and partition metadata for compatible query engines; cataloging data does not itself convert its format.

Amazon S3Amazon S3

Amazon S3 stores columnar files as objects for a data lake. S3 does not impose a columnar layout or execute analytical queries; writers create the format, and readers interpret its metadata and retrieve the required data.

Amazon EMRAmazon EMR

Amazon EMR runs processing frameworks such as Apache Spark that can convert raw data into Parquet or ORC and compact small files. These frameworks can also read selected columns for large-scale transformations and machine learning feature preparation.

Columnar Storage exam questions

Real practice exam questions on Columnar Storage, with the correct answer and its explanation. Answer them with every option in the exam simulator.

MLA-C02 (beta)

A machine learning engineer maintains a historical feature dataset in Amazon S3 as Apache Parquet files. A preprocessing application downloads each entire object to local storage, loads it into a DataFrame, and then selects the features required for an experiment. Each experiment uses all records but only a small subset of the available features. Network transfer from S3 is the main bottleneck.

The dataset must retain all features for future experiments. Which change will reduce the amount of data retrieved from S3 by taking advantage of the existing file layout?

Show answerCORRECT ANSWER

Read the files directly from S3 with a Parquet reader that supports column projection and S3 range requests, specifying only the required feature columns.

Parquet stores column values in separate chunks. A compatible S3-aware reader can retrieve file metadata and the required column chunks through range requests without downloading unrelated feature values. All features remain available in the stored dataset.

DEA-C01

A company uses an AWS Glue Spark job to transform daily CSV exports into uncompressed Apache Parquet files in Amazon S3. Analysts query the Parquet files directly with Amazon Athena. The company wants to reduce the storage footprint of the curated Parquet dataset while preserving Athena's ability to read individual column chunks without first decompressing an entire S3 object. The solution must not require a separate decompression step before queries run.

Which change should a data engineer make?

Show answerCORRECT ANSWER

Configure the Glue job's Parquet writer to use Snappy compression, and rewrite the curated dataset.

Parquet supports compression internally at the column-page level. Writing Parquet with Snappy reduces stored bytes while preserving the format's metadata and column-chunk access, so Athena can query the files directly without decompressing each entire object.

SAA-C03

A payment processor uses an Amazon Data Firehose delivery stream to convert JSON payment events to Apache Parquet before delivering them to Amazon S3. Format conversion references an AWS Glue Data Catalog table and is configured to use its latest schema version. Analysts query the Parquet files through a separate catalog table.

The producer recently added a numeric processing_fee field to every event. The field is present in the JSON records accepted by the delivery stream, but it is absent from newly delivered Parquet files. Delivery succeeds without conversion errors. The catalog table used for conversion still contains only the original fields.

A solutions architect must ensure that future Parquet files include processing_fee after configuration changes take effect. The solution must preserve native format conversion and require the LEAST operational effort. What should the architect do?

Show answerCORRECT ANSWER

Add processing_fee with a compatible numeric data type to the Glue Data Catalog table referenced by Firehose format conversion.

Firehose uses the referenced Glue table schema to determine which fields to include in Parquet output. JSON fields absent from that schema are omitted. Adding the field to the conversion table allows Firehose, which references the latest schema version, to include it in future output after the schema update takes effect.

Practice Columnar Storage in a full exam

Questions on Columnar Storage in each practice exam, with every answer option and an explanation for each:

Start a free practice exam

What is Columnar Storage

3 min read

Columnar storage is a physical data organization technique that groups values by column instead of storing all fields of each record together. In a row-oriented layout, an order's identifier, customer, timestamp, product, and amount are stored together. In a columnar layout, values for each field are stored in separate column chunks or blocks. Formats such as Apache Parquet still divide records into row groups, but organize the data within each group by column.

This organization benefits analytical queries that process many records but need relatively few fields. A query calculating total sales by region may require only the region and amount columns, plus any columns used in filters. A compatible engine can avoid reading unrelated fields such as customer addresses and product descriptions. Similar values stored together also support efficient dictionary encoding, run-length encoding, and compression. The resulting reduction in storage reads can improve execution time and reduce charges in query systems billed by bytes scanned.

In cloud data lakes, engineers commonly retain incoming CSV or JSON files in a raw area and create a curated dataset in Parquet or ORC. Conversion requires parsing the source, assigning data types, handling nulls and malformed records, and writing a new dataset; changing a filename extension does not change its format. For example, a transformation job might read daily sales CSV files, validate decimal amounts and timestamps, and write compressed Parquet files partitioned by transaction date. Validate record counts, aggregates, and schema before making the output available to consumers.

Column selection, partition pruning, and predicate pushdown are complementary mechanisms. Column selection avoids unneeded fields. Partition pruning excludes entire partition locations, such as dates outside the requested range. Predicate pushdown allows supported readers to use file metadata, including minimum and maximum values, to skip irrelevant row groups or other storage units. These optimizations depend on the engine, predicates, file metadata, and data layout; using Parquet alone does not guarantee that every filter avoids reading data.

For machine learning preparation, columnar datasets are useful when feature engineering reads selected attributes from large historical datasets. CSV remains useful for simple interchange and consumers that require tabular text, while JSON suits nested event payloads and flexible ingestion interfaces. Parquet and ORC provide typed, compressed representations for repeated analytical access. Confirm that the training algorithm or data loader supports the chosen format; an efficient lake format is not automatically a supported training input.

The main trade-off is that columnar layouts favor scans and aggregations rather than frequent single-record updates or retrieval of complete individual rows. Updating individual records in plain columnar files generally requires rewriting affected files or using a table layer that manages changes. Numerous tiny files also create request, metadata, and scheduling overhead, so compact files and choose partition granularity according to actual access patterns. Include conversion compute, compaction, storage, and query costs when evaluating savings.

Choose database types by workload rather than by labels alone. A columnar warehouse is often suitable for repeated reporting across large datasets, while a row-oriented relational database is usually a stronger starting point for transactional operations. A time-series database adds capabilities for timestamped measurements, retention, and time-based queries; time-series specialization and columnar organization are not mutually exclusive. Finally, a wide-column database is not synonymous with an analytical columnar store: its column-family data model does not imply the same scan-oriented physical layout or performance characteristics.

Learn Columnar Storage in lessons

6 short lessons on Columnar Storage in 3 certification courses.

Open the lessons