Storing and processing data is only half the job. To create business value, you need to find patterns and insights hidden inside the data. The analysis step transforms raw data into answers to business questions. Visualization then presents those answers as charts and dashboards that non-experts can understand. DEA-C01 exam questions on analysis ask which service to choose in a given situation and how to optimize cost and performance.
Comparing Analysis Services
AWS offers several services for data analysis. Each has different strengths and fits different situations.
| Service | Characteristics | Best Situation | |---------|----------------|----------------| | Athena | Serverless SQL, queries S3 directly | Ad-hoc analysis, cost efficiency | | Redshift SQL | Petabyte-scale data warehouse | Complex aggregations, large structured datasets | | Athena Notebooks | SQL + PySpark interactive | Exploratory analysis, ML preprocessing | | DataBrew | No-code visual analysis | Non-developer data exploration, quality checks |
How to choose: Data is in S3 and you query it occasionally → Athena Thousands of complex queries every day at high performance → Redshift Mix SQL and Python for exploratory analysis → Athena Notebooks Explore data visually without writing SQL → DataBrew
Athena Optimization
Athena charges based on how much data is scanned per query. Scanning less means faster and cheaper queries.
Partitioning: Store data in folders organized by date, region, category, or other dimensions. When a query filters on a partition column, Athena scans only the relevant partition folders instead of all the data.
Columnar formats (Parquet, ORC): CSV stores data row by row. Even if you only need two columns, you read every column for every row. Parquet and ORC store data column by column. A query like reads only the name and age columns — all other column data is skipped entirely. This dramatically reduces the amount scanned, cutting both cost and query time.
Compression: Applying Snappy or Gzip compression reduces file sizes, which reduces scan costs. Using Parquet with Snappy compression together is a standard best practice.
Cost controls: Set maximum bytes scanned per query in workgroup settings Specify only needed columns instead of SELECT Always include partition columns in WHERE clauses Use LIMIT on exploratory queries to avoid reading the full dataset
QuickSight — Interactive Dashboards
Amazon QuickSight is AWS's business intelligence (BI) tool. Users create charts and dashboards through drag-and-drop without writing SQL.
SPICE Engine: SPICE (Super-fast, Parallel, In-memory Calculation Engine) is QuickSight's in-memory cache. Import data into SPICE once, and QuickSight serves queries from memory without hitting the source database every time. Large datasets load fast, and no matter how many users are viewing dashboards simultaneously, the source database sees no load.
Data sources: QuickSight connects to a wide range of sources: S3 (via Athena) Redshift RDS / Aurora DynamoDB SaaS services like Salesforce and SAP Direct file uploads (CSV, Excel)
Embedding: Embed QuickSight dashboards directly inside your own web application. Users view dashboards within the application without logging into the AWS console.
ML Insights: Built-in machine learning features inside QuickSight. Automatically detect anomalies in data, forecast future values, or run contribution analysis (which factors drove a change). No machine learning expertise required.
SQL Analysis Techniques
Several SQL patterns appear frequently in data analysis work and in DEA-C01 exam questions.
Aggregate functions:
Window functions: Compute aggregate values per row within a group without collapsing rows the way GROUP BY does. Use them for ranking, running totals, and moving averages while keeping individual row detail.
Pivot: Transform row values into columns. Redshift implements pivot using CASE WHEN.
CTE (Common Table Expression): Break a complex query into named, readable steps using WITH clauses.
Exam Key Points
"Serverless SQL analysis of S3 data" → Athena "High-performance processing of large complex queries" → Redshift "Reduce Athena cost with folder structure" → Partitioning "Reduce Athena cost with file format" → Parquet or ORC "Fast QuickSight dashboards" → SPICE in-memory cache "Auto-detect anomalies in QuickSight" → ML Insights "Embed QuickSight in your application" → Embedding "Compute group aggregates while keeping individual rows" → Window functions "Break a complex query into readable steps" → CTE