Optimizing AWS Glue ETL Workflows for Efficient Data Transfer from RDS to S3

Most tutorials treat AWS Glue as a black-box ETL tool—here's how understanding Glue's architecture leads to tailored pipelines that outperform generic setups in scalability and cost-effectiveness.

If you're working with AWS and need to migrate data from an RDS instance to S3 efficiently, AWS Glue is often the go-to service. However, many users simply spin up a Glue job without fully leveraging its capabilities or understanding underlying mechanics. This results in suboptimal performance, higher costs, and sluggish workflows—especially when scaling.

In this post, I’ll walk you through optimizing an AWS Glue ETL workflow specifically for transferring data from Amazon RDS (say MySQL or PostgreSQL) to S3. We’ll look beyond the basics, dive into best practices around JDBC connections, data partitioning strategies, job tuning, and cost management.

Why Optimize AWS Glue for RDS to S3?

Efficient data transfer from RDS to S3 enables quicker analytics and ML workflows by making your data lake fresh and readily available. Without optimization:

Glue jobs may run longer than necessary.
You’ll incur higher compute costs.
You might hit JDBC read bottlenecks.
Data output could be skewed or unbalanced in your S3 bucket, causing downstream inefficiency.

By tailoring Glue workflows around your RDS schema, workload patterns, and data pipeline objectives, you get faster jobs that cost less—and pave the way for smooth analytics downstream.

Step 1: Set Up Your Environment (RDS & IAM Roles)

Before fine-tuning anything in Glue:

Verify your RDS instance has network connectivity with your Glue environment:
- Ensure Glue is set up inside the same VPC or has appropriate VPC endpoints.
- The security group attached to your RDS must allow inbound connections on its port (default 3306 for MySQL) from the subnet where Glue jobs run.
Create an IAM role with permissions:
- Access to read/write S3 buckets.
- Access to manage Glue jobs.
- Permissions for Secrets Manager if you plan on storing your DB credentials there (strongly recommended).

Step 2: Create a Connection to Your RDS DB in AWS Glue

AWS Glue lets you define a JDBC connection that it can reuse across jobs.

In the AWS Console → AWS Glue → Connections → Add connection
Choose JDBC connection type.
Provide hostname (endpoint of RDS), port number, database name.
Provide credentials via:
- Direct input (not recommended for security).
- Or store credentials in Secrets Manager and reference them securely.

Make sure this connection is associated with the correct VPC/subnet/security groups so that Glue workers can connect to RDS.

Step 3: Write the ETL Script with Partition-Aware Reads

By default, if you load large tables straight from RDS without partitioning reads, you'll get single-threaded performance — jobs will be slow and costly.

Leverage pushdown predicates and parallel reads:

import sys
from awsglue.transforms import *
from awsglue.utils import getResolvedOptions
from pyspark.context import SparkContext
from awsglue.context import GlueContext
from awsglue.job import Job

args = getResolvedOptions(sys.argv, ['JOB_NAME'])
sc = SparkContext()
glueContext = GlueContext(sc)
spark = glueContext.spark_session
job = Job(glueContext)
job.init(args['JOB_NAME'], args)

# Reading from JDBC using pushdown predicates and partition column for parallelism
jdbc_url = "jdbc:mysql://my-rds-endpoint:3306/mydatabase"

connection_options = {
    "url": jdbc_url,
    "dbtable": "my_table",
    "user": "myuser",
    "password": "mypassword",  # ideally use Secrets Manager
    # Partition options – change accordingly!
    "partitionColumn": "id",
    "lowerBound": "1",
    "upperBound": "1000000",
    "numPartitions": "10"
}

dynamic_frame = glueContext.create_dynamic_frame.from_options(
    connection_type="mysql",
    connection_options=connection_options,
)

# Write out as Parquet files into S3 with partitioning by date column if available
sink = glueContext.getSink(
    path="s3://my-bucket/landing/my_table/",
    connection_type="s3",
    updateBehavior="UPDATE_IN_DATABASE",
    partitionKeys=["event_date"],  # optional partition key in target dataset
    enableUpdateCatalog=True,
)
sink.setFormat("glueparquet")
sink.writeFrame(dynamic_frame)

job.commit()

Explanation:

partitionColumn: Picking a numeric or date column with uniform distribution lets you split the read into multiple parallel JDBC queries (numPartitions).
lowerBound / upperBound: define min and max values of the partition column.

This approach significantly speeds up extraction compared to single-thread fetches.

Step 4: Use Pushdown Predicates and Incremental Loads

For very large tables or production workflows where full syncs are expensive:

Track changes via:
- A timestamp column (last_updated).
- Or an auto-incrementing ID.
Use these columns with pushdown predicates inside dbtable query like:

"dbtable": "(select * from my_table where last_updated > '2024-06-01') as filtered_table"

Only transferring incremental changes reduces network traffic and job runtime drastically.

Step 5: Tuning Your Spark Executors & Memory

Sometimes partitions alone aren't enough; tune Spark execution parameters per job:

conf = spark.sparkContext._conf.setAll([
    ('spark.executor.memory', '4g'),
    ('spark.executor.cores', '2'),
    ('spark.dynamicAllocation.enabled', 'false')
])

In AWS Glue version 2.0+, you can define worker type and count when creating the job (G.1X=1 core/4GB RAM per worker typically).

For huge datasets:

Increase workers count instead of overloading individual workers.
Avoid overly large executors as that can lead to throttling or garbage collection issues.

Test different combinations based on job run time logs in CloudWatch until you reach a balance of cost vs speed.

Step 6: Optimize Output Data Layouts on S3

Avoid dumping all raw data into one big Parquet file; perform partitioning on keys commonly filtered by downstream queries like date or region:

sink.partitionKeys = ['year', 'month', 'day']

Partition pruning accelerates query engines like Athena later when scanning massive datasets by only accessing relevant partitions—saving money and time.

Bonus: Automate Monitoring & Alerts

Set up Amazon CloudWatch metrics alarms for:

Job failure rates.
Longer-than-usual job runtimes.
Data skew or partition size anomalies in S3.

Additionally, use AWS Glue job bookmarks if applicable for easy incremental runs without custom state tracking code.

Conclusion

By understanding how AWS Glue interacts with JDBC sources like RDS—and tuning parallel reads plus output layout—you can build fast, scalable ETL workflows that cut down latency and reduce costs compared to naive setups. Switching from black-box usage toward architecture-aware pipelines pays dividends when processing tens of millions of rows routinely.

Give it a try following steps shared here—and feel free to share feedback or questions below!

Aws Glue Rds To S3