Troubleshooting ETL with CloudWatch Logs Insights

1. From Log Browsing to Log Analytics

You know that services like AWS Lambda or AWS Glue can generate numerous log streams—one for each concurrent Lambda instance or Glue worker. When troubleshooting, sifting through these streams one by one is inefficient and often impossible under time pressure.

This is the problem CloudWatch Logs Insights solves. It provides a purpose-built query language to analyze entire log groups at once.

To get a feel for the service and its core capabilities, let's start with a foundational video from AWS.

The query language is the heart of Logs Insights. Its syntax is straightforward and powerful, typically following this pattern:

source log events | command1 | command2 | ...

Let's break down the most common commands you'll use:

• fields: Specifies which fields to display in the query results. By default, @timestamp and @message are shown.
• filter: Filters results based on one or more conditions. This is your primary tool for narrowing down the search space (e.g., filter @message like /ERROR/).
• stats: Calculates aggregate statistics, like count(), avg(), sum(), min(), and max(). This is incredibly useful for summarizing data.
• sort: Sorts the results in ascending (asc) or descending (desc) order.
• limit: Restricts the number of log events returned.
• parse: Extracts data from a log field, creating one or more ephemeral fields that you can use in your query. This is the key to turning unstructured log text into structured, queryable data.

2. Practical Troubleshooting: Investigating a Failing Glue Job

Now, let's apply these concepts to a scenario you're likely to face on the job and in the exam: a slow or failing AWS Glue ETL job. When a Glue job runs, its logs (from the driver and executors) are sent to a CloudWatch log group, typically /aws-glue/jobs/output and /aws-glue/jobs/error.

The following AWS blog post provides a fantastic walkthrough of using Logs Insights to diagnose common Glue job issues.

Let's deconstruct one of the queries from that article to solidify the concepts.

Goal: Find the average and maximum execution time for each Glue job run to identify jobs that are inconsistent or have outlier long runs.

fields @timestamp, @message, @logStream as JobRun
| filter @message like /committed/
| parse @message /Job (?<jobNumber>\d+) finished:.*?, took (?<executionTime_inSeconds>\S+) s/
| parse @message /Write Job (?<jobId>[a-f0-9\-]+) committed\./
| stats avg(executionTime_inSeconds) as AvgDuration, max(executionTime_inSeconds) as MaxDuration by JobRun
| fields MaxDuration - AvgDuration as difference
| sort difference desc

How it works:

1. filter @message like /committed/: Narrows the search to only the log lines that indicate a job has finished and committed its output.
2. parse @message ...: This is the magic. It uses regular expressions to find specific patterns in the completion message and extract the executionTime_inSeconds into a new, temporary field.
3. stats avg(...) as AvgDuration, max(...) as MaxDuration by JobRun: For each unique JobRun (log stream), it calculates the average and maximum of the executionTime_inSeconds field we just extracted.
4. fields MaxDuration - AvgDuration as difference: It then calculates the difference between the max and average duration, which is a great indicator of performance variability.
5. sort difference desc: Finally, it sorts the results to bring the job runs with the highest variability to the top.

This single query transforms thousands of raw log lines into a concise, actionable report on job performance. This is the power of Logs Insights.

3. Expanding Your Troubleshooting Toolkit

The techniques for querying Glue logs are broadly applicable. You can use the same filter | parse | stats pattern to troubleshoot any component in your data architecture.

The image below shows a query that filters for Lambda function errors or timeouts, a common task when troubleshooting a serverless processing stage.

To become proficient, it's useful to have a mental library of query patterns for different services.

Here are two key examples adapted from that documentation that are highly relevant for data engineering:

Example 1: Analyzing Lambda Performance

If your data pipeline uses Lambda functions, you need to monitor their performance. The REPORT line logged at the end of every Lambda invocation contains valuable metrics.

Goal: Get a latency report for a Lambda function, showing average, max, and min duration every 5 minutes.

filter @type = "REPORT"
| stats avg(@duration), max(@duration), min(@duration) by bin(5m)

This query isolates the REPORT logs and uses stats to aggregate the built-in @duration field, giving you an instant performance overview.

Example 2: Troubleshooting Permission Errors

Data pipelines often fail due to IAM permission issues (e.g., a Glue job can't read from an S3 bucket). CloudTrail logs all API calls, and querying these logs is the definitive way to diagnose such problems.

Goal: Find failed API calls for a specific service and group them by error code.

# Assuming your CloudTrail log group is selected
filter eventSource = "s3.amazonaws.com" and ispresent(errorCode)
| stats count(*) by eventName, errorCode, errorMessage

This query filters for API calls to S3 that resulted in an error, then counts them by the specific API call (eventName) and the errorCode (e.g., AccessDenied). This will tell you exactly what action failed and why.

fields @timestamp, @message
| filter @message like /Invalid data format/
| stats count(*) as errorCount

Conclusion

In this lesson, we moved from the what of monitoring (alarms) to the why (troubleshooting). You've seen how CloudWatch Logs Insights transforms log files from a passive record into an active, queryable database for diagnostics.

Key Takeaways:

• Logs Insights is for scale: It's designed to query across many log streams within a log group, making it ideal for distributed services like Glue and Lambda.
• The core pattern is filter | parse | stats: Master this sequence to narrow down events, extract structured data, and aggregate the results into meaningful insights.
• parse is your most powerful tool: It allows you to impose a schema on-the-fly on unstructured or semi-structured log data, making it queryable.
• Apply it across services: The same query principles can be used to troubleshoot performance in Glue, errors in Lambda, and permission issues via CloudTrail.

Next Up:

We have now covered how to monitor pipeline health and troubleshoot failures. The next logical step is to look at another critical aspect of operations: cost. In our next lesson, we will "Design S3 cost optimization strategies using storage classes, lifecycle policies, and S3 Intelligent-Tiering." You'll learn how to manage your data lake storage to be as cost-effective as possible without sacrificing performance.