Build a U.S. Debt Analytics Pipeline

Ingest Treasury debt data with AWS and publish a typed analytics dashboard.

Introduction

30 Second Summary

Public debt numbers change every business day. A single headline makes it hard to see how the balance moves over time.

In this project, you will build a serverless U.S. debt analytics pipeline on AWS that turns public Treasury records into an interactive dashboard. The finished dashboard reveals the total debt trend plus its public and intragovernmental components.

What You'll Build

You'll open a published Amazon Quick Sight dashboard where the total debt trend, both debt components, and recent dated records make the pipeline's result easy to explore.

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

  • A working AWS Lambda ingestion run where you see fresh Debt to the Penny records land in a private Amazon S3 raw layer.
  • A SQL workflow that exposes the raw string type problem before producing typed, partitioned Parquet data.
  • A published Amazon Quick Sight dashboard where you can compare total debt, its components, and recent records over time.
  • Secret Mission: Calculate day-over-day debt movement with a SQL window function and add a daily-change bar chart to the dashboard.

Are there any prerequisites?

You should be comfortable with intermediate SQL plus basic Python.

You need full or administrator-like AWS access with permission to start the 30-day Amazon Quick trial.

Before We Start

Before the hands-on work begins, commit to building a portfolio-ready U.S. debt analytics pipeline that turns public finance data into a useful dashboard. Your finished project demonstrates practical serverless data engineering skills across ingestion, transformation, validation, and visualization.

Prepare the AWS Workspace

Before ingestion can run, your debt pipeline needs one private storage boundary. It also needs a browser-based environment that can package the function later.

You will create an Amazon S3 bucket through the AWS Management Console. You will verify that AWS CloudShell has every command the pipeline needs.

In this step, get ready to:
  • Set the AWS Management Console to US East (N. Virginia).
  • Create a private S3 bucket with one exact reusable name.
  • Verify the CloudShell toolchain for later packaging and deployment.
Create the private S3 bucket

A single private bucket keeps the pipeline's raw data inside one storage boundary. Its exact name becomes a shared reference for every service you configure later.

  • Open the AWS Management Console sign-in page in your browser.
  • Sign in to your AWS account.
  • Select US East (N. Virginia) from the Region menu in the console header.
  • Enter S3 in the console search bar.
  • Select S3 from the search results.
  • Click Create bucket.

Why use one Region?

Keeping the bucket and browser shell in US East (N. Virginia) makes the project easier to follow. Each later AWS resource uses the same regional workspace.

  • Select General purpose as the bucket type.
  • Open the account menu in the console header.
  • Copy the AWS account ID shown in your account details.
  • Replace your-account-id in debt-lake-your-account-id with the account ID you copied.
  • Enter the resulting name in the Bucket name field.
  • Keep Bucket owner enforced selected.
  • Keep every Block Public Access setting enabled.
  • Keep SSE-S3 as the default encryption.
  • Click Create bucket.

Good progress. You should see your new private bucket in the S3 bucket list with US East (N. Virginia) shown as its Region.

Bucket name rejected?

  • Recheck the account ID if S3 rejects the bucket name.
  • Remove any characters other than lowercase letters, numbers, or hyphens from the bucket name.
  • Ask for help with the rejected name through this troubleshooting prompt. Share the rejected name without any credentials in the NextWork community if you still need help.
Verify the CloudShell toolchain

CloudShell gives you an authenticated terminal inside the browser. Its preinstalled tools support the packaging work used later in the pipeline.

  • Confirm that US East (N. Virginia) remains selected in the console header.
  • Open AWS CloudShell from the console header.
  • Keep Bash selected as the shell.

CloudShell is ready when you see a terminal prompt that accepts commands.

Before you run the checks, which four tools do you expect CloudShell to confirm?

  • Verify the installed toolchain by running these commands:
python3 --version
pip3 --version
zip --version
aws --version

What do these checks prove?

  • The python3 --version result confirms that Python is available for packaging the ingestion function.
  • The pip3 --version result confirms that the Python package installer is available.
  • The zip --version result confirms that CloudShell can build a deployment archive.
  • The aws --version result confirms that the AWS CLI is available inside the authenticated shell.

You should see version information after each command. That key setup check is complete because CloudShell now has all four tools required for the later deployment.

Missing version information?

  • Reload AWS CloudShell if the terminal prompt never appears.
  • Confirm that Bash is selected if one command does not print version information.
  • Run the affected command again by copying its exact line from the code block.
  • Use this troubleshooting prompt for targeted help. Share the command output with the NextWork community if the issue continues.
Record and confirm the workspace

Later configurations must point to the exact bucket you created. One changed character would send Lambda or Athena to the wrong S3 location.

  • Return to the S3 bucket list from earlier.
  • Locate the bucket whose name begins with debt-lake-.
  • Copy the bucket's exact name.
  • Record the exact result here: debt-lake-your-account-id.

Before you check, do you expect the recorded name to match the S3 bucket list character for character?

  • Compare debt-lake-your-account-id with the name shown in the S3 bucket list.
  • Switch back to AWS CloudShell from earlier.
  • Confirm that US East (N. Virginia) remains selected in the console header.
  • Review the terminal output for results from all four version checks.

You should see debt-lake-your-account-id in the S3 bucket list. CloudShell should remain open with Bash selected and four version results visible.

✔️ Awesome, I've got everything!

Great. Your private bucket and verified CloudShell environment are ready for the ingestion build.

ⓧ I'd like to double check the full code

This setup step has no project file to compare. Match your workspace with these final states instead.

  • AWS CloudShell: Open in us-east-1 with Bash selected. All four version checks show output.
  • S3 bucket: Created as debt-lake-your-account-id in us-east-1. Bucket owner enforced is selected. Block Public Access is enabled. SSE-S3 is the default encryption.

Your private bucket now has a verified browser shell beside it. Next, you will use that workspace to move Treasury debt records into the raw data layer.

Ingest Treasury Debt Data

Your private Amazon S3 bucket is ready to hold the project data. It is still empty.

Here, you will create an AWS Lambda function that reads the U.S. Treasury Fiscal Data API. You will package it in AWS CloudShell before writing newline-delimited JSON into your bucket.

In this step, get ready to:
  • Create the Lambda function with its runtime settings.
  • Build its deployment package with the pinned Boto3 dependency.
  • Grant raw-prefix access before running a successful ingestion test.
Create the Lambda function

A Lambda function needs a runtime for your Python code. Its execution role also needs permission to write the finished dataset into your bucket.

  • Return to the AWS Management Console from earlier.
  • Open the Functions page of the Lambda console.
  • Choose Create function.
  • Select Author from scratch.
  • Enter treasury-debt-ingestor for Function name.
  • Choose Python 3.14 for Runtime.
  • Leave Architecture set to x86_64.
  • Choose Create function.

Your function shell is ready. Lambda has also created the execution role that you will extend with access to the raw S3 prefix.

  • Choose the Configuration tab.
  • Choose General configuration.
  • Choose Edit.
  • Set Timeout to 60 seconds.
  • Save the general configuration.

Why Increase the Timeout?

The function follows every page of Treasury records before writing one object. The longer timeout gives that pagination loop enough time to finish.

  • Choose Environment variables under Configuration.
  • Choose Edit.
  • Choose Add environment variable.
  • Enter BUCKET_NAME as the key.
  • Enter the exact bucket name you recorded earlier as the value.
  • Save the environment variable.

What Does BUCKET_NAME Do?

The BUCKET_NAME environment variable keeps your bucket name outside the Python source. The handler reads that value when it sends the raw object to S3.

The default role can write logs. A least-privilege AWS Identity and Access Management policy adds permission for one action against your bucket's raw prefix.

  • Switch back to CloudShell from earlier.
  • Create lambda-s3-policy.json with the built-in file editor.
  • Paste this policy into lambda-s3-policy.json:
{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Sid": "WriteTreasuryRawData",
      "Effect": "Allow",
      "Action": "s3:PutObject",
      "Resource": "arn:aws:s3:::your-bucket-name/raw/*"
    }
  ]
}

What Does This Policy Allow?

  • The s3:PutObject action allows the function to write an object.
  • The resource ARN limits that permission to the raw/* prefix in your project bucket.
  • The WriteTreasuryRawData statement name makes the permission easy to identify later.
  • Save lambda-s3-policy.json.
  • Return to treasury-debt-ingestor in the Lambda console.
  • Choose Configuration.
  • Choose Permissions.
  • Choose the role name under Execution role.
  • Choose Add permissions on the role page.
  • Choose Create inline policy.
  • Switch the policy editor to its JSON view.

Keep the CloudShell File Reusable

Keep your-bucket-name unchanged in the saved CloudShell file. Replace it only in the IAM policy editor so the reusable template remains intact.

  • Paste the policy from lambda-s3-policy.json into the IAM policy editor.
  • Replace your-bucket-name with the exact bucket name you recorded earlier.
  • Continue to the policy naming screen.
  • Enter WriteTreasuryRawData as the policy name.
  • Create the inline policy.

You should see WriteTreasuryRawData listed on the role's permissions page. Its resource ends with your bucket name followed by /raw/*.

Policy Not Appearing?

  • Confirm that you opened the execution role attached to treasury-debt-ingestor.
  • Check that the resource ARN contains your exact bucket name.
  • Confirm that the ARN ends with /raw/*.
  • Help me troubleshoot the inline policy for my Lambda execution role.
Build the deployment package

A deployment package places the handler beside every dependency it imports. Pinning Boto3 to 1.43.83 keeps the function's SDK version predictable.

  • Switch back to CloudShell from earlier.
  • Create requirements.txt with the built-in file editor.
  • Paste this dependency pin into requirements.txt:
boto3==1.43.83

What Does This File Control?

The version pin tells pip which Boto3 release belongs in the package. Every deployment built from this file uses the same dependency version.

  • Save requirements.txt.
  • Install the pinned dependency into package by running:
python3 -m pip install boto3==1.43.83 --target package

What Does This Command Do?

The command installs Boto3 1.43.83 inside the package directory. That directory becomes the dependency layer at the root of the deployment archive.

The terminal should report a successful installation. CloudShell now contains a package directory with Boto3 and its supporting libraries.

Dependency Installation Failed?

  • Confirm that CloudShell is still using Bash in US East (N. Virginia).
  • Check that requirements.txt contains the exact Boto3 pin.
  • Retry the installation after CloudShell reconnects if the session lost network access.
  • Help me troubleshoot the Boto3 installation in AWS CloudShell.

Why Package Boto3?

Lambda runtimes include an AWS SDK. Packaging the dependency gives your function control over its Boto3 version and avoids runtime dependency misalignment.

The handler has four logical parts. You will add each part to one lambda_function.py file.

  • Create lambda_function.py with the CloudShell file editor.
  • Paste these imports and configuration values at the top of the file:
import json
import os
from urllib.parse import urlencode
from urllib.request import urlopen

import boto3

BASE_URL = "https://api.fiscaldata.treasury.gov/services/api/fiscal_service/v2/accounting/od/debt_to_penny"
FIELDS = "record_date,debt_held_public_amt,intragov_hold_amt,tot_pub_debt_out_amt"
START_DATE = "2020-01-01"
PAGE_SIZE = 100
OUTPUT_KEY = "raw/debt_to_penny/debt_records.json"

s3 = boto3.session.Session().client("s3")

What Does This Code Set Up?

  • The imports provide JSON serialization, environment-variable access, URL encoding, HTTP retrieval, and S3 access.
  • The constants select four Debt to the Penny fields from 2020-01-01 onward.
  • The OUTPUT_KEY constant fixes every run to the same raw object path.
  • The s3 client uses the function's execution-role credentials.
  • Save lambda_function.py.
  • Check the current Python syntax by running:
python3 -m py_compile lambda_function.py

What Does This Check Prove?

Python compiles the current source into cached bytecode. A return to the prompt without a syntax error confirms that this section parses successfully.

Seeing a Syntax Error?

  • Compare every quote in the constants with the code above.
  • Confirm that import boto3 starts at the left edge.
  • Help me fix the syntax in the configuration section of lambda_function.py.
  • Add this URL builder below the s3 client declaration:
def build_url(page_number):
    params = {
        "fields": FIELDS,
        "filter": f"record_date:gte:{START_DATE}",
        "sort": "record_date",
        "page[number]": page_number,
        "page[size]": PAGE_SIZE,
        "format": "json"
    }
    return f"{BASE_URL}?{urlencode(params)}"

How Does the URL Builder Work?

  • The fields parameter limits each response to the four columns needed by the pipeline.
  • The filter starts the dataset at 2020-01-01.
  • The page values let the caller request one ordered API page at a time.
  • The urlencode() call converts the parameter mapping into a valid query string.
  • Save lambda_function.py.
  • Check the updated Python syntax by running:
python3 -m py_compile lambda_function.py

What Should Happen?

The command should return to the prompt without a syntax error. That result confirms that build_url() is structurally valid.

  • Add this pagination function below build_url():
def fetch_all_records():
    records = []
    page_number = 1

    while True:
        with urlopen(build_url(page_number), timeout=30) as response:
            payload = json.loads(response.read().decode("utf-8"))
            records.extend(payload["data"])
            total_pages = int(payload["meta"]["total-pages"])

            if page_number >= total_pages:
                return records

            page_number += 1

How Does Pagination Work?

  • The records list collects data from every response page.
  • Each request reads its JSON response before adding the page's records.
  • The API's total-pages metadata tells the loop when the final page has arrived.
  • The function returns one combined list after the last page.
  • Save lambda_function.py.
  • Check the pagination code by running:
python3 -m py_compile lambda_function.py

What Does This Check Confirm?

A clean return to the prompt confirms that the loop and its indentation compile. The check catches misplaced indentation before deployment.

Pagination Code Not Compiling?

  • Confirm that the lines inside while True: use consistent indentation.
  • Check that page_number += 1 remains inside the loop.
  • Help me find an indentation problem in fetch_all_records().
  • Add this handler below fetch_all_records():
def lambda_handler(event, context):
    bucket_name = os.environ["BUCKET_NAME"]
    records = fetch_all_records()
    body = "\n".join(
        json.dumps(record, separators=(",", ":")) for record in records
    )

    s3.put_object(
        Bucket=bucket_name,
        Key=OUTPUT_KEY,
        Body=body.encode("utf-8")
    )

    return {
        "statusCode": 200,
        "body": json.dumps(
            {"bucket": bucket_name, "key": OUTPUT_KEY, "records": len(records)}
        )
    }

What Does the Handler Do?

  • The handler reads the destination bucket from BUCKET_NAME.
  • The newline join writes one compact JSON object per line for Athena-compatible raw storage.
  • The put_object() call writes the encoded data to raw/debt_to_penny/debt_records.json.
  • The response reports the destination bucket, object key, and record count.
  • Save the completed lambda_function.py file.
  • Run the final syntax check with:
python3 -m py_compile lambda_function.py

What Should You See?

The terminal should return to the prompt without a syntax error. Your complete handler is now ready for packaging.

✔️ Awesome, I've got everything!

Great. Double-check that lambda_function.py is saved in CloudShell.

ⓧ I'd like to double check the full code

Compare your complete lambda_function.py file with this reference.

import json
import os
from urllib.parse import urlencode
from urllib.request import urlopen

import boto3

BASE_URL = "https://api.fiscaldata.treasury.gov/services/api/fiscal_service/v2/accounting/od/debt_to_penny"
FIELDS = "record_date,debt_held_public_amt,intragov_hold_amt,tot_pub_debt_out_amt"
START_DATE = "2020-01-01"
PAGE_SIZE = 100
OUTPUT_KEY = "raw/debt_to_penny/debt_records.json"

s3 = boto3.session.Session().client("s3")


def build_url(page_number):
    params = {
        "fields": FIELDS,
        "filter": f"record_date:gte:{START_DATE}",
        "sort": "record_date",
        "page[number]": page_number,
        "page[size]": PAGE_SIZE,
        "format": "json"
    }
    return f"{BASE_URL}?{urlencode(params)}"


def fetch_all_records():
    records = []
    page_number = 1

    while True:
        with urlopen(build_url(page_number), timeout=30) as response:
            payload = json.loads(response.read().decode("utf-8"))
            records.extend(payload["data"])
            total_pages = int(payload["meta"]["total-pages"])

            if page_number >= total_pages:
                return records

            page_number += 1


def lambda_handler(event, context):
    bucket_name = os.environ["BUCKET_NAME"]
    records = fetch_all_records()
    body = "\n".join(
        json.dumps(record, separators=(",", ":")) for record in records
    )

    s3.put_object(
        Bucket=bucket_name,
        Key=OUTPUT_KEY,
        Body=body.encode("utf-8")
    )

    return {
        "statusCode": 200,
        "body": json.dumps(
            {"bucket": bucket_name, "key": OUTPUT_KEY, "records": len(records)}
        )
    }
  • Package the installed dependencies at the archive root by running:
cd package && zip -r ../treasury-debt-ingestor.zip . && cd ..

What Does This Command Package?

The command moves into package before creating the archive. This places Boto3 and its supporting libraries at the root of treasury-debt-ingestor.zip.

You should see dependency files listed as the archive is created. The terminal returns to the directory containing your project files.

  • Add the handler file to the archive by running:
zip treasury-debt-ingestor.zip lambda_function.py

Why Add the Handler Separately?

The dependency archive already exists. This command adds lambda_function.py beside those dependencies so Lambda can load lambda_handler.

You should see lambda_function.py added to the archive. CloudShell now contains treasury-debt-ingestor.zip.

Archive Creation Failed?

  • Confirm that package exists in the same CloudShell location as lambda_function.py.
  • Check that the archive name is exactly treasury-debt-ingestor.zip.
  • Help me troubleshoot the Lambda ZIP package in CloudShell.
Deploy the ingestion package

The ZIP file contains the complete runtime payload. Deploying it replaces the starter code in your existing function.

  • Update treasury-debt-ingestor from the CloudShell archive by running:
aws lambda update-function-code --function-name treasury-debt-ingestor --zip-file fileb://treasury-debt-ingestor.zip

What Does This Command Deploy?

The AWS CLI uploads the local ZIP archive to the existing Lambda function. CloudShell uses your current console authentication for the request.

The command should return function configuration in JSON. Its function name should be treasury-debt-ingestor.

Deployment Command Failed?

  • Confirm that CloudShell remains in US East (N. Virginia).
  • Check that treasury-debt-ingestor.zip exists in the current CloudShell location.
  • Confirm that the Lambda function name is treasury-debt-ingestor.
  • Help me troubleshoot the Lambda code deployment command.

The function requires no event input because its API settings come from constants. An empty JSON document is enough to start the ingestion.

  • Return to treasury-debt-ingestor in the Lambda console.
  • Choose the Test tab.
  • Choose Create new event under Test event.
  • Enter treasury-debt-ingestor as the test name.
  • Replace the event JSON with:
{}

Why Is the Event Empty?

The handler does not read values from the event. It gets the destination from BUCKET_NAME before using constants for the API request and object key.

  • Choose Save changes.

Before you choose Test, do you expect the function to finish with a successful response or a permissions failure?

  • Choose Test to invoke the function.

The execution result should show statusCode set to 200. The response body should contain a positive records count.

That is the first working slice of your pipeline. Python has retrieved the Treasury dataset before landing it inside your private data lake.

Test Did Not Succeed?

  • Confirm that BUCKET_NAME matches your exact bucket name.
  • Check that WriteTreasuryRawData is attached to the function's execution role.
  • Confirm that the policy resource ends with /raw/*.
  • Help me troubleshoot my Treasury ingestion Lambda test.
  • Return to the Amazon S3 console from earlier.
  • Select your recorded project bucket.
  • Open the raw prefix.
  • Open the debt_to_penny prefix.
  • Confirm that debt_records.json is listed.

You should see raw/debt_to_penny/debt_records.json in the bucket. The object contains newline-delimited Treasury Debt to the Penny records from 2020-01-01 onward.

Your raw ingestion path is working. Next, you will register this JSON in Athena and expose the financial type problem hidden inside it.

Catalog the Raw JSON

Your AWS Lambda function has placed a newline-delimited JSON file in Amazon S3.

Amazon Athena needs table metadata before it can query those records. You will give the object a source-shaped schema through the AWS Glue Data Catalog.

The final query tests whether those raw financial values are ready for aggregation.

In this step, get ready to:
  • Configure the Athena query-results location.
  • Register the raw JSON file as a catalog table.
  • Test whether the raw financial values support aggregation.
Set the Athena query-results location

Athena stores query results in S3. A dedicated prefix keeps those outputs separate from your raw Treasury data.

  • Use the AWS console search bar to open Amazon Athena.
  • Choose Workgroups from the left navigation pane.
  • Choose the primary workgroup.
  • Choose Edit.
  • Enter s3://debt-lake-your-account-id/athena-results/ in the Location of query result field.
  • Select Override client-side settings under Settings.
  • Choose Save changes.
  • Select primary from the workgroup list.

You should see s3://debt-lake-your-account-id/athena-results/ listed as the query-results location. Good. Your future query outputs now have a predictable home beside the data they analyze.

Can't Save the Workgroup?

  • Confirm that you selected the primary workgroup before choosing Edit.
  • Use the administrator-like AWS access from the prerequisites if your current identity cannot update the workgroup.
  • Help me configure the Athena primary workgroup query-results location.
Register the raw JSON schema

Schema-on-read lets Athena interpret files where they already live. The catalog stores the column definitions while the raw records remain in S3.

  • Switch back to AWS CloudShell from earlier.
  • At the CloudShell prompt, launch the built-in editor for create_raw_table.sql using edit.

You should see an editor tab for create_raw_table.sql.

  • Add the raw table schema by pasting this SQL into create_raw_table.sql:
CREATE DATABASE IF NOT EXISTS finance_portfolio;

CREATE EXTERNAL TABLE IF NOT EXISTS finance_portfolio.debt_raw (
  record_date string,
  debt_held_public_amt string,
  intragov_hold_amt string,
  tot_pub_debt_out_amt string
)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
LOCATION 's3://your-bucket-name/raw/debt_to_penny/'
TBLPROPERTIES ('classification'='json');

What Does This Schema Define?

  • The finance_portfolio database groups the catalog objects for this pipeline.
  • The debt_raw table gives the four source fields queryable column names.
  • Each column uses the string type to preserve the source-shaped values.
  • The org.openx.data.jsonserde.JsonSerDe SerDe reads one JSON record from each line.
  • The LOCATION property points Athena at the raw S3 prefix.
  • Press Cmd+S on macOS or Ctrl+S on Windows to save create_raw_table.sql.

You should see create_raw_table.sql remain open with the complete schema saved.

✔️ Awesome, I've got everything!

Your saved SQL file contains the reusable raw-table definition.

ⓧ I'd like to double check the full code

CREATE DATABASE IF NOT EXISTS finance_portfolio;

CREATE EXTERNAL TABLE IF NOT EXISTS finance_portfolio.debt_raw (
  record_date string,
  debt_held_public_amt string,
  intragov_hold_amt string,
  tot_pub_debt_out_amt string
)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
LOCATION 's3://your-bucket-name/raw/debt_to_penny/'
TBLPROPERTIES ('classification'='json');

The saved file keeps the reusable placeholder. The copy you run in Athena needs the exact name of your project bucket.

  • Switch back to the Athena query editor.
  • Paste the contents of create_raw_table.sql into the query editor.
  • Replace your-bucket-name with debt-lake-your-account-id in the query editor.
  • Choose Run.

You should see both statements finish successfully. That's a key milestone. The catalog now contains finance_portfolio.debt_raw with four SQL-visible columns.

Table Creation Not Completing?

  • Confirm that the query-editor copy uses s3://debt-lake-your-account-id/raw/debt_to_penny/ with the final slash.
  • Confirm that every your-bucket-name placeholder in the query editor was replaced.
  • Help me troubleshoot my Athena external table creation.

The table now exists. A small sample confirms that Athena can read the newline-delimited records through the catalog schema.

  • Inspect the first ten raw records by running this query in Athena:
SELECT * FROM finance_portfolio.debt_raw LIMIT 10;

What Does This Query Check?

  • The query reads every column from finance_portfolio.debt_raw.
  • The LIMIT 10 clause keeps the preview small.

You should see up to ten dated rows with all four Treasury field names. The three financial values remain source-shaped text.

No Raw Rows Returned?

  • Confirm that raw/debt_to_penny/debt_records.json still exists in your project bucket.
  • Compare the table location with s3://debt-lake-your-account-id/raw/debt_to_penny/.
  • Help me find why my Athena raw table returns no rows.
Expose the financial type problem

A readable row proves that the schema can access the file. The next query asks whether the cataloged total-debt field can behave like a financial measure.

  • Before you run it, predict whether Athena can total the field with its current schema:
  • Test the total-debt field by running this query:
SELECT SUM(tot_pub_debt_out_amt) FROM finance_portfolio.debt_raw;

This Failure Is the Evidence

Athena rejects the aggregation because tot_pub_debt_out_amt is cataloged as VARCHAR. The SUM function requires numeric input.

This is the intended shortfall. The raw table preserves the source shape while the next layer needs typed financial fields.

Did the Query Behave Differently?

  • Confirm that tot_pub_debt_out_amt has the string type in the table schema.
  • Recreate the table from the exact create_raw_table.sql definition if its schema was changed previously.
  • Help me reproduce the Athena VARCHAR aggregation failure.

Your raw layer is now queryable. Next, you will build a typed Parquet layer that supports financial aggregation.

Build the Curated Parquet Layer

Your last query failed because Amazon Athena sees each financial amount as VARCHAR. Those strings block financial aggregation.

This step uses CTAS to cast the source fields into analytics-ready types. The query writes SNAPPY-compressed Parquet into year-based Amazon S3 partitions registered in the AWS Glue Data Catalog.

In this step, get ready to:
  • Create a curated Parquet table with typed finance fields.
  • Confirm the catalog schema plus the year-partitioned S3 layout.
  • Run SQL quality checks against the curated table.
Create the curated table

A CTAS statement creates a table from a query result. Here it controls storage while casting the source strings.

  • In the CloudShell file editor from earlier, create create_curated_table.sql in your home folder by pasting this SQL into a new file:
CREATE TABLE finance_portfolio.debt_curated
WITH (
  format='PARQUET',
  write_compression='SNAPPY',
  partitioned_by=ARRAY['record_year'],
  external_location='s3://your-bucket-name/curated/debt/'
) AS
SELECT
  CAST(record_date AS DATE) AS record_date,
  CAST(debt_held_public_amt AS DECIMAL(20,2)) AS debt_held_public_amt,
  CAST(intragov_hold_amt AS DECIMAL(20,2)) AS intragov_hold_amt,
  CAST(tot_pub_debt_out_amt AS DECIMAL(20,2)) AS tot_pub_debt_out_amt,
  CAST(substr(record_date, 1, 4) AS INTEGER) AS record_year
FROM finance_portfolio.debt_raw;

What does this SQL do?

  • The format='PARQUET' property writes a columnar reporting format.
  • The write_compression='SNAPPY' property reduces the stored file size.
  • The partitioned_by=ARRAY['record_year'] property organizes the output by year.
  • The CAST expressions convert the source strings into DATE, DECIMAL(20,2), and INTEGER values.

Why CTAS for this pipeline?

Athena CTAS performs the raw-to-curated transformation with SQL. An AWS Glue Spark job would add runtime configuration that this compact dataset does not need.

  • Save create_curated_table.sql.

You should see create_curated_table.sql in the CloudShell file list.

✔️ Awesome, I've got everything!

Your reusable CTAS template is saved in CloudShell.

ⓧ I'd like to double check the full code

The complete create_curated_table.sql file should match this reference.

CREATE TABLE finance_portfolio.debt_curated
WITH (
  format='PARQUET',
  write_compression='SNAPPY',
  partitioned_by=ARRAY['record_year'],
  external_location='s3://your-bucket-name/curated/debt/'
) AS
SELECT
  CAST(record_date AS DATE) AS record_date,
  CAST(debt_held_public_amt AS DECIMAL(20,2)) AS debt_held_public_amt,
  CAST(intragov_hold_amt AS DECIMAL(20,2)) AS intragov_hold_amt,
  CAST(tot_pub_debt_out_amt AS DECIMAL(20,2)) AS tot_pub_debt_out_amt,
  CAST(substr(record_date, 1, 4) AS INTEGER) AS record_year
FROM finance_portfolio.debt_raw;
  • Copy the full contents of create_curated_table.sql from the CloudShell editor.
  • Return to Amazon S3 in the AWS Management Console.
  • Select the project bucket you created in Step 1.
  • Confirm that curated/debt/ contains no objects.
  • Switch back to Athena.
  • Create a new query tab.
  • Paste the copied SQL into the query editor.
  • Replace your-bucket-name with your exact project bucket name.

Before you run the CTAS query, what changes do you expect Athena to make in the catalog and S3?

  • Run the CTAS query.
  • Refresh the Athena table list after the query succeeds.

You should see debt_curated under finance_portfolio.

You have crossed the key data-engineering boundary. The raw strings now feed a typed curated table.

CTAS query not succeeding?

A retry may require clearing only incomplete curated output. Your raw data remains safe.

  • Confirm that the external_location value uses your exact project bucket.
  • Remove partial objects only from curated/debt/ if an earlier attempt wrote files.
  • Retry the query after the curated prefix is empty.
  • Help me troubleshoot my Athena CTAS query.
Inspect the curated layout

The catalog schema proves that Athena recognizes each data type. The S3 layout proves that CTAS wrote partitioned files.

  • In Athena, select finance_portfolio in the database list.
  • Expand debt_curated to inspect its columns.
  • Use Athena's table preview for debt_curated.

You should see dated debt records returned from the curated table.

The record_date column should use DATE. The three amount columns should use DECIMAL(20,2).

The record_year column should use INTEGER.

  • Return to your project bucket in Amazon S3.
  • Open the curated/ prefix.
  • Open the debt/ prefix.
  • Inspect the folders whose names begin with record_year=.

You should see one or more year partitions containing Parquet objects.

Can't see the curated files?

  • Refresh the S3 object list after the CTAS query finishes.
  • Confirm that you are viewing the same project bucket used in external_location.
  • Return to Athena to confirm that the CTAS query succeeded.
  • Help me find the Parquet partitions in S3.
Run the quality checks

The curated table needs proof that the transformation preserved the dataset. This query checks coverage plus missing total-debt values.

  • Return to the CloudShell file editor.
  • Create quality_checks.sql in your home folder by pasting this SQL into a new file:
SELECT
  COUNT(*) AS row_count,
  MIN(record_date) AS first_record_date,
  MAX(record_date) AS latest_record_date,
  SUM(CASE WHEN tot_pub_debt_out_amt IS NULL THEN 1 ELSE 0 END) AS null_total_debt_rows
FROM finance_portfolio.debt_curated;

What does this SQL check?

  • The COUNT(*) expression confirms that the curated table contains rows.
  • The MIN(record_date) expression finds the earliest curated date.
  • The MAX(record_date) expression finds the latest curated date.
  • The SUM(CASE WHEN tot_pub_debt_out_amt IS NULL THEN 1 ELSE 0 END) expression counts missing total-debt values.
  • Save quality_checks.sql.

You should see quality_checks.sql in the CloudShell file list.

✔️ Awesome, I've got everything!

Your reusable quality-check query is saved in CloudShell.

ⓧ I'd like to double check the full code

The complete quality_checks.sql file should match this reference.

SELECT
  COUNT(*) AS row_count,
  MIN(record_date) AS first_record_date,
  MAX(record_date) AS latest_record_date,
  SUM(CASE WHEN tot_pub_debt_out_amt IS NULL THEN 1 ELSE 0 END) AS null_total_debt_rows
FROM finance_portfolio.debt_curated;
  • Switch back to Athena.
  • Create a new query tab.
  • Paste the contents of quality_checks.sql into the query editor.

Before you run the quality check, what result would prove that the curated layer is ready for reporting?

  • Run the quality-check query.

You should see a positive row_count. The date range should begin in 2020.

The null_total_debt_rows value should be 0. You have now proven that the curated layer contains typed total-debt values for reporting.

Quality check not matching?

  • Confirm that the query reads from finance_portfolio.debt_curated.
  • Check the curated schema for the expected DATE plus DECIMAL(20,2) types.
  • Return to the CTAS query if the curated table contains no rows.
  • Help me troubleshoot my Athena quality-check result.

Your curated Parquet layer now passes its quality checks. Next, you will turn these typed debt records into a published Amazon Quick Sight dashboard.

Publish the Debt Analytics Dashboard

Your curated Parquet table now passes its quality checks. The typed debt records are ready to become something another person can explore.

In this step, you will use Amazon Quick Sight inside Amazon Quick to turn the Athena table into a published dashboard. Direct query keeps each visual connected to the curated data.

In this step, get ready to:
  • Start the Amazon Quick trial and authorize access to the project data.
  • Create a direct-query dataset from the curated Athena table.
  • Build three visuals and publish the U.S. Debt Analytics dashboard.
Start the trial and authorize your data

Amazon Quick needs an active subscription before you can create an Amazon Quick Sight analysis. The trial gives you 30 days to build and review the dashboard.

Know the trial boundary

This is the billing-sensitive moment in the project, so take a minute to understand what starts today.

The trial covers up to 25 users for 30 days.

After the trial, Amazon Quick Enterprise costs $40 per user per month plus a $250 per-account monthly infrastructure fee.

A BI-only Amazon Quick Sight Author subscription costs $24 per user per month.

  • Confirm that the AWS console Region is US East (N. Virginia).
  • Use the AWS console search bar to find Amazon Quick.
  • Select Amazon Quick from the search results.
  • Start the 30-day trial from the sign-up page.
  • Choose US East (N. Virginia) as the initial SPICE Region.
  • Complete the account details requested on the sign-up page.

You should reach the Amazon Quick home page with the trial active in your selected Region.

Authorization lets Amazon Quick reach Athena metadata and the files in Amazon S3. Both permissions are required for the dashboard to query the curated table.

  • Open the profile menu in Amazon Quick.
  • Select Manage QuickSight.
  • Select Security and permissions.
  • Select Add or remove.
  • Enable Amazon Athena.
  • Select Next.

You should now see the S3 bucket authorization controls.

  • Enable S3 buckets.
  • Select the project bucket named debt-lake-your-account-id.
  • Select Finish.
  • Select Update.

Good work. Your BI workspace can now reach the catalog metadata and the curated Parquet files.

Can't authorize the project bucket?

  • Confirm that you are managing Amazon Quick in the same AWS account as the project bucket.
  • Check that debt-lake-your-account-id is selected under the S3 bucket authorization controls.
  • Return to Security and permissions if either Athena or S3 access is missing.
  • Help me troubleshoot Amazon Quick access to my Athena table and S3 bucket.
Create a direct-query Athena dataset

The AwsDataCatalog catalog exposes your AWS Glue Data Catalog tables to Amazon Quick. Direct query reads from finance_portfolio.debt_curated without creating a separate imported copy.

Why direct query?

Direct query keeps the visuals connected to the Athena table. New curated data becomes available without maintaining a separate SPICE refresh workflow.

This approach keeps the project focused on the data lake as the reporting source.

  • Select Data in Amazon Quick.
  • Select Create.
  • Select New dataset.
  • Select Athena as the data source.
  • Enter Debt Analytics Athena as the connection name.
  • Run the connection validation.

You should see confirmation that Amazon Quick can connect to Athena.

  • Create the validated data source.
  • Select AwsDataCatalog as the catalog.
  • Select finance_portfolio as the database.
  • Select debt_curated as the table.
  • Choose direct query mode when Amazon Quick asks how to use the table.
  • Select Visualize.

You should land in the analysis workspace with record_date in the field list. You should also see debt_held_public_amt, intragov_hold_amt, and tot_pub_debt_out_amt.

Can't see the curated table?

  • Confirm that the selected catalog is AwsDataCatalog.
  • Confirm that the selected database is finance_portfolio.
  • Return to Amazon Quick permissions if debt_curated is missing.
  • Help me find my curated Athena table in Amazon Quick.
Build and publish the dashboard

An analysis is the editable workspace where you arrange fields into visuals. Publishing turns that analysis into the dashboard another person can open.

Start with the total-debt trend. This visual shows how the outstanding balance changes over the full date range.

  • Select Add in the analysis workspace.
  • Select Add visual.
  • Choose a line chart as the visual type.
  • Drag record_date from the Data pane into the horizontal-axis field well.
  • Drag tot_pub_debt_out_amt into the value field well.

You should see a dated line that tracks total public debt from 2020 onward.

Add the component comparison next. Two lines reveal how debt held by the public compares with intragovernmental holdings.

  • Select Add.
  • Select Add visual.
  • Choose a line chart as the visual type.
  • Drag record_date into the horizontal-axis field well.
  • Drag debt_held_public_amt into the value field well.
  • Drag intragov_hold_amt into the same value field well.

You should see two lines across the same date range. Each line represents one component of total debt.

Finish the analysis with a recent-record table. Sorting by date makes the newest Treasury observations easy to verify.

  • Select Add.
  • Select Add visual.
  • Choose a table as the visual type.
  • Drag record_date into the table field well.
  • Drag debt_held_public_amt into the table field well.
  • Drag intragov_hold_amt into the table field well.
  • Drag tot_pub_debt_out_amt into the table field well.
  • Use the record_date field menu to sort the table in descending order.

You should see the newest dated records at the top of the table with all three financial measures.

Visuals blank or missing fields?

  • Confirm that the analysis uses the direct-query dataset for finance_portfolio.debt_curated.
  • Check that record_date is used as the date field for both line charts.
  • Check that each amount field is placed in a value field well.
  • Help me troubleshoot blank Amazon Quick Sight visuals built from Athena.
  • Select Publish.
  • Select New dashboard.
  • Enter U.S. Debt Analytics as the dashboard name.
  • Select Publish dashboard.

Before you check the published view, which three visuals do you expect to find?

  • Open the published U.S. Debt Analytics dashboard.

You should see a total-debt line chart and a component-comparison line chart. You should also see a recent-record table sorted by record_date in descending order.

You have turned the curated finance table into a published analytical result that someone else can inspect.

Secret mission

Visualize Daily Debt Movement

Extend your published dashboard with a metric that reveals how much total debt moved from one business date to the next. Build the metric with an Athena window function. Publish it as a vertical daily-change chart in Amazon Quick Sight.

Clean Up Your Resources

Amazon Quick Enterprise costs $40 per user per month after the trial. It also adds a $250 per-account monthly infrastructure fee.

A BI-only Amazon Quick Sight Author subscription costs $24 per user per month.

Choose Pause or Delete before the trial ends if you do not want subscription charges.

Nice Work!

Nice Work!

You did it! Your U.S. debt analytics pipeline now turns public Treasury records into a published dashboard. It reveals long-term trends plus daily movement.

You've learned how to:

  • Build a serverless ingestion path with AWS Lambda that retrieves Debt to the Penny records from the U.S. Treasury Fiscal Data API before writing newline-delimited JSON to a private Amazon S3 raw layer.
  • Diagnose a realistic data type failure in Amazon Athena when raw financial strings block aggregation. Use CTAS to cast dates into DATE values. Convert the financial fields into DECIMAL(20,2) values. Store the result as partitioned Parquet registered in the AWS Glue Data Catalog.
  • Publish an interactive Amazon Quick Sight dashboard that queries the curated Athena table directly. The finished dashboard shows the total-debt trend across record_date. It compares debt_held_public_amt with intragov_hold_amt. Its recent-record table surfaces the newest dates first.
  • Complete the optional Secret Mission with a SQL window function based on LAG that calculates daily_change_amt. Add the result to the republished U.S. Debt Analytics dashboard as a vertical daily-change bar chart.

Ready to quiz yourself?