# Build a Serverless Data Warehouse on AWS

Key Takeaways

  • A serverless data warehouse on AWS typically combines S3 (storage), AWS Glue (ETL/cataloging), Athena (ad hoc SQL), and Redshift Serverless (structured analytics) — no provisioned clusters to manage.
  • You pay only for queries run and data scanned, which makes this architecture dramatically cheaper for spiky or unpredictable analytics workloads than a fixed-capacity warehouse.
  • Partitioning and columnar formats (Parquet) are the single biggest cost and performance lever in this stack.
  • Redshift Serverless adds structured, high-concurrency BI workloads on top of the same S3 data lake without duplicating storage.

Why Go Serverless for a Data Warehouse

Traditional data warehouses require provisioning clusters sized for peak load, which means paying for idle capacity most of the time. A serverless data warehouse architecture on AWS inverts that: storage lives cheaply in S3, and compute (Glue, Athena, Redshift Serverless) scales up only when a query runs and scales back to zero cost between queries. For teams with bursty reporting workloads — end-of-month closes, ad hoc analyst questions — this is usually both cheaper and simpler to operate than a fixed-size cluster.

Reference Architecture

<svg viewBox="0 0 640 260" xmlns="http://www.w3.org/2000/svg" style="width:100%;max-width:640px;font-family:sans-serif;font-size:12px">
  <style>
    .box{fill:none;stroke:currentColor;stroke-width:1.5}
    .lbl{fill:currentColor;text-anchor:middle}
    .sub{fill:currentColor;text-anchor:middle;font-size:10px;opacity:0.8}
    .arrow{stroke:currentColor;stroke-width:1.5;marker-end:url(#arr5)}
  </style>
  <defs>
    <marker id="arr5" markerWidth="8" markerHeight="8" refX="6" refY="3" orient="auto">
      <path d="M0,0 L6,3 L0,6 Z" fill="currentColor"/>
    </marker>
  </defs>
  <rect class="box" x="10" y="90" width="120" height="50" rx="6"/>
  <text class="lbl" x="70" y="112">Source</text>
  <text class="sub" x="70" y="128">Systems</text>
  <line class="arrow" x1="130" y1="115" x2="185" y2="115"/>
  <rect class="box" x="190" y="90" width="120" height="50" rx="6"/>
  <text class="lbl" x="250" y="112">AWS Glue</text>
  <text class="sub" x="250" y="128">ETL + Crawler</text>
  <line class="arrow" x1="310" y1="115" x2="365" y2="115"/>
  <rect class="box" x="370" y="90" width="130" height="50" rx="6"/>
  <text class="lbl" x="435" y="112">S3 Data Lake</text>
  <text class="sub" x="435" y="128">Parquet, partitioned</text>
  <line class="arrow" x1="435" y1="140" x2="200" y2="200"/>
  <line class="arrow" x1="435" y1="140" x2="435" y2="200"/>
  <line class="arrow" x1="435" y1="140" x2="580" y2="200"/>
  <rect class="box" x="130" y="205" width="130" height="45" rx="6"/>
  <text class="lbl" x="195" y="230">Athena</text>
  <rect class="box" x="370" y="205" width="130" height="45" rx="6"/>
  <text class="lbl" x="435" y="230">Redshift Serverless</text>
  <rect class="box" x="515" y="205" width="110" height="45" rx="6"/>
  <text class="lbl" x="570" y="230">QuickSight</text>
</svg>

Step 1: Land Raw Data in S3

Ingest from source systems into an S3 "raw" prefix, partitioned by date for efficient querying later.

aws s3 cp orders_2026-02-14.json \
  s3://my-data-lake/raw/orders/year=2026/month=02/day=14/orders_2026-02-14.json

Step 2: Catalog and Transform with Glue

A Glue Crawler builds a schema in the Glue Data Catalog automatically from the raw data, which both Athena and Redshift Serverless can then query.

import boto3

glue = boto3.client("glue")

glue.create_crawler(
    Name="orders-raw-crawler",
    Role="arn:aws:iam::123456789012:role/GlueCrawlerRole",
    DatabaseName="data_lake_db",
    Targets={"S3Targets": [{"Path": "s3://my-data-lake/raw/orders/"}]},
    SchemaChangePolicy={"UpdateBehavior": "UPDATE_IN_DATABASE"}
)

glue.start_crawler(Name="orders-raw-crawler")

A Glue ETL job then converts raw JSON into partitioned, compressed Parquet in a "curated" layer — the format that makes Athena and Redshift Serverless both fast and cheap.

# Glue ETL job script (PySpark)
df = glueContext.create_dynamic_frame.from_catalog(
    database="data_lake_db", table_name="orders_raw"
).toDF()

df.write.partitionBy("year", "month", "day") \
    .mode("overwrite") \
    .parquet("s3://my-data-lake/curated/orders/")

Step 3: Query Ad Hoc with Athena

Once cataloged, any analyst can query the curated layer directly with standard SQL — no infrastructure to provision.

SELECT
  product_category,
  SUM(order_total) AS revenue,
  COUNT(*) AS order_count
FROM data_lake_db.orders_curated
WHERE year = 2026 AND month = 2
GROUP BY product_category
ORDER BY revenue DESC
LIMIT 10;

Athena bills per terabyte scanned (currently around $5/TB), which is why partition pruning (filtering on year/month above) and Parquet's columnar compression matter directly to your bill.

Step 4: Serve Structured BI with Redshift Serverless

For recurring dashboards and higher-concurrency BI traffic, Redshift Serverless reads directly from the same S3 data via Redshift Spectrum, avoiding a separate ETL/copy step.

CREATE EXTERNAL SCHEMA curated_lake
FROM DATA CATALOG
DATABASE 'data_lake_db'
IAM_ROLE 'arn:aws:iam::123456789012:role/RedshiftSpectrumRole';

SELECT product_category, SUM(order_total) AS revenue
FROM curated_lake.orders_curated
WHERE year = 2026
GROUP BY product_category;
aws redshift-serverless create-workgroup \
  --workgroup-name analytics-wg \
  --namespace-name analytics-ns \
  --base-capacity 32 \
  --publicly-accessible false

base-capacity here is measured in Redshift Processing Units (RPUs) and auto-scales up under load, which is the "serverless" part — no fixed cluster size to pre-plan.

Cost Control Tips

  • Partition aggressively on query predicates (date, region, category) to minimize scanned data.
  • Convert to Parquet — typically a 70-90% reduction in bytes scanned versus raw JSON/CSV.
  • Set Athena workgroup query limits to cap runaway cost from an unbounded ad hoc query.
aws athena update-work-group \
  --work-group analytics-wg \
  --configuration-updates '{"BytesScannedCutoffPerQuery": 10737418240}'

That example caps any single query at 10 GB scanned, preventing an accidental full-table scan from generating a large bill.

FAQ

Q: Do I need Redshift Serverless if I already have Athena? A: Not always — Athena covers most ad hoc analytics. Redshift Serverless adds value when you need high query concurrency, complex joins, or materialized views for repeat dashboard queries.

Q: How much does a serverless data warehouse cost compared to a provisioned one? A: It depends on query volume — for spiky, low-to-moderate volume workloads it's usually far cheaper; for constant, high-concurrency 24/7 analytics, a provisioned cluster can become more cost-predictable.

Q: What file format should I use in the S3 data lake? A: Parquet, columnar and compressed, is the standard choice for both Athena and Redshift Spectrum performance.

Q: Can this architecture handle streaming data? A: Yes — add Kinesis Data Firehose or MSK to land streaming data into S3 continuously, which Glue and Athena/Redshift can then query the same way.

Let F9infotech Build Your Serverless Data Warehouse

Designing a cost-efficient serverless data warehouse means getting partitioning, file formats, and query engine choice right from the start. F9infotech's AWS data engineering team can architect and implement this for you. Get in touch to scope your data warehouse project.