Build a Serverless Data Lake Pipeline

Ingest, catalog, transform, and query data using S3, Glue, and Athena.

Introduction

30 Second Summary

Every hospital visit, insurance claim, and medical bill generates data. Raw data sitting in scattered files is useless until someone cleans it, organizes it, and makes it searchable.

In this project, you will build a serverless data lake pipeline on AWS using a real Medicare hospital dataset. You'll use S3, AWS Glue, and Amazon Athena to go from a messy CSV file to optimized, queryable data.

What You'll Build

You'll run the same SQL query against raw CSV data and transformed Parquet data side by side, then watch the amount of data scanned shrink dramatically.

By the end of this project, you'll have:

A Glue Crawler that automatically discovers the schema of a raw healthcare dataset in S3, making it instantly queryable without any manual column definitions.
A Glue ETL job that cleans raw CSV data and converts it to compressed Parquet, the industry-standard columnar format for analytics.
A side-by-side performance comparison in Athena proving that your Parquet pipeline slashes the amount of data scanned compared to querying raw CSV.
Secret Mission: Add partitioning to your Parquet output for real-world query optimization.

Are there any prerequisites?

You'll need an AWS account (Free Tier or paid), basic SQL knowledge, and basic Python familiarity. No local tools are required. Everything runs in the AWS Management Console.

Before We Start

You are about to build a real data engineering pipeline on AWS. Before diving in, take a moment to commit to what you are building and why it matters for your career.

Set Up Your S3 Buckets and IAM Role

Every data lake starts with a place to store data. Before you can catalog, transform, or query anything, you need dedicated storage to separate your raw inputs from your processed outputs.

In this step, you'll create three Amazon S3 buckets and an IAM role. The buckets hold your raw data, transformed data, and query results. The role gives AWS Glue permission to access those buckets on your behalf.

In this step, get ready to:

Create three S3 buckets for your data lake pipeline.
Create an IAM role that grants AWS Glue access to your buckets.
Upload a public Medicare dataset to your raw data bucket.

Create your S3 buckets

S3 buckets are the foundation of any data lake on AWS. You'll create three: one for raw incoming data, one for transformed output, and one for Amazon Athena query results.

Sign in to the AWS Management Console.
In the search bar at the top of the page, type S3.
Select S3 from the search results.
Click Create bucket.

In the Bucket name field, enter a unique name following this pattern: yourname-datalake-raw. Replace yourname with something unique to you, like your first name or initials.
For AWS Region, select US East (N. Virginia) us-east-1.
Leave all other settings as their defaults.
Scroll down and click Create bucket.

Remember your unique prefix

S3 bucket names must be globally unique across all AWS accounts. If you see a "Bucket already exists" error, try a more specific prefix like your full name or name plus a number.

Your first bucket appears in the bucket list. This is your raw data landing zone.

Click Create bucket again.
Enter yourname-datalake-transformed as the bucket name, using the same unique prefix you chose earlier.
Select the same region: US East (N. Virginia) us-east-1.
Leave all other settings as defaults and click Create bucket.

Click Create bucket one more time.
Enter yourname-athena-results as the bucket name, using the same unique prefix.
Select the same region: US East (N. Virginia) us-east-1.
Leave all other settings as defaults and click Create bucket.

You should now see all three buckets listed on the S3 Buckets page.

Why three separate buckets?

Separating raw and transformed data is a data lake best practice. Your raw bucket holds the original, unmodified data. Your transformed bucket holds the cleaned and optimized version. Keeping them apart means you can always re-process from the original source if something goes wrong.

The third bucket stores Athena query results. Athena requires a dedicated output location for every query it runs.

Don't see all three buckets?

Make sure you clicked Create bucket (the orange button at the bottom of the page) after filling in each bucket's details. If you see a "Bucket already exists" error, try a more specific prefix.

Help me figure out why my S3 buckets didn't create successfully.

Create an IAM role for AWS Glue

AWS Glue needs permission to read from and write to your S3 buckets. An IAM role defines exactly what actions a service can perform on your behalf.

In the search bar at the top of the page, type IAM.
Select IAM from the search results.
In the left navigation menu, click Roles.
Click Create role.

For Trusted entity type, select AWS service.
Under Use case, search for or scroll to Glue and select it.
Click Next.

In the permissions search bar, type AWSGlueServiceRole.
Select the checkbox next to the AWSGlueServiceRole policy.
Clear the search bar.
Type AmazonS3FullAccess.
Select the checkbox next to the AmazonS3FullAccess policy.
Click Next.

In the Role name field, enter AWSGlueServiceRole-DataLake.
Scroll down and click Create role.

You should see a success banner confirming the role was created.

In the Roles list, search for AWSGlueServiceRole-DataLake and click on it.
Under Permissions policies, confirm that both AWSGlueServiceRole and AmazonS3FullAccess are listed.

What is an IAM role?

An IAM role is like a set of keys you hand to an AWS service. Instead of giving AWS Glue your personal credentials, you create a role that says "Glue is allowed to read and write to S3." AWS Glue assumes this role whenever it runs a job on your behalf.

Can't find the Glue use case?

Make sure you selected AWS service as the trusted entity type first. The Glue option only appears under AWS service use cases.

Help me find the Glue option when creating an IAM role.

Upload the raw dataset to S3

Your buckets are ready, but they're empty. You'll now copy a public Medicare hospital provider dataset into your raw bucket. This CSV file contains hospital charge data that your pipeline will process in later steps.

In the AWS Console, click the CloudShell icon on the navigation bar at the top of the page. It looks like a small terminal prompt.
Wait for AWS CloudShell to initialize. This may take a few seconds on first use.

Copy the Medicare dataset from the public AWS bucket into your raw bucket by running this command:

aws s3 cp s3://awsglue-datasets/examples/medicare/Medicare_Hospital_Provider.csv s3://[[RAW_BUCKET="yourname-datalake-raw"]]/

What does this command do?

The aws s3 cp command copies a file between S3 locations. The source is a public AWS bucket that hosts sample datasets for Glue tutorials. The destination is your raw data bucket.

The file copies directly between buckets without downloading to your local machine. CloudShell already has the AWS CLI installed and authenticated with your account credentials.

Before you check your bucket, what do you expect to see inside it?

Navigate back to S3 using the search bar at the top.
Click on your raw bucket (the one ending in -datalake-raw).

You should see Medicare_Hospital_Provider.csv listed as an object in the bucket. This is the raw data your pipeline will process.

Don't see the file in your bucket?

Make sure you replaced the bucket name in the command with your actual bucket name (the one ending in -datalake-raw). Check that the command completed successfully in CloudShell. You should see a line confirming the copy operation.

Also confirm your bucket is in the us-east-1 region. The public dataset bucket is hosted in that region.

Help me figure out why the Medicare CSV didn't copy to my S3 bucket.

Your storage and permissions are in place. Next, you'll point AWS Glue at your raw data to automatically discover what's inside it.

Crawl Your Data and Discover the Schema

Your S3 buckets are ready and your raw Medicare CSV is uploaded. The next challenge is making that data understandable to other AWS services.

Right now, no service knows what columns or data types your CSV contains. In this step, you'll create an AWS Glue Crawler that automatically scans your data, discovers its structure, and registers the schema in the Data Catalog.

In this step, get ready to:

Create a Glue database and a Crawler to scan your raw data.
Run the Crawler and confirm it completes successfully.
Explore the inferred schema in the Glue Data Catalog.

Create a Glue database and Crawler

Before the Crawler can store its findings, it needs a database in the Glue Data Catalog to write to. You'll create the database first, then configure the Crawler.

Search for Glue in the AWS console search bar and open the AWS Glue console.
In the left menu under Data Catalog, choose Databases.
Choose Add database.
Enter datalake-raw as the database name.
Choose Create database.

You should see datalake-raw listed in your databases.

What is the Glue Data Catalog?

The Glue Data Catalog is a centralized metadata repository. It stores table definitions, column names, data types, and file locations, but not the actual data itself.

When services like Athena query your data, they read from the Data Catalog to understand how to interpret the raw files sitting in S3.

Now you'll create the Crawler that scans the CSV in your raw bucket and writes its schema to this database.

In the left menu, choose Crawlers.
Choose Create crawler.
Enter medicare-raw-crawler as the crawler name.
Choose Next.

The wizard advances to the data source configuration.

For Is your data already mapped to AWS Glue tables?, leave Not yet selected.
Choose Add a data source.
For Data source, select S3.
For S3 path, browse to and select your yourname-datalake-raw bucket.
Choose Add an S3 data source.
Choose Next.

What does the Crawler do with this S3 path?

The Crawler scans every file at this S3 path and infers the structure of your data. It detects column names, data types, and file format automatically.

All of that metadata gets written to the Data Catalog as a table definition. This is what makes your raw CSV queryable by other services.

For IAM role, select AWSGlueServiceRole-DataLake from the dropdown.
Choose Next.
For Target database, select datalake-raw.
Leave the Table name prefix field empty.
Choose Next.
Review your crawler settings on the summary page.
Choose Create crawler.

You should see medicare-raw-crawler listed on the Crawlers page.

Don't see your IAM role in the dropdown?

Make sure you created the IAM role with Glue selected as the trusted entity type. If you chose a different service, the role won't appear in the Glue console.

Also confirm that your IAM role and S3 buckets are in the same AWS Region.

Help me troubleshoot my Glue Crawler setup.

Run the Crawler

The Crawler is configured but hasn't scanned anything yet. You need to run it so it reads your CSV, infers column names and data types, and writes the results to the Data Catalog.

Select your medicare-raw-crawler by clicking its checkbox.
Choose Run.

The Crawler takes about 1 minute to scan the CSV and infer the schema. You can monitor its progress in the Status column.

Wait for the Status column to show Completed. Refresh the page if the status hasn't updated.

Crawler showing a failed status?

Check that your IAM role AWSGlueServiceRole-DataLake has both the AWSGlueServiceRole and AmazonS3FullAccess policies attached.

Confirm that your S3 bucket and Glue resources are in the same AWS Region. A region mismatch causes the Crawler to fail when trying to reach the bucket.

Help me fix my failed Glue Crawler.

Explore the Data Catalog

Your Crawler has finished scanning. It found columns and data types in your CSV and registered them as a table in the Data Catalog.

In the left menu under Data Catalog, choose Tables.
Click on the table the Crawler created. The table name is based on your bucket name or file name (for example, medicare_hospital_provider_csv).

Before scrolling down, take a guess: what columns would you expect to find in a Medicare hospital provider dataset?

Scroll down to the Schema section.

You should see a full list of columns with inferred data types. Look for columns like drg_definition, provider_id, provider_name, provider_state, total_discharges, and average_total_payments.

What did the Crawler actually do?

The Crawler read the CSV's header row and sampled the data to infer a data type for each column. It then wrote all of this as a table definition in the Data Catalog.

This metadata is what makes your raw CSV queryable. Without it, services like Athena would have no way to interpret the raw bytes in your S3 file.

Your data is cataloged and its schema is registered. Next up, you'll use Athena to run SQL queries directly on your S3 data, no servers required.

Query the Raw Data with Athena

Your Glue Crawler scanned the raw CSV in S3 and built a full table definition in the Data Catalog. You now have column names, data types, and a queryable table without writing a single schema definition yourself.

With the data cataloged, you can query it directly using Amazon Athena and standard SQL. No servers to spin up, no data to load into a database. But you will also spot an important limitation of querying raw CSV files that will motivate the next step.

In this step, get ready to:

Configure Athena to store query results in your S3 bucket.
Run a SQL query on the raw Medicare dataset and observe how much data Athena scans.

Configure Athena query results location

Before Athena can run any query, it needs an S3 location to store the output files. You already created a bucket for this in Step 1.

In the AWS Management Console, search for Athena and open the Athena console.
If this is your first time visiting the Athena console, choose Explore the query editor to open the query editor.
Choose Edit Settings to configure a query result location.
In the Location of query result box, enter the S3 path to your Athena results bucket:

s3://[[ATHENA_RESULTS_BUCKET="yourname-athena-results"]]/

Choose Save.
Choose Editor to switch back to the query editor.

What is Amazon Athena?

Athena is a serverless query service that lets you run SQL directly against data in S3. You do not need to provision any infrastructure or load data into a separate database.

Athena reads table definitions from the Glue Data Catalog, which is why the crawler you ran in Step 2 matters. Without that catalog entry, Athena would have no idea what columns your CSV contains.

Don't see the Edit Settings option?

If you have used Athena before in this Region, the query editor opens directly. In that case, choose the Settings tab in the navigation bar, then choose Manage to reach the same settings page.

Still stuck? Help me configure Athena query results location

Run your first query on the raw data

Now point Athena at the table your crawler created and run a real SQL query against the Medicare dataset.

In the Athena query editor, find the Data source dropdown on the left and choose AwsDataCatalog.
In the Database dropdown below it, choose datalake-raw.

You should see your medicare_hospital_provider_csv table appear in the Tables list on the left. This is the table your crawler created from the raw CSV file.

Your table name may differ

The crawler auto-generates the table name based on the S3 bucket or file name. If your table name is slightly different from medicare_hospital_provider_csv, use your actual table name in the query below.

Paste the following SQL query into the query editor:

SELECT drg_definition, provider_name, provider_state, average_total_payments
FROM medicare_hospital_provider_csv
WHERE provider_state = 'CA'
ORDER BY average_total_payments DESC
LIMIT 10;

What does this query do?

This query pulls 4 columns from the Medicare dataset: the diagnosis group, hospital name, state, and average total payment. It filters for hospitals in California, sorts by the highest payments first, and returns only the top 10 results.

Before you run this query, think about how much data Athena will need to read. You only asked for 4 columns out of 10, and only rows in California. Do you think Athena will scan just those 4 columns, or the entire CSV file?

Choose Run.

The results appear in the Results pane below the query editor. You can see hospital charges in California sorted from highest to lowest.

Now look at the Data scanned value shown beneath the query results. Make a note of this number.

The Data scanned value is around 12-14 MB. That is the full size of the entire CSV file. Even though you only asked for 4 columns and a small subset of rows, Athena had to read every single byte of the file.

Why did Athena scan the whole file?

CSV is a row-based format. Every row contains all columns packed together in a single line. To read just the 4 columns you requested, Athena still has to parse every row and discard the columns it does not need.

Athena charges per TB of data scanned. At petabyte scale, scanning entire files for every query becomes extremely expensive. This is the core problem that the next step solves.

Query failed or no results?

Check that the Database dropdown is set to datalake-raw, not default. Also verify the table name in your query matches the table name shown in the left panel under Tables.

If you see a permissions error, make sure your Athena query results location is set correctly. Return to Settings and confirm the S3 path points to your yourname-athena-results bucket.

Need more help? Help me troubleshoot my Athena query

You just proved that querying raw CSV forces Athena to scan everything. Next up, you will build a Glue ETL job that converts this CSV into Parquet format, a columnar file type that lets Athena read only the columns it needs.

Build a Glue ETL Job to Transform Your Data

In the last step, you queried the raw CSV with Athena and noticed a problem. Every query scanned the entire file, even when you only needed a few columns. At production scale, this wastes money and slows everything down.

The fix is to transform your data into a columnar format called Parquet. You will build an AWS Glue ETL job that reads the cataloged CSV, removes rows with missing values, selects only the most useful columns, and writes the output as compressed Parquet files.

In this step, get ready to:

Create and configure a Glue ETL job with a PySpark script.
Run the job and verify Parquet files appear in your transformed bucket.
Crawl the transformed data so Athena can query it.

Create a new Glue ETL job

AWS Glue's script editor lets you write your own PySpark code to define exactly how your data gets transformed. You will create a new job, paste in your ETL script, and configure the job settings.

In the AWS Glue console, choose ETL jobs in the left menu.
In the Create job section, choose Script editor.
For Engine, make sure Spark is selected.
Choose Create script.

The script editor opens with some default boilerplate code. You will replace all of it with your own ETL script.

Select all of the default code in the editor and delete it.
Paste the following imports and job initialization code into the empty editor:

import sys
from awsglue.transforms import *
from awsglue.utils import getResolvedOptions
from pyspark.context import SparkContext
from awsglue.context import GlueContext
from awsglue.job import Job

args = getResolvedOptions(sys.argv, ["JOB_NAME"])
sc = SparkContext()
glueContext = GlueContext(sc)
spark = glueContext.spark_session
job = Job(glueContext)
job.init(args["JOB_NAME"], args)

What does this code do?

This block handles all the setup that every Glue job needs before it can process data.

The import lines pull in the Glue and Spark libraries your script depends on.
getResolvedOptions reads the job name that Glue passes in when it starts the job.
GlueContext and Job wrap the Spark engine so Glue can manage it for you.

Below the initialization code you just pasted, add the data reading and cleaning logic:

# Read from the Glue Data Catalog (the table created by the crawler)
datasource = glueContext.create_dynamic_frame.from_catalog(
    database="datalake-raw",
    table_name="medicare_hospital_provider_csv",
    transformation_ctx="datasource"
)

# Convert to Spark DataFrame for transformations
df = datasource.toDF()

# Drop rows with null values in critical columns
df_cleaned = df.dropna(subset=[
    "drg_definition",
    "provider_id",
    "provider_name",
    "provider_state",
    "total_discharges",
    "average_covered_charges",
    "average_total_payments"
])

What does this code do?

This section reads the raw data using the Data Catalog table your crawler built in Step 2, then cleans it.

create_dynamic_frame.from_catalog reads the CSV from S3 using the schema the crawler discovered. No manual column definitions needed.
toDF() converts the Glue DynamicFrame into a standard Spark DataFrame so you can use familiar PySpark operations.
dropna removes any rows where critical columns have missing values. This keeps your analytics data trustworthy.

Below the cleaning code, add the column selection and Parquet write logic:

# Select the most useful columns for analysis
df_selected = df_cleaned.select(
    "drg_definition",
    "provider_id",
    "provider_name",
    "provider_city",
    "provider_state",
    "provider_zip_code",
    "total_discharges",
    "average_covered_charges",
    "average_total_payments",
    "average_medicare_payments"
)

# Write as compressed Parquet to the transformed bucket
df_selected.write.mode("overwrite").format("parquet").save(
    "s3://[[TRANSFORMED_BUCKET=\"yourname-datalake-transformed\"]]/"
)

job.commit()

What does this code do?

This final section narrows the data to the 10 most useful columns and writes the result.

select() keeps only the columns you want for analysis, dropping everything else. Fewer columns means smaller files and faster queries.
write.format("parquet") outputs the data as Parquet, a columnar format. Spark uses Snappy compression by default, so the output files will be named .snappy.parquet.
job.commit() tells Glue the job finished successfully.

Make sure to replace the bucket name in the S3 path with the actual name of your transformed bucket from Step 1.

Why use the Spark DataFrame API instead of Glue's DynamicFrame?

Glue's DynamicFrame has its own write methods, but the Spark DataFrame API is what data engineers use across the industry. The toDF(), dropna(), select(), and write.parquet() calls you just wrote work in any Spark environment, not just Glue.

Now configure the job settings so Glue knows how to run your script.

Choose the Job details tab at the top of the editor.
For Name, enter medicare-csv-to-parquet.
For IAM Role, choose AWSGlueServiceRole-DataLake (the role you created in Step 1).
For Glue version, choose Glue 4.0.
For Worker type, choose G.1X.
For Number of workers, enter 2 (the minimum).
Choose Save.

What are DPUs and workers?

A Glue worker is a unit of compute that runs your script. G.1X means each worker has 1 DPU (Data Processing Unit), which provides 4 vCPUs and 16 GB of memory. Two workers is the minimum for a Spark job.

Glue charges per DPU-second. For this small dataset, the job runs for about 2 minutes on 2 workers, costing roughly $0.03.

✔️ Awesome, I've got everything!

Great. Double check you saved the job before moving on.

ⓧ I'd like to double check the full code

Your script is built from three chunks. Scroll up and confirm each one is present in your script editor, in this order:

Chunk 1: Imports and job initialization (import sys through job.init(...)).
Chunk 2: Data reading and cleaning (create_dynamic_frame.from_catalog through dropna(...)).
Chunk 3: Column selection, Parquet write, and job commit (df_cleaned.select(...) through job.commit()).

Make sure there are no extra blank lines between chunks and that the S3 path points to your own transformed bucket.

Run the ETL job

Your script is saved and configured. Time to run it and see Parquet files appear in your transformed bucket.

Choose Run at the top of the job editor.
Scroll down to the Runs tab at the bottom of the page to monitor the job's progress.
Wait for the status to show Succeeded. The job takes about 2-3 minutes. Refresh the page if the status does not update automatically.

Before you check S3, what do you expect to see in your transformed bucket? Think about the file format and compression the script uses.

Open the S3 console and navigate to your yourname-datalake-transformed bucket.

You should see one or more .snappy.parquet files inside the bucket. Spark splits the output across multiple files for parallel processing. The Snappy compression keeps the files small.

Job failed or bucket is still empty?

Check the Runs tab for an error message. The most common cause is a typo in the S3 bucket name in your script. Make sure it matches your actual bucket name exactly.
Verify that the IAM role AWSGlueServiceRole-DataLake is selected in the Job details tab. Without the right role, Glue cannot read from or write to S3.
Confirm that the table name in your script (medicare_hospital_provider_csv) matches the table name shown in your Data Catalog. If the crawler created a slightly different name, update the script to match.

Still stuck? Help me debug my Glue ETL job failure.

Crawl the transformed data

Your Parquet files are in S3, but Athena does not know about them yet. You need a second crawler to scan the Parquet files and register a new table in the Data Catalog.

Return to the Glue console. In the left menu, choose Databases under Data Catalog.
Choose Add database.
Enter datalake-transformed as the database name.
Choose Create database.

Now create a crawler to scan the Parquet files in your transformed bucket.

In the left menu, choose Crawlers.
Choose Create crawler.
For the name, enter medicare-parquet-crawler. Choose Next.
Choose Not yet for whether data is already mapped to Glue tables. Choose Add a data source.
For Data source, choose S3. For S3 path, browse to your yourname-datalake-transformed bucket. Choose Add an S3 data source.
Choose Next.
For IAM role, choose AWSGlueServiceRole-DataLake. Choose Next.
For Target database, choose datalake-transformed. Leave the table prefix empty. Choose Next.
Review your settings and choose Create crawler.

Select your medicare-parquet-crawler and choose Run.
Wait for the status to show Completed. This takes about 1 minute. Refresh the page if needed.

What do you expect the crawler to find? Think about how the format of the data has changed since the first crawler ran.

In the left menu, choose Tables under Data Catalog.
Click on the table the crawler created in the datalake-transformed database.

You should see a new table with the same 10 columns you selected in your ETL script. Notice that the crawler detected the Parquet format automatically.

Don't see a table in datalake-transformed?

Make sure the crawler's S3 path points to the root of your transformed bucket, not a subfolder.
Confirm the crawler's target database is set to datalake-transformed, not datalake-raw.
Verify the Parquet files actually exist in the transformed bucket. If the bucket is empty, the ETL job may not have completed successfully.

Need more help? Help me troubleshoot my Parquet crawler.

Your raw CSV data has been cleaned, filtered, and converted to optimized Parquet. In the next step, you will run the same query against both tables and see the dramatic difference in how much data Athena scans.

Compare CSV vs Parquet Query Performance

Your Glue ETL job cleaned the Medicare data and converted it to compressed Parquet format. The datalake-transformed database now has a table ready for querying.

This step is the payoff. You will run the exact same SQL query against both the CSV and Parquet tables, then compare how much data Athena scans for each. The difference proves why Parquet is the industry standard for analytics workloads.

In this step, get ready to:

Query the Parquet table in Athena and record the data scanned metric.
Re-run the CSV query and compare scan metrics side by side.

Query your transformed Parquet table

The Parquet table in datalake-transformed holds the same data as the CSV, but stored in a columnar format. Athena can read only the columns your query asks for instead of scanning the entire file.

Return to the Athena query editor in the AWS Management Console.
For Data source, confirm AwsDataCatalog is selected.
For Database, choose datalake-transformed.
Under Tables, you should see the Parquet table created by your crawler. Note its exact name.

Before you run the next query, think about this: will Athena scan more data, the same amount, or less data compared to the ~12-14 MB it scanned for the CSV query in Step 3?

Run the same query you used in Step 3, but this time against your Parquet table. Paste the following into the query editor:

-- Query the Parquet table for California hospital payments
SELECT drg_definition, provider_name, provider_state, average_total_payments
FROM [[PARQUET_TABLE="your Parquet table name from the Tables list"]]
WHERE provider_state = 'CA'
ORDER BY average_total_payments DESC
LIMIT 10;

Choose Run.

What does this query do?

This is the exact same SQL you ran in Step 3. It selects four columns, filters for California hospitals, and sorts by total payments. The only difference is the table it reads from: your Parquet table instead of the CSV table.

Look at the query results. You should see the same California hospital rows you saw in Step 3.
Now look at the Data scanned value shown below the results. Write this number down.

The Parquet query's Data scanned value is significantly smaller than the ~12-14 MB the CSV query scanned. That is the columnar format at work.

Not seeing your Parquet table?

Make sure you selected datalake-transformed as the Database, not datalake-raw. The Parquet table only appears under datalake-transformed.

If the table still does not appear, return to the Glue console and confirm that your medicare-parquet-crawler ran successfully with a Completed status.

Still stuck? Help me figure out why my Parquet table is not showing up in Athena after running the crawler.

Compare CSV and Parquet scan metrics

You have the Parquet scan number. Now re-run the original CSV query so you can see both metrics fresh and compare them directly.

For Database, switch back to datalake-raw.
Run the same query against the CSV table by pasting this into the query editor:

-- Query the CSV table for the same California hospital data
SELECT drg_definition, provider_name, provider_state, average_total_payments
FROM medicare_hospital_provider_csv
WHERE provider_state = 'CA'
ORDER BY average_total_payments DESC
LIMIT 10;

Choose Run.
Note the Data scanned value for this CSV query. It should be around 12-14 MB.

Now compare the two values. The results are identical: same rows, same columns, same California hospital data. But the Parquet query scanned dramatically less data, often 50-90% less than the CSV query.

Why does Parquet scan so much less data?

CSV is a row-based format. When you ask for 4 columns out of 10, Athena still has to read every row from start to finish to extract those columns. It scans the entire file every time.

Parquet is a columnar format. It stores each column as a separate block of data. When your query asks for drg_definition, provider_name, provider_state, and average_total_payments, Athena reads only those 4 column blocks and skips the other 6 entirely.

At petabyte scale, this difference translates to thousands of dollars in cost savings. Athena bills per TB scanned, so scanning less data means paying less for every query.

Extra for Experts: What about Snappy compression?

Your Parquet files use Snappy compression by default (that is what the .snappy.parquet extension means). Snappy adds another layer of savings: the data is physically smaller on disk, so Athena reads even fewer bytes from S3. Columnar layout plus compression is the standard combination for production data lakes.

Secret mission

Add Partitioning for Real-World Optimization

Your Parquet transformation already slashed the data Athena scans. But in production data lakes, partitioning is the single most impactful optimization you can apply. Can you reorganize your Parquet output into folders by state so Athena skips entire directories of data it does not need?

Clean Up Your Resources

Decide whether to keep your resources running, pause them to come back later, or delete them entirely. All services in this project are serverless and only charge when you use them, so ongoing costs are minimal.

Cost warning

S3 charges a small amount for stored data, and Glue ETL jobs charge per DPU-second when you run them. The data in this project is only a few megabytes, so storage costs are negligible.

The real risk is accidentally re-running the Glue ETL jobs, which would incur another small charge. If you are done experimenting, deleting the jobs prevents this.

Resources you used:

3 S3 buckets: yourname-datalake-raw, yourname-datalake-transformed, and yourname-athena-results.
2 Glue databases: datalake-raw and datalake-transformed (each containing Data Catalog tables).
3 Glue Crawlers: medicare-raw-crawler, medicare-parquet-crawler, and medicare-parquet-partitioned-crawler.
2 Glue ETL jobs: medicare-csv-to-parquet and medicare-csv-to-parquet-partitioned.
1 IAM role: AWSGlueServiceRole-DataLake (with AWSGlueServiceRole and AmazonS3FullAccess policies).

Keep everything running

No action needed. Choose this if you plan to keep experimenting with Athena queries or modifying the ETL job.

All services are serverless. S3 charges for storage, but the data is only a few megabytes.
Glue and Athena only charge when you actively run jobs or queries.
The IAM role remains in your account but does not incur charges on its own.

Pause - I'll come back to this later

There is nothing to pause. All services in this project are serverless and only incur charges when you actively use them.

S3 storage costs for a few megabytes are fractions of a cent per month.
Glue ETL jobs, crawlers, and Athena queries only cost money when you run them.
You can safely leave everything in place and return later without accumulating meaningful charges.

Delete - I don't want to use this again

Remove all project resources to start fresh. Follow these steps in order.

Delete the Glue ETL jobs:

In the AWS Glue console, choose ETL jobs in the left menu.
Select medicare-csv-to-parquet and medicare-csv-to-parquet-partitioned.
From the Actions menu, choose Delete job.
Type delete to confirm, then choose Delete.

Delete the Glue Crawlers:

In the left menu, choose Crawlers.
Select medicare-raw-crawler, medicare-parquet-crawler, and medicare-parquet-partitioned-crawler.
Choose Delete and confirm the deletion.

Delete the Glue databases:

In the left menu under Data Catalog, choose Databases.
Select datalake-raw and choose Delete. This also deletes all tables inside the database.
Select datalake-transformed and choose Delete.

Empty and delete the S3 buckets:

Open the S3 console.
Select your yourname-datalake-raw bucket and choose Empty.
Type permanently delete to confirm, then choose Empty.
After the bucket is empty, select it again and choose Delete.
Type the bucket name to confirm, then choose Delete bucket.
Repeat for yourname-datalake-transformed and yourname-athena-results.

Delete the IAM role:

Open the IAM console.
In the left navigation, choose Roles.
Search for AWSGlueServiceRole-DataLake and select it.
Choose Delete at the top of the page.
Enter the role name to confirm, then choose Delete. The console automatically detaches the managed policies.

Nice Work!

You just built a complete serverless data lake pipeline on AWS. From raw CSV to optimized Parquet, you now have hands-on experience with the three most in-demand AWS data engineering services.

You've learned how to:

Built an end-to-end serverless data lake pipeline using Amazon S3, AWS Glue, and Amazon Athena to ingest, catalog, transform, and query data without managing any servers.
Created a Glue ETL job in PySpark that cleans raw CSV data and converts it to compressed Parquet format for efficient analytics.
Proved the performance advantage of columnar storage by comparing Athena's data scanned metrics between CSV and Parquet queries side by side.
Secret Mission: Added partitioning by provider_state to achieve even greater query optimization.

Ready to quiz yourself?