Data operations and support

Automating data processing

• Orchestrate with Step Functions (serverless, native service integrations) or MWAA (Airflow DAGs); for Glue-only flows use Glue workflows and triggers. See the ingestion notes for the comparison.
• Scripting: Glue (PySpark/Scala/Python), EMR (steps, Spark/Hive scripts, bootstrap actions), Redshift (SQL, stored procedures, scheduled queries), Athena (SQL, saved queries). Know which accept scripts and where the script lives (usually S3).
• SDK and API calls: boto3 (Python) and CLI calls start Glue jobs (StartJobRun), Athena queries (StartQueryExecution, poll GetQueryExecution), Redshift Data API (ExecuteStatement, asynchronous), EMR Serverless job runs and Step Functions executions. Use IAM roles, not keys; wait with polling or .sync integrations.
• Lambda automation: small glue steps, file validation on S3 events, calling APIs, kicking off Glue or Step Functions. Respect the 15-minute limit.
• Events and schedulers: EventBridge rules (event patterns) and Scheduler (cron or rate, time zones, flexible windows, retries, one-time schedules). S3 notifications to EventBridge give pattern-based filtering.
• Repeatable outcomes: parameterise jobs (job arguments, SSM Parameter Store), keep them idempotent, version code in Git and deploy with CI/CD and IaC.
• Consuming and maintaining data APIs: API Gateway with throttling, usage plans and caching in front of Lambda/DynamoDB/Athena; version stages; monitor 4xx/5xx and latency in CloudWatch.

Troubleshooting managed workflows

Querying with Amazon Athena

Serverless SQL (Trino/Presto engine) on S3 with the Glue Data Catalog. You pay per TB scanned (provisioned capacity reservations also exist), so cost control is about scanning less.

Reduce cost and time

1. Store data in Parquet or ORC (columnar).
2. Partition by common filter columns, and filter on them (WHERE dt = ...), or use partition projection.
3. Compress and use well-sized files (avoid thousands of small ones).
4. Select only needed columns, not SELECT *.
5. Use CTAS (CREATE TABLE AS SELECT) to convert/compact/repartition (can output Parquet, bucketed, partitioned); INSERT INTO to append.
6. Use workgroups for per-team result locations, query limits (bytes scanned per query or per workgroup), encryption settings and cost tracking by tags.

Other features

• Views (logical, stored in the catalog) hide complexity and can restrict columns.
• Federated queries use Lambda data source connectors to query RDS, DynamoDB, Redshift, OpenSearch, CloudWatch Logs, on-premises sources and more, joined with S3 data.
• Athena for Apache Spark (notebooks): interactive PySpark for exploration in the console, no cluster to manage.
• Iceberg tables: ACID, MERGE, time travel, OPTIMIZE (compaction) and VACUUM.
• Prepared statements, query result reuse and results stored in the workgroup’s S3 location.
• Common errors: HIVE_BAD_DATA or HIVE_PARTITION_SCHEMA_MISMATCH (schema/partition mismatch, fix catalog or repair), throttling (too many concurrent queries), HIVE_CURSOR_ERROR (corrupt file), slow queries from small files or no partition filter.

Provisioned vs serverless analytics

If the scenario says “occasional”, “unpredictable”, “no infrastructure to manage” choose serverless. If it says “24/7 steady heavy workload” and cost, consider provisioned with reservations.

Cleaning, preparing and visualising

AWS Glue DataBrew

Visual, no-code data preparation with 250+ built-in transformations (recipes). Concepts: dataset, project (interactive session), recipe (versioned steps), job (runs a recipe on the full dataset), data profile job (statistics, duplicates, missing values, correlations, outliers).

• Good for: analysts cleaning data, standardising formats, handling nulls, masking and encrypting PII columns (hashing, substitution, shuffling), deduplication, pivoting.
• Data quality rules (rulesets) check completeness and validity per dataset, and can be run with profile jobs.
• Output to S3 (CSV, Parquet and others) or databases, and it can be scheduled or run from Step Functions.
• Choose DataBrew for no-code and quick profiling; choose Glue ETL for code at scale; SageMaker Data Wrangler / Canvas for ML-oriented prep in the SageMaker UI.

Amazon QuickSight

BI service for dashboards.

• Sources: Athena, Redshift, RDS, S3, OpenSearch, SaaS and files.
• SPICE is the in-memory engine: imported data is fast and doesn’t hit the source each time (refresh on a schedule). Direct query reads live data.
• Features: Q (natural language questions), embedded analytics, row-level and column-level security, ML insights/anomaly detection, paginated reports, sharing to users and groups, and VPC connections for private data.
• Standard vs Enterprise editions: Enterprise adds row-level security, encryption at rest, Active Directory integration and VPC connections.
• If the question says “visualise” and “business users”, think QuickSight; for profile-style charts of data quality, DataBrew.
• Amazon Managed Grafana is for operational/time-series metrics dashboards (CloudWatch, Prometheus, OpenSearch), not business BI.

Analysis techniques and SQL

Know the language of aggregate and window operations:

• GROUP BY, HAVING, ROLLUP/CUBE; rolling averages via AVG(x) OVER (ORDER BY d ROWS BETWEEN 6 PRECEDING AND CURRENT ROW); ranking with ROW_NUMBER, RANK; LAG/LEAD for period over period.
• Pivoting: turning rows into columns (PIVOT in Redshift, conditional aggregation with CASE in Athena); unpivot the reverse.
• Joins: inner, left, full, cross; join on matching types and put the smaller table where the engine expects it; avoid exploding joins on non-unique keys.
• Cleansing: remove duplicates (ROW_NUMBER() ... = 1), fill or drop nulls, standardise case, trim, cast types, handle outliers, parse dates.
• Notebooks (Jupyter on SageMaker, EMR Studio, Athena Spark) help for exploration; Lambda can do small row-level cleaning.

Monitoring and maintaining pipelines

Logging and metrics

• CloudWatch Logs: log groups and streams for Lambda, Glue (/aws-glue/jobs/output, /error, continuous logging), EMR (via the CloudWatch agent or S3), MSK, Step Functions (execution logs), API Gateway. Set retention (default never expires, a cost trap) and encryption with KMS.
• CloudWatch Logs Insights: query language for ad hoc log analysis (fields, filter, stats, parse).
• Metric filters turn log patterns into metrics, and subscription filters stream logs to Lambda, Kinesis, Firehose or OpenSearch. Export to S3 for long-term, cheaper storage and Athena queries.
• CloudWatch metrics and alarms: Glue (job failures, DPU use), Kinesis (GetRecords.IteratorAgeMilliseconds shows consumer lag; WriteProvisionedThroughputExceeded), Lambda (errors, throttles, duration, iterator age), Redshift (CPU, disk space, query duration, WLM queue), EMR (YARN memory, HDFS use), DynamoDB (throttled requests, consumed capacity), MSK (consumer lag, disk). Alarm actions notify SNS or call Lambda/Systems Manager.
• EventBridge events for state changes (Glue job state change, crawler status, Step Functions status, EMR step failures) trigger notifications and automated retries.
• CloudTrail: records API calls (who did what, when, from where). Management events are on by default for 90 days of event history; create a trail to S3 for long-term, and enable data events (S3 object-level, Lambda invoke, DynamoDB item-level) which are off by default.
• Macie monitors S3 for sensitive data and bucket exposure.
• Notifications: SNS topics (email, SMS, Lambda, HTTP, SQS fan-out) for alerts; SQS to queue failed work.
• Cost: AWS Budgets (alerts when spend crosses a threshold) and Cost Explorer (analyse spend, tags); use cost-allocation tags per pipeline.

Analysing logs at scale

Troubleshooting performance

• Glue: look at the Spark UI and job metrics. Many small files (group files), skew (one task far longer: repartition, salt keys), too few workers (enable auto scaling), JDBC reads single-threaded, shuffle spill (larger worker types).
• EMR: executors out of memory (tune spark.executor.memory, partitions), underused cluster (managed scaling), slow S3 listing (fewer files), Spot reclamation (use On-Demand for core).
• Redshift: check SVL_QUERY_REPORT, STL_ALERT_EVENT_LOG, Query monitoring and the Advisor; fixes are better distribution/sort keys, ANALYZE, fewer wide SELECT *, WLM tuning, concurrency scaling, materialized views, resizing; high disk use means unvacuumed or skewed data; disk-based (spilled) queries need more memory.
• Athena: partition and format fixes above.
• Kinesis: rising iterator age means consumers are behind: add consumer capacity (Lambda parallelization, more KCL workers), shards, enhanced fan-out.
• DynamoDB: throttles and hot keys; on-demand or better keys; use Contributor Insights to find hot keys.
• Lambda: out of memory/timeout; add memory, batch size, concurrency; RDS connection exhaustion fixed with RDS Proxy.

Ensuring data quality

Dimensions and techniques

• Profiling looks at the data first (null %, distinct counts, distributions, patterns, min/max) so you can write rules. Sampling (random or stratified) lets you inspect big datasets cheaply, but small samples can miss rare errors.
• Skew (uneven value distribution) is detected by profiling and mitigated with salting, better keys, repartitioning, and broadcast joins. Don’t confuse with data quality, but profiling helps find it.
• Validation in-flight: check during processing so bad records are routed away (a dead-letter/quarantine S3 prefix) and good data continues.

AWS Glue Data Quality

Built on the open-source Deequ. Rules are written in DQDL (Data Quality Definition Language): IsComplete "id", Uniqueness "id" > 0.99, ColumnValues "age" between 0 and 120, RowCount > 0, ReferentialIntegrity, DataFreshness.

• Evaluate on catalog tables (a ruleset attached to a table) or inside Glue ETL jobs with the Evaluate Data Quality transform, which can fail the job or route bad rows.
• Recommendation feature generates rules from the data.
• Results go to the console, CloudWatch metrics, and EventBridge events, so you can alarm or stop a pipeline; anomaly detection learns normal behaviour.
• DataBrew rulesets do similar checks for no-code users. Empty fields: IsComplete or DataBrew Missing values rules.
• Gate a pipeline: a Step Functions choice state looks at the quality result before publishing.
• Investigating consistency: compare row counts and checksums between source and target, run DataBrew profile jobs, check for duplicates, and look at lineage.