Amazon DEA-C01: Data Query and Analytics — Study Guide

By Laurynas Brazys · 2 August 2026 · #amazon #dea-c01 #certification #exam-prep #study-guide

Part of the Amazon Data Engineer Associate DEA-C01 — Study Guide. Practice with verified answers in the Amazon exam hub, or take timed practice tests on ExamRoll.io.

This domain covers designing, tuning, and operating AWS services that support analytical queries and BI on large datasets. It focuses on cost-efficient, high-performance query patterns across Athena, Redshift, OpenSearch, and QuickSight, and how they interoperate with S3, Glue, and transactional stores. Mastery requires balancing storage format, partitioning, compute type, and data placement to minimize scanned bytes and network skew while delivering low-latency insights.

Amazon Athena query optimization

Athena pricing is bytes scanned, so physical data layout and metadata are the primary levers. Use columnar formats (Parquet or ORC) with compression (Snappy for Parquet, Zlib/ORC options) to reduce size and CPU. Partition data by high-cardinality, query-filtered columns (date, region) and register partitions in the Glue Data Catalog. Typical patterns:

Write data to S3 in paths like s3://bucket/events/date=2026-08-02/ and use Glue crawlers or MSCK REPAIR TABLE to populate partitions.
CREATE EXTERNAL TABLE … STORED AS PARQUET TBLPROPERTIES (‘parquet.compress’=‘SNAPPY’) to enforce columnar compression.
Use projection and partition pruning via WHERE clauses that reference partition keys to avoid scanning unneeded partitions.

When queries require joins with transactional stores, use Athena Federated Query (Lambda connectors) to perform cross-source joins with RDS, DynamoDB, or Redshift. Connectors are deployed as Lambda functions and registered as data sources in Athena; example console flow: Athena > Data sources > Connectors > New. Decision criteria:

Use Federated Query when data volumes in RDS/DynamoDB are modest or when joining a small dimension table to a large S3 dataset.
For repeated heavy joins, extract and materialize the operational data into S3 (Parquet) to shift join cost to a single ETL and use Athena for repeated reads.

Amazon Redshift query tuning and distribution

Redshift performance hinges on distribution style and sort keys to minimize data movement and enable zone mapping. Choose DIST styles using these decision points:

DISTKEY (KEY): useful when joining large tables on a high-cardinality join key; avoids redistribution if both tables share the same DISTKEY.
ALL: replicate a small dimension table to all nodes to avoid network shuffles for joins.
EVEN: default for unpredictable workloads or when no good key exists; avoids hotspots.
AUTO: let Redshift pick based on table size and workload if you lack clear guidance.

Define SORTKEYs on columns used in range filters or ORDER BY to enable zone maps and reduce disk reads. Common operational commands:

CREATE TABLE sales (…) DISTKEY(user_id) SORTKEY(order_date);
Use VACUUM and ANALYZE periodically: VACUUM; ANALYZE VERBOSE table; monitor SVV_TABLE_INFO and STL_QUERY for skew and distribution metrics. Redshift Spectrum lets you query S3 external tables through the Glue Data Catalog. Create the external schema with:
CREATE EXTERNAL SCHEMA spectrum FROM DATA CATALOG DATABASE ’ext_db’ IAM_ROLE ‘arn:aws:iam::123456789012:role/RedshiftSpectrumRole’ CREATE EXTERNAL DATABASE IF NOT EXISTS; Decision criteria for Spectrum vs native Redshift:
Use Spectrum for infrequently queried, large cold data stored in S3 or for tiered data architectures.
Keep hot, frequently-joined datasets inside Redshift for performance; when joining with Spectrum, choose DISTKEYs to colocate join keys or use redistribution to minimize network I/O.

Amazon OpenSearch Service for log analytics

OpenSearch is optimized for ingest and fast, ad-hoc log analytics; its index and cluster configuration determines throughput and cost. Index design and lifecycle:

Use index patterns like logs-YYYY.MM.DD and an index template to set index.number_of_shards (small indices: 1 shard; large: multiple shards sized ~10–50 GB) and index.number_of_replicas for availability.
Configure index lifecycle policies (ILM) to transition indices through hot, warm, cold, and UltraWarm tiers for cost control; UltraWarm reduces hot node storage costs for historical data. Sharding and replicas affect query and indexing performance:
More shards increase parallelism but add overhead; tune shards per node based on heap and CPU.
Replicas improve read throughput and fault tolerance; set replicas based on query concurrency and SLA. Operational commands and console patterns:
Use the OpenSearch Dev Tools (or curl) to PUT index templates and ILM policies, and monitor with cluster health APIs. Allocate node attributes and use shard allocation awareness to prevent hot-spotting. Decision criteria:
Choose UltraWarm when query latency requirements on historical logs can tolerate higher read latency in exchange for lower storage cost.
Keep recent indices on hot nodes to support fast aggregations and dashboards.

QuickSight for BI and visualization

QuickSight provides fast dashboards with two main ingestion modes: SPICE (in-memory) and direct query. SPICE offers sub-second performance for dashboards and is suited for repeated reads; direct SQL queries (to Athena, Redshift, RDS) are preferable for very large datasets or frequently changing data. Key configuration and best practices:

Create datasets in the console: New dataset > Choose source (Athena/Redshift/RDS/OpenSearch) > Import to SPICE or Use direct query.
Use scheduled SPICE refreshes for daily/near-real-time needs; configure incremental refresh by timestamp partitioning to limit data movement. Security and governance:
Implement row-level security via QuickSight user/group mappings and dataset rules.
For cross-account data access, deploy IAM role and resource-based permissions for QuickSight to assume. Decision criteria:
Use SPICE for dashboards with many concurrent viewers and predictable refresh windows.
Use direct query when data freshness is critical or SPICE capacity are constrained; combine with calculated fields and parameters for interactive UX.

Common Pitfalls and Decision Criteria

Athena charges per data scanned — always partition by query predicates and store in columnar formats (Parquet/ORC) with compression (Snappy/Zlib) to reduce scanned bytes.
Redshift DISTKEY on a low-cardinality column causes data skew — pick high-cardinality join keys for DISTKEY or use DISTSTYLE ALL for small dimension tables.
Redshift Spectrum external tables require the Glue Data Catalog — ensure Glue is enabled in the target region and that IAM roles permit Redshift to access the catalog.
OpenSearch shard count and sizing mistakes — avoid too many small shards; size shards to tens of GB and use ILM to move older indices to UltraWarm for cost savings.
Forgetting to RUN ANALYZE/VACUUM on Redshift after bulk loads — schedule ANALYZE and VACUUM to refresh statistics and reclaim disk space for optimal query plans.
QuickSight SPICE overflow and stale data — plan SPICE capacity, use incremental refreshes, or switch to direct query for real-time needs.

Practical Problem: Use-Case Scenario

Acme Retail needs daily BI reports combining transactional orders in Amazon RDS, clickstream events in S3, and DynamoDB user profiles, with cost limits and sub-minute dashboard refreshes for recent data.

Convert clickstream S3 data to partitioned Parquet (date-based), compress with Snappy, and register metadata in AWS Glue via a crawler.
Use Athena for ad-hoc queries on S3 and deploy Federated Query connectors for RDS and DynamoDB to perform small-dimension joins; materialize frequent join results as Parquet if queries are repeated.
Provision Redshift for heavy analytical joins: load aggregated snapshots into Redshift, set DISTKEY on a high-cardinality customer_id, and define SORTKEY on order_date; use Spectrum for cold historical S3 data.
Ingest application logs into OpenSearch with daily index patterns; apply ILM to keep recent indices on hot nodes and move older indices to UltraWarm for cost savings.
Build QuickSight dashboards: import recent aggregates into SPICE with scheduled incremental refreshes for sub-minute perceived responsiveness, and use direct queries for always-fresh metrics.

Rationale: This approach minimizes Athena scan costs via partitioning and columnar formats, reduces Redshift network shuffle with proper distribution and sort keys, uses Spectrum to avoid storing cold data in Redshift, applies OpenSearch ILM to optimize storage cost, and leverages SPICE for responsive dashboards while keeping critical freshness via direct queries.

← Data Orchestration and Workflow Management · All domains · Data Security →

Practice these questions → · Timed practice on ExamRoll.io →