Analytics Data Lake On S3

1. Situation

An online retailer has useful data in several places:

• the order database contains purchases;
• application servers produce logs;
• the website produces click events;
• partners send daily comma-separated values (CSV) files; and
• analysts maintain spreadsheet extracts.

The business asks questions that cross those systems:

• Which campaigns lead to completed orders?
• Which products are commonly returned?
• Did yesterday's error spike affect revenue?
• Can executives see a daily sales dashboard?

Running these questions against the production order database is risky. Large analytical scans compete with the application's small, time-sensitive reads and writes. The operational database was designed to place orders, not explore years of history from many sources.

The company needs a separate analytics foundation that can retain large amounts of data cheaply and let different tools use it.

That is the problem a data lake solves. It is a shared analytics system built around keeping many kinds of source data in low-cost storage. S3 holds the data, while separate tools describe it, clean it, control access, query it, and present results. A data lake is an architecture—not one AWS service or simply a large bucket.

operational systems -> durable analytics storage -> questions and dashboards

Tap to exploreOpen diagram

The analogy is useful, but remember the technical boundary: storing an object, describing a dataset, transforming records, running SQL, and drawing a dashboard are separate jobs.

2. Naive Design

Naive option 1: query the production database

Tap to exploreOpen diagram

This avoids building an analytics system, but reporting traffic now competes with customer traffic. The database's schema—its tables, columns, and relationships—also describes only operational data, not every log, click, and partner file.

Naive option 2: put every file in one S3 bucket

s3://company-data/final2.csv
s3://company-data/logs-new.json
s3://company-data/orders-copy-fixed.csv

S3 stores these objects durably, but nobody knows which schema is trustworthy, who owns a file, or which version a dashboard should use. Cheap object storage alone is not a usable data lake.

This disorganized collection is often called a data swamp.

Tap to exploreOpen diagram

Naive option 3: load everything into Redshift immediately

A data warehouse is a database designed for repeated analytical queries across prepared business data. It may be right for frequent, complex business intelligence (BI), but it is unnecessary overhead for data retained mainly for audit or queried only a few times per month. Storage and query needs should not be forced into one decision.

3. What Breaks

These failures come from incomplete versions of the same analytics system. Follow one analyst trying to calculate last month's sales by country. Each story shows the starting shortcut, what breaks, and the missing capability that the completed architecture adds.

Failure 1: the analyst cannot find the trusted dataset

Starting design: Three teams export order files into S3, but there is no catalog or agreed trusted dataset.

What breaks: The files use different column names, so the analyst cannot tell which structure and version are approved. S3 stores object keys and bytes; it does not supply that business meaning. The missing controls are a catalog—a searchable description of each dataset—and separate locations that distinguish original input from approved output. The architecture later calls those locations trust zones.

Failure 2: the query reads far too much data

Starting design: The analyst runs SQL directly over years of row-oriented CSV files.

What breaks: The report needs one month and three columns, but the query reads every row and column. It becomes slow and expensive because the file layout does not match the question. A better layout groups values by column and files by date, allowing the query to skip unrelated data. The architecture later names these techniques column-oriented storage and partitioning.

Failure 3: a transformation bug overwrites the only copy

Starting design: A cleaning job reads source files, converts prices, and overwrites those same files with its output.

What breaks: A bug converts the prices incorrectly, and the only original copy is gone. The team cannot run a corrected transformation from the source. The missing control is an immutable raw input: an original file that the processing pipeline preserves instead of changing.

Failure 4: dashboard refreshes behave like exploration

Starting design: Every dashboard refresh runs the same complex joins through an on-demand query path intended for exploration.

What breaks: Hundreds of users repeatedly trigger expensive work and expect predictable response times. An ad hoc query is useful for a question created as the need arises; a frequently refreshed dashboard may instead need prepared datasets or a data warehouse built for repeated, concurrent queries.

Failure 5: broad access exposes sensitive columns

Starting design: An analyst receives access to the whole S3 bucket just to query one approved sales dataset.

What breaks: The same access also exposes raw customer details that the report does not need. Separate locations and permissions by trust and sensitivity so access to a published report does not imply access to every source file.

The architecture must separate storage, organization, preparation, query, and presentation.

4. AWS Architecture

Build the lake one missing capability at a time.

Step 1: protect production by landing analytics data in S3

Start with batch exports from databases, applications, and partners. Store the original files in a raw zone: a private S3 location that preserves what arrived before later jobs clean or reorganize it.

Tap to exploreOpen diagram

S3 keeps the analytics files independently of the production database. It can store tables, JSON, logs, images, and other object types without forcing every source into one database schema before the file arrives.

This first step solves retention and workload isolation. It does not make the files clean or queryable.

Keep the raw zone replayable. Here, replay means running processing again from the preserved original input. If a transformation rule is wrong, the team should be able to fix it and rebuild the later output.

Step 2: create trust zones

Do not let every consumer guess which files are ready. Move data through clearly named trust stages: preserve the original input, create a cleaned version for analysis, and publish stable datasets for reports.

Tap to exploreOpen diagram

• Raw preserves what arrived and usually has the tightest access.
• Curated contains validated, standardized, and query-efficient data.
• Published contains datasets with a stable business meaning for reports or consumers.

These zone names are an architecture convention, not special S3 features. S3 does not automatically know that curated/ is more trusted than raw/. The boundaries become real only through separate locations, permissions, owners, and pipelines that control how data moves between them.

Step 3: describe the data with the Glue Data Catalog

An object can exist in S3 without anyone knowing its columns or data types. Add metadata—a description of the data rather than another copy. In this catalog, a partition describes a subset such as one date's records:

Tap to exploreOpen diagram

A catalog table records a table name, column definitions, file format, partitions, and the S3 location containing the objects. The catalog does not move the rows into Glue; the actual data remains in S3.

A Glue crawler is an automated metadata discoverer. It can inspect supported files, infer likely column names and types, and create or update catalog tables and partitions. It does not clean records, remove duplicates, or prove that an inferred column has the correct business meaning. For controlled production datasets, explicit schemas and reviewed changes may be safer than accepting every inference automatically.

Step 4: use Glue ETL to create query-friendly data

Raw data often needs validation, standardization, deduplication, and a better physical format, such as compressed Apache Parquet, a column-oriented file format designed for analytics.

Tap to exploreOpen diagram

ETL means extract, transform, and load:

1. read source data;
2. apply defined transformations; and
3. write the result to its target.

For analytics, Parquet is often useful because it stores values by column instead of writing every field of each row together. If an order has 30 columns but a query needs only country and revenue, a columnar layout can avoid reading the other 28 columns. Compression further reduces stored and scanned bytes.

Tap to exploreOpen diagram

Partitioning groups data by values that queries commonly filter, such as date:

s3://analytics/curated/orders/order_date=2026-08-06/part-0001.parquet

If a query filters on order_date = '2026-08-06', Athena can read that partition and skip the directories for other dates. This is partition pruning. A poor partition choice—such as creating a partition for nearly every unique customer—can produce huge numbers of tiny directories and files, so partition for common selective filters rather than every possible column.

Tap to exploreOpen diagram

Step 5: ask occasional questions with Athena

Now analysts need to ask questions without first loading the S3 files into a database server. Athena provides serverless SQL for that job:

Tap to exploreOpen diagram

Athena reads the table definition from the catalog and scans the matching S3 objects. There is no database server or cluster for the learner to provision for this path.

Use Athena when the question is ad hoc, interactive, or occasional and the data already belongs in S3. Its simplest cost and performance lever is reducing how many bytes a query scans.

Athena does not clean the raw data merely because SQL can read it. Good file layout remains part of the architecture.

Step 6: add Redshift only when the workload becomes a warehouse workload

Suppose hundreds of dashboard users repeatedly combine large tables and calculate the same agreed metrics, such as revenue after returns. That workload is different from one analyst exploring S3 occasionally.

Use Redshift when analytics becomes a steady shared workload: many users, repeated complex joins, agreed business definitions, and predictable performance needs. Athena runs queries on demand over S3; Redshift provides a dedicated data warehouse for work that runs repeatedly. Redshift can load curated data and also work with external data, but those deeper mechanics belong in the Amazon Redshift service article.

The exam decision is not “Which service supports SQL?” Both Athena and Redshift do. Ask where the data lives and how the queries behave.

Tap to exploreOpen diagram

Step 7: add a streaming path only for continuously arriving events

Batch files can go directly to S3 because they arrive as complete files on a schedule. Click events are different: they may arrive continuously, one record at a time.

The first question is whether applications need to process the stream themselves or whether AWS mainly needs to deliver the records somewhere.

Choose Amazon Kinesis Data Streams when custom applications need to read the retained stream—for example, when several consumers process the same events independently, records need ordering within a shard (one partition of the stream), or consumers may reread earlier records during the retention window. Choose Amazon Data Firehose when the main job is managed buffering and delivery to S3 or another supported destination with less custom consumer code.

Tap to exploreOpen diagram

Neither service is required just because the final destination is a data lake. Daily exports are still batch ingestion.

Step 8: put QuickSight at the presentation layer

Tap to exploreOpen diagram

QuickSight helps people explore visualizations and dashboards. It does not replace S3, the catalog, transformation, or the query engine beneath it.

Completed architecture

Tap to exploreOpen diagram

5. Request Or Data Flow

Learn the architecture through three flows.

Flow 1: a daily partner file becomes trusted data

1. The partner file lands in the S3 raw zone.
2. The pipeline records its source and arrival details.
3. A Glue job reads the file, validates its schema, and applies transformation rules.
4. The job writes compressed Parquet into a curated date partition.
5. The Data Catalog contains the table and partition metadata.
6. Consumers read the curated dataset; the raw object remains available for replay according to retention policy.

Flow 2: an analyst runs an Athena query

1. The analyst submits SQL to Athena.
2. Athena reads the table definition from the Glue Data Catalog.
3. The query filter identifies the relevant partitions.
4. Athena reads only the needed objects and columns when the layout permits it.
5. Athena writes query results to the configured S3 results location.

The catalog answers “where and how is this dataset described?” S3 supplies the bytes. Athena performs the query.

Flow 3: a dashboard uses repeated business logic

1. Curated data is loaded or exposed through the chosen Redshift design.
2. Warehouse models define stable business entities and calculations.
3. QuickSight queries Redshift or uses an appropriate prepared dataset.
4. Business users view the dashboard without scanning every raw object on each refresh.

6. Security Controls

Keep every lake zone private

Enable S3 Block Public Access to prevent public permissions from exposing lake data. Use least-privilege AWS Identity and Access Management (IAM) roles and bucket policies so each service or user receives only the required access. A dashboard user does not need direct permission to every raw object.

Separate data by trust and sensitivity

Raw customer data, curated aggregates, and public-safe reports should not inherit one broad permission model. Separate roles and locations make the intended boundary easier to enforce and audit.

Coordinate encryption permissions

S3 encrypts stored data by default. Use a customer-managed AWS Key Management Service (KMS) key when the requirement calls for customer control over encryption-key permissions or auditing. Every service role that reads or writes the encrypted data must be allowed to use that key, including the ingestion, Glue, Athena, and Redshift roles where applicable.

Athena query results are data too. Protect the results location rather than focusing only on source buckets.

Add centralized governance when required

AWS Lake Formation provides a central place to manage fine-grained data lake permissions. Introduce it when the scenario requires governed sharing across teams, accounts, tables, columns, or rows—not merely because S3 exists.

Monitor relevant API activity and data access. AWS CloudTrail data events can record object-level actions, such as reads and writes, for selected sensitive buckets, with additional cost and log volume.

7. Resilience Controls

Retain a replayable source

A transformation error should be recoverable by fixing the code and rebuilding the curated output from raw data. Retention must be long enough to detect and repair realistic errors.

Make transformations restartable

A failed transformation may need to run again. Rerunning the job for order_date=2026-08-06 should replace or reconcile that date's output instead of appending the same records again. In other words, the same input should produce the same planned result—often called a deterministic output. Determinism does not prevent consumers from seeing partially written files. For that separate publication boundary, write rebuilt data to a new run-specific location, validate it, and then update the catalog or consumer-facing reference to that completed location.

Watch freshness, not only job success

A successful job that processed zero new files may still leave yesterday's dashboard stale. Monitor expected arrivals, newest partition time, records processed, invalid records, job failures, and end-to-end data delay.

Choose additional copies from the failure requirement

S3 stores general-purpose bucket data redundantly across multiple Availability Zones, separate infrastructure locations within one AWS Region. Versioning helps with accidental overwrite or deletion. Cross-Region Replication copies objects to a bucket in another Region and belongs only when regional recovery, compliance, or data-location requirements justify it.

For streaming paths, monitor consumer lag—how far a consumer has fallen behind the newest stream records—and delivery failures. Retention and replay must cover the longest realistic outage.

8. Performance Controls

Use the physical layout to eliminate unnecessary work:

• convert frequently queried data from row-oriented text to columnar formats such as Parquet or Optimized Row Columnar (ORC);
• compress it;
• partition on common selective filters such as date;
• select only needed columns;
• compact tiny objects into appropriately sized files; and
• avoid partitioning so finely that metadata and small-file overhead dominate.

An Athena workgroup groups users and queries so the organization can apply controls and measure usage separately. Use workgroups, query history, and scanned-byte metrics to find waste.

For stable, repeated, high-concurrency analytical workloads, evaluate Redshift or prepared BI datasets instead of repeatedly treating the workload as ad hoc exploration.

9. Cost Controls

Each layer has a different cost driver:

Apply S3 lifecycle rules to move or delete raw data only after considering replay, compliance, and retrieval time. Moving data to an archive storage class reduces storage price but can add retrieval delay and cost.

The cheapest analytics query is often the one the data layout lets the engine skip.

10. Exam Variants

Read the requirement in layers. “SQL” alone is not enough to choose between Athena and Redshift.

11. Common Traps

• Do not call an unorganized bucket a data lake architecture.
• Do not say the Glue Data Catalog stores the dataset; it stores metadata.
• Do not say a crawler cleans or validates business data; it primarily discovers and records metadata.
• Do not query production transaction databases for heavy analytics when isolation is required.
• Do not use Redshift automatically for every SQL question.
• Do not expect Athena to make poor file layout inexpensive.
• Do not partition by every column or produce huge numbers of tiny files.
• Do not let dashboards read sensitive raw zones when published datasets are enough.
• Do not add streaming services to a daily batch requirement.
• Do not delete raw data before the realistic replay window has passed.

Final Mental Model: One-Minute Review

Tap to exploreOpen diagram

If you remember only one sentence, remember this:

S3 keeps the data, Glue makes it understandable and usable, Athena or Redshift answers the question, and QuickSight communicates the answer.

12. Related Topics

Deepen the individual service mechanics with Amazon S3, AWS Glue, Amazon Athena, Amazon Redshift, Amazon Kinesis Data Streams, and Amazon QuickSight.

Official AWS references:

• Using AWS Glue crawlers to populate the Data Catalog
• Optimizing data for Athena
• What is Amazon Redshift?
• What is Amazon Kinesis Data Streams?