Data Modeling, Glue Catalog and Schema Evolution

Two DEA-C01 Domain 2 tasks meet in this lesson. Task 2.2, understand data cataloging systems, asks you to build and reference a technical data catalog (AWS Glue Data Catalog, Apache Hive metastore), discover schemas with crawlers, synchronize partitions and create source/target connections. Task 2.4, design data models and schema evolution, asks you to design schemas for Amazon Redshift, DynamoDB and Lake Formation, address changes to the characteristics of data, and describe best practices for indexing, partitioning strategies and compression. The exam treats them as one story: a model is only as good as the catalog that describes it, and a catalog is only safe if you can change what it describes without breaking every consumer.

In this lesson you will:

• read the Glue Data Catalog object model, catalog, database, table, StorageDescriptor, PartitionKeys, classification, and the quotas that shape it;
• run a crawler in your head: custom classifiers in order, first success wins, built-ins only if none matched, folder heuristics and SchemaChangePolicy;
• separate partition indexing, pruning and partition projection, including the two settings Athena needs to use an index;
• operate table versioning: what UpdateTable archives, how you compare two versions, and how a blue/green table swap is assembled from verified building blocks;
• wire resource links for cross-account and cross-Region sharing, with the two-grant rule;
• choose star versus snowflake, set the grain, identify facts, dimensions and surrogate keys, and denormalize for Redshift distribution;
• implement SCD Type 1 versus Type 2 (and why Type 2 is the exam staple) with a worked merge;
• classify a proposed schema change as additive or breaking across Hive-style, Parquet/JSON/Avro and Iceberg tables, including partition evolution;
• design a DynamoDB single-table model from access patterns, keys and GSIs;
• stack partitioning, bucketing, sorting, compression and file size into one scan-elimination ladder;
• study three AWS customer case studies (BMW Group, GoDaddy and Integral Ad Science) and a sourced October 2026 update box;
• practise with 14 exam-style questions plus four interactive checks.

---

1. The Glue Data Catalog object model

1.1 The tree, and the one sentence that matters

The Data Catalog is a hierarchy of metadata: account/Region → catalog → database → table → partitions, plus functions. AWS's crawler documentation states the fact every catalog question is built on: "The tables in the Data Catalog do not contain data." A table row points at data (S3 Location, InputFormat, OutputFormat, SerdeInfo); it never holds it. That is why deleting a catalog table does not delete the objects, and why two catalogs can point at the same prefixes.

One metadata layer feeds Athena, Redshift Spectrum, EMR, Glue ETL and SageMaker AI; which is the whole point: crawl once, query from several engines. Catalogs can also be federated, so a Redshift-federated catalog (or a catalog holding resource links to Redshift databases in another account or Region) appears in the same tree.

1.2 The quotas table (as of October 2026)

• 📚 Did you know? The Data Catalog itself has a free tier: the first 1,000,000 metadata objects and 2,000,000 metadata requests are free (AWS Glue pricing, accessed Oct 2026). Crawling and ETL are metered separately at $0.44 per DPU-hour, so a team can run a busy catalog for nothing and still get a surprise bill from its crawlers: the two lines on the bill answer different questions.

---

2. Crawlers and classifiers: how a schema gets discovered

2.1 The run order: the single most-tested crawler fact

AWS documents a fixed pipeline: the crawler runs any custom classifiers that you choose… in the order that you specify, and "The first custom classifier to successfully recognize the structure of your data is used to create a schema. Custom classifiers lower in the list are skipped." Only if no custom classifier matches do the built-in classifiers try. Then the crawler connects, infers the schema and writes metadata.

Two consequences exam writers love. First, a classifier is a format/schema recognizer (it "checks whether a given file is in a format the crawler can handle" and emits a StructType); the crawler is the orchestrator that discovers, groups, versions and writes. Second, priority is positional: a CSV classifier with delimiter=; listed first beats a JSON classifier listed second, and everything below the winner is skipped.

2.2 Targets, schedule and recrawl

2.3 SchemaChangePolicy, and how to freeze a curated schema

UpdateBehavior ∈ {LOG, UPDATE_IN_DATABASE}; DeleteBehavior ∈ {LOG, DELETE_FROM_DATABASE, DEPRECATE_IN_DATABASE (adds a DEPRECATED marker plus a timestamp)}. The critical trap: the policy "does not affect whether or how new tables and partitions are added."

2.4 Worked example E1: a crawler that must not wreck three hand-curated columns

Input: s3://acme-raw/orders/dt=2026-10-0*/, semicolon-delimited CSV with quoted fields, timestamps currently inferred as string, three catalog columns hand-edited by an analyst that must survive every crawl.

Cost check as of Oct 2026: crawlers bill $0.44 per DPU-hour, per second, with a 10-minute minimum per crawl (1 DPU = 4 vCPU + 16 GB). A nightly crawl that runs two DPUs for the ten-minute minimum costs 2 × (10/60) × 0.44 ≈ $0.15 per night ≈ $4.5 per month, and that is before any idle queueing; which is why event-driven incremental crawls are the default recommendation for tables that change once a day.

• 📚 Did you know? The folder heuristic is a majority vote, not a rule: "When the majority of schemas at a folder level are similar, the crawler creates partitions of a table instead of separate tables." So a folder of clean CSVs plus two malformed JSON files yields one table with partitions and the JSON noise is absorbed; while forcing genuinely different datasets apart means adding each root as a separate data store, not tweaking a flag (AWS Glue crawler documentation, accessed Oct 2026).

---

3. Synchronizing partitions: indexing, pruning and projection

3.1 Five mechanisms, one job

The partition schema exists at the table level and per partition, and Athena validates both: a mismatch fails fast with HIVE_PARTITION_SCHEMA_MISMATCH rather than silently misreading data.

3.2 Index versus pruning versus projection

• Pruning is the outcome: dropping non-matching partitions before any file is read. Athena prunes for every partitioned table, projected ones included.
• A partition index is a stored Data Catalog index over an ordered subset of partition keys, so GetPartitions fetches a subset instead of everything. Rules: max 3 per table; key types String/Numeric/Date; operators =, >, >=, <, <=, BETWEEN and only AND (LIKE, IN, OR, NOT are ignored and applied afterwards); the index's first key must appear in the expression or the index is not used at all.
• Projection is Athena computing partition values and locations in memory from table properties you configure on the Glue table: "Enabling partition projection on a table causes Athena to ignore any partition metadata registered to the table in the AWS Glue Data Catalog or Hive metastore."

3.3 Worked example E2: 400,000 partitions, one index, four queries

Index: [country, category, year] on a table with 400,000 partitions.

Two settings, then two different consumers:

• 📚 Did you know? Glue ETL gives you two different pushdown knobs, and they prune in different places: push_down_predicate is Spark SQL applied client-side after listing all partitions, while catalogPartitionPredicate is JSQL applied server-side through the partition index: the one to reach for when a table has millions of partitions (AWS Glue best practices, accessed Oct 2026).

---

4. Table versioning, compare and blue/green swaps

4.1 Versioning is always on

"By default, UpdateTable always creates an archived version of the table before updating it. However, if skipArchive is set to true, UpdateTable does not create the archived version." Every crawl that changes a schema therefore bumps VersionId by 1, against a ceiling of 100,000 versions per table and 1,000,000 per account (as of Oct 2026).

There is no true rollback: the current version cannot be deleted, and restoring means publishing a new version identical to the one you want back. (Apache Iceberg can roll back to a snapshot ID: a different feature, not a catalog version operation.)

4.2 Blue/green table swaps: a pattern, not a product

AWS ships no named "blue/green table deployment" feature for the Glue Data Catalog. What is verified are the building blocks, and the exam-safe way to answer is as an assembled pattern:

---

5. Resource links and cross-account sharing

A resource link is a local table (or database) whose TargetTable is {CatalogId, DatabaseName, Name, Region}, so it can point across an account and across a Region. Creating one needs Lake Formation CREATE_TABLE/CREATE_DATABASE and IAM glue:CreateTable/glue:CreateDatabase.

The rules that generate questions:

• "granting permissions on a resource link doesn't grant permissions on the target"; you need DESCRIBE on the link plus a separate grant on the target. Two steps, always.

• Links render in italics in the console; there is no existence check at creation, so a link can point at nothing.

• Chains are useless: APIs follow only the first resource link, so a link to a link does not resolve.

• Two sharing stacks exist: Lake Formation (named-resource or tag-based access control, requiring the source account's resource policy to allow glue:ShareResource for ram.amazonaws.com) or legacy IAM-only Glue permissions. Turning off "Use only IAM access control for new databases/tables" stops new objects being granted Super to IAMAllowedPrincipals.

• LF-Tags are key-value pairs such as department=sales or classification=restricted, and "Tables inherit LF-Tags from databases and columns inherit LF-Tags from tables"; which is how PII marking scales: grants count as n(principals) + n(resources) instead of a product.

• 📚 Did you know? Nobody at GoDaddy typed those resource links in. Their mesh's provisioning pipeline used AWS Service Catalog and AWS Lambda to create the consumer-side links automatically, so a new data product showed up in a consumer account already linked and still exactly one grant away from being queryable. Automating the metadata object, not the data copy, is what keeps "hundreds of accounts" from becoming hundreds of manual tickets, and the two-step DESCRIBE-plus-target rule still applied to every one of them (AWS Big Data Blog, accessed Oct 2026).

---

6. Dimensional modeling: grain, facts, dimensions, star versus snowflake

6.1 Grain first: the decision every other decision hangs from

Grain is what one row of a fact table means: "one row per order line", not "one row per order", not "one row per day per store". Decide it before you choose keys, because it determines which events can share a table and how big each row is. (AWS's Redshift modeling posts use grain throughout but never publish a formal definition: the exam wording is the industry/Kimball framing.)

Redshift builds surrogate keys with IDENTITY, and you should declare primary/foreign keys anyway: "Redshift will not enforce them, but declaring them will help the optimizer arrive at optimal query plans." Keep a date dimension: every fact table needs a date_sk.

6.2 Star versus snowflake

"Snowflake schemas extend the star concept by further normalizing the dimensions into multiple tables." AWS picks the star where it "closely align[s] with the business process", and columnar compression makes denormalized keys "relatively inexpensive to store", so the extra joins of a snowflake buy little and cost query time.

Read the two diagrams as a trade: the star joins the fact to four flat dimensions in four hops; the snowflake normalizes city → country and subcategory → category, so one hop becomes two and every extra join is work the engine must plan. On this exam, "the business process aligns with one fact and flat dimensions" ⇒ star; "the requirement is normalized, reusable conformed dimensions" ⇒ snowflake, with the join cost named.

6.3 Fact table shapes (awareness)

---

7. Slowly changing dimensions: Type 1 versus Type 2

7.1 The four types you are allowed to define

7.2 Worked example E3: a Type 2 merge, statement by statement

Stage holds the delta; the dimension holds history. Match on the business key with a left outer join, hash the tracked columns to decide update versus insert, and do it as bulk operations, never row-by-row cursors:

Mechanics worth memorising:

• Match detection: left outer join stage ↔ dimension on the business key; matched rows are update/expire candidates, unmatched rows are inserts.

• Change detection: sha2 across sources, FNV_HASH (64-bit) for large dimensions, CHECKSUM (32-bit) for small ones: a changed hash means the row must version.

• Delete detection: full extracts → compare previous and current versions; change-only extracts → use a status column.

• Distribution: give stage and target matched distribution keys so the merge is node-local in Redshift.

• Contract: load dimensions first, then facts: a failed dimension load must block the fact load.

• 📚 Did you know? The high-date convention 2999-12-31 is not decoration: it lets a query find "every customer's current address" with WHERE is_active = 1 or with WHERE CURRENT_DATE BETWEEN rec_eff_dt AND rec_exp_dt, and lets a historical fact join with f.order_date BETWEEN d.rec_eff_dt AND d.rec_exp_dt; which is exactly how a star schema answers "what did the order look like at the time". Type 1 has no equivalent because there is no interval to join on (AWS Redshift SCD guidance, accessed Oct 2026).

---

8. Denormalization and physical design in Redshift

The star schema's flat dimensions are a denormalization decision, and Redshift rewards it physically:

Denormalization also answers the snowflake question physically: extra normalized tables mean extra joins, and in a columnar warehouse those joins cost far more than the disk they save.

---

9. Schema evolution: additive versus breaking

9.1 The audit table

A change is additive when old readers and old files keep working; it is breaking when a consumer fails, silently coerces, or the partition layout no longer matches the query.

Two anchors: "Iceberg schema updates are metadata-only changes. No data files are changed when you perform a schema update", and Iceberg correctness rests on unique field IDs: "Added columns never read existing values from another column". Athena, by contrast, fails fast rather than misreading: a partition schema that diverges from the table schema raises HIVE_PARTITION_SCHEMA_MISMATCH.

9.2 Partition evolution is the trap inside the trap

"Hive table partitioning cannot change so moving from a daily partition layout to an hourly partition layout requires a new table. And because queries are dependent on partitions, queries must be rewritten for the new table." For Iceberg: "When you evolve a partition spec, the old data written with an earlier spec remains unchanged. New data is written using the new spec in a new layout… Partition evolution is a metadata operation and does not eagerly rewrite files." Both statements are examinable, and they point in opposite directions: Hive-style partition keys are the S3 object keys, so changing the scheme is a migration; Iceberg partition specs are metadata, so old and new layouts coexist: at the cost of extra split planning.

9.3 How Parquet, JSON and Avro each handle the change

9.4 Worked example E5: classify five requests before you ship them

The ordering rule, additive before code deploy, deletive after code deploy, is the single most reusable sentence in this section.

---

10. DynamoDB single-table design: access patterns first

"Single table design is a pattern that allows you to store multiple types (entities) of data in a single DynamoDB table." The AWS-documented workflow runs use case → entity-relationship → access patterns (written as SQL) → schema → PK/SK per table and GSI → repeat, and it starts from queries, not entities.

10.1 Worked example E6: four access patterns to a design

Choose single-table when the entities are queried together with similar operational needs; choose multiple tables when retention, backup, per-tenant KMS keys or table class needs diverge. AWS states both sides; this is a judgement question, not a "single table is always better" question.

---

11. Partitioning, bucketing, sorting, compression: the elimination ladder

Each layer removes work before the next layer runs, outermost first:

Codec choice (from the AWS Glue best-practices codec table): Snappy: fast, moderate ratio, default for Parquet; GZIP: high ratio, CPU-intensive and not splittable, so cold/archival text only; Bzip2: very high ratio, splittable, slow; ZLIB: default for ORC; Zstd: balanced. Compression vs parallelism is a real trade: Gzip on hot text loses splittability.

11.1 Worked example E7: the scan-cost ladder

100-column, 4 TB uncompressed CSV, a query selecting one column for one day of 365:

Athena prices the result: at $5.00 per TB scanned with a 10 MB minimum per query (Athena pricing, accessed Oct 2026), the raw scan is about $20.00, the GZIP scan $5.00, the Parquet single-column scan $0.05, and the partitioned scan is measured in fractions of a cent. Format and layout are cost controls, not merely performance controls.

• 📚 Did you know? Sorting helps only filters on the sort column: the min/max stats describe the sort key, so a predicate on any other column skips nothing. That is why "add a SORTKEY on customer_id" answers a customer-filtered query and does nothing for a product-filtered one (AWS Athena performance guidance, accessed Oct 2026).

The other half of an Athena bill is the reservation minimum rather than the bytes scanned, and that minimum itself moved in 2026. Take the same nightly batch window, 12 DPU running for 10 minutes, and read it in DPU-hours (DPU × hours) under the old rule and the new one:

Reading the chart: the work never changed, 12 DPU for 10 minutes either way, but the billable quantity fell from 24 × 1 = 24 DPU-hours to 12 × (10/60) = 2 DPU-hours, a 12× cut produced purely by lowering the service minimum from 24 DPU / 60 minutes to 4 DPU / 1 minute (AWS Big Data Blog, 11 Feb 2026, accessed Oct 2026). Layout still decides how many bytes you scan; the reservation rule decides how many of those minutes you are forced to buy.

2026 Updates (as of October 2026)

---

Real-World Case Studies

Every figure below is customer- or AWS-claimed and unaudited, with the source named so you can check it. The examinable point is the pattern, which catalog object was shared, which schema contract was enforced, not the marketing.

Case A, BMW Group: schemas in Glue, data in S3

The examinable pattern: the data never left S3 while ownership split across accounts: Glue holds the schemas, Athena and SageMaker consume them in place. That is exactly the resource-link-plus-Lake-Formation arrangement in Section 5, and it is why "copy the data into each team's account" is a distractor: sharing belongs at the metadata layer.

Case B, GoDaddy: a data mesh on one shared catalog

"There are over 2,000 data products built on the GoDaddy data mesh on AWS … Our data mesh manages multiple petabytes of data across hundreds of accounts."

Read the number correctly: 2,000 data products is a catalog-scale problem, not a storage problem. Resource links (which render in italics, need DESCRIBE plus a target grant, and resolve only the first link in a chain) plus AWS RAM shares are what make hundreds of accounts query one Glue database without copying it.

• 📚 Did you know? All three case studies put AWS Glue Data Catalog in the middle and none of them moved data to give a team access: BMW for producer/consumer separation, GoDaddy for mesh-wide sharing, IAS for classification-based access. That repetition is the hint: on DEA-C01, "make the data available to another account or engine" most often resolves to a catalog or permission answer (resource link, LF grant, RAM share, catalog view), not a copy answer (COPY, export, snapshot) (AWS case study and AWS Big Data Blog, accessed Oct 2026).

Case C, Integral Ad Science: hundreds of rules collapse into two tags

"With Lake Formation tag-based access controls, IAS reduced hundreds of permission rules down to precisely two rules."

Read the arithmetic, not the marketing: two rules is a different kind of grant, not a shorter list. A per-table policy costs roughly n(principals) × n(tables); a tag grant costs n(principals) + n(tag values), and because "Tables inherit LF-Tags from databases and columns inherit LF-Tags from tables" (Section 5), tagging the database once pulls every future table and column into the same policy without anyone editing a rule. The grant is written against classification=restricted, not against a table name, which is why column-level PII control survives a thousand new tables.

• 📚 Did you know? IAS separated who may query from how much a team may run: access to individual columns was decided by Lake Formation tags, while each business unit got its own Athena workgroup carrying query limits and cost-allocation tags. Two independent control planes, grants answer "can I read this column?", workgroups answer "how far may this team go and who pays?", and exam options love to swap them (AWS Big Data Blog, accessed Oct 2026).

---

Practice Questions

Comparative Verdict: how this topic compares on exam day

• Versus other clouds: the DEA-C01 guide tests AWS services only: AWS Glue Data Catalog, AWS Lake Formation, Amazon Athena, Amazon Redshift, Amazon DynamoDB. Nothing in the guide compares AWS with another provider, so any option pivoting to a competitor's metastore, catalog or table format offering is out of scope by construction; answer with an in-scope AWS service or an AWS-published principle.
• Versus self-managed / on-premises (Hive metastore you run yourself): you can build the same tree on Hadoop, but then you own the metastore's availability, its IAM, its cross-account story and its scaling. The exam's preference is the managed, shared metadata layer, Glue plus Lake Formation grants, with the Apache Hive metastore appearing as the concept the Data Catalog implements (Task 2.2.2 names both). Choose the option with the least undifferentiated operational work.
• Versus another AWS service in the same layer: Glue Data Catalog is the metadata store; Athena is the query engine; Lake Formation is the permission plane; Amazon SageMaker Catalog is the business/governance catalog (skill 2.2.6); Glue crawlers are the discovery mechanism. A question asking "where is the schema registered" never answers "Athena", and one asking "who may read this column" never answers "Glue": Lake Formation grants and LF-Tags do that.
• Versus a manual, human process: GoDaddy's 2,000+ data products and BMW's 10 TB/day (customer claims, accessed Oct 2026) are outcomes of mechanisms, crawls, resource links, LF grants, partition indexes, not of someone maintaining a spreadsheet of table locations. On the exam, prefer the option that automates registration, synchronization or permissioning over the option that requires an engineer to remember.