Building a Data Lake Module 5: Cataloging and Querying with Glue and Athena

Step 1: Understand the catalog

The Data Catalog holds databases and tables that describe data in S3: columns, types, format, location, and partitions. The data stays in S3. Athena, Glue jobs, Amazon EMR, and Redshift Spectrum all read the same catalog, so you define a table once and use it from many tools.

Step 2: Create a database and a table in Athena

In the Athena query editor, set a query results location in S3 first. Then create a database and an external table that points at your Parquet files. External means dropping the table does not delete the files.

CREATE DATABASE IF NOT EXISTS sales_lake;

CREATE EXTERNAL TABLE sales_lake.orders (
  order_id    bigint,
  customer_id bigint,
  amount      double
)
PARTITIONED BY (order_date date)
STORED AS PARQUET
LOCATION 's3://acme-datalake-clean-dev/sales/orders/';

Step 3: Load the partitions

A partitioned table needs to know which partitions exist. Run MSCK REPAIR TABLE to discover Hive-style folders, or use a Glue crawler (next module) or partition projection, which computes partitions from a pattern without registering each one.

MSCK REPAIR TABLE sales_lake.orders;

SHOW PARTITIONS sales_lake.orders;

Step 4: Query efficiently

Athena bills by data scanned, so select only the columns you need and filter on the partition column. SELECT star on a large Parquet table reads every column. Preview with LIMIT, and check the data-scanned figure shown after each query.

SELECT order_date, count(*) AS orders, sum(amount) AS revenue
FROM sales_lake.orders
WHERE order_date BETWEEN date '2026-09-01' AND date '2026-09-07'
GROUP BY order_date
ORDER BY order_date;

Step 5: Create curated tables with CTAS

CREATE TABLE AS SELECT writes the result of a query as new files and registers the table in one statement, which is a quick way to build curated datasets. Choose Parquet, compression, and partitioning in the table properties.

CREATE TABLE sales_lake.daily_revenue
WITH (
  format = 'PARQUET',
  write_compression = 'SNAPPY',
  external_location = 's3://acme-datalake-curated-dev/sales/daily_revenue/'
) AS
SELECT order_date, count(*) AS orders, sum(amount) AS revenue
FROM sales_lake.orders
GROUP BY order_date;