1. Plain-English Mental Model
Athena, Redshift, and OpenSearch can all produce analytical answers, but they prepare data differently and are optimized for different questions.
Athena = query files in S3 with serverless SQL
Redshift = warehouse structured data for repeated BI analytics
OpenSearch = index documents and logs for search and exploration
If an incident responder wants to run occasional SQL over CloudTrail logs already stored in S3, Athena can read those objects in place without a customer-managed query cluster. “In place” means the source rows remain in S3; Athena still needs table metadata that describes their format and location.
If finance repeatedly joins years of orders, customers, and calendar data for dashboards, a modeled warehouse can justify the preparation and capacity it requires. Redshift stores or accesses analytical tables through a warehouse execution model designed for those repeated joins and aggregations.
If an operator needs to find error messages containing a phrase and filter them by service within recently ingested logs, OpenSearch fits the indexed-search shape. The data must first be transformed into indexed documents; OpenSearch does not discover arbitrary S3 files at query time.
2. Why This Service Exists
Analytics workloads are not all the same.
Some data sits in S3 and only needs occasional SQL queries. Loading every log into a warehouse before the first question adds pipeline and operating work that the use case may not justify. Athena exists so SQL can query supported data sources such as S3 while AWS supplies the query execution capacity.
Some organizations need repeated business-intelligence (BI) queries over structured history: the same revenue, cohort, or inventory model serves many reports and concurrent users. Redshift exists as a managed data warehouse so teams can pay the preparation cost once and reuse a model tuned for analytical joins and aggregations.
Some users need to search documents, logs, and events by text and fields soon after ingestion. OpenSearch exists for indexed search and operational analytics: ingestion maps fields and builds an index so later searches avoid scanning every source document. Faster search therefore depends on earlier preparation and on the index being fresh.
For SAA-C03, the wording usually reveals the service: serverless SQL over S3, data warehouse, or full-text search/log analytics.
3. The Naive Approach And Where It Breaks
The naive approach is to call every analytics store a database.
That leads to wrong fits.
Redshift can query and analyze data well, but loading and modeling a warehouse is usually unnecessary for one occasional CloudTrail investigation over files already in S3. Athena can query that lake data directly, and bytes scanned remain the main cost and performance pressure.
Athena can query S3 with SQL, but it is not a low-latency product-search index or a transactional application database. A search box that must find partial text on every user request needs prepared search structures and predictable serving behavior, not a fresh object scan.
OpenSearch can aggregate and visualize logs, but it is not a traditional relational warehouse for complex SQL modeling and BI semantics. Index mappings describe searchable document fields; they do not provide the same relational constraints and join model as warehouse tables.
The architecture should match query shape, latency expectation, data layout, and operational model.
4. Core Primitives
An Athena query needs a catalog and a data source. AWS Glue Data Catalog tables store metadata such as columns, formats, partitions, and S3 locations—not the data rows themselves. A partition narrows data by a value such as date, and columnar formats such as Parquet let a query read relevant columns instead of every byte. Workgroups govern query settings and results are written to a configured location.
Redshift provides provisioned clusters and Serverless workgroups as compute models around warehouse data. Schemas and tables define the analytical model; columnar storage, distribution, sort choices, compression, and workload management influence how much data moves and how concurrent queries share capacity. Snapshots support recovery. Redshift Spectrum can query external S3 tables, but that option does not erase the broader warehouse operating model.
OpenSearch stores documents in indexes. A mapping defines how fields are interpreted; shards divide an index for storage and work, and replicas provide additional copies that can support availability and reads. Domains or Serverless collections provide the service boundary, while ingestion pipelines, refresh behavior, snapshots, and retention controls determine whether the searchable view is current and recoverable.
Athena reads files. Redshift warehouses modeled data. OpenSearch indexes documents.
5. Architecture Use Cases
Use Athena for ad hoc or occasional SQL over data lakes, logs, exports, and supported formats such as CSV, JSON, Parquet, or ORC. The smallest useful flow is:
S3 logs -> Glue Data Catalog -> Athena SQL
Use Redshift when repeated BI questions justify loading, modeling, and operating a warehouse. Extract-transform-load (ETL) and extract-load-transform (ELT) are two common ways to prepare source data for that warehouse:
operational systems -> ETL/ELT -> Redshift -> dashboards
Use OpenSearch when data can be ingested into a search-oriented document model before users ask for full-text matches, filters, or aggregations:
logs or documents -> indexing pipeline -> OpenSearch -> search and dashboards
Use Glue to discover schemas, catalog locations, or transform data for an analytical path. Use QuickSight or another presentation layer to build dashboards over an appropriate engine. A dashboard does not decide whether the underlying data should remain lake objects, become warehouse tables, or become indexed documents.
7. Security Model
Athena security includes IAM, S3 data permissions, Glue Data Catalog permissions, workgroups, query-result bucket controls, Lake Formation where used, and KMS keys.
Redshift security includes IAM, database users and roles, network placement, security groups, encryption, S3 access roles, and audit logs.
OpenSearch security includes domain or collection policies, network placement, IAM, fine-grained access control, encryption, dashboard access, and index-level permissions.
All three can expose sensitive analytical data. Protect query results, logs, indexed documents, and warehouse tables with least privilege.
Do not index or warehouse secrets casually.
8. Reliability And Resilience
Athena reliability depends on S3 data availability, catalog correctness, permissions, and query result configuration. There is no query cluster to patch.
Redshift reliability depends on warehouse mode, snapshots, data pipelines, workload management, and recovery objectives.
OpenSearch reliability depends on shard design, replicas, snapshots, ingestion buffering, and domain or collection configuration.
For all three, copied or transformed data pipelines should be replayable: after a failed batch or bad mapping, the team needs a known source from which it can rebuild the analytical view. Analytics systems often fail through stale data, schema drift, or partial ingestion rather than a total service outage.
Monitor freshness, failed jobs, query failures, and storage growth.
9. Performance And Scaling
Athena performance depends heavily on partitioning, file size, compression, and columnar formats such as Parquet or ORC. A date predicate reduces scanning only when the layout and table metadata let Athena prune unrelated partitions; writing WHERE date = ... does not by itself reorganize the objects.
Redshift performance depends on warehouse capacity, data modeling, distribution, sort keys, compression, workload management, and query design.
OpenSearch performance depends on index mapping, shard count, replica count, ingestion rate, query shape, refresh behavior, and storage tier.
Use the performance model that matches the query. Do not expect Athena to serve product search or OpenSearch to replace a warehouse join model.
10. Cost Model
Athena cost is driven largely by data scanned, plus S3, catalog, and result storage. Partition pruning, compression, and columnar formats can reduce scanned bytes because the engine opens less irrelevant data.
Redshift cost is driven by provisioned or serverless compute, storage, snapshots, data transfer, and workload usage.
OpenSearch cost is driven by domain or serverless capacity, storage, replicas, snapshots, and ingestion pipelines.
Athena can be cheaper for occasional queries. Redshift can be more efficient for repeated warehouse workloads. OpenSearch is worth it when indexed search and log exploration justify the cost.
12. SAA-C03 Exam Signals
"Serverless SQL over S3" points to Athena.
"Query CloudTrail, ALB, or VPC Flow Logs in S3" points to Athena.
"Data warehouse" points to Redshift.
"Business intelligence over structured historical data" points to Redshift.
"Full-text search" points to OpenSearch.
"Log analytics with indexed search and dashboards" points to OpenSearch.
"Crawler or ETL job" points to Glue, not Athena, Redshift, or OpenSearch alone.
13. Common Exam Traps
Do not choose Redshift for a small occasional S3 query when Athena fits.
Do not choose Athena for low-latency full-text search.
Do not choose OpenSearch as the warehouse for complex SQL BI.
Do not forget Glue Data Catalog permissions for Athena.
Do not ignore data scanned cost in Athena.
Do not index all logs forever in OpenSearch without retention planning.
15. Related Topics
Review Amazon Athena, Amazon Redshift, Amazon OpenSearch Service, AWS Glue, and Analytics Data Lake On S3.
Official AWS references: