AWS Glue Data Catalog and crawlers: tables, partitions and schema drift
Catalogue Harbourline's raw and curated data in the AWS Glue Data Catalog, run a crawler safely with the right schema-change and recrawl policies, and know when to define schemas yourself, register partitions explicitly or use Athena partition projection instead.
Assumes: .NET 10 LTS · AWS SDK for .NET v4 (AWSSDK.Glue 4.1.1.2) · Amazon.CDK.Lib 2.277.0 · Terraform AWS provider v6 · AWS CLI v2 · Region us-east-1. Verified against official sources on 2026-11-18.
Learning objectives
By the end of this lesson you can:
- Explain what the Glue Data Catalog stores (databases, tables, partitions, schemas, table properties) and which services read it.
- Configure a crawler with a least-privilege role, classifiers, a schema change policy, a recrawl policy and table-grouping options, and read its results critically.
- Recognise schema drift and decide whether the crawler should update the table, log the change, or fail the pipeline.
- Keep partitions in sync using four methods: crawler,
MSCK REPAIR TABLE, explicitALTER TABLE ADD PARTITION/ Glue API calls, and Athena partition projection, and choose between them. - Create and update catalogue entries from C# and with CDK (C#) or Terraform.
Prerequisites
- S3 data lake design — the buckets and key layout used here
- File formats — Parquet types and schema evolution
- Tools: AWS CLI v2 (
learn-sandbox), .NET 10 SDK, CDK CLI or Terraform 1.14
Why this matters in banking
On Monday morning, the reconciliation dashboard shows zero transactions for Sunday. The job ran, the Parquet files are in S3, and nothing failed. The cause: the Sunday partition was never added to the catalogue, so Athena did not know it existed. Finance spends two hours chasing a "missing" £3.2 million.
A week later, the opposite happens. A partner adds a column to its file, a nightly crawler "helpfully" updates the raw table's schema, the column order shifts for CSV, and the quarantine rate jumps to 100%. Nobody approved that schema change; a crawler made it.
The catalogue is where "files in S3" become "tables people trust". A bank needs to know who can change a table definition, when partitions appear, and how schema changes are approved.
The idea in plain language
S3 holds files. Query engines need to know: which files belong to which table, what the columns are, what types they have, how the files are formatted, and which partitions exist. The AWS Glue Data Catalog is that shared dictionary. It is Hive Metastore-compatible, one per account per Region, and used by Athena, Glue jobs, EMR, Redshift Spectrum, Lake Formation and Quick Sight (through Athena).
A crawler is a robot that reads a sample of your files, guesses the schema and partitions, and writes or updates table definitions. Helpful for discovery, risky as an authority.
Analogy: The catalogue is the bank's records index: a card for every file series saying what is in it, how it is laid out and which drawers hold it. A crawler is a temp worker who walks the archive and writes index cards from what they see. Brilliant on day one, when nobody knows what the boxes contain. But you would not let the temp rewrite the index card for the general ledger every night without a review.
What problem the AWS service solves
What the Data Catalog holds
| Object | What it is | Example |
|---|---|---|
| Catalog | One per account per Region (plus federated catalogs such as S3 Tables) | 123456789012 |
| Database | A namespace for tables | hl_m07_raw, hl_m07_curated, hl_m07_gold |
| Table | Location, columns and types, input/output format, SerDe, partition keys, properties | hl_m07_curated.transactions |
| Partition | A partition key value set plus its S3 location (and its own storage descriptor) | year=2026/month=11/day=16 |
| Table version | Previous definitions kept on each update | Useful to diff schema changes |
| Connection | JDBC/network details for Glue jobs and crawlers | SQL Server source over a VPC |
Pricing (us-east-1, checked 2026-11-18): the first 1 million objects stored and 1 million requests per month are free; beyond that USD 1 per 100,000 objects per month and USD 1 per million requests. Tables, partitions, table versions and indexes count as objects. Partition-heavy tables are the usual way to exceed the free tier.
What a crawler does
- Lists objects under its targets (S3 paths, JDBC, DynamoDB, or Iceberg/Delta/Hudi tables).
- Samples files and runs classifiers in order: your custom classifiers first, then built-in ones (CSV, JSON, Parquet, ORC, Avro, XML and others). The first confident match decides the format.
- Infers a schema and groups compatible folders into one table; detects
name=valuefolders as partitions. - Writes changes to the catalogue according to its schema change policy.
Crawlers run on Glue capacity billed at USD 0.44 per DPU-hour, per second, with a minimum billed duration per run (historically 10 minutes ⚠ VERIFY). A crawler over a few files typically costs a few cents per run.
Crawler settings that matter
| Setting | Options | Harbourline choice |
|---|---|---|
| Schema change policy: UpdateBehavior | UPDATE_IN_DATABASE (apply changes) or LOG (only log) | Raw discovery crawler: LOG. Never let a crawler change a curated table |
| Schema change policy: DeleteBehavior | LOG, DELETE_FROM_DATABASE, DEPRECATE_IN_DATABASE | LOG (deletions are investigated, not automated) |
| Recrawl policy | CRAWL_EVERYTHING, CRAWL_NEW_FOLDERS_ONLY, CRAWL_EVENT_MODE (S3 events via SQS) | CRAWL_NEW_FOLDERS_ONLY for append-only date partitions; event mode for large, busy prefixes |
| Configuration: partitions inherit from table | "Partitions": {"AddOrUpdateBehavior": "InheritFromTable"} | On: partitions use the table's schema, not their own guess |
| Configuration: table grouping | CombineCompatibleSchemas, TableLevelConfiguration: N | Set the table level so transactions/ is one table, not one per day |
| Configuration: new columns | "Tables": {"AddOrUpdateBehavior": "MergeNewColumns"} | Only for discovery crawlers |
| Exclusions | Glob patterns | Exclude _SUCCESS, *.crc, _temporary/**, *_defects.csv |
| Lake Formation credentials | Crawler uses LF permissions instead of IAM S3 access | Use when the lake is governed by Lake Formation |
| Security configuration | Encryption for CloudWatch logs and job bookmarks | Always, with a CMK |
Schema drift
Schema drift means the data no longer matches the table definition: new columns, removed columns, changed types, or a different file format. How to react depends on the zone:
| Zone | Policy |
|---|---|
| Raw | Detect and alert. A raw "discovery" crawler in LOG mode, or your own header validation in the ingest job, flags the change. The file goes to quarantine until the contract is updated. |
| Curated / gold | The table definition is code. It changes only through a reviewed IaC or migration change, never because a crawler saw a new column. |
Keeping partitions in sync
| Method | How | Good for | Watch out for |
|---|---|---|---|
| Crawler | Scheduled or event-driven crawler adds new partitions | Discovery, irregular layouts | Cost per run, latency, may change schemas if misconfigured |
MSCK REPAIR TABLE (Athena) | Scans the table location for Hive-style folders and adds missing partitions | Occasional backfills | Slow on large tables (lists everything); only Hive-style key=value paths |
| Explicit registration | ALTER TABLE ADD PARTITION in Athena, or CreatePartition/BatchCreatePartition from the job that wrote the data | Pipelines: the writer knows exactly which partition it wrote | Must be part of the job, with retries (AlreadyExists is fine) |
| Partition projection (Athena) | Table properties describe partition values and the location template; Athena computes partitions at query time | Predictable, date-based layouts with many partitions | Athena only: Glue jobs, EMR and Redshift Spectrum do not use projection |
Spark enableUpdateCatalog | A Glue job's sink updates the catalogue as it writes | Glue-written tables | Glue-specific; still needs the table defined first |
Harbourline's rule: curated and gold tables are defined in IaC; the job that writes a partition registers it; Athena uses partition projection for the long history of date partitions; crawlers are only for discovery in raw.
When to define schemas yourself
Define tables explicitly (DDL, IaC or API) when:
- The data has a contract (curated, gold): types such as
decimal(18,2)must be exact, and crawlers inferdoublefor numbers in CSV and JSON. - Columns contain values that look numeric but are not (
merchant_category_code=0742). - The table must not change without review (audit, Lake Formation permissions tied to columns).
- You need a specific SerDe (
OpenCSVSerdefor quoted CSV) or table properties (projection,skip.header.line.count).
Let a crawler propose a schema when you are exploring an unfamiliar source, then copy its suggestion into code and fix the types.
Microsoft / Azure comparison
| You know (Microsoft/Azure) | AWS equivalent | What's different |
|---|---|---|
| Hive Metastore in HDInsight / Databricks | Glue Data Catalog | Fully managed, serverless, one per account per Region |
| Microsoft Purview data map scans | Glue crawlers | Crawlers write technical metadata; business glossary lives in Amazon DataZone / SageMaker Catalog |
Synapse serverless CREATE EXTERNAL TABLE | Athena CREATE EXTERNAL TABLE (writes to the Glue catalogue) | The catalogue is shared by every AWS analytics engine |
sp_rename, ALTER TABLE migrations in a DACPAC | IaC (CDK/Terraform) for table definitions | Treat table definitions like a database project: reviewed and deployed |
| Partition switching / partition functions | Partition registration or projection | Partitions are folders plus catalogue entries, not filegroups |
| ADF "Get Metadata" activity | Glue GetTable, GetPartitions API calls | Called from Step Functions or code |
Architecture
Security-boundary view: catalogue permissions (glue:GetTable, glue:CreatePartition, glue:UpdateTable) are separate from data permissions (s3:GetObject, kms:Decrypt). An analyst may be able to see that hl_m07_raw.transactions_sftp exists without being able to read a byte of it. With Lake Formation, both become grants on the catalogue object (see the governance lesson in the module overview).
Step-by-step implementation
Create the databases
Use the IaC below or the CLI: hl_m07_raw, hl_m07_curated, hl_m07_gold, each with a description and location.
Land sample data in raw and curated
Raw: the messy CSV and the SFTP file. Curated: two days of Parquet from the converter in File formats.
aws s3 cp datasets/samples/csv/transactions_messy.csv \
"s3://hl-m07-raw-$ACCOUNT/transactions/source=core/ingest_date=2026-11-16/transactions_messy.csv"
aws s3 cp out/curated/transactions/ "s3://hl-m07-curated-$ACCOUNT/transactions/" --recursiveRun a discovery crawler on raw
Create crawler hl-m07-raw-discovery with UpdateBehavior=LOG, DeleteBehavior=LOG, exclusions and a table level of 2 (transactions/ becomes one table). Run it and read what it proposes. Expect: CSV detected, amount typed as double, merchant_category_code as bigint, partitions source and ingest_date detected. Those wrong types are exactly why curated tables are not crawled.
Define the curated table yourself, with partition projection
Create hl_m07_curated.transactions from IaC (or 03-curated-ddl.sql below) with exact types and projection properties for year, month, day.
Register a partition from code
Run the C# CatalogTool add-partition command for 2026-11-16. This is what the pipeline does after writing a day. Then query in Athena; note that with projection enabled Athena ignores registered partitions for that table, so try both a projected and a non-projected copy of the table to see the difference.
Simulate drift
Upload a CSV with an extra column in the middle to a new ingest_date folder, re-run the crawler and read the CloudWatch log: the crawler logs the change instead of applying it. Compare with UPDATE_IN_DATABASE, which would have altered the table.
AWS Console instructions
- Console → AWS Glue → Data Catalog → Databases → Add database: name
hl_m07_raw, locations3://hl-m07-raw-<account-id>/. Repeat for curated and gold. - Console → AWS Glue → Data Catalog → Crawlers → Create crawler: name
hl-m07-raw-discovery; data source S3s3://hl-m07-raw-<account-id>/transactions/, Crawl new sub-folders only; exclude patterns**/_SUCCESS,**/*.crc,**/*_defects.csv. IAM role:hl-m07-crawler(created by the IaC below). Target databasehl_m07_raw, table prefixcrawled_. Advanced options: "Update the table definition in the data catalog" off → choose "Ignore the change and don't update the table"; "Object deletion" → "Ignore the change"; "Create a single schema for each S3 path" on. Security configuration:hl-m07-glue-security. Schedule: on demand. - Run crawler, then Crawler runs → View CloudWatch logs to see what it found or would have changed.
- Data Catalog → Tables → crawled_transactions → Schema and Partitions tabs. Versions shows table history.
- Data Catalog → Tables → transactions (curated) → Edit table → Table properties shows projection properties.
AWS CLI
export AWS_PROFILE=learn-sandbox AWS_REGION=us-east-1
ACCOUNT=$(aws sts get-caller-identity --query Account --output text)
# Databases
for DB in raw curated gold; do
aws glue create-database --database-input "{\"Name\":\"hl_m07_$DB\",\"Description\":\"Harbourline m07 $DB zone\",\"LocationUri\":\"s3://hl-m07-$DB-$ACCOUNT/\"}"
done
# Discovery crawler on raw: logs schema changes, never applies them
aws glue create-crawler --name hl-m07-raw-discovery \
--role "arn:aws:iam::$ACCOUNT:role/hl-m07-crawler" \
--database-name hl_m07_raw --table-prefix crawled_ \
--targets "{\"S3Targets\":[{\"Path\":\"s3://hl-m07-raw-$ACCOUNT/transactions/\",\"Exclusions\":[\"**/_SUCCESS\",\"**/*.crc\",\"**/*_defects.csv\"]}]}" \
--schema-change-policy UpdateBehavior=LOG,DeleteBehavior=LOG \
--recrawl-policy RecrawlBehavior=CRAWL_NEW_FOLDERS_ONLY \
--configuration '{"Version":1.0,"Grouping":{"TableLevelConfiguration":2,"TableGroupingPolicy":"CombineCompatibleSchemas"},"CrawlerOutput":{"Partitions":{"AddOrUpdateBehavior":"InheritFromTable"}}}' \
--crawler-security-configuration hl-m07-glue-security \
--tags Project=cea-lab,Lab=m07-transaction-pipeline,Environment=learn,Owner=your-name
# Note: CRAWL_NEW_FOLDERS_ONLY requires UpdateBehavior=LOG, which is what we want here.
aws glue start-crawler --name hl-m07-raw-discovery
aws glue get-crawler --name hl-m07-raw-discovery --query 'Crawler.{State:State,Last:LastCrawl.Status,Error:LastCrawl.ErrorMessage}'
# What did it create?
aws glue get-tables --database-name hl_m07_raw \
--query 'TableList[].{Name:Name,Format:Parameters.classification,Cols:StorageDescriptor.Columns[].join(`:`,[Name,Type])}'
aws glue get-partitions --database-name hl_m07_raw --table-name crawled_transactions \
--query 'Partitions[].Values'
# Crawl metrics (tables created/updated/deleted, time left)
aws glue get-crawler-metrics --crawler-name-list hl-m07-raw-discovery
# Explicitly register one curated partition (what the pipeline does after writing a day)
aws glue create-partition --database-name hl_m07_curated --table-name transactions_registered \
--partition-input "{\"Values\":[\"2026\",\"11\",\"16\"],\"StorageDescriptor\":{\"Location\":\"s3://hl-m07-curated-$ACCOUNT/transactions/year=2026/month=11/day=16/\",\"InputFormat\":\"org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat\",\"OutputFormat\":\"org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat\",\"SerdeInfo\":{\"SerializationLibrary\":\"org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe\"}}}"Infrastructure as Code
using System.Collections.Generic;
using Amazon.CDK;
using Amazon.CDK.AWS.Glue;
using Amazon.CDK.AWS.IAM;
using Amazon.CDK.AWS.KMS;
using Constructs;
namespace Harbourline.DataLake.Infra;
public sealed class CatalogStack : Stack
{
public CatalogStack(Construct scope, string id, IStackProps? props = null) : base(scope, id, props)
{
var account = Aws.ACCOUNT_ID;
var rawBucketArn = $"arn:aws:s3:::hl-m07-raw-{account}";
var rawKey = Key.FromLookup(this, "RawKey", new KeyLookupOptions { AliasName = "alias/hl-m07-raw" });
foreach (var zone in new[] { "raw", "curated", "gold" })
{
_ = new CfnDatabase(this, $"Db-{zone}", new CfnDatabaseProps
{
CatalogId = account,
DatabaseInput = new CfnDatabase.DatabaseInputProperty
{
Name = $"hl_m07_{zone}",
Description = $"Harbourline m07 {zone} zone",
LocationUri = $"s3://hl-m07-{zone}-{account}/",
},
});
}
// Least-privilege crawler role: read raw only, write catalogue, write its own logs.
var role = new Role(this, "CrawlerRole", new RoleProps
{
RoleName = "hl-m07-crawler",
AssumedBy = new ServicePrincipal("glue.amazonaws.com"),
ManagedPolicies = new[] { ManagedPolicy.FromAwsManagedPolicyName("service-role/AWSGlueServiceRole") },
});
role.AddToPolicy(new PolicyStatement(new PolicyStatementProps
{
Actions = new[] { "s3:GetObject", "s3:ListBucket" },
Resources = new[] { rawBucketArn, $"{rawBucketArn}/transactions/*" },
}));
rawKey.GrantDecrypt(role);
var securityConfig = new CfnSecurityConfiguration(this, "GlueSecurity", new CfnSecurityConfigurationProps
{
Name = "hl-m07-glue-security",
EncryptionConfiguration = new CfnSecurityConfiguration.EncryptionConfigurationProperty
{
CloudWatchEncryption = new CfnSecurityConfiguration.CloudWatchEncryptionProperty
{
CloudWatchEncryptionMode = "SSE-KMS",
KmsKeyArn = rawKey.KeyArn,
},
JobBookmarksEncryption = new CfnSecurityConfiguration.JobBookmarksEncryptionProperty
{
JobBookmarksEncryptionMode = "CSE-KMS",
KmsKeyArn = rawKey.KeyArn,
},
S3Encryptions = new object[]
{
new CfnSecurityConfiguration.S3EncryptionProperty { S3EncryptionMode = "SSE-KMS", KmsKeyArn = rawKey.KeyArn },
},
},
});
_ = new CfnCrawler(this, "RawDiscovery", new CfnCrawlerProps
{
Name = "hl-m07-raw-discovery",
Role = role.RoleArn,
DatabaseName = "hl_m07_raw",
TablePrefix = "crawled_",
CrawlerSecurityConfiguration = securityConfig.Name,
Targets = new CfnCrawler.TargetsProperty
{
S3Targets = new object[]
{
new CfnCrawler.S3TargetProperty
{
Path = $"s3://hl-m07-raw-{account}/transactions/",
Exclusions = new[] { "**/_SUCCESS", "**/*.crc", "**/*_defects.csv" },
},
},
},
SchemaChangePolicy = new CfnCrawler.SchemaChangePolicyProperty
{
UpdateBehavior = "LOG",
DeleteBehavior = "LOG",
},
RecrawlPolicy = new CfnCrawler.RecrawlPolicyProperty { RecrawlBehavior = "CRAWL_NEW_FOLDERS_ONLY" },
Configuration = """
{"Version":1.0,
"Grouping":{"TableLevelConfiguration":2,"TableGroupingPolicy":"CombineCompatibleSchemas"},
"CrawlerOutput":{"Partitions":{"AddOrUpdateBehavior":"InheritFromTable"}}}
""",
Tags = new Dictionary<string, string>
{
["Project"] = "cea-lab", ["Lab"] = "m07-transaction-pipeline",
["Environment"] = "learn", ["Owner"] = "your-name",
},
});
Amazon.CDK.Tags.Of(this).Add("Project", "cea-lab");
Amazon.CDK.Tags.Of(this).Add("Lab", "m07-transaction-pipeline");
Amazon.CDK.Tags.Of(this).Add("Environment", "learn");
Amazon.CDK.Tags.Of(this).Add("Owner", "your-name");
}
}Key.FromLookup needs the stack's Env to set an explicit account and Region. cdk-nag will flag the AWS managed policy (AwsSolutions-IAM4); in production replace AWSGlueServiceRole with a customer managed policy limited to the catalogue resources and log groups the crawler uses.
Complete code
SQL: the curated table with partition projection
CREATE EXTERNAL TABLE IF NOT EXISTS hl_m07_curated.transactions (
transaction_id string,
account_id string,
booking_date date,
value_date date,
amount decimal(18,2),
currency string,
type string,
channel string,
counterparty_name string,
counterparty_iban_token string,
counterparty_iban_masked string,
merchant_category_code string,
reference string,
status string,
idempotency_key string,
created_at timestamp,
is_late_arriving boolean,
dq_warnings string,
source_file string,
ingest_run_id string
)
PARTITIONED BY (year string, month string, day string)
STORED AS PARQUET
LOCATION 's3://hl-m07-curated-ACCOUNT_ID/transactions/'
TBLPROPERTIES (
'parquet.compression' = 'ZSTD',
'projection.enabled' = 'true',
'projection.year.type' = 'integer',
'projection.year.range' = '2025,2030',
'projection.month.type' = 'integer',
'projection.month.range' = '1,12',
'projection.month.digits' = '2',
'projection.day.type' = 'integer',
'projection.day.range' = '1,31',
'projection.day.digits' = '2',
'storage.location.template' = 's3://hl-m07-curated-ACCOUNT_ID/transactions/year=${year}/month=${month}/day=${day}/'
);
-- A filter on the partition columns prunes to one folder:
SELECT currency, COUNT(*) AS n, SUM(amount) AS net
FROM hl_m07_curated.transactions
WHERE year = '2026' AND month = '11' AND day = '16' AND status = 'booked'
GROUP BY currency;The columns match CURATED_COLUMNS in labs/m07-transaction-pipeline/glue/src/harbourline_etl/transactions.py (the last three, year, month, day, are partition keys rather than data columns).
C#: a small catalogue tool
<Project Sdk="Microsoft.NET.Sdk">
<PropertyGroup>
<OutputType>Exe</OutputType>
<TargetFramework>net10.0</TargetFramework>
<Nullable>enable</Nullable>
<ImplicitUsings>enable</ImplicitUsings>
<TreatWarningsAsErrors>true</TreatWarningsAsErrors>
<RootNamespace>Harbourline.DataLake.CatalogTool</RootNamespace>
</PropertyGroup>
<ItemGroup>
<PackageReference Include="AWSSDK.Glue" Version="4.1.1.2" />
</ItemGroup>
</Project>using System.Globalization;
using Amazon.Glue;
using Amazon.Glue.Model;
namespace Harbourline.DataLake.CatalogTool;
public static class Program
{
public static async Task<int> Main(string[] args)
{
using var glue = new AmazonGlueClient(Amazon.RegionEndpoint.USEast1);
return args switch
{
["crawl", var crawler] => await CrawlAsync(glue, crawler),
["add-partition", var db, var table, var date] => await AddPartitionAsync(glue, db, table,
DateOnly.ParseExact(date, "yyyy-MM-dd", CultureInfo.InvariantCulture)),
["diff-versions", var db, var table] => await DiffVersionsAsync(glue, db, table),
_ => Usage(),
};
}
private static int Usage()
{
Console.Error.WriteLine("""
usage:
CatalogTool crawl <crawler-name>
CatalogTool add-partition <database> <table> <yyyy-MM-dd>
CatalogTool diff-versions <database> <table>
""");
return 2;
}
// Start a crawler, wait for it to finish, and report what it did.
private static async Task<int> CrawlAsync(IAmazonGlue glue, string name)
{
try
{
await glue.StartCrawlerAsync(new StartCrawlerRequest { Name = name });
}
catch (CrawlerRunningException)
{
Console.WriteLine("Crawler already running; waiting for it.");
}
Crawler crawler;
do
{
await Task.Delay(TimeSpan.FromSeconds(15));
crawler = (await glue.GetCrawlerAsync(new GetCrawlerRequest { Name = name })).Crawler;
Console.WriteLine($" state: {crawler.State}");
} while (crawler.State != CrawlerState.READY);
var metrics = (await glue.GetCrawlerMetricsAsync(new GetCrawlerMetricsRequest
{
CrawlerNameList = new List<string> { name },
})).CrawlerMetricsList.Single();
Console.WriteLine($"Last crawl: {crawler.LastCrawl?.Status} {crawler.LastCrawl?.ErrorMessage}");
Console.WriteLine($"Tables created {metrics.TablesCreated}, updated {metrics.TablesUpdated}, deleted {metrics.TablesDeleted}");
Console.WriteLine($"Logs: {crawler.LastCrawl?.LogGroup} / {crawler.LastCrawl?.LogStream}");
return crawler.LastCrawl?.Status == LastCrawlStatus.SUCCEEDED ? 0 : 1;
}
// Register one day's partition, copying format settings from the table (what a pipeline does after writing).
private static async Task<int> AddPartitionAsync(IAmazonGlue glue, string db, string table, DateOnly day)
{
var t = (await glue.GetTableAsync(new GetTableRequest { DatabaseName = db, Name = table })).Table;
var location = $"{t.StorageDescriptor.Location.TrimEnd('/')}/year={day:yyyy}/month={day:MM}/day={day:dd}/";
var input = new PartitionInput
{
Values = new List<string> { day.ToString("yyyy", CultureInfo.InvariantCulture),
day.ToString("MM", CultureInfo.InvariantCulture),
day.ToString("dd", CultureInfo.InvariantCulture) },
StorageDescriptor = new StorageDescriptor
{
Location = location,
Columns = t.StorageDescriptor.Columns,
InputFormat = t.StorageDescriptor.InputFormat,
OutputFormat = t.StorageDescriptor.OutputFormat,
SerdeInfo = t.StorageDescriptor.SerdeInfo,
},
};
try
{
await glue.CreatePartitionAsync(new CreatePartitionRequest
{
DatabaseName = db,
TableName = table,
PartitionInput = input,
});
Console.WriteLine($"Registered {location}");
}
catch (AlreadyExistsException)
{
// Idempotent: a re-run of the same day is not an error.
Console.WriteLine($"Already registered: {location}");
}
return 0;
}
// Compare the two most recent table versions: catches unreviewed schema changes.
private static async Task<int> DiffVersionsAsync(IAmazonGlue glue, string db, string table)
{
var versions = (await glue.GetTableVersionsAsync(new GetTableVersionsRequest
{
DatabaseName = db,
TableName = table,
MaxResults = 2,
})).TableVersions;
if (versions is null || versions.Count < 2)
{
Console.WriteLine("Only one version exists: no changes.");
return 0;
}
static Dictionary<string, string> Cols(TableVersion v) =>
v.Table.StorageDescriptor.Columns.ToDictionary(c => c.Name, c => c.Type);
var (newer, older) = (Cols(versions[0]), Cols(versions[1]));
var changes = 0;
foreach (var (name, type) in newer)
{
if (!older.TryGetValue(name, out var oldType)) { Console.WriteLine($"+ {name} {type}"); changes++; }
else if (oldType != type) { Console.WriteLine($"~ {name} {oldType} -> {type}"); changes++; }
}
foreach (var name in older.Keys.Except(newer.Keys)) { Console.WriteLine($"- {name}"); changes++; }
Console.WriteLine(changes == 0 ? "No column changes." : $"{changes} column change(s) between versions {versions[1].VersionId} and {versions[0].VersionId}.");
return changes == 0 ? 0 : 1;
}
}Run it:
export AWS_PROFILE=learn-sandbox
dotnet run --project CatalogTool -- crawl hl-m07-raw-discovery
dotnet run --project CatalogTool -- add-partition hl_m07_curated transactions_registered 2026-11-16
dotnet run --project CatalogTool -- diff-versions hl_m07_raw crawled_transactionsCode explained line by line
SQL
decimal(18,2),date,timestamp,stringformerchant_category_code— exact types a crawler would not infer from CSV.PARTITIONED BY (year string, month string, day string)— string keys keep zero padding ('06'), which matches the S3 folder names exactly.STORED AS PARQUET— sets the Parquet input/output formats and SerDe.'projection.enabled' = 'true'— Athena computes partitions from the rules instead of reading them from the catalogue. No crawler, noMSCK REPAIR, no registration needed for Athena.'projection.month.digits' = '2'— generates01…12, matching zero-padded folders.'storage.location.template'— tells Athena where each projected partition lives. Athena substitutes${year},${month},${day}.- The example query filters on all three partition keys, so Athena reads one folder. Filters on
booking_datealone would not prune here, because projection works on partition columns; that is one reason Iceberg's hidden partitioning is attractive.
C#
args switch { ["crawl", var crawler] => ... }— C# list patterns make a tiny, readable command-line parser.StartCrawlerAsyncinsidetry ... catch (CrawlerRunningException)— two schedules can overlap; treat "already running" as "wait for it".- The
do ... while (crawler.State != CrawlerState.READY)loop — crawlers goRUNNING→STOPPING→READY. Poll every 15 seconds; crawls take at least a minute or two. GetCrawlerMetricsAsync— tables created, updated and deleted by the last run. A non-zero "updated" on a crawler you expected to only log is an alarm.crawler.LastCrawl?.LogGroup/LogStream— the CloudWatch location of the crawler's decisions, including logged-but-not-applied schema changes.GetTableAsyncthennew StorageDescriptor { ... }— partitions copy the table's format so they are readable by every engine. OnlyLocationdiffers.catch (AlreadyExistsException)— partition registration must be idempotent, because the job that writes a day may be retried.GetTableVersionsAsync(... MaxResults = 2)— the catalogue keeps versions of table definitions; the newest comes first. Diffing them in a scheduled check is a cheap control against unreviewed schema changes.- Exit code 1 when columns changed — lets a Step Functions task or CI job fail visibly.
Security considerations
⚠ Learning shortcut — not for production
The crawler role uses the AWS managed policy AWSGlueServiceRole, and you create tables as an administrator. In production, crawler and job roles get customer managed policies scoped to specific databases, tables and log groups; humans cannot call glue:UpdateTable or glue:DeleteTable on curated and gold outside a reviewed deployment pipeline; and access to data is granted through Lake Formation rather than broad IAM S3 permissions.
- Catalogue metadata can be sensitive. Column names (
customer_date_of_birth), table comments and partition values (partner=PTN0042) reveal information. Restrictglue:GetTables/GetPartitionsto roles that need them. - Catalogue encryption. The Data Catalog can be encrypted with a KMS key (catalogue settings) and connection passwords encrypted separately. Turn both on in production.
- Crawler blast radius. A crawler with
UPDATE_IN_DATABASEandDELETE_FROM_DATABASEcan change or remove table definitions that Lake Formation permissions and reports depend on. Keep those behaviours for discovery databases only. - Resource policies. A Data Catalog resource policy can grant cross-account access; review it the same way as bucket policies. Lake Formation cross-account sharing is the managed way to do this.
Banking use case
Harbourline's catalogue rules, from the Data Platform runbook:
| Database | Defined by | Partitions | Crawlers |
|---|---|---|---|
hl_m07_raw | Discovery crawler proposes; data engineer copies into IaC as raw_* tables | Projection on ingest_date and source | hl-m07-raw-discovery, LOG only, daily |
hl_m07_curated | IaC only | Projection for Athena + explicit registration by the Glue job (for Spectrum and Glue readers) | None |
hl_m07_gold | IaC only (CTAS output tables recreated by the job) | Projection | None |
A nightly Step Functions task runs CatalogTool diff-versions across curated and gold; any unexpected change raises a ticket to the data owner.
Common mistakes
- Crawling curated tables → types drift to
double/bigint, columns reorder → define curated tables in code. - Crawler creates one table per day (
day_16,day_17…) → table level or grouping not set → setTableLevelConfigurationandCombineCompatibleSchemas; keep schemas identical across folders. - Non-data files in table locations (
_SUCCESS,.crc,*_defects.csv) → crawler confusion or Athena read errors → exclusions, and keep only data files under table prefixes. - Partition projection range too narrow (
2025,2026) → data for 2027 silently invisible → set a generous range and monitor. - Expecting projection to work in Glue jobs or Spectrum → those engines see no partitions → also register partitions if non-Athena readers need them.
MSCK REPAIR TABLEon huge tables every hour → slow, costly listing → explicit registration or projection.- Forgetting partition-level schemas → partitions created by crawlers carry their own column lists that may differ from the table →
InheritFromTable.
Troubleshooting
| Symptom | Likely cause | Fix |
|---|---|---|
| Athena returns zero rows for a new day | Partition not registered (no projection) | Register the partition, run MSCK REPAIR TABLE, or enable projection |
| Athena returns zero rows with projection | Template or digits do not match folder names (month=6 vs 06) | Fix storage.location.template / digits; check with aws s3 ls |
| Crawler finishes but creates no tables | Wrong path, exclusions too broad, or no read/decrypt permission | Check the crawler log; test s3:GetObject and kms:Decrypt as the crawler role |
| Crawler creates dozens of tables | Schemas differ between folders, or table level wrong | Set TableLevelConfiguration; fix inconsistent files |
HIVE_PARTITION_SCHEMA_MISMATCH | Partition columns differ from table columns | Use InheritFromTable; update or recreate the partitions |
CRAWL_NEW_FOLDERS_ONLY rejected on create | Requires UpdateBehavior=LOG | Set the schema change policy to LOG |
Crawler typed CSV amount as double | Inference from text | Define the table yourself with decimal(18,2) |
AccessDeniedException on glue:CreatePartition from the job | Job role lacks catalogue permission (or Lake Formation grant) | Grant CreatePartition/BatchCreatePartition on the specific table |
Cost and cleanup
Expected cost: one crawler over a few files: billed per second at USD 0.44 per DPU-hour, with a minimum billed duration per run ⚠ VERIFY; plan on USD 0.05–0.15 per crawler run. Run it two or three times: under USD 0.50. Catalogue objects and requests: within the free tier. Athena queries on a few KB: USD 0.00005 each (10 MB minimum).
export AWS_PROFILE=learn-sandbox AWS_REGION=us-east-1
aws glue delete-crawler --name hl-m07-raw-discovery
for DB in hl_m07_raw hl_m07_curated hl_m07_gold; do
aws glue delete-database --name "$DB" # also deletes its tables and partitions
done
aws glue delete-security-configuration --name hl-m07-glue-security
aws iam delete-role-policy --role-name hl-m07-crawler --policy-name crawler_data 2>/dev/null
aws iam detach-role-policy --role-name hl-m07-crawler \
--policy-arn arn:aws:iam::aws:policy/service-role/AWSGlueServiceRole
aws iam delete-role --role-name hl-m07-crawler
# Or: cdk destroy HlM07Catalog / terraform destroy
# Crawler log group (created on first run)
aws logs delete-log-group --log-group-name /aws-glue/crawlers 2>/dev/null
aws resourcegroupstaggingapi get-resources --tag-filters Key=Project,Values=cea-lab \
--query "ResourceTagMappingList[].ResourceARN"Deleting a database or table does not delete S3 data (except for Iceberg tables dropped through Athena). Remove data with the bucket cleanup in S3 data lake design.
Exercises
- 🟢 Guided: Create the databases and the discovery crawler, crawl the messy CSV, and list three types the crawler got wrong compared with CONVENTIONS.md §2.
- 🟡 Challenge: Create
hl_m07_raw.transactions_coreyourself withOpenCSVSerde,skip.header.line.count, all columns asstring(the raw landing shape), and projection onsource(enumtype with valuescore,sftp,api) andingest_date(datetype). Query one partition. - 🔴 Live problem: The Spectrum-based finance report shows no data for the last 9 days, but Athena queries on the same curated table work perfectly. Nothing changed in the Glue job. Diagnose and fix, and propose a control so it cannot recur silently.
Assignment
Write a catalogue standard for Harbourline: naming rules for databases and tables, which zones may be crawled and with which policies, how partitions are maintained per zone and per engine, how schema changes are proposed and approved, and the diff-versions control. Commit it with your IaC.
Solutions
Typical inferences on transactions_messy.csv: amount → double (should be decimal(18,2)); merchant_category_code → bigint (should be string); booking_date/value_date → string because messy values like 10/28/2026 break date inference (correct for raw, but shows why you cannot rely on inference); created_at → string. The crawler also adds partition columns source and ingest_date (partition_0/partition_1 if folders were not Hive-style). Why: inference is a guess from samples; contracts are decisions.
CREATE EXTERNAL TABLE hl_m07_raw.transactions_core (
transaction_id string, account_id string, booking_date string, value_date string,
amount string, currency string, type string, channel string, counterparty_name string,
counterparty_iban string, merchant_category_code string, reference string,
status string, idempotency_key string, created_at string)
PARTITIONED BY (source string, ingest_date string)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
WITH SERDEPROPERTIES ('separatorChar' = ',', 'quoteChar' = '"')
LOCATION 's3://hl-m07-raw-ACCOUNT_ID/transactions/'
TBLPROPERTIES (
'skip.header.line.count' = '1',
'projection.enabled' = 'true',
'projection.source.type' = 'enum',
'projection.source.values' = 'core,sftp,api',
'projection.ingest_date.type' = 'date',
'projection.ingest_date.format' = 'yyyy-MM-dd',
'projection.ingest_date.range' = '2026-01-01,NOW',
'storage.location.template' = 's3://hl-m07-raw-ACCOUNT_ID/transactions/source=${source}/ingest_date=${ingest_date}/'
);
SELECT COUNT(*) FROM hl_m07_raw.transactions_core WHERE source = 'core' AND ingest_date = '2026-11-16';All-string columns mean every raw row is readable, even malformed ones, so validation happens in the pipeline with reason codes rather than as query errors. NOW keeps the date range open-ended. Note: the SFTP files have a header/detail/trailer layout, so in practice sftp gets its own table.
Athena works because the table uses partition projection; Redshift Spectrum (and Glue jobs) ignore projection and read only partitions registered in the catalogue. Something stopped registering partitions nine days ago: check the job logs for CreatePartition calls, AccessDenied on glue:BatchCreatePartition (a recent IAM or Lake Formation change), or a removed post-write step. Fix: backfill with BatchCreatePartition for the missing days (or MSCK REPAIR TABLE once), restore the job step and permission. Control: a daily check comparing the latest registered partition (GetPartitions with an expression on year/month/day) with the latest S3 folder and the job's run history; alarm if they differ by more than one day.
Knowledge check
Answer: Athena, Glue ETL jobs, EMR (when configured), Redshift Spectrum, Lake Formation (permissions on catalogue objects) and Quick Sight through Athena, among others.
Answer: The crawler records detected schema changes in its logs but does not modify existing table definitions. New tables can still be created.
Answer: The table uses Athena partition projection. Only Athena computes partitions from projection properties; other engines need partitions registered in the catalogue.
Answer: Exact types (for example decimal(18,2), string MCCs) and controlled change (reviewed schema, stable Lake Formation column permissions). Also specific SerDes and table properties.
Answer: Partition projection: no crawler runs and no catalogue partition objects or API calls are needed.
Interview questions
- "How do you manage schema changes in a data lake?" — Contracts at ingestion, raw discovery in log-only mode, curated and gold definitions in IaC with review, table-version diffs as a control, Iceberg where schema evolves often, and Lake Formation permissions that are re-checked when columns change.
- "Crawlers: yes or no?" — Yes for discovery and for messy, changing raw sources; no as the authority for curated tables. Prefer explicit registration or projection for partitions in pipelines.
- "A dashboard shows no data for yesterday; where do you look?" — Did the job run and write files? Is the partition registered or projected correctly? Do the partition values match folder names? Does the reader's engine honour projection? Are Lake Formation permissions in place for the new partition's data location?
Certification practice
New data lands daily in s3://bucket/sales/dt=YYYY-MM-DD/. Analysts query it only with Athena. A crawler runs every hour to add partitions, and costs are rising. What is the MOST cost-effective change?
A. Run the crawler every 15 minutes with more DPUs.
B. Configure partition projection on the table with a date type for dt.
C. Run MSCK REPAIR TABLE every hour.
D. Store all data in a single unpartitioned prefix.
Answer: B. Projection removes the need to register partitions for Athena at no extra cost. A increases cost. C repeatedly lists the whole location. D removes partition pruning and increases scan cost.
A crawler runs nightly on a curated Parquet location. Last week it changed a column type, breaking downstream reports and Lake Formation column grants. The team still wants new partitions detected. What should the data engineer do?
A. Delete the crawler and stop adding partitions.
B. Set the crawler's schema change policy to LOG for updates and deletions, and configure partitions to inherit the table schema; manage the table definition through IaC.
C. Give analysts permission to fix types when they notice them.
D. Increase the crawler sample size.
Answer: B. The crawler still adds partitions but cannot alter or delete the reviewed table definition. A loses new data. C is uncontrolled change. D does not prevent updates.
A company runs Spark on Amazon EMR, ad hoc SQL in Athena and some queries from Amazon Redshift Spectrum over the same S3 data. Table definitions keep diverging between tools. What should a solutions architect recommend?
A. Maintain separate Hive metastores per tool and sync them nightly. B. Use the AWS Glue Data Catalog as the shared metastore for all three. C. Copy the data into each tool's own storage. D. Store schemas in a spreadsheet maintained by the data team.
Answer: B. The Glue Data Catalog is a shared, Hive-compatible metastore used by EMR, Athena and Spectrum. A and D keep divergence. C multiplies storage and inconsistency.
Completion checklist
- I created the three databases and a log-only discovery crawler with exclusions
- I compared the crawler's inferred types with the data contract
- I defined the curated table with exact types and partition projection
- I registered a partition from C# idempotently and diffed table versions
- I deleted the crawler, databases, security configuration and role
Key takeaways
- The Glue Data Catalog turns S3 files into shared tables for Athena, Glue, EMR, Spectrum and Lake Formation.
- Crawlers are for discovery; curated and gold definitions are code, reviewed like any schema migration.
- Use
LOGschema-change behaviour, partition inheritance, exclusions and a table level that matches your layout. - Keep partitions current with explicit registration from the writer, or with projection for Athena; remember projection is Athena-only.
- Diff table versions as a control against silent schema drift.
Further reading
Independent learning resource — not affiliated with, endorsed or sponsored by Amazon Web Services. AWS and related marks are trademarks of Amazon.com, Inc. or its affiliates. All banking data in examples is fictional. Sample architectures are for learning and are not automatically compliant with GDPR, PCI DSS or any banking regulation.
Data lake vs warehouse vs lakehouse: Apache Iceberg, time travel and when Redshift still wins
Compare data lakes, data warehouses and lakehouses, create an Apache Iceberg table for Harbourline's curated transactions in Athena, correct late-arriving data with MERGE, query history with time travel, and decide when Amazon Redshift or S3 Tables is the better answer.
PySpark in AWS Glue for beginners: DataFrames for C# developers
Learn just enough Python and Apache Spark to read, validate, transform and write Harbourline's transactions in an AWS Glue job, using LINQ as your mental model, and run the same job free on your laptop before paying for Glue.