Athena, Redshift & QuickSight Pipeline

Build a dual-engine analytics pipeline with a data lake and data warehouse.

Introduction

30 Second Summary

Every time you get a product recommendation or check a sales dashboard, a data pipeline is processing millions of records behind the scenes. Data engineering teams face a key decision every day: which query engine should power each workload?

In this project, you will build a dual-engine analytics pipeline on AWS that processes e-commerce sales data through a serverless data lake and a cloud data warehouse in parallel. You'll write advanced SQL with window functions and CTEs in both engines, then connect everything to an Amazon QuickSight dashboard.

What You'll Build

A production-grade dual-engine analytics architecture that routes e-commerce data through both a serverless data lake (Amazon S3 + AWS Glue + Amazon Athena) and a managed data warehouse (Redshift Serverless), feeding into a unified BI dashboard.

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

  • A serverless data lake on S3 with automated schema discovery via AWS Glue Crawlers and Parquet-optimized reporting tables built with CTAS transformations.
  • A Redshift Serverless data warehouse with distribution keys, sort keys, and materialized views for high-performance analytics.
  • A multi-source QuickSight dashboard connected to both Athena and Redshift, with interactive filters and performance comparisons across both engines.
  • Secret Mission: An automated reporting pipeline using scheduled queries, stored procedures, and daily email reports that run without manual intervention.

Are there any prerequisites?

This project assumes familiarity with SQL concepts like CTEs, window functions, and subqueries. You'll also need an active AWS account.

Set Up the Data Lake Foundation

Every analytics pipeline starts with a place to store raw data. In production, data engineering teams land raw files into a data lake on Amazon S3, then use a metadata catalog to make that data queryable without loading it into a database.

In this step, you will create the storage layer for your pipeline. You will set up two S3 buckets, upload sample e-commerce data, and use AWS Glue to automatically discover and catalog the schema of your raw data.

In this step, get ready to:
  • Create two S3 buckets for your data lake and Athena query results.
  • Upload a sample e-commerce CSV dataset to your data lake bucket.
  • Set up a Glue Crawler to auto-discover your data schema and populate the Data Catalog.
Create your S3 buckets

You need two separate buckets. One stores your raw data lake files. The other stores query results that Amazon Athena produces when you run SQL against your data later.

  • Sign in to the AWS Management Console and open the Amazon S3 console at https://console.aws.amazon.com/s3/.
  • In the navigation bar at the top of the page, check that your region is set to US East (N. Virginia) us-east-1. If it shows a different region, click the region name and select US East (N. Virginia).
  • In the left navigation pane, click General purpose buckets.
  • Click Create bucket.
  • In the Bucket name field, enter analytics-datalake- followed by your initials and -2026 (for example, analytics-datalake-jd-2026).
  • Leave all other settings as their defaults.
  • Scroll to the bottom and click Create bucket.

Why two buckets?

Athena requires a separate S3 location to write query results. Keeping results separate from your raw data avoids polluting the data lake with generated output files.

  • Repeat the bucket creation process. Click Create bucket again.
  • For the Bucket name, enter athena-results- followed by your initials and -2026 (for example, athena-results-jd-2026).
  • Leave all other settings as defaults and click Create bucket.

You should now see both buckets listed on the General purpose buckets page.

Seeing a 'Bucket name already exists' error?

S3 bucket names must be globally unique across all AWS accounts. Try adding extra characters to your initials (e.g., your full last name or a number) to make the name unique.

help me fix my S3 bucket naming error

Upload the sample e-commerce dataset

Your data lake needs data to analyze. You will create a small CSV file with synthetic e-commerce sales records covering multiple product categories, regions, and customers.

  • On your local computer, open a text editor and create a new file called sales_data.csv.
  • Paste the following sample data into the file and save it:
order_id,order_date,product_category,product_name,quantity,unit_price,total_amount,region,customer_id
1001,2026-01-05,Electronics,Wireless Headphones,2,49.99,99.98,Northeast,C001
1002,2026-01-07,Clothing,Winter Jacket,1,89.99,89.99,Southeast,C002
1003,2026-01-10,Electronics,USB-C Hub,3,29.99,89.97,West,C003
1004,2026-01-12,Home,Desk Lamp,1,34.99,34.99,Northeast,C001
1005,2026-01-15,Electronics,Bluetooth Speaker,1,79.99,79.99,Midwest,C004
1006,2026-01-18,Clothing,Running Shoes,2,64.99,129.98,West,C005
1007,2026-01-20,Home,Coffee Maker,1,119.99,119.99,Southeast,C002
1008,2026-01-22,Electronics,Laptop Stand,1,45.99,45.99,Northeast,C006
1009,2026-01-25,Clothing,Wool Scarf,3,24.99,74.97,Midwest,C007
1010,2026-01-28,Home,Air Purifier,1,149.99,149.99,West,C003
1011,2026-02-01,Electronics,Mechanical Keyboard,1,89.99,89.99,Northeast,C001
1012,2026-02-03,Clothing,Denim Jeans,2,54.99,109.98,Southeast,C008
1013,2026-02-06,Home,Smart Thermostat,1,199.99,199.99,West,C005
1014,2026-02-08,Electronics,Webcam HD,1,59.99,59.99,Midwest,C004
1015,2026-02-10,Clothing,Hiking Boots,1,129.99,129.99,Northeast,C006
1016,2026-02-12,Home,Robot Vacuum,1,249.99,249.99,Southeast,C002
1017,2026-02-15,Electronics,Portable Charger,2,39.99,79.98,West,C009
1018,2026-02-18,Clothing,Fleece Pullover,1,49.99,49.99,Midwest,C007
1019,2026-02-20,Home,Standing Desk Mat,1,44.99,44.99,Northeast,C010
1020,2026-02-22,Electronics,Noise Cancelling Earbuds,1,149.99,149.99,Southeast,C008

What does this dataset represent?

This is a synthetic e-commerce sales dataset with 20 orders spread across four product categories (Electronics, Clothing, Home) and four regions (Northeast, Southeast, West, Midwest). Each row represents a single order with quantity, pricing, and customer information.

Later, you will run window functions and CTEs against this data to calculate running totals, rankings, and aggregations.

  • Back in the S3 console, click on the name of your data lake bucket (the one starting with analytics-datalake-).
  • You need to create a folder path to organize your raw data. Click Create folder.
  • Enter raw as the folder name and click Create folder.
  • Click into the raw/ folder, then click Create folder again.
  • Enter sales as the folder name and click Create folder.
  • Click into the sales/ folder.
  • Click Upload.
  • Click Add files, select the sales_data.csv file from your computer, and click Upload.

You should see a green success banner confirming your file was uploaded. Your data now lives at the path s3://your-bucket/raw/sales/sales_data.csv.

Don't see the Upload button?

Make sure you navigated inside the sales/ folder. The Upload button appears when you are inside a folder or at the bucket root level.

help me upload my CSV to S3

Create the Glue database and run a Crawler

AWS Glue's Data Catalog acts as a shared metadata layer that both Athena and Redshift Spectrum can query against. A Glue Crawler scans your S3 data, detects column names and data types, and registers the schema as a table in the catalog.

Why use a Crawler instead of writing the schema manually?

In production, data teams use Crawlers to automate schema discovery. When new files land in S3, the Crawler detects format changes and updates the catalog automatically. This removes manual DDL maintenance and ensures your catalog always reflects reality.

  • Open the AWS Glue console at https://console.aws.amazon.com/glue.
  • In the left navigation pane, expand Data Catalog and click Databases.
  • Click Add database.
  • Enter analytics_db as the database name.
  • Click Create database.

Your database is now ready. Next, you will create a Crawler to scan your CSV data and register it as a table inside this database.

  • In the left navigation pane, click Crawlers.
  • Click Create crawler.
  • In the Crawler name field, enter raw-sales-crawler.
  • Click Next.
  • On the Choose data sources and classifiers page, click Add a data source.
  • For Data source, select S3.
  • For the S3 path, click Browse S3 and navigate to your data lake bucket, then select the raw/sales/ folder. The path should look like s3://analytics-datalake-yourname-2026/raw/sales/.
  • Click Add an S3 data source.
  • Click Next.
  • On the Configure security settings page, select Create new IAM role.
  • Enter a suffix for the role name (for example, analytics-crawler). AWS will create a role named AWSGlueServiceRole-analytics-crawler.
  • Click Next.
  • On the Set output and scheduling page, under Target database, select analytics_db from the dropdown.
  • For Crawler schedule, keep On demand selected.
  • Click Next.
  • On the Review and create page, verify your settings. Confirm the data source points to your raw/sales/ path and the target database is analytics_db.
  • Click Create crawler.

Now run the Crawler to scan your data and populate the catalog.

  • Select the checkbox next to raw-sales-crawler in the Crawlers list.
  • Click Run.
  • Wait about one minute for the Crawler to complete. Click the refresh icon to check the status. When the Last run column shows Succeeded, your data has been cataloged.

What did the Crawler do?

The Crawler scanned your CSV file in S3, identified the column names from the header row, inferred data types for each column, and registered a new table in the analytics_db database. This table is metadata only. Your actual data stays in S3.

  • To verify the table was created, click Data Catalog in the left navigation, then click Tables.
  • You should see a table named sales (or similar, based on your folder name) in the analytics_db database.
  • Click the table name to inspect the schema. You should see all nine columns listed: order_id, order_date, product_category, product_name, quantity, unit_price, total_amount, region, and customer_id.

Crawler ran but no tables appeared?

Make sure the S3 path you specified in the Crawler points to the folder containing your CSV file, not to the file itself. The path should end with raw/sales/ (with a trailing slash).

Also confirm you uploaded sales_data.csv inside the sales/ folder, not at the bucket root level.

help me troubleshoot my Glue Crawler

Your data lake foundation is complete. You have raw data in S3, and the Glue Data Catalog knows its schema. Next up, you will connect Athena to this catalog and write advanced analytical SQL with window functions and CTEs.

Run Advanced Athena Queries and CTAS Transformations

Your data lake foundation is in place. You have raw sales data in S3, a Glue Crawler that cataloged the schema, and a database ready for querying.

Now you need to turn that raw data into something useful. In this step, you will write advanced analytical SQL in Amazon Athena and persist the results as an optimized Parquet reporting table using a CTAS (Create Table As Select) transformation.

In this step, get ready to:
  • Run exploratory and advanced window-function queries against your raw sales data.
  • Use CTAS to create a Parquet-formatted reporting table partitioned by region.
  • Compare scan sizes between CSV and Parquet to see cost and performance improvements.
Open Athena and run exploratory queries with window functions

Before running transformations, you need to validate that your data looks correct. You also need to write the analytical queries that will power your reporting layer.

  • Open the Athena console at https://console.aws.amazon.com/athena/.
  • From the navigation bar, choose Settings.
  • Choose Manage.
  • In the Location of query result box, enter the S3 path to your results bucket:
s3://[[ATHENA_RESULTS_BUCKET="your-athena-results-bucket-name"]]/
  • Choose Save.
  • Return to the Editor tab.
  • In the Database dropdown on the left, select analytics_db.

Why set a query results location?

Athena writes the output of every query to S3. Without a designated results bucket, Athena cannot execute any queries. This is also where Athena stores metadata files for tracking query history.

Now validate your raw data by running an exploratory query.

  • Paste the following query into the editor and click Run:
-- Validate the raw sales data
SELECT
    COUNT(*) AS total_rows,
    COUNT(DISTINCT customer_id) AS unique_customers,
    COUNT(DISTINCT product_category) AS categories,
    MIN(order_date) AS earliest_order,
    MAX(order_date) AS latest_order
FROM sales;

What does this query do?

This query gives you a quick health check of your dataset. It counts total rows, unique customers, distinct product categories, and the date range of your orders.

If any of these values look unexpected, you know there is a problem with your source data before you build reporting layers on top of it.

  • Confirm you see a results table with row counts and date ranges that match your uploaded CSV data.
  • Note the Data scanned value shown beneath the results. This tells you how much data Athena read from your raw CSV. You will compare this number later.

Query returning zero rows or an error?

Make sure you selected analytics_db in the Database dropdown. If the sales table does not appear in the left panel, your Glue Crawler from Step 1 may not have completed successfully. Return to the Glue console and re-run the crawler.

Help me troubleshoot this Athena query issue

Now write the advanced analytical query using CTEs and window functions.

  • Clear the editor and paste the following CTE-based query, then click Run:
-- CTE with window functions: running totals and category rankings
WITH sales_analytics AS (
    SELECT
        order_id,
        order_date,
        product_category,
        product_name,
        quantity,
        unit_price,
        total_amount,
        customer_id,
        -- Running total of sales per category, ordered by date
        SUM(total_amount) OVER (
            PARTITION BY product_category
            ORDER BY order_date
        ) AS running_total,
        -- Rank orders within each region by total_amount descending
        RANK() OVER (
            PARTITION BY region
            ORDER BY total_amount DESC
        ) AS sales_rank,
        region
    FROM sales
)
SELECT *
FROM sales_analytics
ORDER BY region, sales_rank;

What do these window functions do?

Window functions perform calculations across a set of rows related to the current row without collapsing the result set into a single aggregate.

  • SUM(total_amount) OVER (PARTITION BY product_category ORDER BY order_date) calculates a running total of sales within each product category. Each row shows the cumulative sum up to that order date.
  • RANK() OVER (PARTITION BY region ORDER BY total_amount DESC) assigns a ranking to each order within its region. The highest-value order is ranked first.
  • The CTE wraps this logic cleanly so the outer SELECT can filter or re-order without repeating the window function definitions.
  • Confirm you see columns including running_total and sales_rank populated with calculated values.
  • Note the Data scanned value again. It should be similar to your exploratory query since both read the full CSV table.
Use CTAS to create a Parquet reporting table partitioned by region

Your analytical query works, but it reads raw CSV every time you run it. CTAS lets you persist those results as an optimized Parquet table in a single statement.

Parquet is a columnar format that Athena reads much more efficiently. Partitioning by region means queries filtered to a single region skip all other region directories entirely.

  • Clear the editor and paste the following CTAS query, then click Run:
-- Create a Parquet reporting table partitioned by region
CREATE TABLE sales_summary
WITH (
    format = 'PARQUET',
    external_location = 's3://[[DATALAKE_BUCKET="your-datalake-bucket-name"]]/curated/sales_summary/',
    partitioned_by = ARRAY['region']
) AS
WITH sales_analytics AS (
    SELECT
        order_id,
        order_date,
        product_category,
        product_name,
        quantity,
        unit_price,
        total_amount,
        customer_id,
        SUM(total_amount) OVER (
            PARTITION BY product_category
            ORDER BY order_date
        ) AS running_total,
        RANK() OVER (
            PARTITION BY region
            ORDER BY total_amount DESC
        ) AS sales_rank,
        -- region must be LAST because it is the partition column
        region
    FROM sales
)
SELECT
    order_id,
    order_date,
    product_category,
    product_name,
    quantity,
    unit_price,
    total_amount,
    customer_id,
    running_total,
    sales_rank,
    region
FROM sales_analytics;

What does this CTAS query do?

CTAS (Create Table As Select) combines a CREATE TABLE statement with a SELECT query. It writes the query results as physical files in S3 and automatically registers the new table in the Glue Data Catalog.

  • format = 'PARQUET' stores the data in columnar Parquet format instead of row-based CSV.
  • external_location specifies the S3 path where Athena writes the Parquet files.
  • partitioned_by = ARRAY['region'] creates separate S3 prefixes for each region value, so Athena only scans relevant partitions when you filter by region.
  • The partition column (region) must appear last in the SELECT statement. This is a requirement of Athena CTAS.
  • Wait for the query to complete. You should see a success message indicating the table was created.
  • In the left panel under Tables, confirm that sales_summary now appears in the analytics_db database.

Seeing an error about external_location?

If your workgroup has Override client-side settings enabled, CTAS with external_location will fail. Open Settings and confirm that override is not enabled. Also ensure the S3 path you specified is empty. CTAS will not write to a location that already contains data.

Help me fix this CTAS error

Catalog the Parquet table and compare scan sizes

Your CTAS query wrote Parquet files to S3 and registered the table in the Data Catalog. Now you will run a Glue Crawler on the curated path to ensure all partition metadata is fully discovered for optimal query performance.

  • Open the AWS Glue console at https://console.aws.amazon.com/glue.
  • In the left navigation, click Crawlers.
  • Click Create crawler.
  • Enter the name curated-sales-crawler.
  • Click Next.
  • Click Add a data source.
  • Set the S3 path to your curated location:
s3://[[DATALAKE_BUCKET="your-datalake-bucket-name"]]/curated/sales_summary/
  • Click Add an S3 data source.
  • Click Next.
  • Select an existing IAM role (the same one you used for your first crawler) or create a new one.
  • Click Next.
  • For the target database, select analytics_db.
  • Click Next.
  • Click Create crawler.
  • Select the curated-sales-crawler and click Run.
  • Wait for the crawler to finish (status changes to Completed).

Why run a crawler on the curated path?

CTAS registered the table in the Glue Data Catalog, but running a dedicated crawler ensures all partition metadata is fully discovered and consistent. This is especially important for Parquet tables where individual partition directories need to be registered for Athena to use partition pruning effectively.

In production, crawlers are scheduled to run regularly so new partitions are picked up automatically as data arrives.

Now query the Parquet table and compare the scan size to your earlier raw CSV queries.

  • Return to the Athena console.
  • Paste the following query and click Run:
-- Query the optimized Parquet table filtered by region
SELECT *
FROM sales_summary
WHERE region = 'West'
ORDER BY sales_rank
LIMIT 20;

What does this query do?

This query reads only the region = 'West' partition from the Parquet table. Athena skips all other region directories entirely, scanning far less data than a full-table read.

  • After the query completes, note the Data scanned value beneath the results.

Now run the same filter on the raw CSV table to see the difference.

  • Paste the following query and click Run:
-- Same filter on the raw CSV table for comparison
SELECT *
FROM sales
WHERE region = 'West'
ORDER BY total_amount DESC
LIMIT 20;
  • Note the Data scanned value. The raw CSV query scans the entire file regardless of the WHERE filter.
  • The Parquet query scanned significantly less data because of columnar storage and partition pruning.

Why is the Parquet scan so much smaller?

Parquet is a columnar format. Athena only reads the columns referenced in your query, skipping the rest entirely.

Partitioning by region means Athena skips entire directories of data that do not match your WHERE filter. Since Athena charges $5 per TB scanned, less data scanned means lower cost. On large datasets, switching from CSV to partitioned Parquet can reduce scan sizes by 90% or more.

Parquet query scanning the same amount as CSV?

If the scan sizes are identical, your Parquet table may not have registered partitions correctly. Go to the Glue console, click Tables, select sales_summary, and check that region appears as a partition key. If it does not, re-run the curated-sales-crawler.

Help me fix my partition setup

Your data lake now has two layers: raw CSV for source data and curated Parquet for optimized analytics. Next up, you will load this same data into Redshift Serverless and see how a data warehouse handles the same analytical queries.

Set Up Redshift Serverless

You have a working data lake with Athena running advanced SQL against Parquet tables in S3. Now you'll build the second engine in your dual-engine architecture: a cloud data warehouse.

Amazon Redshift Serverless gives you a fully managed data warehouse that charges only when queries run. In this step, you'll activate the free trial, set a spending guard, and create your first table with proper data modeling.

In this step, get ready to:
  • Activate Redshift Serverless with the $300 free trial credit and set a daily RPU-hours limit.
  • Create an IAM role with S3 read access and attach it to your Redshift namespace.
  • Create a schema and sales table with a distribution key and sort key for optimal query performance.
Activate Redshift Serverless and set a usage limit

Redshift Serverless scales compute automatically and charges per RPU-hour. The free trial provides $300 in credit for 90 days, which is more than enough for this project.

  • In the AWS Console, search for Redshift in the top search bar and select Amazon Redshift.
  • Choose Try Amazon Redshift Serverless free trial.
  • Under Configuration, choose Use default settings.
  • Click Save configuration.

What is Redshift Serverless?

Amazon Redshift is a cloud data warehouse designed for analytical queries on structured data. The Serverless option removes capacity planning entirely.

Redshift measures compute in Redshift Processing Units (RPUs). You pay per RPU-hour consumed, starting at $1.50 per hour, with no charge when idle.

Wait for the workgroup and namespace to finish creating. This takes 1-2 minutes. You'll see the status change to Available.

Now set a daily usage limit to prevent surprise charges during the project.

  • In the left sidebar, click Workgroup configuration.
  • Click your workgroup name (default-workgroup).
  • Select the Limits tab.
  • Click Manage usage limits.
  • Set Frequency to Daily.
  • Enter 10 for the usage limit (in RPU hours).
  • Set the Action to Turn off user queries.
  • Click Save changes.

Why set a usage limit?

Redshift Serverless can scale up RPUs to handle higher load, and capacity can remain elevated after queries finish. A daily limit of 10 RPU-hours caps your spend at roughly $15/day, which you'll never approach in a tutorial.

The Turn off user queries action is the most protective. It stops all queries when the limit is reached, preventing any overspend.

Don't see the free trial option?

The $300 free trial is only available to accounts that have never used Redshift Serverless before. If your account has previously activated it, you can still follow along, but usage will count against your regular billing.

Setting the usage limit is especially important if you are not on the free trial. Help me figure out my Redshift Serverless billing situation.

Create an IAM role and attach it to your namespace

Redshift needs an IAM role with S3 read permissions to load data from your data lake bucket in the next step.

  • In the left sidebar of the Redshift console, click Namespace configuration.
  • Click your namespace name (default-namespace).
  • Select the Security and encryption tab.
  • Click Manage IAM roles.
  • Click Create IAM role.
  • For S3 bucket access, choose Any S3 bucket.
  • Click Create IAM role as default.
  • Click Save changes.

Why does Redshift need an IAM role?

Redshift runs in its own managed VPC and cannot directly access your S3 buckets. The IAM role acts as a bridge, granting Redshift permission to read objects from S3 on your behalf.

Setting it as the default role means you can use IAM_ROLE default in your COPY commands instead of pasting a full ARN every time.

You should see the new role listed under Associated IAM roles with a status of in-sync.

Role not showing as in-sync?

It can take 30-60 seconds for the role to synchronize. Refresh the page and check again.

If you see a permissions error, make sure you are signed in as a user with IAM admin privileges. Help me troubleshoot my Redshift IAM role setup.

Open Query Editor v2 and create your schema and table

Query Editor v2 is a web-based SQL client built into the Redshift console. You'll use it to run all your Redshift SQL throughout this project.

  • In the left sidebar, click Query data to open Query Editor v2 in a new browser tab.
  • In the tree-view panel on the left, click your workgroup name to connect.
  • If prompted for a connection type, leave Federated user selected and click Create connection.

You should see the dev database appear under your workgroup in the tree view.

  • Create your analytics schema by pasting this SQL into the query editor and clicking Run:
-- Create a dedicated schema for our analytics tables
CREATE SCHEMA IF NOT EXISTS analytics;

What is a schema in Redshift?

A schema is a namespace inside a database that groups related tables together. By creating an analytics schema instead of using the default public schema, you keep your project tables organized and separate from system tables.

You should see a CREATE SCHEMA success message in the results panel.

  • Now create your sales table with proper data modeling by running this SQL:
-- Create sales table with distribution key and sort key
-- DISTKEY on customer_id optimizes joins on that column
-- SORTKEY on order_date speeds up date-range filters
CREATE TABLE analytics.sales (
    order_id INTEGER,
    order_date DATE,
    product_category VARCHAR(50),
    product_name VARCHAR(100),
    quantity INTEGER,
    unit_price DECIMAL(10,2),
    total_amount DECIMAL(10,2),
    region VARCHAR(50),
    customer_id INTEGER
)
DISTKEY(customer_id)
SORTKEY(order_date);

What do DISTKEY and SORTKEY do?

Redshift distributes table rows across multiple compute nodes. DISTKEY(customer_id) ensures rows with the same customer_id land on the same node. This makes joins on customer_id much faster because data doesn't need to shuffle between nodes.

SORTKEY(order_date) physically stores rows in date order on disk. Queries that filter by date ranges can skip entire blocks of data, dramatically reducing I/O.

You should see a CREATE TABLE success message in the results panel.

  • Verify the table was created by running:
-- Confirm the table exists and check its columns
SELECT * FROM analytics.sales LIMIT 5;

You should see an empty result set with all nine column headers: order_id, order_date, product_category, product_name, quantity, unit_price, total_amount, region, and customer_id. The table is empty because you haven't loaded data yet.

Seeing a 'schema does not exist' error?

Make sure you ran the CREATE SCHEMA statement first and saw a success message. Also confirm you're connected to the dev database in the tree-view panel on the left.

Help me debug my Redshift schema creation error.

Why this approach instead of Athena?

Athena uses schema-on-read. It discovers structure at query time and charges per TB scanned. Redshift uses schema-on-write with physical data modeling (distribution keys, sort keys) designed for repeated, predictable workloads.

When the same query runs hundreds of times a day for a dashboard, Redshift's pre-optimized storage delivers consistent sub-second performance. Athena is better for ad-hoc exploration where you don't know the query patterns in advance.

Your Redshift warehouse is modeled and ready for data. Next up, you'll load the CSV data from S3 and run the same advanced analytics queries to compare performance against Athena.

Load Data and Model in Redshift

Your Redshift Serverless warehouse has its table structure ready, but it's empty. Now it's time to load your S3 data into Redshift and run the same advanced analytical queries you built in Athena.

Running identical queries in both engines gives you a real comparison of how a serverless data lake (Athena) performs versus a dedicated data warehouse (Redshift). You'll also create a materialized view that pre-computes aggregations for instant dashboard-ready results.

In this step, get ready to:
  • Load your CSV data from S3 into Redshift using the COPY command.
  • Run advanced analytical queries with window functions and CTEs, then compare performance against Athena.
  • Create and refresh a materialized view for sub-second aggregation queries.
Load CSV data from S3 with the COPY command

The COPY command is Redshift's high-performance bulk loader. It reads data directly from S3 in parallel, making it far faster than row-by-row inserts.

  • In Query Editor v2, paste and run the following COPY command to load your sales data from S3:
-- Load CSV data from S3 into the analytics.sales table
COPY analytics.sales
FROM 's3://[[DATALAKE_BUCKET="analytics-datalake-yourname-2026"]]/raw/sales/'
IAM_ROLE '[[IAM_ROLE_ARN="arn:aws:iam::123456789012:role/RedshiftS3AccessRole"]]'
CSV
IGNOREHEADER 1
REGION 'us-east-1';

What does this command do?

COPY analytics.sales tells Redshift which table to load into. The FROM clause points to your S3 folder containing the CSV file.

IAM_ROLE provides the credentials Redshift uses to access S3. CSV specifies the file format, and IGNOREHEADER 1 skips the first row (your column headers).

  • Verify the data loaded successfully by running this count query:
-- Check how many rows were loaded
SELECT COUNT(*) AS total_rows FROM analytics.sales;
  • Confirm the row count matches the number of data rows in your original CSV (excluding the header row).

Quick row count check

If you uploaded the sample dataset from Step 1 with 50 rows of data, your count should return 50. A mismatch means either the COPY skipped malformed rows or your S3 path is slightly off.

Seeing a COPY command error?

If you see an access denied error, confirm that the IAM role ARN you pasted matches the role attached to your Redshift namespace exactly. Check under Namespace configuration > Security and encryption to verify it's attached.

If you see a file not found error, double check your S3 bucket name and path. The path must end with a trailing slash (/raw/sales/).

Help me troubleshoot my Redshift COPY command error

Run advanced analytical queries and compare to Athena

Now you'll run the same style of analytical queries you built in Athena. Running identical logic in both engines lets you see how a dedicated data warehouse handles complex window functions compared to a serverless query engine.

  • Run this CTE-based query with window functions for running totals and category rankings:
-- CTE with window functions: running totals and rankings
WITH daily_sales AS (
    SELECT
        order_date,
        product_category,
        region,
        SUM(total_amount) AS daily_total,
        SUM(quantity) AS daily_quantity
    FROM analytics.sales
    GROUP BY order_date, product_category, region
)
SELECT
    order_date,
    product_category,
    region,
    daily_total,
    -- Running total per category, ordered by date
    SUM(daily_total) OVER (
        PARTITION BY product_category
        ORDER BY order_date
        ROWS UNBOUNDED PRECEDING
    ) AS running_total,
    -- Rank categories by daily total within each region
    RANK() OVER (
        PARTITION BY region
        ORDER BY daily_total DESC
    ) AS category_rank
FROM daily_sales
ORDER BY region, category_rank;

What does this query do?

The CTE (daily_sales) first aggregates raw orders into daily totals by category and region. This gives the outer query a cleaner dataset to work with.

SUM(daily_total) OVER (PARTITION BY product_category ORDER BY order_date ROWS UNBOUNDED PRECEDING) calculates a cumulative running total for each category across time. RANK() OVER (PARTITION BY region ORDER BY daily_total DESC) ranks categories by revenue within each region.

  • Note the execution time shown at the bottom of the Query Editor v2 results panel.
  • Compare this to the execution time you saw when running similar queries in Athena in Step 2.

How do the execution times compare?

With a small dataset, both engines return results quickly. The real difference appears at scale.

Athena scans S3 objects on every query (pay-per-scan, no pre-loading). Redshift keeps data in local storage with distribution and sort keys optimized for your access patterns, so repeated queries get faster over time.

Query returning unexpected results?

If the running totals look wrong, confirm your ORDER BY order_date is inside the window frame, not just in the outer query. Without it, the window has no defined order and results are unpredictable.

Help me debug my Redshift window function query

Create a materialized view for pre-computed analytics

A materialized view stores the results of a query physically on disk. Instead of recalculating aggregations every time a dashboard loads, Redshift serves the pre-computed answer instantly.

  • Create a materialized view that pre-computes daily sales aggregations by category and region:
-- Materialized view: pre-compute daily sales by category and region
CREATE MATERIALIZED VIEW analytics.mv_daily_sales AS
SELECT
    order_date,
    product_category,
    region,
    SUM(total_amount) AS daily_revenue,
    SUM(quantity) AS daily_units,
    COUNT(DISTINCT customer_id) AS unique_customers
FROM analytics.sales
GROUP BY order_date, product_category, region;

What does this materialized view do?

This view aggregates daily revenue, units sold, and unique customers for every combination of date, category, and region. Redshift stores these results physically so queries against the view don't need to re-scan the full sales table.

In production, dashboards querying this view get sub-second response times even when the underlying table has millions of rows.

  • Refresh the materialized view to ensure it contains the latest data:
-- Refresh to populate with current data
REFRESH MATERIALIZED VIEW analytics.mv_daily_sales;
  • Query the materialized view to confirm it returns pre-computed results:
-- Query the materialized view for instant aggregated results
SELECT *
FROM analytics.mv_daily_sales
ORDER BY daily_revenue DESC
LIMIT 10;
  • Check the execution time in the results panel. You should see sub-second response times since Redshift is reading pre-computed results rather than scanning the full table.

Why refresh a materialized view?

Materialized views are snapshots. When the underlying table changes (new rows loaded, updates applied), the view becomes stale. Running REFRESH MATERIALIZED VIEW recalculates and stores the latest results.

In production, teams schedule these refreshes overnight so dashboards always show fresh data by morning.

Materialized view not returning data?

If querying the view returns zero rows, make sure you ran the REFRESH MATERIALIZED VIEW command after creating it. The view won't contain data until it's been refreshed at least once after the underlying table is loaded.

Help me troubleshoot my Redshift materialized view

Your Redshift warehouse is now loaded, queried, and optimized with a materialized view. Next up, you'll connect Redshift Spectrum to your data lake so Redshift can query S3 data directly without loading it first.

Connect Redshift Spectrum to Your Data Lake

You now have two separate analytics engines running on the same data. Athena queries your S3 data lake directly, while Redshift queries a loaded copy inside its own storage.

In production, teams often keep frequently-accessed "hot" data inside Redshift for fast joins and pre-computed views, while leaving historical or raw "cold" data in S3. Redshift Spectrum bridges the two by letting Redshift query S3 data directly through the Glue Data Catalog without loading it first.

In this step, get ready to:
  • Create an external schema in Redshift that maps to your Glue Data Catalog.
  • Write a hybrid query joining local Redshift tables with external S3 tables via Spectrum.
  • Compare three query paths and document the trade-offs in latency, cost, and data freshness.
Create an external schema mapping to Glue Data Catalog

An external schema in Redshift acts as a bridge to your Glue Data Catalog. Once created, every table cataloged in that Glue database becomes queryable from Redshift without any data loading.

  • In the Redshift Query Editor v2 from earlier, make sure you're connected to your Serverless workgroup and database.
  • Create the external schema by running this SQL:
-- Create an external schema that maps to the Glue Data Catalog database
CREATE EXTERNAL SCHEMA spectrum_schema
FROM DATA CATALOG
DATABASE 'analytics_db'
REGION 'us-east-1'
IAM_ROLE default;

What does this query do?

This command creates a schema called spectrum_schema inside Redshift that points to your analytics_db database in the Glue Data Catalog.

  • FROM DATA CATALOG tells Redshift to use the Glue Data Catalog as the metadata source.
  • REGION 'us-east-1' specifies the region where your Glue catalog lives.
  • IAM_ROLE default uses the IAM role you already attached to your Redshift namespace in the previous step.
  • Click Run.
  • Verify the schema was created by running this query:
-- List the external tables now visible through Spectrum
SELECT schemaname, tablename
FROM svv_external_tables
WHERE schemaname = 'spectrum_schema';
  • Click Run.
  • You should see your Glue-cataloged tables (sales and sales_summary) listed in the results.

Why is this powerful?

You didn't load any data into Redshift for these external tables. Redshift Spectrum reads the data directly from S3 at query time, using the same schema metadata that Athena uses. One catalog, two engines.

Not seeing your tables?

Make sure the IAM role attached to your namespace has both S3 read access and glue:GetTable permission. Without Glue access, Redshift cannot read the catalog metadata.

Also confirm your Redshift Serverless workgroup and S3 buckets are in the same AWS Region (us-east-1).

Help me troubleshoot why my external schema isn't showing tables from my Glue Data Catalog in Redshift Spectrum.

Write a hybrid query joining local and external tables

The real power of Spectrum is combining local Redshift tables with external S3 data in a single query. This is the hybrid query pattern used in production architectures.

  • Run this query that joins your Redshift local analytics.sales table with the external Spectrum table to compare data from both sources:
-- Hybrid query: join local Redshift table with external S3 data via Spectrum
SELECT
    local_tbl.product_category,
    local_tbl.region,
    SUM(local_tbl.total_amount) AS redshift_total,
    SUM(ext_tbl.total_amount) AS spectrum_total
FROM analytics.sales AS local_tbl
JOIN spectrum_schema.sales AS ext_tbl
    ON local_tbl.order_id = ext_tbl.order_id
GROUP BY local_tbl.product_category, local_tbl.region
ORDER BY redshift_total DESC;

What does this query do?

This query demonstrates the hybrid pattern by joining two representations of the same data:

  • analytics.sales is the local table loaded into Redshift storage (hot data, fast scans).
  • spectrum_schema.sales is the external table reading directly from S3 via Spectrum (cold data, no loading required).
  • The JOIN proves both paths return matching data, validating your Spectrum connection.

In production, you would use this pattern to join current-month data (local Redshift) with multi-year historical data (S3 via Spectrum) without paying to load terabytes into the warehouse.

  • Click Run.
  • You should see results with matching totals in both the redshift_total and spectrum_total columns, confirming the join works correctly across both engines.

Getting a permission error on the JOIN?

If you see an error mentioning S3 access denied, your IAM role may not have read access to the S3 path where the raw CSV lives. Return to the Redshift console, check your namespace IAM role, and verify it has a policy allowing s3:GetObject on your data lake bucket.

Help me fix an S3 permission error when running a Redshift Spectrum query that joins local and external tables.

Compare three query paths and document trade-offs

You now have three distinct ways to query the same underlying sales data. Each path has different characteristics in terms of latency, cost, and data freshness.

  • Run this query against the Redshift local table to note its execution time:
-- Path 1: Redshift local table (data loaded into Redshift storage)
SELECT
    product_category,
    region,
    SUM(total_amount) AS total_sales,
    COUNT(*) AS order_count
FROM analytics.sales
GROUP BY product_category, region
ORDER BY total_sales DESC;
  • Click Run and note the execution time shown in the results pane.
  • Now run the same aggregation via Spectrum over the raw S3 data:
-- Path 2: Redshift Spectrum over S3 (reads raw CSV from S3 at query time)
SELECT
    product_category,
    region,
    SUM(total_amount) AS total_sales,
    COUNT(*) AS order_count
FROM spectrum_schema.sales
GROUP BY product_category, region
ORDER BY total_sales DESC;
  • Click Run and note the execution time.
  • Switch to the Athena console tab from earlier and run the same query there:
-- Path 3: Athena direct (serverless query over S3)
SELECT
    product_category,
    region,
    SUM(total_amount) AS total_sales,
    COUNT(*) AS order_count
FROM sales
GROUP BY product_category, region
ORDER BY total_sales DESC;
  • Click Run and note the execution time and data scanned shown at the bottom of the Athena results.

How do the three paths compare?

Here is what you should observe:

  • Redshift local table is fastest. Data is already loaded, compressed, and sorted by order_date. Best for repeated BI queries and dashboards.
  • Redshift Spectrum is slightly slower. It reads from S3 at query time but benefits from Redshift's query optimizer and can join with local tables. Best for hybrid queries combining hot and cold data.
  • Athena direct has similar performance to Spectrum for standalone queries. It charges $5 per TB scanned with no infrastructure to manage. Best for ad-hoc exploration and teams without a Redshift warehouse.

The key insight: you don't choose one engine. Production data teams use all three depending on the workload pattern.

You've connected both analytics engines through a single Glue Data Catalog. Next up, you'll build a QuickSight dashboard that visualizes data from both Athena and Redshift side by side.

Build a Multi-Source QuickSight Dashboard

You now have two parallel analytics engines running against the same data. You've seen how Athena excels at ad-hoc exploration and how Redshift delivers predictable performance on repeated queries.

The final piece of a production analytics architecture is a BI dashboard that pulls insights from both engines. In this step, you'll connect Amazon QuickSight to both Athena and Redshift, then build a multi-source dashboard that demonstrates when to use each engine.

In this step, get ready to:
  • Sign up for QuickSight and grant it access to your data sources.
  • Create datasets from both Athena and Redshift Serverless.
  • Build a dashboard with multiple visuals and an interactive filter.
Sign up for QuickSight and grant permissions

Before you can visualize data, QuickSight needs its own account within your AWS environment. It also needs explicit permission to reach Athena, your S3 buckets, and Redshift Serverless.

  • In the AWS Console, search for QuickSight in the top search bar (or find it under **Analytics**).
  • Click the QuickSight result to open the signup page.
  • Enter a unique account name for your QuickSight account (e.g. analytics-quicksight-2026).
  • Enter a notification email address for the account owner.
  • For the SPICE region, select US East (N. Virginia) to match your data.
  • For authentication method, select Password-based or Single-Sign On (recommended).
  • Review your choices and click Create account.
  • Once the account is ready, click Go to QuickSight to open the QuickSight console.

What is SPICE?

SPICE (Super-fast, Parallel, In-memory Calculation Engine) is QuickSight's in-memory data store. When you import a dataset into SPICE, QuickSight caches the data locally for fast dashboard rendering instead of querying the source every time.

Now grant QuickSight access to your data services.

  • In the top-right corner of QuickSight, click your profile icon.
  • Select Manage QuickSight.
  • In the left navigation, choose Security & permissions.
  • Click Add or remove.
  • Check the box next to Amazon Athena and click Next.
  • Enable access to both of your S3 buckets: analytics-datalake-{initials}-2026 and athena-results-{initials}-2026.
  • Also enable Amazon Redshift access if prompted.
  • Click Update to save your permissions.

Why grant access to both S3 buckets?

QuickSight needs the data lake bucket to read your source data through Athena. It also needs the Athena results bucket because that's where Athena stores query output that QuickSight retrieves.

Don't see Manage QuickSight?

You must be an administrator of the QuickSight account to access this menu. If you just created the account, you should already be the admin. If the option is missing, confirm you're signed in with the same AWS user that created the QuickSight account.

help me troubleshoot why I can't see Manage QuickSight in my profile menu

Create datasets from Athena and Redshift

QuickSight separates data connections into two layers: a data source (the connection itself) and a dataset (the specific table or query you pull from that source). You'll create one dataset from your Athena curated Parquet table and another from your Redshift materialized view.

Start with the Athena dataset.

  • In QuickSight, choose Data from the left navigation pane.
  • Click Create, then New dataset.
  • Choose Athena as the data source type.
  • For Data source name, enter Athena-SalesSummary.
  • For Athena workgroup, select your workgroup (likely primary).
  • Click Validate connection to confirm QuickSight can reach Athena.
  • Click Create data source.
  • On the Choose your table screen, select AwsDataCatalog as the catalog.
  • Under Database, choose analytics_db.
  • Select the sales_summary table from the list.
  • Choose Import to SPICE for quicker analytics, then click Visualize.

Why import into SPICE?

Importing into SPICE caches your data in QuickSight's in-memory engine. This means your dashboard loads instantly without re-running Athena queries each time. For a BI dashboard that multiple people refresh throughout the day, SPICE saves both time and Athena scan costs.

QuickSight opens a new analysis with your Athena data. You'll come back to build visuals shortly. First, create the Redshift dataset.

  • Return to the QuickSight start page by clicking the QuickSight logo in the top-left corner.
  • Choose Data from the left navigation, then Create > New dataset.
  • Choose the Redshift Auto-discovered data source card.
  • For Data source name, enter Redshift-DailySales.
  • QuickSight should auto-detect your Redshift Serverless workgroup. Confirm the connection details match your active workgroup.
  • For Database name, enter dev (the default Redshift Serverless database).
  • Enter your database credentials (the admin username and password you set during Redshift Serverless setup).
  • Click Validate connection, then Create data source.
  • On the table selection screen, choose schema analytics and select mv_daily_sales (your materialized view).
  • Choose Import to SPICE for quicker analytics, then click Visualize.

Can't connect to Redshift Serverless?

If QuickSight can't reach your Redshift Serverless workgroup, you may need to configure VPC access. Check that the Redshift Serverless workgroup is set to publicly accessible, or create a VPC connection in QuickSight that matches the VPC of your Redshift workgroup.

help me troubleshoot connecting QuickSight to my Redshift Serverless workgroup

Build the dashboard with three visuals and a filter

With both datasets ready in SPICE, you can now build a dashboard that demonstrates the dual-engine architecture visually. You'll create three charts, each pulling from a different source, plus an interactive filter.

Start with a bar chart from your Athena data.

  • In QuickSight, choose Analyses from the left navigation.
  • Open the analysis that was created from your Athena-SalesSummary dataset.
  • In the Visual types panel, select Vertical bar chart.
  • Drag product_category to the X-axis field well.
  • Drag total_amount to the Value field well (QuickSight auto-aggregates as SUM).
  • Click the visual title and rename it to Sales by Category (Athena).

Now add a line chart from your Redshift data.

  • Click the + icon at the top of the analysis to add a new visual.
  • In the dataset dropdown (top-left of the visual), switch to your Redshift-DailySales dataset.
  • Select Line chart from the Visual types panel.
  • Drag order_date to the X-axis.
  • Drag daily_total (or running_total if available from your materialized view) to the Value field well.
  • Rename the visual title to Daily Trends & Running Totals (Redshift).

Finally, add a KPI card showing the performance comparison.

  • Add another new visual by clicking the + icon.
  • Select KPI from the Visual types panel.
  • For this card, you'll display a manually noted metric. Use a calculated field or simply label the KPI with query latency values you observed (e.g. Athena scan time vs Redshift execution time from your earlier queries).
  • Rename the visual title to Query Latency Comparison.

Comparing latency across engines

QuickSight KPI visuals work best with numeric values. Note the execution times from your Athena and Redshift queries earlier in the project. You can create a calculated field in QuickSight or simply annotate the KPI with text showing the comparison (e.g. Athena: 2.3s scan, Redshift: 0.4s pre-computed).

Now add a region filter for interactivity.

  • Click Filter in the left panel of the analysis.
  • Click Add filter and select the region field.
  • Click the filter you just created, then select Add to sheet > Filter control.
  • A dropdown control appears at the top of the analysis. Selecting a region now filters all visuals that share the region field.

To publish your analysis as a shareable dashboard:

  • Click Share in the top-right corner of the analysis.
  • Select Publish dashboard.
  • Name your dashboard Dual-Engine Analytics Dashboard and click Publish.

What makes this a multi-source dashboard?

Each visual in this dashboard pulls from a different engine. The bar chart queries your Athena curated Parquet layer (pay-per-scan, ideal for ad-hoc exploration). The line chart pulls from Redshift's pre-computed materialized view (sub-second response, ideal for repeated BI queries).

In production, you'd route ad-hoc exploration to Athena and predictable dashboards to Redshift. This dual-engine pattern gives you both flexibility and performance.

Visuals showing no data?

Confirm your SPICE import completed successfully. Check the dataset page for import status. If the import failed, verify that QuickSight has permission to read your S3 buckets (for Athena) or that your Redshift credentials are correct.

help me debug why my QuickSight visuals are showing no data after importing into SPICE

Your multi-source dashboard is live. You've now completed the full analytics pipeline, from raw data in S3 through to interactive BI visuals. Next up, take on the Secret Mission to automate the entire reporting pipeline with scheduled queries and stored procedures.

Secret mission

Automate Your Reporting Pipeline

In production, data teams don't run queries by hand every morning. They automate the entire pipeline. Can you wire up Athena named queries, Redshift stored procedures, EventBridge schedules, and QuickSight email reports into a zero-intervention daily reporting system?

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. S3 and Glue are negligible cost. Redshift Serverless only charges when queries run (it shuts down when idle). QuickSight will charge after the 30-day free trial ends.

Cost warning

QuickSight will begin charging after 30 days if you do not unsubscribe. Redshift Serverless charges per RPU-hour when queries are active, covered by the $300 free trial credit for 90 days. If your free trial credit is exhausted or expires, you will be billed at $1.50 per hour minimum.

Resources you used:

  • 2 S3 buckets (analytics-datalake-{initials}-2026 and athena-results-{initials}-2026).
  • AWS Glue Data Catalog database (analytics_db) and tables.
  • Athena query results stored in S3.
  • Redshift Serverless workgroup and namespace.
  • IAM role (RedshiftS3AccessRole).
  • QuickSight account (30-day free trial).
  • EventBridge rule, Lambda function (Secret Mission only).

Keep everything running

No action needed. Choose this if you are still actively building or exploring your analytics architecture.

  • S3 and Glue Data Catalog cost is negligible for small datasets (well within free tier).
  • Athena charges only when you run queries ($5/TB scanned). Your tutorial data is under 10 MB.
  • Redshift Serverless shuts down automatically when idle. No charge until you run queries again. Your $300 free trial credit covers usage for 90 days.
  • QuickSight is free for 30 days. After the trial, charges begin at $3+/user/month.

Pause - I'll come back to this later

Shut down the resources that accumulate charges while keeping your data intact so you can resume later.

  • Delete your QuickSight account to avoid post-trial charges. Open https://us-east-1.quicksight.aws.amazon.com/sn/console/unsubscribe in your browser while signed in to AWS, then follow the prompts to unsubscribe.
  • In the Redshift console, go to Workgroup configuration. Select your workgroup and choose Delete Amazon Redshift Serverless instance. Type delete to confirm. This stops all compute charges while your namespace data remains.
  • Keep your S3 buckets, Glue Data Catalog, and Athena configuration in place. These cost near-zero for small datasets.
  • If you completed the Secret Mission, disable the EventBridge rule to stop automated pipeline runs. In the EventBridge console, select your rule and choose Disable.

Delete - I don't want to use this again

Remove all project resources completely. Follow these steps in order.

  • Delete QuickSight: Open https://us-east-1.quicksight.aws.amazon.com/sn/console/unsubscribe while signed in to AWS. Toggle off Account termination protection, type the confirmation word shown on screen, and choose Delete. This deletes all dashboards, datasets, and analyses.
  • Delete Redshift Serverless workgroup: Open the Redshift console at https://console.aws.amazon.com/redshiftv2/. Choose Workgroup configuration in the left menu. Select your workgroup and choose Delete Amazon Redshift Serverless instance. Type delete to confirm.
  • Delete Redshift Serverless namespace: After the workgroup finishes deleting, choose Namespace configuration in the left menu. Select your namespace, choose Actions, then Delete namespace. Type delete to confirm.
  • Delete the IAM role: In the IAM console, go to Roles. Search for RedshiftS3AccessRole. Select it and choose Delete.
  • Delete the Glue database: Open the AWS Glue console at https://console.aws.amazon.com/glue. Choose Databases in the left menu. Select the checkbox next to analytics_db and choose Remove. This also deletes all tables in the database.
  • Empty and delete S3 buckets: Open the S3 console. For each bucket (analytics-datalake-{initials}-2026 and athena-results-{initials}-2026), select it and choose Empty. Type permanently delete to confirm. After the bucket is empty, select it again and choose Delete. Type the bucket name to confirm.
  • If you completed the Secret Mission, also delete these resources: In the EventBridge console, select your daily-analytics-pipeline rule and choose Delete. In the Lambda console, select the trigger-athena-daily-query function and choose Actions > Delete.

That's a Wrap!

That's a Wrap!

Nice work! You just built a production-grade dual-engine analytics architecture on AWS, processing e-commerce data through both a serverless data lake and a cloud data warehouse.

You now understand exactly when to use Athena (ad-hoc exploration, schema-on-read, pay-per-scan) versus Redshift (repeated BI workloads, predictable performance, pre-computed views). This is the architecture pattern used by data engineering teams at scale.

You've learned how to:

  • Built a serverless data lake on S3 with automated schema discovery via AWS Glue Crawlers, then wrote advanced Athena SQL using window functions, CTEs, and CTAS to transform raw CSV into optimized Parquet reporting tables.
  • Set up Redshift Serverless with proper data modeling (distribution keys, sort keys, materialized views) and connected it to your data lake via Redshift Spectrum for hybrid queries across local and S3 data.
  • Built a multi-source QuickSight dashboard pulling from both Athena and Redshift, demonstrating how to choose the right engine for each analytics workload.
  • Automated the entire pipeline with scheduled queries, stored procedures, and email reports that run daily without manual intervention.

Ready to quiz yourself?