Glue Catalog and Schema Drift: A Table Has Four Schemas

Business Challenge

Every account of schema drift starts in the same place: an upstream team added a field, or renamed one, or changed an integer to a string, and now the dashboard is wrong. The story is told as a data-quality problem, and the remedy proposed is always some version of "talk to the upstream team".

That is worth doing and it does not fix this. The upstream change is an event lasting one deploy. What turns it into six weeks of wrong numbers is what happens next, in the AWS Glue Data Catalog, where a set of defaults decides on your behalf what the change meant. Those defaults are documented, they are reasonable in isolation, and three of them do the opposite of what an operator assumes.

The thing that makes this hard to reason about is that the catalog does not hold a schema for your table. It holds several, they are edited by different processes at different times, and the query engine reconciles them at read time without telling anybody it did.

1A table does not have "a" schema

There is a table-level schema, which is what the console shows and what everyone means by the word. There is separately one schema per partition, and a table may have up to 10,000,000 of them. There is a table version history. And if the data arrives on a stream, there may be a Glue Schema Registry entry sitting upstream of the producer, enforcing something entirely different.

Four artefacts, four owners in practice, and no operation that reconciles them. Athena is a schema-on-read engine: it applies a schema when reading, and it does not change or rewrite the underlying data. Whatever disagreement exists is resolved per query.

2The crawler's answer to an incompatible schema is a second table

This is the one that surprises people, and it is spelled out in AWS's own documentation. A crawler pointed at one include path finds two JSON files whose schemas are A:int, B:int and C:int, D:int. By default it decides they are not sufficiently similar — and it creates two tables, named year_2017 and year_2018 after the partition values.

No error. No warning. The crawler run is green. The query that has been running against the original table for a year keeps working, keeps returning rows, and quietly stops including anything from the newer prefix.

3Parquet and ORC have opposite defaults

Two columnar formats, treated by most teams as an interchangeable choice made once and never revisited. Athena reads Parquet by name and ORC by index, and those defaults are exactly inverted with respect to each other.

So renaming a column is a safe operation in ORC and a breaking one in Parquet. Adding a column in the middle of the table is safe in Parquet and breaking in ORC. The same migration script, run against two tables in the same database, produces two different outcomes for reasons nothing in the script mentions.

4The failure mode is a wrong answer, not an outage

Nothing here pages anybody. A dropped column reads as NULL. A split table reads as a decline in volume. A type coercion reads as a distribution shifting. Each of those is indistinguishable, from the consuming end, from a real change in the business.

Which means the detection budget belongs on the catalog, not on the job. The job succeeded. The job was always going to succeed.

Architecture

The design question is not "how do I stop schemas changing" — they change, that is what upstream systems do. It is "which of the four artefacts is authoritative, and what is allowed to edit it".

Diagram: the four places a schema lives around one Glue Data Catalog table — the Schema Registry upstream of the producer, the table-level schema, the per-partition schema and the table version history — with the drift behaviour and quota of each, followed by the opposite column-access defaults of Parquet and ORC, the position-only identity of CSV, and the crawler's default of creating two tables when schemas differ

The crawler is a policy engine, and its defaults are opinions

A crawler is usually installed as though it were a discovery tool that reports what is in the bucket. It is closer to a scheduled process with write access to your table definitions, and its behaviour is set by two independent knobs that are easy to confuse.

The SchemaChangePolicy governs whether it may edit at all. Setting UpdateBehavior to LOG stops it changing an existing schema entirely. The Configuration document governs what it does when it is allowed to edit, and it has separate settings for tables and for partitions:

# Discover new columns, but never overwrite a schema that was corrected by hand,
# and force every partition to carry the table's schema rather than its own.
aws glue update-crawler \
  --name sales-events \
  --schema-change-policy UpdateBehavior=LOG,DeleteBehavior=LOG \
  --configuration '{
    "Version": 1.0,
    "Grouping":      { "TableGroupingPolicy": "CombineCompatibleSchemas" },
    "CrawlerOutput": {
      "Tables":     { "AddOrUpdateBehavior": "MergeNewColumns" },
      "Partitions": { "AddOrUpdateBehavior": "InheritFromTable" }
    }
  }'

Three of those four settings are corrections to a default. MergeNewColumns adds new columns without overwriting the existing schema, which matters the moment anybody has fixed a type by hand — without it, the next crawl reverts the fix. InheritFromTable is the setting AWS describes as "update all new and existing partitions with metadata from the table"; its absence is why partition schemas drift away from the table and stay there. And CombineCompatibleSchemas is what asks for one table rather than a table per partition value: the crawler still checks data compatibility — format, compression, path structure — but stops splitting on schema similarity.

Partitions carry their own schema, and the forgiving formats are the dangerous ones

AWS states the rule plainly: a table and its partitions must use the same data format, but their schemas may differ. A new partition usually inherits the table's schema at creation, and after that the two are independent. If the table's schema changes, partition schemas are not updated to stay in sync.

What happens next depends entirely on the format, and the relationship is the reverse of what you would want:

CSV, JSON and Avro are verified by index. A mismatch raises HIVE_PARTITION_SCHEMA_MISMATCH and the query fails, naming the partition and the column. Loud, immediate, and the workaround is to drop and recreate the partition.
Parquet, and ORC read by name, are verified by column name. AWS notes that this "eliminates HIVE_PARTITION_SCHEMA_MISMATCH errors for tables with partitions". It does — by matching what it can and leaving the rest alone.

Read that second bullet as an operator rather than as a developer. The format that never raises the error is not the format with no drift; it is the format that resolves drift silently, one partition at a time, and returns a result set that looks complete. The noisy formats fail a query. The quiet ones change an answer.

One more asymmetry worth knowing before it is discovered under pressure: for Parquet and ORC, changing a column's data type works only for partitioned tables. On an unpartitioned table it is not a supported operation at all.

The format is the drift policy, chosen years before the drift

Storage format is usually decided on compression ratio and scan cost. It is also, and more consequentially, a decision about which schema changes your platform will survive. The SerDe properties parquet.column.index.access and orc.column.index.access toggle the access method, so this is a choice rather than a fact — but it is a choice made in CREATE TABLE, and AWS warns that these properties are not automatically propagated to each partition.

Change	CSV / TSV	JSON	Avro	Parquet (by name, default)	ORC (by index, default)
Rename a column	Yes	No	No	No	Yes
Add a column at the end	Yes	Yes	Yes	Yes	Yes
Add a column at the front or middle	No	Yes	Yes	Yes	No
Remove a column	No	Yes	Yes	Yes	No
Reorder columns	No	Yes	Yes	Yes	No
Change a column's type	Yes	Yes	Yes	Partitioned tables only	Partitioned tables only

The Parquet and ORC columns are near mirror images, which is the practical point: there is no "columnar" behaviour to reason about, only two specific behaviours. And CSV's row is a useful reminder of why it keeps causing trouble in a lake — with no column names in the format, position is the only identity a column has, so removing one is not a supported operation and never will be.

Where the Schema Registry stops the problem instead of recording it

Everything above is reconciliation after the fact. The Glue Schema Registry is the only component in this picture that refuses a change before it is written, and it is worth being precise about where it applies: it enforces schemas on streaming producers, integrating with Apache Kafka, Amazon MSK, Kinesis Data Streams, Managed Service for Apache Flink and Lambda. It has nothing to say about a partner dropping a CSV in a bucket.

Within that scope it is genuinely a contract. Eight compatibility modes govern what a new version may do relative to a checkpoint version: NONE, DISABLED, BACKWARD, BACKWARD_ALL, FORWARD, FORWARD_ALL, FULL and FULL_ALL. AWS recommends BACKWARD, which lets consumers read both the current and the previous version, and is the right default for the common case where consumers are upgraded after producers. DISABLED is the one to know about for a schema that must never move: it prevents versioning entirely, so no new version can be added at all.

The quota to design around is 10,000 schema versions per Region per account, and unlike almost everything else in Glue it is not adjustable. Every new schema consumes a version, so that is also the ceiling on distinct schemas. Compare that with the catalog side, where Max table versions per table is 100,000 and adjustable — an hourly crawler rewriting the schema on every single run would need about 11.4 years to exhaust it. Table versions are not going to be your constraint. Schema versions can be, and there is no support ticket for it.

What the job does when a column already holds two types

When drift has already happened and both shapes exist in the data, a Glue DynamicFrame represents the column as a ChoiceType and makes you say what it means. The resolutions available to ResolveChoice are not equivalent, and one of them loses data by design:

cast — coerce to a named type, for example cast:long.
make_cols — flatten into columnA_int and columnA_string. Ugly, and it keeps every row.
make_struct — a struct holding both, preserving the distinction without widening the table.
project — keep only values of one type. Rows of the other type are dropped.
MATCH_CATALOG — cast each choice to the corresponding type in a named catalog table, which requires database and table_name.

project is the one that reads as tidy in a code review and shows up later as a row count nobody can explain. On a lake you do not control, make_cols is the honest default: it makes the drift visible in the schema itself, which is where somebody will eventually look.

Why This Architecture Holds Up

Platform engineering has good instincts for availability failures and poor ones for correctness failures, because the feedback loops are so different. An availability failure announces itself within minutes to the person on call. A correctness failure announces itself in a meeting, weeks later, as a question about why a number moved — and by then the partition that caused it is one of several hundred thousand.

Schema drift is the second kind, and the catalog is where it becomes irreversible. Not because the data is lost — the objects in S3 are untouched, and schema-on-read means nothing was rewritten — but because the interpretation was wrong for a period nobody can now delimit. Restating a quarter is not a technical problem.

The scale figures are worth holding in mind, because they explain why manual reconciliation stops being available so quickly. A table may hold up to 10,000,000 partitions, each with its own schema, and the account ceiling is 20,000,000 — so exactly two tables at the per-table maximum exhaust it. Long before those numbers, the population of partition schemas is past the point where anyone will audit it by hand. Whatever policy is in place when the partitions are created is the policy you have.

Which is the argument for spending the effort at configuration time rather than on detection. Detection is still worth having, and the highest-value alarm is an unusual one: alert on the number of tables in a database. A crawler quietly splitting a table into year_2017 and year_2018 is invisible in every job metric and obvious in a table count.

Key Architecture Decisions

Decision	Take this	Because
Who owns the table schema	Not the crawler. `UpdateBehavior=LOG` with `MergeNewColumns`	Keeps discovery of new columns while stopping the next crawl from reverting a type somebody corrected by hand
Partition metadata	Set `Partitions AddOrUpdateBehavior=InheritFromTable` on every crawler	Partition schemas do not follow the table by default, and there is no later operation that reconciles them
Table grouping	`TableGroupingPolicy=CombineCompatibleSchemas` on any partitioned prefix	The default is to split one table into several named after partition values, silently, and report success
Storage format	Parquet, left at its read-by-name default	Survives adds, removes and reorders — the changes that actually occur — and name-based verification avoids partition mismatch errors
Renaming a column in Parquet	Do not. Add the new name, backfill, deprecate the old one	Rename is the one operation read-by-name cannot absorb, and inverting the SerDe property to allow it breaks everything else
Where to enforce the contract	Schema Registry when the producer is a stream you control; a validation step and a quarantine prefix when it is not	The registry rejects an incompatible version before it is written, but only integrates with streaming producers
Schema Registry sizing	Budget against 10,000 schema versions per Region, per account	It is a hard limit, not adjustable, and every new schema consumes one
Resolving a column with two types	`make_cols`, not `project`	`project` silently drops every row of the non-selected type; `make_cols` puts the drift in the schema where it will be seen
Detection	Alarm on table count per database, and on partition count per table	The two drift outcomes that raise no error both show up there and nowhere else

Closing Thought

The Glue Data Catalog is a Hive metastore with an AWS interface, and it inherits the assumption Hive was built on: that a schema is metadata describing files, applied when somebody reads them. That assumption is what makes the catalog cheap, fast to populate and able to sit over a bucket somebody else writes to. It is also what makes every mechanism in this post a reconciliation rather than a guarantee.

Configured deliberately, that is a reasonable trade and it holds up well. Left on its defaults — a crawler with write access, partitions that do not inherit, and a format chosen for compression ratio — it is four sources of truth with no arbiter, and the arbitration happens per query, invisibly, in favour of whatever the reader can match.

Next in this series

#31 — Iceberg on S3: why table formats arrived. Everything above is the case for the defence of schema-on-read. Table formats exist because a decade of that argument was lost: they move schema, partitioning and history into the data itself, where a rename is a recorded operation rather than a reinterpretation. Having seen what drift costs in a catalog, the next post is about what it costs to stop it.

AWS Architecture Series #30 — Glue Catalog and Schema Drift: A Table Has Four Schemas