Blog archive

Build an AWS RDS to S3 ETL Pipeline with Jenkins

Automate RDS exports to S3 with Jenkins, Glue Crawlers, and Athena for fast ETL, schema detection, and cost-efficient analytics.

Published · Republished on Medium

JenkinsAWSData LakeEtlS3

Screenshot from Automating RDS to S3 ETL Pipelines with Jenkins: Build a Scalable AWS Data Lake

Choose the correct data-movement pattern

Amazon RDS snapshot export is an asynchronous batch operation. It reads a DB snapshot and writes analytical files to Amazon S3 without running an extraction query against the live database. It is useful for periodic reporting, historical analysis, and data-lake ingestion, but it is not a near-real-time change-data-capture pipeline.

Use AWS Database Migration Service, native logical replication, or another CDC design when consumers need row-level changes with low latency. Use the workflow below when data freshness can follow the snapshot and export schedule.

Pipeline boundaries

The complete batch has distinct stages:

  1. Select or create a consistent RDS snapshot.
  2. Wait until the snapshot is available.
  3. Start an RDS snapshot export to a unique S3 prefix.
  4. Poll the asynchronous export until it is COMPLETE or a failure state.
  5. Update the Glue Data Catalog only after export success.
  6. Validate the catalog and an Athena query before publishing the batch as ready.

Jenkins is the orchestrator here. RDS performs the export, Glue discovers metadata, and Athena queries the exported data. A crawler infers schema; it does not by itself transform or validate business data.

Prerequisites and service constraints

  • The snapshot, S3 bucket, and export task must be in the same AWS Region.
  • Confirm that snapshot export supports the selected RDS engine and Region.
  • The source ARN passed to start-export-task must identify a DB snapshot or DB cluster snapshot, not a live DB instance ARN.
  • Use a customer-managed symmetric KMS key whose policy allows the required grant operations.
  • Give the RDS export service role access only to the intended bucket and prefix.
  • Enable S3 versioning or an immutable publication pattern when downstream consumers require reproducible datasets.

Snapshot exports are written in a structure determined by RDS and the database engine. Inspect a representative export before designing Glue tables and Athena queries around its paths and column types.

Separate the two IAM roles

The Jenkins execution role starts and observes the workflow. The RDS export service role is assumed by export.rds.amazonaws.com to write export objects. Keeping these responsibilities separate makes permissions easier to review.

The Jenkins role normally needs scoped permissions such as:

json

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Sid": "OperateSnapshotExport",
      "Effect": "Allow",
      "Action": [
        "rds:CreateDBSnapshot",
        "rds:DescribeDBSnapshots",
        "rds:StartExportTask",
        "rds:DescribeExportTasks"
      ],
      "Resource": "*"
    },
    {
      "Sid": "OperateCatalogCrawler",
      "Effect": "Allow",
      "Action": [
        "glue:StartCrawler",
        "glue:GetCrawler"
      ],
      "Resource": "arn:aws:glue:ap-southeast-1:123456789012:crawler/rds-snapshot-crawler"
    },
    {
      "Sid": "UseExportKey",
      "Effect": "Allow",
      "Action": [
        "kms:CreateGrant",
        "kms:DescribeKey"
      ],
      "Resource": "arn:aws:kms:ap-southeast-1:123456789012:key/11111111-2222-3333-4444-555555555555"
    }
  ]
}

Some RDS actions do not support every desired resource-level restriction. Validate the final policy with IAM Access Analyzer and add conditions such as approved Regions, resource tags, and role boundaries where supported.

The export service role trust policy should allow only the RDS export service and protect against confused-deputy access:

json

{
  "Version": "2012-10-17",
  "Statement": [{
    "Effect": "Allow",
    "Principal": { "Service": "export.rds.amazonaws.com" },
    "Action": "sts:AssumeRole",
    "Condition": {
      "StringEquals": { "aws:SourceAccount": "123456789012" },
      "ArnLike": { "aws:SourceArn": "arn:aws:rds:ap-southeast-1:123456789012:snapshot:*" }
    }
  }]
}

Its permissions must include the S3 operations documented for snapshot export, scoped to the data-lake bucket and export prefix. The KMS key policy must allow kms:CreateGrant and kms:DescribeKey for the authorized caller. Review explicit deny statements so they do not unintentionally block export.rds.amazonaws.com.

A safer Jenkins pipeline

The following example creates a snapshot, waits for it, starts an export, handles every terminal status, and runs the crawler only after success. Replace example identifiers with managed Jenkins configuration, not user-controlled build parameters.

groovy

pipeline {
  agent { label 'aws-ops' }

  options {
    disableConcurrentBuilds()
    timeout(time: 4, unit: 'HOURS')
  }

  environment {
    AWS_REGION = 'ap-southeast-1'
    DB_INSTANCE_ID = 'prod-postgres'
    DATA_LAKE_BUCKET = 'example-data-lake'
    EXPORT_ROLE_ARN = 'arn:aws:iam::123456789012:role/rds-snapshot-export'
    KMS_KEY_ARN = 'arn:aws:kms:ap-southeast-1:123456789012:key/11111111-2222-3333-4444-555555555555'
    CRAWLER_NAME = 'rds-snapshot-crawler'
  }

  stages {
    stage('Create snapshot') {
      steps {
        script {
          env.BATCH_ID = "${new Date().format('yyyyMMdd-HHmmss', TimeZone.getTimeZone('UTC'))}-${env.BUILD_NUMBER}"
          env.SNAPSHOT_ID = "analytics-${env.BATCH_ID}"
          env.EXPORT_TASK_ID = "rds-export-${env.BATCH_ID}"
          env.S3_PREFIX = "raw/rds/${env.BATCH_ID}"

          sh '''
            set -eu
            aws rds create-db-snapshot \
              --region "$AWS_REGION" \
              --db-instance-identifier "$DB_INSTANCE_ID" \
              --db-snapshot-identifier "$SNAPSHOT_ID"
            aws rds wait db-snapshot-available \
              --region "$AWS_REGION" \
              --db-snapshot-identifier "$SNAPSHOT_ID"
          '''

          env.SNAPSHOT_ARN = sh(
            script: '''aws rds describe-db-snapshots \
              --region "$AWS_REGION" \
              --db-snapshot-identifier "$SNAPSHOT_ID" \
              --query 'DBSnapshots[0].DBSnapshotArn' \
              --output text''',
            returnStdout: true
          ).trim()
        }
      }
    }

    stage('Export snapshot') {
      steps {
        sh '''
          set -eu
          aws rds start-export-task \
            --region "$AWS_REGION" \
            --export-task-identifier "$EXPORT_TASK_ID" \
            --source-arn "$SNAPSHOT_ARN" \
            --s3-bucket-name "$DATA_LAKE_BUCKET" \
            --s3-prefix "$S3_PREFIX" \
            --iam-role-arn "$EXPORT_ROLE_ARN" \
            --kms-key-id "$KMS_KEY_ARN"
        '''

        script {
          waitUntil(initialRecurrencePeriod: 15000) {
            def result = sh(
              script: '''aws rds describe-export-tasks \
                --region "$AWS_REGION" \
                --export-task-identifier "$EXPORT_TASK_ID" \
                --query 'ExportTasks[0].[Status,FailureCause,WarningMessage]' \
                --output text''',
              returnStdout: true
            ).trim()

            def status = result.tokenize()[0]
            echo "RDS export status: ${status}"

            if (status in ['FAILED', 'CANCELED']) {
              error("RDS export ended in ${status}: ${result}")
            }
            return status == 'COMPLETE'
          }
        }
      }
    }

    stage('Update Glue catalog') {
      steps {
        sh '''
          set -eu
          state=$(aws glue get-crawler --name "$CRAWLER_NAME" --query 'Crawler.State' --output text)
          test "$state" = "READY"
          aws glue start-crawler --name "$CRAWLER_NAME"
        '''

        script {
          waitUntil(initialRecurrencePeriod: 10000) {
            def state = sh(
              script: '''aws glue get-crawler --name "$CRAWLER_NAME" \
                --query 'Crawler.State' --output text''',
              returnStdout: true
            ).trim()
            return state == 'READY'
          }
        }

        sh '''
          last_status=$(aws glue get-crawler --name "$CRAWLER_NAME" \
            --query 'Crawler.LastCrawl.Status' --output text)
          test "$last_status" = "SUCCEEDED"
        '''
      }
    }
  }

  post {
    always {
      echo "Batch ${BATCH_ID}: snapshot=${SNAPSHOT_ID}, export=${EXPORT_TASK_ID}, prefix=${S3_PREFIX}"
    }
  }
}

The unique batch prefix makes retries easier to reason about and prevents a failed rerun from silently mixing objects with a previous successful export. If Jenkins restarts after start-export-task, first call describe-export-tasks for the deterministic task ID; do not create a second export blindly.

For long-running or high-volume workflows, consider Step Functions or EventBridge rather than occupying a Jenkins executor while polling.

Publish only validated data

Do not treat a READY crawler as proof that the last crawl succeeded. Check Crawler.LastCrawl.Status, inspect schema changes, then run a small Athena validation query against the expected partition or table. Publish a manifest or update a stable ready/ pointer only after those checks pass.

Useful validation includes:

  • Expected tables and required columns exist.
  • Row counts and null ratios remain within agreed thresholds.
  • The batch corresponds to the intended snapshot time.
  • Sensitive columns are excluded, tokenized, or access-controlled.
  • Athena workgroups enforce result encryption, query limits, and approved output locations.

Lifecycle and retention

Apply lifecycle rules only after checking query and recovery requirements. Moving raw/ data to an archive class after 30 days can add retrieval delay and cost, and may break routine Athena access expectations. A safer lifecycle commonly separates:

  • immutable raw exports retained for the required audit period;
  • curated data optimized for routine queries;
  • failed or abandoned prefixes expired quickly;
  • noncurrent versions removed according to policy.

Also define who deletes the temporary RDS snapshots. Keep them long enough for recovery and investigation, then expire them through an explicit retention job or AWS Backup policy.

Failure modes to test

  1. Snapshot creation or waiter timeout.
  2. Wrong Region, unsupported engine, or invalid snapshot ARN.
  3. RDS export role cannot access the S3 prefix.
  4. KMS key is disabled or its grant is denied.
  5. Export returns FAILED, CANCELED, or a warning.
  6. Jenkins restarts while the asynchronous export is running.
  7. The crawler is already running or its last crawl fails.
  8. Schema drift breaks an Athena query.
  9. A rerun attempts to reuse an existing export task ID or prefix.

CloudTrail, RDS export status, Glue crawler history, S3 data events where justified, and Jenkins logs together form the operational audit trail. Jenkins logs alone are not a complete record of AWS-side actions.

Further reading

Conclusion

RDS snapshot export provides a reliable batch ingestion boundary when the pipeline treats snapshots, exports, catalog updates, and data validation as separate asynchronous steps. Make task IDs and prefixes deterministic, fail on every terminal error, protect the S3 and KMS permissions, and publish a batch only after the catalog and query checks succeed.