Build a Healthcare Data Lake on AWS

Build a validated healthcare lake with Athena, Parquet, and Quick Sight.

Introduction

30 Second Summary

A polished dashboard can hide messy source data. Without a visible trail from the original file to each chart, the numbers are hard to trust.

In this project, you will build a healthcare data lake pipeline on AWS using the public UCI Heart Failure Clinical Records dataset. The raw CSV lands in Amazon S3 before AWS Glue Data Catalog plus Amazon Athena turn it into typed Parquet for a published Amazon Quick Sight dashboard backed by 299 validated records.

What You'll Build

Open your finished dashboard to see a total-record KPI of 299 beside bar charts for death-event distribution plus average serum creatinine.

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

  • A raw data trail where you can inspect the original CSV through Athena while every field remains visibly string typed.
  • A curated Parquet table with explicit numeric types plus Snappy compression for dependable analysis.
  • Reconciliation checks that show 299 source records beside 299 curated records. Contract checks expose null numeric values or invalid binary codes.
  • Secret Mission: Create a reusable quality-issues view that returns only records violating the pipeline contract.

Are there any prerequisites?

You need an AWS account with broad permissions plus a payment card. The browser-based project uses Amazon Quick Sight Author, which costs $24 per user/month after its automatic 30-day trial.

Before We Start

This checkpoint is where you commit to the healthcare data pipeline you are about to build. The dashboard is the consumer of the trusted data your engineering work produces.

Set Up the Raw Zone

Every trustworthy data lake starts with an unchanged source file in a controlled landing zone. Mixing source files with transformed outputs weakens every check that follows.

You'll anchor the project in one AWS Region. You'll activate Amazon Quick Sight for the later dashboard.

You'll also place the public dataset in a private Amazon S3 raw path. That unchanged object becomes the pipeline's source.

In this step, get ready to:
  • Activate one Amazon Quick Sight Author subscription in us-east-1.
  • Download the public UCI heart-failure dataset.
  • Upload the source CSV to a private Amazon S3 raw zone.
Activate the analytics subscription

Quick Sight uses SPICE as its in-memory data engine. Selecting the initial SPICE Region keeps the dashboard layer aligned with the rest of this project.

  • Sign in to the AWS Management Console using the account with broad permissions.
  • Select the Region menu in the top navigation bar.
  • Choose US East (N. Virginia) with Region code us-east-1.
  • Enter Quick in the console search bar.
  • Select Amazon Quick Sight from the results.

Know the subscription cost

Activating Author access starts an automatic 30-day trial for a first subscription. After the trial, the monthly Author plan costs $24 per user each month.

You need only one Author account for this project. Terminating the Quick Sight account later stops the recurring subscription.

  • Select the monthly plan for one Author account.
  • Choose us-east-1 as the initial SPICE Region.
  • Choose the recommended password-based or single sign-on authentication method for your account.
  • Choose Create account.
  • Choose Go to Quick after account creation completes.

Good progress. Quick Sight is now ready to become the pipeline's dashboard layer.

Quick Sight account not opening?

Confirm that the AWS console still shows us-east-1. Your AWS identity also needs permission to create the Quick Sight subscription.

Help me diagnose my Amazon Quick Sight Author signup issue.

Download the public source file

The UCI Machine Learning Repository provides the public Heart Failure Clinical Records dataset used by this pipeline. Its CSV becomes the unchanged source object in your raw zone.

Use only public data

Use only the public UCI file in this project. Never upload real patient data or protected health information.

  • Open the official Heart Failure Clinical Records dataset page in your browser.
  • Download the dataset ZIP using the page's download control.
  • Open your browser's Downloads list.
  • Select the downloaded ZIP file to extract it.

macOS extracts the archive into a folder beside the downloaded ZIP file.

  • Open the extracted dataset folder.
  • Confirm that the folder contains heart_failure_clinical_records_dataset.csv.

The source is ready. You now have the exact public CSV that the raw zone needs.

CSV missing after extraction?

Confirm that you downloaded the ZIP from the official UCI dataset page. Check the extracted folder for the filename ending in .csv.

Help me find the extracted UCI CSV on macOS.

Create the private raw zone

A raw zone preserves source data exactly as received. This gives every later transformation a stable starting point.

How the bucket stays private

New S3 buckets start with all four Block Public Access settings enabled. They also use SSE-S3 as the base encryption configuration.

Keeping these defaults protects the healthcare sample from public access. The bucket remains dedicated to this pipeline.

  • Return to the AWS Management Console from Quick Sight.
  • Select your account menu in the top navigation bar.
  • Record the displayed 12-digit account ID as <your-account-id>.
  • Enter your complete bucket name here: healthcare-lake-<your-account-id>.
  • Enter S3 in the console search bar.
  • Select Amazon S3 from the results.
  • Select General purpose buckets.
  • Select Create bucket.

What are you setting now?

The account ID suffix connects the bucket name to your AWS account. The selected Region keeps the source beside the analytics services used later.

  • Enter healthcare-lake-<your-account-id> in the Bucket name field.
  • Confirm that the AWS Region field shows us-east-1.
  • Confirm that all four Block Public Access settings remain enabled.
  • Confirm that the default encryption setting remains SSE-S3.
  • Select Create bucket.

That's the storage boundary in place. Your private bucket can now separate source data from every output the pipeline creates.

Bucket creation failed?

Confirm that the bucket name uses lowercase letters plus hyphens. Check that your full 12-digit account ID replaced the placeholder in the name.

Help me fix my Amazon S3 bucket creation issue.

  • Select the bucket named healthcare-lake-<your-account-id>.
  • Select Create folder.
  • Enter raw in the Folder name field.
  • Select Create folder.
  • Open the raw folder.
  • Select Create folder.
  • Enter heart_failure in the Folder name field.
  • Select Create folder.

How the lake prefixes fit together

The source file belongs under raw/heart_failure/. The pipeline reserves curated/heart_failure/ for typed output.

The athena-results/ prefix is reserved for query results. Only the raw path contains an object at this point.

  • Open the heart_failure folder.
  • Select Upload.
  • Select Add file.
  • Choose heart_failure_clinical_records_dataset.csv from the extracted UCI folder.

Before you start the upload, which folder path and filename do you expect to see when it finishes?

  • Select Upload.
  • Wait for the Upload: status page to report success.
  • Return to the bucket's Objects view.
  • Open the raw folder.
  • Open the heart_failure folder.

You'll see heart_failure_clinical_records_dataset.csv listed inside raw/heart_failure/. The upload status confirms that the source object arrived successfully.

CSV not visible in the raw path?

Confirm that you opened raw/heart_failure/ inside the correct bucket. Check the upload status for a failed file transfer before uploading the CSV again.

Help me diagnose my missing S3 raw-zone object.

Your private raw zone now holds the unchanged healthcare dataset. Next, you'll register that source in the catalog and query its string-typed records.

Register and Query the Raw CSV

Your public CSV now has a private landing path in Amazon S3. The pipeline can preserve that source before any transformation.

Amazon Athena lets you inspect the untouched file through metadata. The result reveals whether its field types support dependable metrics. It also reveals whether coded values carry clear meaning.

In this step, get ready to:
  • Route Athena query results into the lake's dedicated results prefix.
  • Register the raw CSV in AWS Glue Data Catalog.
  • Expose the raw table's schema limitations through queries.
Route Athena query results

Athena stores a result file for each query. A dedicated results prefix keeps those files separate from the raw healthcare data.

  • Search for Athena in the AWS Management Console search bar.
  • Select Amazon Athena from the search results.
  • Select the primary workgroup.
  • Choose Settings.
  • Choose Manage.
  • Enter s3://healthcare-lake-<your-account-id>/athena-results/ in Location of query result.
  • Choose Save.

Why separate query results?

The raw prefix holds the source file exactly as it arrived. The results prefix holds files generated by Athena queries.

That routing is locked in. Athena now writes query output away from the source CSV.

Unable to save the result location?

  • Confirm that the selected workgroup is primary.
  • Confirm that the location ends with /athena-results/.
  • Confirm that the bucket name matches your existing S3 bucket.

Help me troubleshoot the Athena query-results location.

Register the raw table

A schema-on-read table describes data where it already lives. Athena uses this metadata to interpret the CSV without moving the source file.

  • Select Query editor in Athena.
  • Add the database statement to the query editor by pasting this code:
CREATE DATABASE IF NOT EXISTS healthcare_lake;

What does this statement create?

The statement creates the healthcare_lake metadata database when it does not already exist. The database provides a namespace for the raw table.

  • Choose Run in the query editor.

You'll see a successful query status. The healthcare_lake database is now available to hold the pipeline's table metadata.

Database statement did not succeed?

  • Confirm that the query editor contains the complete database statement.
  • Confirm that the AWS console still shows us-east-1.

Help me troubleshoot the Athena database statement.

  • Clear the database statement from the query editor.
  • Add the raw-table statement to the query editor by pasting this code:
CREATE EXTERNAL TABLE IF NOT EXISTS healthcare_lake.heart_failure_raw (
  age string,
  anaemia string,
  creatinine_phosphokinase string,
  diabetes string,
  ejection_fraction string,
  high_blood_pressure string,
  platelets string,
  serum_creatinine string,
  serum_sodium string,
  sex string,
  smoking string,
  time string,
  death_event string
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS TEXTFILE
LOCATION 's3://healthcare-lake-your-account-id/raw/heart_failure/'
TBLPROPERTIES ('skip.header.line.count'='1');

What does this table definition do?

  • The statement registers heart_failure_raw as external metadata without copying the CSV.
  • Every column uses string to preserve the raw source representation.
  • FIELDS TERMINATED BY ',' tells Athena to separate each row at its commas.
  • STORED AS TEXTFILE identifies the source as text data.
  • LOCATION limits the table to the raw/heart_failure/ prefix.
  • skip.header.line.count excludes the CSV header from query results.
  • Replace healthcare-lake-your-account-id with healthcare-lake-<your-account-id> in the query editor.
  • Choose Run.
  • Expand heart_failure_raw in the query editor's data pane.

You'll see all thirteen columns listed as string. Athena has registered the source structure in the catalog.

Raw table missing from the data pane?

  • Confirm that you replaced the sample bucket name before running the statement.
  • Confirm that the LOCATION value ends with /raw/heart_failure/.
  • Refresh the query editor's data pane.

Help me troubleshoot the missing raw Athena table.

Query the raw layer

The table definition makes the landing file queryable. A preview now tests what the raw layer can provide before any curation rules are applied.

Take a moment to predict whether Athena will infer analytics-ready field types.

  • Replace the current statement with the raw preview query below:
SELECT *
FROM healthcare_lake.heart_failure_raw
LIMIT 10;

What does this query test?

  • SELECT * requests every column from the raw table.
  • LIMIT 10 keeps the preview to ten records.
  • Choose Run.

You'll see ten healthcare records. The table schema shows every column as string.

Why the raw result falls short

The table preserves the CSV as landed. Every field remains a string.

The binary death_event values also carry no descriptive label. Reliable downstream metrics need a stronger data contract.

Before you run the final query, predict whether grouped raw codes will explain their own business meaning.

  • Replace the preview query with the grouped-count query below:
SELECT death_event, COUNT(*) AS patient_count
FROM healthcare_lake.heart_failure_raw
GROUP BY death_event
ORDER BY death_event;

What does this query measure?

  • GROUP BY death_event collects records with the same raw code.
  • COUNT(*) AS patient_count counts the records in each code group.
  • ORDER BY death_event sorts the groups by their stored code.
  • Choose Run.

You'll see records grouped by death_event. The patient_count values cover all 299 source records.

No rows or seeing the header?

  • Confirm that the table location uses your actual S3 bucket name.
  • Confirm that heart_failure_clinical_records_dataset.csv remains under raw/heart_failure/.
  • Confirm that the table definition includes skip.header.line.count with the value 1.

Help me troubleshoot the raw Athena query results.

✔️ Awesome, I've got everything!

You have run every statement required for the raw layer. The preview confirms ten records while the schema confirms that every source column remains a string.

ⓧ I'd like to double check the full code

CREATE DATABASE IF NOT EXISTS healthcare_lake;

CREATE EXTERNAL TABLE IF NOT EXISTS healthcare_lake.heart_failure_raw (
  age string,
  anaemia string,
  creatinine_phosphokinase string,
  diabetes string,
  ejection_fraction string,
  high_blood_pressure string,
  platelets string,
  serum_creatinine string,
  serum_sodium string,
  sex string,
  smoking string,
  time string,
  death_event string
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS TEXTFILE
LOCATION 's3://healthcare-lake-your-account-id/raw/heart_failure/'
TBLPROPERTIES ('skip.header.line.count'='1');

SELECT *
FROM healthcare_lake.heart_failure_raw
LIMIT 10;

SELECT death_event, COUNT(*) AS patient_count
FROM healthcare_lake.heart_failure_raw
GROUP BY death_event
ORDER BY death_event;

Your raw layer now exposes exactly why source data needs a contract. Next, you'll convert this raw representation into the curated layer.

Transform Data into Curated Parquet

Your raw preview in Amazon Athena proved that the landing file is queryable. Every value still arrives as a string.

This step applies an explicit data contract through CTAS. The query writes typed columns as Parquet with Snappy compression in the curated zone.

In this step, get ready to:
  • Confirm that the curated destination is empty.
  • Transform the raw strings into typed Parquet data.
  • Verify the storage format, schema, and row count.
Create the curated Parquet table

CTAS creates the catalog table while writing its data files to Amazon S3. The destination must be empty because Athena does not overwrite existing data there.

Why Athena CTAS?

For this 299-row batch, CTAS applies the data contract in one query. AWS Glue ETL would add Spark job configuration that this small transformation does not need.

  • Use the AWS Management Console search bar to return to Amazon S3.
  • Select General purpose buckets.
  • Select healthcare-lake-<your-account-id>.
  • Select the curated folder.
  • Select the heart_failure folder.
  • Confirm that the object list is empty.

The empty object list gives CTAS a clean destination for the curated files.

  • Return to the Athena query editor from earlier.
  • Copy the following CTAS statement into a blank query tab:
CREATE TABLE healthcare_lake.heart_failure_curated
WITH (
  format = 'PARQUET',
  write_compression = 'SNAPPY',
  external_location = 's3://healthcare-lake-your-account-id/curated/heart_failure/'
) AS
SELECT
  CAST(age AS DOUBLE) AS age_years,
  CAST(anaemia AS INTEGER) AS anaemia_code,
  CAST(creatinine_phosphokinase AS INTEGER) AS cpk_mcg_per_l,
  CAST(diabetes AS INTEGER) AS diabetes_code,
  CAST(ejection_fraction AS INTEGER) AS ejection_fraction_percent,
  CAST(high_blood_pressure AS INTEGER) AS high_blood_pressure_code,
  CAST(platelets AS DOUBLE) AS platelets_kiloplatelets_per_ml,
  CAST(serum_creatinine AS DOUBLE) AS serum_creatinine_mg_dl,
  CAST(serum_sodium AS INTEGER) AS serum_sodium_meq_l,
  CAST(sex AS INTEGER) AS sex_code,
  CAST(smoking AS INTEGER) AS smoking_code,
  CAST(time AS INTEGER) AS follow_up_days,
  CAST(death_event AS INTEGER) AS death_event_code
FROM healthcare_lake.heart_failure_raw;

What does this query do?

  • The format = 'PARQUET' property selects the columnar output format.
  • The write_compression = 'SNAPPY' property compresses the generated files.
  • The external_location property directs those files to the curated prefix.
  • Each CAST converts a raw string into its declared numeric type.
  • Each alias records the field's meaning in the curated schema.
  • The FROM healthcare_lake.heart_failure_raw clause keeps the raw table as the transformation source.
  • Replace healthcare-lake-your-account-id with the exact name of your project bucket.
  • Run the CTAS statement in Athena.

You have crossed the pipeline's main boundary. Athena has created a typed curated table from the raw strings.

  • Return to the Amazon S3 tab from earlier.
  • Refresh the curated/heart_failure/ prefix.

You'll see generated data objects in the curated prefix. Those objects are the Parquet output written by CTAS.

Did the CTAS query fail?

Confirm that you replaced the sample bucket name with your exact bucket name. Confirm that the curated prefix contains no existing objects.

A previous attempt can also leave behind the curated table metadata. Remove that table metadata plus its curated objects before running the statement again.

Help me diagnose my Athena CTAS failure.

Inspect the curated schema and row count

CTAS automatically registers the new table in the AWS Glue Data Catalog. Its metadata now describes the typed columns plus the Parquet storage configuration.

  • Use the console search bar to open AWS Glue.
  • Select the healthcare_lake database.
  • Select the heart_failure_curated table.
  • Confirm that the table format is Parquet.
  • Confirm that the compression type is Snappy.

What does the storage metadata prove?

Parquet stores the curated values by column for analytical queries. Snappy reduces the stored size while keeping the data available to Athena.

  • Review the column schema for heart_failure_curated.
  • Confirm that age_years, platelets_kiloplatelets_per_ml, and serum_creatinine_mg_dl use double.
  • Confirm that the remaining measurement columns use integer.
  • Confirm that every field ending in _code uses integer.

Before you run the final check, how many rows do you expect after a transformation that preserves the complete batch?

  • Return to Athena and count the curated records by running this query:
SELECT COUNT(*) AS curated_rows
FROM healthcare_lake.heart_failure_curated;

What does this query check?

  • The COUNT(*) expression counts every row in the curated table.
  • The curated_rows alias makes the result column easy to identify.

You'll see 299 under curated_rows. That gives your analytics layer a complete curated batch.

Is the curated count different?

Confirm that the CTAS statement reads from healthcare_lake.heart_failure_raw. Check that no filter was added to its SELECT query.

Help me investigate the curated row count.

✔️ Awesome, I've got everything!

Your healthcare_pipeline.sql now includes the curated transformation plus its row-count check.

ⓧ I'd like to double check the full code

  • Compare healthcare_pipeline.sql with the complete reference below.
CREATE DATABASE IF NOT EXISTS healthcare_lake;

CREATE EXTERNAL TABLE IF NOT EXISTS healthcare_lake.heart_failure_raw (
  age string,
  anaemia string,
  creatinine_phosphokinase string,
  diabetes string,
  ejection_fraction string,
  high_blood_pressure string,
  platelets string,
  serum_creatinine string,
  serum_sodium string,
  sex string,
  smoking string,
  time string,
  death_event string
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS TEXTFILE
LOCATION 's3://healthcare-lake-your-account-id/raw/heart_failure/'
TBLPROPERTIES ('skip.header.line.count'='1');

SELECT *
FROM healthcare_lake.heart_failure_raw
LIMIT 10;

SELECT death_event, COUNT(*) AS patient_count
FROM healthcare_lake.heart_failure_raw
GROUP BY death_event
ORDER BY death_event;

CREATE TABLE healthcare_lake.heart_failure_curated
WITH (
  format = 'PARQUET',
  write_compression = 'SNAPPY',
  external_location = 's3://healthcare-lake-your-account-id/curated/heart_failure/'
) AS
SELECT
  CAST(age AS DOUBLE) AS age_years,
  CAST(anaemia AS INTEGER) AS anaemia_code,
  CAST(creatinine_phosphokinase AS INTEGER) AS cpk_mcg_per_l,
  CAST(diabetes AS INTEGER) AS diabetes_code,
  CAST(ejection_fraction AS INTEGER) AS ejection_fraction_percent,
  CAST(high_blood_pressure AS INTEGER) AS high_blood_pressure_code,
  CAST(platelets AS DOUBLE) AS platelets_kiloplatelets_per_ml,
  CAST(serum_creatinine AS DOUBLE) AS serum_creatinine_mg_dl,
  CAST(serum_sodium AS INTEGER) AS serum_sodium_meq_l,
  CAST(sex AS INTEGER) AS sex_code,
  CAST(smoking AS INTEGER) AS smoking_code,
  CAST(time AS INTEGER) AS follow_up_days,
  CAST(death_event AS INTEGER) AS death_event_code
FROM healthcare_lake.heart_failure_raw;

SELECT COUNT(*) AS curated_rows
FROM healthcare_lake.heart_failure_curated;

Your curated table now holds 299 typed records in compressed Parquet. Next, you'll reconcile the row counts before testing the binary value contract.

Validate the Data Contract

Your curated Parquet table now returns 299 typed records in Amazon Athena. The next goal is to prove that curation preserved the source batch.

A successful transformation can still hide dropped records. It can also carry values that violate the declared schema.

Reconciliation compares the two table counts. Quality checks turn the data contract into repeatable evidence.

In this step, get ready to:
  • Compare the raw and curated row counts.
  • Check required numeric fields for null values.
  • Check binary fields for values outside 0 or 1.
Reconcile the source and target

A reconciliation check compares the number of records before and after transformation. Matching counts prove that the curated layer preserved the complete source batch.

  • Append the reconciliation statement to healthcare_pipeline.sql by copying this code below the existing curated_rows query:
SELECT
  (SELECT COUNT(*) FROM healthcare_lake.heart_failure_raw) AS raw_rows,
  (SELECT COUNT(*) FROM healthcare_lake.heart_failure_curated) AS curated_rows;

What Does This Query Prove?

  • The first subquery counts records in healthcare_lake.heart_failure_raw.
  • The second subquery counts records in healthcare_lake.heart_failure_curated.
  • The separate raw_rows and curated_rows columns make any record loss visible.

Before you run this check, consider whether both table counts will match.

  • Select only the reconciliation statement in the Athena query editor.
  • Run the selected reconciliation statement.

You’ll see raw_rows = 299. You’ll also see curated_rows = 299.

That is a strong checkpoint. Your transformation preserved all 299 source records.

Do the Row Counts Differ?

  • Confirm that the first subquery references healthcare_lake.heart_failure_raw.
  • Confirm that the second subquery references healthcare_lake.heart_failure_curated.
  • Treat different counts as a pipeline defect before continuing.

Help me investigate mismatched raw and curated row counts in Athena.

Test the curated values

The next query checks two parts of the contract. Required numeric fields must contain values.

Each coded binary field must contain only 0 or 1.

  • Append the quality statement to healthcare_pipeline.sql by copying this code below the reconciliation query:
SELECT
  COUNT(*) AS total_rows,
  SUM(
    CASE WHEN age_years IS NULL
      OR cpk_mcg_per_l IS NULL
      OR ejection_fraction_percent IS NULL
      OR platelets_kiloplatelets_per_ml IS NULL
      OR serum_creatinine_mg_dl IS NULL
      OR serum_sodium_meq_l IS NULL
      OR follow_up_days IS NULL
    THEN 1 ELSE 0 END
  ) AS null_numeric_values,
  SUM(
    CASE WHEN anaemia_code NOT IN (0, 1)
      OR diabetes_code NOT IN (0, 1)
      OR high_blood_pressure_code NOT IN (0, 1)
      OR sex_code NOT IN (0, 1)
      OR smoking_code NOT IN (0, 1)
      OR death_event_code NOT IN (0, 1)
    THEN 1 ELSE 0 END
  ) AS invalid_binary_values
FROM healthcare_lake.heart_failure_curated;

What Does This Quality Query Test?

  • The total_rows result confirms how many curated records were tested.
  • The first CASE expression counts records with a null in any required numeric field.
  • The second CASE expression counts records with an invalid value in any binary field.
  • A zero result for each failure count means every tested record satisfies that rule.

Before you run this check, consider whether every contract test will pass.

  • Select only the quality statement in the Athena query editor.
  • Run the selected quality statement.

You’ll see total_rows = 299. You’ll also see null_numeric_values = 0.

You’ll see invalid_binary_values = 0. Every curated record passes the declared contract.

That closes the proof. Your pipeline preserved the source batch while enforcing dependable values for analytics.

Seeing a Nonzero Failure Count?

  • Check the CTAS aliases against the required numeric fields in healthcare_pipeline.sql.
  • Check the six binary casts against the source fields in the CTAS statement.
  • Investigate every nonzero result before publishing the dataset.

Help me diagnose a failing healthcare data contract check in Athena.

✔️ Awesome, I've got everything!

Your healthcare_pipeline.sql script now includes reconciliation plus contract-quality checks.

ⓧ I'd like to double check the full code

CREATE DATABASE IF NOT EXISTS healthcare_lake;

CREATE EXTERNAL TABLE IF NOT EXISTS healthcare_lake.heart_failure_raw (
  age string,
  anaemia string,
  creatinine_phosphokinase string,
  diabetes string,
  ejection_fraction string,
  high_blood_pressure string,
  platelets string,
  serum_creatinine string,
  serum_sodium string,
  sex string,
  smoking string,
  time string,
  death_event string
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS TEXTFILE
LOCATION 's3://healthcare-lake-your-account-id/raw/heart_failure/'
TBLPROPERTIES ('skip.header.line.count'='1');

SELECT *
FROM healthcare_lake.heart_failure_raw
LIMIT 10;

SELECT death_event, COUNT(*) AS patient_count
FROM healthcare_lake.heart_failure_raw
GROUP BY death_event
ORDER BY death_event;

CREATE TABLE healthcare_lake.heart_failure_curated
WITH (
  format = 'PARQUET',
  write_compression = 'SNAPPY',
  external_location = 's3://healthcare-lake-your-account-id/curated/heart_failure/'
) AS
SELECT
  CAST(age AS DOUBLE) AS age_years,
  CAST(anaemia AS INTEGER) AS anaemia_code,
  CAST(creatinine_phosphokinase AS INTEGER) AS cpk_mcg_per_l,
  CAST(diabetes AS INTEGER) AS diabetes_code,
  CAST(ejection_fraction AS INTEGER) AS ejection_fraction_percent,
  CAST(high_blood_pressure AS INTEGER) AS high_blood_pressure_code,
  CAST(platelets AS DOUBLE) AS platelets_kiloplatelets_per_ml,
  CAST(serum_creatinine AS DOUBLE) AS serum_creatinine_mg_dl,
  CAST(serum_sodium AS INTEGER) AS serum_sodium_meq_l,
  CAST(sex AS INTEGER) AS sex_code,
  CAST(smoking AS INTEGER) AS smoking_code,
  CAST(time AS INTEGER) AS follow_up_days,
  CAST(death_event AS INTEGER) AS death_event_code
FROM healthcare_lake.heart_failure_raw;

SELECT COUNT(*) AS curated_rows
FROM healthcare_lake.heart_failure_curated;

SELECT
  (SELECT COUNT(*) FROM healthcare_lake.heart_failure_raw) AS raw_rows,
  (SELECT COUNT(*) FROM healthcare_lake.heart_failure_curated) AS curated_rows;

SELECT
  COUNT(*) AS total_rows,
  SUM(
    CASE WHEN age_years IS NULL
      OR cpk_mcg_per_l IS NULL
      OR ejection_fraction_percent IS NULL
      OR platelets_kiloplatelets_per_ml IS NULL
      OR serum_creatinine_mg_dl IS NULL
      OR serum_sodium_meq_l IS NULL
      OR follow_up_days IS NULL
    THEN 1 ELSE 0 END
  ) AS null_numeric_values,
  SUM(
    CASE WHEN anaemia_code NOT IN (0, 1)
      OR diabetes_code NOT IN (0, 1)
      OR high_blood_pressure_code NOT IN (0, 1)
      OR sex_code NOT IN (0, 1)
      OR smoking_code NOT IN (0, 1)
      OR death_event_code NOT IN (0, 1)
    THEN 1 ELSE 0 END
  ) AS invalid_binary_values
FROM healthcare_lake.heart_failure_curated;

Your curated table now has evidence behind its contract. Next, you’ll publish these verified records through Amazon Quick Sight.

Publish the Curated Dashboard

The curated Parquet table has passed reconciliation. Its required fields have also passed every quality rule.

Amazon Quick Sight now turns that trusted table into a dashboard through Amazon Athena. Access to Amazon S3 lets the query layer read the curated objects.

In this step, get ready to:
  • Authorize Amazon Quick Sight to use the validated data.
  • Build three visuals from the curated Athena table.
  • Publish the finished dashboard without external sharing.
Authorize and connect the curated source

Quick Sight needs permission to query Athena. It also needs access to the private bucket that holds the curated Parquet objects.

  • Use the AWS Management Console search bar to return to Amazon Quick Sight.
  • Open Manage QuickSight from the profile menu.
  • Select Security & permissions.
  • Select Add or remove.
  • Select Amazon Athena.
  • Enable access to the S3 bucket named healthcare-lake-<your-account-id>.
  • Choose Finish.
  • Choose Update.

Good progress. The dashboard layer can now reach the validated source without making the bucket public.

Why authorize both services?

Athena provides the catalog-backed query interface. S3 stores the curated Parquet objects behind that interface.

Quick Sight needs access to both services before it can build visuals from the curated table.

  • Select Data in Quick Sight.
  • Choose Create.
  • Choose New dataset.
  • Choose New data source.
  • Choose Athena.
  • Choose Next.

The Athena source form connects Quick Sight to a specific workgroup. Your project uses the same workgroup that already writes query results to the project bucket.

  • Enter healthcare-curated-athena as the data source name.
  • Select primary as the workgroup.
  • Choose Validate connection.
  • Continue to the catalog selector after the validation succeeds.

You reach the catalog selector for the validated connection. This confirms that Quick Sight can communicate with the Athena workgroup.

Connection validation failing?

  • Confirm that Quick Sight is using us-east-1.
  • Confirm that Amazon Athena access remains enabled.
  • Confirm that access remains enabled for healthcare-lake-<your-account-id>.

Help me diagnose the Quick Sight connection.

Build the curated visuals

The dataset selection determines which layer powers every metric. Choosing the curated table gives Quick Sight typed numeric fields that support dependable aggregation.

  • Select AwsDataCatalog as the catalog.
  • Select healthcare_lake as the database.
  • Select heart_failure_curated as the table.
  • Choose Visualize.

You see the analysis workspace with fields from the curated table. The numeric measurements are ready for aggregation.

Why use the curated table?

The raw table keeps every value as a string. The curated table provides numeric fields with the data types that the dashboard needs.

Every visual now reflects the same contract that passed your quality checks.

  • Choose Add in the analysis toolbar.
  • Choose Add visual.
  • Select the KPI visual type.
  • Drag death_event_code into the Value field well.
  • Configure the measure as a record count.

The KPI displays 299. That total matches the curated row count from Athena.

  • Choose Add in the analysis toolbar.
  • Choose Add visual.
  • Select a bar chart visual type.
  • Drag death_event_code into the dimension field well.
  • Drag death_event_code into the measure field well.
  • Configure the measure as a record count.

You see separate bars for death-event codes 0 and 1. Each bar shows the number of curated records in that group.

  • Choose Add in the analysis toolbar.
  • Choose Add visual.
  • Select a bar chart visual type.
  • Drag death_event_code into the dimension field well.
  • Drag serum_creatinine_mg_dl into the measure field well.
  • Configure the measure to show its average.

The second bar chart compares average serum creatinine across the two death-event codes. Its aggregation uses the curated numeric field.

Visuals showing the wrong values?

  • Confirm that the dataset uses healthcare_lake.heart_failure_curated.
  • Confirm that the KPI counts records using death_event_code.
  • Confirm that the serum chart averages serum_creatinine_mg_dl.

Help me correct the Quick Sight visuals.

Publish the dashboard

Publishing turns the analysis sheet into a dashboard that consumers can open. The published result remains connected to the curated dataset.

  • Choose Publish in the analysis.
  • Choose Publish dashboard.
  • Choose Publish as a new dashboard.
  • Enter Heart Failure Curated Pipeline as the dashboard name.
  • Select the analysis sheet.
  • Keep external sharing disabled.
  • Choose Publish.

Quick Sight creates the published dashboard without external sharing. Your analysis now has a consumer-facing result.

Before the final check, what record total and groupings do you expect the dashboard to show?

  • Open the published Heart Failure Curated Pipeline dashboard from Quick Sight.
  • Refresh the dashboard.

You see a record-count KPI of 299.

You also see record count grouped by death_event_code. The final chart shows average serum_creatinine_mg_dl grouped by the same code.

That closes the loop. Your published dashboard now consumes the curated Parquet table that passed the data contract.

Dashboard missing a result?

  • Confirm that the dashboard dataset uses healthcare_lake.heart_failure_curated.
  • Confirm that both bar charts group by death_event_code.
  • Republish the analysis after correcting a field assignment.

Help me verify the published dashboard.

Secret mission

Build a Quality-Issues View

Turn your one-time validation rules into a reusable Athena view that isolates contract violations. Confirm the verified 299-record dataset produces zero issue rows.

Clean Up Your Resources

Clean Up Your Resources

Choose whether to keep your resources available, pause active use, or delete them entirely. The paid subscription and stored cloud resources can continue to incur charges.

Cost warning

The Amazon Quick Sight Author plan costs $24 per user/month after the automatic 30-day trial.

Pausing queries or dashboard refreshes does not stop the monthly Author subscription charge.

Amazon Athena adds usage-based query charges. Amazon S3 adds storage and request charges.

Resources you used:

  • A private Amazon S3 bucket named healthcare-lake-<your-account-id>. It contains the raw CSV under raw/heart_failure/. It contains curated Parquet objects under curated/heart_failure/. It also contains Athena results under athena-results/.
  • An AWS Glue Data Catalog database named healthcare_lake. It contains the heart_failure_raw table. It also contains the heart_failure_curated table and the heart_failure_quality_issues view.
  • A paid Amazon Quick Sight Author subscription.
  • The Quick Sight data source named healthcare-curated-athena. Its connected dataset uses AwsDataCatalog and the heart_failure_curated table. The published dashboard is named Heart Failure Curated Pipeline.

Keep everything running

No action is needed. Choose this if you plan to keep querying the pipeline or showing the published dashboard.

  • Retain the Amazon Quick Sight Author subscription.
  • Keep the Heart Failure Curated Pipeline dashboard available.
  • Retain the healthcare_lake database with its tables and quality-issues view.
  • Keep healthcare-lake-<your-account-id> available for raw data, curated data, and Athena results.

Pause - I'll come back to this later

Pausing this serverless pipeline leaves its stored resources in place while avoiding new activity. The monthly Author subscription continues during the pause.

  • Stop running new queries in Amazon Athena.
  • Stop refreshing the Heart Failure Curated Pipeline dashboard.
  • Retain the healthcare_lake database for your next session.
  • Retain the healthcare-lake-<your-account-id> bucket with its existing objects.
  • Keep the Amazon Quick Sight Author subscription only if you accept its continuing monthly charge.

Delete - I don't want to use this again

Deleting these resources permanently removes the dashboard and its supporting data. The sequence starts with the paid account before clearing the catalog and storage layers.

  • Export any dashboard materials you need to keep.
  • Choose your profile in Amazon Quick Sight.
  • Choose Manage Quick.
  • Choose Account settings.
  • Choose Manage.
  • Disable Account termination protection is on under Account termination.
  • Enter confirm when prompted.
  • Confirm the account termination.

That takes care of the monthly Author subscription. Your Quick Sight dashboards, analyses, datasets, data sources, and users are permanently removed across all AWS Regions.

  • Return to the Amazon Athena query editor in us-east-1.
  • Remove the healthcare_lake database and its dependent catalog objects by running this statement:
DROP DATABASE IF EXISTS healthcare_lake CASCADE

What does this cleanup command do?

CASCADE removes the healthcare_lake database with its raw table, curated table, and quality-issues view.

Athena leaves the underlying Amazon S3 objects intact when it removes external-table metadata. You delete those stored objects separately in the next cleanup action.

  • Confirm that healthcare_lake no longer appears in Athena's catalog list.

Emptying the bucket permanently removes the raw CSV, curated Parquet objects, and Athena query results. The earlier export action keeps anything you chose to preserve out of this deletion.

  • Return to General purpose buckets in the Amazon S3 console.
  • Select healthcare-lake-<your-account-id>.
  • Choose Empty.
  • Complete the empty-bucket confirmation.

The bucket is now empty. Its raw, curated, and query-result objects have been cleared.

  • Return to General purpose buckets.
  • Select healthcare-lake-<your-account-id>.
  • Choose Delete.
  • Enter healthcare-lake-<your-account-id> as the bucket name.
  • Choose Delete bucket.

Cleanup complete. You should no longer see healthcare-lake-<your-account-id> in the Amazon S3 bucket list.

Nice Work!

Nice Work!

You did it! You've built a verified healthcare data lake pipeline on AWS. Your published Amazon Quick Sight dashboard now turns 299 curated records into clear healthcare metrics.

What you learned:

  • Built a private raw landing zone in Amazon S3. Registered the untouched CSV in AWS Glue Data Catalog. Queried its schema-on-read representation through Amazon Athena.
  • Used CTAS to cast raw strings into a typed table. Stored the curated output as Snappy-compressed Parquet. Reconciled all 299 source records with 299 curated records. Confirmed zero null numeric failures. Confirmed zero invalid binary values.
  • Published the Heart Failure Curated Pipeline dashboard from the curated Athena table. Displayed a 299-record KPI. Compared record counts by death-event code. Compared average serum creatinine by death-event code.
  • Secret Mission: Added a reusable quality-issues view for future pipeline checks. The healthcare_lake.heart_failure_quality_issues view exposes records that violate the data contract. Its verified result contains zero issue rows.

Ready to quiz yourself?