Skip to Content
Cloud Engineering Academy
7. Data Engineering & ETL Pipelines

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.

intermediate⏱ 2 h 🟡 Low costDEA-C01SAA-C03

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, explicit ALTER 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

ObjectWhat it isExample
CatalogOne per account per Region (plus federated catalogs such as S3 Tables)123456789012
DatabaseA namespace for tableshl_m07_raw, hl_m07_curated, hl_m07_gold
TableLocation, columns and types, input/output format, SerDe, partition keys, propertieshl_m07_curated.transactions
PartitionA partition key value set plus its S3 location (and its own storage descriptor)year=2026/month=11/day=16
Table versionPrevious definitions kept on each updateUseful to diff schema changes
ConnectionJDBC/network details for Glue jobs and crawlersSQL 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

  1. Lists objects under its targets (S3 paths, JDBC, DynamoDB, or Iceberg/Delta/Hudi tables).
  2. 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.
  3. Infers a schema and groups compatible folders into one table; detects name=value folders as partitions.
  4. 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

SettingOptionsHarbourline choice
Schema change policy: UpdateBehaviorUPDATE_IN_DATABASE (apply changes) or LOG (only log)Raw discovery crawler: LOG. Never let a crawler change a curated table
Schema change policy: DeleteBehaviorLOG, DELETE_FROM_DATABASE, DEPRECATE_IN_DATABASELOG (deletions are investigated, not automated)
Recrawl policyCRAWL_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 groupingCombineCompatibleSchemas, TableLevelConfiguration: NSet the table level so transactions/ is one table, not one per day
Configuration: new columns"Tables": {"AddOrUpdateBehavior": "MergeNewColumns"}Only for discovery crawlers
ExclusionsGlob patternsExclude _SUCCESS, *.crc, _temporary/**, *_defects.csv
Lake Formation credentialsCrawler uses LF permissions instead of IAM S3 accessUse when the lake is governed by Lake Formation
Security configurationEncryption for CloudWatch logs and job bookmarksAlways, 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:

ZonePolicy
RawDetect 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 / goldThe 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

MethodHowGood forWatch out for
CrawlerScheduled or event-driven crawler adds new partitionsDiscovery, irregular layoutsCost per run, latency, may change schemas if misconfigured
MSCK REPAIR TABLE (Athena)Scans the table location for Hive-style folders and adds missing partitionsOccasional backfillsSlow on large tables (lists everything); only Hive-style key=value paths
Explicit registrationALTER TABLE ADD PARTITION in Athena, or CreatePartition/BatchCreatePartition from the job that wrote the dataPipelines: the writer knows exactly which partition it wroteMust 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 timePredictable, date-based layouts with many partitionsAthena only: Glue jobs, EMR and Redshift Spectrum do not use projection
Spark enableUpdateCatalogA Glue job's sink updates the catalogue as it writesGlue-written tablesGlue-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 infer double for 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 (OpenCSVSerde for 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 equivalentWhat's different
Hive Metastore in HDInsight / DatabricksGlue Data CatalogFully managed, serverless, one per account per Region
Microsoft Purview data map scansGlue crawlersCrawlers write technical metadata; business glossary lives in Amazon DataZone / SageMaker Catalog
Synapse serverless CREATE EXTERNAL TABLEAthena CREATE EXTERNAL TABLE (writes to the Glue catalogue)The catalogue is shared by every AWS analytics engine
sp_rename, ALTER TABLE migrations in a DACPACIaC (CDK/Terraform) for table definitionsTreat table definitions like a database project: reviewed and deployed
Partition switching / partition functionsPartition registration or projectionPartitions are folders plus catalogue entries, not filegroups
ADF "Get Metadata" activityGlue GetTable, GetPartitions API callsCalled 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/" --recursive

Run 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

  1. Console → AWS Glue → Data Catalog → Databases → Add database: name hl_m07_raw, location s3://hl-m07-raw-<account-id>/. Repeat for curated and gold.
  2. Console → AWS Glue → Data Catalog → Crawlers → Create crawler: name hl-m07-raw-discovery; data source S3 s3://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 database hl_m07_raw, table prefix crawled_. 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.
  3. Run crawler, then Crawler runs → View CloudWatch logs to see what it found or would have changed.
  4. Data Catalog → Tables → crawled_transactions → Schema and Partitions tabs. Versions shows table history.
  5. 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

infra/CatalogStack.cs
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

03-curated-ddl.sql
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

CatalogTool/CatalogTool.csproj
<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>
CatalogTool/Program.cs
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_transactions

Code explained line by line

SQL

  1. decimal(18,2), date, timestamp, string for merchant_category_code — exact types a crawler would not infer from CSV.
  2. PARTITIONED BY (year string, month string, day string) — string keys keep zero padding ('06'), which matches the S3 folder names exactly.
  3. STORED AS PARQUET — sets the Parquet input/output formats and SerDe.
  4. 'projection.enabled' = 'true' — Athena computes partitions from the rules instead of reading them from the catalogue. No crawler, no MSCK REPAIR, no registration needed for Athena.
  5. 'projection.month.digits' = '2' — generates 01…12, matching zero-padded folders.
  6. 'storage.location.template' — tells Athena where each projected partition lives. Athena substitutes ${year}, ${month}, ${day}.
  7. The example query filters on all three partition keys, so Athena reads one folder. Filters on booking_date alone would not prune here, because projection works on partition columns; that is one reason Iceberg's hidden partitioning is attractive.

C#

  1. args switch { ["crawl", var crawler] => ... } — C# list patterns make a tiny, readable command-line parser.
  2. StartCrawlerAsync inside try ... catch (CrawlerRunningException) — two schedules can overlap; treat "already running" as "wait for it".
  3. The do ... while (crawler.State != CrawlerState.READY) loop — crawlers go RUNNING → STOPPING → READY. Poll every 15 seconds; crawls take at least a minute or two.
  4. 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.
  5. crawler.LastCrawl?.LogGroup / LogStream — the CloudWatch location of the crawler's decisions, including logged-but-not-applied schema changes.
  6. GetTableAsync then new StorageDescriptor { ... } — partitions copy the table's format so they are readable by every engine. Only Location differs.
  7. catch (AlreadyExistsException) — partition registration must be idempotent, because the job that writes a day may be retried.
  8. 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.
  9. 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. Restrict glue:GetTables/GetPartitions to 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_DATABASE and DELETE_FROM_DATABASE can 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:

DatabaseDefined byPartitionsCrawlers
hl_m07_rawDiscovery crawler proposes; data engineer copies into IaC as raw_* tablesProjection on ingest_date and sourcehl-m07-raw-discovery, LOG only, daily
hl_m07_curatedIaC onlyProjection for Athena + explicit registration by the Glue job (for Spectrum and Glue readers)None
hl_m07_goldIaC only (CTAS output tables recreated by the job)ProjectionNone

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 → set TableLevelConfiguration and CombineCompatibleSchemas; 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 TABLE on 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

SymptomLikely causeFix
Athena returns zero rows for a new dayPartition not registered (no projection)Register the partition, run MSCK REPAIR TABLE, or enable projection
Athena returns zero rows with projectionTemplate 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 tablesWrong path, exclusions too broad, or no read/decrypt permissionCheck the crawler log; test s3:GetObject and kms:Decrypt as the crawler role
Crawler creates dozens of tablesSchemas differ between folders, or table level wrongSet TableLevelConfiguration; fix inconsistent files
HIVE_PARTITION_SCHEMA_MISMATCHPartition columns differ from table columnsUse InheritFromTable; update or recreate the partitions
CRAWL_NEW_FOLDERS_ONLY rejected on createRequires UpdateBehavior=LOGSet the schema change policy to LOG
Crawler typed CSV amount as doubleInference from textDefine the table yourself with decimal(18,2)
AccessDeniedException on glue:CreatePartition from the jobJob 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_core yourself with OpenCSVSerde, skip.header.line.count, all columns as string (the raw landing shape), and projection on source (enum type with values core,sftp,api) and ingest_date (date type). 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

Knowledge check

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

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 LOG schema-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.

On this page