Build a FIFA World Cup Data Lake

Ingest, catalog, and query 2026 World Cup data with S3, Glue, and Athena.

Introduction

30 Second Summary

The 2026 FIFA World Cup is generating data across 16 stadiums in three countries. Every match result, every stadium capacity figure, every stage of the tournament is a data point waiting to be queried.

In this project, you will build a serverless data lake on AWS that ingests World Cup match and stadium data into Amazon S3, automatically discovers its schema with AWS Glue, and lets you query it all with standard SQL using Amazon Athena.

What You'll Build

You will invoke a single AWS Lambda function, watch World Cup data land in S3, run SQL queries that answer real tournament questions, then prove that partitioning slashes the amount of data Athena reads.

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

  • A serverless ingestion function you invoke once to populate your data lake with 18 match results and 16 stadiums, then verify the files sitting in S3.
  • An automatically discovered schema that makes raw JSON queryable with zero manual table definitions. You run a Glue Crawler and watch tables appear in the Data Catalog.
  • Live SQL analytics in Athena answering questions like "Which stadium hosted the most matches?" Then you run the same query before and after Hive-style partitioning and watch the "Data scanned" value drop, proving you understand data lake cost optimization.
  • Secret Mission: Add a teams dataset with FIFA rankings and confederations, then build cross-domain JOIN queries to analyze whether higher-ranked teams actually won their World Cup matches.

Are there any prerequisites?

You need an active AWS account with the AWS CLI installed and configured. Basic Python familiarity is helpful but not required. All code is provided for you to paste and run.

Before We Start

Before we dive in, let's lock in what you're about to build and why it matters.

Set Up S3 Buckets and IAM Roles

Your data lake needs two AWS services to function: Lambda to ingest World Cup data into storage, and Glue to discover what that data looks like.

Both services need IAM permissions to access your S3 bucket. Without proper roles, neither service can read or write your data lake. In this step, you will create the storage layer and grant each service exactly the access it needs.

In this step, get ready to:
  • Create two S3 buckets for data storage and query results.
  • Create an IAM role for Lambda with S3 and logging permissions.
  • Create an IAM role for the Glue Crawler with catalog and S3 permissions.
Create the S3 data lake bucket

Every data lake starts with a storage layer. You will create two buckets: one for the actual World Cup data and one for Athena query results.

  • Create both S3 buckets by running these commands:
aws s3 mb s3://worldcup-datalake-[[YOUR_ID="unique id e.g. jd-0711"]] --region us-east-1
aws s3 mb s3://worldcup-athena-results-[[YOUR_ID="unique id e.g. jd-0711"]] --region us-east-1

What do these buckets do?

The worldcup-datalake bucket stores your raw and partitioned World Cup data. This is the core of your data lake.

The worldcup-athena-results bucket stores the output of every SQL query you run in Athena. Athena requires a dedicated results location before it will execute any query.

  • Confirm both buckets were created by running:
aws s3 ls | grep worldcup

You should see two lines, each showing a creation date followed by your bucket name.

Seeing a BucketAlreadyExists error?

S3 bucket names are globally unique across all AWS accounts. If someone else has already taken the name, change your unique identifier to something more specific (e.g. your full name plus today's date).

Help me fix my S3 bucket name error.

Create the IAM role for Lambda

Lambda needs an execution role that tells AWS what it is allowed to do. You will create a trust policy that lets Lambda assume the role, then attach permissions for S3 access and CloudWatch logging.

  • Create a file called trust-policy.json with the following content:
{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Principal": {
        "Service": "lambda.amazonaws.com"
      },
      "Action": "sts:AssumeRole"
    }
  ]
}

What is a trust policy?

A trust policy defines which AWS service is allowed to "assume" (use) this role. Here you are telling AWS that only the Lambda service can use this role to perform actions on your behalf.

  • Confirm the file was created by running:
ls trust-policy.json

You should see trust-policy.json listed in the output.

  • Create the role and attach its permissions by running these commands:
aws iam create-role \
  --role-name WorldCupLambdaRole \
  --assume-role-policy-document file://trust-policy.json

aws iam attach-role-policy \
  --role-name WorldCupLambdaRole \
  --policy-arn arn:aws:iam::aws:policy/AmazonS3FullAccess

aws iam attach-role-policy \
  --role-name WorldCupLambdaRole \
  --policy-arn arn:aws:iam::aws:policy/service-role/AWSLambdaBasicExecutionRole

What do these policies grant?

AmazonS3FullAccess lets Lambda read and write objects in any S3 bucket in your account. This is what allows the ingestion function to store World Cup data in your data lake.

AWSLambdaBasicExecutionRole lets Lambda write logs to CloudWatch. Without this, you would have no visibility into errors or execution details.

After the first command, you should see a JSON response containing your new role's ARN and creation date.

Seeing an EntityAlreadyExists error?

This means a role named WorldCupLambdaRole already exists in your account from a previous attempt. Delete it first with aws iam delete-role --role-name WorldCupLambdaRole and try again. If policies are attached, detach them first.

Help me fix my IAM role creation error.

Create the IAM role for the Glue Crawler

The Glue Crawler needs its own role to read files from S3 and write table definitions into the Glue Data Catalog.

  • Create a file called glue-trust-policy.json with the following content:
{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Principal": {
        "Service": "glue.amazonaws.com"
      },
      "Action": "sts:AssumeRole"
    }
  ]
}
  • Confirm the file was created by running:
ls glue-trust-policy.json

You should see glue-trust-policy.json listed in the output.

  • Create the Glue role and attach its permissions by running these commands:
aws iam create-role \
  --role-name WorldCupGlueRole \
  --assume-role-policy-document file://glue-trust-policy.json

aws iam attach-role-policy \
  --role-name WorldCupGlueRole \
  --policy-arn arn:aws:iam::aws:policy/service-role/AWSGlueServiceRole

aws iam attach-role-policy \
  --role-name WorldCupGlueRole \
  --policy-arn arn:aws:iam::aws:policy/AmazonS3FullAccess

What does AWSGlueServiceRole grant?

AWSGlueServiceRole gives the crawler permission to write table definitions and partition information into the Glue Data Catalog. Combined with AmazonS3FullAccess, the crawler can read your data files and register their schema automatically.

You should see a JSON response with the new WorldCupGlueRole details after the first command.

Seeing a MalformedPolicyDocument error?

Double-check that glue-trust-policy.json contains valid JSON with no trailing commas. Run cat glue-trust-policy.json to inspect the contents and compare against the JSON shown above.

Help me fix my Glue role creation error.

Before you move on, what do you expect to see if both your buckets and roles were created successfully?

  • Verify your S3 bucket exists by running:
aws s3 ls s3://worldcup-datalake-[[YOUR_ID="unique id e.g. jd-0711"]]/

You should see an empty result with no errors. An empty response confirms the bucket exists but contains nothing yet.

  • Verify your Lambda role exists by running:
aws iam get-role --role-name WorldCupLambdaRole

You should see a JSON response showing WorldCupLambdaRole with its ARN, creation date, and the trust policy you defined.

Your storage layer is live and both services have the permissions they need. Next up, you will write a Lambda function that populates your data lake with real 2026 World Cup match data.

Deploy and Run the Ingestion Lambda

Your data lake bucket is ready, but it is completely empty. Without data, there is nothing to catalog or query in later steps.

In this step, you will write an AWS Lambda function that populates your S3 data lake with real 2026 FIFA World Cup match results and stadium metadata in a single invocation.

In this step, get ready to:
  • Create a Python Lambda function containing World Cup match and stadium data.
  • Deploy the function to AWS Lambda with the correct IAM role and environment variables.
  • Invoke the function and verify data files land in your S3 bucket.
Create and package the Lambda function

The Lambda function stores World Cup data as embedded Python lists, then writes them to S3 in JSON Lines format when invoked. JSON Lines means one JSON object per line, which is the format Athena expects for querying.

  • Create a new file called lambda_function.py in the same folder as your trust policy files by pasting this first section:
import json
import boto3
import os

s3 = boto3.client('s3')

def lambda_handler(event, context):
    bucket = os.environ['BUCKET_NAME']
    mode = event.get('mode', 'raw')
    # Match results from the 2026 FIFA World Cup
    matches = [
        {"match_id": 1, "match_date": "2026-06-11", "stage": "group_stage", "group_name": "A", "team1": "Mexico", "team2": "South Africa", "score1": 1, "score2": 0, "stadium": "Mexico City Stadium", "city": "Mexico City", "host_country": "Mexico"},
        {"match_id": 2, "match_date": "2026-06-11", "stage": "group_stage", "group_name": "A", "team1": "Korea Republic", "team2": "Czechia", "score1": 0, "score2": 0, "stadium": "Guadalajara Stadium", "city": "Guadalajara", "host_country": "Mexico"},
        {"match_id": 3, "match_date": "2026-06-12", "stage": "group_stage", "group_name": "B", "team1": "Canada", "team2": "Bosnia and Herzegovina", "score1": 1, "score2": 0, "stadium": "Toronto Stadium", "city": "Toronto", "host_country": "Canada"},
        {"match_id": 4, "match_date": "2026-06-12", "stage": "group_stage", "group_name": "D", "team1": "United States", "team2": "Paraguay", "score1": 4, "score2": 1, "stadium": "Los Angeles Stadium", "city": "Los Angeles", "host_country": "United States"},
        {"match_id": 5, "match_date": "2026-06-13", "stage": "group_stage", "group_name": "C", "team1": "Haiti", "team2": "Scotland", "score1": 0, "score2": 1, "stadium": "Boston Stadium", "city": "Boston", "host_country": "United States"},
        {"match_id": 6, "match_date": "2026-06-13", "stage": "group_stage", "group_name": "D", "team1": "Australia", "team2": "Turkiye", "score1": 2, "score2": 0, "stadium": "BC Place Vancouver", "city": "Vancouver", "host_country": "Canada"},
        {"match_id": 7, "match_date": "2026-06-13", "stage": "group_stage", "group_name": "C", "team1": "Brazil", "team2": "Morocco", "score1": 1, "score2": 1, "stadium": "New York New Jersey Stadium", "city": "New York New Jersey", "host_country": "United States"},
        {"match_id": 8, "match_date": "2026-06-13", "stage": "group_stage", "group_name": "B", "team1": "Qatar", "team2": "Switzerland", "score1": 1, "score2": 1, "stadium": "San Francisco Bay Area Stadium", "city": "San Francisco Bay Area", "host_country": "United States"},
        {"match_id": 9, "match_date": "2026-06-14", "stage": "group_stage", "group_name": "E", "team1": "Ivory Coast", "team2": "Ecuador", "score1": 1, "score2": 0, "stadium": "Philadelphia Stadium", "city": "Philadelphia", "host_country": "United States"},
        {"match_id": 10, "match_date": "2026-06-14", "stage": "group_stage", "group_name": "E", "team1": "Germany", "team2": "Curacao", "score1": 7, "score2": 1, "stadium": "Houston Stadium", "city": "Houston", "host_country": "United States"},
        {"match_id": 11, "match_date": "2026-06-14", "stage": "group_stage", "group_name": "F", "team1": "Netherlands", "team2": "Japan", "score1": 2, "score2": 2, "stadium": "Dallas Stadium", "city": "Dallas", "host_country": "United States"},
        {"match_id": 12, "match_date": "2026-06-14", "stage": "group_stage", "group_name": "F", "team1": "Sweden", "team2": "Tunisia", "score1": 5, "score2": 1, "stadium": "Estadio Monterrey", "city": "Monterrey", "host_country": "Mexico"},
        {"match_id": 73, "match_date": "2026-06-28", "stage": "round_of_32", "group_name": None, "team1": "Brazil", "team2": "Japan", "score1": 2, "score2": 1, "stadium": "Los Angeles Stadium", "city": "Los Angeles", "host_country": "United States"},
        {"match_id": 74, "match_date": "2026-06-29", "stage": "round_of_32", "group_name": None, "team1": "Germany", "team2": "Scotland", "score1": 3, "score2": 0, "stadium": "Houston Stadium", "city": "Houston", "host_country": "United States"},
        {"match_id": 89, "match_date": "2026-07-04", "stage": "round_of_16", "group_name": None, "team1": "France", "team2": "Paraguay", "score1": 1, "score2": 0, "stadium": "Philadelphia Stadium", "city": "Philadelphia", "host_country": "United States"},
        {"match_id": 90, "match_date": "2026-07-05", "stage": "round_of_16", "group_name": None, "team1": "Spain", "team2": "Portugal", "score1": 1, "score2": 0, "stadium": "Dallas Stadium", "city": "Dallas", "host_country": "United States"},
        {"match_id": 97, "match_date": "2026-07-09", "stage": "quarterfinal", "group_name": None, "team1": "France", "team2": "Morocco", "score1": 2, "score2": 0, "stadium": "Boston Stadium", "city": "Boston", "host_country": "United States"},
        {"match_id": 98, "match_date": "2026-07-10", "stage": "quarterfinal", "group_name": None, "team1": "Spain", "team2": "Belgium", "score1": 2, "score2": 1, "stadium": "Los Angeles Stadium", "city": "Los Angeles", "host_country": "United States"},
    ]

What does this code do?

The imports bring in boto3 (the AWS SDK for Python) and os (for reading environment variables).

The lambda_handler function reads which S3 bucket to write to from the BUCKET_NAME environment variable. The matches list contains 18 real match results from the 2026 World Cup, spanning group stage through quarterfinals.

  • Save lambda_function.py.
  • Confirm the file was created by running:
ls lambda_function.py

You should see lambda_function.py listed in the output.

  • Now add the stadiums data directly below the closing ] of the matches list in lambda_function.py:
    # Metadata for all 16 World Cup venue stadiums
    stadiums = [
        {"stadium_name": "Mexico City Stadium", "city": "Mexico City", "country": "Mexico", "capacity": 80824, "year_opened": 1966},
        {"stadium_name": "Guadalajara Stadium", "city": "Guadalajara", "country": "Mexico", "capacity": 45664, "year_opened": 2010},
        {"stadium_name": "Estadio Monterrey", "city": "Monterrey", "country": "Mexico", "capacity": 51243, "year_opened": 2015},
        {"stadium_name": "Toronto Stadium", "city": "Toronto", "country": "Canada", "capacity": 43036, "year_opened": 2007},
        {"stadium_name": "BC Place Vancouver", "city": "Vancouver", "country": "Canada", "capacity": 52497, "year_opened": 1983},
        {"stadium_name": "Atlanta Stadium", "city": "Atlanta", "country": "United States", "capacity": 68239, "year_opened": 2017},
        {"stadium_name": "Boston Stadium", "city": "Boston", "country": "United States", "capacity": 64146, "year_opened": 2002},
        {"stadium_name": "Dallas Stadium", "city": "Dallas", "country": "United States", "capacity": 70649, "year_opened": 2009},
        {"stadium_name": "Houston Stadium", "city": "Houston", "country": "United States", "capacity": 68777, "year_opened": 2002},
        {"stadium_name": "Kansas City Stadium", "city": "Kansas City", "country": "United States", "capacity": 69045, "year_opened": 1972},
        {"stadium_name": "Los Angeles Stadium", "city": "Los Angeles", "country": "United States", "capacity": 70492, "year_opened": 2020},
        {"stadium_name": "Miami Stadium", "city": "Miami", "country": "United States", "capacity": 64478, "year_opened": 1987},
        {"stadium_name": "New York New Jersey Stadium", "city": "New York New Jersey", "country": "United States", "capacity": 80663, "year_opened": 2010},
        {"stadium_name": "Philadelphia Stadium", "city": "Philadelphia", "country": "United States", "capacity": 68324, "year_opened": 2003},
        {"stadium_name": "San Francisco Bay Area Stadium", "city": "San Francisco Bay Area", "country": "United States", "capacity": 68827, "year_opened": 2014},
        {"stadium_name": "Seattle Stadium", "city": "Seattle", "country": "United States", "capacity": 66925, "year_opened": 2002},
    ]

What does the stadiums list contain?

Each stadium entry records the FIFA tournament name, city, host country, seating capacity, and year opened. These 16 venues span all three host nations: Mexico, Canada, and the United States.

  • Save lambda_function.py and confirm your file is building up by running:
wc -l lambda_function.py

You should see approximately 49 lines reported.

  • Add the raw mode ingestion logic directly below the closing ] of the stadiums list:
    if mode == 'raw':
        # Convert matches to JSON Lines format (one JSON object per line)
        matches_jsonl = '\n'.join(json.dumps(m) for m in matches)
        s3.put_object(
            Bucket=bucket,
            Key='raw/matches/matches.jsonl',
            Body=matches_jsonl,
            ContentType='application/json'
        )

        # Convert and upload stadiums
        stadiums_jsonl = '\n'.join(json.dumps(s) for s in stadiums)
        s3.put_object(
            Bucket=bucket,
            Key='raw/stadiums/stadiums.jsonl',
            Body=stadiums_jsonl,
            ContentType='application/json'
        )

        return {
            'statusCode': 200,
            'body': json.dumps({
                'message': f'Successfully ingested {len(matches)} matches and {len(stadiums)} stadiums',
                'bucket': bucket,
                'paths': ['raw/matches/matches.jsonl', 'raw/stadiums/stadiums.jsonl']
            })
        }

What does the raw mode do?

When mode is 'raw', the function converts each Python dictionary to a JSON string, joins them with newlines (creating JSON Lines format), then writes one file for matches and one for stadiums into the raw/ prefix in your S3 bucket.

JSON Lines stores one record per line. This is the format Athena's OpenX JSON SerDe expects for querying.

  • Add the partitioned mode logic directly below the raw mode's return statement (at the same indentation level as if mode == 'raw':):
    elif mode == 'partitioned':
        # Group matches by tournament stage
        stages = {}
        for match in matches:
            stage = match['stage']
            if stage not in stages:
                stages[stage] = []
            stages[stage].append(match)

        # Write each stage to its own Hive-style partition folder
        for stage, stage_matches in stages.items():
            stage_jsonl = '\n'.join(json.dumps(m) for m in stage_matches)
            s3.put_object(
                Bucket=bucket,
                Key=f'partitioned/matches/stage={stage}/data.jsonl',
                Body=stage_jsonl,
                ContentType='application/json'
            )

        return {
            'statusCode': 200,
            'body': json.dumps({
                'message': f'Successfully wrote {len(matches)} matches across {len(stages)} partitions',
                'bucket': bucket,
                'partitions': list(stages.keys())
            })
        }

What does the partitioned mode do?

Partitioned mode groups matches by their stage field (group_stage, round_of_32, round_of_16, quarterfinal) and writes each group into a separate S3 folder using Hive-style paths like partitioned/matches/stage=group_stage/data.jsonl.

You will use this mode in a later step to demonstrate partition pruning.

  • Save lambda_function.py.
  • Package the function into a zip file for Lambda by running:
zip function.zip lambda_function.py
  • Confirm the zip was created by running:
ls -la function.zip

You should see function.zip listed with a size of a few kilobytes.

Seeing 'zip: command not found'?

On macOS, zip is pre-installed. If you see this error on Linux, install it with your package manager (e.g. sudo apt install zip).

help me fix the zip command not found error

✔️ Awesome, I've got everything!

Great. Double check you have saved lambda_function.py and that function.zip exists in the same directory.

ⓧ I'd like to double check the full code

Your lambda_function.py should contain these sections in order. Scroll up and confirm each is present:

  • Imports (json, boto3, os) and s3 = boto3.client('s3').
  • The lambda_handler function definition reading BUCKET_NAME and mode.
  • A matches list with 18 dictionaries (match_id 1 through 98).
  • A stadiums list with 16 dictionaries.
  • An if mode == 'raw': block that writes both files to S3 and returns a success response.
  • An elif mode == 'partitioned': block that groups matches by stage and writes Hive-style partition folders.
Deploy the Lambda function

With the zip package ready, you can now deploy it to AWS Lambda and wire it to your S3 bucket.

  • Retrieve your AWS account ID and deploy the function by running these commands:
# Store your account ID in a shell variable
ACCOUNT_ID=$(aws sts get-caller-identity --query Account --output text)

aws lambda create-function \
  --function-name WorldCupIngest \
  --runtime python3.12 \
  --role arn:aws:iam::${ACCOUNT_ID}:role/WorldCupLambdaRole \
  --handler lambda_function.lambda_handler \
  --zip-file fileb://function.zip \
  --timeout 30 \
  --environment "Variables={BUCKET_NAME=worldcup-datalake-[[YOURID="your unique identifier e.g. initials-date"]]}"

What does this command do?

  • ACCOUNT_ID=$(...) fetches your 12-digit AWS account number so the role ARN resolves correctly.
  • --runtime python3.12 tells Lambda which language runtime to use.
  • --handler lambda_function.lambda_handler points Lambda at the file and function to execute.
  • --environment injects your bucket name so the function knows where to write data.

You should see a JSON response containing FunctionName, FunctionArn, Runtime, and other configuration details confirming the function was created.

Seeing 'InvalidParameterValueException' about the role?

IAM roles can take up to 10 seconds to propagate after creation. If you just created WorldCupLambdaRole in the previous step, wait 10 seconds and run the command again.

Also confirm the role name matches exactly by running:

aws iam get-role --role-name WorldCupLambdaRole

help me debug my Lambda create-function error

Invoke the Lambda and verify data landed

The function is deployed. Time to trigger it and see World Cup data flow into your data lake.

Before you run the invoke command, what do you expect to see in the response? Think about what the raw mode's return statement sends back.

  • Invoke the function in raw mode by running:
aws lambda invoke \
  --function-name WorldCupIngest \
  --payload '{"mode": "raw"}' \
  --cli-binary-format raw-in-base64-out \
  response.json

What is --cli-binary-format?

AWS CLI v2 expects binary payloads by default. The --cli-binary-format raw-in-base64-out flag tells the CLI to pass your JSON string as-is rather than base64-encoding it.

  • Check the Lambda response by running:
cat response.json

You should see a JSON object with statusCode 200 and a message confirming 18 matches and 16 stadiums were ingested.

  • Verify the data files landed in S3 by running:
aws s3 ls s3://worldcup-datalake-[[YOURID="your unique identifier e.g. initials-date"]]/raw/matches/
aws s3 ls s3://worldcup-datalake-[[YOURID="your unique identifier e.g. initials-date"]]/raw/stadiums/

You should see matches.jsonl listed under the matches prefix and stadiums.jsonl under the stadiums prefix.

Seeing 'FunctionError' or empty response?

  • If response.json shows a FunctionError, check CloudWatch Logs for the specific traceback. The most common cause is a mismatch between the bucket name in the --environment variable and the actual bucket you created.
  • Confirm your bucket exists by running aws s3 ls and checking the bucket name matches exactly.

help me debug my Lambda invocation error

Your data lake now has real World Cup data. Next up, you will point a Glue Crawler at these files to automatically discover their schema and make them queryable with SQL.

Catalog with Glue and Query with Athena

Your Lambda function successfully loaded World Cup data into S3. The next challenge is querying that data with SQL.

The problem is that Amazon Athena cannot interpret raw JSON files without knowing their structure. The AWS Glue Crawler solves this by scanning your S3 data, automatically discovering the schema, and registering table definitions in the Glue Data Catalog. Once tables exist in the catalog, Athena can query them with standard SQL.

In this step, get ready to:
  • Create a Glue Crawler to automatically discover your data schema.
  • Configure Athena and run your first SQL query against the data lake.
  • Observe the partitioning problem that costs money on production datasets.
Create a Glue Crawler via the console

A Glue Crawler reads files in S3, identifies their format and fields, then writes table definitions to the Data Catalog. This is schema-on-read in action: you never defined a schema up front, but the crawler discovers it automatically.

This opens the crawler creation wizard. Walk through each step:

  • In the Name field, enter worldcup-raw-crawler.
  • Click Next.
  • On the data sources step, click Add a data source.
  • Select S3 as the data store.
  • For the S3 path, enter s3://worldcup-datalake-your-id/raw/.
  • Click Add an S3 data source, then click Next.
  • On the security settings step, select the existing IAM role WorldCupGlueRole from the dropdown.
  • Click Next.

Now configure where the crawler writes its results:

  • Under Target database, click Add database.
  • Name the new database worldcup_raw and click Create.
  • Leave the table prefix empty.
  • For the crawler schedule, select On demand.
  • Click Next, review your settings, then click Create crawler.

Now run the crawler to discover your data:

  • Select the worldcup-raw-crawler checkbox on the Crawlers page.
  • Click Run.
  • Wait 1-2 minutes for the crawler to finish. Click the refresh icon to check the status until it shows Completed.

What does the crawler do?

The crawler scans every file under your raw/ prefix, identifies the JSON Lines format, reads the field names and types from the data, and registers two tables in the Data Catalog: matches and stadiums.

You never wrote a CREATE TABLE statement. The crawler inferred everything from the data itself.

  • Verify the tables were created by clicking Tables in the left navigation under Data Catalog.
  • You should see two tables: matches and stadiums, both in the worldcup_raw database.

Don't see the tables?

  • Check the crawler status. If it says Failed, click the crawler name to view CloudWatch logs. The most common cause is a typo in the S3 path.
  • Verify the S3 path ends with /raw/ (trailing slash included). Without it, the crawler may not find any files.
  • Confirm WorldCupGlueRole has AmazonS3FullAccess attached. The crawler needs S3 read permissions.

Why can't my Glue Crawler find my S3 data?

Configure Athena and run your first query

With tables registered in the Data Catalog, Athena can now run SQL queries against them. First you need to tell Athena where to store query results.

  • Open the Athena Console at https://console.aws.amazon.com/athena/.
  • If this is your first time visiting Athena, click Explore the query editor.
  • Click Edit Settings.
  • In the Location of query result box, enter s3://worldcup-athena-results-your-id/.
  • Click Save.
  • Click Editor to return to the query editor.
  • From the Database dropdown on the left, select worldcup_raw.

Why a separate results bucket?

Athena writes CSV result files for every query you run. Keeping results in a dedicated bucket prevents query output from cluttering your data lake storage. It also makes cleanup easier.

Time to run your first SQL query against the data lake. This query counts how many matches each stadium hosted.

  • Paste the following query into the Athena query editor and click Run:
SELECT stadium, city, host_country, COUNT(*) as match_count
FROM worldcup_raw.matches
GROUP BY stadium, city, host_country
ORDER BY match_count DESC;

You should see a results table showing stadiums ranked by the number of matches they hosted. Los Angeles Stadium and Boston Stadium should appear near the top with multiple matches each.

What just happened?

You just ran a SQL query directly against raw JSON files sitting in S3. Athena used the schema that the Glue Crawler registered in the Data Catalog to interpret the fields. No database server needed.

Seeing a permissions or location error?

  • If Athena says it cannot find the query result location, double-check that you entered the full path including the trailing slash.
  • If you see a permission error, confirm that your IAM user has permissions to write to the results bucket.

Why is my Athena query failing with a permissions error?

Observe the partitioning problem

Your query worked. But there is a hidden cost problem lurking in your data lake. Every time Athena runs a query, it charges based on the amount of data scanned. Filtering with a WHERE clause should reduce how much data Athena reads. Let's test whether that is actually happening.

  • Run this query to fetch only group stage matches:
SELECT team1, team2, score1, score2, stadium
FROM worldcup_raw.matches
WHERE stage = 'group_stage';
  • After the query completes, look at the Data scanned value shown below the results. Note this number.
  • Now run a full table scan with no filter:
SELECT * FROM worldcup_raw.matches;

Before you compare the two values, predict: do you think the filtered query scanned less data than the full table scan?

  • Compare the Data scanned value of both queries.

Both queries show the same Data scanned value. Even though you filtered to only group stage matches, Athena read the entire file. This is because all 18 match records live in a single file. Athena has no way to skip records without reading the whole thing.

Why does this matter?

Athena charges $5 per terabyte of data scanned. On a production data lake with millions of rows, scanning the full dataset for every query wastes money and slows down results. The fix is partitioning: organizing data into separate folders so Athena can physically skip entire sections it does not need.

You have proven that a WHERE clause alone does not reduce data scanned when all records live in one file. Next up, you will reorganize the data into partitioned folders and watch Athena skip entire partitions.

Partition for Performance

In the previous step, you ran SQL queries on your data lake and saw something frustrating. Whether you filtered for group stage matches or selected everything, Athena scanned the same amount of data.

The problem is simple: all 18 matches live in a single file. Athena has no choice but to read every byte, even when your WHERE clause only needs a fraction of the records. On production datasets with millions of rows, this means wasted money and slow queries.

The fix is Hive-style partitioning. Instead of dumping all records into one file, you organize data into physical folders named with a key=value pattern. Athena can then skip entire folders that don't match your filter, a technique called partition pruning.

In this step, get ready to:
  • Invoke your Lambda in partitioned mode to write data into Hive-style folders.
  • Create and run a Glue Crawler that discovers partitions automatically.
  • Prove partition pruning reduces data scanned with a side-by-side Athena comparison.
Deploy and invoke Lambda in partitioned mode

Your lambda_function.py already contains an elif mode == 'partitioned': branch that groups matches by their stage field and writes each group to its own S3 folder.

How does the partitioned mode work?

The code loops through the matches list and groups them by the stage field (group_stage, round_of_32, round_of_16, quarterfinal).

Each group gets written to its own path like partitioned/matches/stage=group_stage/data.jsonl. This folder naming convention is what Glue and Athena recognize as a Hive-style partition.

  • Repackage your Lambda code and update the deployed function by running these commands:
zip function.zip lambda_function.py

aws lambda update-function-code \
  --function-name WorldCupIngest \
  --zip-file fileb://function.zip

Why redeploy if the code hasn't changed?

Redeploying ensures your live Lambda matches your local file exactly. This is a good habit before invoking a new code path for the first time.

  • Invoke the Lambda with the partitioned mode payload by running:
aws lambda invoke \
  --function-name WorldCupIngest \
  --payload '{"mode": "partitioned"}' \
  --cli-binary-format raw-in-base64-out \
  response.json
  • Check the response to confirm it succeeded:
cat response.json

You should see a message saying 18 matches were written across 4 partitions.

  • Verify the partitioned folder structure in S3 by running:
aws s3 ls s3://[[DATALAKE_BUCKET="worldcup-datalake-your-id"]]/partitioned/matches/

You should see four partition folders listed with the PRE prefix:

PRE stage=group_stage/
PRE stage=quarterfinal/
PRE stage=round_of_16/
PRE stage=round_of_32/

Not seeing the partition folders?

Make sure response.json contains statusCode: 200. If you see a timeout error, the Lambda may need a higher timeout value (the default 30 seconds should be enough for this dataset).

Double-check that the bucket name in your S3 ls command matches the BUCKET_NAME environment variable on your Lambda function. Help me troubleshoot my Lambda partitioned invocation

Crawl the partitioned data

The partitioned data is in S3, but Athena still has no idea it exists. You need a new Glue Crawler that targets the partitioned path and discovers the stage= folder pattern as a partition column.

  • First, create a new Glue database to keep your partitioned tables separate by running:
aws glue create-database \
  --database-input '{"Name": "worldcup_partitioned"}'
  • Create the new crawler targeting your partitioned matches path by running:
aws glue create-crawler \
  --name worldcup-partitioned-crawler \
  --role WorldCupGlueRole \
  --database-name worldcup_partitioned \
  --targets '{"S3Targets": [{"Path": "s3://[[DATALAKE_BUCKET="worldcup-datalake-your-id"]]/partitioned/matches/"}]}'

What does this crawler do differently?

This crawler points at partitioned/matches/ instead of raw/. When it encounters the stage=value folder pattern, it automatically registers stage as a partition column in the Data Catalog. This is what enables Athena to skip folders during query execution.

  • Start the crawler by running:
aws glue start-crawler --name worldcup-partitioned-crawler

The crawler takes 1-2 minutes to discover your partition structure and register the table.

  • Check whether the crawler has finished by running:
aws glue get-crawler --name worldcup-partitioned-crawler \
  --query 'Crawler.State' --output text

Wait until the output shows READY before proceeding. If it still shows RUNNING, wait 30 seconds and try again.

Crawler stuck or showing errors?

If the crawler shows READY but you see no table in the database, confirm your S3 path ends with a trailing slash in the targets. Also verify the WorldCupGlueRole role has both the AWSGlueServiceRole and AmazonS3FullAccess policies attached.

Help me troubleshoot my Glue Crawler

Prove partition pruning with Athena

Now for the payoff. You will run the same filtered query from the previous step, but this time against the partitioned table. The difference in data scanned proves that Athena is skipping folders it does not need.

  • Return to the Athena Console in your browser.
  • Select database worldcup_partitioned from the dropdown on the left.
  • First, run a full table scan to establish the baseline by pasting this query:
SELECT * FROM worldcup_partitioned.matches;
  • Note the Data scanned value shown at the bottom of the query results. This is the total size of all partition files combined.

Before you run the next query, predict: will the data scanned be the same, or smaller?

  • Run this filtered query that targets only group stage matches:
SELECT team1, team2, score1, score2, stadium
FROM worldcup_partitioned.matches
WHERE stage = 'group_stage';
  • Compare the Data scanned value to the full table scan.

The filtered query scans less data. Athena read only the stage=group_stage/ folder and skipped the round_of_32, round_of_16, and quarterfinal partitions entirely.

Why does this matter at scale?

Athena charges $5 per TB of data scanned. On a production data lake with terabytes of match data, partition pruning can save thousands of dollars per month by letting queries read only the partitions they need.

Your dataset is tiny (kilobytes), so the cost savings are negligible here. The point is proving the pattern works before you scale it.

Data scanned is the same for both queries?

Make sure you are querying the worldcup_partitioned database, not worldcup_raw. The raw database has no partitions, so every query scans everything regardless of your WHERE clause.

Also confirm your crawler finished successfully by checking its state is READY and that the matches table appears under worldcup_partitioned in Athena's left panel.

Help me understand why my partition pruning isn't working

Document your data lake architecture

Your data lake is complete. Create a README to document the architecture, data model, and sample queries for anyone (including future you) who revisits this project.

  • Create a file called README.md in the same folder as your lambda_function.py and add the project title and architecture overview:
# 2026 FIFA World Cup Data Lake

A serverless data lake on AWS that ingests, catalogs, and analyzes 2026 FIFA World Cup data.

## Architecture

```
EventBridge Scheduler (Project 2)
        |
        v
  AWS Lambda ──────> Amazon S3 (Data Lake)
  (Ingestion)         ├── raw/matches/
                      ├── raw/stadiums/
                      └── partitioned/matches/stage=.../
                              |
                              v
                    AWS Glue Crawler
                              |
                              v
                    Glue Data Catalog
                              |
                              v
                    Amazon Athena (SQL Queries)
                              |
                              v
                    QuickSight Dashboard (Project 2)
```
  • Below the architecture section, add the data model documentation:
## Data Model

### Matches
| Field | Type | Description |
|-------|------|-------------|
| match_id | int | Unique match identifier |
| match_date | string | Match date (YYYY-MM-DD) |
| stage | string | Tournament stage |
| group_name | string | Group letter or null |
| team1 | string | Home/first team name |
| team2 | string | Away/second team name |
| score1 | int | Team 1 goals |
| score2 | int | Team 2 goals |
| stadium | string | Stadium name |
| city | string | Host city |
| host_country | string | Host nation |

### Stadiums
| Field | Type | Description |
|-------|------|-------------|
| stadium_name | string | FIFA tournament stadium name |
| city | string | City location |
| country | string | Country |
| capacity | int | Seating capacity |
| year_opened | int | Year stadium opened |
  • Finally, add the design decisions, sample queries, and cost sections:
## Key Design Decisions

- **JSON Lines format**: One JSON object per line for Athena SerDe compatibility
- **Hive-style partitioning**: `stage=value/` folder structure enables partition pruning
- **Schema-on-read**: No upfront schema required; Glue discovers structure automatically
- **Serverless**: Zero infrastructure management; pay only for what you use

## Sample Queries

```sql
-- Matches per stadium
SELECT stadium, COUNT(*) as matches FROM worldcup_raw.matches GROUP BY stadium ORDER BY matches DESC;

-- Host nation performance
SELECT team1, score1, score2, team2 FROM worldcup_raw.matches WHERE team1 IN ('Mexico', 'Canada', 'United States');

-- Partitioned query (scans only group stage data)
SELECT * FROM worldcup_partitioned.matches WHERE stage = 'group_stage';
```

## Cost

All services used operate within AWS Free Tier.

## Cleanup

See project cleanup commands to delete all resources.

What does this README do for you?

A well-structured README makes your project portfolio-ready. Anyone reviewing your GitHub can understand your architecture decisions, data model, and how to run queries without reading your Lambda code.

  • Save README.md.
  • Confirm the file exists by running:
ls

You should see README.md listed alongside lambda_function.py, function.zip, and your trust policy files.

You have proven that Hive-style partitioning reduces the data Athena scans. Your serverless data lake now ingests, catalogs, and queries 2026 FIFA World Cup data with real cost optimization built in. Next up, put your new data engineering skills to the test with a Secret Mission.

Secret mission

Cross-Domain Analytics with Team Rankings

Real data lakes contain multiple datasets that gain power when joined together. Add a teams dimension with FIFA rankings and confederations, then build cross-domain JOIN queries to analyze how team rankings predicted World Cup performance.

Clean Up Your Resources

Clean Up Your Resources

Decide whether to keep your resources running, pause them to come back later, or delete them entirely. This project uses entirely serverless services within the AWS Free Tier, so there are no ongoing costs while your resources sit idle.

Resources you used:

  • S3 bucket worldcup-datalake-{your-id} (match, stadium, and team data files plus partitioned data).
  • S3 bucket worldcup-athena-results-{your-id} (Athena query result files).
  • Lambda function WorldCupIngest.
  • IAM roles WorldCupLambdaRole and WorldCupGlueRole.
  • Glue Crawlers worldcup-raw-crawler and worldcup-partitioned-crawler.
  • Glue Databases worldcup_raw and worldcup_partitioned.
  • Local files: lambda_function.py, function.zip, trust-policy.json, glue-trust-policy.json, README.md.

Keep everything running

No action needed. Choose this if you plan to extend the data lake further or use it as a reference.

  • All services are serverless and only incur charges when actively invoked or queried.
  • The Glue Data Catalog stays within the free tier (first 1 million objects stored free).
  • S3 stores only a few KB of data, which costs fractions of a cent per month.
  • Lambda costs nothing at rest.

Pause - I'll come back to this later

There is nothing to pause. All services in this project are serverless and bill only on use.

  • Lambda does not run unless you invoke it.
  • Glue Crawlers do not run unless you start them.
  • Athena does not charge unless you execute a query.
  • Your resources will be exactly as you left them when you return.

Delete - I don't want to use this again

Remove all project resources and start fresh. Run the following commands in your terminal to delete everything.

  • Delete the Lambda function by running:
aws lambda delete-function --function-name WorldCupIngest
  • Empty and delete both S3 buckets by running:
aws s3 rb s3://worldcup-datalake-[[DATALAKE_BUCKET_ID="your-id"]] --force
aws s3 rb s3://worldcup-athena-results-[[ATHENA_BUCKET_ID="your-id"]] --force
  • Detach policies and delete the Lambda IAM role by running:
aws iam detach-role-policy \
  --role-name WorldCupLambdaRole \
  --policy-arn arn:aws:iam::aws:policy/AmazonS3FullAccess

aws iam detach-role-policy \
  --role-name WorldCupLambdaRole \
  --policy-arn arn:aws:iam::aws:policy/service-role/AWSLambdaBasicExecutionRole

aws iam delete-role --role-name WorldCupLambdaRole
  • Detach policies and delete the Glue IAM role by running:
aws iam detach-role-policy \
  --role-name WorldCupGlueRole \
  --policy-arn arn:aws:iam::aws:policy/service-role/AWSGlueServiceRole

aws iam detach-role-policy \
  --role-name WorldCupGlueRole \
  --policy-arn arn:aws:iam::aws:policy/AmazonS3FullAccess

aws iam delete-role --role-name WorldCupGlueRole
  • Delete the Glue Crawlers and Databases by running:
aws glue delete-crawler --name worldcup-raw-crawler
aws glue delete-crawler --name worldcup-partitioned-crawler
aws glue delete-database --name worldcup_raw
aws glue delete-database --name worldcup_partitioned
  • Delete your local project files by running:
rm lambda_function.py function.zip trust-policy.json glue-trust-policy.json README.md

Nice Work!

Nice Work!

Nice work. You just built a fully serverless data lake on AWS that ingests, catalogs, and analyzes 2026 FIFA World Cup data.

You've learned how to:

  • Build a serverless data lake on AWS using a Lambda ingestion function that populates S3 with World Cup match and stadium data in a single invocation.
  • Configure Glue Crawlers to automatically discover your data schema and query it with standard SQL in Athena.
  • Implement Hive-style partitioning and prove that partition pruning reduces the amount of data Athena scans, cutting cost and improving query speed.
  • (Secret Mission) Add a teams dimension with FIFA rankings and perform cross-domain JOIN queries to analyze whether higher-ranked teams actually won their World Cup matches.

Ready to quiz yourself?