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 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.

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

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

flowchart LR
  App["Customer application"] --> DB["Production database"]
  Analysts["Analysts and dashboards"] -->|"Large scans and joins"| DB

This avoids building an analytics system, but reporting traffic now competes with customer traffic. The schema also contains 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.

Naive option 3: load everything into Redshift immediately

A warehouse may be right for frequent, complex business intelligence. It is unnecessary overhead for data that is retained mainly for audit or queried a few times per month. Storage and query needs should not be forced into one decision.

3. What Breaks

Follow one analyst trying to calculate last month's sales by country.

Failure 1: the analyst cannot find the trusted dataset

Three teams exported orders with different column names. S3 knows object keys and bytes; it does not automatically tell the analyst which business schema is approved.

Failure 2: the query reads far too much data

The analyst needs one month and three columns, but the query scans years of row-oriented CSV files. It is slow and expensive because the physical data layout does not match the question.

Failure 3: a transformation bug overwrites the only copy

A cleaning job converts prices incorrectly and replaces the source files. The team cannot reproduce the corrected dataset because it did not retain immutable raw inputs.

Failure 4: dashboard refreshes behave like exploration

Hundreds of users refresh the same complex joins. A serverless ad hoc query tool can run them, but repeated high-concurrency reporting may need prepared datasets or a warehouse optimized for that workload.

Failure 5: broad access exposes sensitive columns

An analyst receives access to the whole bucket just to query a curated sales table. The same location also contains raw customer details.

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.

flowchart LR
  Sources["Databases, logs,<br/>partner files"] --> Raw["Amazon S3 raw zone<br/>original input"]

S3 is the durable storage foundation. It can hold structured tables, semi-structured JSON, logs, images, and other objects without requiring one database schema at ingestion time.

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

Keep the raw zone replayable. If a transformation rule is wrong, the team should be able to run a corrected job from the original input.

Step 2: create trust zones

Do not let every consumer guess which files are ready.

flowchart LR
  Raw["raw/<br/>original and restricted"] --> Transform["validation and transformation"]
  Transform --> Curated["curated/<br/>clean and optimized"]
  Curated --> Published["published/<br/>approved business datasets"]

• 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 names are an architecture convention, not special S3 features. The boundaries become real through separate locations, permissions, ownership, and processing rules.

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:

flowchart LR
  Curated["S3 curated objects"] --> Catalog["Glue Data Catalog<br/>table name, columns, types,<br/>partitions, S3 location"]

A catalog table is metadata that points to data. The rows remain in S3.

A Glue crawler can inspect supported data, infer a schema, and create or update catalog tables and partitions. A crawler does not prove that the data is correct, remove duplicates, or define the business meaning of a column. For controlled production datasets, explicit schemas and reviewed schema 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.

flowchart LR
  Raw["S3 raw<br/>CSV and JSON"] --> Job["AWS Glue ETL job<br/>clean, join, convert"]
  Catalog["Glue Data Catalog"] -.->|"schema metadata"| Job
  Job --> Curated["S3 curated<br/>compressed Parquet"]

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. A query asking for country and revenue can avoid reading unrelated columns. Compression reduces stored and scanned bytes.

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, Athena can skip unrelated date partitions. A poor partition key can instead produce too many tiny directories and files, so partition for actual query patterns rather than every possible column.

Step 5: ask occasional questions with Athena

Now the organization has cataloged, optimized data in S3. Add serverless SQL:

flowchart LR
  Analyst["Analyst"] --> Athena["Amazon Athena<br/>serverless SQL"]
  Athena --> Catalog["Glue Data Catalog"]
  Catalog --> Curated["S3 curated data"]
  Athena --> Results["S3 query-results location"]

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 run complex joins over governed business models. That pressure is different from an analyst exploring S3 once.

flowchart LR
  Curated["S3 curated data"] --> Redshift["Amazon Redshift<br/>analytics warehouse"]
  BI["Repeated BI queries"] --> Redshift

Redshift is the stronger signal when the question emphasizes a data warehouse, predictable performance for repeated complex analytics, modeled data, or BI concurrency. It can load curated data and can also work with external data patterns, 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.

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

Batch files can go directly to S3. Click events may arrive continuously.

flowchart LR
  Producers["Click and application events"] --> Stream["Kinesis Data Streams<br/>durable real-time stream"]
  Stream --> Consumers["Custom stream consumers"]
  Stream --> Delivery["Delivery or processing path"]
  Delivery --> Raw["S3 raw zone"]

Choose Kinesis Data Streams when applications need a stream with custom consumers, ordering within a shard, replay within the retention window, or low-latency processing. Choose Amazon Data Firehose when the main requirement is managed delivery of streaming records into destinations such as S3 with less custom consumer code.

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

flowchart LR
  Athena["Athena"] --> QuickSight["Amazon QuickSight<br/>dashboards and BI"]
  Redshift["Redshift"] --> QuickSight
  QuickSight --> Users["Business users"]

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

Completed architecture

flowchart LR
  Batch["Batch sources"] --> Raw["S3 raw zone"]
  Events["Streaming events"] --> Ingest["Kinesis / Data Firehose path"]
  Ingest --> Raw
  Raw --> Glue["Glue ETL<br/>validate and convert"]
  Glue --> Curated["S3 curated zone<br/>Parquet and partitions"]
  Catalog["Glue Data Catalog<br/>metadata"] -.-> Raw
  Catalog -.-> Curated
  Analyst["Ad hoc analyst"] --> Athena["Athena"]
  Athena --> Catalog
  BI["Repeated warehouse workload"] --> Redshift["Redshift"]
  Curated --> Redshift
  Athena --> QuickSight["QuickSight"]
  Redshift --> QuickSight

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 and use least-privilege IAM roles and bucket policies. 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 data at rest by default. Use customer-managed KMS keys when the requirement calls for customer-controlled key policy, separation of duties, or specific auditing. The ingestion, Glue, Athena, and Redshift roles must have the S3 and KMS permissions their paths require.

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 can manage centralized and 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. CloudTrail data events can provide object-level evidence for selected sensitive buckets, with additional cost and 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

Jobs should write deterministic partitions or use an explicit commit pattern so a retry does not silently duplicate records or expose a half-written dataset.

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. Versioning helps with accidental overwrite or deletion. Cross-Region replication or backup copies enter only when regional recovery, compliance, or data-sovereignty requirements justify them.

For streaming paths, monitor consumer lag 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 Parquet or 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.

Use Athena workgroups to separate teams, configure controls, and observe usage. Use query history and scanned bytes 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:

Lifecycle raw data only after considering replay, compliance, and retrieval time. Moving data to an archive 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

flowchart LR
  Sources["Batch and streaming sources"] --> Raw["S3 raw<br/>keep the source"]
  Raw --> Glue["Glue<br/>describe and prepare"]
  Glue --> Curated["S3 curated<br/>optimize and trust"]
  Curated --> Athena["Athena<br/>ad hoc SQL"]
  Curated --> Redshift["Redshift<br/>warehouse analytics"]
  Athena --> QuickSight["QuickSight<br/>present answers"]
  Redshift --> QuickSight

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?