Data & Analytics – Athena, Redshift, OpenSearch, EMR, Glue

1. Amazon Athena

Amazon Athena is a serverless query service for analyzing data that already sits in Amazon S3. Its appeal is that you provision nothing — the data stays where it is and you query it in place.

• You query the files with standard SQL (Athena is built on Presto).
• Supported formats: CSV, JSON, ORC, Avro and Parquet.
• Pricing: $5.00 per TB of data scanned.
• It is commonly used with Amazon QuickSight for reporting and dashboards.
• Use cases: business intelligence / analytics / reporting, and analyzing or querying VPC Flow Logs, ELB Logs, CloudTrail trails and similar log data.

The typical flow is short: data is loaded into an S3 bucket, Amazon Athena queries and analyzes it, and Amazon QuickSight turns the results into reports and dashboards.

Exam tip

The deck states the trigger phrase outright: analyze data in S3 using serverless SQL means Athena. If the question adds “without loading the data anywhere” and mentions S3, Athena is almost always the answer.

Improving Athena performance

Because you are billed per TB scanned, performance tuning and cost tuning are the same exercise: scan less data.

• Use columnar formats for cost savings — less data is scanned. Apache Parquet or ORC is recommended, and the performance improvement is large. You can use Glue to convert your data to Parquet or ORC.

• Compress the data so retrievals are smaller: bzip2, gzip, lz4, snappy, zlib, zstd and similar codecs.

• Partition the datasets in S3 so queries can filter on virtual columns. The layout encodes the partition into the key:

  s3://yourBucket/pathToTable
    /<PARTITION_COLUMN_NAME>=<VALUE>
    /<PARTITION_COLUMN_NAME>=<VALUE>
    /<PARTITION_COLUMN_NAME>=<VALUE>
    /etc…

  For example: s3://athena-examples/flight/parquet/year=1991/month=1/day=1/.

• Use larger files (over 128 MB) to minimize overhead — many tiny files cost more to open than they hold.

Athena Federated Query

Federated Query lets you run SQL queries across data stored in relational, non-relational, object and custom data sources, whether they run on AWS or on-premises.

• It uses Data Source Connectors that run on AWS Lambda to execute the federated part of the query — connectors exist for CloudWatch Logs, DynamoDB, RDS and others.
• The slide’s diagram shows the same connector pattern reaching ElastiCache, DocumentDB, DynamoDB, Redshift, HBase on EMR, MySQL, Aurora, SQL Server and an on-premises database.
• The results are stored back in Amazon S3.

2. Amazon Redshift

Redshift is based on PostgreSQL, but it is not used for OLTP. It is OLAP — online analytical processing, meaning analytics and data warehousing.

• 10x better performance than other data warehouses, and it scales to petabytes of data.
• Columnar storage of data (rather than row based) combined with a parallel query engine.
• Two modes: provisioned cluster or serverless cluster.
• It exposes a SQL interface for running queries.
• It plugs into BI front ends: the deck names Amazon QuickSight and Tableau as tools that connect to it.
• Compared with Athena: Redshift gives faster queries, joins and aggregations thanks to indexes.

Exam tip

Athena and Redshift both answer “run SQL over analytics data”, so the question hinges on the rest of the sentence. Ad-hoc, infrequent queries over data already in S3 with no infrastructure means Athena; a sustained warehouse with heavy joins and aggregations means Redshift.

Redshift cluster

A provisioned Redshift cluster has two node roles:

• Leader node — handles query planning and results aggregation. Clients connect to it over JDBC/ODBC.
• Compute nodes — perform the queries and send results back to the leader.

In provisioned mode you choose the instance types in advance, and you can reserve instances for cost savings.

Snapshots and disaster recovery

• Redshift has a “Multi-AZ” mode for some clusters.
• Snapshots are point-in-time backups of a cluster, stored internally in S3, and they are incremental — only what changed is saved.
• You restore a snapshot into a new cluster (not over the existing one).
• Automated snapshots run every 8 hours, every 5 GB, or on a schedule, with a retention of 1 to 35 days.
• Manual snapshots are retained until you delete them.
• You can configure Redshift to automatically copy snapshots — automated or manual — of a cluster to another AWS Region, then restore them there into a new cluster.

Exam tip

Cross-region disaster recovery for Redshift is done by configuring automatic snapshot copy to the other region, and restoring there produces a new cluster. Nothing in the deck restores a snapshot in place.

Loading data into Redshift

The rule from the slide is blunt: large inserts are MUCH better than many small ones. The deck shows three routes in:

Route	How it works
Kinesis Data Firehose	Firehose delivers into Redshift through an S3 copy — it lands the data in S3 and then loads it
S3 with the COPY command	You point Redshift at the bucket and let it pull the files in bulk
EC2 instance with a JDBC driver	Your application writes directly, and it is better to write data in batches

The COPY command takes the S3 prefix and an IAM role:

copy customer
from 's3://mybucket/mydata'
iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole';

The deck contrasts two network paths for that copy: without Enhanced VPC Routing the traffic goes over the internet, and with Enhanced VPC Routing it is forced through your VPC.

Redshift Spectrum

Redshift Spectrum reads S3 data in place: the files stay in the bucket and never have to be loaded into the cluster first.

• A Redshift cluster has to be running before you can start the query — Spectrum is not a standalone service.
• The query is submitted from the cluster to thousands of Redshift Spectrum nodes, which read from S3 in parallel and return results through the compute and leader nodes.

3. Amazon OpenSearch Service

Amazon OpenSearch is the successor to Amazon ElasticSearch. Its value is the query model: in DynamoDB you can only query by primary key or indexes, whereas with OpenSearch you can search any field, including partial matches.

• It is common to use OpenSearch as a complement to another database, not as the system of record.
• Two modes: managed cluster or serverless cluster.
• It does not natively support SQL — SQL can be enabled via a plugin.
• Ingestion from Kinesis Data Firehose, AWS IoT and CloudWatch Logs.
• Security through Cognito and IAM, KMS encryption and TLS.
• It ships with OpenSearch Dashboards for visualization.

Exam tip

“Search any field, partial match, free text” means OpenSearch. Note the two negatives the deck stresses: no native SQL (plugin only), and OpenSearch complements a primary database rather than replacing it.

OpenSearch patterns

The deck shows three recurring ingestion patterns:

Source	Path into OpenSearch	Notes
DynamoDB	DynamoDB table with a DynamoDB Stream into a Lambda function, which writes to OpenSearch	The application does CRUD against DynamoDB and uses the API to retrieve items, while it uses the API to search items against OpenSearch
CloudWatch Logs	A subscription filter into a Lambda function managed by AWS (real time), or a subscription filter into Kinesis Data Firehose (near real time)	The Lambda route is the real-time one
Kinesis	Kinesis Data Streams into Kinesis Data Firehose with optional Lambda data transformation (near real time), or Kinesis Data Streams straight into a Lambda function (real time)	Firehose is the near-real-time path in both diagrams

4. Amazon EMR

EMR stands for “Elastic MapReduce”. Its job is to stand up Hadoop clusters for analyzing and processing very large volumes of data, and a single cluster can reach hundreds of EC2 instances.

The distribution arrives pre-assembled: Apache Spark, HBase, Presto and Flink are bundled in, and EMR handles all of the provisioning and configuration so you do not build the cluster by hand. Cost and elasticity are covered by auto-scaling together with an integration with Spot instances.

The deck’s use cases are data processing, machine learning, web indexing and big data work in general.

Node types and purchasing options

Node type	Role
Master node	Manages the cluster, coordinates work, manages health — long running
Core node	Runs tasks and stores data — long running
Task node (optional)	Just runs tasks — usually Spot

Purchasing options:

• On-demand: reliable, predictable, will not be terminated.
• Reserved (minimum 1 year): cost savings, and EMR will automatically use reserved capacity if available.
• Spot instances: cheaper, can be terminated, less reliable.

A cluster can be long-running, or transient (temporary) — spun up for a job and torn down after.

Exam tip

Because core nodes store data, losing them loses data — that is why the deck puts Spot on task nodes. An exam option that puts core or master nodes on Spot for savings is the wrong one.

5. Amazon QuickSight

Amazon QuickSight is a serverless, machine-learning-powered business intelligence service for creating interactive dashboards.

• Fast, automatically scalable, embeddable, with per-session pricing.
• Use cases: business analytics, building visualizations, performing ad-hoc analysis, and getting business insights from data.
• It is integrated with RDS, Aurora, Athena, Redshift, S3 and others.
• In-memory computation using the SPICE engine when data is imported into QuickSight.
• The Enterprise edition adds the ability to set up Column-Level Security (CLS).

QuickSight integrations

The deck groups the data sources three ways:

• AWS services: RDS, Aurora, Redshift, Athena, S3, OpenSearch, Timestream.
• SaaS sources, and on-premises databases over JDBC.
• Imports, including ELF and CLF log format files.

Dashboards and analyzes

Identities in QuickSight are its own: you define users in the standard version and groups in the enterprise version, and neither is an IAM identity — they exist inside QuickSight only.

A dashboard is what you hand to a reader. It is a read-only snapshot of an analysis and it carries the analysis’s configuration with it — the filtering, parameters, controls and sort are preserved. Either the analysis or the dashboard can be shared with those users or groups, but a dashboard has to be published before you can share it. Note what sharing implies: anyone who can see the dashboard can also see the data behind it.

Exam tip

Two facts are tested almost word for word: QuickSight users and groups are not IAM, and dashboard viewers can see the underlying data — so a dashboard is not a way to hide the source rows. Column-Level Security in the Enterprise edition is the control for that.

6. AWS Glue

AWS Glue is a managed extract, transform and load (ETL) service, useful to prepare and transform data for analytics. It is fully serverless. The canonical picture is extracting from an S3 bucket and Amazon RDS, transforming in Glue ETL, and loading into a Redshift data warehouse.

Converting data into Parquet format

A common Glue job converts CSV in an input S3 bucket into Parquet in an output S3 bucket, so that Amazon Athena can analyze it cheaply. The job can be triggered by an S3 PUT: an S3 event notification invokes a Lambda function that starts the Glue ETL job — and EventBridge works as an alternative trigger.

Glue Data Catalog

The Glue Data Catalog is a catalog of datasets. An AWS Glue Data Crawler performs data discovery across Amazon S3, Amazon RDS, Amazon DynamoDB and JDBC sources, and writes metadata — databases and tables — into the catalog. That metadata is then used by Amazon Athena, Amazon Redshift Spectrum, Amazon EMR and Glue ETL jobs.

Things to know at a high level

• Glue Job Bookmarks — prevent re-processing old data.
• Glue DataBrew — clean and normalize data using pre-built transformations.
• Glue Studio — a GUI to create, run and monitor ETL jobs in Glue.
• Glue Streaming ETL — built on Apache Spark Structured Streaming, and compatible with Kinesis Data Streams, Kafka and MSK.

7. AWS Lake Formation

A data lake is a central place holding all of your data for analytics purposes. AWS Lake Formation is a fully managed service that makes it easy to set up a data lake in days.

• Discover, cleanse, transform and ingest data into the data lake.
• It automates many complex manual steps — collecting, cleansing, moving and cataloging data — and de-duplicates using ML Transforms.
• It combines structured and unstructured data in one lake.
• Out-of-the-box source blueprints for S3, RDS, relational and NoSQL databases.
• Fine-grained access control for your applications, at row and column level.
• It is built on top of AWS Glue.

Architecturally, data sources (Amazon S3, RDS, Aurora, on-premises SQL and NoSQL databases) are ingested by Lake Formation’s source crawlers, pass through ETL and data preparation, and are registered in the Data Catalog, with security settings and access control applied centrally. The data lake itself is stored in S3, and users reach it through Athena, Redshift, EMR and QuickSight — with column-level security enforced at the Lake Formation layer rather than in each tool.

Exam tip

When a question asks for one place to define permissions across several analytics engines, especially row-level or column-level control, the answer is Lake Formation — not bucket policies, and not per-tool settings.

8. Amazon Managed Service for Apache Flink

This service was previously named Kinesis Data Analytics for Apache Flink, and both names appear in exam questions.

• Flink — written in Java, Scala or SQL — is a framework whose purpose is processing data streams.
• The service runs any Apache Flink application on a managed cluster on AWS.
• Provisioned compute resources, parallel computation, automatic scaling.
• Application backups implemented as checkpoints and snapshots.
• You can use any Apache Flink programming feature to transform data.
• Important: Flink does not read from Amazon Data Firehose. Its sources in the deck are Amazon MSK (Apache Kafka) and Kinesis Data Streams.

Exam tip

The “does not read from Firehose” line is written on the slide as an explicit warning, which usually means it appears as a distractor. The sources the deck does show are Kinesis Data Streams and Amazon MSK.

9. Amazon MSK

Amazon Managed Streaming for Apache Kafka (Amazon MSK) is an alternative to Amazon Kinesis — fully managed Apache Kafka on AWS.

• You create, update and delete clusters; MSK creates and manages the Kafka broker nodes and Zookeeper nodes for you.
• The MSK cluster is deployed in your VPC, multi-AZ (up to 3 for high availability).
• Automatic recovery from common Apache Kafka failures.
• Retention is yours to decide: the data sits on EBS volumes and stays there as long as you choose to keep it.
• MSK Serverless runs Apache Kafka on MSK without managing capacity — MSK automatically provisions resources and scales compute and storage.

Apache Kafka at a high level

Producers (your code) write to a topic on the MSK cluster, whose brokers replicate the data among themselves; consumers (your code) poll from the topic. Producers in the deck’s diagram include Kinesis, IoT and RDS; consumers include EMR, S3, SageMaker, Kinesis and RDS.

Kinesis Data Streams versus Amazon MSK

	Kinesis Data Streams	Amazon MSK
Message size	1 MB message size limit	1 MB default, configurable for higher (for example 10 MB)
Unit of parallelizm	Data Streams with Shards	Kafka topics with Partitions
Scaling	Shard splitting and merging	Can only add partitions to a topic
In-flight encryption	TLS	PLAINTEXT or TLS
At-rest encryption	KMS	KMS

Exam tip

The asymmetry is what gets tested: Kinesis can split and merge shards, while MSK partitions can only be added. And MSK is the only one of the two that can carry messages larger than 1 MB.

MSK consumers

The deck lists the supported consumers: Kinesis Data Analytics for Apache Flink, AWS Glue Streaming ETL jobs (powered by Apache Spark Streaming), Lambda, and applications running on Amazon EC2, ECS or EKS.

10. Big data ingestion pipeline

The last part of the section builds one pipeline that satisfies a list of requirements:

• The ingestion pipeline should be fully serverless.
• Collect data in real time.
• Transform the data.
• Query the transformed data using SQL.
• The reports created from the queries should be in S3.
• Load that data into a warehouse and create dashboards.

The architecture

IoT devices send data through IoT Core into Amazon Kinesis Data Streams for real-time collection. Kinesis Data Firehose reads the stream and delivers to an S3 ingestion bucket every 1 minute, calling AWS Lambda for transformation on the way. The S3 PUT triggers a notification to Amazon SQS (optional), a Lambda function pulls from the queue, and Amazon Athena queries the ingested data with SQL. Athena’s output lands in an S3 reporting bucket, which is consumed by Amazon QuickSight and Amazon Redshift Serverless.

Discussion

Each stage in that chain is picked for one property. IoT Core is the piece that harvests the readings off the devices. Kinesis takes over from there because real-time collection is what it is good at, and Firehose is what actually lands the data in S3 — in near real time, meaning a 1 minute delivery window rather than instantly. The transformation step is not Firehose’s own work: Lambda does it on Firehose’s behalf.

Once the object exists in S3, the bucket triggers a notification to SQS, and Lambda subscribes to that queue — the deck points out that S3 could equally have been wired straight to Lambda, so the queue is a buffering choice rather than a requirement. The query layer is Athena, serverless SQL whose results are written back to S3. That leaves the reporting bucket holding the analyzed output, ready for whatever reads it — QuickSight, Redshift or another reporting tool.

Real-world example

A fleet of connected meters emits readings continuously. Kinesis Data Streams absorbs the spikes, Firehose batches them into S3 once a minute while a Lambda normalizes the payloads, Athena runs the nightly SQL over the raw files, and QuickSight reads the reporting bucket. Nothing in the chain is a server you patch, and each stage can be re-run against the files already in S3 if the transformation logic changes.

Quick recap

Service	What to remember for the exam
Athena	Serverless SQL over S3, built on Presto, supports CSV/JSON/ORC/Avro/Parquet, $5.00 per TB scanned; optimize with Parquet/ORC, compression, partitioning and files over 128 MB; Federated Query uses connectors running on Lambda and stores results in S3
Redshift	Based on PostgreSQL but OLAP, 10x faster, columnar storage plus parallel query, provisioned or serverless; faster than Athena thanks to indexes
Redshift cluster	Leader node plans queries and aggregates results; compute nodes execute them
Redshift snapshots	Stored internally in S3, incremental, restored into a new cluster; automated every 8 hours / every 5 GB / on a schedule with 1–35 day retention; manual kept until deleted; copy snapshots to another region for DR
Loading Redshift	Large inserts are much better; via Firehose (through S3 copy), via COPY from S3 (Enhanced VPC Routing keeps the traffic in the VPC instead of the internet), or JDBC from EC2 in batches
Redshift Spectrum	Query data already in S3 without loading it, but a running cluster is still required
OpenSearch	Successor to ElasticSearch, search any field including partial matches, managed or serverless, no native SQL (plugin), ingest from Firehose / IoT / CloudWatch Logs, security via Cognito and IAM, ships OpenSearch Dashboards
EMR	Managed Hadoop clusters bundling Spark/HBase/Presto/Flink, hundreds of EC2 instances, auto-scaling plus Spot; master (manages) and core (tasks plus data) are long running, task nodes are usually Spot; long-running or transient clusters
QuickSight	Serverless BI, per-session pricing, SPICE in-memory engine when data is imported, CLS in Enterprise; users and groups exist only in QuickSight, not IAM; a dashboard is a read-only snapshot of an analysis, must be published before sharing, and viewers see the underlying data
Glue	Managed serverless ETL; converts CSV to Parquet for Athena; the Data Catalog is populated by the Crawler and used by Athena / Redshift Spectrum / EMR; Job Bookmarks avoid reprocessing, plus DataBrew, Glue Studio, Streaming ETL (Kinesis, Kafka, MSK)
Lake Formation	Builds a data lake (stored in S3) in days, built on top of Glue, blueprints for S3/RDS/NoSQL, ML Transforms de-duplicate, row-level and column-level access control in one place
Managed Service for Apache Flink	Formerly Kinesis Data Analytics for Apache Flink; Java/Scala/SQL on a managed cluster, automatic scaling, backups as checkpoints and snapshots; reads from MSK and Kinesis Data Streams — not from Data Firehose
Amazon MSK	Managed Kafka, an alternative to Kinesis; brokers and Zookeeper managed by MSK, deployed in your VPC, multi-AZ up to 3 AZ, data on EBS for as long as you want; MSK Serverless scales automatically
Kinesis vs MSK	Kinesis: 1 MB, shards, split and merge, TLS · MSK: 1 MB default, configurable to 10 MB, partitions, add only, PLAINTEXT or TLS; both encrypt at rest with KMS
Big data pipeline	IoT into Kinesis Data Streams (real time) into Firehose (1 minute, Lambda transform) into an S3 ingestion bucket, then SQS (optional), Lambda, Athena, an S3 reporting bucket, and finally QuickSight / Redshift Serverless