Forem: Aki

Lightweight ETL on AWS Lambda Using DuckDB and Snowflake Connector

Aki — Sat, 04 Apr 2026 13:54:10 +0000

Original Japanese article: AWS Lambda × DuckDB × Snowflake ConnectorによるETLの実装

Introduction

I'm Aki, an AWS Community Builder (@jitepengin).

In my previous article, I introduced how to connect to Snowflake from AWS Lambda using Key Pair authentication.

Securely Implementing Snowflake AWS Lambda Integration with Key Pair Authentication + Secrets Manager

This time, I would like to try the event-driven data ingestion approach that I introduced in the previous article.

In this article, I will implement an event-driven ETL pipeline that uses DuckDB on AWS Lambda to perform lightweight transformations on Parquet files stored in Amazon S3 and then load the processed data into Snowflake.

In addition, during the implementation process, I encountered an interesting limitation where write_pandas fails when writing to a Catalog-Linked Database. I will also summarize the root cause and the workaround.

Why Snowpipe Is Not Enough

Snowpipe is a very convenient feature for automatic data ingestion.

However, it has limitations when it comes to data transformation and complex filtering.

In other words, when you need preprocessing, filtering, or the integration of multiple events, you need to choose another approach.

In such cases, AWS Lambda becomes a strong option due to its high flexibility.

Architecture

A Parquet file is uploaded to S3
Lambda is triggered by the S3 event
DuckDB reads the data and performs the required transformations
snowflake.connector writes the data into Snowflake

The two key libraries used in this implementation are shown below.

DuckDB

DuckDB is an embedded database engine designed for OLAP (Online Analytical Processing).

Because DuckDB is extremely lightweight and supports in-memory processing, it can run efficiently even in a simple execution environment such as AWS Lambda.

It is said to provide particularly strong performance for batch workloads such as data analytics and ETL processing.

In addition, it enables SQL-based filtering and lightweight data transformations, allowing for intuitive implementations.

https://duckdb.org/

Snowflake Connector

Snowflake Connector for Python is a library that provides an interface for connecting to Snowflake and executing all standard operations.

By using this library, it becomes possible to operate Snowflake from runtime environments such as Lambda.

https://docs.snowflake.com/en/developer-guide/python-connector/python-connector

Sample Code

In the sample code below, WHERE VendorID = 1 is added as an ETL filter.

By performing filtering and data transformation inside Lambda, highly flexible preprocessing becomes possible.

import duckdb
import boto3
import json
import snowflake.connector
from cryptography.hazmat.primitives import serialization
from cryptography.hazmat.backends import default_backend
from snowflake.connector.pandas_tools import write_pandas
import time
import random

SECRET_ID = "snowflake-keypair"


def get_secret():
    client = boto3.client("secretsmanager")
    response = client.get_secret_value(SecretId=SECRET_ID)
    return json.loads(response["SecretString"])

def lambda_handler(event, context):
    conn = None
    duckdb_connection = None

    try:
        duckdb_connection = duckdb.connect(database=":memory:")
        duckdb_connection.execute("SET home_directory='/tmp'")
        duckdb_connection.execute("INSTALL httpfs")
        duckdb_connection.execute("LOAD httpfs")

        s3_bucket = event["Records"][0]["s3"]["bucket"]["name"]
        s3_object_key = event["Records"][0]["s3"]["object"]["key"]

        s3_input_path = f"s3://{s3_bucket}/{s3_object_key}"
        print(f"S3 input path: {s3_input_path}")


        query = f"""
            SELECT *
            FROM read_parquet('{s3_input_path}')
            WHERE VendorID = 1
        """

        result_arrow_table = duckdb_connection.execute(query).fetch_arrow_table()

        print(f"DuckDB filtered rows: {result_arrow_table.num_rows}")

        secret = get_secret()

        private_key_obj = serialization.load_pem_private_key(
            secret["privateKey"].encode("utf-8"),
            password=None,
            backend=default_backend()
        )

        conn = snowflake.connector.connect(
            user=secret["user"],
            account=secret["account"],
            private_key=private_key_obj,
            role=secret.get("role"),
            warehouse=secret.get("warehouse"),
            database=secret.get("database"),
            schema=secret.get("schema")
        )

        cur = conn.cursor()
        cur.execute(f"USE DATABASE {secret['database']}")
        cur.execute(f"USE SCHEMA {secret['schema']}")
        cur.close()

        import pandas as pd

        df = result_arrow_table.to_pandas()

        df["tpep_pickup_datetime"] = pd.to_datetime(
            df["tpep_pickup_datetime"],
            unit="us"
        ).dt.strftime("%Y-%m-%d %H:%M:%S")

        df["tpep_dropoff_datetime"] = pd.to_datetime(
            df["tpep_dropoff_datetime"],
            unit="us"
        ).dt.strftime("%Y-%m-%d %H:%M:%S")

        success, nchunks, nrows, _ = write_pandas(
            conn,
            df,
            table_name="YELLOW_TRIPDATA"
        )

        print(
            f"Snowflake write success={success}, "
            f"rows={nrows}, chunks={nchunks}"
        )

        return {
            "statusCode": 200,
            "body": (
                f"Processed {result_arrow_table.num_rows} rows "
                f"and wrote {nrows} rows to Snowflake."
            )
        }

    except Exception as e:
        print(f"An error occurred: {e}")
        import traceback
        traceback.print_exc()

        return {
            "statusCode": 500,
            "body": str(e)
        }

    finally:
        if conn:
            conn.close()

        if duckdb_connection:
            duckdb_connection.close()

Execution Result

As shown above, the data was successfully written.

Switching the Destination to a Catalog-Linked Database

As introduced in a previous article, what happens if we try writing to a table configured with a Catalog-Linked Database (Iceberg)?

Let’s test it.

AWS Snowflake Lakehouse: 2 Practical Apache Iceberg Integration Patterns

A Write Error Occurs

When attempting to write to the Catalog-Linked Database, the following error occurred:

{
  "statusCode": 500,
  "body": "093678 (0A000): SQL Compilation Error: This operation is not supported in a catalog-linked database."
}

Why Writing to a Catalog-Linked Database Fails

The reason this happens is due to the interaction between write_pandas in the Snowflake Connector and the constraints of a Catalog-Linked Database.

Internally, write_pandas creates a temporary stage.

https://docs.snowflake.com/en/developer-guide/python-connector/python-connector-api#id13

Writes a pandas DataFrame to a table in a Snowflake database.

To write the data to the table, the function saves the data to Parquet files, uses the PUT command to upload these files to a temporary stage, and uses the COPY INTO
command to copy the data from the files to the table. You can use some of the function parameters to control how the PUT and COPY INTO
statements are executed.
However, stage creation is not supported in a Catalog-Linked Database.
https://docs.snowflake.com/en/user-guide/tables-iceberg-catalog-linked-database#considerations-for-using-a-catalog-linked-database-for-iceberg-tables

"You can create schemas, externally managed Iceberg tables, or database roles in a catalog-linked database. Creating other Snowflake objects isn't currently supported."

This conflict causes write_pandas to fail with the error:
This operation is not supported in a catalog-linked database.
More specifically, the temporary stage created internally falls under the category of “other Snowflake objects,” so the error occurs at the point where CREATE TEMPORARY STAGE is executed.
That said, there is a workaround.
How to Write to a Catalog-Linked Database
A relatively simple approach is to use an INSERT statement directly.
Here is an example implementation:
        for i in range(0, len(df), 1000): 
            chunk = df.iloc[i:i+1000]
            columns = ", ".join(chunk.columns)
            placeholders = ", ".join(["%s"] * len(chunk.columns))
            sql = f"INSERT INTO {secret['schema']}.YELLOW_TRIPDATA ({columns}) VALUES ({placeholders})"
            cur.executemany(sql, chunk.values.tolist())
Alternatively, another good approach is to create a stage in a different database and execute the INSERT through that route.
Conclusion
In this article, I implemented a lightweight event-driven ETL pipeline triggered by S3 events using AWS Lambda, DuckDB, and the Snowflake Connector.
By using DuckDB inside Lambda, I was able to perform SQL-based filtering and lightweight transformations directly on Parquet files stored in S3, and successfully load the processed results into Snowflake.
In addition, I confirmed an important limitation: when using write_pandas against a Catalog-Linked Database (Iceberg), the write fails because the connector internally creates a temporary stage.
Although there are some constraints, combining DuckDB and the Snowflake Connector enables the construction of a low-cost and flexible data processing pipeline for Snowflake.
The key point is to clearly understand how Snowflake manages Iceberg tables.
It is important to determine whether the table is a Snowflake-managed Iceberg table or connected through mechanisms such as a Catalog-Linked Database, and to properly understand that structure.
In any case, the combination of Snowflake and Iceberg is an extremely powerful option for building a Lakehouse architecture.
I hope this article will be helpful for those considering lightweight data processing and real-time ETL pipelines with AWS and Snowflake when working with Iceberg tables.

Securely Implementing Snowflake AWS Lambda Integration with Key Pair Authentication + Secrets Manager

Aki — Thu, 02 Apr 2026 14:14:17 +0000

Original Japanese article: Snowflake × AWS Lambda連携をKey Pair認証 + Secrets Managerで安全に実装する

Introduction

I'm Aki, an AWS Community Builder (@jitepengin).

When building data pipelines and business systems on AWS, there are cases where you need to access Snowflake directly as part of your application processing.

For example, the following use cases are common:

Load data received via an API into Snowflake
Execute SQL on the Snowflake side after ETL / ELT processing is completed
Write results from external system integrations into Snowflake
Trigger Snowflake stored procedures as part of task execution

AWS Lambda works extremely well with serverless, event-driven processing, and by combining it with Snowflake, you can build a highly flexible data integration platform.

On the other hand, when connecting from Lambda to Snowflake, it is critically important to determine how authentication credentials should be managed securely.

As of 2026, for Snowflake system integrations, Key Pair authentication or OAuth has become the standard, and embedding passwords directly is no longer recommended.

In this article, I would like to explain an implementation pattern that uses AWS Secrets Manager to securely manage the private key and connects from Lambda to Snowflake using Key Pair authentication.

Reference: Deprecation plan for single-factor password sign-in
https://docs.snowflake.com/ja/user-guide/security-mfa-rollout

First of all, why access Snowflake from Lambda?

Before getting into the implementation, let’s first consider why it is necessary to access Snowflake from Lambda in the first place.

I’d like to explain this using three common use case patterns.

Case 1: Event-driven data ingestion

I think this is probably the most common pattern.

S3 / API Gateway / EventBridge
            ↓
         Lambda
            ↓
       Snowflake

For example:

order data received through an API
CSV / JSON files uploaded to S3
SaaS integration events

These can be received by Lambda and then directly loaded into Snowflake.

There are architectures that use Snowpipe, but when:

preprocessing is required
format conversion is required
multiple system integrations are involved

Lambda is often easier to work with.

Case 2: Snowflake integration after AWS ETL completion

This pattern executes SQL on Snowflake via Lambda after ETL processing has been completed on the AWS side.

This example assumes Apache Iceberg is being used as the data platform.

Glue / Lambda
      ↓
S3 Iceberg
      ↓
Lambda
      ↓
Execute SQL on Snowflake

For example:

ETL completed on AWS
REFRESH on Snowflake
execute MERGE SQL
data quality checks
update BI marts

This is a common pattern for downstream processing.

Case 3: Operations automation

This is another pattern that can often be seen in real-world implementations.

For example:

starting / stopping warehouses
executing tasks
automatic SQL execution

These operations can be automated from Lambda.

Architecture

The architecture used in this article is as follows.

Event Source
   ↓
AWS Lambda
   ↓
Secrets Manager
   ↓
Retrieve Private Key
   ↓
Snowflake

Lambda retrieves the private key from Secrets Manager and connects to Snowflake using Key Pair authentication.

Security best practices

1. Do not store the private key in environment variables

This is important not only for Snowflake, but for any sensitive credentials.

A common anti-pattern is storing it like this:

PRIVATE_KEY = os.environ["PRIVATE_KEY"]

This should absolutely be avoided.

In addition to security concerns, there are operational issues such as:

possibility of accidental log output
operational overhead during configuration changes
difficulty in rotation
weak access control

2. Centralized management with Secrets Manager

Sensitive information like this should ideally be centrally managed using Secrets Manager.

By using Secrets Manager, you gain the following benefits:

IAM-based access control
KMS encryption
audit logging with CloudTrail
credential rotation support

3. Principle of least privilege for IAM

Only the minimum required permissions should be granted to Lambda.

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Action": "secretsmanager:GetSecretValue",
      "Resource": "arn:aws:secretsmanager:ap-northeast-1:123456789012:secret:snowflake-keypair-*"
    }
  ]
}

4. Use a dedicated Snowflake service user

Do not use personal users.

Instead, create a dedicated service user.

The service user should be created with:

TYPE = 'LEGACY_SERVICE'
or TYPE = 'SERVICE'

Registering authentication information

1. Create the private key

First, create the private key.

openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out rsa_key.p8 -nocrypt
openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub

2. Configure Snowflake

ALTER USER lambda_service_user
SET RSA_PUBLIC_KEY='MIIBIjANBgkq...';

Use the contents of the rsa_key.pub file as the public key.

3. Register in Secrets Manager

Store the private key as follows.

{
  "account": "XXXXXXXXXX",
  "user": "lambda_service_user",
  "privateKey": "-----BEGIN PRIVATE KEY-----\n...\n-----END PRIVATE KEY-----",
  "passphrase": "",
  "role": "LAMBDA_ROLE",
  "warehouse": "COMPUTE_WH",
  "database": "ICEBERGDB",
  "schema": "PUBLIC"
}

account: Snowflake account identifier
user: Snowflake connection user
privateKey: private key created in step 1 (rsa_key.p8)

The <orgname>-<account_name> format is recommended for the account identifier.

Now the preparation is complete.

Lambda sample code

For verification purposes, this example simply executes:

SELECT CURRENT_VERSION()

The key point is using snowflake.connector, which makes connecting to Snowflake very straightforward.

Add snowflake.connector using a Lambda Layer, or build the Lambda function as a container image.

import json
import boto3
import snowflake.connector
from cryptography.hazmat.primitives import serialization
from cryptography.hazmat.backends import default_backend
import time
import random

SECRET_ID = "snowflake-keypair"

def get_secret():
    client = boto3.client("secretsmanager")
    response = client.get_secret_value(SecretId=SECRET_ID)
    return json.loads(response["SecretString"])

def execute_query_with_retry(conn, query, max_retries=3):
    retry_count = 0
    while retry_count < max_retries:
        try:
            cur = conn.cursor()
            cur.execute(query)
            result = cur.fetchone()
            cur.close()
            return result
        except snowflake.connector.Error as e:
            print(f"Query failed (attempt {retry_count+1}/{max_retries}): {e}")
            retry_count += 1
            sleep_time = min(2 ** retry_count, 30) + random.random()
            time.sleep(sleep_time)
    raise Exception("Query execution exceeded max retries.")

def lambda_handler(event, context):
    secret = get_secret()

    private_key_obj = serialization.load_pem_private_key(
        secret["privateKey"].encode("utf-8"),
        password=None,
        backend=default_backend()
    )

    conn = None
    try:
        conn = snowflake.connector.connect(
            user=secret["user"],
            account=secret["account"],
            private_key=private_key_obj,
            role=secret.get("role"),
            warehouse=secret.get("warehouse"),
            database=secret.get("database"),
            schema=secret.get("schema")
        )

        result = execute_query_with_retry(conn, "SELECT CURRENT_VERSION()")
        return {
            "statusCode": 200,
            "body": str(result)
        }

    except Exception as e:
        print(f"Lambda execution error: {e}")
        return {
            "statusCode": 500,
            "body": str(e)
        }
    finally:
        if conn:
            conn.close()

Depending on default service user settings can lead to unintended behavior during configuration changes.

Therefore, explicitly specifying:

role
warehouse
database
schema

is safer for production environments.

Execution result

{
  "statusCode": 200,
  "body": "('10.11.2',)"
}

Executed on Snowflake side:

SELECT CURRENT_VERSION()
>10.11.2

The results match, so the connection was successful.

Additional considerations

Strengthening network security

Using AWS PrivateLink enables communication within a private network and eliminates internet-based communication risks.

This can be implemented with the following steps:

enable private connectivity in Snowflake
create a VPC endpoint (com.amazonaws.vpce.snowflake)
pair it with Snowflake’s service principal
place Lambda in the same VPC
restrict access using security groups

Error handling

As implemented in the sample code, it is a good idea to add retry processing to tolerate temporary network failures and transient Snowflake issues.

Using exponential backoff improves resilience compared to fixed delays.

Monitoring

The following monitoring perspectives are recommended:

CloudWatch Logs: capture connection errors and query failures
CloudTrail: verify access logs to Secrets Manager
Snowflake audit logs: monitor user connection activity

Conclusion

In this article, I introduced the full flow from key management to connection setup for accessing Snowflake from AWS Lambda using Key Pair authentication.

There are more use cases for Lambda-to-Snowflake access than many people expect, especially for:

event-driven processing
downstream ETL workflows

And of course, secure credential management is critically important—not only for Snowflake, but for any system integration.

At this point in time, Key Pair authentication + Secrets Manager is likely the standard implementation pattern for connecting to Snowflake.

I hope this article helps as a reference when building application integrations between AWS and Snowflake.

AWS Snowflake Lakehouse: 2 Practical Apache Iceberg Integration Patterns

Aki — Tue, 31 Mar 2026 20:44:32 +0000

Original Japanese article: AWSのレイクハウス（Apache Iceberg）をSnowflakeと連携する2つのパターンを整理する

Introduction

I'm Aki, an AWS Community Builder (@jitepengin).

In recent years, it has become increasingly common to build lakehouse architectures centered around Apache Iceberg.

Before the rise of lakehouse architecture, it was common to design systems where data was consolidated into a specific platform, such as:

an architecture centered around Amazon Redshift on AWS
an architecture centered around internal tables in Snowflake

However, with the advent of Apache Iceberg, this assumption is rapidly changing.

Now that data on Amazon S3 can be directly accessed from multiple engines, what matters is no longer simply product selection.
Instead, the architecture design itself has become the central focus: where the data resides, who owns the write responsibility, and who holds governance authority.

In this article, focusing on the coexistence of AWS and Snowflake, I will organize the following:

two patterns based on S3 × Iceberg
connectivity with Power BI Service
future prospects including AI utilization

Why AWS × Snowflake Coexistence Is Necessary

The greatest value of Apache Iceberg lies in its ability to separate data from the query engine.

For example, while keeping the physical data stored in S3, the same data can be accessed from multiple tools such as Athena, AWS Glue / Spark, Redshift, and Snowflake.

In other words, while consolidating the physical data into a single location, it has become possible to choose the most suitable analytics platform depending on the use case.

As a result, the architectural discussion has shifted from “which product should we use?” to “where should data sovereignty reside, and where should analytical ownership be placed?”

The benefits of using Snowflake here include:

user-friendly UI/UX
powerful SQL analytics capabilities
integration with Cortex AI

In particular, I believe that an architecture where AWS serves as the data sovereignty layer while Snowflake is utilized as the analytics layer is highly compatible.

Two Patterns for Snowflake × S3 Integration

Here, I will organize the two commonly used patterns when integrating Snowflake with S3.

Pattern 1: Glue Catalog Integration

In this pattern, Iceberg tables stored on S3 are referenced from Snowflake through AWS Glue Data Catalog.

The advantages of this architecture are:

relatively simple configuration
S3 becomes the Single Source of Truth
because Snowflake cannot write, AWS retains sovereignty over data management (this can also be considered a disadvantage)
since user access is consolidated into Snowflake, access control can be centralized there

In other words, Snowflake focuses solely on the role of data analytics (query engine), while AWS retains authority over data management.

Setup Procedure

Step 1: Create an External Volume (S3 Access Configuration)

Run the following on the Snowflake side:

CREATE EXTERNAL VOLUME IF NOT EXISTS sample_iceberg_volume
  STORAGE_LOCATIONS = (
    (
      NAME = 'my-s3-location'
      STORAGE_PROVIDER = 'S3'
      STORAGE_BASE_URL = catalog S3 path
      STORAGE_AWS_ROLE_ARN = role ARN to use
      STORAGE_AWS_EXTERNAL_ID = 'my_external_id'
    )
  );

Step 2: Create Glue Catalog Integration

Run the following on the Snowflake side:

CREATE OR REPLACE CATALOG INTEGRATION glue_catalog_int -- arbitrary name
  CATALOG_SOURCE = GLUE
  CATALOG_NAMESPACE = Glue catalog namespace
  TABLE_FORMAT = ICEBERG
  GLUE_AWS_ROLE_ARN = role ARN to use
  GLUE_CATALOG_ID = Glue catalog ID to use
  GLUE_REGION = 'ap-northeast-1'
  ENABLED = TRUE;

Step 3: Retrieve Required Information for AWS Trust Policy

DESC EXTERNAL VOLUME sample_iceberg_volume;

→ Note down STORAGE_AWS_IAM_USER_ARN and STORAGE_AWS_EXTERNAL_ID

DESC CATALOG INTEGRATION glue_catalog_int;

→ Note down GLUE_AWS_IAM_USER_ARN and GLUE_AWS_EXTERNAL_ID

Since the two External IDs above will have different values, be sure to add both to the AWS IAM role Trust Policy.

Please configure the Trust Policy for the role used on the AWS side.

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Principal": {
                "AWS": STORAGE_AWS_IAM_USER_ARN
            },
            "Action": "sts:AssumeRole",
            "Condition": {
                "StringEquals": {
                    "sts:ExternalId": STORAGE_AWS_EXTERNAL_ID
                }
            }
        },
        {
            "Effect": "Allow",
            "Principal": {
                "AWS": GLUE_AWS_IAM_USER_ARN
            },
            "Action": "sts:AssumeRole",
            "Condition": {
                "StringEquals": {
                    "sts:ExternalId": GLUE_AWS_EXTERNAL_ID
                }
            }
        }
    ]
}

Step 4: Create Database

Run the following on the Snowflake side:

CREATE DATABASE IF NOT EXISTS icebergdb;

Step 5: Create Table

Run the following on the Snowflake side:

CREATE OR REPLACE ICEBERG TABLE icebergdb.public.yellow_tripdata -- arbitrary name
  EXTERNAL_VOLUME = 'sample_iceberg_volume'
  CATALOG = 'glue_catalog_int'
  CATALOG_TABLE_NAME = 'yellow_tripdata'
  AUTO_REFRESH = TRUE;

Step 6: Verify Data

Run the following on the Snowflake side:

SELECT * FROM icebergdb.public.yellow_tripdata LIMIT 5;

We were able to read the data from S3!

Pattern 2: Catalog-Linked Database (Iceberg)

This pattern integrates Glue Catalog using the REST Catalog approach and manages Iceberg tables directly from the Snowflake side.

The advantages of this architecture are:

read and write operations from Snowflake are possible
the physical data remains stored in S3
Snowflake users can perform SQL-based updates and analytics
easier integration with Power BI and Cortex AI

In other words, the biggest feature is that analysis and updates can be performed from Snowflake while keeping the physical data in S3.

However, governance must be considered from both the AWS side and the Snowflake side.

Setup Procedure

Step 1: Create an External Volume (S3 Access Configuration)

CREATE EXTERNAL VOLUME IF NOT EXISTS sample_iceberg_volume
  STORAGE_LOCATIONS = (
    (
      NAME = 'my-s3-location'
      STORAGE_PROVIDER = 'S3'
      STORAGE_BASE_URL = catalog S3 path
      STORAGE_AWS_ROLE_ARN = role ARN to use
      STORAGE_AWS_EXTERNAL_ID = 'my_external_id'
    )
  );

Step 2: Create Glue Iceberg REST Catalog Integration

CREATE OR REPLACE CATALOG INTEGRATION glue_rest_catalog_int
  CATALOG_SOURCE = ICEBERG_REST
  TABLE_FORMAT = ICEBERG
  CATALOG_NAMESPACE = Glue catalog namespace
  REST_CONFIG = (
    CATALOG_URI = Glue catalog URI
    CATALOG_API_TYPE = AWS_GLUE
    CATALOG_NAME = AWS account ID
  )
  REST_AUTHENTICATION = (
    TYPE = SIGV4
    SIGV4_IAM_ROLE = role ARN to use
    SIGV4_SIGNING_REGION = 'ap-northeast-1'
  )
  ENABLED = TRUE;

Step 3: Retrieve Required Information for AWS Trust Policy

DESC EXTERNAL VOLUME sample_iceberg_volume;

→ Note down STORAGE_AWS_IAM_USER_ARN and STORAGE_AWS_EXTERNAL_ID

DESC CATALOG INTEGRATION glue_rest_catalog_int;

→ Note down GLUE_AWS_IAM_USER_ARN and GLUE_AWS_EXTERNAL_ID

Since the two External IDs above will have different values, be sure to add both to the AWS IAM role Trust Policy.

Please configure the Trust Policy for the role used on the AWS side.

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Principal": {
                "AWS": STORAGE_AWS_IAM_USER_ARN
            },
            "Action": "sts:AssumeRole",
            "Condition": {
                "StringEquals": {
                    "sts:ExternalId": STORAGE_AWS_EXTERNAL_ID
                }
            }
        },
        {
            "Effect": "Allow",
            "Principal": {
                "AWS": GLUE_AWS_IAM_USER_ARN
            },
            "Action": "sts:AssumeRole",
            "Condition": {
                "StringEquals": {
                    "sts:ExternalId": GLUE_AWS_EXTERNAL_ID
                }
            }
        }
    ]
}

Step 4: Create Catalog Linked Database (Read/Write Enabled)

CREATE DATABASE my_iceberg_linked_db
  LINKED_CATALOG = (
    CATALOG = 'glue_rest_catalog_int',
    ALLOWED_WRITE_OPERATIONS = ALL
  )
  EXTERNAL_VOLUME = 'sample_iceberg_volume';

Step 5: Tables Are Automatically Discovered (Synced Every 30 Seconds)

SELECT * FROM my_iceberg_linked_db."icebergdb"."yellow_tripdata" LIMIT 5;

We were able to read the data from S3!

Step 6: Write Test

INSERT INTO my_iceberg_linked_db."icebergdb"."yellow_tripdata"
  (vendorid, tpep_pickup_datetime, tpep_dropoff_datetime, passenger_count,
   trip_distance, ratecodeid, store_and_fwd_flag, pulocationid, dolocationid,
   payment_type, fare_amount, extra, mta_tax, tip_amount, tolls_amount,
   improvement_surcharge, total_amount, congestion_surcharge, airport_fee)
VALUES
  (1, '2025-01-01 13:00:00', '2025-01-01 13:30:00', 2,
   3.5, 1, 'N', 100, 200,
   1, 15.0, 2.5, 0.5, 3.0, 0,
   1.0, 22.0, 2.5, 0);

Step 7: Verify Write

SELECT * FROM my_iceberg_linked_db."icebergdb"."yellow_tripdata"
WHERE tpep_pickup_datetime = '2025-01-01 13:00:00';

We were able to write data into S3!

Athena side

Before write

After write

Which Pattern Should You Choose?

For practical use, the following way of organizing it is easy to understand.

Use Case	Recommended Pattern
AWS-led ETL workloads, with Snowflake primarily used for read/query access	Pattern 1
BI / AI / SQL updates driven primarily from Snowflake	Pattern 2
Governance needs to be centralized on AWS	Pattern 1
Operations are mainly led by Snowflake users	Pattern 2

Integration with Power BI Service

Traditionally, when referencing an S3-based lakehouse from Power BI Service, many architectures used Redshift as an intermediary.

In such cases, securely connecting directly from Power BI Service often required provisioning an EC2 instance and configuring an on-premises data gateway.

This introduces additional operational costs such as EC2 management.

Now, how about Snowflake?

Power BI provides a native connector for Snowflake, allowing direct authentication and connection from Power BI Service.

This eliminates the need for relay servers or on-premises gateways that are often required in Redshift-based architectures.

In other words, it becomes possible to exclude costly operational components such as on-premises gateways.

In addition, since the semantically organized Gold layer on the Snowflake side can be directly connected to Power BI, this also improves usability for BI users.

Thinking in Terms of Medallion Architecture

Personally, I consider the following architecture to be highly practical for real-world use.

Bronze: S3 + Iceberg
Silver: S3 + Iceberg (with Snowflake integration as needed)
Gold: S3 + Iceberg → Snowflake integration

By especially aligning the Gold layer with Snowflake, it becomes easier to provide a semantic layer that is easy for BI users and business departments to consume.

Depending on the use case, the Silver layer can also be utilized for more detailed analysis.

In other words, this enables a separation of responsibilities between data management and data analytics.

Thinking in Terms of Separation of Ownership

This is the most important point in this article.

Domain	Ownership
Physical data	AWS
Governance	AWS / Snowflake
Analytics	Snowflake
AI interaction	Snowflake

What matters is not the product itself, but how ownership is separated.

By clearly defining this separation, it becomes easier to organize the scope of responsibilities across data engineering, BI, and AI utilization, which also provides benefits from an organizational management perspective.

The Change Brought by Snowflake Cortex AI

AI utilization will become even more important going forward.

As with other data platforms, AI adoption is progressing rapidly in Snowflake as well.

By leveraging Snowflake Cortex AI, it becomes possible to query Iceberg tables on S3 using natural language.

In other words, the data platform is evolving from “a platform for writing SQL” into “a platform for conversation.”

AI utilization is expected to continue evolving in many aspects.

One key point will be preparing data that is easier for AI to use—in other words, AI-ready data.

Conclusion

In this article, I organized two patterns for integrating an AWS lakehouse (Apache Iceberg) with Snowflake.

In recent data utilization scenarios, it is increasingly common not only to rely solely on AWS, but also to integrate with platforms such as Databricks and Snowflake as introduced here.

As mentioned earlier, what matters is not the product itself, but how ownership is separated.

Depending on which service takes responsibility for data, governance, and analytics, both the architecture and configuration will change.

In any case, what is truly important is not the product itself, but the perspective of how to design a data platform that users will continue to use over time.

Going forward, it will become even more important to design architectures not only from the perspective of where data is stored, but also from the viewpoint of who owns responsibility for each layer and how that responsibility connects to user value.

I hope this article will be helpful for those considering a combination of AWS and Snowflake.

Is AWS Glue Data Catalog Sufficient as a Data Catalog? Organizing Its Design, Limitations, and Complementary Strategies

Aki — Wed, 25 Mar 2026 14:41:14 +0000

Original Japanese article: AWS Glue Data Catalogはデータカタログとして十分か？設計・限界・補完戦略を整理する

Introduction

I'm Aki, an AWS Community Builder (@jitepengin).

As data utilization within organizations has advanced in recent years, the importance of data catalogs has continued to grow.

When building a data platform on AWS, the first thing that typically comes to mind as a data catalog is AWS Glue Data Catalog.
Especially in data lake architectures centered around Amazon S3, AWS Glue Data Catalog is almost a prerequisite service. By combining it with services like Athena, AWS Glue, and Redshift Spectrum, it is possible to quickly stand up a minimal data platform.

However, as data usage evolves, you may encounter challenges such as:

Not knowing which data to use
Multiple datasets that look similar
Being unable to determine whether data is trustworthy
Not being able to trace how data was generated

At first glance, these may appear to be separate issues, but in reality, they all stem from a single root cause: an insufficient data catalog.

In this article, starting from AWS Glue Data Catalog, we will explore:

The role of a data catalog
The strengths and limitations of AWS Glue Data Catalog
How to complement it within AWS
How to approach building a data catalog on AWS
A comparison with other data catalogs (OpenMetadata)

The conclusion is that AWS Glue Data Catalog is not a “data catalog” in the full sense.
Rather, it is a technical catalog used by query engines, and it is not sufficient as a catalog for humans to discover, understand, and trust data.

For this reason, a data catalog on AWS should not be designed as a single service, but as an architecture composed of multiple services.

What is a Data Catalog?

A data catalog is not simply about metadata management—it is a foundation that makes data usable.

Metadata can be understood as “data about data.”
Specifically, it includes information such as who created the data, what it means, how it is used, where it came from, how it flows, where it is stored, and what its quality is.

A data catalog centralizes this metadata and supports search and utilization.

Traditionally, data catalogs focused on table definitions and schema management. However, modern data catalogs are expected to include the following elements:

Category	Description
Metadata Management	Technical metadata (schemas, types, partitions), business metadata (meaning, usage, owner), operational metadata (job logs, processing metrics)
Data Discovery	Data discovery, filtering, classification
Data Lineage	Tracking data generation and transformation
Data Quality	Reliability indicators, anomaly detection
Data Governance	Access control and permission management

These elements span multiple domains defined in DMBOK, and it is rare for a single tool to cover all of them.

As will be explained later, AWS also requires combining multiple services to achieve this.
In the AWS context, it is more appropriate to think of a data catalog not as a single “service,” but as an “architecture.”

Role and Strengths of Glue Data Catalog

Glue Data Catalog is the core metadata management component in AWS and serves as a foundational element for operating a data platform.

Key Features of Glue Data Catalog

Feature	Description
Metadata Storage	Persistent storage of structured metadata
Schema Management	Definition and updates of table schemas
Partition Management	Management of partition information
Statistics	Column statistics such as min/max values and null counts
Tagging	Classification using key-value pairs
API/SDK	Programmatic access
Data Lineage	Basic lineage is available; advanced visualization requires additional tools
Operational Metadata	CloudWatch logs, Spark UI, job execution insights
Advanced Discovery	Console browsing, attribute filtering, unified search

Strengths of Glue Data Catalog

Seamless Integration with AWS Services

While this may seem obvious, it is an important point: Glue integrates natively with AWS services such as Athena, AWS Glue, and Redshift Spectrum.

Because these services reference the same catalog, it ensures consistency in how data is accessed across the platform.

Strong Affinity with Data Lakes

In modern lakehouse architectures, this is a significant advantage.

Glue Data Catalog allows data stored in S3 to be cataloged directly.
This makes it possible to build a lakehouse using formats like Iceberg and manage it through Glue Data Catalog.

(Note: Iceberg table metadata itself resides in S3, while Glue Data Catalog functions as the catalog endpoint.)

Is Glue Data Catalog Sufficient as a Data Catalog?

The conclusion is that Glue Data Catalog is a data platform–oriented catalog, not a user-oriented catalog.

It is highly effective as a technical metadata foundation referenced by analytics platforms.
However, its capabilities are limited when it comes to serving as a business catalog that enables users to discover, understand, and trust data.

In other words, Glue is extremely strong as the core (technical foundation) of a data catalog, but requires complementary services when used as a user-facing catalog that supports data utilization.

Category	Support by Glue Alone	Notes
Technical Metadata	○	Schemas, types, partitions, column statistics
Business Metadata	△	Descriptions, tags, classifications (advanced capabilities require Amazon DataZone)
Operational Metadata	△	Job execution history is stored; detailed metrics are managed in CloudWatch
Data Discovery	△	Console search and filtering (advanced capabilities require Amazon Q or Amazon DataZone)
Data Lineage	△	Basic lineage (input/output tables in Glue ETL jobs) is captured; no end-to-end lineage
Data Quality	△	Column statistics and auto statistics (advanced capabilities require Glue Data Quality or AWS Glue DataBrew)
Workflow Management	✕	Not handled by Glue Data Catalog
Data Governance	△	IAM integration, resource policies, encryption (advanced capabilities require Lake Formation)
Data Profiling	✕	Not supported

Supplement: How to Complement Areas Where Glue Alone Falls Short

Operational Metadata
Advanced metrics (e.g., processed record counts, error rates, memory usage) need to be managed using CloudWatch or AWS X-Ray.
Data Lineage
For end-to-end, advanced lineage visualization, you need Amazon DataZone, support for the OpenLineage specification, or the AWS Lineage API.
Data Quality
- Glue Data Quality: Rule-based validations (e.g., NULL checks, range checks)
- Glue DataBrew: Statistical profiling, distribution analysis, outlier detection (ML-based)
Workflow Management
Utilize AWS Step Functions, Amazon Managed Workflows for Apache Airflow (MWAA), or AWS Glue Workflows.
Data Profiling
Perform statistical profiling with Glue DataBrew, and detect sensitive data (PII classification) using Amazon Macie.

In summary, the following capabilities are not fully provided by Glue alone and must be complemented:

Business metadata management
Advanced data lineage
Data quality
Data discovery
Workflow management
Data governance
Data profiling

The question then becomes how to realize these capabilities, which leads to combining multiple AWS services.

Complementing Glue Data Catalog as a Data Catalog

As discussed earlier, Glue Data Catalog alone is not sufficient as a complete data catalog.
In AWS, this gap is addressed by combining multiple services to complement its capabilities.

Here, we organize which capabilities are complemented by which services.

Category	AWS Service	Description
Business Metadata	Amazon DataZone	Business glossary, data ownership definition, rich descriptions and context, data asset reviews and ratings
Data Lineage	Amazon DataZone	Lineage visualization, understanding data transformation flows, dependency management (end-to-end lineage requires OpenLineage or AWS Lineage APIs)
Data Quality	AWS Glue Data Quality / DataBrew	Data quality rule definition, scoring, anomaly detection, profiling (Glue Data Quality can auto-generate rules based on profiling results from DataBrew)
Data Discovery	Amazon DataZone / Amazon Q	Filtering, recommendations, related data suggestions, natural language search, AI-assisted analysis and insight generation
Workflow Management	AWS Step Functions / Amazon MWAA (Airflow) / Glue Workflows	Workflow orchestration
Data Governance	AWS Lake Formation	Column/row-level access control, tag-based access control, permissions management, data filtering
Data Profiling	AWS Glue DataBrew / Amazon Macie	Profiling, statistical analysis, sensitive data detection, PII classification

As shown above, AWS enables a data catalog by combining multiple services with Glue Data Catalog at the core.

Data Catalog Architecture

A data catalog on AWS, centered around Glue Data Catalog, can be organized into the following layered structure.

The key point is to view this not as individual services, but as an architecture composed of layers.

┌──────────────────────────────┐
│   Business Catalog Layer      │   ← Amazon DataZone / Amazon Q
│   (Discovery / Glossary)      │
└──────────────┬───────────────┘
               │
┌──────────────┼───────────────┐
│ Governance / Quality Layer   │   ← Lake Formation / Glue Data Quality
│ (Access Control / Quality)   │
└──────────────┬───────────────┘
               │
┌──────────────┼───────────────┐
│ Metadata Core Layer          │   ← Glue Data Catalog
│ (Technical Metadata)         │
└──────────────┬───────────────┘
               │
┌──────────────┼───────────────┐
│ Processing / Query Layer     │   ← Athena / Glue ETL / Redshift
│ (Query / ETL Processing)     │
└──────────────┬───────────────┘
               │
┌──────────────────────────────┐
│ Data Layer (S3)              │   ← Raw / Curated Data
└──────────────────────────────┘

Roles of Each Layer

Business Catalog Layer

Amazon DataZone: Entry point for business users to discover data, review it, and request access
Amazon Q: AI assistant that supports natural language search, data analysis, and insight generation (e.g., “Where is the sales data for 2023?”)

Governance / Quality Layer

Lake Formation: Column- and row-level access control, tag-based permission management
Glue Data Quality: Definition and validation of data quality rules (e.g., “Check that the age column does not contain negative values”)

Metadata Core Layer

Glue Data Catalog: Centralized management of technical metadata (schemas, statistics, partitions)
Integration with S3: Automatically catalogs file structures in the data lake

Processing / Query Layer

Athena / Redshift Spectrum: Query data directly on S3 using Glue Data Catalog
Glue ETL: Executes transformation jobs based on metadata from the catalog

Data Layer

S3: Stores raw data (CSV, Parquet, etc.) and processed (curated) data

Implementation Best Practices

1. Use Glue Data Catalog as the foundation

Place it at the center since it integrates natively with services like Athena, Glue, and Redshift

2. Add a business-facing layer

Introduce Amazon DataZone and build a business glossary
Define data ownership and utilize review/rating features

3. Strengthen data quality and governance

Define rules with Glue Data Quality (e.g., “Order amount must not be negative”)
Apply access control with Lake Formation (e.g., “Finance team can only view accounting data”) Note: DataZone also supports IAM integration and access control independently

4. Visualize data lineage

Use OpenLineage specifications to automatically capture input/output of Glue ETL jobs
Visualize lineage graphs in Amazon DataZone

5. Enable profiling and sensitive data detection

Use DataBrew for profiling (e.g., distribution analysis of columns)
Use Amazon Macie for detecting and classifying PII

6. Improve search experience

Integrate Amazon Q to enable natural language search (e.g., “Customer purchase history”)

Challenges of Adopting DataZone

Among the complementary services, one stands out as particularly important—but also challenging to adopt: Amazon DataZone.

In DataZone, data assets are managed as data products.
A data product represents a meaningful unit of business data (e.g., “Customer transaction data”) with clearly defined ownership and responsibility.

This structure clarifies who owns the data, forming the foundation for data quality and governance.
It also aligns well with Data Mesh principles, enabling domain-oriented data management.

DataZone provides what Glue lacks: a catalog for humans.

Data asset cataloging
Search and discovery
Lineage visualization
Data quality visibility
Governance management

In other words, it extends the technical catalog into a business catalog.

While Glue Data Catalog is a “catalog for systems,” DataZone is a “catalog for people.”

However, adopting DataZone requires meeting organizational, operational, and technical prerequisites.

1. Organizational Prerequisites

This is often the most difficult part.

Data Domain Design

Data domains—logical groupings of business data with clear ownership—must be defined.

Since DataZone manages data at the domain level, unclear boundaries make operations unsustainable.
In reality, many organizations have not formalized domain design, making this the first major challenge.

Data Ownership

Each data asset must have a clearly defined owner.

Data is treated as a “data product,” and each domain is responsible for managing its own data.
However, in many organizations, ownership is ambiguous or fragmented.

Responsibility Definition

Responsibilities for data quality, access control, and updates must be defined.
This forms the basis for governance and approval workflows.

In practice, aligning responsibilities across departments often becomes a bottleneck.

2. Operational Prerequisites

Approval Workflows

Processes for requesting and approving data access must be established.

Data Classification

Standardized classification rules based on sensitivity and usage are required.

Usage Policies

Guidelines and compliance rules for data usage must be clearly defined.

3. Technical Prerequisites

Lineage Collection

DataZone visualizes lineage, but only if lineage data exists.

This requires:

Integration with processing systems (Glue, Redshift, etc.)
Adoption of standards like OpenLineage
Designing metadata collection within pipelines

Metadata Integration

Metadata from various services must be integrated into DataZone:

Catalog integration (Glue / Redshift / S3)
Data quality metadata (Glue Data Quality)
Access control metadata (Lake Formation)

This integration enables a consistent data catalog experience.

In summary, DataZone does not automatically solve data governance problems.
It requires the following conditions:

A data-driven culture is emerging
Cross-functional collaboration exists
Awareness of data quality is high
Continuous improvement processes are in place

Without these, the catalog risks becoming a formality that is not actually used.

A Practical Approach to Adopting DataZone

Given the complexity, a phased approach is often effective.

Phase 1: Foundation (Data Platform Team)

Establish technical metadata with Glue Data Catalog
Basic data classification
Simple access control

Phase 2: Governance (Involving Governance Teams)

Implement fine-grained access control with Lake Formation
- Example: Apply classification tags at the column level and deny SELECT access to PII-tagged columns via IAM policies
Introduce data quality monitoring
Establish basic lineage

Phase 3: DataZone (Business-Led)

Introduce once organizational prerequisites are met
Manage business metadata
Enable self-service analytics

DataZone becomes effective only when the organization reaches a certain level of maturity.

It is not just a tool, but a mechanism for organizational transformation.
Technical readiness alone is not sufficient—cultural and process changes are required.

Considering OpenMetadata

OpenMetadata is an open-source data catalog that supports a wide range of platforms.
https://open-metadata.org/

It provides:

Connectors for various data sources
Data lineage
Data quality management
Search and UI capabilities

It can function as a comprehensive data catalog on its own.

This raises the question: why not use OpenMetadata from the start?

The answer depends on your context—specifically, which layer you want to implement the catalog in (infrastructure-oriented vs. business-oriented).

In AWS-centric environments, Glue Data Catalog integrates natively with services like Athena, Redshift, and Glue, providing consistency and operational simplicity.

Therefore, if your architecture is primarily within AWS, it is reasonable to center your design around Glue.

On the other hand, OpenMetadata becomes advantageous when:

Managing across multiple clouds (AWS / GCP / Azure)
Integrating diverse sources (SaaS, on-premises, etc.)
Requiring flexible and customizable metadata management
Designing a business-centric catalog from the beginning

However, adopting OpenMetadata requires:

Infrastructure setup (ECS/EKS or VMs, metadata storage such as PostgreSQL)
Operations (monitoring with Prometheus/Grafana, scaling, upgrades)
Security design (RBAC, authentication/authorization, encryption)

Compared to AWS managed services, it offers flexibility at the cost of increased operational overhead.

In summary:

Situation	Recommendation
AWS only / small scale	Glue Data Catalog centered
AWS + governance needs	+ Lake Formation
Advanced data usage	+ DataZone
Multi-cloud	Consider OpenMetadata

OpenMetadata can also integrate with Glue Data Catalog as a catalog provider.
Its Glue connector can ingest metadata from Glue and map it to OpenMetadata constructs such as glossaries and data products.

Conclusion

In this article, we explored data catalogs centered around AWS Glue Data Catalog.

A data catalog is essential to prevent a data lake from becoming a data swamp.

What matters is not the tool itself, but:

How metadata is managed
Who owns the data
How it continues to be used

This requires designing not only technology, but also organization and processes.

In other words, a data catalog is not just a tool—it is an organizational system.

Glue Data Catalog plays a central role, but it cannot form a complete data catalog on its own.

On AWS, a data catalog should be designed not as a single service, but as an architecture centered around Glue Data Catalog.

And most importantly, a data catalog should be viewed not merely as a technical foundation, but as a foundation for enabling organizations to operate with data.

I hope this article helps those considering data catalogs on AWS.

Boosting Lightweight ETL on AWS Lambda & Glue Python Shell with DuckDB and Apache Arrow Dataset

Aki — Fri, 06 Mar 2026 00:32:32 +0000

Original Japanese article: AWS Lambda/Glue Python Shell×DuckDBの軽量ETLをApache Arrow Datasetで高速化してみた

Introduction

I'm Aki, an AWS Community Builder (@jitepengin).

In my previous articles, I introduced lightweight ETL using AWS Lambda and Glue Python Shell.
In the process, I found that DuckDB's performance was not as high as expected:

Does Increasing AWS Lambda Memory to 10GB Really Make It Faster? (AWS Lambda chDB/DuckDB PyIceberg Benchmark)
AWS Lambda and AWS Glue Python Shell in the Context of Lightweight ETL

In this article, I will cover what became the bottleneck for DuckDB and how using Apache Arrow Dataset can improve performance, along with the trade-offs observed.

Recap of Previous Articles

Does Increasing AWS Lambda Memory to 10GB Really Make It Faster? (AWS Lambda chDB/DuckDB PyIceberg Benchmark)
AWS Lambda and AWS Glue Python Shell in the Context of Lightweight ETL

Using NYC taxi data, we compared performance on the same file:
data.page]https://www.nyc.gov/site/tlc/about/tlc-trip-record-data.page

Test files:

January 2024 Yellow Taxi Trip Records (2,964,624 records, 48MB)
Full-year 2024 Yellow Taxi Trip Records (41,169,720 records, 807MB)

Lambda measurements were taken with memory configurations of 1024MB, 2048MB, and the maximum 3008MB (without quota increase).
Glue Python Shell tests were performed with DPU settings of 1/16 and 1.

Since memory usage cannot be directly compared, we focus only on execution time.

48MB File (1 Month)

Execution Platform	Resource Setting	chDB Time (s)	DuckDB Time (s)
Glue Python Shell	1/16 DPU	46.000	40.000
Glue Python Shell	1 DPU	39.000	34.000
AWS Lambda	1024 MB	5.092	5.163
AWS Lambda	2048 MB	3.873	4.265
AWS Lambda	3008 MB	3.370	4.061

807MB File (1 Year)

Execution Platform	Resource Setting	chDB Time (s)	DuckDB Time (s)
Glue Python Shell	1/16 DPU	OutOfMemory	OutOfMemory
Glue Python Shell	1 DPU	51.0	212.0
AWS Lambda	1024 MB	OutOfMemory	OutOfMemory
AWS Lambda	2048 MB	OutOfMemory	OutOfMemory
AWS Lambda	3008 MB	27.171	187.332

What Caused the DuckDB Bottleneck?

When loading Parquet directly into DuckDB, the flow is typically:

S3
↓
DuckDB read_parquet
↓
Filter / Query

Key points:

S3 Scan: Reading the entire dataset involves heavy network I/O — this can take most of the time.
Parquet Decode: Decoding inside DuckDB adds CPU load.
Query Processing: For simple filters like WHERE VendorID = 1, query time is minimal.

Even if the query itself is light, S3 scanning becomes the bottleneck, lowering DuckDB’s standalone performance.
Measurements showed that in Glue Python Shell, of the total 210 seconds, 176 seconds (~83%) were spent in S3 Scan + Parquet Decode.

Solution: Use Apache Arrow Dataset to separate reading from querying and improve performance.

What is Apache Arrow Dataset?

https://arrow.apache.org/docs/python/dataset.html

Apache Arrow Dataset is a library for efficiently reading Parquet or CSV files using the columnar Arrow in-memory format.

Features:

Fast Parquet reading
Efficient decode operations
Parallelized S3 reads
Filter/Projection pushdown to reduce I/O

By leveraging these features, the S3 Scan + Parquet Decode bottleneck can be greatly reduced.

Architecture Overview

AWS Lambda

Same architecture as in previous articles:

Glue Python Shell

Also same as before:

Sample Code (AWS Lambda)

import duckdb
import pyarrow.dataset as ds
import pyarrow.fs as fs
import pyarrow as pa
import boto3
from pyiceberg.catalog.glue import GlueCatalog

def lambda_handler(event, context):
    try:

        # DuckDB setup in Lambda
        duckdb_connection = duckdb.connect(database=':memory:')

        # Retrieve S3 path from event
        s3_bucket = event['Records'][0]['s3']['bucket']['name']
        s3_object_key = event['Records'][0]['s3']['object']['key']
        s3_input_path = f"{s3_bucket}/{s3_object_key}"

        print(f"S3 input path: {s3_input_path}")

        # Read Parquet from S3 using Arrow Dataset
        # Use boto3 session to get temporary credentials
        session = boto3.Session()
        credentials = session.get_credentials().get_frozen_credentials()

        s3 = fs.S3FileSystem(
            region="ap-northeast-1",
            access_key=credentials.access_key,
            secret_key=credentials.secret_key,
            session_token=credentials.token
        )

        # Load dataset with Arrow Dataset
        dataset = ds.dataset(
            s3_input_path,
            filesystem=s3,
            format="parquet"
        )

        # Convert dataset to Arrow Table (in-memory)
        arrow_table = dataset.to_table()
        print(f"Number of rows retrieved: {arrow_table.num_rows}")
        print(f"Schema: {arrow_table.schema}")

        # DuckDB processing (SQL query)
        # Use DuckDB from_arrow to run SQL on Arrow 
        rel = duckdb_connection.from_arrow(arrow_table)
        result_arrow_table = duckdb_connection.execute(
            """
            SELECT * 
            FROM rel
            WHERE VendorID = 1
            """
        ).fetch_arrow_table()

        # Configure Glue Catalog (to access Iceberg table)
        catalog = GlueCatalog(region_name="ap-northeast-1", database="icebergdb", name="my_catalog")  # Adjust to your environment.

        # Load the table
        namespace = "icebergdb"  # Adjust to your environment.
        table_name = "yellow_tripdata"  # Adjust to your environment.
        iceberg_table = catalog.load_table(f"{namespace}.{table_name}")

        # Append data to the Iceberg table in bulk
        iceberg_table.append(result_arrow_table)

        print("Data has been appended to S3 in Iceberg format.")

    except Exception as e:
        print(f"An error occurred: {e}")

Note: This article focuses on the differences in operation, so version updates or conflict handling in Iceberg tables are omitted.

Key points:

Arrow Dataset separates S3 reading from DuckDB querying
Light filters can be applied in Arrow Dataset alone for speed

Sample Code (Glue Python Shell)

import boto3
import sys
import os
import pyarrow.dataset as ds
import pyarrow.fs as fs
from awsglue.utils import getResolvedOptions


def get_job_parameters():
    args = getResolvedOptions(sys.argv, ['s3_input'])
    s3_path = args['s3_input']
    print(f"object: {s3_path}")
    return s3_path


def setup_duckdb_environment():
    try:
        duckdb_dir = '/tmp/.duckdb'
        os.environ['HOME'] = '/tmp'
        os.makedirs(duckdb_dir, exist_ok=True)
        print(f"DuckDB environment setup completed: {duckdb_dir}")
        return True
    except Exception as e:
        print(f"DuckDB environment setup error: {e}")
        return False


def read_parquet_with_arrow_dataset(s3_input):

    print("Reading with Arrow Dataset...")

    session = boto3.Session()
    credentials = session.get_credentials().get_frozen_credentials()

    s3 = fs.S3FileSystem(
        region="ap-northeast-1",
        access_key=credentials.access_key,
        secret_key=credentials.secret_key,
        session_token=credentials.token
    )

    path = s3_input.replace("s3://", "")

    dataset = ds.dataset(
        path,
        filesystem=s3,
        format="parquet"
    )

    table = dataset.to_table()

    print(f"Arrow read rows: {table.num_rows}")

    return table


def process_with_duckdb(arrow_table):

    import duckdb

    con = duckdb.connect(":memory:")

    try:

        rel = duckdb.from_arrow(arrow_table)

        result = con.execute("""
            SELECT *
            FROM rel
            WHERE VendorID = 1
        """).arrow()

        print(f"DuckDB filtered rows: {result.num_rows}")

        return result

    finally:
        con.close()


def write_iceberg_table(arrow_table):

    try:

        print("Writing started...")

        from pyiceberg.catalog import load_catalog

        catalog_config = {
            "type": "glue",
            "warehouse": "s3://your-bucket/your-warehouse/",
            "region": "ap-northeast-1"
        }

        catalog = load_catalog("glue_catalog", **catalog_config)

        table_identifier = "icebergdb.yellow_tripdata"

        table = catalog.load_table(table_identifier)

        print(f"Target data to write: {arrow_table.num_rows:,} rows")

        table.append(arrow_table)

        return True

    except Exception as e:

        print(f"Writing error: {e}")

        import traceback
        traceback.print_exc()

        return False


def main():

    if not setup_duckdb_environment():
        print("Failed to set up DuckDB environment")
        return

    try:

        s3_input = get_job_parameters()

        # Arrow Dataset read
        arrow_tbl = read_parquet_with_arrow_dataset(s3_input)

        # DuckDB SQL filter
        result_tbl = process_with_duckdb(arrow_tbl)

        # Iceberg write
        if write_iceberg_table(result_tbl):
            print("Writing fully successful!")
        else:
            print("Writing failed")

    except Exception as e:

        print(f"Main error: {e}")

        import traceback
        traceback.print_exc()


if __name__ == "__main__":
    main()

Note: This article focuses on the differences in operation, so version updates or conflict handling in Iceberg tables are omitted.

Key points:

Glue Python Shell can execute ETL in the same Lambda-style configuration
Responsibilities separated: Arrow Dataset for reading, DuckDB for SQL query
Lightweight filters can be processed efficiently

Benchmarking

Using the same dataset and queries as in previous articles:

AWS Lambda

48MB File (1 Month) memory=3008MB

Method	Time (ms)	Memory (MB)
chDB	3,369.78	1115
DuckDB	4,061.33	524
DuckDB × Arrow Dataset	3,591.84	928

807MB File (1 Year) memory=10240MB

Method	Time (ms)	Memory (MB)
chDB	22,839.18	3490
DuckDB	189,678.02	2788
DuckDB × Arrow Dataset	15,220.6	8086

Glue Python Shell (1 DPU)

48MB File (1 Month)

Method	Time (s)
chDB	39
DuckDB	34
DuckDB × Arrow Dataset	32

807MB File (1 Year)

Method	Time (s)
chDB	51
DuckDB	212
DuckDB × Arrow Dataset	44

As a result, both AWS Lambda and Glue Python Shell were able to achieve significant performance improvements compared to chDB.
In other words, addressing the S3 scan and Parquet decoding bottlenecks seems to be the key to improving DuckDB processing.

However, in the case of Lambda, large file sizes can lead to high memory usage, potentially exceeding the memory limits.
This means that careful consideration of where and how to use this approach is necessary.

Memory Consideration with Arrow Dataset

dataset = ds.dataset(
    s3_input_path,
    filesystem=s3,
    format="parquet"
)

arrow_table = dataset.to_table()

In this process, dataset.to_table() materializes the entire dataset in memory as an Arrow Table.
Arrow Tables use a columnar in-memory format, which is very fast, but in this case, loading the entire file at once can result in high memory usage.

For example, reading an 807MB Parquet file in Lambda can cause the memory footprint of the Arrow Table to be much larger than the compressed Parquet file size.
While to_table() is convenient, it is important to be aware that it can significantly increase memory consumption depending on the processing.

Lambda can also use /tmp for disk-backed processing, but processing in memory is overwhelmingly faster.
However, due to memory limits, expanding a large file into an Arrow Table can quickly consume a large amount of memory.

For simple queries, one approach is to iterate over row groups instead of materializing the entire table in memory, processing small chunks at a time.
This method can potentially keep memory usage within a few hundred MBs.

Trade-Offs

Using Arrow Dataset significantly improves the speed of S3 reads and Parquet decoding.
However, expanding the dataset all at once with to_table() increases memory usage, which may hit Lambda’s memory limits.

Pros: Decoding and I/O are faster, resulting in improved performance.
Cons: Materializing the entire file consumes a lot of memory, and for large files, Lambda may run into OutOfMemory errors.

Therefore, it is important to design your ETL with a balance between performance and memory usage in mind.
For small files or when Lambda has sufficient memory, loading the full dataset at once is fine.
For larger files, consider chunked processing by row group or pushdown filters to keep memory usage under control.

Pushdown with Arrow Dataset

Using Arrow Dataset’s Filter/Projection Pushdown, you can load only the row groups you need from S3.

Here’s how you can apply it:

arrow_table = dataset.to_table(
    columns=["VendorID", "tpep_pickup_datetime"],
    filter=ds.field("VendorID") == 1
)

Only the necessary row groups are read (this is crucial for large datasets).
Reduces network I/O from S3.
Can further shorten processing time.
Arrow Dataset is optimal for lightweight reads and simple filters; complex queries should still be handled in DuckDB.

Experimenting in Lambda

You can integrate pushdown into your existing Lambda code. For example:

import duckdb
import pyarrow.dataset as ds
import pyarrow.fs as fs
import pyarrow as pa
import boto3
from pyiceberg.catalog.glue import GlueCatalog

def lambda_handler(event, context):
    try:

        # DuckDB setup in Lambda
        duckdb_connection = duckdb.connect(database=':memory:')

        # Retrieve S3 path from event
        s3_bucket = event['Records'][0]['s3']['bucket']['name']
        s3_object_key = event['Records'][0]['s3']['object']['key']
        s3_input_path = f"{s3_bucket}/{s3_object_key}"

        print(f"S3 input path: {s3_input_path}")

        # Read Parquet from S3 using Arrow Dataset
        # Use boto3 session to get temporary credentials
        session = boto3.Session()
        credentials = session.get_credentials().get_frozen_credentials()

        s3 = fs.S3FileSystem(
            region="ap-northeast-1",
            access_key=credentials.access_key,
            secret_key=credentials.secret_key,
            session_token=credentials.token
        )

        # Load dataset with Arrow Dataset
        dataset = ds.dataset(
            s3_input_path,
            filesystem=s3,
            format="parquet"
        )

        # Convert dataset to Arrow Table (in-memory)
        arrow_table = dataset.to_table(filter=ds.field("VendorID") == 1)
        print(f"Number of rows retrieved: {arrow_table.num_rows}")
        print(f"Schema: {arrow_table.schema}")

        # DuckDB processing (SQL query)
        # Use DuckDB from_arrow to run SQL on Arrow 
        rel = duckdb_connection.from_arrow(arrow_table)
        result_arrow_table = duckdb_connection.execute(
            """
            SELECT * 
            FROM rel
            """
        ).fetch_arrow_table()

        # Configure Glue Catalog (to access Iceberg table)
        catalog = GlueCatalog(region_name="ap-northeast-1", database="icebergdb", name="my_catalog")  # Adjust to your environment.

        # Load the table
        namespace = "icebergdb"  # Adjust to your environment.
        table_name = "yellow_tripdata"  # Adjust to your environment.
        iceberg_table = catalog.load_table(f"{namespace}.{table_name}")

        # Append data to the Iceberg table in bulk
        iceberg_table.append(result_arrow_table)

        print("Data has been appended to S3 in Iceberg format.")

    except Exception as e:
        print(f"An error occurred: {e}")

Using pushdown, memory usage was reduced significantly:

Method	Time (ms)	Memory (MB)
DuckDB × Arrow Dataset	15,220.6	8086
DuckDB × Arrow Dataset (Pushdown)	15,238.14	3458

The memory usage was significantly reduced with pushdown.
This two-step approach filtering unnecessary data with Arrow Dataset before passing it to DuckDB proves to be effective for large datasets.

Insights

DuckDB alone is slow due to S3 Scan + Parquet Decode
DuckDB shines with complex queries (JOIN, GROUP BY, WINDOW)
Pushdown is key in Arrow Dataset
Separating responsibilities (Arrow Dataset + DuckDB) enables efficient ETL in Lambda/Glue
chDB offers balanced memory and speed out-of-the-box

Responsibility-Separated Architecture

S3 Parquet (raw data)
      │
      ▼
Arrow Dataset → row group scan + simple filter
      │
      ▼
DuckDB → SQL query (JOIN, GROUP BY, Window functions)
      │
      ▼
PyIceberg → Iceberg table write

Conclusion

In this article, we explored performance improvements for a lightweight ETL built with AWS Lambda / Glue Python Shell × DuckDB × PyIceberg.

For lightweight ETL, especially on AWS Lambda, processing time is a critical factor. By using Apache Arrow Dataset, we were able to significantly improve performance by offloading S3 reading and Parquet decoding before running queries in DuckDB.

However, there are trade-offs. Expanding an entire dataset into memory with to_table() can lead to high memory usage, which may exceed Lambda’s limits for large files. Therefore, careful responsibility separation and chunked processing (e.g., row group iteration or pushdown filters) are important considerations.

With the architecture presented here, even large files can be processed quickly in a lightweight ETL on AWS. While complex queries may still face performance limitations, this approach provides a practical and efficient option for real-time or near-real-time ETL in a Lakehouse environment using Apache Iceberg.

We hope this article serves as a reference for those exploring lightweight data processing and ETL patterns on Iceberg tables.

Does Increasing AWS Lambda Memory to 10GB Really Make It Faster? (AWS Lambda chDB/DuckDB PyIceberg Benchmark)

Aki — Thu, 26 Feb 2026 13:33:33 +0000

Original Japanese article: AWS Lambdaを10GBにすると本当に速くなるのか？（AWS Lambda×chDB/DuckDB×PyIceberg検証）

Introduction

I'm Aki, an AWS Community Builder (@jitepengin).

In a previous article, I benchmarked Iceberg integration using AWS Lambda with DuckDB and chDB.

Lightweight ETL with AWS Lambda, chDB, and PyIceberg (Compared with DuckDB)

In that article, I tested two patterns on AWS Lambda:

chDB × PyIceberg
DuckDB × PyIceberg

Memory sizes were set to 1024 MB, 2048 MB, and 3008 MB (the maximum without quota increase at the time).

The results showed:

For small datasets, increasing memory generally improved performance.
For a large dataset (807 MB), 3008 MB was barely enough to complete processing.

This time, I extended the experiment:

What happens if we increase Lambda memory up to 10GB (10240 MB)?

Increasing the Lambda Memory Quota

To raise the Lambda memory limit beyond 3008 MB, you must request a quota increase.

Important:
You cannot increase Lambda memory from the Service Quotas console.

Steps

Go to the AWS Support Center and create a new case.
Clearly state:

The reason for the increase
The target region

Example request content:

We are building and validating a data processing platform using AWS Lambda.
The workload is memory-intensive, including large Parquet file loading, aggregation, and transformation.
The current 3008 MB limit is insufficient to complete processing.

We are performing analytical processing inside Lambda using columnar formats (Parquet), and the workload requires higher memory allocation.

Currently, we experience performance degradation and OutOfMemory errors.

We would like to request an increase of the Lambda memory limit in the Tokyo region to 10240 MB.

Although we considered migrating to other compute services, we determined that continuing with Lambda is the most appropriate option from both operational and architectural perspectives.

After submission, AWS responded in about 3 business days and applied the increase.

Architecture

The architecture is identical to the previous article.

Flow:

Load a Parquet file from S3 in Lambda
Process it using chDB or DuckDB
Write results into an Iceberg table

In short:

S3 → Lambda (chDB/DuckDB) → Iceberg (via Glue Catalog)

In this article, I focus on performance behavior differences.
Iceberg version conflicts and concurrency handling are omitted for simplicity.

Sample Code (chDB)

import chdb
import pyarrow as pa
from pyiceberg.catalog.glue import GlueCatalog


def _to_pyarrow_table(result):
    """
    Compatibility helper to extract a pyarrow.Table from a chDB query_result.
    """
    if hasattr(chdb, "to_arrowTable"):
        return chdb.to_arrowTable(result)

    if hasattr(result, "to_pyarrow"):
        return result.to_pyarrow()
    if hasattr(result, "to_arrow"):
        return result.to_arrow()

    raise RuntimeError(
        "Cannot convert chdb query_result to pyarrow.Table. "
        f"Available attributes: {sorted(dir(result))[:200]}"
    )


def normalize_arrow_for_iceberg(table: pa.Table) -> pa.Table:
    """
    Normalize Arrow types that Iceberg does not accept
    (mainly timezone-aware timestamps).
    """
    new_fields = []
    new_columns = []

    for field, column in zip(table.schema, table.columns):
        if pa.types.is_timestamp(field.type) and field.type.tz is not None:
            # Remove timezone information (values remain in UTC)
            new_type = pa.timestamp(field.type.unit)
            new_fields.append(pa.field(field.name, new_type, field.nullable))
            new_columns.append(column.cast(new_type))
        else:
            new_fields.append(field)
            new_columns.append(column)

    new_schema = pa.schema(new_fields)
    return pa.Table.from_arrays(new_columns, schema=new_schema)


def lambda_handler(event, context):
    try:
        # Extract S3 bucket and object key from the event
        s3_bucket = event['Records'][0]['s3']['bucket']['name']
        s3_object_key = event['Records'][0]['s3']['object']['key']

        # Build S3 HTTPS URL
        s3_url = (
            f"https://{s3_bucket}."
            f"s3.ap-northeast-1.amazonaws.com/"
            f"{s3_object_key}"
        )

        print(f"s3_url: {s3_url}")

        # Query Parquet data on S3 using chDB
        query = f"""
            SELECT *
            FROM s3('{s3_url}', 'Parquet')
            WHERE VendorID = 1
        """

        # Execute chDB query with Arrow output
        result = chdb.query(query, "Arrow")

        # Convert chDB result to pyarrow.Table
        arrow_table = _to_pyarrow_table(result)
        print(f"Original schema: {arrow_table.schema}")

        # Normalize schema for Iceberg compatibility
        arrow_table = normalize_arrow_for_iceberg(arrow_table)
        print(f"Normalized schema: {arrow_table.schema}")
        print(f"Rows: {arrow_table.num_rows}")

        # Initialize Iceberg Glue Catalog
        catalog = GlueCatalog(
            name="my_catalog",
            database="icebergdb",
            region_name="ap-northeast-1",
        )

        # Load Iceberg table
        iceberg_table = catalog.load_table("icebergdb.yellow_tripdata")

        # Append data to Iceberg table
        iceberg_table.append(arrow_table)

        print("Data appended to Iceberg table.")

    except Exception as e:
        print("Exception:", e)
        raise

Note: This article focuses on the differences in operation, so version updates or conflict handling in Iceberg tables are omitted.

Sample Code (DuckDB)

import duckdb
import pyarrow as pa
from pyiceberg.catalog.glue import GlueCatalog  

def lambda_handler(event, context):
    try:
        # Connect to DuckDB and set the home directory
        duckdb_connection = duckdb.connect(database=':memory:')
        duckdb_connection.execute("SET home_directory='/tmp'") 

        # Install and load the httpfs extension
        duckdb_connection.execute("INSTALL httpfs;")
        duckdb_connection.execute("LOAD httpfs;")

        # Load data from S3 using DuckDB
        s3_bucket = event['Records'][0]['s3']['bucket']['name']
        s3_object_key = event['Records'][0]['s3']['object']['key']

        s3_input_path = f"s3://{s3_bucket}/{s3_object_key}"

        print(f"s3_input_path: {s3_input_path}")

        query = f"""
            SELECT * FROM read_parquet('{s3_input_path}') WHERE VendorID = 1
        """
        # Execute SQL and retrieve results as a PyArrow Table
        result_arrow_table = duckdb_connection.execute(query).fetch_arrow_table()

        print(f"Number of rows retrieved: {result_arrow_table.num_rows}")
        print(f"Data schema: {result_arrow_table.schema}")

        # Configure Glue Catalog (to access Iceberg table)
        catalog = GlueCatalog(region_name="ap-northeast-1", database="icebergdb", name="my_catalog")  # Adjust to your environment.

        # Load the table
        namespace = "icebergdb"  # Adjust to your environment.
        table_name = "yellow_tripdata"  # Adjust to your environment.
        iceberg_table = catalog.load_table(f"{namespace}.{table_name}")

        # Append data to the Iceberg table in bulk
        iceberg_table.append(result_arrow_table) 

        print("Data has been appended to S3 in Iceberg format.")

    except Exception as e:
        print(f"An error occurred: {e}")

Note: Version updates and conflict handling are omitted here as well.

Test Conditions

Dataset:
NYC Taxi Trip Records
https://www.nyc.gov/site/tlc/about/tlc-trip-record-data.page

Test files:

January 2024 (Yellow Taxi)
- 2,964,624 records
- 48 MB
Full year 2024 (aggregated file)
- 41,169,720 records
- 807 MB

Memory configurations tested:

1024 MB
2048 MB
3008 MB (no quota increase maximum)
4096 MB
10240 MB

Each configuration was executed 5 times under warm conditions to eliminate cold start effects.

Results

48MB File (1 Month)

Memory (MB)	chDB Time (ms)	chDB Memory Used (MB)	DuckDB Time (ms)	DuckDB Memory Used (MB)
1024	5,092	1018	5,163	512
2048	3,872	1132	4,264	538
3008	3,369	1115	4,061	524
4096	3,197	1263	3,547	568
10240	3,087	1255	3,484	554

Performance improved as memory increased — but only marginally above 4096 MB.

Actual memory usage:

chDB ≈ 1.2 GB
DuckDB ≈ 550 MB

Allocated memory increased, but real usage did not scale proportionally.

807MB File (1 Year)

Memory (MB)	chDB Time (ms)	chDB Memory Used (MB)	DuckDB Time (ms)	DuckDB Memory Used (MB)
1024	OOM	-	OOM	-
2048	OOM	-	OOM	-
3008	27,170	3001	187,331	2732
4096	24,631	3322	188,880	2767
10240	22,839	3490	189,678	2788

OOM occurred because the memory allocation could not hold the temporary buffers required during the Parquet → Arrow → Iceberg transformation.

Did Performance Really Improve at 4096MB and 10GB?

What we really wanted to check in this experiment was whether performance would actually improve once memory goes beyond 3008 MB.

48MB (1-month data)

Increasing memory from 3008 → 4096 → 10240 MB resulted in:

chDB: 3,369 → 3,197 → 3,087 ms
DuckDB: 4,061 → 3,547 → 3,484 ms

Performance did improve, but the gains were limited.
In particular, the difference from 4096 → 10240 MB is almost negligible, within the margin of error.

Looking at Max Memory Used, chDB only used about 1.2 GB and DuckDB about 550 MB, meaning increasing allocated memory did not increase actual usage.

807MB (1-year data)

chDB:

3008 MB: 27,170 ms
4096 MB: 24,631 ms
10240 MB: 22,839 ms

Overall, going from 3008 → 10240 MB improved performance by roughly 16%,
but from 4096 → 10240 MB, the improvement was only about 7%.

Even though memory increased roughly 3.4×, performance only improved by ~16%, suggesting that performance is hitting a ceiling.

DuckDB:

3008 MB: 187,331 ms
4096 MB: 188,881 ms
10240 MB: 189,678 ms

Almost no improvement; in some cases, it was slightly slower.
Simply increasing memory does not affect execution time.

Analysis (Bottleneck Insights)

Memory behaves as a threshold parameter

For Lambda × DuckDB/chDB, memory seems to behave more like a threshold than a proportional scaling parameter.

1024 MB and 2048 MB → OOM
3008 MB → first point where processing succeeds

Beyond that, adding memory does not yield proportional performance gains.
This suggests the bottleneck is likely elsewhere, not just compute resources.

Does increasing vCPU help?

Lambda increases available CPU with memory.
However:

DuckDB barely scaled
chDB only slightly improved

Likely bottlenecks are I/O and serialization, such as:

Reading from S3

Iceberg metadata operations

Parquet → Arrow conversion

Engine-specific behavior

chDB: small improvements with more memory
DuckDB: almost no change

This difference may be due to internal implementations or parallelization strategies.
At least for this workload, simply going to 10 GB does not make DuckDB explode in speed.

Key Takeaways for Large Workloads on Lambda

From this experiment, a few points are clear:

Crossing the OOM threshold is the main goal.
Memory beyond that should be considered carefully, especially for cost.
Simply allocating 10 GB does not guarantee faster execution.

Looking at DuckDB results, it's clear that maxing out memory does not automatically make things faster.
From a cost perspective, finding the “just enough” memory is more practical.

For more complex or larger workloads, sticking to Lambda may not be optimal — Glue or EMR could be faster and more stable.

Conclusion

In this article, we walked through applying for a Lambda memory quota increase and measured the performance of lightweight ETL tasks with the expanded memory.

Both chDB and DuckDB are attractive open-source options, but they have significantly different characteristics. One clear takeaway is that crossing the OOM threshold should always be the first goal; beyond that, performance improvements will likely need to come from areas other than memory.

This experiment reinforced that designing for maximum memory by default is not necessarily the best approach. It's more important to understand your workload and identify the critical memory boundaries.

Also, keep in mind that Lambda quota increases cannot be requested from the Service Quotas screen, which can be useful knowledge in both personal projects and professional settings.

While both engines are still evolving, understanding their characteristics and using them appropriately allows you to build simple, yet highly extensible data processing workflows.

I hope this article serves as a helpful reference for anyone considering lightweight data processing or real-time ETL with Iceberg tables.

S3 Triggers: How to Launch Glue Python Shell via AWS Lambda

Aki — Sat, 31 Jan 2026 14:20:35 +0000

Original Japanese article: S3トリガー×AWS Lambda×Glue Python Shellの起動パターン整理

Introduction

I'm Aki, an AWS Community Builder (@jitepengin).

In my previous articles, I introduced lightweight ETL using Glue Python Shell.
In this article, I’ll organize two patterns for triggering Glue Python Shell when a file is placed in S3, and explain the reasoning behind calling Glue via Lambda.

Why Use Lambda to Trigger Glue Python Shell

While it is possible to implement ETL processing entirely within Lambda triggered by S3 events, there are limitations in runtime and memory.
By using Lambda as a trigger and delegating lightweight preprocessing or integration with other services to Lambda while executing the main ETL in Glue Python Shell, you can achieve flexible service integration and long-running processing.

For more details on when to use Lambda vs Glue Python Shell, check out my previous article:
AWS Lambda and AWS Glue Python Shell in the Context of Lightweight ETL

Trigger Patterns

The two patterns covered in this article are:

Lambda + start_job_run (Direct Job Execution)
Lambda + start_workflow_run (Workflow Execution)

Other patterns exist, such as S3 → EventBridge → Step Functions → Glue Python Shell, but we’ll focus on these two simpler approaches.

Pattern 1: Lambda + `start_job_run` (Direct Job Execution)

In this pattern, Lambda receives the S3 file placement event and directly triggers a Glue Python Shell job using start_job_run.
This was the setup used in my previous article.

Characteristics:

High flexibility: Lambda can integrate with many services.
Error handling and retries need consideration. Glue Python Shell allows job retries, but trigger retries must be implemented in Lambda.
Flow control can get complex if needed (may require EventBridge → Step Functions → Lambda setup).
Lambda alone might suffice in simple cases; choose between flexible Lambda and long-running Glue Python Shell.

Lambda Sample Code

Set the target Job name and parameters for start_job_run. Here we pass the S3 file path.

import boto3

def lambda_handler(event, context):
    glue = boto3.client("glue")

    s3_bucket = event['Records'][0]['s3']['bucket']['name']
    s3_object_key = event['Records'][0]['s3']['object']['key']

    s3_input_path = f"s3://{s3_bucket}/{s3_object_key}"

    response = glue.start_job_run(
        JobName="YOUR_TARGET_JOB_NAME",
        Arguments={
            "--s3_input": s3_input_path
        }
    )

    print(f"Glue Job started: {response['JobRunId']}")
    return response

Setup Steps

Create a Glue Python Shell job.

import boto3
import sys
import os
from awsglue.utils import getResolvedOptions


def get_job_parameters():
    try:
        required_args = ['s3_input']
        args = getResolvedOptions(sys.argv, required_args)

        s3_file_path = args['s3_input']
        print(f"s3_input: {s3_file_path}")

        return s3_file_path

    except Exception as e:
        print(f"parameters error: {e}")
        raise


def _to_pyarrow_table(result):
    """
    Compatibility helper to extract a pyarrow.Table from a chDB query_result.
    """
    import chdb

    if hasattr(chdb, "to_arrowTable"):
        return chdb.to_arrowTable(result)

    if hasattr(result, "to_pyarrow"):
        return result.to_pyarrow()
    if hasattr(result, "to_arrow"):
        return result.to_arrow()

    raise RuntimeError(
        "Cannot convert chdb query_result to pyarrow.Table. "
        f"Available attributes: {sorted(dir(result))[:200]}"
    )


def normalize_arrow_for_iceberg(table):
    """
    Normalize Arrow schema for Iceberg compatibility.
    - timestamptz -> timestamp
    """
    import pyarrow as pa

    new_fields = []
    new_columns = []

    for field, column in zip(table.schema, table.columns):
        # timestamp with timezone -> timestamp
        if pa.types.is_timestamp(field.type) and field.type.tz is not None:
            new_type = pa.timestamp(field.type.unit)
            new_fields.append(pa.field(field.name, new_type, field.nullable))
            new_columns.append(column.cast(new_type))
        else:
            new_fields.append(field)
            new_columns.append(column)

    new_schema = pa.schema(new_fields)
    return pa.Table.from_arrays(new_columns, schema=new_schema)


def read_parquet_with_chdb(s3_input):
    """
    Read Parquet file from S3 using chDB.
    """
    import chdb

    if s3_input.startswith("s3://"):
        bucket, key = s3_input.replace("s3://", "").split("/", 1)
        s3_url = f"https://{bucket}.s3.ap-northeast-1.amazonaws.com/{key}"
    else:
        s3_url = s3_input

    print(f"Reading data from S3: {s3_url}")

    query = f"""
        SELECT *
        FROM s3('{s3_url}', 'Parquet')
        WHERE VendorID = 1
    """

    result = chdb.query(query, "Arrow")
    arrow_table = _to_pyarrow_table(result)

    print("Original schema:")
    print(arrow_table.schema)

    # Normalize schema for Iceberg compatibility
    arrow_table = normalize_arrow_for_iceberg(arrow_table)

    print("Normalized schema:")
    print(arrow_table.schema)
    print(f"Rows: {arrow_table.num_rows:,}")

    return arrow_table


def write_iceberg_table(arrow_table):
    """
    Write Arrow table to Iceberg table using PyIceberg.
    """
    try:
        print("Writing started...")

        from pyiceberg.catalog import load_catalog

        catalog_config = {
            "type": "glue",
            "warehouse": "s3://your-bucket/your-warehouse/",  # Adjust to your environment.
            "region": "ap-northeast-1",
        }

        catalog = load_catalog("glue_catalog", **catalog_config)
        table_identifier = "icebergdb.yellow_tripdata"

        table = catalog.load_table(table_identifier)

        print(f"Target data to write: {arrow_table.num_rows:,} rows")
        table.append(arrow_table)

        return True

    except Exception as e:
        print(f"Writing error: {e}")
        import traceback
        traceback.print_exc()
        return False


def main():
    try:
        import chdb
        import pyiceberg

        # Read input parameter
        s3_input = get_job_parameters()

        # Read data with chDB
        arrow_tbl = read_parquet_with_chdb(s3_input)
        print(f"Data read success: {arrow_tbl.num_rows:,} rows")

        # Write to Iceberg table
        if write_iceberg_table(arrow_tbl):
            print("\nWriting fully successful!")
        else:
            print("Writing failed")

    except Exception as e:
        print(f"Main error: {e}")
        import traceback
        traceback.print_exc()


if __name__ == "__main__":
    main()

Create a Lambda function and paste the sample code above.
Set an S3 trigger for PUT events.

Execution Result

Pattern 2: Lambda + `start_workflow_run` (Workflow Execution)

In this pattern, Lambda receives the S3 file event and triggers a Glue Workflow using start_workflow_run.
The Workflow then runs the Glue Python Shell jobs.

Characteristics:

Combines Lambda flexibility with Glue Workflow flow control.
Error handling requires clear responsibility division between Lambda and Workflow.
Workflows allow preprocessing and postprocessing to be added later.

Lambda Sample Code

import boto3

def lambda_handler(event, context):
    glue = boto3.client("glue")

    s3_bucket = event['Records'][0]['s3']['bucket']['name']
    s3_object_key = event['Records'][0]['s3']['object']['key']

    s3_input_path = f"s3://{s3_bucket}/{s3_object_key}"

    response = glue.start_workflow_run(
        Name="YOUR_TARGET_WORKFLOW_NAME",
        RunProperties={'--s3_input': s3_input_path}
    )

    print(f"Glue Workflow started: {response['RunId']}")
    return response

Setup Steps

Create Glue Python Shell jobs.

1st Job

import boto3
import sys
import os
from awsglue.utils import getResolvedOptions


def get_job_parameters():
    args = getResolvedOptions(sys.argv, ['WORKFLOW_NAME', 'WORKFLOW_RUN_ID'])

    glue = boto3.client('glue')
    resp = glue.get_workflow_run_properties(
        Name=args['WORKFLOW_NAME'],
        RunId=args['WORKFLOW_RUN_ID']
    )

    s3_input = resp['RunProperties'].get('s3_input')
    if not s3_input:
        raise ValueError("s3_input Not Found")

    print(f"s3_input: {s3_input}")
    return s3_input

def _to_pyarrow_table(result):
    """
    Compatibility helper to extract a pyarrow.Table from a chDB query_result.
    """
    import chdb

    if hasattr(chdb, "to_arrowTable"):
        return chdb.to_arrowTable(result)

    if hasattr(result, "to_pyarrow"):
        return result.to_pyarrow()
    if hasattr(result, "to_arrow"):
        return result.to_arrow()

    raise RuntimeError(
        "Cannot convert chdb query_result to pyarrow.Table. "
        f"Available attributes: {sorted(dir(result))[:200]}"
    )


def normalize_arrow_for_iceberg(table):
    """
    Normalize Arrow schema for Iceberg compatibility.
    - timestamptz -> timestamp
    - binary -> string
    """
    import pyarrow as pa

    new_fields = []
    new_columns = []

    for field, column in zip(table.schema, table.columns):
        # timestamp with timezone -> timestamp
        if pa.types.is_timestamp(field.type) and field.type.tz is not None:
            new_type = pa.timestamp(field.type.unit)
            new_fields.append(pa.field(field.name, new_type, field.nullable))
            new_columns.append(column.cast(new_type))
        else:
            new_fields.append(field)
            new_columns.append(column)

    new_schema = pa.schema(new_fields)
    return pa.Table.from_arrays(new_columns, schema=new_schema)


def read_parquet_with_chdb(s3_input):
    """
    Read Parquet file from S3 using chDB.
    """
    import chdb

    if s3_input.startswith("s3://"):
        bucket, key = s3_input.replace("s3://", "").split("/", 1)
        s3_url = f"https://{bucket}.s3.ap-northeast-1.amazonaws.com/{key}"
    else:
        s3_url = s3_input

    print(f"Reading data from S3: {s3_url}")

    query = f"""
        SELECT *
        FROM s3('{s3_url}', 'Parquet')
        WHERE VendorID = 1
    """

    result = chdb.query(query, "Arrow")
    arrow_table = _to_pyarrow_table(result)

    print("Original schema:")
    print(arrow_table.schema)

    # Normalize schema for Iceberg compatibility
    arrow_table = normalize_arrow_for_iceberg(arrow_table)

    print("Normalized schema:")
    print(arrow_table.schema)
    print(f"Rows: {arrow_table.num_rows:,}")

    return arrow_table


def write_iceberg_table(arrow_table):
    """
    Write Arrow table to Iceberg table using PyIceberg.
    """
    try:
        print("Writing started...")

        from pyiceberg.catalog import load_catalog

        catalog_config = {
            "type": "glue",
            "warehouse": "s3://your-bucket/your-warehouse/",  # Adjust to your environment.
            "region": "ap-northeast-1",
        }

        catalog = load_catalog("glue_catalog", **catalog_config)
        table_identifier = "icebergdb.yellow_tripdata"

        table = catalog.load_table(table_identifier)

        print(f"Target data to write: {arrow_table.num_rows:,} rows")
        table.append(arrow_table)

        return True

    except Exception as e:
        print(f"Writing error: {e}")
        import traceback
        traceback.print_exc()
        return False


def main():
    try:
        import chdb
        import pyiceberg

        # Read input parameter
        s3_input = get_job_parameters()

        # Read data with chDB
        arrow_tbl = read_parquet_with_chdb(s3_input)
        print(f"Data read success: {arrow_tbl.num_rows:,} rows")

        # Write to Iceberg table
        if write_iceberg_table(arrow_tbl):
            print("\nWriting fully successful!")
        else:
            print("Writing failed")

    except Exception as e:
        print(f"Main error: {e}")
        import traceback
        traceback.print_exc()


if __name__ == "__main__":
    main()

2nd Job

import boto3
import sys
import os
from awsglue.utils import getResolvedOptions


def get_job_parameters():
    args = getResolvedOptions(sys.argv, ['WORKFLOW_NAME', 'WORKFLOW_RUN_ID'])

    glue = boto3.client('glue')
    resp = glue.get_workflow_run_properties(
        Name=args['WORKFLOW_NAME'],
        RunId=args['WORKFLOW_RUN_ID']
    )

    s3_input = resp['RunProperties'].get('s3_input')
    if not s3_input:
        raise ValueError("s3_input Not Found")

    print(f"s3_input: {s3_input}")
    return s3_input

def _to_pyarrow_table(result):
    """
    Compatibility helper to extract a pyarrow.Table from a chDB query_result.
    """
    import chdb

    if hasattr(chdb, "to_arrowTable"):
        return chdb.to_arrowTable(result)

    if hasattr(result, "to_pyarrow"):
        return result.to_pyarrow()
    if hasattr(result, "to_arrow"):
        return result.to_arrow()

    raise RuntimeError(
        "Cannot convert chdb query_result to pyarrow.Table. "
        f"Available attributes: {sorted(dir(result))[:200]}"
    )


def normalize_arrow_for_iceberg(table):
    """
    Normalize Arrow schema for Iceberg compatibility.
    - timestamptz -> timestamp
    - binary -> string
    """
    import pyarrow as pa

    new_fields = []
    new_columns = []

    for field, column in zip(table.schema, table.columns):
        # timestamp with timezone -> timestamp
        if pa.types.is_timestamp(field.type) and field.type.tz is not None:
            new_type = pa.timestamp(field.type.unit)
            new_fields.append(pa.field(field.name, new_type, field.nullable))
            new_columns.append(column.cast(new_type))
        else:
            new_fields.append(field)
            new_columns.append(column)

    new_schema = pa.schema(new_fields)
    return pa.Table.from_arrays(new_columns, schema=new_schema)


def read_parquet_with_chdb(s3_input):
    """
    Read Parquet file from S3 using chDB.
    """
    import chdb

    if s3_input.startswith("s3://"):
        bucket, key = s3_input.replace("s3://", "").split("/", 1)
        s3_url = f"https://{bucket}.s3.ap-northeast-1.amazonaws.com/{key}"
    else:
        s3_url = s3_input

    print(f"Reading data from S3: {s3_url}")

    query = f"""
        SELECT *
        FROM s3('{s3_url}', 'Parquet')
        WHERE VendorID = 2
    """

    result = chdb.query(query, "Arrow")
    arrow_table = _to_pyarrow_table(result)

    print("Original schema:")
    print(arrow_table.schema)

    # Normalize schema for Iceberg compatibility
    arrow_table = normalize_arrow_for_iceberg(arrow_table)

    print("Normalized schema:")
    print(arrow_table.schema)
    print(f"Rows: {arrow_table.num_rows:,}")

    return arrow_table


def write_iceberg_table(arrow_table):
    """
    Write Arrow table to Iceberg table using PyIceberg.
    """
    try:
        print("Writing started...")

        from pyiceberg.catalog import load_catalog

        catalog_config = {
            "type": "glue",
            "warehouse": "s3://your-bucket/your-warehouse/",  # Adjust to your environment.
            "region": "ap-northeast-1",
        }

        catalog = load_catalog("glue_catalog", **catalog_config)
        table_identifier = "icebergdb.yellow_tripdata"

        table = catalog.load_table(table_identifier)

        print(f"Target data to write: {arrow_table.num_rows:,} rows")
        table.append(arrow_table)

        return True

    except Exception as e:
        print(f"Writing error: {e}")
        import traceback
        traceback.print_exc()
        return False


def main():
    try:
        import chdb
        import pyiceberg

        # Read input parameter
        s3_input = get_job_parameters()

        # Read data with chDB
        arrow_tbl = read_parquet_with_chdb(s3_input)
        print(f"Data read success: {arrow_tbl.num_rows:,} rows")

        # Write to Iceberg table
        if write_iceberg_table(arrow_tbl):
            print("\nWriting fully successful!")
        else:
            print("Writing failed")

    except Exception as e:
        print(f"Main error: {e}")
        import traceback
        traceback.print_exc()


if __name__ == "__main__":
    main()

Create a Glue Workflow and include the jobs.
Create a Lambda function with the sample code.
Set an S3 trigger for PUT events.

Execution Result

Comparing Workflow Execution vs Direct Job Execution

Workflow Execution Pros

Dependency management between multiple jobs (order, branching, parallel control)
Unified parameter management (Run Properties)
Centralized monitoring and management (stop/restart on error, unified execution history)

Workflow Execution Cons

Complex setup (triggers and dependencies)
Overhead (management cost even for single jobs, longer startup)
Harder debugging (difficult to test individual jobs)

Direct Job Execution Pros

Simple setup and immediate execution
Flexible parameters, independent scheduling, easier debugging
Cost-efficient: run only when needed, low management overhead

Direct Job Execution Cons

Difficult dependency management (manual order control, error handling)
Dispersed parameter management (per-job configuration)
Dispersed monitoring (hard to get overall picture)

When to Choose Workflow vs Direct Job

Use Workflow When:

Multiple jobs are chained (e.g., data ingestion → transformation → validation → output)
Shared parameters across jobs
Unified monitoring needed
Regular batch processing

Example:

S3 → Lambda → Glue Workflow
  ├── Job1: Data ingestion & preprocessing
  ├── Job2: Data transformation
  └── Job3: Validation & output

Use Direct Job When:

Single-job processing is sufficient
Real-time processing needed
Flexible individual control required
Prototype or testing phase

Example:

S3 → Lambda → Glue Job (single)

Conclusion

We introduced two patterns for triggering Glue Python Shell via S3 events.
When using Lambda to trigger Glue:

For single-job processing → Lambda + Job
For multiple jobs / flow control → Lambda + Workflow

Glue Python Shell may seem like a niche service compared to Glue Spark, EMR, or Lambda, but it can be cost-effective, long-running, and Spark-independent.
Combining it with chDB or DuckDB can boost efficiency, and PyIceberg makes Iceberg integration straightforward.
While this article focused on S3-triggered jobs, Glue Python Shell can also be used as a general-purpose long-running job environment.

I hope this helps you design your ETL workflows and data platforms more effectively.

Exploring the Potential of AWS Glue Python Shell as a Long-Running Batch Execution Environment

Aki — Sat, 17 Jan 2026 14:29:27 +0000

Original Japanese article: AWS Glue Python Shellの長時間バッチ実行環境としての可能性を探る

Introduction

I'm Aki, an AWS Community Builder (@jitepengin).

In my recent articles, I’ve been exploring AWS Glue Python Shell.
Have you ever struggled with the question: “How should we run heavy batch jobs that need to run for a long time, maybe once a month, in production?”

Typical examples include weekly or monthly aggregations, backfills, or integrations with external systems—jobs that can easily run for dozens of hours. For these kinds of long-running workloads, you often have to make several architectural and operational decisions.

A very common approach is to launch an EC2 instance and run the batch job there.

This approach is relatively straightforward, but it also comes with additional concerns beyond the batch logic itself, such as server management, start/stop operations, capacity shortages, and handling Spot interruptions.

On the other hand, AWS Glue Python Shell has the following characteristics, and depending on how you use it, it can be a very convenient option:

Serverless, no infrastructure management required
Jobs can run for up to 48 hours by default, and up to 168 hours maximum
Pay only for what you actually run (pay-as-you-go)

In this article, I’ll run an experiment where I keep a Glue Python Shell job running for 48 hours. Based on that experiment, I’ll examine whether it really runs stably for long periods, and compare it with EC2 from both cost and operational perspectives. The goal is to evaluate whether Glue Python Shell can realistically be considered a long-running batch execution platform.

AWS Glue Python Shell: Key Characteristics

I’ve covered this in previous articles, but let’s briefly recap the main characteristics of Glue Python Shell:

Job type: Python Shell
Maximum execution time: 48 hours by default, up to 168 hours
Resource configuration: DPU (Data Processing Unit), configurable from 1/16 DPU or 1 DPU
Pricing: DPU × execution time
Instance management: Not required (fully managed by AWS)

Since it doesn’t use Spark, it works particularly well for the following use cases:

Relatively lightweight ETL jobs
API integrations or workflows that are I/O-heavy rather than CPU-heavy

I’ve also written an article comparing Glue Python Shell and Lambda in the context of lightweight ETL, so feel free to check it out:

AWS Lambda and AWS Glue Python Shell in the Context of Lightweight ETL

Does It Really Run for 48 Hours? Let’s Test It

While it’s documented that Glue Python Shell can run for up to 48 hours, I was curious whether it really runs continuously without stopping or failing midway.

To test this, I created a simple job that outputs logs every hour and let it run for a full 48 hours.

In this experiment, I limited the execution to 48 hours, but Glue Python Shell can be configured to run for up to 168 hours (about 7 days). So you can read this with weekly or even longer batch workloads in mind.

Sample Code

import time
import datetime

def process_data():
    time.sleep(5)  # Dummy processing

def main():
    start_time = datetime.datetime.now()
    max_hours = 48
    max_seconds = max_hours * 3600

    print(f"[INFO] Job started at {start_time}", flush=True)

    next_log_seconds = 3600  # Next log after 1 hour

    while True:
        process_data()

        elapsed = (datetime.datetime.now() - start_time).total_seconds()

        if elapsed >= next_log_seconds:
            elapsed_hours = int(elapsed // 3600)
            print(f"[INFO] Elapsed time: {elapsed_hours} hours", flush=True)
            next_log_seconds += 3600

        if elapsed >= max_seconds:
            break

    print(
        f"[INFO] Job completed at {datetime.datetime.now()} "
        f"(total: {elapsed/3600:.2f} hours)",
        flush=True
    )

if __name__ == "__main__":
    main()

By using print(..., flush=True), log output is immediately sent to CloudWatch Logs.
Without flush=True, output may be buffered and appear in CloudWatch in batches after some delay.
For long-running jobs, being able to confirm progress via logs is important, so explicitly flushing logs can be helpful.

Results

CloudWatch Logs

CloudWatch Logs showed continuous output like the following:

[INFO] Job started at 2026-01-14 09:01:45.644511
[INFO] Elapsed time: 1 hours
[INFO] Elapsed time: 2 hours
[INFO] Elapsed time: 3 hours
[INFO] Elapsed time: 4 hours
[INFO] Elapsed time: 5 hours
[INFO] Elapsed time: 6 hours
[INFO] Elapsed time: 7 hours
[INFO] Elapsed time: 8 hours
[INFO] Elapsed time: 9 hours
[INFO] Elapsed time: 10 hours
[INFO] Elapsed time: 11 hours
[INFO] Elapsed time: 12 hours
[INFO] Elapsed time: 13 hours
[INFO] Elapsed time: 14 hours
[INFO] Elapsed time: 15 hours
[INFO] Elapsed time: 16 hours
[INFO] Elapsed time: 17 hours
[INFO] Elapsed time: 18 hours
[INFO] Elapsed time: 19 hours
[INFO] Elapsed time: 20 hours
[INFO] Elapsed time: 21 hours
[INFO] Elapsed time: 22 hours
[INFO] Elapsed time: 23 hours
[INFO] Elapsed time: 24 hours
[INFO] Elapsed time: 25 hours
[INFO] Elapsed time: 26 hours
[INFO] Elapsed time: 27 hours
[INFO] Elapsed time: 28 hours
[INFO] Elapsed time: 29 hours
[INFO] Elapsed time: 30 hours
[INFO] Elapsed time: 31 hours
[INFO] Elapsed time: 32 hours
[INFO] Elapsed time: 33 hours
[INFO] Elapsed time: 34 hours
[INFO] Elapsed time: 35 hours
[INFO] Elapsed time: 36 hours
[INFO] Elapsed time: 37 hours
[INFO] Elapsed time: 38 hours
[INFO] Elapsed time: 39 hours
[INFO] Elapsed time: 40 hours
[INFO] Elapsed time: 41 hours
[INFO] Elapsed time: 42 hours
[INFO] Elapsed time: 43 hours
[INFO] Elapsed time: 44 hours
[INFO] Elapsed time: 45 hours
[INFO] Elapsed time: 46 hours
[INFO] Elapsed time: 47 hours
[INFO] Elapsed time: 48 hours
[INFO] Job completed at 2026-01-16 09:01:49.059613 (total: 48.00 hours)

From the logs, we can clearly see that the job ran for approximately 48 hours.

Glue Job Run Monitoring

Glue’s Job Run Monitoring also confirmed a 48-hour execution.

The Run Status is shown as Timeout, but this is expected behavior—it simply means the job reached its maximum execution time of 48 hours.

In this run, the job used 1 DPU for 48 hours, resulting in 48 DPU-hours, exactly as expected.
The total cost for this 48-hour execution was $21.12.

Observations

No manual intervention was required from start to finish
No interruptions or unexpected timeouts
Logging was fully handled by CloudWatch

While this may sound obvious, the experiment confirms—using actual logs and metrics—that Glue Python Shell runs stably for long durations, exactly as documented.

One thing to note is that in AWS Cost Explorer, the cost was not visible until the full 48-hour execution had completed.
This means that if a job runs longer than intended, costs can increase significantly before you notice. You should consider putting monitoring or guardrails in place to detect unexpected long executions.

Comparison with EC2 (m6i.xlarge)

Next, let’s compare this with running the same workload on an EC2 instance with roughly equivalent specs.

Glue Python Shell (1 DPU):

vCPU: 4
Memory: 16 GiB

EC2 m6i.xlarge:

vCPU: 4
Memory: 16 GiB

Cost Comparison (48-Hour Execution)

Assumptions:

Execution frequency: once per month
Execution time: 48 hours

Aspect	Glue Python Shell	EC2 m6i.xlarge
Execution cost	Charged only for runtime	Charged while instance is running
Cost while idle	None	None if stopped (but EBS and Elastic IP costs may still apply)
Setup	None	AMI and launch configuration required

Execution Platform	Unit Price	48-Hour Cost
EC2 Spot (m6i.xlarge)	$0.0828/hour	$3.97
EC2 On-Demand (m6i.xlarge)	$0.248/hour	$11.90
Glue Python Shell (1 DPU)	$0.44/hour	$21.10

Purely from a runtime cost perspective, EC2 is cheaper.
However, with EC2 you also need to account for EBS volume costs, Elastic IP usage, whether the instance is left running outside batch windows, and ongoing OS maintenance and patching. These factors add to the real operational cost over time.

Operational Overhead

While EC2 can reduce costs, it clearly increases operational complexity.
In the table below, “launch failure” refers to whether users must explicitly design for and handle this risk.

Aspect	EC2 (especially Spot)	Glue Python Shell
Start/stop control	User-managed	Managed
Risk of launch failure	Sometimes	Rare
Interruption handling	Required	Not required
OS management	Required	Not required
Long execution	Depends on design	Up to 168 hours
Setup	AMI & launch config needed	None

Things to Consider When Using EC2

Even with On-Demand instances, there are scenarios where instances fail to launch. Typical examples include:

InsufficientInstanceCapacity
Occurs when AWS doesn’t have enough On-Demand capacity. This can happen even when restarting a stopped instance.
InstanceLimitExceeded
Happens when you hit your regional instance quota. This can be resolved by requesting a quota increase.
UnauthorizedOperation
Occurs when required IAM permissions are missing.
The requested configuration is currently not supported
Happens when a specific combination of AZ, instance type, or AMI is unsupported.

These issues may be rare, but for long-running batch jobs that are usually stopped and only run occasionally, such uncertainty can become a non-trivial operational risk.

To mitigate launch failures, AWS provides On-Demand Capacity Reservations.

What Is an On-Demand Capacity Reservation?

An On-Demand Capacity Reservation lets you reserve EC2 capacity in a specific Availability Zone in advance, ensuring your instance can launch when needed.

The benefit is improved launch reliability.
The downside is that you are charged for the reserved capacity even while the instance is stopped.

Therefore, whether capacity reservations make sense depends on whether it’s acceptable to pay continuous costs for a batch job that runs infrequently.

Trade-offs When Choosing AWS Glue Python Shell

AWS Glue Python Shell also has clear limitations:

Maximum execution time (48 hours by default, up to 168 hours)
CPU and memory can’t be finely tuned due to DPU-based configuration
No OS-level customization

If your workload requires:

Always-on execution
Execution times exceeding 168 hours
OS-level control

then EC2 is a better fit.

However, if you understand and accept these constraints, Glue Python Shell becomes a very convenient and pragmatic option.

Conclusion

In this article, I ran an actual 48-hour experiment with AWS Glue Python Shell and compared it with EC2 as a long-running batch execution platform.

Choosing between Glue Python Shell and EC2 isn’t just about raw execution cost—it also requires evaluating operational overhead, failure modes, and overall uncertainty.

Glue Python Shell is a realistic choice for long-running batch jobs that are infrequent, require stable execution, and aim to minimize operational burden. For workloads where “finishing reliably” is the top priority, Glue Python Shell is a particularly good fit.

On the other hand, if your workload runs frequently, requires long execution times, or needs OS-level flexibility, EC2 is the better choice. In that case, it’s important to carefully evaluate EBS sizing, Elastic IP usage, and whether capacity reservations are necessary.

When selecting a platform for long-running batch jobs, the key is not just “how cheap it is,” but how much uncertainty and operational complexity you’re willing to accept.

I hope this article helps you make a better decision when designing long-running batch processing systems.

AWS Lambda and AWS Glue Python Shell in the Context of Lightweight ETL

Aki — Thu, 08 Jan 2026 14:12:42 +0000

Original Japanese article: 軽量ETLの文脈で考えるAWS LambdaとAWS Glue Python Shell

Introduction

I'm Aki, an AWS Community Builder (@jitepengin).

In my previous articles, I introduced lightweight ETL patterns using AWS Lambda and AWS Glue Python Shell.

In this article, I would like to organize when to use AWS Lambda and when to use AWS Glue Python Shell for lightweight ETL workloads, and clarify where each service fits best.

When building with native AWS services, it’s common to think “use Lambda first, and move to Glue when Lambda hits its limits.”
This approach is not wrong, but I believe adding AWS Glue Python Shell as another option makes the design space more flexible.

What is Lightweight ETL?

After publishing several articles on the topic, I should mention this upfront: there is no strict definition of “lightweight ETL.”

For the sake of discussion, I’ll use the following working definition:

Files or data up to a few hundred MB
One or a small number of files
No need for advanced processing such as distributed computation
Scheduled or event-driven execution
Mainly used for preprocessing or initial transformations

In other words, these workloads are not heavy enough to require Glue (Spark) or EMR, but are still too expensive or complex to ignore.
Designing this “middle zone” effectively is the key point.

Position of Lightweight ETL (Big Picture)

Using the Medallion Architecture as a reference, let’s consider where lightweight ETL fits.
Lightweight ETL is most effective when transforming data from the source or Landing layer into the Bronze layer.

There are many interpretations of Medallion Architecture layer definitions, but in this article, you can think of it mainly in the context of preprocessing or initial transformations.

Depending on the size and nature of the dataset, there are also cases where lightweight ETL can be applied to Bronze → Silver transformations.

How Data Changes from Bronze to Silver to Gold

This is a high-level explanation, but each layer generally looks like the following.

Bronze

This layer stores raw data (or lightly transformed data if a Landing layer exists).

In some cases, data is converted from JSON or CSV to Parquet, or stored in Iceberg format.
One advantage of using Parquet or Iceberg at this stage is improved efficiency for Bronze → Silver transformations.
It’s also useful if you want to run analytical queries directly on Bronze data.

Sometimes, Bronze and Silver tables are joined to produce Gold tables.
Using Parquet or Iceberg formats also helps significantly in these scenarios.

Deciding what should be considered “raw” data is an important design decision.
There are debates about whether formatted data can still be called raw, but thinking in terms of a separate Landing layer can help clarify this.

Typical responsibilities include:

Storing raw data
(Optionally) format conversion
(Optionally) type normalization
(Optionally) dropping unnecessary columns
(Optionally) filtering

If you plan to apply Data Contracts, this is typically the layer where they are enforced.

Silver

This layer stores data transformed into 3NF (Third Normal Form).
Normalization reduces data redundancy and prepares the data for reporting.

Data modeling choices often become a topic of discussion here.
Depending on the dataset, this layer may only perform cleansing, so once again, it depends on the use case.

Typical responsibilities include:

Removing duplicate data
Aggregation
Data cleansing

Gold

This layer stores data optimized using dimensional modeling.
Whether to use a One Big Table or a more traditional dimensional model is another common discussion point.
The choice of data model depends on the use case.

Typical responsibilities include:

Optimizing business logic
Creating business metrics
Building data marts

An important point is that data volume and query complexity increase significantly from the Silver layer onward.
At that stage, workloads often exceed the scope of lightweight ETL and are better handled by distributed ETL solutions.

AWS Lambda for Lightweight ETL

As mentioned in previous articles, AWS Lambda works very well for lightweight ETL.

Fast startup
Easy integration with many AWS services
Simple architecture
Serverless, with low operational overhead

For preprocessing and small-scale transformations, Lambda is usually the first option to consider.

That said, Lambda has clear limitations:

Memory limit (3008 MB by default, up to 10240 MB with a quota increase)
Execution time limit (15 minutes)
vCPU allocation depends on memory size

Even with optimizations using DuckDB or chDB, these constraints eventually become bottlenecks.

AWS Glue Python Shell for Lightweight ETL

AWS Glue Python Shell is not very visible, but compared to Glue (Spark) or EMR, it is easier to control costs and supports long-running jobs, up to 168 hours.

It can be seen as the next option after Lambda.

Processing units selectable from 1/16 DPU to 1 DPU (1 DPU = 4 vCPU / 16 GB memory)
Maximum execution time of up to 168 hours (default: 48 hours) This extended execution time is one of the biggest advantages of Glue Python Shell.
Serverless, with low operational overhead
Integration with Glue Workflows

Compared to Lambda, Glue Python Shell offers more memory and much longer execution time, making it suitable for heavier preprocessing workloads.

However, it also has drawbacks:

Slower startup time than Lambda
Limited event-driven integration

Compared to Lambda’s simplicity and versatility, these can be disadvantages.

Glue Python Shell is useful when you need to process heavy workloads that require time rather than immediate responsiveness.
It also works well for nightly batch processing of multiple files.

Choosing Between Lambda and Glue Python Shell

As discussed, Lambda and Glue Python Shell have different characteristics.
A simple way to think about it is:

Lambda is for responsive, lightweight preprocessing
Glue Python Shell is for heavier preprocessing that requires more time and memory

Rough guidelines:

Small workloads that finish within 15 minutes (up to a few hundred MB): Lambda
Medium workloads that exceed 15 minutes or data that Lambda cannot handle (up to a few GB): Glue Python Shell
Large-scale or complex workloads: Glue (Spark) or EMR

Cost is also a critical factor.
Lambda includes a free tier of 1 million requests and 400,000 GB-seconds per month, while Glue Python Shell does not.
For the same execution duration, Glue Python Shell is generally more expensive.

These factors should all be considered when making a decision.

Configuration Comparison

Aspect	Lambda	Glue Python Shell
Memory	Up to 10 GB (quota required)	16 GB per DPU
Execution time	Up to 15 minutes	Up to 168 hours
Startup speed	Very fast	Slower
Event integration	Very good	Good
Operational effort	Very low	Low

Insights from Benchmarks

Let’s look at benchmarks using the same dataset and processing logic described in the following articles:

Lightweight ETL with AWS Lambda, chDB, and PyIceberg (Compared with DuckDB)
Lightweight ETL with AWS Glue Python Shell, chDB, and PyIceberg (Compared with DuckDB)

The dataset used is NYC Taxi data:

https://www.nyc.gov/site/tlc/about/tlc-trip-record-data.page

The test data includes:

January 2024 Yellow Taxi Trip Records (2,964,624 records, 48 MB)
A full year combined file (41,169,720 records, 807 MB)

Lambda was tested with memory sizes of 1024 MB, 2048 MB, and the default maximum of 3008 MB.
Glue Python Shell was tested with 1/16 DPU and 1 DPU.

Memory usage cannot be directly compared, so the focus is on processing time.

January file (48 MB)

Platform	Resource setting	chDB time (s)	DuckDB time (s)
Glue Python Shell	1/16 DPU	46.000	40.000
Glue Python Shell	1 DPU	39.000	34.000
AWS Lambda	1024 MB	5.092	5.163
AWS Lambda	2048 MB	3.873	4.265
AWS Lambda	3008 MB	3.370	4.061

Full year file (807 MB)

Platform	Resource setting	chDB time (s)	DuckDB time (s)
Glue Python Shell	1/16 DPU	OutOfMemory	OutOfMemory
Glue Python Shell	1 DPU	51.0	212.0
AWS Lambda	1024 MB	OutOfMemory	OutOfMemory
AWS Lambda	2048 MB	OutOfMemory	OutOfMemory
AWS Lambda	3008 MB	27.171	187.332

One important observation is that Glue Python Shell shows longer execution times.
With 1 DPU, it should theoretically outperform Lambda.
This difference is not due to raw processing power, but to fixed startup overhead in Glue Python Shell, regardless of workload size.

This includes:

Job startup time
Container preparation
Dependency initialization

These factors add to the overall lead time and must be considered.
Evaluating this startup overhead is key when deciding whether to use Glue Python Shell.

Another point is that Lambda increases vCPU allocation as memory increases.
With 3008 MB, Lambda may achieve higher single-process performance than Glue Python Shell.

That said, Glue Python Shell still offers two major strengths: long execution time (up to 168 hours) and 16 GB of memory.

While there are many other decision factors, a simple rule of thumb could look like this:

File size
 ├─ Up to a few hundred MB → Lambda
 │    └─ If processing exceeds 15 minutes → Glue Python Shell
 └─ Multiple GB or complex workloads → Glue (Spark) / EMR

The most important thing is being able to clearly explain why you chose a particular approach.

Conclusion

In this article, I discussed how to choose between AWS Lambda and AWS Glue Python Shell in the context of lightweight ETL.

For preprocessing workloads up to a few hundred MB, AWS Lambda is often the best choice in terms of execution time, cost, and operational simplicity.
Glue Python Shell is a useful option for filling the gap between Lambda and distributed processing, handling workloads that are too heavy for Lambda but do not require Spark or EMR.

Rather than forcing all workloads into Lambda or Glue Python Shell, choosing the right tool for each situation is essential.

I hope this article helps anyone considering lightweight data processing on AWS.

Lightweight ETL with AWS Glue Python Shell, chDB, and PyIceberg (Compared with DuckDB)

Aki — Mon, 05 Jan 2026 14:30:06 +0000

Original Japanese article: AWS Glue Python Shell×chDB×PyIcebergで実現する軽量ETLとDuckDB比較

Introduction

I'm Aki, an AWS Community Builder (@jitepengin).

In my previous article, I implemented a lightweight ETL pipeline using AWS Lambda, chDB, and PyIceberg.

Lightweight ETL with AWS Lambda, chDB, and PyIceberg (Compared with DuckDB)

In this article, I’ll introduce a Glue Python Shell version of that architecture, implementing ETL that writes data into Apache Iceberg using chDB.

AWS Glue Python Shell has different characteristics compared to Lambda. By leveraging those strengths, we can design ETL workloads that go beyond Lambda’s limitations.
As in the previous article, I’ll also run the same benchmark comparison against DuckDB to highlight the characteristics of each engine.

What is AWS Glue Python Shell?

AWS Glue Python Shell is a Glue execution environment that runs Python scripts without Apache Spark. Compared with Lambda’s 15-minute limit, it supports much longer batch jobs—the default maximum is 48 hours—so it’s suitable for longer-running ETL tasks. It’s serverless, so you don’t manage infrastructure and you pay for what you use.

Compared to Glue Spark or EMR, Glue Python Shell is often cheaper for lightweight ETL and data transformations. However, one downside versus Lambda is fewer native trigger options: Glue Python Shell does not directly support S3 object-created triggers or EventBridge triggers in the same way Lambda does. Typical approaches are to invoke Glue Python Shell via Lambda or Glue Workflows. Despite constraints, when used well it can significantly expand your processing options.

Architecture Used in This Article

The architecture is intentionally simple (and identical to the DuckDB version).

Here’s the architecture covered in this post: when a file is uploaded to S3, a Lambda function is triggered. The Lambda only starts a Glue Python Shell job and passes the S3 path; the actual ETL runs inside the Glue Python Shell and writes Iceberg-format data back to S3. This lets you process workloads that exceed Lambda’s 15-minute limit.

Key Libraries Used

chDB: A database engine that enables fast in-memory SQL query execution.
PyArrow: Supports high-speed data conversion and transfer using the Arrow format.
PyIceberg: Allows access and manipulation of Iceberg tables via AWS Glue Catalog.

Note: Another way to invoke Glue Python Shell is via EventBridge → Glue Workflow → Glue Python Shell.
I’ll cover the differences and benefits of these approaches in a future article.

What is chDB?

https://clickhouse.com/chdb

chDB is an embedded engine version of ClickHouse that can be used as a library.
Its main features:

Contains the ClickHouse execution engine
High-performance C++ native implementation
Execute SQL directly from Python
Can run serverless in a single process

Like DuckDB, it’s positioned as an “embedded analytical engine.”

Additional Python modules path

Set the Additional Python modules path to include:

pyiceberg[glue]==0.9.1,pydantic==2.5.0,pydantic-core==2.14.1,annotated-types==0.6.0,chdb==0.13.0,pyarrow==14.0.1,typing-extensions==4.8.0

Sample Code

Lambda trigger function

A simple Lambda that receives an S3 event and starts the Glue job:

import boto3

def lambda_handler(event, context):
    glue = boto3.client("glue")

    s3_bucket = event['Records'][0]['s3']['bucket']['name']
    s3_object_key = event['Records'][0]['s3']['object']['key']

    s3_input_path = f"s3://{s3_bucket}/{s3_object_key}"

    response = glue.start_job_run(
        JobName="chdb shell",
        Arguments={
            "--s3_input": s3_input_path
        }
    )
    print(f"Glue Job started: {response['JobRunId']}")
    return response

AWS Glue Python Shell ETL Logic

The Glue Python Shell job is started from Lambda with the S3 input path; the job uses DuckDB to read the file and PyIceberg to write into an Iceberg table. In this example the DuckDB query filters VendorID = 1 to demonstrate SQL-style cleansing/filters.

This example uses GlueCatalog access. You can also use the Glue Iceberg REST catalog approach (REST Catalog) depending on your requirements—see the link below for differences.

Reference about catalog access modes:
PyIceberg on AWS Lambda: Comparing GlueCatalog and REST Catalog Access Methods

import boto3
import sys
import os
from awsglue.utils import getResolvedOptions


def get_job_parameters():
    try:
        required_args = ['s3_input']
        args = getResolvedOptions(sys.argv, required_args)

        s3_file_path = args['s3_input']
        print(f"s3_input: {s3_file_path}")

        return s3_file_path

    except Exception as e:
        print(f"parameters error: {e}")
        raise


def _to_pyarrow_table(result):
    """
    Compatibility helper to extract a pyarrow.Table from a chDB query_result.
    """
    import chdb

    if hasattr(chdb, "to_arrowTable"):
        return chdb.to_arrowTable(result)

    if hasattr(result, "to_pyarrow"):
        return result.to_pyarrow()
    if hasattr(result, "to_arrow"):
        return result.to_arrow()

    raise RuntimeError(
        "Cannot convert chdb query_result to pyarrow.Table. "
        f"Available attributes: {sorted(dir(result))[:200]}"
    )


def normalize_arrow_for_iceberg(table):
    """
    Normalize Arrow schema for Iceberg compatibility.
    - timestamptz -> timestamp
    - binary -> string
    """
    import pyarrow as pa

    new_fields = []
    new_columns = []

    for field, column in zip(table.schema, table.columns):
        # timestamp with timezone -> timestamp
        if pa.types.is_timestamp(field.type) and field.type.tz is not None:
            new_type = pa.timestamp(field.type.unit)
            new_fields.append(pa.field(field.name, new_type, field.nullable))
            new_columns.append(column.cast(new_type))
        else:
            new_fields.append(field)
            new_columns.append(column)

    new_schema = pa.schema(new_fields)
    return pa.Table.from_arrays(new_columns, schema=new_schema)


def read_parquet_with_chdb(s3_input):
    """
    Read Parquet file from S3 using chDB.
    """
    import chdb

    if s3_input.startswith("s3://"):
        bucket, key = s3_input.replace("s3://", "").split("/", 1)
        s3_url = f"https://{bucket}.s3.ap-northeast-1.amazonaws.com/{key}"
    else:
        s3_url = s3_input

    print(f"Reading data from S3: {s3_url}")

    query = f"""
        SELECT *
        FROM s3('{s3_url}', 'Parquet')
        WHERE VendorID = 1
    """

    result = chdb.query(query, "Arrow")
    arrow_table = _to_pyarrow_table(result)

    print("Original schema:")
    print(arrow_table.schema)

    # Normalize schema for Iceberg compatibility
    arrow_table = normalize_arrow_for_iceberg(arrow_table)

    print("Normalized schema:")
    print(arrow_table.schema)
    print(f"Rows: {arrow_table.num_rows:,}")

    return arrow_table


def write_iceberg_table(arrow_table):
    """
    Write Arrow table to Iceberg table using PyIceberg.
    """
    try:
        print("Writing started...")

        from pyiceberg.catalog import load_catalog

        catalog_config = {
            "type": "glue",
            "warehouse": "s3://your-bucket/your-warehouse/",  # Adjust to your environment.
            "region": "ap-northeast-1",
        }

        catalog = load_catalog("glue_catalog", **catalog_config)
        table_identifier = "icebergdb.yellow_tripdata"

        table = catalog.load_table(table_identifier)

        print(f"Target data to write: {arrow_table.num_rows:,} rows")
        table.append(arrow_table)

        return True

    except Exception as e:
        print(f"Writing error: {e}")
        import traceback
        traceback.print_exc()
        return False


def main():
    try:
        import chdb
        import pyiceberg

        # Read input parameter
        s3_input = get_job_parameters()

        # Read data with chDB
        arrow_tbl = read_parquet_with_chdb(s3_input)
        print(f"Data read success: {arrow_tbl.num_rows:,} rows")

        # Write to Iceberg table
        if write_iceberg_table(arrow_tbl):
            print("\nWriting fully successful!")
        else:
            print("Writing failed")

    except Exception as e:
        print(f"Main error: {e}")
        import traceback
        traceback.print_exc()


if __name__ == "__main__":
    main()

Note: This article focuses on the differences in operation, so version updates or conflict handling in Iceberg tables are omitted.

Execution Result

We successfully registered data into an Iceberg table (OTF) using this approach.

Comparison with DuckDB

Let’s compare this implementation with the DuckDB-based approach introduced earlier.

Lightweight ETL with AWS Glue Python Shell, DuckDB, and PyIceberg

DuckDB Sample Code

import boto3
import sys
import os
from awsglue.utils import getResolvedOptions


def get_job_parameters():
    # get job parameters
    try:
        required_args = ['s3_input']

        args = getResolvedOptions(sys.argv, required_args)

        s3_file_path = args['s3_input']
        print(f"object: {s3_file_path}")

        return s3_file_path

    except Exception as e:
        print(f"parameters error: {e}")
        raise

def setup_duckdb_environment():
    # Properly set up the DuckDB environment
    try:
        home_dir = '/tmp'
        duckdb_dir = '/tmp/.duckdb'
        extensions_dir = '/tmp/.duckdb/extensions'

        os.environ['HOME'] = home_dir
        os.environ['DUCKDB_HOME'] = duckdb_dir
        os.environ['DUCKDB_CONFIG_PATH'] = duckdb_dir
        os.environ['DUCKDB_EXTENSION_DIRECTORY'] = extensions_dir

        os.makedirs(duckdb_dir, exist_ok=True)
        os.makedirs(extensions_dir, exist_ok=True)
        os.chmod(duckdb_dir, 0o755)
        os.chmod(extensions_dir, 0o755)

        print(f"DuckDB environment setup completed: {duckdb_dir}")
        return True

    except Exception as e:
        print(f"DuckDB environment setup error: {e}")
        return False

def read_parquet_with_duckdb(s3_input):
    # Read Parquet file from S3 using DuckDB
    import duckdb

    con = duckdb.connect(':memory:')

    try:
        con.execute("SET extension_directory='/tmp/.duckdb/extensions';")
        con.execute("INSTALL httpfs;")
        con.execute("LOAD httpfs;")

        session = boto3.Session()
        credentials = session.get_credentials()

        con.execute(f"SET s3_region='ap-northeast-1';")
        con.execute(f"SET s3_access_key_id='{credentials.access_key}';")
        con.execute(f"SET s3_secret_access_key='{credentials.secret_key}';")

        if credentials.token:
            con.execute(f"SET s3_session_token='{credentials.token}';")

        print(f"Reading data from S3: {s3_input}")
        sql = f"SELECT * FROM read_parquet('{s3_input}') WHERE VendorID = 1"
        res = con.execute(sql)

        return res.arrow()

    except Exception as e:
        print(f"DuckDB error: {e}")
        raise
    finally:
        con.close()

def write_iceberg_table(arrow_table):
    # Write to an Iceberg table
    try:
        print("Writing started...")

        from pyiceberg.catalog import load_catalog
        from pyiceberg.schema import Schema
        from pyiceberg.types import NestedField, StringType, IntegerType, DoubleType, TimestampType

        catalog_config = {
            "type": "glue",
            "warehouse": "s3://your-bucket/your-warehouse/", # Adjust to your environment.
            "region": "ap-northeast-1"
        }

        catalog = load_catalog("glue_catalog", **catalog_config)

        table_identifier = "icebergdb.yellow_tripdata"

        table = catalog.load_table(table_identifier)
        print(f"Target data to write: {arrow_table.num_rows:,} rows")

        # Write Arrow table to Iceberg table
        table.append(arrow_table)


        return True

    except Exception as e:
        print(f"Writing error: {e}")
        import traceback
        traceback.print_exc()
        return False

def main():  
    # Set up DuckDB environment
    if not setup_duckdb_environment():
        print("Failed to set up DuckDB environment")
        return

    try:
        import pyiceberg

        # Read data
        s3_input = get_job_parameters()

        arrow_tbl = read_parquet_with_duckdb(s3_input)
        print(f"Data read success: {arrow_tbl.shape}")

        # Write to Iceberg table
        if write_iceberg_table(arrow_tbl):
            print("\nWriting fully successful!")

        else:
            print("Writing failed")

    except Exception as e:
        print(f"Main error: {e}")
        import traceback
        traceback.print_exc()

if __name__ == "__main__":
    main()

Note: This article focuses on the differences in operation, so version updates or conflict handling in Iceberg tables are omitted.

Benchmark

We used NYC taxi data for testing, comparing the same file with different memory allocations.

Source: NYC Taxi Trip Record Data

Test files:

January 2024 Yellow Taxi Trip Records (2,964,624 records, 48 MB)
Full 2024 dataset (41,169,720 records, 807 MB)

We test with DPU = 1/16 and 1.

Benchmark Results

January File (48MB)

DPU	chDB Time (s)	chDB DPU hours	DuckDB Time (s)	DuckDB DPU hours
1/16	46	<0.01	40	<0.01
1	39	0.02	34	0.01

Full Year File (807MB)

DPU	chDB Time (s)	chDB DPU hours	DuckDB Time (s)	DuckDB DPU hours
1/16	OutOfMemory	–	OutOfMemory	–
1	51	0.02	212	0.06

Glue Python Shell ran out of memory due to insufficient buffer for Parquet → Arrow → Iceberg conversion.

Observations

For small datasets (~48MB), both chDB and DuckDB are fully practical. The performance difference is negligible.
With 1 DPU and sufficient memory, chDB outperforms DuckDB in both execution time and cost efficiency. This makes chDB particularly suitable for processing large files in a single run.
In lightweight ETL using Glue Python Shell, DPU sizing based on file size is the most critical design factor.

Conclusion

In this article, we implemented an ETL pipeline that writes data into Apache Iceberg using AWS Glue Python Shell and chDB.

When using 1 DPU and processing large files, chDB demonstrated superior performance and cost efficiency compared to DuckDB.
That said, behavior may vary with more complex queries, so benchmarking under real workloads is always recommended.

By leveraging Glue Python Shell’s long execution time, we can efficiently process workloads that would otherwise time out in Lambda.

Combining chDB or DuckDB with PyIceberg enables highly efficient, serverless ETL pipelines with Iceberg integration.
Since Iceberg has become a de facto standard for Open Table Formats (OTF), this approach significantly expands the practical use cases of lakehouse architectures.

I hope this article helps anyone considering lightweight ETL or near–real-time processing on Iceberg tables.

Lightweight ETL with AWS Lambda, chDB, and PyIceberg (Compared with DuckDB)

Aki — Fri, 02 Jan 2026 20:33:00 +0000

Original Japanese article: AWS Lambda×chDB×PyIcebergで実現する軽量ETLとDuckDB比較

Introduction

I'm Aki, an AWS Community Builder (@jitepengin).

In my previous articles, I’ve explored lightweight ETL implementations:

Lightweight ETL using AWS Lambda is widely used in practical scenarios.
When using Lambda, the choice of query engine significantly affects performance and stability, especially given the limited memory and execution time.

So far, I’ve used DuckDB for processing, but in this article, I’ll focus on chDB, the embedded version of ClickHouse, to implement a lightweight ETL.
We’ll also compare it with DuckDB under the same conditions to explore the characteristics of each engine.

Architecture

The architecture is very simple (the same setup as my DuckDB article).
A Lambda function is triggered by S3 file uploads and writes the data to S3 in Iceberg format.

The key libraries used in Lambda:

chDB: A database engine that enables fast in-memory SQL query execution.
PyArrow: Supports high-speed data conversion and transfer using the Arrow format.
PyIceberg: Allows access and manipulation of Iceberg tables via AWS Glue Catalog.

What is chDB?

chDB is an embedded engine version of ClickHouse that can be used as a library.
Its main features:

Contains the ClickHouse execution engine
High-performance C++ native implementation
Execute SQL directly from Python
Can run serverless in a single process

Like DuckDB, it’s positioned as an “embedded analytical engine.”

Packaging for Lambda

Since the dependencies are large, I used a container image.

Dockerfile

FROM public.ecr.aws/lambda/python:3.12

WORKDIR /var/task

COPY requirements.txt .
RUN pip install -r requirements.txt --target "${LAMBDA_TASK_ROOT}"

COPY lambda_function.py .

CMD ["lambda_function.lambda_handler"]

requirements.txt

chdb
pyiceberg[glue]
pyarrow

Sample Code

import chdb
import pyarrow as pa
from pyiceberg.catalog.glue import GlueCatalog


def _to_pyarrow_table(result):
    """
    Compatibility helper to extract a pyarrow.Table from a chDB query_result.
    """
    if hasattr(chdb, "to_arrowTable"):
        return chdb.to_arrowTable(result)

    if hasattr(result, "to_pyarrow"):
        return result.to_pyarrow()
    if hasattr(result, "to_arrow"):
        return result.to_arrow()

    raise RuntimeError(
        "Cannot convert chdb query_result to pyarrow.Table. "
        f"Available attributes: {sorted(dir(result))[:200]}"
    )


def normalize_arrow_for_iceberg(table: pa.Table) -> pa.Table:
    """
    Normalize Arrow types that Iceberg does not accept
    (mainly timezone-aware timestamps).
    """
    new_fields = []
    new_columns = []

    for field, column in zip(table.schema, table.columns):
        if pa.types.is_timestamp(field.type) and field.type.tz is not None:
            # Remove timezone information (values remain in UTC)
            new_type = pa.timestamp(field.type.unit)
            new_fields.append(pa.field(field.name, new_type, field.nullable))
            new_columns.append(column.cast(new_type))
        else:
            new_fields.append(field)
            new_columns.append(column)

    new_schema = pa.schema(new_fields)
    return pa.Table.from_arrays(new_columns, schema=new_schema)


def lambda_handler(event, context):
    try:
        # Extract S3 bucket and object key from the event
        s3_bucket = event['Records'][0]['s3']['bucket']['name']
        s3_object_key = event['Records'][0]['s3']['object']['key']

        # Build S3 HTTPS URL
        s3_url = (
            f"https://{s3_bucket}."
            f"s3.ap-northeast-1.amazonaws.com/"
            f"{s3_object_key}"
        )

        print(f"s3_url: {s3_url}")

        # Query Parquet data on S3 using chDB
        query = f"""
            SELECT *
            FROM s3('{s3_url}', 'Parquet')
            WHERE VendorID = 1
        """

        # Execute chDB query with Arrow output
        result = chdb.query(query, "Arrow")

        # Convert chDB result to pyarrow.Table
        arrow_table = _to_pyarrow_table(result)
        print(f"Original schema: {arrow_table.schema}")

        # Normalize schema for Iceberg compatibility
        arrow_table = normalize_arrow_for_iceberg(arrow_table)
        print(f"Normalized schema: {arrow_table.schema}")
        print(f"Rows: {arrow_table.num_rows}")

        # Initialize Iceberg Glue Catalog
        catalog = GlueCatalog(
            name="my_catalog",
            database="icebergdb",
            region_name="ap-northeast-1",
        )

        # Load Iceberg table
        iceberg_table = catalog.load_table("icebergdb.yellow_tripdata")

        # Append data to Iceberg table
        iceberg_table.append(arrow_table)

        print("Data appended to Iceberg table.")

    except Exception as e:
        print("Exception:", e)
        raise

Note: This article focuses on the differences in operation, so version updates or conflict handling in Iceberg tables are omitted.

Execution Result

We successfully appended data to an Iceberg table!
Although no filtering or validation was implemented here, additional processing can be added inside Lambda.

Comparison with DuckDB

DuckDB is another popular engine for lightweight ETL.
For details, see my DuckDB article:

Lightweight ETL with AWS Lambda, DuckDB, and PyIceberg

DuckDB Dockerfile

FROM public.ecr.aws/lambda/python:3.12

WORKDIR /var/task

COPY requirements.txt .
RUN pip install -r requirements.txt --target "${LAMBDA_TASK_ROOT}"

COPY lambda_function.py .

CMD ["lambda_function.lambda_handler"]

DuckDB requirements.txt

chdb
pyiceberg[glue]
pyarrow

DuckDB Sample Code

import duckdb
import pyarrow as pa
from pyiceberg.catalog.glue import GlueCatalog  

def lambda_handler(event, context):
    try:
        # Connect to DuckDB and set the home directory
        duckdb_connection = duckdb.connect(database=':memory:')
        duckdb_connection.execute("SET home_directory='/tmp'") 

        # Install and load the httpfs extension
        duckdb_connection.execute("INSTALL httpfs;")
        duckdb_connection.execute("LOAD httpfs;")

        # Load data from S3 using DuckDB
        s3_bucket = event['Records'][0]['s3']['bucket']['name']
        s3_object_key = event['Records'][0]['s3']['object']['key']

        s3_input_path = f"s3://{s3_bucket}/{s3_object_key}"

        print(f"s3_input_path: {s3_input_path}")

        query = f"""
            SELECT * FROM read_parquet('{s3_input_path}') WHERE VendorID = 1
        """
        # Execute SQL and retrieve results as a PyArrow Table
        result_arrow_table = duckdb_connection.execute(query).fetch_arrow_table()

        print(f"Number of rows retrieved: {result_arrow_table.num_rows}")
        print(f"Data schema: {result_arrow_table.schema}")

        # Configure Glue Catalog (to access Iceberg table)
        catalog = GlueCatalog(region_name="ap-northeast-1", database="icebergdb", name="my_catalog")  # Adjust to your environment.

        # Load the table
        namespace = "icebergdb"  # Adjust to your environment.
        table_name = "yellow_tripdata"  # Adjust to your environment.
        iceberg_table = catalog.load_table(f"{namespace}.{table_name}")

        # Append data to the Iceberg table in bulk
        iceberg_table.append(result_arrow_table) 

        print("Data has been appended to S3 in Iceberg format.")

    except Exception as e:
        print(f"An error occurred: {e}")

Note: Version updates and conflict handling are omitted here as well.

Benchmark

We used NYC taxi data for testing, comparing the same file with different memory allocations.

Source: NYC Taxi Trip Record Data

Test files:

January 2024 Yellow Taxi Trip Records (2,964,624 records, 48 MB)
Full 2024 dataset (41,169,720 records, 807 MB)

Memory settings: 1024 MB, 2048 MB, and 3008 MB (maximum without quota increase).

January File (48 MB)

Memory (MB)	chDB Time (ms)	chDB Memory (MB)	DuckDB Time (ms)	DuckDB Memory (MB)
1024	5,092.17	1018	5,163.48	512
2048	3,872.62	1132	4,264.52	538
3008	3,369.78	1115	4,061.33	524

Full Year File (807 MB)

Memory (MB)	chDB Time (ms)	chDB Memory (MB)	DuckDB Time (ms)	DuckDB Memory (MB)
1024	OutOfMemory	-	OutOfMemory	-
2048	OutOfMemory	-	OutOfMemory	-
3008	27,170.76	3001	187,331.51	2732

Lambda ran out of memory due to insufficient buffer for Parquet → Arrow → Iceberg conversion.

Observations

chDB: Fast but high peak memory consumption; sufficient Lambda memory is required.
DuckDB: Memory-efficient, but processing time increases with large datasets.
Lightweight ETL: “One file per Lambda execution” is the optimal design.
Key point: On Lambda, consider data size × memory allocation × engine characteristics for efficient ETL.

Conclusion

We implemented ETL for Iceberg tables using AWS Lambda and chDB.
Like DuckDB, chDB is a strong candidate for lightweight ETL.

Memory usage is high, but execution is fast, making it suitable when speed is critical.
For complex queries, performance may vary, so testing is recommended.

Both engines have different strengths, but using Lambda enables real-time ETL triggered by S3 events, allowing a lightweight and simple data processing flow.
Integrating with Iceberg also enables scalable Lakehouse architectures.

Although both chDB and DuckDB are still evolving, understanding their characteristics and using them according to the use case allows for building simple yet extensible data pipelines.

I hope this article helps those exploring lightweight ETL or real-time processing for Iceberg tables.

Looking at AWS Inter-Region Latency Through “Distance”

Aki — Fri, 19 Dec 2025 13:46:00 +0000

Original Japanese article: 「距離」から考えるAWSリージョン間通信

Introduction

I'm Aki, an AWS Community Builder (@jitepengin).

When designing architectures on AWS, you’ve probably found yourself asking questions like:

“How far apart are these two regions, actually?”
“Is this distance realistic for a DR target?”
“Is this latency something we can solve with architecture, or not?”

In this article, I want to look at “distance” as a prerequisite for region selection and disaster recovery (DR) design.

The goal here is not to measure latency precisely.
Instead, by organizing our perspective as:

distance → theoretical lower bound → observed latency

I hope to make everyday design decisions feel a bit more grounded and less stressful.

Latency is influenced by many factors: network paths, link quality, congestion, routing policies, and more.
You can’t explain everything with distance alone—but physical distance is one of the hard constraints.

Keeping that in mind, let’s walk through this together.

Why Look at “Distance” Instead of Latency?

As most of you already know, distance and latency are not the same thing.

In real-world communication, factors such as:

Submarine cable routes
Delays introduced by routers and switches
Congestion and routing control

all stack up, and there is always a gap between theoretical and measured values.

So why bother looking at distance?

Because distance is a physical constraint.

Latency values fluctuate depending on environment and time of day, but the fact that Tokyo is far from Frankfurt never changes.

Distance helps as:

A way to think about the lower bound of latency
A decision axis for synchronous vs. asynchronous design
A prerequisite for evaluating the realism of DR architectures

It doesn’t give you the answer—but it gives you a solid starting point.

Assumptions for Distance Calculation (Latitude, Longitude, and the Haversine Formula)

AWS regions exist somewhere on Earth, so their locations can be expressed using latitude and longitude.

Latitude (lat): north–south position
Longitude (lon): east–west position

For example, the Tokyo region (ap-northeast-1) can be approximated as:

lat = 35.6895
lon = 139.6917

Since the exact coordinates of AWS data centers are not public, we use approximate coordinates for representative cities.

To calculate distances, we use the Haversine formula.

The Haversine formula assumes the Earth is a perfect sphere and calculates the shortest distance (great-circle distance) between two points. It’s commonly used for distance calculations based on latitude and longitude.

Key points:

Treat the Earth as a sphere, not a plane
Compare all regions under the same assumptions

This is not about precise geodetic measurement—it’s a pragmatic way to understand relative distances between regions.

A CLI Tool to Visualize Distance Between AWS Regions

To make distance easier to grasp, I built a simple CLI tool.

The goal of this tool is not absolute accuracy, but to align our assumptions before having architecture discussions.

https://github.com/dataPenginPenguin/aws-region-distance

Directory Structure

aws-region-distance/
├── regions.json
├── geo.py
└── main.py

Region Definitions (`regions.json`)

This file defines AWS regions and the latitude/longitude of a representative city for each.

{
  "us-east-2":   { "name": "Ohio",          "lat": 40.4173, "lon": -82.9071 },
  "us-east-1":   { "name": "N. Virginia",   "lat": 38.0336, "lon": -78.5080 },
  "us-west-1":   { "name": "N. California", "lat": 37.7749, "lon": -122.4194 },
  "us-west-2":   { "name": "Oregon",        "lat": 45.5152, "lon": -122.6784 },

  "af-south-1":  { "name": "Cape Town",     "lat": -33.9249, "lon": 18.4241 },

  "ap-east-1":   { "name": "Hong Kong",     "lat": 22.3193, "lon": 114.1694 },
  "ap-south-2":  { "name": "Hyderabad",     "lat": 17.3850, "lon": 78.4867 },
  "ap-southeast-3": { "name": "Jakarta",    "lat": -6.2088, "lon": 106.8456 },
  "ap-southeast-5": { "name": "Malaysia",   "lat": 3.1390, "lon": 101.6869 },
  "ap-southeast-4": { "name": "Melbourne",  "lat": -37.8136, "lon": 144.9631 },
  "ap-south-1":     { "name": "Mumbai",     "lat": 19.0760, "lon": 72.8777 },
  "ap-southeast-6": { "name": "New Zealand","lat": -36.8485, "lon": 174.7633 },
  "ap-northeast-3": { "name": "Osaka",      "lat": 34.6937, "lon": 135.5023 },
  "ap-northeast-2": { "name": "Seoul",      "lat": 37.5665, "lon": 126.9780 },
  "ap-southeast-1": { "name": "Singapore",  "lat": 1.3521, "lon": 103.8198 },
  "ap-southeast-2": { "name": "Sydney",     "lat": -33.8688, "lon": 151.2093 },
  "ap-northeast-1": { "name": "Tokyo",      "lat": 35.6895, "lon": 139.6917 },

  "ca-central-1":  { "name": "Central",     "lat": 43.6532, "lon": -79.3832 },
  "ca-west-1":     { "name": "West",        "lat": 51.0447, "lon": -114.0719 },

  "eu-west-1":   { "name": "Ireland",       "lat": 53.3498, "lon": -6.2603 },
  "eu-west-2":   { "name": "London",        "lat": 51.5074, "lon": -0.1278 },
  "eu-west-3":   { "name": "Paris",         "lat": 48.8566, "lon": 2.3522 },
  "eu-central-1": { "name": "Frankfurt",    "lat": 50.1109, "lon": 8.6821 },
  "eu-central-2": { "name": "Zurich",       "lat": 47.3769, "lon": 8.5417 },
  "eu-north-1":   { "name": "Stockholm",    "lat": 59.3293, "lon": 18.0686 },
  "eu-south-1":   { "name": "Milan",        "lat": 45.4642, "lon": 9.1900 },
  "eu-south-2":   { "name": "Spain",        "lat": 40.4168, "lon": -3.7038 },

  "sa-east-1":   { "name": "São Paulo",     "lat": -23.5505, "lon": -46.6333 },

  "me-central-1": { "name": "UAE",           "lat": 25.2048, "lon": 55.2708 },
  "me-south-1":   { "name": "Bahrain",       "lat": 26.0667, "lon": 50.5577 },

  "il-central-1": { "name": "Tel Aviv",      "lat": 32.0853, "lon": 34.7818 }
}

Distance Calculation Logic (`geo.py`)

This module calculates physical distance between regions.

The theoretical latency is based on fiber optic propagation:

Theoretical RTT (round trip)
= distance_km × 2 ÷ (299,792 / 1.47) × 1000 (ms)

Signal speed in fiber ≈ c / 1.47 (refractive index)
c ≈ 299,792 km/s (speed of light in vacuum)
This represents the physical lower bound of RTT
Network devices and routing delays are not included

import math

def haversine_km(lat1, lon1, lat2, lon2):
    """
    Calculate great-circle distance between two points on Earth.
    Returns distance in kilometers.
    """
    R = 6371  # Earth radius in km

    dlat = math.radians(lat2 - lat1)
    dlon = math.radians(lon2 - lon1)

    a = (
        math.sin(dlat / 2) ** 2
        + math.cos(math.radians(lat1))
        * math.cos(math.radians(lat2))
        * math.sin(dlon / 2) ** 2
    )
    c = 2 * math.atan2(math.sqrt(a), math.sqrt(1 - a))

    return R * c


def theoretical_rtt_ms(distance_km, fiber_index=1.47):
    """
    Calculate theoretical round-trip latency (ms)
    based on fiber optic propagation speed.
    """
    c = 299_792  # speed of light in vacuum (km/s)
    fiber_speed = c / fiber_index

    # RTT (round-trip)
    return (distance_km * 2) / fiber_speed * 1000

CLI Implementation (`main.py`)

You select an origin region, and the tool lists distances to all other regions.

import json
from geo import haversine_km, theoretical_rtt_ms


def select_origin_region(regions):
    """
    Let user select an origin AWS region.
    """
    region_codes = list(regions.keys())

    print("Select origin AWS region:\n")
    for i, code in enumerate(region_codes, 1):
        print(f"{i:2}) {code:15} {regions[code]['name']}")

    while True:
        try:
            idx = int(input("\nEnter number: ")) - 1
            if 0 <= idx < len(region_codes):
                return region_codes[idx]
        except ValueError:
            pass

        print("Invalid input. Please try again.")


def main():
    with open("regions.json", encoding="utf-8") as f:
        regions = json.load(f)

    origin = select_origin_region(regions)
    origin_info = regions[origin]

    rows = []

    for code, info in regions.items():
        distance = haversine_km(
            origin_info["lat"],
            origin_info["lon"],
            info["lat"],
            info["lon"],
        )
        rtt = theoretical_rtt_ms(distance)

        rows.append(
            (code, info["name"], distance, rtt)
        )
    rows.sort(key=lambda x: x[2])

    print("\nREGION            NAME                    DIST(km)  THEOR RTT(ms)")
    print("------------------------------------------------------------------")

    for code, name, dist, rtt in rows:
        print(
            f"{code:15} "
            f"{name:22} "
            f"{dist:8.0f} "
            f"{rtt:13.2f}"
        )


if __name__ == "__main__":
    main()

Example Output (Tokyo as Origin)

Select origin AWS region:

 1) us-east-2       Ohio
 2) us-east-1       N. Virginia
 3) us-west-1       N. California
 4) us-west-2       Oregon
 5) af-south-1      Cape Town
 6) ap-east-1       Hong Kong
 7) ap-south-2      Hyderabad
 8) ap-southeast-3  Jakarta
 9) ap-southeast-5  Malaysia
10) ap-southeast-4  Melbourne
11) ap-south-1      Mumbai
12) ap-southeast-6  New Zealand
13) ap-northeast-3  Osaka
14) ap-northeast-2  Seoul
15) ap-southeast-1  Singapore
16) ap-southeast-2  Sydney
17) ap-northeast-1  Tokyo
18) ca-central-1    Central
19) ca-west-1       West
20) eu-west-1       Ireland
21) eu-west-2       London
22) eu-west-3       Paris
23) eu-central-1    Frankfurt
24) eu-central-2    Zurich
25) eu-north-1      Stockholm
26) eu-south-1      Milan
27) eu-south-2      Spain
28) sa-east-1       São Paulo
29) me-central-1    UAE
30) me-south-1      Bahrain
31) il-central-1    Tel Aviv

Enter number: 17

REGION            NAME                    DIST(km)  THEOR RTT(ms)
------------------------------------------------------------------
ap-northeast-1  Tokyo                         0          0.00
ap-northeast-3  Osaka                       396          3.89
ap-northeast-2  Seoul                      1153         11.30
ap-east-1       Hong Kong                  2880         28.24
ap-southeast-1  Singapore                  5315         52.12
ap-southeast-5  Malaysia                   5322         52.19
ap-southeast-3  Jakarta                    5785         56.74
ap-south-2      Hyderabad                  6315         61.93
ap-south-1      Mumbai                     6724         65.94
us-west-2       Oregon                     7793         76.42
ap-southeast-2  Sydney                     7827         76.75
me-central-1    UAE                        7933         77.80
ca-west-1       West                       7993         78.39
eu-north-1      Stockholm                  8169         80.11
ap-southeast-4  Melbourne                  8191         80.33
us-west-1       N. California              8271         81.11
me-south-1      Bahrain                    8283         81.23
ap-southeast-6  New Zealand                8841         86.70
il-central-1    Tel Aviv                   9159         89.82
eu-central-1    Frankfurt                  9332         91.52
eu-west-2       London                     9559         93.74
eu-central-2    Zurich                     9578         93.93
eu-west-1       Ireland                    9585         93.99
eu-west-3       Paris                      9712         95.24
eu-south-1      Milan                      9715         95.27
ca-central-1    Central                   10348        101.49
us-east-2       Ohio                      10498        102.95
eu-south-2      Spain                     10762        105.54
us-east-1       N. Virginia               10924        107.13
af-south-1      Cape Town                 14732        144.47
sa-east-1       São Paulo                 18534        181.76

While Tokyo and Osaka are only a few hundred kilometers apart, regions in Europe and the US are 9,000–11,000 km away.

São Paulo is really far.

How Does Knowing Distance Change Design Discussions?

This CLI tool does not measure latency.

But aligning on distance as a shared assumption improves the quality of architectural discussions.

Making Latency Discussions More Realistic

Phrases like “overseas regions are slow” are common—but vague.

By looking at distance, you can say:

Tokyo → N. Virginia: ~11,000 km
Tokyo → Frankfurt: ~9,500 km

Once you see the numbers, it becomes obvious why real-time synchronization is difficult and why higher latency is expected.

Better Intuition for Multi-Region Design

In DR or global architecture discussions, “just deploy it in another region” often sounds easy.

Distance makes it clear that:

Synchronous replication is unrealistic
Asynchronous design is required

This understanding becomes intuitive, not just theoretical.

Easier to Explain to Non-Engineers

Latency and networking terms can get very technical.

But “these systems are 10,000 km apart” is easy for anyone to understand.

Distance becomes a powerful communication tool.

Realistic Multi-Region Choices in Japan

Japan has two AWS regions:

Tokyo (ap-northeast-1)
Osaka (ap-northeast-3)

The distance is roughly:

Tokyo → Osaka: ~400 km

This makes domestic DR a realistic option.

For unfamiliar regions or countries, visualizing distance like this can be surprisingly helpful.

Observed Latency

Distance is only a prerequisite.

Final decisions should always be based on measured latency.

You can spin up EC2 instances and measure manually—but that’s often overkill.
Instead, there are convenient tools available.

CloudPing (AWS Latency Monitoring)

https://cloudping.co/

CloudPing measures RTT by sending requests between region-based endpoints and visualizes the results in a matrix.

Conceptually:

Lambda functions in each region initiate TCP connections
Response times are measured and aggregated
Inter-region latency trends become visible

AWS Network Manager

AWS Network Manager allows you to monitor performance across the AWS global network.

Comparing Theoretical and Measured Values

Below is a comparison using Tokyo as the origin:

REGION	NAME	DIST (km)	THEOR RTT (ms)	CloudPing (ms)	AWS Network Manager (ms)
ap-northeast-3	Osaka	396	3.89	12.91	8.09
us-west-2	Oregon	7793	76.42	102.91	96.70
eu-west-1	Ireland	9585	93.99	205.86	202.00
sa-east-1	São Paulo	18534	181.76	261.56	260.00

CloudPing and AWS Network Manager show very similar results.

Remember: the theoretical RTT is the physical lower bound based only on fiber propagation speed.
It does not include routing, switching, or congestion.

Still, it’s useful for understanding latency trends.

Factors Beyond Distance

This article focused on physical distance, but real latency depends on more:

Internet-based connectivity
AWS global network routing
Dedicated connections via Direct Connect

Even with the best connectivity, distance never becomes zero.

Distance defines the lower bound, and everything else stacks on top of it.

How to Use This in Design

This distance-based approach is best used to decide whether a design is feasible at all.

Before measuring anything, distance helps you quickly assess:

Synchronous vs. asynchronous design
Impact on user experience
What can be optimized vs. what must be accepted
Whether a DR strategy is realistic

Using Distance in DR Design

Without considering distance, DR discussions often default to synchronous replication and vague RPO/RTO assumptions.

Just comparing:

Tokyo → Osaka: ~400 km
Tokyo → Virginia: ~11,000 km

makes it obvious:

Tokyo–Osaka: synchronous or near-synchronous may work
Tokyo–Virginia: async-only, latency-tolerant design required

This understanding clicks immediately.

Deciding on Region-Dependent Services

Some AWS services are tightly region-bound or limited cross-region.

If regions are close, you can trade off latency against complexity.
If they’re far apart, the design may simply not be viable.

Looking at distance first helps you judge architectural validity, not just technical possibility.

Combining with Measured Data

A practical workflow:

Use distance to understand the rough trend
Check CloudPing or AWS Network Manager
Decide if the latency is acceptable

This CLI tool supports step 1—it helps explain why differences exist before comparing numbers.

Conclusion

In this article, we looked at a simple tool for visualizing distance between AWS regions, along with services for observing real latency.

The tool itself is simple, but understanding physical distance helps turn vague assumptions into more concrete design discussions.

Distance is not latency—but it’s an important prerequisite.

Sharing this perspective makes region selection and DR design discussions more grounded and productive.

You may not use this tool every day, but sometimes it’s fun (and useful) to confirm:
“Yeah… that region really is far.”

I hope this article helps with your architecture decisions and learning.