Athena, Glue, Redshift, and Data Lake Foundations

A data lake foundation lets teams store raw and curated files once, describe them consistently, and query them with the right engine for each workload. In this lesson, the foundation is not a single product. It is the combination of Amazon S3 for durable object storage, AWS Glue Data Catalog for table metadata, Amazon Athena for serverless SQL over files, and Amazon Redshift for managed warehouse analytics.

The practical outcome is a small but realistic pattern: land files in S3, organize them by dataset and partition, register table definitions in Glue, query those files from Athena, and understand when Redshift should query the lake directly or load selected data into warehouse tables. This fits the AWS Cloud Engineering course because analytics systems are usually built from several managed services whose boundaries, costs, and failure modes must be understood together.

How the Pieces Fit

S3 stores objects in buckets. A data lake normally uses prefixes to separate zones such as raw/, curated/, and analytics/. S3 does not know that a group of objects is a table. It only stores keys, bytes, metadata, versions if enabled, and access controls. The table meaning comes from a catalog.

The Glue Data Catalog is that catalog. A Glue database is a namespace. A Glue table describes where data lives in S3, which SerDe or file format reads it, which columns exist, and which columns are partitions. Glue crawlers can infer this metadata by scanning files, but production systems often create or update catalog definitions deliberately because inference can drift when a malformed file appears.

Athena uses the Trino query engine to run SQL without provisioning servers. When a query references a Glue table, Athena reads the table location and schema, plans which S3 objects must be scanned, reads the files, applies predicates and aggregations, and writes query results to a configured S3 results location. Athena charges mainly by bytes scanned, so file format, compression, projection, and partition pruning are core design concerns.

Redshift is different. It is a warehouse engine optimized for repeated analytical workloads, joins, materialized views, and governed reporting. Redshift can load lake data with COPY, or it can query S3 through Redshift Spectrum using external schemas backed by Glue. Loading data improves predictable performance for hot datasets. Spectrum keeps data in the lake and is useful for occasional or broad lake queries.

Metadata and Query Anatomy

A lake table definition has four important parts. The first is location, such as s3://example-lake/curated/orders/. The second is schema, which maps fields to types like string, bigint, double, or timestamp. The third is storage format. CSV is simple but expensive and fragile for typed analytics. Parquet is columnar, compressible, and allows engines to skip unrelated columns. The fourth is partitioning, where values such as year=2026/month=09 appear in the S3 key path and let the engine skip whole prefixes.

Partitioning is powerful but easy to misuse. A query with WHERE year = 2026 AND month = 9 can avoid scanning other months if those are partition columns. A query filtering only on a nonpartitioned timestamp may still scan many files. High-cardinality partitions, such as one partition per user, create too much catalog and file-list overhead. Low-cardinality partitions, such as year only, may not prune enough data.

Glue jobs add another layer. A Glue ETL job can read from S3, transform data with Spark, and write cleaned files back to S3. The job may also update the Data Catalog. Glue is appropriate when files need conversion, enrichment, compaction, or schema normalization before analysts query them. Athena is better for ad hoc SQL over existing data. Redshift is better when many dashboards repeatedly query curated, modeled data.

Example 1: Register a Small CSV Table

This first example lands a tiny orders file in S3 and creates a Glue-backed Athena table over it. The deterministic data contains two orders, so after the table is repaired or the partition is registered, a query for September 2026 should count 2 rows and total 125.49.

set -euo pipefail

: "${BUCKET:?Set BUCKET to an existing S3 bucket}"
RESULTS="s3://$BUCKET/athena-results/"
DATA="s3://$BUCKET/lake/curated/orders/year=2026/month=09/orders.csv"

printf 'order_id,customer_id,total\n1001,c-7,42.50\n1002,c-9,82.99\n' | aws s3 cp - "$DATA"

aws glue create-database \
  --database-input '{"Name":"retail_lake"}' \
  >/dev/null 2>&1 || true

aws athena start-query-execution \
  --query-execution-context Database=retail_lake \
  --result-configuration OutputLocation="$RESULTS" \
  --query-string "CREATE EXTERNAL TABLE IF NOT EXISTS orders_csv (order_id bigint, customer_id string, total double) PARTITIONED BY (year int, month int) ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde' WITH SERDEPROPERTIES ('skip.header.line.count'='1') LOCATION 's3://$BUCKET/lake/curated/orders/'"

The command returns a query execution identifier, not the final result, because Athena runs asynchronously. Use the Athena console or get-query-execution until the state is SUCCEEDED. The table exists even though the partition still needs to be discovered or added. That separation is important: Glue can know the table shape before it knows each partition value.

Example 2: Add Partitions and Query Efficiently

The next step teaches partition awareness. MSCK REPAIR TABLE scans the table location for Hive-style partition paths such as year=2026/month=09 and adds them to the catalog. For large production tables, explicit partition registration or partition projection is often better because repair can be slow over many prefixes.

set -euo pipefail

: "${BUCKET:?Set BUCKET to an existing S3 bucket}"
RESULTS="s3://$BUCKET/athena-results/"

aws athena start-query-execution \
  --query-execution-context Database=retail_lake \
  --result-configuration OutputLocation="$RESULTS" \
  --query-string "MSCK REPAIR TABLE orders_csv"

aws athena start-query-execution \
  --query-execution-context Database=retail_lake \
  --result-configuration OutputLocation="$RESULTS" \
  --query-string "SELECT count(*) AS orders, round(sum(total), 2) AS revenue FROM orders_csv WHERE year = 2026 AND month = 9"

When the second query succeeds, the expected row contains orders = 2 and revenue = 125.49. If the query omits the partition filter, Athena can still return the same answer in this tiny dataset, but the plan may scan more data as the table grows. This is the central Athena cost lesson: correct SQL and efficient SQL are not automatically the same.

Example 3: Query the Lake from Redshift

Redshift Spectrum lets a warehouse join local warehouse tables with external lake tables. The external schema points Redshift at the Glue catalog. The IAM role attached to the Redshift cluster or workgroup must allow reading the S3 data and Glue metadata. The expected query result for the sample data is again two orders and 125.49 revenue, but execution happens through Redshift SQL.

set -euo pipefail

: "${REDSHIFT_HOST:?Set REDSHIFT_HOST}"
: "${REDSHIFT_DB:?Set REDSHIFT_DB}"
: "${REDSHIFT_USER:?Set REDSHIFT_USER}"
: "${REDSHIFT_IAM_ROLE_ARN:?Set REDSHIFT_IAM_ROLE_ARN}"
: "${AWS_REGION:?Set AWS_REGION}"

psql "host=$REDSHIFT_HOST dbname=$REDSHIFT_DB user=$REDSHIFT_USER sslmode=require" <<'SQL'
CREATE EXTERNAL SCHEMA IF NOT EXISTS lake
FROM DATA CATALOG
DATABASE 'retail_lake'
IAM_ROLE :'REDSHIFT_IAM_ROLE_ARN'
REGION :'AWS_REGION';

SELECT count(*) AS orders, round(sum(total), 2) AS revenue
FROM lake.orders_csv
WHERE year = 2026 AND month = 9;
SQL

This pattern is useful when analysts already live in Redshift and need occasional lake access. If the query becomes part of a frequent dashboard, consider loading curated data into Redshift tables, choosing distribution and sort keys for the access pattern, and refreshing on a controlled schedule.

Design Choices and Trade-Offs

Choose CSV only for interchange, inspection, or very small datasets. Choose Parquet for most curated lake tables because column pruning and compression reduce Athena scans and speed Redshift Spectrum reads. Small files are a common hidden cost: thousands of tiny objects make planning slower and reduce scan efficiency. Glue ETL or another compaction process can combine small files into larger Parquet objects.

Use crawlers for discovery and early exploration, but prefer managed schema changes for critical datasets. A crawler may infer a column differently when a new file contains unexpected values. Deliberate schema management makes breaking changes reviewable. For evolving data, additive nullable columns are easier than renames or type narrowing.

Athena is excellent for ad hoc questions and low-administration access to lake data. It is not a replacement for every warehouse workload. Redshift is better for predictable concurrency, dimensional models, workload management, and repeated joins across curated data. The clean architecture is often both: Athena for exploration and lake validation, Redshift for served analytics.

Failure Modes and Troubleshooting

Symptom: Athena returns zero rows even though files exist. Cause: the table was created, but partitions were not added to Glue, or files were placed under a path that does not match partition names. Diagnose: run SHOW PARTITIONS orders_csv and list the S3 prefix. Correct: run MSCK REPAIR TABLE, add partitions explicitly, or move objects under the expected key=value paths.

Symptom: a query fails with access denied. Cause: the Athena caller, Glue job role, or Redshift role lacks S3, Glue, KMS, or result-bucket permissions. Diagnose: check the failing principal in CloudTrail and confirm whether the denied action is against the data bucket, result bucket, catalog, or KMS key. Correct: grant the narrow missing permission and test again with the same role.

Symptom: Athena scans far more bytes than expected. Cause: partition filters are missing, data is stored as uncompressed CSV, or many small files force broad reads. Diagnose: inspect query statistics, S3 layout, file format, and predicates. Correct: add partition predicates, convert curated data to Parquet, compact files, and avoid selecting unused columns.

Symptom: Redshift Spectrum reports a schema or type error. Cause: the Glue table definition no longer matches the files. Diagnose: sample the S3 objects, inspect the Glue column types, and isolate the newest partition. Correct: repair the malformed partition, create a compatible table version, or transform data into a stable curated schema.

Security, Performance, and Reliability

Separate raw and curated prefixes and apply least-privilege IAM to each zone. Analysts may need read access to curated data but not raw files containing sensitive payloads. If buckets use SSE-KMS, the query role also needs permission to use the key. S3 bucket policies, Lake Formation permissions, Glue catalog access, and IAM policies can all participate, so troubleshoot authorization by identifying the exact principal and resource.

Performance starts with layout. Partition on fields commonly used to remove large amounts of data, not fields that create millions of tiny partitions. Prefer columnar formats for curated analytics. Configure Athena result locations deliberately and clean old results with lifecycle rules. For Redshift, decide whether external access is enough or whether hot data should be loaded, sorted, compressed, and modeled inside the warehouse.

Reliability depends on reproducible metadata and recoverable data. Version important S3 objects where deletion risk matters. Treat catalog changes as deployable artifacts when possible. Keep raw immutable inputs long enough to rebuild curated tables after a bad transform. For Glue jobs, capture failed record locations and make reruns idempotent so a retry does not duplicate output.

Hands-On Lab

Prerequisites: an AWS account, AWS CLI configured to a sandbox account, permission to create Glue databases and Athena queries, an existing S3 bucket, and a region where Athena is available. Set BUCKET to a bucket you can write to. Avoid using production buckets for this lab.

  1. Create the sample CSV object and Athena table from Example 1.
  2. Wait until the create-table query reaches SUCCEEDED.
  3. Run the partition repair and aggregate query from Example 2.
  4. Open the Athena query results and verify that the aggregate row shows two orders and total revenue of 125.49.
  5. Run the same aggregate without the month predicate and compare bytes scanned in the query statistics.
  6. If you have a Redshift test environment and an appropriate IAM role, run Example 3 and verify the same aggregate through Redshift.

Cleanup: drop the Athena table, delete the Glue database if it contains only lab objects, and remove the lab S3 prefixes for lake/curated/orders/ and athena-results/. If Redshift external schema was created only for this lab, drop it from the test database.

Assessment Exercises

  1. A table has daily queries that always filter by customer and date. Which field would you partition by first, and what risk appears if customer is used as a partition key?
  2. An Athena query over a small CSV table is correct but expensive after six months of growth. Propose two layout changes and explain why each reduces scan cost.
  3. A Glue crawler changes a column from numeric to string after new files arrive. Explain how this can affect Athena and Redshift Spectrum, and propose a safer schema workflow.
  4. A dashboard reads the same curated lake data every five minutes through Redshift Spectrum. When would you keep Spectrum, and when would you load the data into Redshift tables?
  5. Design a recovery plan for a Glue job that wrote malformed Parquet files to one partition but left raw input intact.

Summary

AWS data lake foundations work by separating storage, metadata, and compute. S3 holds the files, Glue describes them, Athena queries them serverlessly, and Redshift serves warehouse-grade analytics or reaches into the lake through Spectrum. The main engineering decisions are file format, partition layout, schema control, permission boundaries, and which engine should own each workload. A dependable design keeps raw inputs recoverable, curated tables efficient, metadata deliberate, and query roles narrowly scoped.