skip to content

An AWS Glue crawler retyped a table column on its next run and broke Athena queries — which crawler settings govern that?

level: seniorimportance: should knowfreq 44%

answer

  1. the crawler re-derives, it does not remember
  2. one bad value retypes a column
  3. there is a setting for don't touch my table
  4. the catalog keeps a history of the definition
  5. partitions carry their own copy of the schema

basics

~20 s

The crawler's schema change policy. UpdateBehavior decides whether a re-crawl rewrites the existing table (UPDATE_IN_DATABASE) or only logs the difference (LOG); DeleteBehavior decides what happens to tables and partitions whose data vanished. Every write also creates a new table version.

solid answer

~50 s

A Glue crawler re-infers the schema on every run, so a column that has always sampled as `bigint` becomes `string` the moment a file contains a quoted or malformed value. What it does with that discovery is the **`SchemaChangePolicy`**: `UpdateBehavior` is either `UPDATE_IN_DATABASE`, which overwrites the catalog table, or `LOG`, which records the change and leaves the table alone. `DeleteBehavior` covers disappearing objects and is `DELETE_FROM_DATABASE`, `DEPRECATE_IN_DATABASE` or `LOG`. Because every catalog write creates a new **table version**, you can list versions and see exactly which run changed the type and roll back to the previous definition. For tables that downstream consumers depend on, the usual production answer is to set `UpdateBehavior` to `LOG` — or to stop crawling them entirely and own the DDL — so schema changes become a reviewed change rather than something a scheduled crawl does to you at 3 a.m.

code

json · 13 lines
json
{
  "SchemaChangePolicy": {
    "UpdateBehavior": "LOG",
    "DeleteBehavior": "DEPRECATE_IN_DATABASE"
  },
  "Configuration": {
    "Version": 1.0,
    "CrawlerOutput": {
      "Tables":     { "AddOrUpdateBehavior": "MergeNewColumns" },
      "Partitions": { "AddOrUpdateBehavior": "InheritFromTable" }
    }
  }
}

go deeper

for a junior

Know that a crawler re-infers the schema every run, so the catalog table can change under you, and that there is a setting controlling whether it applies the change.

for a middle

Name the schema change policy fields and what each value does, and explain why inference on CSV or JSON is far more fragile than reading a Parquet footer.

for a senior

Show the production stance: crawlers detect drift on tables you own, they do not apply it; use table versions to roll back an incident, and fix the upstream file that caused it.

for a principal

Own schema as a contract across the lake — which zones tolerate inference, what a breaking change costs consumers, and what validation stops bad files before they reach a crawled prefix.

## Why the type changed at all A crawler does not remember what it decided last time; it re-derives the schema from a sample of the current files. For self-describing formats such as Parquet or Avro the schema comes from the file itself, so drift means an upstream writer genuinely changed. For CSV and JSON the type is *inferred* from values, and inference is fragile: one row where `"amount": "12.00"` is quoted, one `NULL` written as the literal text `\N`, one file produced by a different writer, and a column that was `bigint` for a year is now `string`. The crawler then applies its **schema change policy** to that finding, and by default it writes the change into the catalog. Athena queries that cast, compare or aggregate the column start failing, or worse, keep working with different semantics. ## The two settings **`UpdateBehavior`** - `UPDATE_IN_DATABASE` (the default) — the crawler rewrites the table definition to match what it just inferred. - `LOG` — the crawler records that the schema differs and leaves the existing table untouched. In the console this is "Ignore the change and don't update the table in the data catalog". **`DeleteBehavior`**, for objects that are no longer there: - `DELETE_FROM_DATABASE` — remove the table or partition from the catalog. Destructive: a transient listing problem or an exclude-pattern change can delete metadata for data that still exists. - `DEPRECATE_IN_DATABASE` (the usual default) — keep the table and mark it deprecated, so a human decides. - `LOG` — change nothing, just record it. There is a third, related option in the crawler configuration: for tables, `AddOrUpdateBehavior: MergeNewColumns`, which adds newly seen columns without disturbing existing ones — the middle ground between "overwrite freely" and "never touch". ## Table versions are your undo Every write to a Data Catalog table creates a new **table version**. `aws glue get-table-versions` lists them, and you can inspect the exact definition each run produced and restore the previous one. This is the fastest incident response when a crawl breaks a mart: find the version from before the run, re-create the table from it, then go and fix the upstream file that caused the inference to change. Versions accumulate, so long-lived heavily crawled tables eventually want pruning with `DeleteTableVersion`. ## The partition schema trap A subtlety that produces one of the most confusing Athena errors: **each partition carries its own storage descriptor** — its own columns and SerDe — separate from the table's. If a crawl updates the table's schema but leaves existing partitions on the old one, reading an old partition raises `HIVE_PARTITION_SCHEMA_MISMATCH`, because the partition claims a column type the table does not agree with. The fix is either the crawler option "Update all new and existing partitions with metadata from the table" (partition `AddOrUpdateBehavior: InheritFromTable`), or dropping and re-adding the offending partitions. ## What to actually do Split tables into two classes. **Tables you own** — written by your own jobs, consumed by your own marts and dashboards. Do not let a crawler define them. Declare the schema explicitly (`CREATE EXTERNAL TABLE`, the `CreateTable` API, or infrastructure-as-code) and have the writing job register partitions. If you keep a crawler for convenience, set `UpdateBehavior` to `LOG` so the crawl becomes a *detector* of drift rather than an *applier* of it, and alert on the log entry. **Landing zones you do not own** — third-party drops, exploratory data. Here inference is the point and `UPDATE_IN_DATABASE` is reasonable, because there is no contract to break and you would rather see the new shape than a stale one. Back that up with prevention: use a self-describing format (Parquet) as early in the pipeline as possible so inference stops guessing; validate incoming files against an expected schema before they land in the crawled prefix; and treat a schema-change log entry as a paging-worthy signal for published tables. The interviewer is listening for the idea that **schema is a contract**, and that a scheduled process silently rewriting that contract is a design mistake rather than a feature to be tuned.

  • An Athena query fails with HIVE_PARTITION_SCHEMA_MISMATCH. What happened?
    A partition's own storage descriptor disagrees with the table's — typically the table schema was updated by a crawl while existing partition records kept the old column types. Either set the crawler to update existing partitions from the table, or drop and re-add the affected partitions so their descriptors are rewritten.
  • Why does this bite JSON and CSV tables far more than Parquet ones?
    Parquet and Avro carry an explicit schema in the file, so the crawler reads it rather than guessing. JSON and CSV have no types at all, so the crawler infers them from sampled values — and a single quoted number, an empty string, or a file from a different writer is enough to widen a column to string on the next run.
  • Is DELETE_FROM_DATABASE ever the right delete behaviour?
    Only for genuinely ephemeral zones where stale metadata is worse than lost metadata — a scratch area, or a prefix with strict lifecycle expiry. For anything published, deprecation is safer: an exclude-pattern edit, a permissions hiccup or a mis-typed target can make data look absent, and deleting the table metadata turns a temporary blip into a broken downstream consumer.

Letting a scheduled crawler own a published table's schema is like letting a proofreader silently rewrite a signed contract every night because this week's draft read differently.

saying these in an interview costs you the question

  • Assuming the crawler remembers last run's schema
  • Leaving update-in-database on for published, contract-bound tables
  • Thinking a broken schema change cannot be rolled back
  • Forgetting that partitions store their own schema
  • Treating schema drift as a crawler bug rather than an upstream data problem

context

resolving links…