Cloud concept · Updated

Schema on Read - Schema on read is an approach that applies a schema when data is read or queried rather than requiring the storage layer to enforce that schema when data is written.

In short

Data can be stored in its original form and interpreted later using table metadata, parsing rules, and query expressions. This separates ingestion from analytical modeling, enabling flexible exploration while shifting responsibility for type compatibility, validation, and query efficiency to readers and processing pipelines.

Cloud Concepts Hub →

5 key aspects of Schema on Read

Schema on read enables flexible, metadata-driven consumption while requiring deliberate data validation, schema compatibility management, and storage optimization.

01
Read-time interpretation

Readers apply field mappings, data types, and parsing rules when accessing data. Ingestion can retain original files without requiring the storage layer to enforce every downstream analytical schema.

02
Metadata separate from data

A catalog can describe external datasets through schemas, formats, locations, and partition metadata. Updating this metadata does not automatically rewrite or repair the underlying files.

03
Flexible consumption

Different consumers can apply different projections and transformations to the same source records. Compatibility still depends on the file format, reader implementation, and actual stored values.

04
Deferred validation and schema management

Malformed records and incompatible types may surface only when a query or job reads them. Review inferred schemas, test changes against representative data, and isolate incompatible dataset versions.

05
Independent physical optimization

Schema on read works with both raw text and structured columnar formats. Converting CSV to compressed Parquet, selecting useful partitions, and compacting small files can improve recurring analytical workloads.

Cloud services for Schema on Read

Amazon S3 holds raw and curated files, while the AWS Glue Data Catalog records how compatible engines should interpret them. Amazon Athena uses those definitions to query source data without a separate database load. AWS Glue jobs or Apache Spark on Amazon EMR can apply validation, normalize types, and convert CSV or JSON into compressed Parquet. Registering curated outputs in the catalog makes them available to downstream consumers. Reliable automation coordinates processing completion, metadata updates, and quality checks; effective access also requires the appropriate storage and encryption-key permissions.

Amazon AthenaAmazon Athena

Athena applies external table definitions to query data in Amazon S3 without first loading it into a database. It commonly uses the AWS Glue Data Catalog and can create optimized Parquet datasets through CREATE TABLE AS SELECT.

AWS GlueAWS Glue

The AWS Glue Data Catalog stores reusable table and partition metadata, while crawlers can discover source structures. Glue ETL jobs can read data using explicit or inferred schemas, validate and transform records, and write curated columnar datasets.

Amazon S3Amazon S3

Amazon S3 stores the source objects that query engines interpret using external schemas. It does not itself enforce analytical column types, so dataset layout, access controls, and lifecycle management remain separate design concerns.

Amazon EMRAmazon EMR

Processing engines such as Apache Spark running on Amazon EMR can apply schemas when reading files from object storage. They can also transform raw datasets into validated, partitioned Parquet outputs for subsequent queries.

Schema on Read exam questions

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

DEA-C01

A retailer stores recent sales in native tables in an Amazon Redshift provisioned cluster. Historical sales are stored as Parquet files in Amazon S3, with compatible table definitions already registered in the AWS Glue Data Catalog. A nightly reporting application submits SQL to the Redshift cluster and must join recent and historical sales in a single query. Historical files must remain in S3 without being loaded into native Redshift tables, and the application must continue using its existing query endpoint. Which solution meets these requirements?

Show answerCORRECT ANSWER

Create an external schema in Redshift that references the AWS Glue Data Catalog database. Associate an IAM role with the cluster that permits access to the catalog and S3 files, and join the external historical table with the native sales table.

Redshift Spectrum allows Redshift to query cataloged files directly in S3 through external tables. An external schema provides access to the catalog definitions, and an authorized cluster IAM role provides access to the metadata and files. The reporting application can join external and native tables through its existing Redshift endpoint without loading the historical files.

SAA-C03

A travel company stores a year of newline-delimited JSON booking events in Amazon S3. Analysts query the files with Amazon Athena through an external table in the AWS Glue Data Catalog. Every stored event includes a nested referral object, but the table definition includes only the booking fields used by existing reports.

A marketing team now needs to analyze referral.campaign_id across the entire year. The original files must remain unchanged, and the company must not create another physical copy of the event dataset. Which solution meets these requirements with the LEAST operational effort?

Show answerCORRECT ANSWER

Create another external table in the AWS Glue Data Catalog over the same S3 location. Include the existing booking fields and the referral object with its campaign_id field, using a compatible JSON SerDe, and query the new table with Athena.

The new table stores metadata, not another copy of the events. Athena applies the table's schema when reading the existing JSON files, making the historical referral values available without rewriting or reingesting the data. A separate table also preserves the definition used by existing reports.

Practice Schema on Read in a full exam

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

Start a free practice exam

What is Schema on Read

3 min read

Schema on read separates storing data from interpreting it. A storage system can accept files without enforcing the analytical table definition that consumers will later use. When a query or processing job reads those files, it applies rules for identifying fields, assigning types, and interpreting records. By contrast, schema on write typically validates or transforms incoming data against a target schema before accepting it into a table.

In a cloud data lake, teams commonly store CSV, JSON, or columnar files in object storage and define external tables over those locations. Table metadata describes columns, data types, file formats, parsing settings, and partitions. A data catalog makes those definitions discoverable and reusable across compatible query engines. The catalog usually stores metadata rather than a second copy of the underlying dataset; consumers access the source files through the engine, subject to permissions.

For example, an application might deliver daily JSON event files to Amazon S3. An engineer can define an external table in the AWS Glue Data Catalog and query it with Amazon Athena without first loading the events into a database. A crawler can help discover fields and register partitions, but inferred metadata must still be reviewed. Sampling, inconsistent records, or mixed file layouts can produce incomplete or unsuitable definitions. Creating a table definition does not itself validate every stored record.

Schema on read does not mean that data has no structure or that a schema is always inferred automatically. A CSV reader needs delimiter, quoting, and column-mapping rules. JSON readers need expectations about field names and nested structures. Parquet files already contain schema metadata, yet readers still need compatible table definitions and mappings. Depending on the engine and format, incompatible values can cause query failures, appear as nulls, or require explicit parsing. Missing and malformed fields therefore need deliberate handling rather than assumptions about automatic compatibility.

The main benefit is flexibility: multiple consumers can interpret the same source data differently without rewriting it for every analytical question. The trade-off is that parsing and validation occur later, potentially increasing query latency and allowing quality problems to remain undiscovered until consumption. Schema changes also require coordination. A new optional field may be manageable, while changing a field from a number to a nested object can break existing readers. Versioned source locations, compatibility checks, and controlled catalog updates reduce that risk.

Schema on read does not require leaving data in an inefficient raw format. For repeated analytics, a processing job can read CSV using an explicit schema, normalize timestamps, validate values, and write compressed Parquet. Athena can also create a Parquet dataset from a query using CREATE TABLE AS SELECT. Columnar storage allows compatible engines to read selected columns and skip data using available metadata. Partitioning by a commonly filtered attribute, such as event date, can further reduce scanned data when queries include suitable predicates. Avoid excessive small files and overly granular partitions.

A practical architecture preserves raw files for replay and creates curated datasets for dependable consumption. Automated workflows can register new partitions, execute queries or transformation jobs, check completion, and validate results before downstream use. Catalog permissions alone do not necessarily grant access to source objects or encryption keys, so configure the applicable storage, catalog, and key permissions together. Choose schema on read for flexible consumption of source data, but combine it with explicit quality checks and optimized formats when reliability and predictable performance matter.

Learn Schema on Read in lessons

2 short lessons on Schema on Read in 2 certification courses.

Open the lessons