Design High Performing Architectures · Topic 7 of 7
Data Transformation and Analytics
Transform and analyze data on AWS with Glue, Athena, EMR, Redshift, Lake Formation, QuickSight, OpenSearch Service and Managed Service for Apache Flink.
- 4 min
- Glue is serverless ETL plus the Data Catalog, and crawlers discover schemas.
- Athena queries S3 with SQL, charges by data scanned, and gets cheaper with Parquet, partitions and compression.
- EMR is for Spark and Hadoop with cluster level control, and can use Spot Instances for task nodes.
What it is
Raw data is rarely ready to use. Transformation cleans it, joins it and converts it to a better format. This is often called extract, transform and load (ETL).
After transformation, you query the data and visualize it. The common AWS design is a data lake: raw and curated data kept in Amazon S3, with serverless tools on top, and sometimes a data warehouse for fast structured reporting.
How it works
AWS Glue
AWS Glue is a serverless data integration service. A Glue job runs ETL code (usually Apache Spark) without you managing servers. A crawler scans data, infers its schema and stores table definitions in the Glue Data Catalog, a central metadata store that Athena, EMR and Redshift Spectrum can use.
Choose Glue when the question says "serverless ETL" or "discover the schema and catalog the data". Glue DataBrew offers a visual interface for cleaning data without writing code.
Amazon Athena
Athena runs standard SQL queries directly on files in S3, with no servers to manage. You pay by the amount of data scanned, so you reduce cost and speed up queries by using these habits.
- Store data in a columnar format such as Parquet or ORC instead of CSV or JSON.
- Partition data by a column such as date so queries read only the folders they need.
- Compress files, and avoid huge numbers of very tiny files.
Athena fits ad hoc analysis, log analysis for CloudTrail or load balancer logs, and occasional queries. For constant heavy reporting across many tables, a warehouse may fit better.
Amazon EMR
Amazon EMR runs big data frameworks such as Apache Spark, Hadoop, Hive and Presto on managed clusters. Choose it when you need those frameworks with deep control over configuration, or must migrate existing Hadoop jobs. Task nodes can run on Spot Instances to cut cost, since losing them does not lose data stored on core nodes.
Amazon Redshift
Redshift is a columnar data warehouse for complex SQL across large structured datasets. It can load data from S3, and Redshift Spectrum queries data that stays in S3. It is a better fit than Athena for steady, high concurrency reporting with joins across large tables.
AWS Lake Formation
Lake Formation helps you build and secure a data lake. It centralizes permissions, down to table, column and row level, and applies them across services like Athena, Redshift Spectrum and EMR. Choose it when the requirement is fine grained access control or governance across a lake.
Streaming analytics and search
Amazon Managed Service for Apache Flink analyzes streaming data in real time, for example by computing metrics over time windows. Amazon OpenSearch Service provides search and log analytics with dashboards, and it often receives data from Firehose.
Visualization
Amazon QuickSight is a business intelligence (BI) service for dashboards. It connects to Athena, Redshift, RDS and many other sources, and can cache data in its in memory engine called SPICE for fast dashboards.
Choosing between services
| Requirement | Service |
|---|---|
| Serverless ETL and schema discovery | AWS Glue |
| Ad hoc SQL on S3 with no servers | Amazon Athena |
| Spark or Hadoop with cluster control | Amazon EMR |
| Fast structured analytics and reporting | Amazon Redshift |
| Column and row level permissions for a lake | AWS Lake Formation |
| Real time stream analytics | Managed Service for Apache Flink |
| Search and log analytics | Amazon OpenSearch Service |
| Dashboards | Amazon QuickSight |
When the scenario says "reduce Athena cost and improve speed", the answer is Parquet plus partitioning. When it says "serverless" and "transform", think Glue before EMR.
What to remember for the exam
- Glue is serverless ETL plus the Data Catalog, and crawlers discover schemas.
- Athena queries S3 with SQL, charges by data scanned, and gets cheaper with Parquet, partitions and compression.
- EMR is for Spark and Hadoop with cluster level control, and can use Spot Instances for task nodes.
- Redshift is the data warehouse. Redshift Spectrum reaches into S3.
- Lake Formation adds central, fine grained permissions to a data lake.
- Managed Service for Apache Flink analyzes streams in real time.
- OpenSearch Service handles search and log analytics, and QuickSight handles dashboards.
- Land raw data in S3 first, then transform into a curated zone.
Common mistakes
Choosing EMR for a simple serverless ETL. Glue gives the same outcome with no clusters to manage.
Querying CSV files on every request. Athena scans the whole file. Convert to Parquet and partition the data.
Using Athena for a high concurrency reporting platform. A warehouse such as Redshift is built for that pattern.
Controlling data lake access only with bucket policies. For column or row level rules across services, use Lake Formation.
Athena charges for data scanned, so a query without partition filters over a large table can be expensive, even though no servers run.
Check yourself
Which service discovers a schema and stores table definitions for S3 data?
AWS Glue, with a crawler writing to the Glue Data Catalog.
How do you make Athena queries cheaper and faster?
Use a columnar format such as Parquet, partition the data, and compress it, so each query scans less data.
Which service applies column level permissions across a data lake?
AWS Lake Formation.
Topic 7 of 7 in Design High Performing Architectures
Test yourself on Design High Performing Architectures
The full AWS SAA-C03 question bank has 823 questions, with timed practice exams and an explanation for every answer.
This study guide is independent and is not affiliated with, endorsed by, or sponsored by AWS. Vendor names, certification names and exam codes are trademarks of their respective owners and are used for identification only.