Ingesting Into Data Lake

Performance and Troubleshooting Guide

Guide for diagnosing and resolving performance issues, incremental loading problems, IAM/permissions errors, and monitoring for external data import pipelines.

Table of Contents

• Performance Issues — Slow queries, job timeouts

• Incremental Loading Issues — Watermark not advancing, duplicates

• IAM and Permissions Errors — S3 access denied, Glue catalog access

• Monitoring and Alerting — CloudWatch alarms, key metrics

• Troubleshooting Checklist — Systematic diagnosis steps

Performance Issues

Slow Query Execution

Symptom:

• Job runs for hours

• CloudWatch logs show: "Executing query..." but no progress

Root causes:

1. Missing indexes - Source query does full table scan

2. Too much data - Loading entire table instead of incremental

3. Network bandwidth - Limited throughput between database and Glue

4. Source database load - Database under heavy load

Troubleshooting:

1. Check query execution plan in source database:

   Copy

   -- Oracle
   EXPLAIN PLAN FOR
   SELECT * FROM large_table WHERE updated_at > '2024-01-01';
   SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
   
   -- SQL Server
   SET SHOWPLAN_TEXT ON;
   SELECT * FROM large_table WHERE updated_at > '2024-01-01';
   
   -- PostgreSQL
   EXPLAIN ANALYZE
   SELECT * FROM large_table WHERE updated_at > '2024-01-01';
   
   -- MySQL
   EXPLAIN
   SELECT * FROM large_table WHERE updated_at > '2024-01-01';

2. Monitor source database load:

   • Check CPU, memory, I/O utilization

   • Review slow query logs

   • Identify concurrent queries

3. Measure network throughput:

   • Check data transfer rate in CloudWatch metrics

   • Look for bandwidth bottlenecks

Solutions:

1. Add index on watermark column:

   Copy

   -- Oracle
   CREATE INDEX idx_updated_at ON large_table(updated_at);
   
   -- SQL Server
   CREATE INDEX idx_updated_at ON large_table(updated_at);
   
   -- PostgreSQL
   CREATE INDEX idx_updated_at ON large_table(updated_at);
   
   -- MySQL
   CREATE INDEX idx_updated_at ON large_table(updated_at);

2. Use parallel reads:

   Copy

   source_df = spark.read.format("jdbc").options(
       url=jdbc_url,
       dbtable="large_table",
       numPartitions=10,  # Read in parallel
       partitionColumn="id",
       lowerBound=1,
       upperBound=10000000
   ).load()

3. Reduce batch size:

   Copy

   # Load 1 day at a time instead of full month
   WHERE updated_at >= '2024-01-01' AND updated_at < '2024-01-02'

4. Increase Glue workers:

   Copy

   'NumberOfWorkers': 20,  # Up from 5
   'WorkerType': 'G.2X'    # Larger workers

Job Timeout

Symptom:

ERROR: Job exceeded timeout of 60 minutes
JobRunState: TIMEOUT

Root causes:

1. Timeout too short - Data volume requires more time

2. Performance issues - See "Slow Query Execution" above

Solution:

Increase job timeout:

aws glue update-job \
  --job-name external-import-customers \
  --job-update Timeout=180

Incremental Loading Issues

Watermark Not Advancing

Symptom:

• Job runs successfully but loads 0 records every time

• Watermark file contains same value after each run

Root causes:

1. No new data in source - Actually no changes

2. Timezone mismatch - Source uses local time, watermark uses UTC

3. Watermark filter logic incorrect - Using >= instead of >

Troubleshooting:

1. Check source for new data:

   Copy

   SELECT COUNT(*) FROM table WHERE updated_at > '<last-watermark>';

2. Check timezone:

   Copy

   print(f"Last watermark: {last_watermark}")
   print(f"Last watermark timezone: {last_watermark_tz}")
   
   # Convert to UTC
   from datetime import datetime
   import pytz
   
   utc_watermark = pytz.timezone('America/New_York').localize(
       datetime.strptime(last_watermark, '%Y-%m-%d %H:%M:%S')
   ).astimezone(pytz.utc)

3. Check filter logic:

   Copy

   # Correct: > (strictly greater than)
   filtered_df = source_df.filter(f"{watermark_column} > '{last_watermark}'")
   
   # Incorrect: >= (will reload last batch every time)
   # filtered_df = source_df.filter(f"{watermark_column} >= '{last_watermark}'")

Solution:

Normalize all timestamps to UTC:

from pyspark.sql.functions import to_utc_timestamp

# Convert source timestamp to UTC
df_utc = source_df.withColumn(
    "updated_at_utc",
    to_utc_timestamp(col("updated_at"), "America/New_York")
)

# Filter using UTC timestamps
filtered_df = df_utc.filter(f"updated_at_utc > '{last_watermark_utc}'")

Duplicate Records

Symptom:

• Target table contains duplicate records (same primary key multiple times)

Root causes:

1. Using append instead of upsert - For mutable data

2. Job retry - Job failed mid-run, reran from same watermark

3. Late-arriving data - Records arrive after their event timestamp

Solution:

1. Use upsert for mutable data:

   Copy

   # MERGE INTO instead of append
   spark.sql(f"""
   MERGE INTO {target_table} AS target
   USING source_view AS source
   ON target.customer_id = source.customer_id
   WHEN MATCHED THEN UPDATE SET *
   WHEN NOT MATCHED THEN INSERT *
   """)

2. Add deduplication logic:

   Copy

   from pyspark.sql.window import Window
   from pyspark.sql.functions import row_number
   
   window = Window.partitionBy("customer_id").orderBy(col("updated_at").desc())
   deduplicated_df = source_df.withColumn("row_num", row_number().over(window)) \
       .filter(col("row_num") == 1) \
       .drop("row_num")

3. Handle late arrivals with buffer:

   Copy

   # Load from 1 day before watermark
   buffer_watermark = last_watermark - timedelta(days=1)
   filtered_df = source_df.filter(f"{watermark_column} > '{buffer_watermark}'")
   
   # Then upsert to avoid duplicates

IAM and Permissions Errors

S3 Access Denied

Symptom:

ERROR: Access Denied (Service: Amazon S3; Status Code: 403)

Root cause: Glue job IAM role lacks S3 permissions

Solution:

Add S3 permissions to Glue role:

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Action": [
        "s3:GetObject",
        "s3:PutObject",
        "s3:DeleteObject"
      ],
      "Resource": [
        "arn:aws:s3:::<scripts-bucket>/*",
        "arn:aws:s3:::<watermark-bucket>/*",
        "arn:aws:s3:::<data-bucket>/*"
      ]
    },
    {
      "Effect": "Allow",
      "Action": "s3:ListBucket",
      "Resource": [
        "arn:aws:s3:::<scripts-bucket>",
        "arn:aws:s3:::<watermark-bucket>",
        "arn:aws:s3:::<data-bucket>"
      ]
    }
  ]
}

Glue Data Catalog Access Denied

Symptom:

ERROR: User is not authorized to perform glue:GetTable

Root cause: Glue job role lacks Glue Data Catalog permissions

Solution:

Add Glue permissions:

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Action": [
        "glue:GetDatabase",
        "glue:GetTable",
        "glue:GetPartitions",
        "glue:CreateTable",
        "glue:UpdateTable",
        "glue:DeleteTable"
      ],
      "Resource": [
        "arn:aws:glue:region:account:catalog",
        "arn:aws:glue:region:account:database/*",
        "arn:aws:glue:region:account:table/*/*"
      ]
    }
  ]
}

Monitoring and Alerting

Set Up CloudWatch Alarms

Job failure alarm:

aws cloudwatch put-metric-alarm \
  --alarm-name "glue-job-failure-customers" \
  --metric-name JobFailure \
  --namespace AWS/Glue \
  --statistic Sum \
  --period 300 \
  --threshold 1 \
  --comparison-operator GreaterThanOrEqualToThreshold \
  --dimensions Name=JobName,Value="external-import-customers" \
  --evaluation-periods 1 \
  --alarm-actions <sns-topic-arn>

Long-running job alarm:

aws cloudwatch put-metric-alarm \
  --alarm-name "glue-job-long-running-customers" \
  --metric-name glue.driver.aggregate.elapsedTime \
  --namespace Glue \
  --statistic Maximum \
  --period 300 \
  --threshold 3600000 \
  --comparison-operator GreaterThanThreshold \
  --dimensions Name=JobName,Value="external-import-customers" \
  --evaluation-periods 1 \
  --alarm-actions <sns-topic-arn>

Key Metrics to Track

• glue.driver.aggregate.recordsRead - Records read from source

• glue.driver.aggregate.bytesRead - Bytes read from source

• glue.driver.aggregate.elapsedTime - Job duration

• glue.driver.aggregate.numCompletedTasks - Tasks completed

• Job run state (SUCCEEDED, FAILED, TIMEOUT)

Troubleshooting Checklist

When a job fails, follow this systematic approach:

1. Check Job Run Status

aws glue get-job-run \
  --job-name <job-name> \
  --run-id <run-id> \
  --query 'JobRun.[JobRunState,ErrorMessage]'

2. Review CloudWatch Logs

aws logs tail /aws-glue/jobs/output --follow \
  --log-stream-names "<job-name>-<run-id>"

Look for:

• ERROR messages

• Exception stack traces

• Last successful log message before failure

3. Test Connection

# Test Glue connection
aws glue get-connection --name <connection-name>

# Test from EC2 in same subnet
telnet <db-host> <db-port>

4. Verify Permissions

# Check IAM role policies
aws iam get-role --role-name <glue-role-name>
aws iam list-attached-role-policies --role-name <glue-role-name>

5. Validate Source Data

-- Run query in source database
SELECT COUNT(*) FROM table WHERE updated_at > '<watermark>';

6. Check Watermark

# Read watermark file
aws s3 cp s3://<bucket>/watermarks/<table>.txt -

Summary

Error resolution workflow:

1. Identify error category - Connection, schema, performance, incremental, or permissions

2. Check CloudWatch logs - Read error messages and stack traces

3. Test connectivity - Verify network, security groups, credentials

4. Validate source data - Query source database directly

5. Review job configuration - Check worker count, timeout, arguments

6. Monitor metrics - Set up CloudWatch alarms for proactive detection

7. Document resolution - Keep runbook of common issues and fixes

With systematic troubleshooting and proper monitoring, external data import pipelines run reliably with minimal intervention.