Tag Archives: Analytics

Using Apache Sedona with AWS Glue to process billions of daily points from a geospatial dataset

2026-04-22 Ruan Roloff

Post Syndicated from Ruan Roloff original https://aws.amazon.com/blogs/big-data/using-apache-sedona-with-aws-glue-to-process-billions-of-daily-points-from-a-geospatial-dataset/

Data strategy can use geospatial data to provide organizations with insights for decision-making and operational optimization. By incorporating geospatial data (such as GPS coordinates, points, polygons and geographic boundaries), businesses can uncover patterns, trends, and relationships that might otherwise remain hidden across multiple industries, from aviation and transportation to environmental studies and urban planning. Processing and analyzing this geospatial data at scale can be challenging, especially when dealing with billions of daily observations.

In this post, we explore how to use Apache Sedona with AWS Glue to process and analyze massive geospatial datasets.

Introduction to geospatial data

Geospatial data is information that has a geographic component. It describes objects, events, or phenomena along with their location on the Earth’s surface. This data includes coordinates (latitude and longitude), shapes (points, lines, polygons), and associated attributes (such as the name of a city or the type of road).

Key types of geospatial geometries (and examples of each in parentheses) include:

Point – Represents a single coordinate (a weather station).
MultiPoint – A collection of points (bus stops in a city).
LineString – A series of points connected in a line (a river or a flight path).
MultiLineString – Multiple lines (multiple flight routes).
Polygon – A closed area (the boundary of a city).
MultiPolygon – Multiple polygons (national parks in a country).

Geospatial datasets come in different formats, each designed to store and represent different types of geographic information. Common formats for geospatial data are vector formats (Shapefile, GeoJSON), raster formats (GeoTIFF, ESRI Grid), GPS formats (GPX, NMEA), web formats (WMS, GeoRSS) among others.

Core concepts of Apache Sedona

Apache Sedona is an open-source computing framework for processing large-scale geospatial data. Built on top of Apache Spark, Sedona extends Spark’s capabilities to handle spatial operations efficiently. At its core, Sedona introduces several key concepts that enable distributed spatial processing. These include Spatial Resilient Distributed Datasets (SRDDs), which allow for the distribution of spatial data across a cluster, and Spatial SQL, which provides a familiar SQL-like interface for spatial queries. Some of the core capabilities of Apache Sedona are:

Efficient spatial data types like points, lines and polygons.
Spatial operations and functions such as ST_Contains (check if point is inside of a polygon), ST_Intersects (check if point is inside of a polygon), ST_H3CellIDs (geospatial indexing system developed by Uber, return the H3 cell ID(s) that contain the given point at the specified resolution).
Spatial joins to combine different spatial datasets.
Integration with Spark SQL (geospatial functions to run spatial SQL queries).
Spatial indexing techniques, such as quad-trees and R-trees, to optimize query performance.

For more information about the functions available in Apache Sedona, visit the official Sedona Functions documentation.

Use case

This use case consists of a global air traffic visualization and analysis platform that processes and displays real-time or historical aircraft tracking data on an interactive world map. Using unique aircraft identifiers from the International Civic Aviation Organization (ICAO), the system ingests trajectory records containing information such as geographic position (latitude and longitude), altitude, speed, and flight direction, then transforms this raw data into two complementary visual layers. The Flight Tracks Layer plots the routes traveled by each aircraft individually, allowing for the analysis of specific trajectories and navigation patterns. The Flight Density Layer uses hexagonal spatial indexing (H3) to aggregate and identify regions of higher air traffic concentration worldwide, revealing busy air corridors, aviation hubs, and high-density flight zones.

The dataset used for this use case is historical flight tracker data from ADSB.lol. ADSB.lol provides unfiltered flight tracker with a focus on open data. Data is also freely available via the API. The data contains a file per aircraft, a JSON gzip file containing the data for that aircraft for the day.

This is a JSON trace file format sample:

{
    icao: "0123ac", // hex id of the aircraft
    timestamp: 1609275898.495, // unix timestamp in seconds since epoch (1970)
    trace: [
        [ seconds after timestamp,
            lat,
            lon,
            altitude in ft or "ground" or null,
            ground speed in knots or null,
            track in degrees or null, (if altitude == "ground", this will be true heading instead of track)
            flags as a bitfield: (use bitwise and to extract data)
                (flags & 1 > 0): position is stale (no position received for 20 seconds before this one)
                (flags & 2 > 0): start of a new leg (tries to detect a separation point between landing and takeoff that separates flights)
                (flags & 4 > 0): vertical rate is geometric and not barometric
                (flags & 8 > 0): altitude is geometric and not barometric
             ,
            vertical rate in fpm or null,
            aircraft object with extra details or null,
            type / source of this position or null,
            geometric altitude or null,
            geometric vertical rate or null,
            indicated airspeed or null,
            roll angle or null
        ],
    ]
}

For this use case, this is a simplified schema of the dataset after processing:

icao - Unique aircraft identifier
timestamp - Epoch timestamp of the observation (converted to readable format)
trace.lat / trace.lon - Latitude and longitude of the aircraft
trace.altitude - Aircraft altitude
trace.ground_speed - Ground speed
geometry - Geospatial geometry of the observation point (Point)

Solution overview

This solution enables aircraft tracking and analysis. The data can be visualized on maps and used for aviation management and safety applications. The process begins with data acquisition, extracting the compressed JSON files from TAR archives, then transforms this raw data into geospatial objects, aggregating them into H3 cells for efficient analysis. The processed data schema includes ICAO aircraft identifiers, timestamps, latitude/longitude coordinates, and derived fields such as H3 cell identifiers and point counts per cell. This structure allows detailed tracking of individual flights and aggregate analysis of traffic patterns. For visualization, you can generate density maps using the H3 grid system and create visual representations of individual flight tracks. The architecture data flow is as follows:

Data ingestion – Aircraft observation data stored as JSON compressed files in Amazon Simple Storage Service (Amazon S3).
Data processing – AWS Glue jobs using Apache Sedona for geospatial processing.
Data visualization – Spark SQL with Sedona’s spatial functions to extract insights and export data to visualize the information in a map on Kepler.gl.

The following figure illustrates this solution.

AWS architecture diagram showing a geospatial data processing pipeline.

Prerequisites

You will need the following for this solution:

An AWS Account and a user with AWS Console access.
Access to a Linux terminal and the AWS Command Line Interface (AWS CLI).
An IAM role for AWS Glue with list, read, and write permissions for Amazon S3 buckets.
An Amazon S3 Bucket for flight files. For this example, name the bucket blog-sedona-nessie-<account_number>-<aws_region>, using your account number and region.
An Amazon S3 bucket for artifacts and Sedona libraries. For this example, name the bucket blog-sedona-artifacts-<account_number>-<aws_region>, using your account number and region.
Download a day of historical data from ADSB.lol. In our examples, we used v2025.05.29-planes-readsb-prod-0tmp.tar.aa and v2025.05.29-planes-readsb-prod-0tmp.tar.ab.
Download the Apache Sedona libraries. The example was created using sedona-spark-shaded-3.5_2.12-1.7.1.jar and geotools-wrapper-1.7.1-28.5.jar.
Download the AWS Glue script from AWS Sample to process the geospatial data.
Review the AWS Glue security best practices, especially IAM least-privilege, encryption for sensitive data at rest and in transit, and configuring VPC Endpoints to prevent data from routing through the public internet.

Solution walkthrough

From now on, executing the next steps will incur costs on AWS. This step-by-step walkthrough demonstrates an approach to processing and analyzing large-scale geospatial flight data using Apache Sedona and Uber’s H3 spatial indexing system, using AWS Glue for distributed processing and Apache Sedona for efficient geospatial computations. It explains how to ingest raw flight data, transform it using Sedona’s geospatial functions, and index it with H3 for optimized spatial queries. Finally, it also demonstrates how to visualize the data using Kepler.gl. For data processing, it is possible to use both Glue scripts and Glue notebooks. In this post, we will focus only on Glue scripts.

Upload the Apache Sedona libraries to Amazon S3

Open your OS terminal command line.

Create a folder to download the Sedona libraries and name it jar.


	# Create a directory for the Sedona libraries (JARs files)
	mkdir jar
	# Go to the folder JARs folder
	cd jar

Download the Apache Sedona libraries.


	# Download required Sedona libraries (JARs files)
	wget https://repo1.maven.org/maven2/org/apache/sedona/sedona-spark-shaded-3.5_2.12/1.7.1/sedona-spark-shaded-3.5_2.12-1.7.1.jar
	wget https://repo1.maven.org/maven2/org/datasyslab/geotools-wrapper/1.7.1-28.5/geotools-wrapper-1.7.1-28.5.jar

Upload the Sedona libraries (JARs files) to Amazon S3. In this example, we use the S3 path s3://aws-blog-post-sedona-artifacts/jar/.


	# Upload the JARs files to Amazon S3 bucket
	aws s3 cp . s3://blog-sedona-artifacts-<account_number>-<aws_region>/jar/ --recursive

Your Amazon S3 folder should now look similar to the following image:

Amazon S3 console screenshot displaying the jar folder contents in blog-sedona-artifacts bucket.

Download and upload the geospatial data to Amazon S3

Open your OS terminal command line.

Create a folder to download the flight files and name it adsb_dataset.

		# Create a directory for download the geospatial flight files
		mkdir adsb_dataset
		# Go to the folder for geospatial flight files
		cd adsb_dataset

Download the flight files data from adsblol GitHub repository.

	# Download the geospatial flight files in the folder created
	wget https://github.com/adsblol/globe_history_2025/releases/download/v2025.05.29-planes-readsb-prod-0tmp/v2025.05.29-planes-readsb-prod-0tmp.tar.aa
	wget https://github.com/adsblol/globe_history_2025/releases/download/v2025.05.29-planes-readsb-prod-0tmp/v2025.05.29-planes-readsb-prod-0tmp.tar.ab

Extract the flight files.

	# Combine the two the tar files together
	cat v2025.05.29* >> combined.tar
	# Extract the json flight files from the tar file
	tar xf combined.tar

Copy the flight files to Amazon S3. In this case, we are using the S3 folder: s3://blog-sedona-nessie-<account_number>-<aws_region>/raw/adsb-2025-05-28/traces/.

	# Copy the json flight files to Amazon S3
	aws s3 cp ./traces/ s3://blog-sedona-nessie-<account_number>-<aws_region>/raw/adsb-2025-05-28/traces/ --recursive

Your Amazon S3 folder should now look similar to the following image.

Amazon S3 console showing JSON trace files in the path raw/adsb-2025-05-28/traces/00/.

Create an AWS Glue job and set up the job

Now, we are ready to define the AWS Glue job using Apache Sedona to read the geospatial data files. To create a Glue job:

Open the AWS Glue console.
On the Notebooks page, choose Script editor.

AWS Glue Studio jobs creation interface showing three job creation methods: Visual ETL with data flow interface, Notebook for interactive coding, and Script editor for code authoring

On the Script screen, for the engine, choose Spark, then select the option Upload script.
Choose Choose file. Find the process_sedona_geo_track.py file, then choose Create script.

Script creation dialog box with Spark engine selected. Upload script option is active, showing successfully uploaded file process_sedona_geo_track.py.

Rename the job from Untitled to process_sedona_geo_track.
Choose Save.
Now, let’s set up the AWS Glue job. Choose Job Details.
Choose the IAM Role created to be used with Glue. For this example, we use blog-glue.
Set the Glue version to Glue 5.0 and the Worker type as needed. For this example, G.1X is sufficient, but we use G.2X to speed up processing.

AWS Glue job details configuration page for process_sedona_geo_track.

Now, let’s import the libraries for Apache Sedona.
In the Dependent JARs path, type the path of the JAR files for Apache Sedona that you uploaded in the preceding steps. For this example, we used s3://blog-sedona-artifacts-<account_number>-<aws_region>/jar/sedona-spark-shaded-3.5_2.12-1.7.1.jar,s3://blog-sedona-artifacts-<account_number>-<aws_region>/jar/geotools-wrapper-1.7.1-28.5.jar
In Additional Python modules path, enter the modules for Apache Sedona: apache-sedona==1.7.1,geopandas==0.13.2,shapely==2.0.1,pyproj==3.6.0,fiona==1.9.5,rtree==1.2.0

ob libraries configuration section showing Dependent JARs path pointing to S3 bucket.

In the Job parameters section, in the Key field, type —BUCKET_NAME. For its Value, enter your bucket name. In this example, ours is blog-sedona-nessie-<account_number>-<aws_region>.

ob parameters configuration interface showing key-value pair with --BUCKET_NAME parameter.

Choose Save.

Processing the geospatial flights data

Before we run the job, let’s understand how the code works. First, import the Apache Sedona libraries:

import json 
import gzip 
from sedona.spark import SedonaContext

Next, initialize the Sedona context using an existing Spark session:

sedona = SedonaContext.create(spark)

After that, create a function for handling compressed JSON data:

def parse_gzip_json(byte_content):
        try:
            decompressed = gzip.decompress(byte_content)
            return json.loads(decompressed.decode('utf-8'))
        except Exception as e:
            print(f"Error during gzip parse: {str(e)}")
            return None

Add a function to transform raw tracking data into a structured format suitable for a valid coordinates process:

def flatten_records(json_obj):
    records = []
    if "trace" in json_obj and isinstance(json_obj["trace"], list):
        for point in json_obj["trace"]:
            if len(point) >= 3:
                lat, lon = float(point[1]), float(point[2])
                if -90 <= lat <= 90 and -180 <= lon <= 180:
                    records.append(Row(
                        icao=json_obj.get("icao", None),
                        timestamp=json_obj.get("timestamp", None),
                        lat=lat,
                        lon=lon
                    ))
    return records

The flat_rdd variable applies these functions to the structured data from the original gzipped JSON. Each element in this RDD is a Row object representing a single data point from an aircraft’s trace, with fields for ICAO, timestamp, latitude, and longitude.

flat_rdd = raw_rdd.map(lambda x: parse_gzip_json(x[1])).filter(lambda x: x is not None).flatMap(flatten_records)

The ADSB trace files contain a deeply nested JSON structure where the trace field holds an array of mixed-type arrays, compressed in Gzip format. For this specific case, developing a UDF represented one of the most practical and efficient solutions. Since Gzip is a non-splittable format, Spark is unable to parallelize processing, constraining both methods to a single worker per file and processing the data multiple times across JVM decompression, full JSON parsing, and subsequent re-parsing operations. The UDF bypasses all of this by reading raw bytes and doing everything in a single Python pass: decompress → parse → extract → validate, returning only the small set of needed fields directly to Spark.

The Spark SQL query processes geographic trace data using the H3 hexagonal grid system, converting point data into a regularized hexagonal grid that can help identify areas of high point density. A resolution of 5 was adopted, producing hexagons of approximately 253 km² (roughly the same size as the city of Edinburgh, Scotland, which is approximately 264 km²), for its ability to effectively capture route density patterns at the city and metropolitan level.

h3_traces_df = spark.sql("""
WITH base_h3 AS (
    SELECT
        ST_H3CellIDs(geometry, 5, false)[0] AS h3_index,
        lat,
        lon
    FROM traces
)
SELECT
    COUNT(*) AS num, -- Count points in each H3 cell
    h3_index,
    AVG(lon) AS center_lon,
    AVG(lat) AS center_lat
FROM base_h3
GROUP BY h3_index
""")

Finally, this code prepares the datasets for visualization purposes. The first dataset is based on the aircraft unique identifier. The complete dataset for a single day can contain more than 80 million data points. A random sampling rate of 0.1% was applied, which proves sufficient to illustrate route density patterns without overwhelming the Kepler.gl browser renderer. The second dataset aggregates trace points into hexagonal spatial cells (result from the query above).

points_viz_sampled = df_points.select(
    col("icao"), # Aircraft unique identifier (24-bit address)
    col("timestamp").cast("double").alias("timestamp"),
    col("lat").cast("double").alias("lat"),
    col("lon").cast("double").alias("lon")
).sample(False, 0.001)

h3_viz_csv = h3_traces_df.select(
    col("num").alias("point_count"),
    col("h3_index").cast("string").alias("h3_index"),
    col("center_lon"),
    col("center_lat")
)

Now that we understand the code, let’s run it.

Open the AWS Glue console.
On the ETL jobs >> Notebooks page, choose the job name process_sedona_geo_track.
Choose Run.

Python script editor showing import statements for process_sedona_geo_track job.

Now, it is possible to monitor the job by choosing the Runs tab.
It may take a few minutes to run the entire job. It took nearly 8 minutes to process approximately 2.50 GB (67,540 compressed files) with 20 DPUs. After the job is processed, you should see your job with the status Succeeded.

Job runs monitoring dashboard showing successful execution on June 5, 2025, running from 12:28:03 to 12:36:37 with 8 minutes 19 seconds duration.

Now your data should be saved for a preview visualization demo in a folder named s3://blog-sedona-nessie-<account_number>-<aws_region>/visualization/.

Performance insights

The workload characterization of this job reveals a CPU-intensive profile, primarily because of the processing of small binary files with GZIP compression and subsequent JSON parsing. Given the inherent nature of this pipeline, which includes Python UDF serialization and partial single-partition write stages, linear scaling does not yield proportional performance gains. The following table presents an analysis of AWS Glue configurations, evaluating the trade-off between computational capacity, execution duration, and associated costs:

Duration	Capacity (DPUs)	Worker type	Glue version	Estimated Cost*
10 m 7 s	32 DPUs	G.1X	5	$2.34
11 m 50 s	10 DPUs	G.1X	5	$0.88
19 m 7 s	4 DPUs	G.1X	5	$0.59
8 m 19 s	20 DPUs	G.2X	5	$1.32

*Estimated Cost = DPUs x Duration (hours) x $0.44 per DPU-hour (us-east-1)

Visualizing and analyzing geospatial data with Kepler.gl

Kepler.gl is an open-source geospatial analysis tool developed by Uber with code available at Github. Kepler.gl is designed for large-scale data exploration and visualization, offering multiple map layers, including point, arc, heatmap, and 3D hexagon. It supports various file formats like CSV, GeoJSON, and KML. In this use case, we will use Kepler.gl to present interactive visualizations that illustrate flight patterns, routes, and densities across global airspace.

Downloading the geospatial files

Before we can view the graph, we will need to download the flight files to our local machine, unzip them, and rename them (to make it easier to identify the files).

Open your OS terminal command line.

Create the folders to download the data processed in the steps before. In this case, we create kepler and kepler_csv.

	#create kepler folders: first folder is to download the files,
	#second folder is to organize the files to use in the next step
	mkdir kepler
	mkdir kepler_csv

Replace the bracketed variables with your account and directory information, then download all the CSV files.

	#copy the files from Amazon S3 to local machine
	aws s3 cp s3://blog-sedona-nessie-<account_number>-<aws_region>/visualization/ /<user_directory>/kepler --recursive

Extract the files, rename them, and move them to another folder.

	# Extract the files processed by Spark and Sedona
	gzip -d ./kepler/kepler_h3_density/*.gz
	gzip -d ./kepler/kepler_track_points_sample/*.gz
	
	# Rename the Spark output files to more readable names
	cd ./kepler/kepler_h3_density/
	ls
	mv part-00000-*.csv kepler_h3_density.csv
	cd ..
	
	cd ./kepler/kepler_track_points_sample/
	ls
	mv part-00000-*.csv kepler_track_points_sample.csv
	cd ..
	
	# Ensure the output folder exists
	mkdir -p ../kepler_csv
	
	# Copy the renamed CSV files to the folder that will be used as input in kepler.gl
	cp ./kepler/kepler_h3_density/*.csv ../kepler_csv
	cp ./kepler/kepler_track_points_sample/*.csv ../kepler_csv

Your kepler_csv folder should look similar to the return of the command below.

	#list the files in the kepler_csv directory
	ls -l
	total 11684
	-rw-rw-r-- 1 ec2-user ec2-user 8630110 Jun 12 14:47 kepler_h3_density.csv
	-rw-rw-r-- 1 ec2-user ec2-user 3331763 Jun 12 14:47 kepler_track_points_sample.csv

Visualizing the data in a graph

Now that you have saved the data to your local machine, you can analyze the flight data through interactive map graphics. To import the data into the Kepler.gl web visualization tool:

Open the Kepler.gl Demo web application.
Load data into Kepler.gl:
1. Choose Add Data in the left panel.
2. Drag and drop both CSV files (flight_points and h3_density) into the upload area.
3. Confirm that both datasets are loaded successfully.
Delete all layers.
Create the Flight Density Layer:
1. Choose Add Layer in the left panel.
2. In Basic, choose H3 as the layer type, then add the following configuration:
  1. Layer Name: Flight Density
  2. Data Source: kepler_h3_density.csv
  3. Hex ID: h3_index
3. In the Fill Color section:
  1. Color: point_count
  2. Color Scale: Quantile.
  3. Color Range: Choose a blue/green gradient.
4. Set Opacity to 0.7.
5. In the Coverage section, set it to 0.9.
Create the Flight Tracks Layer:
1. Choose Add Layer in the left panel.
2. In Basic, choose Point as the layer type, then add the following configuration:
  1. Layer Name: Flight Tracks
  2. Data Source: kepler_track_points_sample.csv
  3. Columns:
    1. Latitude: lat
    2. Longitude: lon
3. In the Fill Color section:
  1. Solid Color: Orange
  2. Opacity: 0.3
4. Set the Point’s Radius to 1
The layers should look similar to the following figure.

Kepler.gl layer configuration panel for Flight Density H3 layer using kepler_h3_density.csv data source.

The graph visualization should now show flight density through color-coded hexagons, with individual flight tracks visible as orange points:

Kepler.gl interactive map visualization displaying global flight density heatmap. High-density areas shown in yellow over North America, particularly the United States.

There you go! Now that you have knowledge about geospatial data and have created your first use case, take the opportunity to do some analysis and learn some interesting facts about flight patterns.

It is possible to experiment with other interesting types of analysis in Kepler.gl, such as Time Playback.

Clean up

To clean up your resources, complete the following tasks:

Delete the AWS Glue job process_sedona_geo_track.
Delete content from the Amazon S3 buckets: blog-sedona-artifacts-<account_number>-<aws_region> and blog-sedona-nessie-<account_number>-<aws_region>.

Conclusion

In this post, we showed how processing geospatial data can present significant challenges due to its complex nature (from big data to data structure format). For this use case of flight trackers, it involves vast amounts of information across multiple dimensions such as time, location, altitude, and flight paths, however, the combination of Spark’s distributed computing capabilities and Sedona’s optimized geospatial functions helps overcome those challenges. The spatial partitioning and indexing features of Sedona, coupled with Spark’s framework, enable us to perform complex spatial joins and proximity analyses efficiently, simplifying the overall data processing workflow.

The serverless nature of AWS Glue eliminates the need for managing infrastructure while automatically scaling resources based on workload demands, making it an ideal platform for processing growing volumes of flight data. As the volume of flight data grows or as processing requirements fluctuate, with AWS Glue, you can quickly adjust resources to meet demand, ensuring optimal performance without the need for cluster management.

By converting the processed results into CSV format and visualizing them in Kepler.gl, it is possible to create interactive visualizations that reveal patterns in flight paths, and you can efficiently analyze air traffic patterns, routes, and other insights. This end-to-end solution demonstrates how a modern data strategy in AWS with the support of open-source tools can transform raw geospatial data into actionable insights.

About the authors

Migrate third-party and self-managed Apache Kafka clusters to Amazon MSK Express brokers with Amazon MSK Replicator

2026-04-20 Ankita Mishra

Post Syndicated from Ankita Mishra original https://aws.amazon.com/blogs/big-data/migrate-third-party-and-self-managed-apache-kafka-clusters-to-amazon-msk-express-brokers-with-amazon-msk-replicator/

Migrating Apache Kafka workloads to the cloud often involves managing complex replication infrastructure, coordinating application cutovers with extended downtime windows, and maintaining deep expertise in open-source tools like Apache Kafka’s MirrorMaker 2 (MM2). These challenges slow down migrations and increase operational risk. Amazon MSK Replicator addresses these challenges, enabling you to migrate your Kafka deployments (referred to as “external” Kafka clusters) to Amazon MSK Express brokers with minimal operational overhead and reduced downtime. MSK Replicator supports data migration from Kafka deployments (version 2.8.1 or later) that have SASL/SCRAM authentication enabled – including Kafka clusters running on-premises, on AWS, or other cloud providers, as well as Kafka-protocol-compatible services like Confluent Platform, Avien, RedPanda, WarpStream, or AutoMQ when configured with SASL/SCRAM authentication.

In this post, we walk you through how to replicate Apache Kafka data from your external Apache Kafka deployments to Amazon MSK Express brokers using MSK Replicator. You will learn how to configure authentication on your external cluster, establish network connectivity, set up bidirectional replication, and monitor replication health to achieve a low-downtime migration.

How it works

MSK Replicator is a fully managed serverless service that replicates topics, configurations, and offsets from cluster to cluster. It alleviates the need to manage complex infrastructure or configure open-source tools.

Before MSK Replicator, customers used tools like MM2 for migrations. These tools lack bi-directional topic replication when using the same topic names, creating complex application architectures to consume different topics on different clusters. Custom replication policies in MM2 can allow identical topic names, but MM2 still lacks bidirectional offset replication because the MM2 architecture requires producers and consumers to run on the same cluster to replicate offsets. This created complex migrations that required either migrating consumers before producers or big-bang migrations migrating all applications at once. When customers run into issues during the migration, the rollback process is error-prone and introduces large amounts of duplicate message processing due to the lack of consumer group offset synchronization. These approaches create risk and complexity for customers that make migrations difficult to manage.

MSK Replicator addresses these problems by supporting bidirectional replication of data and enhanced consumer group offset synchronization. MSK Replicator copies topics and offsets from an external Kafka cluster to MSK, allowing you to preserve the same topic and consumer group names on both clusters. MSK Replicator also supports creating a second Replicator instance for bidirectional replication of both data and enhanced offset synchronization, allowing producers and consumers to run independently on different Kafka clusters. Data published or consumed on the Amazon MSK cluster will be replicated back to the external cluster by the second Replicator. This feature works when producers and consumers are migrated regardless of order without worrying about dependencies between applications.

Because MSK Replicator provides bidirectional data replication and enhanced consumer group offset synchronization, you can move producers and consumers at your own pace without data loss. This reduces migration complexity, allowing you to migrate applications between your external Kafka cluster and Amazon MSK regardless of order. If you run into problems during the migration, enhanced offset synchronization allows you to roll back changes by moving applications back to the external Kafka cluster, where they restart from the latest checkpoint from the Amazon MSK cluster.

For example, consider three applications:

The “Orders” application, which accepts incoming orders and writes them to the orders Kafka topic
The “Order status” application, which reads from the “orders” Kafka topic and writes status updates to the order_status topic
The “Customer notification” application, which reads from the order_status topic and notifies customers when status changes

MSK Replicator enables these applications to be migrated between an on-premises Apache Kafka cluster and an Amazon MSK Express cluster with low downtime and no data loss, regardless of order. The “Order status” application can migrate first, receive orders from the on-premises “Orders” application, and send status updates to the on-premises “Customer notification” application. If issues arise during the migration, the “Order status” application can roll back to the on-premises cluster and its consumer group offsets for the orders topic will be ready for it to pick up from where it left off on the Amazon MSK cluster.

MSK Replicator supports data distribution across hybrid and multi-cloud environments for analytics, compliance, and business continuity. It is also configured for disaster recovery scenarios where Amazon MSK Express serves as a resilient target for your external Kafka clusters.

If you are currently using MM2 for replication, see Amazon MSK Replicator and MirrorMaker2: Choosing the right replication strategy for Apache Kafka disaster recovery and migrations to understand which solution best fits your use case.

Solution overview

MSK Replicator supports Kafka deployments running version 2.8.1 or later as a source, including 3rd party managed Kafka services, self-managed Kafka, and on-premises or third-party cloud-hosted Kafka. MSK Replicator automatically handles data transfer, uses SASL/SCRAM authentication with SSL encryption, and maintains consumer group positions across both clusters. If you do not use SASL/SCRAM today, this can be configured as a new listener used for MSK Replicator allowing current clients to use their existing authentication mechanisms alongside MSK Replicator.

Prerequisites

To follow along with this walkthrough, you need the following resources in place:

A source Kafka cluster using Kafka version 2.8.1 or above
Network connectivity between your external Kafka cluster and AWS (for example, using AWS Direct Connect, Site-to-Site VPN, or Amazon Virtual Private Cloud (VPC) peering or AWS Transit Gateway for connections between AWS VPCs) so that MSK Replicator can reach your source brokers
SASL/SCRAM authentication configured on your external cluster (SHA-256 or SHA-512), which MSK Replicator uses to authenticate with external clusters
An admin user configured on your external cluster with permissions to describe the external cluster and create and modify users/ACLs
An Amazon MSK Express cluster with IAM authentication enabled to serve as your target
AWS Secrets Manager configured to store your SASL/SCRAM credentials for the external cluster so that MSK Replicator can securely retrieve them at runtime
An Amazon CloudWatch log group for MSK Replicator logs
Appropriate IAM permissions for creating and managing MSK Replicator resources

Setting up replication

Step 1: Configure network connectivity

You can set up network connectivity between your external Kafka cluster and your AWS VPC using methods such as AWS Direct Connect for dedicated network connections, AWS Site-to-Site VPN for encrypted connections over the internet, and AWS VPC peering or AWS Transit Gateway for connections between AWS VPCs. Verify that IP routing and DNS resolution are properly configured between your external cluster and AWS.

To verify IP routing and DNS resolution, connect to your external Kafka cluster from inside of your VPC by using the Kafka CLI to list topics on the external cluster. If you can list topics from your VPC using the Kafka CLI, this means DNS resolution and IP routing are working successfully. If it fails, work with your network admins to troubleshoot network connectivity issues.

Step 2: Configure external cluster

In this step, you will set up authentication on your external Kafka cluster and store the credentials in AWS Secrets Manager so that MSK Replicator can connect securely.

Configure authentication

Using the external cluster admin user, configure SASL/SCRAM authentication for MSK Replicator using SHA-256 or 512 on your external Kafka cluster. Create a SASL/SCRAM user for MSK Replicator and give the user the following ACL permissions:

Topic operations – Alter, AlterConfigs, Create, Describe, DescribeConfigs, Read, Write
Group operations – Read, Describe
Cluster operations – Create, ClusterAction, Describe, DescribeConfigs

Configure SecretsManager

AWS Secrets Manager stores your SASL/SCRAM credentials securely so that MSK Replicator can retrieve them at runtime. The secret must use JSON format and have the following keys:

username – The SCRAM username that you configured in the authentication step above
password – The SCRAM password that you configured in the authentication step above
certificate – The public root CA certificate (the top-level certificate authority that issued your cluster’s TLS certificate) and the intermediate CA chain (intermediate certificates between the root and your cluster’s certificate), used for SSL handshakes with the external cluster

Optionally, you may create separate secrets for SCRAM credentials and the SSL certificate. This approach is useful when secrets for SCRAM credentials and certificates are provisioned in different stages, such as in Infrastructure as Code (IaC) pipelines.

Retrieve the cluster ID

As the admin user, use the Kafka CLI tools to retrieve the cluster ID of your external cluster. Run the following command, replacing your-broker-host:9096 with the address of one of your external cluster’s bootstrap servers:

bin/kafka-cluster.sh cluster-id --bootstrap-server your-broker-host:9096 --config admin.properties

The command returns a cluster ID string such as lkc-abc123. Take note of this value because you will need it when creating the replicator in Step 4.

Step 3: Create your MSK Express target cluster

With your external cluster configured, you can now set up the target. Create an Amazon MSK Express cluster with IAM authentication enabled. Make sure that the cluster is in subnets that have access to AWS Secrets Manager endpoints. See Get started using Amazon MSK for more information on creating an MSK cluster.

Step 4: Create the replicator

Now that both clusters are ready, you can connect them by setting up the MSK Replicator with the appropriate IAM role and replication configuration.

Set up an IAM role for MSK Replicator

MSK Replicator needs an IAM role to interact with your MSK Express cluster and retrieve secrets. Set up a service execution IAM role with a trust policy allowing kafka.amazonaws.com and attach the AWSMSKReplicatorExecutionRole permissions policy. Take note of the role ARN for creating the replicator.

Create and attach a policy for accessing your Secrets Manager secrets and reading/writing data in your MSK cluster. See Creating roles and attaching policies (console) for more information on creating IAM roles and policies.

The following is an example policy for reading and writing data to your MSK cluster and reading KMS-encrypted Secrets Manager secrets:

{ 
    "Version": "2012-10-17", 
    "Statement": [ 
        { 
            "Sid": "SecretsManagerAccess", 
            "Effect": "Allow", 
            "Action": [ 
                "secretsmanager:GetSecretValue", 
                "secretsmanager:DescribeSecret" 
            ], 
            "Resource": [ 
                "<SCRAM_SECRET_ARN>", 
                "<CERT_SECRET_ARN>" 
            ] 
        }, 
        { 
            "Sid": "KMSDecrypt", 
            "Effect": "Allow", 
            "Action": "kms:Decrypt", 
            "Resource": "<SECRETSMANAGER_KMS_KEY_ARN>" 
        }, 
        { 
            "Sid": "TargetClusterAccess", 
            "Effect": "Allow", 
            "Action": [ 
                "kafka-cluster:Connect", 
                "kafka-cluster:DescribeCluster", 
                "kafka-cluster:AlterCluster", 
                "kafka-cluster:DescribeClusterDynamicConfiguration", 
                "kafka-cluster:AlterClusterDynamicConfiguration", 
                "kafka-cluster:DescribeTopic", 
                "kafka-cluster:CreateTopic", 
                "kafka-cluster:AlterTopic", 
                "kafka-cluster:DescribeTopicDynamicConfiguration", 
                "kafka-cluster:AlterTopicDynamicConfiguration", 
                "kafka-cluster:WriteData", 
                "kafka-cluster:WriteDataIdempotently", 
                "kafka-cluster:ReadData", 
                "kafka-cluster:DescribeGroup", 
                "kafka-cluster:AlterGroup" 
            ], 
            "Resource": [ 
                "arn:aws:kafka:<REGION>:<ACCOUNT_ID>:cluster/<MSK_CLUSTER_NAME>*/*", 
                "arn:aws:kafka:<REGION>:<ACCOUNT_ID>:topic/<MSK_CLUSTER_NAME>/*", 
                "arn:aws:kafka:<REGION>:<ACCOUNT_ID>:group/<MSK_CLUSTER_NAME>*/*" 
            ] 
        }, 
        { 
            "Sid": "CloudWatchLogsAccess", 
            "Effect": "Allow", 
            "Action": [ 
                "logs:CreateLogStream", 
                "logs:PutLogEvents", 
                "logs:DescribeLogStreams" 
            ], 
            "Resource": "<MSK_REPLICATOR_LOG_GROUP_ARN>" 
        } 
    ] 
}

Create the replicator for external to MSK replication

Use the AWS CLI, API, or Console to create your replicator. Here’s an example using the AWS CLI:

aws kafka create-replicator \
  --replicator-name external-to-msk \
  --service-execution-role-arn "arn:aws:iam::123456789012:role/MSKReplicatorRole" \
  --kafka-clusters file://./kafka-clusters.json \
  --replication-info-list file://./replication-info.json \
  --log-delivery file://./log-delivery.json \
  --region us-east-1

The kafka-clusters.json file defines the source and target Kafka cluster connection information, replication-info.json specifies which topics to replicate and how to handle consumer group offset synchronization, and log-delivery.json specifies the CloudWatch logging configuration. The following tables describe the required parameters:

CLI inputs:

CLI Parameter	Description	Example
replicator-name	The name of the replicator	external-to-msk
service-execution-role-arn	The ARN for the service execution IAM role you created	arn:aws:iam::123456789012:role/MSKReplicatorRole
kafka-clusters	The Kafka cluster connection info	See below
replication-info-list	The replication configuration	See below
log-delivery	The logging configuration	See below

Key kafka-clusters.json inputs:

CLI Parameter	Description	Example
ApacheKafkaClusterId	The cluster ID retrieved in Step 2	lkc-abc123
RootCaCertificate	The Secrets Manager ARN containing the public CA certificate and intermediate CA chain	arn:aws:secretsmanager:<REGION>:<ACCOUNT_ID>:secret:my-cert
MskClusterArn	The ARN for the MSK Express cluster	arn:aws:kafka:<REGION>:<ACCOUNT_ID>:cluster/my-cluster/abc-123
SecretArn	The Secrets Manager ARN containing the SASL/SCRAM username and password	arn:aws:secretsmanager:<REGION>:<ACCOUNT_ID>:secret:my-creds
SecurityGroupIds	The security group IDs for MSK Replicator	sg-0123456789abcdef0

Key replication-info.json inputs:

CLI Parameter	Description	Example
TargetCompressionType	The compression type to use for replicating data	LZ4
TopicsToReplicate	The list of topics to replicate (use [“.*”] for all topics)	[“my-topic”]
ConsumerGroupsToReplicate	The list of consumer groups to replicate	[“my-group”]
StartingPosition	The point in the Kafka topics to begin replication from (either EARLIEST or LATEST)	EARLIEST
ConsumerGroupOffsetSyncMode	Whether or not to use enhanced bidirectional consumer group offset synchronization	ENHANCED

Note that startingPosition is set to EARLIEST in the configuration below, which means the replicator begins reading from the oldest available offset on each topic. This is the recommended setting for migrations to avoid data loss.

Key log-delivery.json inputs:

CLI Parameter	Description	Example
Enabled	Allows you to enable CloudWatch logging	true
LogGroup	The CloudWatch logs log group name to log to	/msk/replicator/my-replicator

Additional log delivery methods for Amazon S3 and Amazon Data Firehose are supported. In this post, we use CloudWatch logging.

The configs should look like the following for external to MSK replication.

kafka-clusters.json:

[ 
  { 
    "ApacheKafkaCluster": { 
      "ApacheKafkaClusterId": "lkc-abc123", 
      "BootstrapBrokerString": "broker1.example.com:9096" 
    }, 
    "ClientAuthentication": { 
      "SaslScram": { 
        "Mechanism": "SHA512", 
        "SecretArn": "arn:aws:secretsmanager:<REGION>:<ACCOUNT_ID>:secret:my-creds" 
      } 
    }, 
    "EncryptionInTransit": { 
      "EncryptionType": "TLS", 
      "RootCaCertificate": "arn:aws:secretsmanager:<REGION>:<ACCOUNT_ID>:secret:my-cert" 
    } 
  }, 
  { 
    "AmazonMskCluster": { 
      "MskClusterArn": "arn:aws:kafka:<REGION>:<ACCOUNT_ID>:cluster/my-cluster/abc-123" 
    }, 
    "VpcConfig": { 
      "SecurityGroupIds": ["sg-0123456789abcdef0"], 
      "SubnetIds": ["subnet-abc123", "subnet-abc124", "subnet-abc125"] 
    } 
  } 
]

replication-info.json:

[ 
  { 
    "SourceKafkaClusterId": "lkc-abc123", 
    "TargetKafkaClusterArn": "arn:aws:kafka:<REGION>:<ACCOUNT_ID>:cluster/my-cluster/abc-123", 
    "TargetCompressionType": "LZ4", 
    "TopicReplication": { 
      "TopicsToReplicate": ["my-topic"], 
      "CopyTopicConfigurations": true, 
      "CopyAccessControlListsForTopics": true, 
      "DetectAndCopyNewTopics": true, 
      "StartingPosition": {"Type": "EARLIEST"}, 
      "TopicNameConfiguration": {"Type": "IDENTICAL"} 
    }, 
    "ConsumerGroupReplication": { 
      "ConsumerGroupsToReplicate": ["my-group"], 
      "SynchroniseConsumerGroupOffsets": true, 
      "DetectAndCopyNewConsumerGroups": true, 
      "ConsumerGroupOffsetSyncMode": "ENHANCED" 
    } 
  } 
]

log-delivery.json:

{ 
  "ReplicatorLogDelivery": {
     "CloudWatchLogs": {
       "Enabled": true, 
       "LogGroup": "<LOG_GROUP_NAME>"
     }
  } 
}

Configure bidirectional replication from MSK to the external cluster

To enable bidirectional replication, create a second replicator that replicates in the opposite direction. Use the same IAM role and network configuration from Step 4, but swap the source and target. Replace SourceKafkaClusterId with TargetKafkaClusterId and TargetKafkaClusterArn with SourceKafkaClusterArn in a new msk-to-external-replication-info.json file:

aws kafka create-replicator \
  --replicator-name msk-to-external \
  --service-execution-role-arn "arn:aws:iam::123456789012:role/MSKReplicatorRole" \
  --kafka-clusters file:///./kafka-clusters.json \
  --replication-info-list file:///./msk-to-external-replication-info.json \
  --log-delivery file:///./log-delivery.json \
  --region us-east-1

Monitoring replication health

Monitor your replication using Amazon CloudWatch metrics. Three key metrics to understand are MessageLag, SumOffsetLag, and ReplicationLatency. MessageLag measures how far behind the replicator is from the external cluster in terms of messages not yet replicated, while SumOffsetLag measures how far behind a consumer group is from the latest message in a topic. ReplicationLatency is the amount of latency between the source and target clusters in data replication. When the three reach a sustained low level, your clusters are fully synchronized for both data and consumer group offsets.

To troubleshoot MSK Replicator replication or errors, use the CloudWatch logs to get more details about the health of the replicator. MSK Replicator logs status and troubleshooting information which can be helpful in diagnosing issues like connectivity, authentication, and SSL errors.

Note that the replication is asynchronous, so there will be some lag during replication. The lag will reach zero once a client is shut down during migration to the target cluster. This takes about 30 seconds under normal operations, allowing a low downtime migration without data loss. If your lag is continually increasing or does not reach a sustained low level, this indicates that you have insufficient partitions for high-throughput replication. Refer to Troubleshoot MSK Replicator for more information on troubleshooting replication throughput and lag.

Key metrics include:

MessageLag – Monitors the sync between the MSK Replicator and the source cluster. MessageLag indicates the lag between the messages produced to the source cluster and messages consumed by the replicator. It is not the lag between the source and target cluster.
ReplicationLatency – Time taken for records to replicate from source to target cluster (ms)
ReplicatorThroughput – Average number of bytes replicated per second
ReplicatorFailure – Number of failures the replicator is experiencing
KafkaClusterPingSuccessCount – Connection health indicator (1 = healthy, 0 = unhealthy)
ConsumerGroupCount – Total consumer groups being synchronized
ConsumerGroupOffsetSyncFailure – Failures during offset synchronization
AuthError – Number of connections with failed authentication per second, by cluster
ThrottleTime – Average time in ms a request was throttled by brokers, by cluster
SumOffsetLag – Aggregated offset lag across partitions for a consumer group on a topic (MSK cluster-level metric)

For more details on these metrics, see the MSK Replicator metrics documentation.

Your applications are ready to migrate when the following conditions are met. For most workloads, you should expect these metrics to stabilize within a few hours of starting replication. High-throughput clusters may take longer depending on topic volume and partition count.

ReplicatorFailure = 0
ConsumerGroupOffsetSyncFailure = 0
KafkaClusterPingSuccessCount = 1 for both source and target clusters
MessageLag < 1,000
- Your sustained lag may be lower or higher depending on your throughput per partition, message size, and other factors
- Sustained high message lag usually indicates insufficient partitions for high-throughput replication
ReplicationLatency < 90 seconds
- Your sustained latency may be lower or higher depending on your throughput per partition, message size, and other factors
- Sustained high latency usually indicates insufficient partitions for high-throughput replication
SumOffsetLag is at a sustained low level on both clusters
- Offset values on the two clusters may not be numerically identical.
- MSK Replicator translates offsets between clusters so that consumers resume from the correct position, but the raw offset numbers can differ due to how offset translation works. What matters is that SumOffsetLag is at a sustained low level.
ConsumerGroupCount (MSK) = Expected count (external cluster)
- If ConsumerGroupCount is zero or does not match the expected count, then there is an issue in the Replicator configuration or a permissions issue preventing consumer group synchronization

Migrating your applications

With bidirectional consumer offset synchronization, you can migrate your producers and consumers regardless of order. Start by monitoring replication metrics until they reach the target values described in the previous section. Then migrate your applications (producers or consumers) to use the MSK Express cluster endpoints and verify that they are producing and consuming as expected. If you encounter issues, you can roll back by switching applications back to the external cluster. The consumer offset synchronization makes sure that your applications resume from their last committed position regardless of which cluster they connect to.

For a comprehensive, hands-on walkthrough of the end-to-end migration process, explore the MSK Migration Workshop, which provides step-by-step guidance for migrating your Kafka workloads to Amazon MSK.

Security considerations

MSK Replicator uses SASL/SCRAM authentication with SSL encryption for secure data transfer between your external cluster and AWS. The solution supports both publicly trusted certificates and private or self-signed certificates. Credentials are stored securely in AWS Secrets Manager, and the target MSK Express cluster uses IAM authentication for access control.

When configuring security, keep the following in mind:

Make sure that the IAM role you create in Step 4 follows the principle of least privileges. Only attach AWSMSKReplicatorExecutionRole and an IAM policy for Secrets Manager with least-privileges access to read secret values and avoid adding broader permissions.
Verify that your Secrets Manager secret is encrypted with an AWS KMS key that the MSK Replicator service execution role has permission to decrypt.
Confirm that the security groups assigned to MSK Replicator allow outbound traffic to your external cluster’s broker ports (typically 9096 for SASL/SCRAM with TLS) and to the MSK Express cluster.
Rotate your SASL/SCRAM credentials periodically and update the corresponding Secrets Manager secret. MSK Replicator picks up the new credentials automatically on the next connection attempt.

Under the AWS shared responsibility model, AWS is responsible for securing the underlying infrastructure that runs MSK Replicator, including the compute, storage, and networking resources. You are responsible for configuring authentication mechanisms (SASL/SCRAM), managing credentials in AWS Secrets Manager, configuring network security (security groups and VPC settings), implementing IAM policies following least privilege, and rotating credentials. For more information, see Security in Amazon MSK in the Amazon MSK Developer Guide.

Cleanup

To avoid ongoing charges, delete the resources you created during this walkthrough. Start by deleting the replicators first, because they depend on the other resources:

aws kafka delete-replicator --replicator-arn <replicator-arn>

After both replicators are deleted, you can remove the following resources if they were created solely for this walkthrough:

The MSK Express cluster (deleting a cluster also removes its stored data, so verify that your applications have fully migrated before proceeding)
The Secrets Manager secrets containing your SASL/SCRAM credentials and certificates
The IAM role and policies created for MSK Replicator

You can verify that a replicator has been fully deleted by running aws kafka list-replicators and confirming it no longer appears in the output.

Conclusion

Amazon MSK Replicator simplifies the process of migrating to Amazon MSK Express brokers and establishes hybrid Kafka architectures. The fully managed service alleviates the operational complexity of managing replication while bidirectional consumer offset synchronization enables flexible, low-risk application migration.

Next Steps

To get started using MSK Replicator to migrate applications to MSK Express brokers, use the MSK Migration Workshop for a hands-on, end-to-end migration walkthrough. The Amazon MSK Replicator documentation includes detailed configuration details to help configure MSK Replicator for your use case. From there, use MSK Replicator to migrate your Apache Kafka workloads to MSK Express broker.

Once your migration is complete, consider exploring multi-region replication patterns for disaster recovery, or integrating your MSK Express cluster with AWS analytics services such as Amazon Data Firehose and Amazon Athena. If you need help planning your migration, reach out to your AWS account team, AWS Support or AWS Professional Services.

About the authors

Getting started with Apache Iceberg write support in Amazon Redshift – Part 2

2026-04-16 Sanket Hase

Post Syndicated from Sanket Hase original https://aws.amazon.com/blogs/big-data/getting-started-with-apache-iceberg-write-support-in-amazon-redshift-part-2/

In Getting started with Apache Iceberg write support in Amazon Redshift – part 1, you learned how to create Apache Iceberg tables and write data directly from Amazon Redshift to your data lake. You set up external schemas, created tables in both Amazon Simple Storage Service (Amazon S3) and S3 Tables, and performed INSERT operations while maintaining ACID (Atomicity, Consistency, Isolation, Durability) compliance.

Amazon Redshift now supports DELETE, UPDATE, and MERGE operations for Apache Iceberg tables stored in Amazon S3 and Amazon S3 table buckets. With these operations, you can modify data at the row level, implement upsert patterns, and manage the data lifecycle while maintaining transactional consistency using familiar SQL syntax. You can run complex transformations in Amazon Redshift and write results to Apache Iceberg tables that other analytics engines like Amazon EMR or Amazon Athena can immediately query.

In this post, you work with customer and orders datasets that were created and used in the previously mentioned post to demonstrate these capabilities in a data synchronization scenario.

Solution overview

This solution demonstrates DELETE, UPDATE, and MERGE operations for Apache Iceberg tables in Amazon Redshift using a common data synchronization pattern: maintaining customer records and orders data across staging and production tables. The workflow includes three key operations:

DELETE – Remove customer records based on opt-out requests
UPDATE – Modify existing customer information
MERGE – Synchronize order data between staging and production tables using upsert patterns

Figure 1: solution overview

The solution uses a staging table (orders_stg) stored in an S3 table bucket for incoming data and reference tables (customer_opt_out) in Amazon Redshift for managing data lifecycle operations. With this architecture, you can process changes efficiently while maintaining ACID compliance across both storage types.

Prerequisites

For this walkthrough, you should have completed the setup steps from Getting started with Apache Iceberg write support in Amazon Redshift – part 1, including:

Create an Amazon Redshift data warehouse (provisioned or Serverless)
Set up the required IAM role (RedshifticebergRole) with appropriate permissions
Create an Amazon S3 bucket and S3 Table bucket
Configure AWS Glue Data Catalog database and setting up access
Set up AWS Lake Formation permissions
Create the customer Apache Iceberg table in Amazon S3 standard buckets with sample customer data
Create the orders Apache Iceberg table in Amazon S3 Table buckets with sample order data
Amazon Redshift data warehouse on p200 version or higher

Data preparation

In this section, you set up the sample data needed to demonstrate MERGE, UPDATE, and DELETE operations. To prepare your data, complete the following steps:

Log in to Amazon Redshift using Query Editor V2 with the Federated user option.
Create the orders_stg and customer_opt_out tables with sample data:

CREATE TABLE "iceberg-write-blog@s3tablescatalog".iceberg_write_namespace.orders_stg
(
customer_id BIGINT,
order_id BIGINT,
Total_order_amt DECIMAL(10,2),
Total_order_tax_amt REAL,
tax_pct DOUBLE PRECISION,
order_date DATE,
order_created_at_tz TIMESTAMPTZ,
is_active_ind BOOLEAN
)
USING ICEBERG;
INSERT INTO "iceberg-write-blog@s3tablescatalog".iceberg_write_namespace.orders_stg
(order_date, order_id, customer_id, total_order_amt, total_order_tax_amt, tax_pct, order_created_at_tz, is_active_ind)
VALUES
('2024-11-11', 1016, 10, 167.45, 13.40, 0.08, '2024-11-11 06:55:00-06:00', true),
('2024-11-12', 1017, 15, 34.99, 2.80, 0.08, '2024-11-12 23:30:30-06:00', true),
('2024-11-09', 1014, 9, 500.60, 56.80, 0.09, '2024-11-09 16:20:55-06:00', true),
('2024-11-10', 1015, 5, 329.85, 33.51, 0.08, '2024-11-10 11:45:30-06:00', true);
select * from "iceberg-write-blog@s3tablescatalog".iceberg_write_namespace.orders_stg;

Figure 2: orders_stg result set

CREATE TABLE dev.public.customer_opt_out
(
customer_id bigint,
customer_name varchar,
opt_out_ind char(1),
cust_rec_upd_ind char(1)
);
INSERT INTO dev.public.customer_opt_out VALUES
(9, 'Customer9 Martinez', 'Y', 'N'),
(12, 'Customer12 Thomas', 'Y', 'N'),
(13, 'Customer13 Albon', 'N', 'Y'),
(14, 'Customer14 Oscar', 'N', 'Y');
select * from dev.public.customer_opt_out;

Figure 3: customer_opt_out result set

You can now use the orders_stg and customer_opt_out tables to demonstrate data manipulation operations on the orders and customer tables created in the prerequisite section.

MERGE

MERGE conditionally inserts, updates, or deletes rows in a target table based on the results of a join with a source table. You can use MERGE to synchronize two tables by inserting, updating, or deleting rows in one table based on differences found in the other table.

To perform a MERGE operation:

Verify that the current data in the orders table for order IDs 1014, 1015, 1016, and 1017.You loaded this sample data in Part 1:

select * from "iceberg-write-blog@s3tablescatalog".iceberg_write_namespace.orders
where order_id in (1014,1015,1016,1017);

Figure 4: orders data for existing orders for orders in orders_stg

The orders table contains existing rows for order IDs 1014 and 1015.

Run the following MERGE operation using order_id as the key column to match rows between the orders and orders_stg tables:

MERGE INTO "iceberg-write-blog@s3tablescatalog".iceberg_write_namespace.orders
USING "iceberg-write-blog@s3tablescatalog".iceberg_write_namespace.orders_stg
ON orders.order_id = orders_stg.order_id
WHEN MATCHED THEN UPDATE 
SET
customer_id         = orders_stg.customer_id,
total_order_amt     = orders_stg.total_order_amt,
total_order_tax_amt = orders_stg.total_order_tax_amt,
tax_pct             = orders_stg.tax_pct,
order_date          = orders_stg.order_date,
order_created_at_tz = orders_stg.order_created_at_tz,
is_active_ind       = orders_stg.is_active_ind
WHEN NOT MATCHED THEN INSERT
VALUES 
(orders_stg.customer_id,orders_stg.order_id,orders_stg.total_order_amt,orders_stg.total_order_tax_amt,orders_stg.tax_pct,orders_stg.order_date,orders_stg.order_created_at_tz,orders_stg.is_active_ind);

The operation updates existing rows (1014 and 1015) and inserts new rows for order IDs that don’t exist in the orders table (1016 and 1017).

Verify the updated data in the orders table:

select * from "iceberg-write-blog@s3tablescatalog".iceberg_write_namespace.orderswhere order_id in (1014,1015,1016,1017);

Figure 5: merged data on orders from orders_stg

The MERGE operation performs the following changes:

Updates existing rows – Order IDs 1014 and 1015 have updated total_order_amt and total_order_tax_amt values from the orders_stg table
Inserts new rows – Order IDs 1016 and 1017 are inserted because they don’t exist in the orders table

This demonstrates the upsert pattern, where MERGE conditionally updates or inserts rows based on the matching key column.

UPDATE

UPDATE modifies existing rows in a table based on specified conditions or values from another table.

Update the customer Apache Iceberg table using data from the customer_opt_out Amazon Redshift native table. The UPDATE operation uses the cust_rec_upd_ind column as a filter, updating only rows where the value is ‘Y’.

To perform an UPDATE operation:

Verify the current customer_name values for customer IDs 13 and 14 in customer_opt_out and customer (loaded this sample data in Part 1) tables:

select * from dev.public.customer_opt_out
where cust_rec_upd_ind = 'Y';

Figure 6: verify existing customer data for customers from customer_opt_out

select customer_id,customer_name from dev.demo_iceberg.customer
where customer_id in(13,14);

Figure 7: verify existing customer name for customers from customer_opt_out

Run the following UPDATE operation to modify customer names based on the cust_rec_upd_ind from customer_opt_out:

UPDATE dev.demo_iceberg.customerSET customer_name = customer_opt_out.customer_name
FROM dev.public.customer_opt_out
WHERE customer_opt_out.cust_rec_upd_ind = 'Y'and customer.customer_id = customer_opt_out.customer_id;

Verify the changes for customer IDs 13 and 14:

select customer_id,customer_name from dev.demo_iceberg.customer where customer_id in(13,14) order by 1;

Figure 8: updated customer names in customer table

The UPDATE operation modifies the customer_name values based on the join condition with the customer_opt_out table. Customer IDs 13 and 14 now have updated names (Customer13 Albon and Customer14 Oscar).

DELETE

DELETE removes rows from a table based on specified conditions. Without a WHERE clause, DELETE removes all the rows from table.

Delete rows from the customer Apache Iceberg table using data from the customer_opt_out Amazon Redshift native table. The DELETE operation uses the opt_out_ind column as a filter, removing only rows where the value is ‘Y’.

To perform a DELETE operation:

Verify the opt-out indicator data in the customer_opt_out table:

select * from dev.public.customer_opt_out
where opt_out_ind = 'Y';

Figure 9: verify customer records for opt out

Verify the current customer data for customer IDs 9 and 12:

select * from dev.demo_iceberg.customerwhere customer_id in(9,12);

Figure 0: verify existing customers data in customer table for opt out

Figure 10: verify existing customers data in customer table for opt out

Review the query execution plan:

EXPLAINDELETE FROM demo_iceberg.customerUSING public.customer_opt_out
WHERE customer.customer_id = customer_opt_out.customer_id
AND customer_opt_out.opt_out_ind = 'Y';

Figure 1: query plan for the DELETE queryThe execution plan shows Amazon S3 scans for Apache Iceberg format tables, indicating that Amazon Redshift removes rows directly from the Amazon S3 bucket.

Figure 11: query plan for the DELETE query. The execution plan shows Amazon S3 scans for Apache Iceberg format tables, indicating that Amazon Redshift removes rows directly from the Amazon S3 bucket.

Run the following DELETE operation:

DELETE FROM demo_iceberg.customer
USING public.customer_opt_out
WHERE customer.customer_id = customer_opt_out.customer_id
AND customer_opt_out.opt_out_ind = 'Y';

Verify that the rows were removed:

select * from dev.demo_iceberg.customer where customer_id in(9,12);

Figure 2: result set from customer table for opt out customer after delete

Figure 12: result set from customer table for opt out customer after delete

The query returns no rows, confirming that customer IDs 9 and 12 were successfully deleted from the customer table.

Best practices

After performing multiple UPDATE or DELETE operations, consider running table maintenance to optimize read performance:

For AWS Glue tables – Use AWS Glue table optimizers. For more information, see Table optimizers in the AWS Glue Developer Guide.
For S3 Tables – Use S3 Tables maintenance operations. For more information, see S3 Tables maintenance in the Amazon S3 User Guide.

Table maintenance merges and compacts deletion files generated by Merge-on-Read operations, improving query performance for subsequent reads.

Conclusion

You can use Amazon Redshift support for DELETE, UPDATE, and MERGE operations on Apache Iceberg tables to build data architectures that combine warehouse performance with data lake scalability. You can modify data at the row level while maintaining ACID compliance, giving you the same flexibility with Apache Iceberg tables as you have with native Amazon Redshift tables.

Get started:

Review the Amazon Redshift Iceberg integration documentation for complete syntax reference
Explore Writing to Apache Iceberg tables for detailed examples
Learn about table maintenance best practices for AWS Glue tables
Discover S3 Tables maintenance operations for improving query performance

About the authors

Get to insights faster using Notebooks in Amazon SageMaker Unified Studio

2026-04-16 Praveen Kumar

Post Syndicated from Praveen Kumar original https://aws.amazon.com/blogs/big-data/get-to-insights-faster-using-notebooks-in-amazon-sagemaker-unified-studio/

In this post, we demonstrate how Notebooks in Amazon SageMaker Unified Studio help you get to insights faster by simplifying infrastructure configuration. You’ll see how to analyze housing price data, create scalable data tables, run distributed profiling, and train machine learning (ML) models within a single notebook environment.

Data scientists and analysts often spend days configuring infrastructure and managing authentication across multiple data sources before they can begin analysis. When working with data across Amazon Simple Storage Service (Amazon S3), Amazon Redshift, Snowflake, and local files, teams face repeated authentication setup, manual compute scaling decisions, and tool-switching overhead that delays insights.

Notebooks in Amazon SageMaker Unified Studio provide instant access to 12+ data sources, compute scaling from local to distributed processing, and AI-powered code generation within a single browser-based environment. You’ll learn to use polyglot programming, multi-engine compute, and AI-assisted development to accelerate your path from question to insight.

What are Notebooks in Amazon SageMaker Unified Studio?

Notebooks in Amazon SageMaker Unified Studio provide an interactive environment for data analysis, exploration, engineering, and machine learning workflows. It delivers five integrated capabilities:

Polyglot programming: Write code in Python and SQL interchangeably within the same notebook environment
Unified data access: Connect instantly to data stored in Amazon S3, AWS Glue Data Catalog, Apache Iceberg tables, and third-party sources like Snowflake and BigQuery
Native visualization: Create charts directly from Python and SQL results for immersive data analytics
AI-powered development: Generate code through natural language prompts using SageMaker Data Agent, with an intelligent chat interface for data analytics, data science, and ML tasks
Flexible compute: Scale from basic instances to GPU-powered environments as your needs grow

Architecture

This section covers the architecture of Notebooks, which delivers enterprise-scale analytics with browser-based simplicity through a cloud-native architecture that integrates multiple compute engines, diverse data sources, and AI-powered assistance.

Presentation layer

You access the notebook interface through Amazon SageMaker Unified Studio, interacting with a familiar interface featuring code cells for execution, markdown cells for documentation, and visualization cells for charts and tables.

Compute layer

A dedicated notebook server manages your kernel lifecycle and session state. Key components include a Language Server for code completion, a Python 3.11 runtime with pre-loaded data science libraries, and a Polyglot Kernel that handles your Python, PySpark, and SQL execution within the same notebook. Persistent Amazon Elastic Block Store (Amazon EBS) storage backs each notebook you create.

Execution layer

Notebooks support multiple execution engines, automatically routing your code to the optimal processing engine. In-memory execution handles your smaller datasets and rapid prototyping. Apache Spark via Amazon Athena provides distributed processing for your large-scale analytics via Spark Connect. Native connectivity to Amazon Athena (Trino), Amazon Redshift, Snowflake, and BigQuery processes your SQL queries.

Data Integration

You get unified access to 12+ data sources including AWS-native (Amazon S3, AWS Glue, Amazon Athena, Amazon Redshift) and third-party (Snowflake, BigQuery, PostgreSQL, MySQL) data sources. For the latest supported data sources, see Connect to data sources .

AI layer

The SageMaker Data Agent operates in two modes to assist you: an Agent Panel for multi-step analytical workflows and Inline Assistance for focused, cell-level code generation. For a detailed overview, see Accelerate context-aware data analysis and ML workflows with Amazon SageMaker Data Agent .

Security is embedded throughout the architecture to protect your work. Data access respects your AWS Identity and Access Management (AWS IAM) permissions. The notebook and the agent can only access data sources you’re authorized to use. Communication between components uses encrypted channels, and your notebook storage is encrypted at rest. The AI agent includes built-in guardrails to help prevent destructive operations and logs interactions for your compliance and auditing purposes.

Prerequisites

Before you begin, you need:

An AWS account with appropriate permissions to create Amazon SageMaker Unified Studio resources. See Set up IAM-based domains for complete permission requirements.
Basic familiarity with Python programming and SQL queries
Understanding of data analysis concepts and ML workflows
Access to the sample housing dataset (provided in the walkthrough)

Getting started with Notebooks

To get started, open the Amazon SageMaker console and choose Get started.

You will be prompted either to select an existing AWS Identity and Access Management (AWS IAM) role that has access to your data and compute, or to create a new role. For this walkthrough, choose Create a new role and leave the other options at their defaults.

Choose Set up. It takes a few minutes to complete your environment.

Use case

In this post, you’ll use a Notebook and the SageMaker Data Agent to perform the following:

Working with dataset: Upload sample dataset housing.csv and explore with data explorer
Polyglot programming: Query dataframes with SQL via DuckDB
Multi-engine access via AWS Glue: Create an AWS Glue table to unlock Athena SQL/Spark engines for distributed processing
Advanced analytics: Use Athena Spark for data profiling
AI-assisted development: Generate profiling and ML code with Data Agent
ML workflow: Train Random Forest model and evaluate results

First, let’s walk through the interface and explore its core capabilities.

Understanding the interface

The Notebooks interface follows familiar notebook conventions with cells for code execution and markdown for documentation. Within the notebook, you’ll see your current programming environment (such as Python 3.11) and compute profile specifications. The interface allows you to:

Access your data by browsing files, exploring data catalogs, and managing third-party connections
Monitor variables created within your notebook context
Scale compute resources on demand by adjusting virtual CPUs and RAM based on your workload requirements, even scaling up to GPU instances
Manage packages by installing and configuring Python packages as needed

Working with the dataset

For this walkthrough, you’ll use the housing.csv sample dataset which you can download from this page. (the file is named canvas-sample-housing.csv on the linked page). Choose the Files icon in the left panel and choose the Local tab. Upload the CSV file to the notebook on the Local tab.

Notebooks provide you with instant access to your data assets. Using the data explorer, you can browse your AWS Glue Data Catalog, Amazon S3 table catalogs, Amazon S3 buckets, and configured third-party connections.

Choose the three-dot options menu.

Choose Read as dataframe, then run the inserted cell in the notebook to view the results.

import pandas as pd
<<df_csv_xxxx>> = pd.read_csv('housing.csv')
<<df_csv_xxxx>>

When you return a dataframe, Notebooks render it in a rich table format with automatic data profiling.

Polyglot programming: Python and SQL together

One of the most powerful features in Notebooks is the interoperability between Python and SQL. After you load data into a Python dataframe, you can immediately query it using SQL. For example, to calculate total population and household by ocean proximity, you can run:

select sum(population) ,sum(households),ocean_proximity 
from<<df_csv_xxxx>> 
group byocean_proximity

The notebook’s autocomplete functionality recognizes dataframes in your context, making SQL queries intuitive.

This SQL query runs on DuckDB (an in-memory SQL database engine), which requires no separate installation or server maintenance on your part. DuckDB’s lightweight design integrates into Python, Java, and other environments, making it ideal for your rapid interactive data analysis. For distributed processing needs, you can use engines such as Apache Spark or Trino after creating an AWS Glue table for this dataset.

Create an AWS Glue table for the dataset

After you create an AWS Glue table, you can query the dataset using various AWS Glue catalog-compatible engines, including Amazon Athena SQL (Trino) and Amazon Athena Spark. These engines deliver optimal price-performance for your specific workload requirements.

Start by creating an AWS Glue database. To do that, create a new cell in the notebook by choosing SQL and selecting Amazon Athena (SQL).

Run this SQL to create a database: create database demo;

Next, go to data explorer and choose +Add on the top left, then choose Create table. Choose the database you created earlier and enter a name for the table. Upload the housing.csv dataset file used earlier. Continue by choosing Next in the side panel to create the table.

Next, let’s run a sample SQL query in a new cell using Amazon Athena SQL:

select sum(population) , sum(households), ocean_proximity 
fromdemo.housing
group by ocean_proximity

Advanced capabilities with Athena Spark

Before you can build an ML model to predict house prices, let’s analyze the dataset further and run data profiling for additional insights. For advanced exploration, you can use Amazon Athena Spark within your notebook.To do that, you’ll create a new Python cell which has a built-in Spark session. Run the following code to check the Spark version:

# Verify Spark version
spark.version

Using the SageMaker Data Agent for data profiling

Instead of writing boilerplate code manually, you can use the built-in generative AI capability.

Prompt: “Perform data profiling and create visualization for housing table”

The AI assistant generates comprehensive profiling code for you, including basic statistics calculation, column-level profiling, data type analysis, and missing value detection.

The agent accessed your AWS Glue Data Catalog, understood your housing table structure, and generated profiling code tailored to your specific columns and data types. This context awareness reduces the trial-and-error cycle you’d normally face when adapting generic code snippets to your environment. Review the generated code and run it. The fast response times help you iterate on your analysis efficiently.

If you encounter an error, you can resolve it using Fix with AI as shown in the following figure. When errors occur during execution, the “Fix with AI” feature analyzes the traceback, diagnoses the root cause, and generates corrected code, so you can keep your analysis moving forward.

Training ML models

Next, you’ll use the data agent to generate code for training a model that predicts housing prices.

Prompt: “Generate code to train a model that predicts housing prices. Use table housing.”

The AI assistant generates end-to-end code for you that:

Reads housing data from AWS Glue catalog using Amazon Athena Spark and converts to pandas
Converts string columns to numeric, encodes using one-hot encoding and removes missing values
Trains a Random Forest model to predict median house values
Evaluates model performance (RMSE, MAE, R-square)
Displays top 10 most important features for predictions

This multi-step orchestration saves you hours of development time by handling the entire workflow from data access to model evaluation.

If you encounter an error, you can resolve it using Fix with AI available in the results traceback section.

This workflow showcased Notebooks’ unified capabilities: you uploaded files locally, created AWS Glue tables for multi-engine access, used Amazon Athena Spark for distributed profiling, and used AI-assisted ML development to predict housing prices. All of this happened within a single notebook environment without switching tools.

Key benefits and best practices

Notebooks in Amazon SageMaker Unified Studio deliver several advantages:

Faster time to insights: With traditional environments, you might spend hours on configuration before analysis begins. Notebooks bypass this overhead, so you can start work immediately.
Improved collaboration: You can share notebooks with consistent environments, supporting reproducibility and reducing “works on my machine” issues.
Reduced complexity: You can access multiple data sources and compute engines from one interface rather than navigating separate tools for each data source or processing engine.
AI-accelerated development: Generate task-specific code and receive intelligent suggestions, reducing time spent on repetitive coding tasks.
Scalable performance: Handle datasets from megabytes to petabytes with appropriate compute resources. The system scales automatically as data volumes grow.

Best practices

Start with appropriate compute profiles by beginning with smaller instances and scaling up as your needs grow.
Use AI assistance with natural language prompts for your repetitive tasks and complex operations.
Combine engines strategically by using Amazon Athena Spark for your large-scale processing, Amazon Redshift for data warehousing and other specialized engines for your specific workloads.
Document your work using markdown cells to create living documentation alongside your code.
Organize using multiple cells by breaking the complex workflows into logical steps for better readability and debugging.

Cleaning up

To avoid incurring future charges, delete the resources you created in this walkthrough:

In the Amazon SageMaker Unified Studio console, navigate to the Notebook page
Delete the notebook
Delete the demo database and housing table from the AWS Glue Data Catalog
Delete Amazon SageMaker Unified Studio domain created during this walkthrough
If you created a new IAM role specifically for this walkthrough, delete it from the IAM console

Conclusion

In this post, we demonstrated how Notebooks in Amazon SageMaker Unified Studio help you work more efficiently and deliver insights more quickly. By combining familiar notebook interfaces with enterprise-scale compute, multi-engine support, and generative AI assistance, teams can streamline data and AI workflows.

The integration of Python and SQL, instant access to diverse data sources, and intelligent code generation capabilities make Notebooks a valuable tool for modern data teams. Teams can perform exploratory data analysis, build complex data pipelines, or train ML models with the flexibility and power needed within a single, intuitive environment.

Ready to get started? Create your first notebook in Amazon SageMaker Unified Studio and begin analyzing data within minutes.

Explore additional capabilities:

Time series analysis workflows with seasonal decomposition and forecasting
Natural language processing pipelines for text classification and sentiment analysis
Integration with Amazon SageMaker Model Registry for ML model versioning
Advanced Spark optimization techniques for petabyte-scale processing

Learn more:

About the authors

AWS Weekly Roundup: Claude Mythos Preview in Amazon Bedrock, AWS Agent Registry, and more (April 13, 2026)

2026-04-13 Micah Walter

Post Syndicated from Micah Walter original https://aws.amazon.com/blogs/aws/aws-weekly-roundup-claude-mythos-preview-in-amazon-bedrock-aws-agent-registry-and-more-april-13-2026/

In my last Week in Review post, I mentioned how much time I’ve been spending on AI-Driven Development Lifecycle (AI-DLC) workshops with customers this year. A common theme in those sessions is the need for better cost visibility. Teams are moving fast with AI, but as they go from experimenting to full production, finance and leadership really need to know who is using which resources and at what cost. That’s why I was so excited to see the launch of Amazon Bedrock new support for cost allocation by IAM user and role this week. This lets you tag IAM principals with attributes like team or cost center and then activate those tags in your Billing and Cost Management console. The resulting cost data flows into AWS Cost Explorer and the detailed Cost and Usage Report, giving you a clear line of sight into model inference spending. Whether you’re scaling agents across teams, tracking foundation model use by department, or running tools like Claude Code on Amazon Bedrock, this new feature is a game changer for tracking and managing your AI investments. You can get all the details on setting this up in the IAM principal cost allocation documentation.

Now, let’s get into this week’s AWS news…

Headlines
Amazon Bedrock now offers Claude Mythos Preview Anthropic’s most sophisticated AI model to date is now available on Amazon Bedrock as a gated research preview through Project Glasswing. Claude Mythos introduces a new model class focused on cybersecurity, capable of identifying sophisticated security vulnerabilities in software, analyzing large codebases, and delivering state of the art performance across cybersecurity, coding, and complex reasoning tasks. Security teams can use it to discover and address vulnerabilities in critical software before threats emerge. Access is currently limited to allowlisted organizations, with Anthropic and AWS prioritizing internet critical companies and open source maintainers.

AWS Agent Registry for centralized agent discovery and governance now in preview AWS launched Agent Registry through Amazon Bedrock AgentCore, providing organizations with a private catalog for discovering and managing AI agents, tools, skills, MCP servers, and custom resources. The registry helps teams locate existing capabilities rather than duplicating them, with semantic and keyword search, approval workflows, and CloudTrail audit trails. It is accessible via the AgentCore Console, AWS CLI, SDK, and as an MCP server queryable from IDEs.

Last week’s launches
Here are some launches and updates from this past week that caught my attention:

Announcing Amazon S3 Files, making S3 buckets accessible as file systems — Amazon S3 Files transforms S3 buckets into shared file systems that connect any AWS compute resource directly with your S3 data. Built on Amazon EFS technology, it delivers full file system semantics with low latency performance, caching actively used data and providing multiple terabytes per second of aggregate read throughput. Applications can access S3 data through both file system and S3 APIs simultaneously without code modifications or data migration.
Amazon OpenSearch Service supports Managed Prometheus and agent tracing —Amazon OpenSearch Service now provides a unified observability platform that consolidates metrics, logs, traces, and AI agent tracing into a single interface. The update includes native Prometheus integration with direct PromQL query support, RED metrics monitoring, and OpenTelemetry GenAI semantic convention support for LLM execution visibility. Operations teams can correlate slow traces to logs and overlay Prometheus metrics on dashboards without switching between tools.
Amazon WorkSpaces Advisor now available for AI powered troubleshooting— AWS launched Amazon WorkSpaces Advisor, an AI powered administrative tool that uses generative AI to help IT administrators troubleshoot Amazon WorkSpaces Personal deployments. It analyzes WorkSpace configurations, detects problems automatically, and provides actionable recommendations to restore service and optimize performance.
Amazon Braket adds support for Rigetti’s 108 qubit Cepheus QPU — Amazon Braket now offers access to Rigetti’s Cepheus-1-108Q device, the first 100+ qubit superconducting quantum processor on the platform. The modular design features twelve 9 qubit chiplets with CZ gates that offer enhanced resilience to phase errors. It supports multiple frameworks including Braket SDK, Qiskit, CUDA-Q, and Pennylane, with pulse level control for researchers.

For a full list of AWS announcements, be sure to keep an eye on the What’s New with AWS page.

Other AWS news
Here are some additional posts and resources that you might find interesting:

Building automated AWS Regional availability checks with Amazon S3— Storage blog post on implementing automated systems for monitoring service availability across AWS regions using Amazon S3 as core infrastructure.
Understanding Amazon Bedrock model lifecycle — Machine learning blog post that walks through the stages foundation models go through in Bedrock from availability through deprecation, helping teams plan for model updates and manage version dependencies in production.
Building memory intensive apps with AWS Lambda managed instances — Compute blog post exploring how Lambda managed instances extend the platform beyond lightweight workloads to support memory intensive applications while maintaining serverless benefits.
Deploy OpenClaw on AWS: Choose the right options for your AI workload — Builder Center guide comparing four AWS deployment options for OpenClaw: Amazon Lightsail for individual developers, Amazon EC2 for startups needing deeper AWS integration, Amazon Bedrock AgentCore for serverless multiuser scenarios, and Amazon EKS for enterprises requiring VM level isolation and advanced orchestration.
We’re bringing back the Kiro startup credits program — Kiro is relaunching its startup credits initiative, offering eligible early stage companies complimentary access to Kiro Pro+ for up to one year. The three tier program (Starter, Growth, Scale) provides 2 to 30 users based on team size, with rolling applications accepted globally.

Upcoming AWS events
Check your calendar and sign up for upcoming AWS events:

What’s Next with AWS (April 28, Virtual) Join this livestream at 9am PT for a candid discussion about how agentic AI is transforming how businesses operate. Featuring AWS CEO Matt Garman, SVP Colleen Aubrey, and OpenAI leaders discussing emerging agent capabilities, Amazon’s internal experiences, and new agentic solutions and platform capabilities.

Browse here for upcoming AWS led in person and virtual events, startup events, and developer focused events.

That’s all for this week. Check back next Monday for another Weekly Roundup!

~ micah

How to use Parquet Column Indexes with Amazon Athena

2026-04-13 Matt Wong

Post Syndicated from Matt Wong original https://aws.amazon.com/blogs/big-data/how-to-use-parquet-column-indexes-with-amazon-athena/

Amazon Athena recently added support for reading Parquet Column Indexes in Apache Iceberg tables on November 21, 2025. With this optimization, Athena can perform page-level data pruning to skip unnecessary data within Parquet row groups, potentially reducing the amount of data scanned and improving query runtime for queries with selective filters. For data teams, this may help enable faster insights and help reduce costs when analyzing large-scale data lakes.

Data teams building data lakes often choose Apache Iceberg for its ACID transactions, schema evolution, and metadata management capabilities. Athena is a serverless query engine that allows you to query Amazon S3-based data lakes using SQL, and you don’t need to manage infrastructure. Based on the type of data and query logic, Athena can apply multiple query optimizations to improve performance and reduce costs.

In this blog post, we use Athena and Amazon SageMaker Unified Studio to explore Parquet Column Indexes and demonstrate how they can improve Iceberg query performance. We explain what Parquet Column Indexes are, demonstrate their performance benefits, and show you how to use them in your applications.

Overview of Parquet Column Indexes

Parquet Column Indexes store metadata that query engines can use to skip irrelevant data with greater precision than row group statistics alone. To understand how they work, consider how data is structured within Parquet files and how engines like Athena process them.

Parquet files organize data hierarchically by dividing data into row groups (typically 128-512 MB each) and further subdividing them into pages (typically 1 MB each). Traditionally, Parquet maintains metadata on the contents of each row group level in the form of min/max statistics, allowing engines like Athena to skip row groups that don’t satisfy query predicates. Although this approach reduces the bytes scanned and query runtime, it has limitations. If even a single page within a row group overlaps with the values you are searching for, Athena scans all pages within the row group.

Parquet Column Indexes help address this problem by storing page-level min/max statistics in the Parquet file footer. Row group statistics provide coarse-grained filtering, but Parquet Column Indexes enable finer-grained filtering by allowing query engines like Athena to skip individual pages within a row group. Consider a Parquet file with a single row group containing 5 pages for a column. The row group has min/max statistics of (1, 20), and each page for that column has the following min/max statistics.

row-group-0: min=1, max=20
    page-0: min=1, max=10
    page-1: min=1, max=10
    page-2: min=5, max=15
    page-3: min=6, max=16
    page-4: min=10, max=20

When Athena runs a query filtering for values equal to 2, it first checks the row group statistics and confirms that 2 falls within the range (1, 20). Athena will then plan to scan the pages within that row group. Without Parquet Column Indexes, Athena scans each of the 5 pages in the row group. With Parquet Column Indexes, Athena examines the page-level statistics and determines that only page-0 and page-1 need to be read, skipping the remaining 3 pages.

How to use Parquet Column Indexes with Athena

Athena uses Parquet Column Indexes based on table type:

Amazon S3 Tables: Athena automatically uses Parquet Column Indexes by default when they are present.
Iceberg tables in S3 general purpose buckets: Athena does not use Parquet Column Indexes by default. To allow Athena to use Parquet Column Indexes, add an AWS Glue table property named use_iceberg_parquet_column_index and set it to true. Use the AWS Glue console or AWS Glue UpdateTable API to perform these actions.

Read more about how to use this feature in Use Parquet column indexing.

Measuring Athena performance gains when using Parquet Column Indexes

Now that we understand what Parquet Column Indexes are, we’ll demonstrate the performance benefits of using Parquet Column Indexes by analyzing the catalog_sales table from a 3TB TPC-DS dataset. This table contains ecommerce transaction data including order dates, sales amounts, customer IDs, and product information. This dataset is a good proxy for the types of business analysis that you might perform on your own data, such as identifying sales trends, analyzing customer purchasing patterns, and calculating revenue metrics. We compare query execution statistics with and without Parquet Column Indexes to quantify the performance improvement.

Prerequisites

Before you begin, you must have the following resources:

A SageMaker Unified Studio IAM-based domain.
An Execution IAM Role configured within the SageMaker Unified Studio IAM-based domain with access to S3, AWS Glue Data Catalog, and Athena.
An S3 bucket in your account to store Iceberg table data and Athena query results.

Create catalog_sales Iceberg table

Complete the following steps using SageMaker Unified Studio notebooks. There, you can use SageMaker Unified Studio’s multi-dialect notebook functionality to work with your data using the Athena SQL and Spark engines. To create a catalog_sales Iceberg table in your account, follow these steps:

Navigate to Amazon SageMaker in the AWS Management Console and choose Open under Get started with Amazon SageMaker Unified Studio.
From the side navigation, select Notebooks and choose Create Notebook. The subsequent steps in this post will execute scripts in this notebook.
Create a new SQL cell in the notebook and set the connection type to Athena (Spark). Execute the following query to create a database for the tables in this post.
```
CREATE DATABASE parquet_column_index_blog;
```

Create a new SQL cell in the notebook and verify the connection type is Athena (Spark). Execute the following query to create a Hive table pointing to the location of the TPC-DS catalog_sales table data at the public S3 bucket.

CREATE TABLE IF NOT EXISTS parquet_column_index_blog.catalog_sales_hive (
	  cs_sold_time_sk int,
	  cs_ship_date_sk int,
	  cs_bill_customer_sk int,
	  cs_bill_cdemo_sk int,
	  cs_bill_hdemo_sk int,
	  cs_bill_addr_sk int,
	  cs_ship_customer_sk int,
	  cs_ship_cdemo_sk int,
	  cs_ship_hdemo_sk int,
	  cs_ship_addr_sk int,
	  cs_call_center_sk int,
	  cs_catalog_page_sk int,
	  cs_ship_mode_sk int,
	  cs_warehouse_sk int,
	  cs_item_sk int,
	  cs_promo_sk int,
	  cs_order_number bigint,
	  cs_quantity int,
	  cs_wholesale_cost decimal(7, 2),
	  cs_list_price decimal(7, 2),
	  cs_sales_price decimal(7, 2),
	  cs_ext_discount_amt decimal(7, 2),
	  cs_ext_sales_price decimal(7, 2),
	  cs_ext_wholesale_cost decimal(7, 2),
	  cs_ext_list_price decimal(7, 2),
	  cs_ext_tax decimal(7, 2),
	  cs_coupon_amt decimal(7, 2),
	  cs_ext_ship_cost decimal(7, 2),
	  cs_net_paid decimal(7, 2),
	  cs_net_paid_inc_tax decimal(7, 2),
	  cs_net_paid_inc_ship decimal(7, 2),
	  cs_net_paid_inc_ship_tax decimal(7, 2),
	  cs_net_profit decimal(7, 2))
	USING parquet
	PARTITIONED BY (cs_sold_date_sk int)
	LOCATION 's3://blogpost-sparkoneks-us-east-1/blog/BLOG_TPCDS-TEST-3T-partitioned/catalog_sales/'
	TBLPROPERTIES (
	  'parquet.compression'='SNAPPY'
	);

Create a new SQL cell in the notebook and verify the connection type is Athena (Spark). Execute the following query to add the Hive partitions to the AWS Glue metadata.
```
MSCK REPAIR TABLE parquet_column_index_blog.catalog_sales_hive;
```
Create a new SQL cell in the notebook and verify the connection type is Athena (Spark). Replace s3://amzn-s3-demo-bucket/athena_parquet_column_index_blog/catalog_sales/ with the S3 URI where you want to store your Iceberg table data, then execute the following query to create the catalog_sales Iceberg table from the Hive table.
```
CREATE TABLE parquet_column_index_blog.catalog_sales
	USING iceberg
	PARTITIONED BY (cs_sold_date_sk)
	LOCATION 's3://amzn-s3-demo-bucket/athena_parquet_column_index_blog/catalog_sales/'
	AS
	SELECT * FROM parquet_column_index_blog.catalog_sales_hive;
```
Create a new SQL cell in the notebook and verify the connection type is Athena (Spark). Execute the following query to delete the catalog_sales_hive table, which was only needed to create the catalog_sales Iceberg table.
```
DROP TABLE parquet_column_index_blog.catalog_sales_hive;
```

Run an Athena query without Parquet Column Indexes

After creating the catalog_sales Iceberg table in the preceding steps, we run a simple query that analyzes shipping delays of the top 10 most ordered items. This type of analysis could be critical for ecommerce and retail operations. By identifying which popular items experience the greatest delays, fulfillment teams can focus resources where they matter most. For example, you can adjust inventory placement, change warehouse assignments, or address carrier issues. Additionally, popular items with significant shipping delays are more likely to result in order cancellations or returns, so proactively identifying these issues helps protect revenue.

SELECT cs_item_sk,
    SUM(cs_quantity) as total_orders,
    AVG(cs_ship_date_sk - cs_sold_date_sk) as avg_ship_delay_days,
    MIN(cs_ship_date_sk - cs_sold_date_sk) as min_ship_delay,
    MAX(cs_ship_date_sk - cs_sold_date_sk) as max_ship_delay,
    SUM(
        CASE
            WHEN cs_ship_date_sk - cs_sold_date_sk > 7 THEN 1 ELSE 0
        END
    ) as late_shipments,
    SUM(
        CASE
            WHEN cs_ship_date_sk - cs_sold_date_sk > 7 THEN 1 ELSE 0
        END
    ) * 100.0 / COUNT(*) as late_shipment_pct,
    AVG(cs_ext_ship_cost) as avg_shipping_cost
FROM parquet_column_index_blog.catalog_sales
WHERE cs_item_sk IN (
        SELECT cs_item_sk
        FROM parquet_column_index_blog.catalog_sales
        WHERE cs_item_sk IS NOT NULL
        GROUP BY cs_item_sk
        ORDER BY SUM(cs_quantity) DESC
        LIMIT 10
    )
    AND cs_ship_date_sk IS NOT NULL
    AND cs_sold_date_sk IS NOT NULL
GROUP BY cs_item_sk
ORDER BY avg_ship_delay_days DESC;

Additionally, this query is a good candidate for demonstrating the effectiveness of using Parquet Column Indexes because it has a selective filter predicate on a single column cs_item_sk. When Athena executes this query, it first identifies row groups whose min/max ranges overlap with the top 10 most ordered items. Without using Parquet Column Indexes, Athena has to scan every page of data within those matched row groups. However, when using Parquet Column Indexes, Athena can prune data further by skipping individual pages within those row groups whose min/max ranges do not overlap with the ids. Complete the following steps to establish baseline query performance when Athena does not use Parquet Column Indexes during the query.

Create a new Python cell in the notebook. Replace s3://amzn-s3-demo-bucket/athena_parquet_column_index_blog/query_results/ with the S3 URI where you want to store your Athena query results, then execute the following script. Note the runtime and bytes scanned that will be printed. The script will run the query five times with query result reuse disabled and chooses the minimum runtime and the corresponding bytes scanned among those iterations. See our numbers in the Run Athena query with Parquet Column Indexes section.

import boto3
import time

# Configuration
DATABASE = "parquet_column_index_blog"
OUTPUT_LOCATION = "s3://amzn-s3-demo-bucket/athena_parquet_column_index_blog/query_results/"

def run_athena_query(query: str, database: str, output_location: str):
    athena_client = boto3.client('athena')
    
    response = athena_client.start_query_execution(
        QueryString=query,
        QueryExecutionContext={'Database': database},
        ResultConfiguration={'OutputLocation': output_location}
    )
    
    query_execution_id = response['QueryExecutionId']
    
    while True:
        result = athena_client.get_query_execution(QueryExecutionId=query_execution_id)
        state = result['QueryExecution']['Status']['State']
        
        if state in ['SUCCEEDED', 'FAILED', 'CANCELLED']:
            break
        
        time.sleep(5)
    
    if state != 'SUCCEEDED':
        raise Exception(f"Query failed with state: {state}")
    
    stats = result['QueryExecution']['Statistics']
    
    return {
        'execution_time_sec': stats['EngineExecutionTimeInMillis'] / 1000,
        'data_scanned_gb': stats['DataScannedInBytes'] / (1024 ** 3)
    }


def benchmark_query(query: str, database: str, output_location: str, num_runs: int = 5):
    results = []
    
    for i in range(num_runs):
        stats = run_athena_query(query, database, output_location)
        results.append(stats)
    
    best_run = min(results, key=lambda r: r['execution_time_sec'])
    
    execution_time = round(best_run['execution_time_sec'], 1)
    data_scanned = round(best_run['data_scanned_gb'], 1)
    
    print(f"Execution time: {execution_time} sec")
    print(f"Data scanned: {data_scanned} GB")


QUERY = """
SELECT cs_item_sk,
    SUM(cs_quantity) as total_orders,
    AVG(cs_ship_date_sk - cs_sold_date_sk) as avg_ship_delay_days,
    MIN(cs_ship_date_sk - cs_sold_date_sk) as min_ship_delay,
    MAX(cs_ship_date_sk - cs_sold_date_sk) as max_ship_delay,
    SUM(
        CASE
            WHEN cs_ship_date_sk - cs_sold_date_sk > 7 THEN 1 ELSE 0
        END
    ) as late_shipments,
    SUM(
        CASE
            WHEN cs_ship_date_sk - cs_sold_date_sk > 7 THEN 1 ELSE 0
        END
    ) * 100.0 / COUNT(*) as late_shipment_pct,
    AVG(cs_ext_ship_cost) as avg_shipping_cost
FROM parquet_column_index_blog.catalog_sales
WHERE cs_item_sk IN (
        SELECT cs_item_sk
        FROM parquet_column_index_blog.catalog_sales
        WHERE cs_item_sk IS NOT NULL
        GROUP BY cs_item_sk
        ORDER BY SUM(cs_quantity) DESC
        LIMIT 10
    )
    AND cs_ship_date_sk IS NOT NULL
    AND cs_sold_date_sk IS NOT NULL
GROUP BY cs_item_sk
ORDER BY avg_ship_delay_days DESC;
"""

# Run benchmark
benchmark_query(QUERY, DATABASE, OUTPUT_LOCATION, num_runs=5)

Sort the catalog_sales table

Before rerunning the query with Athena using Parquet Column Indexes, you need to sort the catalog_sales table by the cs_item_sk column. In the preceding query, there is a dynamic filter as a subquery on the cs_item_sk column:

cs_item_sk IN (
        SELECT cs_item_sk
        FROM parquet_column_index_blog.catalog_sales
        WHERE cs_item_sk IS NOT NULL
        GROUP BY cs_item_sk
        ORDER BY SUM(cs_quantity) DESC
        LIMIT 10
    )

When executing this query, Athena pushes down the filter predicate to the data source level, fetching only rows that match the top 10 most ordered items. To maximize page pruning with Parquet Column Indexes, rows with the same cs_item_sk values should be stored near each other in the Parquet file. Without sorting, matching values could be scattered across many pages, forcing Athena to read more data. Sorting the table by cs_item_sk clusters similar values together, enabling Athena to read fewer pages.

Let’s examine the Parquet Column Indexes in one of the Parquet files to understand how the data in the catalog_sales table is currently organized. First, download the Parquet file from the cs_sold_date_sk = 2450815 partition and install the open-source parquet-cli tool on your local machine. Replace <local-path-to-parquet-file> with the path to the downloaded Parquet file, then run the following command on your local machine:

parquet column-index <local-path-to-parquet-file>

This displays Parquet Column Indexes for all columns. For brevity, only the first 11 pages of the cs_item_sk column from the first row group are shown in the following example:

row-group 0:
column index for column cs_item_sk:
Boundary order: UNORDERED
         null_count  min  max
page-0            0    4  359989
page-1            0    2  359996
page-2            0   10  359995
page-3            0   13  359996
page-4            0   22  359989
page-5            0   25  359984
page-6            0   13  359989
page-7            0   56  359990
page-8            0   14  359984
page-9            0    7  359978
page-10           0    1  359998

Notice that nearly every page contains a wide range of values. This overlap means Athena cannot eliminate pages when filtering with Parquet Column Indexes on cs_item_sk. For example, searching for cs_item_sk = 100 requires scanning each of the 11 pages because the value 100 falls within every page’s min/max range. With this overlap, enabling Athena to use Parquet Column Indexes would provide no performance benefit. Sorting the data by cs_item_sk eliminates this overlap, creating distinct, non-overlapping ranges for each page. To make Parquet Column Indexes more effective, sort the table by completing the following step:

Create a new SQL cell in the notebook and verify the connection type is Athena (Spark). Execute the query to sort the cs_item_sk column values of the catalog_sales table in ascending order and to put all the null values in the last few Parquet pages. New Iceberg data files will be generated from this query.
```
CALL spark_catalog.system.rewrite_data_files(
table => 'parquet_column_index_blog.catalog_sales', 
strategy => 'sort', 
sort_order => 'cs_item_sk ASC NULLS LAST', 
options => map('target-file-size-bytes', '1073741824', 
'rewrite-all', 'true', 'max-concurrent-file-group-rewrites', '200'));
```

Running the parquet column-index command on the sorted data file from the cs_sold_date_sk = 2450815 partition shows that the Parquet Column Indexes are now sorted and have non-overlapping ranges. The first 11 pages of the cs_item_sk column from the first row group are shown in the following example:

row-group 0:
column index for column cs_item_sk:
Boundary order: ASCENDING
         null_count  min    max
page-0           0      1   5282
page-1           0   5282  10556
page-2           0  10556  15842
page-3           0  15842  21154
page-4           0  21154  26434
page-5           0  26434  31669
page-6           0  31669  36916
page-7           0  36916  42205
page-8           0  42205  47528
page-9           0  47528  52808
page-10          0  52808  58189

Now when searching for cs_item_sk = 100, Athena only needs to read page-0, skipping the remaining 10 pages entirely.

Run Athena query with Parquet Column Indexes

Now that the data is sorted to eliminate overlapping pages within the row groups for the cs_item_sk column, we run two experiments on the sorted data. The first measures the impact of sorting alone, and the second measures the combined effect of sorting with Parquet Column Indexes.

Create a new Python cell in the notebook. Execute the same script in the section Run Athena query without Parquet Column Indexes and take note of the query runtime and bytes scanned results. This measures the performance of querying sorted data without using Parquet Column Indexes.

Create a new Python cell in the notebook. Execute the following Python script to set the use_iceberg_parquet_column_index table property to true for the catalog_sales table in the AWS Glue Data Catalog.

import boto3

def add_iceberg_parquet_column_index(database_name: str, table_name: str):
    glue_client = boto3.client('glue')
    
    # Get current table definition
    response = glue_client.get_table(DatabaseName=database_name, Name=table_name)
    table = response['Table']
    
    # Build TableInput with only allowed fields
    table_input = {'Name': table['Name']}
    
    allowed_fields = [
        'Description', 'Owner', 'LastAccessTime', 'LastAnalyzedTime',
        'Retention', 'StorageDescriptor', 'PartitionKeys', 'ViewOriginalText',
        'ViewExpandedText', 'TableType', 'Parameters', 'TargetTable'
    ]
    
    for field in allowed_fields:
        if field in table:
            table_input[field] = table[field]
    
    # Add the property
    if 'Parameters' not in table_input:
        table_input['Parameters'] = {}
    table_input['Parameters']['use_iceberg_parquet_column_index'] = 'true'
    
    # Update the table
    glue_client.update_table(DatabaseName=database_name, TableInput=table_input)

# Usage
add_iceberg_parquet_column_index("parquet_column_index_blog", "catalog_sales")

Create a new Python cell in the notebook. Execute the same script in the section Run Athena query without Parquet Column Indexes and take note of the query runtime and bytes scanned results. This measures the performance of querying sorted data using Parquet Column Indexes.

Athena query time and bytes scanned improvement

The following table summarizes the results from each experiment. The percentage improvements for the sorted experiments are measured against the unsorted baseline.

Experiment	Runtime (sec)	Bytes Scanned (GB)
Unsorted without Parquet Column Indexes	20.6	45.2
Sorted without Parquet Column Indexes	15.4 (25.2% faster)	27.8 (38.5% fewer bytes)
Sorted with Parquet Column Indexes	10.3 (50.0% faster)	13.0 (71.2% fewer bytes)

Recommendations

To maximize Athena’s ability to use Parquet Column Indexes and achieve optimal query performance, we recommend the following.

Sort data by frequently filtered columns. This allows Athena to efficiently read Parquet Column Indexes and skip irrelevant pages, potentially reducing scan time. When data is sorted by a filter column, similar values are clustered together within pages. Because Parquet Column Indexes store min/max values for each page, Athena can quickly determine which pages contain matching values and skip the rest.
Sort data by high-cardinality columns. This creates distinct value ranges between pages, maximizing the opportunity for Athena to skip pages during query execution. High-cardinality (many distinct values) columns produce non-overlapping min/max ranges across pages, allowing Athena to more effectively filter out irrelevant pages. In contrast, low-cardinality columns such as boolean or status fields result in overlapping ranges across many pages, reducing the number of skipped pages.

Clean up

When you have finished the steps in this post, complete the following cleanup actions to avoid incurring ongoing charges:

Create a new SQL cell in the notebook and set the connection type to Athena (Spark). Execute the following command to drop the parquet_column_index_blog database and the catalog_sales table.
```
DROP DATABASE parquet_column_index_blog CASCADE;
```
Delete the Iceberg table data and the Athena query results from your S3 bucket.
Delete the SageMaker Unified Studio IAM-based domain if it is no longer needed.

Conclusion

In this post, we showed you how Athena uses Parquet Column Indexes to speed up queries and reduce the number of bytes scanned. By using Parquet Column Indexes, Athena can skip irrelevant data pages to improve query performance, especially for queries with selective filters on sorted data. Refer to Optimize Iceberg tables to learn more about this feature and try it out on your own queries.

About the Author

Implementing Kerberos authentication for Apache Spark jobs on Amazon EMR on EKS to access a Kerberos-enabled Hive Metastore

2026-04-13 Krishna Kumar Venkateswaran

Post Syndicated from Krishna Kumar Venkateswaran original https://aws.amazon.com/blogs/big-data/implementing-kerberos-authentication-for-apache-spark-jobs-on-amazon-emr-on-eks-to-access-a-kerberos-enabled-hive-metastore/

Many organizations run their Apache Spark analytics platforms on Amazon EMR on Amazon Elastic Compute Cloud (Amazon EC2), using Kerberos authentication to secure connectivity between Spark jobs and a centralized shared Apache Hive Metastore (HMS). With Amazon EMR on Amazon EKS, they gained a new option for running Spark jobs with the benefits of Kubernetes-based container orchestration, improved resource utilization, and faster job startup times. However, an HMS deployment supports only one authentication mechanism at a time. This means that they must configure Kerberos authentication for their Spark jobs on Amazon EMR on EKS to connect to the existing Kerberos-enabled HMS.

In this post, we show how to configure Kerberos authentication for Spark jobs on Amazon EMR on EKS, authenticating against a Kerberos-enabled HMS so you can run both Amazon EMR on EC2 and Amazon EMR on EKS workloads against a single, secure HMS deployment.

Overview of solution

Consider an enterprise data platform team that’s been running Spark jobs on Amazon EMR on EC2 for several years. Their architecture includes a Kerberos-enabled standalone HMS that serves as the centralized data catalog, with Microsoft Active Directory functioning as the Key Distribution Center (KDC). As the team evaluates Amazon EMR on EKS for new workloads, their existing HMS must continue serving Amazon EMR on EC2, with both authenticating through the same Kerberos infrastructure. To address this, the platform team must configure their Spark jobs running on Amazon EMR on EKS to authenticate with the same KDC. This is so they can obtain valid Kerberos tickets and establish authenticated connections to the HMS while maintaining a unified security posture across their data platform.

Scope of Kerberos in this solution

Kerberos authentication in this solution secures the connection between Spark jobs and the HMS. Other components in the architecture use AWS and Kubernetes security mechanisms instead.

Solution architecture

Our solution implements Kerberos authentication to secure the connection between Spark jobs and the HMS. The architecture spans two Amazon Virtual Private Clouds (Amazon VPCs) connected using VPC peering, with distinct components handling identity management, compute, and metadata services.

Identity and Authentication layer

A self-managed Microsoft Active Directory Domain Controller is deployed in a dedicated VPC and serves as the KDC for Kerberos authentication. The Active Directory server hosts service principals for both the HMS service and Spark jobs. This separate VPC deployment mirrors real-world enterprise architectures where Active Directory is typically managed by identity teams in their own network boundary, whether on-premises or in AWS.

Data Platform layer

The data platform components reside in a separate VPC and includes an EKS cluster that hosts both the HMS service and Amazon EMR on EKS based Spark jobs persisting data in an Amazon Simple Storage Service (Amazon S3) bucket.

Hive Metastore service

The HMS is deployed in the EKS hive-metastore namespace and simulates a pre-existing, standalone Kerberos-enabled HMS, a common enterprise pattern where HMS is managed independently of any data processing platform. You can learn more about other enterprise design patterns in the post Design patterns for implementing Hive Metastore for Amazon EMR or EKS. The HMS service authenticates with the KDC using its service principal and keytab mounted from a Kubernetes secret.

Apache Spark Execution layer

Apache Spark jobs are deployed using the Spark Operator on EKS. The Spark driver and executor pods are configured with Kerberos credentials through mounted ConfigMaps containing krb5.conf and jaas.conf, along with keytab files from Kubernetes secrets. When a Spark job must access Hive tables, the driver authenticates with the KDC and establishes a secure Simple Authentication and Security Layer (SASL) connection to the HMS.

Authentication flow

The HMS runs as a long-running Kubernetes service that must be deployed and authenticated before Spark jobs can connect.

During HMS deployment:

HMS pod validates its Kerberos configuration. krb5.conf and jaas.conf are mounted from ConfigMaps
Service authenticates with KDC using its principal hive/hive-metastore-svc.hive-metastore.svc.cluster.local@CORP.KERBEROS
keytab is mounted from Kubernetes secret for credential access
Secure Thrift endpoint is established on port 9083 with SASL authentication enabled

When a Spark job must interact with the HMS:

Spark job submission:
1. User submits Spark job through Spark Operator
2. Driver and executor pods are created with Kerberos configuration mounted as volumes
3. krb5.conf ConfigMap provides KDC connection details including realm and server addresses
4. jaas.conf ConfigMap specifies a login module configuration with keytab path and principal
5. Keytab secret contains encrypted credentials for Spark service principal spark/[email protected]
Authentication and connection:
1. Spark driver authenticates with KDC using its principal and keytab to obtain a Ticket Granting Ticket (TGT)
2. When connecting to HMS, Spark requests a service ticket from the KDC for the HMS principal hive/hive-metastore-svc.hive-metastore.svc.cluster.local@CORP.KERBEROS
3. KDC issues a service ticket encrypted with HMS’s secret key
4. Spark presents this service TGT to HMS over the Thrift connection on port 9083
5. HMS decrypts the ticket using its keytab, verifies Spark’s identity, and establishes the authenticated SASL session
6. Executor pods use the same configuration for authenticated operations
Data access:
1. Authenticated Spark job queries HMS for table metadata
2. HMS validates Kerberos tickets before serving metadata requests
3. Spark accesses underlying data in Amazon S3 using IRSA

Implementation workflow

The implementation involves three key stakeholders working together to establish the Kerberos-enabled communication:

Microsoft Active Directory Administrator

The Active Directory Administrator creates service accounts that are used for HMS and Spark jobs. This involves setting up the service principal names using the setspn utility and generating keytab files using ktpass for secure credential storage. The administrator configures the appropriate Active Directory permissions and Kerberos AES256 encryption type. Finally, the keytab files are uploaded to AWS Secrets Manager for secure distribution to Kubernetes workloads.

Data Platform Team

The platform team handles the Amazon EMR on EKS and Kubernetes configurations. They retrieve keytabs from Secrets Manager and create Kubernetes secrets for the workloads. They configure Helm charts for HMS deployment with Kerberos settings and set up ConfigMaps for krb5.conf, jaas.conf, and core-site.xml.

Data Engineering Operations

Data engineers submit jobs using the configured service account with Kerberos authentication. They monitor job execution and verify authenticated access to HMS.

Deploy the solution

In the remainder of this post, you will explore the implementation details for this solution. You can find the sample code in the AWS Samples GitHub repository. For additional details, including verification steps for each deployment stage, refer to the README in the repository.

Prerequisites

Before you deploy this solution, make sure that the following prerequisites are in place:

Access to a valid AWS account and permission to create AWS resources.
The AWS Command Line Interface (AWS CLI) is installed on your local machine.
Git, Docker, eksctl, kubectl, Helm, envsubst, jq, and yq utilities are installed on your local machine.
Familiarity with Kerberos, Apache Hive Metastore (HMS), Apache Spark, Kubernetes, Amazon EKS, and Amazon EMR on Amazon EKS.

Clone the repository and set up environment variables

Clone the repository to your local machine and set the two environment variables. Replace <AWS_REGION> with the AWS Region where you want to deploy these resources.

# Clone the Git repository
git clone https://github.com/aws-samples/sample-emr-eks-spark-kerberos-hms.git
cd sample-emr-eks-spark-kerberos-hms

# Set environment variables
export REPO_DIR=$(pwd)
export AWS_REGION=<AWS_REGION>

Setup Microsoft Active Directory infrastructure

In this section, we deploy a self-managed Microsoft Active Directory with KDC on a Windows Server EC2 instance into a dedicated VPC. This is an intentionally minimal implementation highlighting only the key components required for this blog post.

cd ${REPO_DIR}/microsoft-ad
./setup.sh

Setup EKS infrastructure

This section provisions the Amazon EMR on EKS infrastructure stack, including VPC, EKS cluster, Amazon Aurora PostgreSQL database, Amazon Elastic Container Registry (Amazon ECR), Amazon S3, Amazon EMR on EKS virtual clusters and the Spark Operator. Run the following script.

cd ${REPO_DIR}/data-infra
./setup.sh

Set up VPC peering

This section establishes network connectivity between the Active Directory VPC and EKS VPC for Kerberos authentication. Run the following script:

cd ${REPO_DIR}/vpc-peering
./setup.sh

Deploy Hive Metastore with Kerberos authentication

This section deploys a Kerberos-enabled HMS service on the EKS cluster. Complete the following steps:

Create Kerberos Service Principal for HMS service

cd ${REPO_DIR}/microsoft-ad/
# Create HMS service principal
./manage-ad-service-principals.sh create hive "hive/hive-metastore-svc.hive-metastore.svc.cluster.local"
# Verify the service principal was created
./manage-ad-service-principals.sh list

Deploy HMS service with Kerberos authentication

cd ${REPO_DIR}/hive-metastore
./deploy.sh

Set up Amazon EMR on Amazon EKS with Kerberos authentication

This section configures Spark jobs to authenticate with Kerberos-enabled HMS. This involves creating service principles for Spark jobs and generating the necessary configuration files. Complete the following steps:

Create Service Principal for Spark jobs

cd ${REPO_DIR}/microsoft-ad/
# Create Spark service principal
./manage-ad-service-principals.sh create spark "spark/analytics-team"
# Verify the service principal was created
./manage-ad-service-principals.sh list

Generate Kerberos configurations for Spark jobs

cd ${REPO_DIR}/spark-jobs/
./generate-spark-configs.sh --principal "spark/[email protected]" --namespace emr

Submit Spark jobs

This section verifies Kerberos authentication by running a Spark job that connects to the Kerberized HMS. Complete the following steps:

Submit the test Spark job

cd ${REPO_DIR}/spark-jobs
kubectl apply -f spark-job.yaml

Monitor job execution

# Watch the SparkApplication status
kubectl get sparkapplications -n emr -w
# Check pod status
kubectl get pods -n emr | grep "spark-kerberos"

Verify Kerberos authentication and HMS connection

# Check Spark driver logs for successful authentication
kubectl logs spark-kerberos-job-driver -n emr

The logs should confirm successful authentication, along with a listing of sample databases and tables.

Understanding Kerberos configuration

The HMS requires specific configuration parameters to enable Kerberos authentication, applied through the previously mentioned steps. The key configurations are outlined in the following section.

HMS configuration (metastore-site.xml)

The following configurations are added to metastore-site.xml file.

Setting	Value	Purpose
`hive.metastore.sasl.enabled`	true	Enable SASL authentication
`hive.metastore.kerberos.principal`	`hive/hive-metastore-svc.hive-metastore.svc.cluster.local@CORP.KERBEROS`	HMS service principal
`hive.metastore.kerberos.keytab.file`	`/etc/security/keytab/hive.keytab`	Keytab path

Hadoop security (core-site.xml)

The following configurations are added to the core-site.xml file.

Setting	Value
`hadoop.security.authentication`	kerberos
`hadoop.security.authorization`	true

Spark configuration

Setting	Value	Purpose
`spark.security.credentials.kerberos.enabled`	true	Enable Kerberos for Spark
`spark.hadoop.hive.metastore.sasl.enabled`	true	SASL for HMS connection
`spark.kerberos.principal`	`spark/[email protected]`	Spark service principal
`spark.kerberos.keytab`	`local:///etc/security/keytab/analytics-team.keytab`	Keytab path

Shared Kerberos files

Both HMS and Spark pods mount two common Kerberos configuration files: krb5.conf and jaas.conf, using ConfigMaps and Kubernetes secrets. The krb5.conf file is identical across both services and defines how each component connects to the KDC. The jaas.conf file follows the same structure but differs in the principal and keytab path for each service.

krb5 Configuration

[libdefaults]
	default_realm = CORP.KERBEROS
	dns_lookup_realm = false
	dns_lookup_kdc = false
	ticket_lifetime = 24h
	forwardable = true
	udp_preference_limit = 1
	default_tkt_enctypes = aes256-cts-hmac-sha1-96 aes128-cts-hmac-sha1-96
	default_tgs_enctypes = aes256-cts-hmac-sha1-96 aes128-cts-hmac-sha1-96
	permitted_enctypes = aes256-cts-hmac-sha1-96 aes128-cts-hmac-sha1-96

[realms]
	CORP.KERBEROS = {
		kdc = <ad-server-ip>
		admin_server = <ad-server-ip>
	}

[domain_realm]
	.corp.kerberos = CORP.KERBEROS
	corp.kerberos = CORP.KERBEROS

For more information, see the online documentation for krb5.conf.

JAAS configuration

Client {
 com.sun.security.auth.module.Krb5LoginModule required
 useKeyTab=true
 keyTab="/etc/security/keytab/hive.keytab"
 principal="hive/hive-metastore-svc.hive-metastore.svc.cluster.local@CORP.KERBEROS"
 useTicketCache=false
 storeKey=true
 debug=false;
};

Additional security considerations

This post focuses on core Kerberos authentication mechanics between Spark and HMS. We recommend two additional security hardening steps based on your organization’s security posture and compliance requirements.

Protecting Keytabs at Rest with AWS KMS Envelope Encryption

Keytabs stored as Kubernetes Secrets are only base64-encoded by default, not encrypted at rest. We recommend enabling EKS envelope encryption using an AWS Key Management Service (AWS KMS) customer managed key. With envelope encryption, secret data is encrypted with a Data Encryption Key (DEK), which is encrypted by your customer managed key. This protects keytab content even if the etcd datastore is compromised. To enable this on an existing EKS cluster:

aws eks associate-encryption-config \
  --cluster-name <your-cluster> \
  --encryption-config '[{"resources":["secrets"],"provider":{"keyArn":"arn:aws:kms:<region>:<account-id>:key/<key-id>"}}]'

Refer to the Amazon EKS documentation on envelope encryption for full setup guidance.

Encrypting the Thrift Data Channel with TLS

SASL with Kerberos provides mutual authentication but doesn’t automatically encrypt data over the Thrift connection. Many deployments default to auth QoP, leaving the data channel unencrypted. We recommend either:

Set SASL QoP to auth-conf — enables SASL-layer encryption using Kerberos session keys
Layer TLS over Thrift (preferred) — enables transport-level encryption using modern cipher suites

Enabling TLS on HiveServer2 / Hive Metastore Thrift:

<property>
  <name>hive.server2.use.SSL</name>
  <value>true</value>
</property>
<property>
  <name>hive.server2.keystore.path</name>
  <value>/etc/tls/keystore.jks</value>
</property>

Refer to the Hive SSL/TLS configuration documentation for full details.

Cleaning up

To avoid incurring future charges, clean up all provisioned resources during this setup by executing the following cleanup script.

cd ${REPO_DIR}/
./cleanup.sh

Conclusion

In this post, we demonstrated how to implement Kerberos authentication for Amazon EMR on EKS to securely connect to a Kerberos-enabled HMS. This solution addresses a common challenge faced by organizations with existing Kerberos-enabled HMS deployments who want to adopt Amazon EMR on EKS while maintaining their Kerberos-enabled security posture.

This pattern applies whether you’re migrating from on-premises Hadoop, running hybrid Amazon EMR on EC2 or Amazon EMR on EKS environments, or building a new cloud-native platform. Any scenario where Spark jobs on Kerberos must authenticate with a shared, Kerberos-enabled HMS.

You can use this post as a starting point to implement this pattern and extend it further to suit your organization’s data platform needs.

About the authors

Agentic AI for observability and troubleshooting with Amazon OpenSearch Service

2026-04-03 Muthu Pitchaimani

Post Syndicated from Muthu Pitchaimani original https://aws.amazon.com/blogs/big-data/agentic-ai-for-observability-and-troubleshooting-with-amazon-opensearch-service/

Amazon OpenSearch Service powers observability workflows for organizations, giving their Site Reliability Engineering (SRE) and DevOps teams a single pane of glass to aggregate and analyze telemetry data. During incidents, correlating signals and identifying root causes demand deep expertise in log analytics and hours of manual work. Identifying the root cause remains largely manual. For many teams, this is the bottleneck that delays service recovery and burns engineering resources.

We recently showed how to build an Observability Agent using Amazon OpenSearch Service and Amazon Bedrock to reduce Mean time to Resolution (MTTR). Now, Amazon OpenSearch Service brings many of these functions to the OpenSearch UI—no additional infrastructure required. Three new agentic AI features are offered to streamline and accelerate MTTR:

An Agentic Chatbot that can access the context and the underlying data that you’re looking at, apply agentic reasoning, and use tools to query data and generate insights on your behalf.
An Investigation Agent that deep-dives across signal data with hypothesis-driven analysis, explaining its reasoning at every step.
An Agentic Memory that supports both agents, so their accuracy and speed improve the more you use them.

In this post, we show how these capabilities work together to help engineers go from alert to root cause in minutes. We also walk through a sample scenario where the Investigation Agent automatically correlates data across multiple indices to surface a root cause hypothesis.

How the agentic AI capabilities work together

These AI capabilities are accessible from OpenSearch UI through an Ask AI button, as shown in the following diagram, which gives an entry point for the Agentic Chatbot.

Agentic Chatbot

To open the chatbot interface, choose Ask AI.

The chatbot understands the context of the current page, so it understands what you’re looking at before you ask a question. You can ask questions about your data, initiate an investigation, or ask the chatbot to explain a concept. After it understands your request, the chatbot plans and uses tools to access data, including generating and running queries in the Discover page, and applies reasoning to produce a data-driven answer. You can also use the chatbot in the Dashboard page, initiating conversations from a particular visualization to get a summary as shown in the following image.

Investigation agent

Many incidents are too complex to resolve with one or two queries. Now you can get the help of the investigation agent to handle these complex situations. The investigation agent uses the plan-execute-reflect agent, which is designed for solving complex tasks that require iterative reasoning and step-by-step execution. It uses a Large Language Model (LLM) as a planner and another LLM as an executor. When an engineer identifies a suspicious observation, like an error rate spike or a latency anomaly, they can ask the investigation agent to investigate. One of the important steps the investigation agent performs is re-evaluation. The agent, after executing each step, reevaluates the plan using the planner and the intermediate results. The planner can adjust the plan if necessary or skip a step or dynamically add steps based on this new information. Using the planner, the agent generates a root cause analysis report led by the most likely hypothesis and recommendations, with full agent traces showing every reasoning step, all findings, and how they support the final hypotheses. You can provide feedback, add your own findings, iterate on the investigation goal, and review and validate each step of the agent’s reasoning. This approach mirrors how experienced incident responders work, but completes automatically in minutes. You can also use the “/investigate” slash command to initiate an investigation directly from the chatbot, building on an ongoing conversation or starting with a different investigation goal.

Agent in action

Automatic query generation

Consider a situation where you’re an SRE or DevOps engineer and received an alert that a key service is experiencing elevated latency. You log in to the OpenSearch UI, navigate to the Discover page, and select the Ask AI button. Without any expertise in the Piped Processing Language (PPL) query language, you enter the question “find all requests with latency greater than 10 seconds”. The chatbot understands the context and the data that you’re looking at, thinks through the request, generates the right PPL command, and updates it in the query bar to get you the results. And if the query runs into any errors, the chatbot can learn about the error, self-correct, and iterate on the query to get the results for you.

Investigation and investigation management

For complex incidents that normally require manually analyzing and correlating multiple logs for the possible root cause, you can choose Start Investigation to initiate the investigation agent. You can provide a goal for the investigation, along with any context or hypothesis that you want to instruct the investigation. For example, “identify the root cause of widespread high latency across services. Use TraceIDs from slow spans to correlate with detailed log entries in the related log indices. Analyze affected services, operations, error patterns, and any infrastructure or application-level bottlenecks without sampling”.

The agent, as part of the conversation, will offer to investigate any issue that you’re trying to debug.

The agent sets goals for itself along with any other relevant information like indices, associated time range, and other, and asks for your confirmation before creating a Notebook for this investigation. A Notebook is a way within the OpenSearch UI to develop a rich report that’s live and collaborative. This helps with the management of the investigation and allows for reinvestigation at a later date if necessary.

After the investigation starts, the agent will perform a quick analysis by log sequence and data distribution to surface outliers. Then, it will plan for the investigation into a series of actions, and then performs each action, such as query for a specific log type and time range. It will reflect on the results at every step, and iterate on the plan until it reaches the most likely hypotheses. Intermediate results will appear on the same page as the agent works so that you can follow the reasoning in real time. For example, you find that the Investigation Agent accurately mapped out the service topology and used it as a key intermediary steps for the investigation.

As the investigation completes, the investigation agent concludes that the most likely hypothesis is a fraud detection timeout. The associated finding shows a log entry from the payment service: “currency amount is too big, waiting for fraud detection”. This matches a known system design where large transactions trigger a fraud detection call that blocks the request until the transaction is scored and assessed. The agent arrived at this finding by correlating data across two separate indices, a metrics index where the original duration data lived, and a correlated log index where the payment service entries were stored. The agent linked these indices using trace IDs, connecting the latency measurement to the specific log entry that explained it.

After reviewing the hypothesis and the supporting evidence, you find the result reasonable and aligns with your domain knowledge and past experiences with similar issues. You can now accept the hypothesis and review the request flow topology for the affected traces that were provided as part of the hypothesis investigation.

Alternatively, if you find that the initial hypothesis wasn’t helpful, you can review the alternative hypothesis at the bottom of the report and select any of the alternative hypotheses if there’s one that’s more accurate. You can also trigger a re-investigation with additional inputs, or corrections from previous input so that the Investigation Agent can rework it.

Getting started

You can use any of the new agentic AI features (limits apply) in the OpenSearch UI at no cost. You will find the new agentic AI features ready to use in your OpenSearch UI applications, unless you have previously disabled AI features in any OpenSearch Service domains in your account. To enable or disable the AI features, you can navigate to the details page of the OpenSearch UI application in AWS Management Console and update the AI settings from there. Alternatively, you can also use the registerCapability API to enable the AI features or use the deregisterCapability API to disable them. Learn more at Agentic AI in Amazon OpenSearch Services.

The agentic AI feature uses the identity and permissions of the logged in users for authorizing access to the connected data sources. Make sure that your users have the necessary permissions to access the data sources. For more information, see Getting Started with OpenSearch UI.

The investigation results are saved in the metadata system of OpenSearch UI and encrypted with a service managed key. Optionally, you can configure a customer managed key to encrypt all of the metadata with your own key. For more information, see Encryption and Customer Managed Key with OpenSearch UI.

The AI features are powered by Claude Sonnet 4.6 model in Amazon Bedrock. Learn more at Amazon Bedrock Data Protection.

Conclusion

The new agentic AI capabilities announced for Amazon OpenSearch Service help reduce Mean Time to Resolution by providing context-aware agentic chatbot for assistance, hypothesis-driven investigations with full explainability, and agentic memory for context consistency. With the new agentic AI capabilities, your engineering team can spend less time writing queries and correlating signals, and more time acting on confirmed root causes. We invite you to explore these capabilities and experiment with your applications today.

About the authors

Improve the discoverability of your unstructured data in Amazon SageMaker Catalog using generative AI

2026-04-01 Nishchai JM

Post Syndicated from Nishchai JM original https://aws.amazon.com/blogs/big-data/improve-the-discoverability-of-your-unstructured-data-in-amazon-sagemaker-catalog-using-generative-ai/

Every day, businesses generate massive amounts of unstructured data such as PDFs, images, emails, customer feedback. Although this data holds valuable business insights, extracting meaningful value from it remains a significant challenge. Its lack of proper context and searchability often keeps it siloed and underutilized, limiting data-driven decision making. Financial reports, legal documents, and customer feedback are prime examples. They contain the answers that your business needs, yet they frequently go unanalyzed due to these barriers.The sheer volume of unstructured content requires scalable infrastructure and automated processing tools, while sensitive information embedded within demands sophisticated classification and protection strategies. Without proper management, organizations face operational inefficiencies, high costs, and increased regulatory risks and reduced AI effectiveness.

What if business context from your PDFs, images, and emails could be automatically extracted and surfaced wherever your teams search for information? In this post, we show you how to implement this. By combining Amazon SageMaker Catalog with generative AI capabilities, you can make unstructured data searchable and queryable through the same interfaces that your teams use for structured data analysis. Success requires balancing advanced AI techniques with governance frameworks so that your data is discoverable and secure for better decision making.

This is a two-part series post. In the first part, we walk you through how to set up the automated processing for unstructured documents, extract and enrich metadata using AI, and make your data discoverable through SageMaker Catalog. The second part is currently in the works and will show you how to discover and access the enriched unstructured data assets as a data consumer. By the end of this post, you will understand how to combine Amazon Textract and Anthropic Claude through Amazon Bedrock to extract key business terms and enrich metadata using Amazon SageMaker Catalog to transform unstructured data into a governed, discoverable asset.

Solutions overviewYou will transform unstructured data into an interactive knowledge base through automated processing within the Amazon SageMaker AI environment. Here is how it works:

You will set up an Amazon SageMaker Unified Studio Data Notebook Jupyter-based workspace where you manage your entire processing pipeline, add metadata to unstructured documents like PDFs, photos, emails, or audio recordings stored in Amazon Simple Storage Service (Amazon S3).
You will add your files to the SageMaker Project.
Amazon Textract extracts information and insights from text, removing manual transcription. This extracted content instantly populates your asset’s README.
Amazon Bedrock turns the text into business terms that provide SageMaker Catalog assets the correct business context to help with semantic search or business query search.
You will use a publish method to publish the enriched data to the Amazon SageMaker Catalog, making it available to your organization.

The architecture sets up a pipeline from processing raw documents to enabling end users interaction, with the Amazon SageMaker Catalog serving as the central hub for sending and receiving data. Amazon SageMaker Catalog includes generative AI features that automatically develop and add business descriptions for structured data assets. This capability streamlines documentation processes and provides greater consistency across data assets. You can further enhance this solution to create summaries by also reading and incorporating S3 metadata. This will add more context, such as object properties, access patterns, and storage characteristics, to the extracted document content, streamlining the process to find and catalog data.

Prerequisites

To implement the solution, you must complete the following prerequisites:

Create an AWS account – Required to access all AWS services (Amazon SageMaker Catalog, Amazon S3, Amazon Textract, Amazon Bedrock) used in this solution.
Create an Amazon SageMaker Unified Studio domain: This provides a collaborative environment for connecting your assets, users, and their projects.
Create an SageMaker Project with all capabilities: Your collaborative workspace where you will upload documents, run processing notebooks, and manage permissions for your data enrichment pipeline. Team members added to this project gain immediate access to all shared resources.
- Producer Project (project name: $(-your-project-name) use “unstructured-producer-project”, project profile: All capabilities)

Solution deployment

Now let’s complete the following steps to deploy and verify the solution.

Prepare source datasets

In this section you will use the following sample datasets by downloading them to your local machine. We will upload these files into your SageMaker Project S3 bucket created in the prerequisite step.

ED_DistributionToothDisorder.png: The dataset shows emergency department visits for tooth disorders in the US from 2020–2022, broken down by age group, gender, and race/ethnicity.
analysisDentalEDvsts.pdf: This report shows emergency department visits analysis for dental conditions across the United States between 2016–2019, showing that among non-traumatic dental visits.
s3_document_processor_unstructured.ipynb notebook (keep in local environment and will be used at a later stage)

With your sample datasets ready, let’s log in to SageMaker Unified Studio and upload them to your project.

Log in to Amazon SageMaker Unified Studio as a data producer

Log in to the SageMaker Unified Studio URL using your username and password. In the portal UI, select the producer project (unstructured-producer-project) that you created in the project selector (at the top center of the screen).
Under Data, do the following:
- Choose the default project bucket created amazon-sagemaker-12*********-us-west-2-51271642b525/dzd_*********/c3jl67qvxbic9c/.
- Next, choose the three dots and upload the downloaded files (1&2) from the prepared dataset section.
- After adding the files, choose Publish to Catalog to publish your asset.

Your files are now in the catalog. Before we process them, your project needs permission to access the services. Let’s add those permissions.

Add permissions to an IAM role for the Amazon SageMaker Project role.
- Go to the Project overview tab and find the Project role ARN. It can be found in the Project details section.
- Go to the AWS IAM service and choose Roles. Search for the role as highlighted in the preceding image and add the following permissions. The following policies use full-access managed policies to keep things straightforward for this walkthrough. We don’t recommend this for production environments. Instead, we encourage you to take a moment to review each policy with your security team and scope them down to the least-privilege permissions that your workload needs:
  - Add an AmazonBedrockFullAccess managed policy.
  - Add an AmazonTextractFullAccess managed policy.
  - Add an AmazonS3FullAccess managed policy.
  - Add this inline policy to project policy
```
{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": [
                "datazone:Search",
                "datazone:GetAsset",
                "datazone:CreateAssetRevision",
                "kms:Decrypt"
            ],
            "Resource": "*"
        }
    ]
}
```

With permissions configured, let’s set up the governance framework that will classify your documents. We will create glossary terms to tag sensitive and non-sensitive data.

Add Glossary and Glossary terms.
- Choose Glossaries and CREATE GLOSSARY.
- Add the following:
- Name of the glossary and descriptions.
- Toggle on the Enable button as shown in the following screenshot.
- Create an appropriate glossary term. You will add this term to your business metadata.
- Navigate to the Discover menu in the top navigation bar.
- Choose Glossaries, and then select Create term.
- Ensure that you’re creating the term under the Confidentiality glossary that you created in step 4.
- Create two terms (sensitive and non-sensitive) and add a description. Make sure that the Enabled toggle is on to enable the new term.

Now that we have configured the necessary permissions and created our glossary terms, let’s proceed with building the business metadata.

Build business metadata

In this section, you will use Amazon Textract and Amazon Bedrock to automatically build and curate business metadata for your assets using a SageMaker Unified Studio Notebook.

From the Project Overview page, access the Compute section in the left menu.
- Navigate to the Spaces tab.
- Choose the default space created by your project(default-985-) to begin”.

Open the space details page by selecting the Name (default-****).
- Under Actions, Choose Open space to be taken to the Data Notebook workspace (Jupyter based).

After connected, upload the downloaded notebook from the prerequisite step in your JupyterLab interface by either dragging it into the File browser or using the upload icon.

Before running the notebook, let’s understand what each cell does and how they work together to transform your documents into discoverable assets.

The notebook contains code for processing your documents. It begins by setting up AWS service connections using Boto3, the SDK for Python, importing necessary libraries, and initializing clients for Amazon S3, Amazon Textract, and Amazon Bedrock. The code configures an S3 bucket for processing medical documents.

Now, proceed to run through the individual cells:

This cell searches an S3 bucket for files with specific extensions (.pdf, .jpg, .jpeg, .png, .tiff), collects them into a list called ‘documents’, and prints the total count and names of found files. It uses the list_objects_v2 method to fetch the contents and filters them based on their file extensions.

The documents extracted from the S3 bucket are processed using Amazon Textract. It loops through each document, starts an Amazon Textract job, monitors its progress, and when successful, extracts text from all pages. The extracted text is stored in a list along with its document identifier. The code handles pagination, errors, and includes delays between API calls to prevent throttling.

Output [1] The following screenshot shows the output of a Jupyter notebook cell after you run the Amazon Textract API.

This code takes all previously extracted document text, combines it into one string (2,002 characters total), and uses the Anthropic Claude 3 Sonnet model (available through Amazon Bedrock) to generate a concise summary. The code configures the AI model with specific parameters, sends the combined text for analysis, and returns a summarized version of all document content.

Output [2] The following screenshot shows the proceeding output result.

This code detects whether document text contains sensitive PII data (names, emails, addresses, financial details) and returns a Boolean true/false result.

Finally, the code completes the document processing pipeline by classifying the asset based on sensitivity, updating its metadata with an AI-generated summary, and assigning the appropriate glossary term. The script retrieves existing asset details to preserve all metadata forms, updates the README field with the summary content, and creates a new revision. This record of the classification and documentation is stored in the data catalog, accessible alongside your source assets for ongoing governance.

Output: The following screenshot shows the output result.

After generating metadata with Amazon Textract and Amazon Bedrock, republish the data to make it discoverable to users. Note the README and the Glossary terms have already been added based on the previous script.

To republish unstructured data with enriched metadata, go to your producer project and choose Re-publish asset.

To search for the asset that was published, choose one of the keywords from the README sections. For this example, we search using these keywords high percent of ED visits.

Go to Home in the search bar to enter the keyword. Then choose the asset as displayed in the following screenshot:

As shown in the preceding image, the search results display the asset name along with the project it belongs to. Select the asset to view its details, where you will find rich business metadata, lineage information, and more, as shown in the following screenshot.

With the asset published and metadata enriched, data assets are ready to be used.

Clean up

To avoid ongoing charges, make sure to delete the resources immediately after completing the tutorial:

Stop Studio Resources – Close all running notebooks – Stop any running notebook instances – Shut down unused kernels. Running instances continue to incur charges even when not actively used.
Clean S3 Storage – Delete any temporary files created during processing – Remove uploaded test documents if no longer needed. While Amazon S3 costs are minimal, large volumes of unneeded data can accumulate charges.

Conclusion

In this post we showed you how you can transform unstructured data into valuable business assets through seamless integration with AWS services. You can efficiently process documents using Amazon Textract for text extraction, harness the capabilities of Amazon Bedrock for intelligent term identification, and use Amazon SageMaker Catalog for metadata management—all within a secure, governed framework.

Additional resources

To continue your Amazon SageMaker AI journey, see the following resources:

1. - Explore the Amazon SageMaker Developer Guide.
  - Documentation – You can find complete setup guides in Amazon SageMaker Unified Studio Documentation.
  - Get started with these new integrations through the Amazon s Unified Studio console.

About the authors

Introducing enhancements to Amazon EMR Managed Scaling

2026-03-26 Amit Maindola

Post Syndicated from Amit Maindola original https://aws.amazon.com/blogs/big-data/introducing-enhancements-to-amazon-emr-managed-scaling/

Amazon EMR Managed Scaling has been helping customers automatically resize their clusters to optimize performance and reduce costs. We are excited to introduce a significant enhancement to this feature: Advanced Scaling for Amazon EMR. This new capability provides additional flexibility to configure the desired resource utilization or performance levels for your cluster using a utilization-performance slider. After the slider is set, EMR Managed Scaling intelligently scales the cluster and optimizes cluster resources based on your configured performance or resource utilization levels.

Customers appreciate the simplicity of EMR Managed Scaling, where they specify the minimum and maximum compute limits for their clusters and EMR Managed Scaling automatically resizes the cluster. EMR Managed Scaling continuously samples key metrics associated with the workloads running on clusters and scales up or down accordingly. However, customers’ workloads are increasingly getting more complex, with variability across dimensions such as data volumes and cost vs. SLA requirements. Consequently, customers prefer to have additional levers to tune the scaling behavior most suitable for their workload. In this post, we discuss the benefits of Advanced Scaling for Amazon EMR and demonstrate how it works through some example scenarios.

Advanced Scaling for Amazon EMR

Previously, customers who wanted to adjust the default EMR Managed Scaling behavior had no other option but to disable EMR Managed Scaling and create custom automatic scaling rules. Custom autoscaling rules created several problems:

Custom autoscaling rules are not shuffle-aware and shuffle data is lost.
Custom autoscaling is not aware of the application driver and can terminate it, failing the entire job.
Custom autoscaling can be slower to respond to real time needs.

These are some of the reasons why custom autoscaling is not the right fit. Customers wanted out-of-the-box support for Managed Scaling to handle the scaling that optimizes for the customers end goal to optimize cost or performance. The new Advanced Scaling capability enhances the existing benefits of EMR Managed Scaling by introducing additional controls and helping you configure the desired resource utilization or performance level for your cluster using a utilization-performance slider. EMR Advanced Scaling then internally translates intent into tailored algorithm strategy (UtilizationPerformanceIndex), such as how quickly to scale, how much to scale, and so on, to make scaling decisions for the cluster. This helps optimize cluster resources while making sure we meet the performance or resource utilization intent set by the customer.

For example, for a cluster running multiple tasks of relatively short duration (order of seconds), EMR Managed Scaling previously used to scale up the cluster aggressively and conservatively scale it down to avoid negative impact to job runtimes. Although this is the right approach for SLA-sensitive workloads, it might not be optimal for customers who are fine with little delay but prefers saving cost. Now, you can configure EMR Managed Scaling behavior suitable for your workload types, and we will apply tailored optimization to intelligently add or remove nodes from the clusters. This helps you achieve the optimal price-performance for your clusters along with increased flexibility of additional user-controls.

The value you set for Advanced Scaling optimizes your cluster to your requirements. Values range from 1-100. Supported values are 1, 25, 50, 75 and 100. If you set the index to values other than these, it results in a validation error. Scaling values map to resource-utilization strategies. The following list defines several of these:

Utilization optimized (1) – This setting prevents resource over provisioning. Use a low value when you want to keep costs low and to prioritize efficient resource utilization. It causes the cluster to scale up less aggressively. This works well for the use case when there are regularly occurring workload spikes and you don’t want resources to ramp up too quickly.
Balanced (50) – This balances resource utilization and job performance. This setting is suitable for steady workloads where most stages have a stable runtime. It’s also suitable for workloads with a mix of short and long-running stages. We recommend starting with this setting if you aren’t sure which to choose.
Performance optimized (100) – This strategy prioritizes performance. The cluster scales up aggressively to ensure that jobs complete quickly and meet performance targets. Performance optimized is suitable for service-level-agreement (SLA) sensitive workloads where fast run time is critical.

Customers can also choose intermediate values (25 and 75) for more nuanced control. The intermediate values available provide a middle ground between strategies to fine tune your cluster’s Advanced Scaling behavior.

Use cases and benefits

Amazon EMR’s Advanced Scaling feature improves cluster management by offering dynamic adaptation to diverse business requirements across industries. The feature enables strategic timing of scaling policies throughout the day, with early morning hours dedicated to workload preparation, peak business hours focusing on maximum performance, evening periods maintaining moderate scaling for post-business processing, and overnight hours optimized for cost-effective batch operations. This comprehensive approach allows organizations to fine-tune their resource allocation based on specific operational patterns, ultimately delivering an optimal balance between performance and cost-efficiency while ensuring business needs are met across different time zones and usage patterns.

Scaling configuration

In the following sections, we walk through a range of scenarios testing against a 3 TB TPC-DS dataset, then walk you through the results of testing a sample job. We wanted to evaluate how Amazon EMR would respond with advanced scaling policies in scenarios optimizing cluster utilization, balancing performance with utilization, and aggressive performance requirements.

With Advanced Scaling currently available through API and console support coming soon, we updated existing cluster configurations. We modified UtilizationPerformanceIndex with 1, 50, and 100, to correspond to the different scaling strategies using the put-managed-scaling-policy API with an advanced scaling strategy, as seen in the following examples:

Scenario 1: Utilization optimized

In this scenario, we used a utilization optimized configuration by setting UtilizationPerformanceIndex to 1:

aws emr put-managed-scaling-policy --cluster-id <'cluster-id'> \ 
  --managed-scaling-policy '{ 
  "ComputeLimits": { 
    "UnitType": "Instances", 
    "MinimumCapacityUnits": 2, 
    "MaximumCapacityUnits": 50, 
    "MaximumOnDemandCapacityUnits": 50, 
    "MaximumCoreCapacityUnits": 2 
  	}, 
  }'

The result of the test yielded a peak of 16 nodes running and 16 requested. The scale-up and scale-down process is conservative. It takes 15 minutes to completely release the nodes after the requested metric subsides, as shown in the following figure. The job completed in 12 minutes, 39 seconds. UtilizationPerformanceIndex of 1 or 25 can be useful when the cluster is running a sequence of jobs with little to zero idle time. It can prevent frequent node churn because nodes will be available for the next set of jobs.

Scenario 2: Balanced

In this scenario, we used a balanced configuration by setting UtilizationPerformanceIndex to 50:

aws emr put-managed-scaling-policy --cluster-id <'cluster-id'> \ 
  --managed-scaling-policy '{ 
  "ComputeLimits": { 
    "UnitType": "Instances", 
    "MinimumCapacityUnits": 2, 
    "MaximumCapacityUnits": 50, 
    "MaximumOnDemandCapacityUnits": 50, 
    "MaximumCoreCapacityUnits": 2 
  	}, 
  }'

The result of the test yielded a peak of 43 nodes running and 32 requested. UtilizationPerformanceIndex of 50 uses a balanced approach for scaling the resources. Nodes requested and running are higher such that you can get a better price-performance ratio. The job completed in 7 minutes, 1 second.

Scenario 3: Performance optimized

In this scenario, we used a performance optimized configuration by setting UtilizationPerformanceIndex to 100:

aws emr put-managed-scaling-policy --cluster-id <'cluster-id'> \ 
  --managed-scaling-policy '{ 
  "ComputeLimits": { 
    "UnitType": "Instances", 
    "MinimumCapacityUnits": 2, 
    "MaximumCapacityUnits": 50, 
    "MaximumOnDemandCapacityUnits": 50, 
    "MaximumCoreCapacityUnits": 2 
	  }, 
  }'

The result of the test yielded a peak of 50 nodes running and 46 requested. UtilizationPerformanceIndex of 100 delivers the highest performance by aggressively scaling resources up and down. You can expect the highest nodes requested and running in this configuration. Scale-down will closely follow the node requested metric and therefore can lead to frequent churn of nodes if there are short idle periods between job submissions. This setting is ideal for latency-sensitive workloads that need to finish under SLA. The example job completed in 6 minutes, 16 seconds.

Comparison

The following table summarizes the differences between these scaling methods and time taken for each.

Scaling Method	Utilization Index	Peak Total Nodes Requested	Peak Total Nodes Running	Job Run Time (Seconds)	Cost to Run job	Use Case
Scenario1 – Utilization optimized	1	16	16	759	Low	Workloads with regular spikes; prioritizes cost efficiency with conservative scaling
Scenario 2 – Balanced	50	32	43	421	Medium	Steady workloads with mixed stage durations; recommended starting point
Scenario 3 – Performance Optimized	100	46	50	376	High	SLA-sensitive workloads requiring fast completion times

Advanced Managed Scaling in Amazon EMR introduces a more nuanced approach to cluster management through the customized scaling strategies to meet your business requirements. This spectrum offers fine-grained control over how clusters respond to workload demands. At one end, with a utilization optimized configuration of 1, the system prioritizes efficient resource usage, scaling up conservatively to maintain cost-effectiveness and taking advantage of existing cluster resources. In the balanced configuration at 50, the strategy aims to strike an equilibrium between resource utilization and job performance. To meet performance SLAs, the performance optimized value of 100 showed aggressive scaling responding to increased demand for resources quickly, regardless of resource consumption. This granular control helps you fine-tune your cluster’s behavior based on your specific needs, balancing cost, efficiency, and performance.

Conclusion

To Summarize, Advanced Scaling for Amazon EMR represents an advancement in cluster management, offering greater control and efficiency. By fine-tuning your clusters’ behavior, you can achieve more cost-effective and performant big data processing. We encourage you to try this new feature and discover how it can optimize your EMR workloads. Start by experimenting with different UtilizationPerformanceIndex values and closely monitor your cluster’s performance and cost metrics. Over time, you will be able to find the perfect balance that meets your specific needs.

To learn more about Amazon EMR Managed Scaling and Advanced Scaling, refer to our documentation. We’re excited to see how you use this new capability to enhance your big data processing on AWS, and we look forward to your feedback as we continue to evolve and improve our services.

About the authors

Build AWS Glue Data Quality pipeline using Terraform

2026-03-26 Viquar Khan

Post Syndicated from Viquar Khan original https://aws.amazon.com/blogs/big-data/build-aws-glue-data-quality-pipeline-using-terraform/

AWS Glue Data Quality is a feature of AWS Glue that helps maintain trust in your data and support better decision-making and analytics across your organization. It allows users to define, monitor, and enforce data quality rules across their data lakes and data pipelines. With AWS Glue Data Quality, you can automatically detect anomalies, validate data against predefined rules, and generate quality scores for your datasets. This feature provides flexibility in how you validate your data – you can incorporate quality checks into your ETL processes for transformation-time validation, or validate data directly against cataloged tables for ongoing data lake monitoring. By leveraging machine learning, it can also suggest data quality rules based on your data patterns.

You can use Terraform, an open source Infrastructure as Code (IaC) tool developed by HashiCorp, to deploy AWS Glue Data Quality pipelines.

It allows developers and operations teams to define, provision, and manage cloud infrastructure using a declarative language. With Terraform, you can version, share, and reuse your infrastructure code across multiple cloud providers and services. Its powerful state management and planning capabilities enable teams to collaborate efficiently and maintain consistent infrastructure across different environments.

Using Terraform to deploy AWS Glue Data Quality pipeline enables IaC best practices to ensure consistent, version controlled and repeatable deployments across multiple environments, while fostering collaboration and reducing errors due to manual configuration.

In this post, we explore two complementary methods for implementing AWS Glue Data Quality using Terraform:

ETL-based Data Quality – Validates data during ETL (Extract, Transform, Load) job execution, generating detailed quality metrics and row-level validation outputs
Catalog-based Data Quality – Validates data directly against Glue Data Catalog tables without requiring ETL execution, ideal for monitoring data at rest

Solution overview

This post demonstrates how to implement AWS Glue Data Quality pipelines using Terraform using two complementary approaches mentioned above to ensure comprehensive data quality across your data lake.

We’ll use the NYC yellow taxi trip data, a real-world public dataset, to illustrate data quality validation and monitoring capabilities. The pipeline ingests parquet-formatted taxi trip data from Amazon Simple Storage Service (Amazon S3) and applies comprehensive data quality rules that validate data completeness, accuracy, and consistency across various trip attributes.

Method 1: ETL-based Data Quality

ETL-based Data Quality validates data during Extract, Transform, Load (ETL) job execution. This approach is ideal for:

Validating data as it moves through transformation pipelines
Applying quality checks during data processing workflows
Generating row-level validation outputs alongside transformed data

The pipeline generates two key outputs:

Data Quality Results – Detailed quality metrics and rule evaluation outcomes stored in the dqresults/ folder, providing insights into data quality trends and anomalies
Row-Level Validation – Individual records with their corresponding quality check results written to the processed/ folder, enabling granular analysis of data quality issues

Method 2: Catalog-based Data Quality

Catalog-based Data Quality validates data quality rules directly against AWS Glue Data Catalog tables without requiring ETL job execution. This approach is ideal for:

Validating data at rest in the data lake
Running scheduled data quality checks independent of ETL pipelines
Monitoring data quality across multiple tables in a database

Architecture overview

The following diagram illustrates how both approaches work together to provide comprehensive data quality validation:

Source data stored in Amazon S3 (Yellow Taxi Data)
AWS Glue ETL processes data with quality checks
ETL validation results are stored in S3
AWS Glue Crawler discovers schema
Metadata is stored in AWS Glue Catalog
AWS Glue Data Quality validates catalog tables
Catalog validation results are stored in S3
Amazon CloudWatch monitors all operations

By using AWS Glue’s serverless ETL capabilities and Terraform’s infrastructure-as-code approach, this solution provides a scalable, maintainable, and automated framework for ensuring data quality in your analytics pipeline.

Prerequisites:

An AWS account with AWS CLI installed and configured
HashiCorp Terraform installed and configured

Solution Implementation

Complete the following steps to build AWS Glue Data Quality pipeline using Terraform:

Clone the Repository

This post includes a GitHub repository that generates the following resources when deployed. To clone the repository, run the following command in your terminal:

git clone https://github.com/aws-samples/sample-build-aws-glue-data-quality-pipeline-using-terraform.git
cd sample-build-aws-glue-data-quality-pipeline-using-terraform

Core Infrastructure:

Amazon S3 bucket: glue-data-quality-{AWS AccountID}-{env} with AES256 encryption
Sample NYC taxi dataset (sample-data.parquet) automatically uploaded to the data/ folder
AWS Identity and Access Management (IAM) role: aws-glue-data-quality-role-{env} with Glue execution permissions and S3 read/write access
CloudWatch dashboard: glue-data-quality-{env} for monitoring job execution and data quality metrics
CloudWatch Log Groups for job logging with configurable retention

ETL-Based Data Quality Resources:

AWS Glue ETL job: data-quality-pipeline with 8 comprehensive validation rules
Python script: GlueDataQualityDynamicRules.py stored in glue-scripts/ folder
Results storage in dqresults/ folder with detailed rule outcomes
Row-level validation outputs in processed/ folder
Optional scheduled triggers for automated execution
CloudWatch alarm: etl-glue-data-quality-failure-{env} for monitoring job failures

Catalog-Based Data Quality Resources (Optional – when catalog_dq_enabled = true):

Glue Database: {catalog_database_name} for catalog table management
Glue Crawler: {job_name}-catalog-crawler for automatic schema discovery from S3 data
Crawler schedule trigger for automated execution (default: daily at 4 AM)
Glue Catalog Tables automatically discovered and created by the crawler
Catalog Data Quality job: {job_name}-catalog with 7 catalog-specific validation rules
Python script: CatalogDataQuality.py for catalog validation
Results storage in catalog-dq-results/ folder partitioned by table name
Catalog DQ schedule trigger for automated validation (default: daily at 6 AM)
CloudWatch alarm: catalog-glue-data-quality-failure-{env} for monitoring catalog job failures
Enhanced CloudWatch dashboard widgets for crawler status and catalog metrics

Review the Glue Data Quality Job Script

Review the Glue Data Quality job script GlueDataQualityDynamicRules.py located in the folder scripts, which has the following rules:

Rules = [
    CustomSql "select vendorid from primary where passenger_count > 0" with threshold > 0.9,
    Mean "trip_distance" < 150,
    Sum "total_amount" between 1000 and 100000,
    RowCount between 1000 and 1000000,
    Completeness "fare_amount" > 0.9,
    DistinctValuesCount "ratecodeid" between 3 and 10,
    DistinctValuesCount "pulocationid" > 100,
    ColumnCount = 19
]

Brief explanation of rules for NY Taxi data is as follows:

Rule Type	Condition	Description
CustomSql	“select vendorid from primary where passenger_count > 0” with threshold > 0.9	Checks if at least 90% of rides have at least one passenger
Mean	“trip_distance” < 150	Ensures the average trip distance is less than 150 miles
Sum	“total_amount” between 1000 and 100000	Verifies that total revenue from all trips falls within this range
RowCount	between 1000 and 1000000	Checks if the dataset has between 1,000 and 1 million records
Completeness	“fare_amount” > 0.9	Ensures over 90% of records have a fare amount
DistinctValuesCount	“ratecodeid” between 3 and 10	Verifies rate codes fall between 3-10 unique values
DistinctValuesCount	“pulocationid” > 100	Checks if there are over 100 unique pickup locations
ColumnCount	19	Validates that dataset has exactly 19 columns

These rules together ensure data quality by validating volume, completeness, reasonable values and proper structure of the taxi trip data.

Configure Terraform Variables

Before deploying the infrastructure, configure your Terraform variables in the terraform.tfvars file located in the examples directory. This configuration determines which features will be deployed – ETL-based Data Quality only, or both ETL-based and Catalog-based Data Quality.

Basic Configuration

The solution uses default values for most settings, but you can customize the following in your terraform.tfvars file:

AWS Region – The AWS region where resources will be deployed
Environment – Environment identifier (such as, “dev”, “prod”) used in resource naming
Job Name – Name for the Glue job (default: data-quality-pipeline)

Enable Catalog-Based Data Quality

By default, the solution deploys only ETL-based Data Quality. To enable Catalog-based Data Quality validation, add the following configuration to your terraform.tfvars file:

# Enable Catalog-based Data Quality
catalog_dq_enabled = true

# Glue Database name for catalog tables
catalog_database_name = "taxi_data_catalog"

# S3 paths containing parquet data for catalog table creation
s3_data_paths = ["data/"]

# Optional: Specific table names to validate (empty = all tables in database)
catalog_table_names = []

# Data quality rules for catalog validation
catalog_dq_rules = <<EOF
Rules = [
  RowCount > 0,
  Completeness "vendorid" > 0.9,
  Completeness "passenger_count" > 0.95,
  Mean "trip_distance" < 150,
  ColumnCount > 5
]
EOF

# Enable scheduled execution for catalog data quality
catalog_enable_schedule = false
catalog_schedule_expression = "cron(0 6 * * ? *)"

# Crawler schedule for automatic table discovery
catalog_crawler_schedule = "cron(0 4 * * ? *)"

Configuration Notes:

catalog_dq_enabled – Set to true to enable Catalog-based validation alongside ETL-based validation,which will deploy both ETL and Catalog validation
catalog_database_name – Name of the Glue database that will be created for catalog tables
s3_data_paths – S3 folders containing parquet data that the Glue Crawler will discover
catalog_table_names – Leave empty to validate all tables, or specify specific table names
catalog_dq_rules – Define validation rules specific to catalog tables (can differ from ETL rules)
catalog_enable_schedule – Set to true to enable automatic scheduled execution
Schedule expressions – Use cron format for automated execution (crawler runs before DQ job)

Once you’ve configured your variables, save the terraform.tfvars file and proceed to the next step.

Set Up AWS CLI Authentication

Before you can interact with AWS services using the command line, you need to set up and authenticate the AWS CLI. This section guides you through the process of configuring your AWS CLI and verifying your authentication. Follow these steps to ensure you have the necessary permissions to access AWS resources.

Open your terminal or command prompt.
Set up authentication in the AWS CLI. You need administrator permissions to set up this environment.
```
aws configure
```

To test if your AWS CLI is working and you’re authenticated, run the following command:

aws sts get-caller-identity --output json

The output should look similar to the following:

{
   "UserId": "UUID123123:your_user",
  "Account": "111122223333",
"Arn": "arn:aws:sts::111122223333:assumed-role/some-role/your_user"
 }

Deploy with Terraform

Follow these steps to deploy your infrastructure using Terraform. This process will initialize your working directory, review planned changes, and apply your infrastructure configuration to AWS.

To deploy with Terraform, navigate to the examples folder by running the following command in your CLI from inside the repository

cd .\examples

Run the following bash commands:

terraform init

Initializes a Terraform working directory, downloads required provider plugins, and sets up the backend for storing state.

On success you will receive output Terraform has been successfully initialized!

terraform plan

Creates an execution plan, shows what changes Terraform will make to your infrastructure. This command doesn’t make any changes.

terraform apply

Deploys infrastructure and code to the AWS Account. By default, it asks for confirmation before making any changes. Use ‘terraform apply -auto-approve’ to skip the confirmation step.

When prompted with ‘Do you want to perform these actions?’, type ‘yes’ and press Enter to confirm and allow Terraform to execute the described actions.

Upon successful execution, the system will display ‘Apply complete!’ message.

Run the AWS Glue Data Quality Pipeline

After deploying the infrastructure with Terraform, you can validate data quality using two methods – ETL-based and Catalog-based. Each method serves different use cases and can be run independently or together.

Method 1: Run the ETL-Based Data Quality Job

ETL-based data quality validates data during the transformation process, making it ideal for catching issues early in your data pipeline.

Steps to execute:

Navigate to the AWS Glue Console and select ETL Jobs from the left navigation panel
Locate and select the job named data-quality-pipeline
Choose Run to start the job execution
Monitor the job status – it typically completes in 2-3 minutes

Review the results:

Once completed, click on the Data Quality tab to review the validation results.
The following screenshot shows the results.

Understanding AWS Glue Data Quality Results: NYC Taxi Data Example.

Rule Results Summary

We had 8 total rules
7 rules passed
1 rule failed

Rule	Rule Condition	Status	Pass/Fail Reason
Passenger Count Check	At least 90% of rides should have at least one passenger	Passed	95% of rides had passengers, exceeding 90% threshold
Trip Distance	Average trip < 150 miles	Passed	Average was 5.94 miles, well below 150-mile limit
Row Count	Between 1,000 and 1,000,000 records	Passed	63,441 records fell within required range
Fare Amount Completeness	90% of records should have fare amounts	Passed	100% completeness exceeded 90% requirement
Rate Code Variety	Between 3-10 different rate codes	Passed	7 unique codes fell within acceptable range
Pickup Locations	More than 100 different pickup locations	Passed	205 locations exceeded minimum requirement
Column Count	Exactly 19 columns	Passed	Exact match at 19 columns
Total Amount Range	Sum of all fares between $1,000 and $100,000	Failed	Total of $130,638.29 exceeded maximum limit

Check the S3 bucket for detailed outputs:
- Data Quality metrics: s3://glue-data-quality-{AccountID}-{env}/dqresults/
- Row-level validation: s3://glue-data-quality-{AccountID}-{env}/processed/

The job processes the NYC taxi data and applies all 8 validation rules during the ETL execution. You’ll see a quality score along with detailed metrics for each rule.

Method 2: Run the Catalog-Based Data Quality Pipeline

Catalog-based data quality validates data at rest in your data lake, independent of ETL processing. This method requires the Glue Crawler to first discover and catalog your data.

Run the Glue Crawler (first-time setup or when schema changes):
- Navigate to AWS Glue Console and select Crawlers
- Locate data-quality-pipeline-catalog-crawler
- Select data-quality-pipeline-catalog-crawler checkbox and click Run and wait for completion (1-2 minutes)
- Verify the table was created in your Glue database
Run the Catalog Data Quality Job:
- Navigate to the AWS Glue Console and select ETL Jobs from the left navigation panel
- Select the job named data-quality-pipeline-catalog
- Click Run job to execute the validation
- Monitor the job status until completion

Review the results:

Once completed, click on the Data Quality tab to review the validation results.

The following screenshot shows the results.

Rule Results Summary

We had 7 total rules
6 rules passed
1 rule failed

Rule	Rule Condition	Status	Pass/Fail Reason
Row Count	Row count should be greater than zero	Passed	63441 rows present in the source data file
Completeness “vendorid”	90% of records should have vendorid	Passed	100% completeness exceeded 90% requirement
Completeness “passenger_count”	95% of records should have vendorid	Passed	96% completeness exceeded 95% requirement
Mean “trip_distance”	Mean “trip_distance” < 150	Passed	trip_distance.Mean: 5.94 which is less than threshold 150
Sum “total_amount”	Sum “total_amount” between 1000 and 100000	Failed	total_amount.Sum: 1330638.29 which does not satisfy condition

Distinct vale count “ratecodeid”	DistinctValuesCount “ratecodeid” between 3 and 10	Passed	ratecodeid.DistinctValuesCount: 7, satisfies condition
Column Count	Greater than 5 columns	Passed	ColumnCount: 19, satisfies condition

Check the S3 bucket for detailed outputs s3://glue-data-quality-{AccountID}-{env}/catalog-dq-results/

Catalog vs ETL Data Quality Comparison

Feature	ETL Data Quality	Catalog Data Quality
Execution Context	Validates data during ETL job processing	Validates data against catalog tables at rest
Data Source	Reads directly from S3 files (parquet format)	Queries Glue Data Catalog tables
Results Location	s3://…/dqresults/	s3://…/catalog-dq-results/
Primary Use Case	Validate data quality during transformation pipelines	Monitor data lake quality independent of ETL workflows
Execution Trigger	Runs as part of Glue ETL job execution	Runs independently as scheduled Data Quality job
Scheduling	Configured via Glue job schedule or on-demand	Configured via Data Quality job schedule or on-demand
Table Discovery	Manual – requires explicit S3 path configuration	Automatic – Glue Crawler discovers schema and creates tables
Schema Management	Defined in ETL job script	Managed by Glue Data Catalog
Output Format	Data Quality metrics + row-level validation outputs	Data Quality metrics only
Best For	Catching issues early in data pipelines	Ongoing monitoring of data at rest in data lakes
Dependencies	Requires ETL job execution	Requires Glue Crawler to run first
CloudWatch Integration	Job-level metrics and logs	Data Quality-specific metrics and logs

Monitoring and Troubleshooting

Both data quality methods automatically send metrics and logs to Amazon CloudWatch. You can set up alarms to notify you when quality scores drop below acceptable thresholds.

Clean up

To avoid incurring unnecessary AWS charges, make sure to delete all resources created during this tutorial. Ensure you have backed up any important data before running these commands, as this will permanently delete the resources and their associated data. To destroy all resources created as part of this blog, run following command in your terminal:

terraform destroy

Conclusion

In this blog post, we demonstrated how to build and deploy a scalable data quality pipeline using AWS Glue Data Quality and Terraform. The solution implements two validation methods:

ETL-based Data Quality – Integrated validation during ETL job execution for transformation pipeline quality assurance
Catalog-based Data Quality – Independent validation against Glue Data Catalog tables for data lake quality monitoring

By implementing data quality checks on NYC taxi trip data, we showed how organizations can automate their data validation processes and maintain data integrity at scale. The combination of AWS Glue’s serverless architecture and Terraform’s infrastructure-as-code capabilities provides a powerful framework for implementing reproducible, version-controlled data quality solutions. This approach not only helps teams catch data issues early but also enables them to maintain consistent data quality standards across different environments. Whether you’re dealing with small datasets or processing massive amounts of data, this solution can be adapted to meet your organization’s specific data quality requirements. As data quality continues to be a crucial aspect of successful data initiatives, implementing automated quality checks using AWS Glue Data Quality and Terraform sets a strong foundation for reliable data analytics and decision-making.

To learn more about AWS Glue Data Quality, refer to the following:

About the authors

Automating data classification in Amazon SageMaker Catalog using an AI agent

2026-03-24 Ramesh H Singh

Post Syndicated from Ramesh H Singh original https://aws.amazon.com/blogs/big-data/automating-data-classification-in-amazon-sagemaker-catalog-using-an-ai-agent/

If you’re struggling with manual data classification in your organization, the new Amazon SageMaker Catalog AI agent can automate this process for you. Most large organizations face challenges with the manual tagging of data assets, which doesn’t scale and is unreliable. In some cases, business terms aren’t applied consistently across teams. Different groups name and tag data assets based on local conventions. This creates a fragmented catalog where discovery becomes unreliable and governance teams spend more time normalizing metadata than governing.

In this post, we show you how to implement this automated classification to help reduce the manual tagging effort and improve metadata consistency across your organization.

Amazon SageMaker Catalog provides automated data classification that suggests business glossary terms during data publishing. This helps to reduce the manual tagging effort and improve metadata consistency across organizations. This capability analyzes table metadata and schema information using Amazon Bedrock language models to recommend relevant terms from organizational business glossaries. Data producers receive AI-generated suggestions for business terms defined within their glossaries. These suggestions include both functional terms and sensitive data classifications such as PII and PHI, making it straightforward to tag their datasets with standardized vocabulary. Producers can accept or modify these suggestions before publishing, facilitating consistent terminology across data assets and improving data discoverability for business users.

The problem with manual classification

Manual tagging doesn’t scale effectively. Data producers interpret business terms differently, especially across domains. Critical labels like PII and PHI get missed because the publishing workflow is already complex. After assets enter the catalog with inconsistent terminology, search functionality and access controls quickly degrade.The solution isn’t only better training—it’s making the classification process predictable and consistent.

How automated classification works

The capability runs directly inside the publish workflow:

The catalog looks at the table’s structure—column names, types, whatever metadata exists.
That structure is sent to an Amazon Bedrock model that matches patterns against the organization’s glossary.
Producers receive a set of suggestions from the defined business glossary terms for classification that might include both functional and sensitive-data glossary terms.
They accept or adjust the suggestions before publishing.
The final list is written into the asset’s metadata using the controlled vocabulary.

The model evaluates column names, data types, schema patterns, and existing metadata. It maps those signals to the terms defined in the organization’s glossary. The suggestions are generated inline during publishing, with no separate Extract, Transform and Load (ETL) or batch processes to maintain. The accepted terms become part of the asset’s metadata and flow into downstream catalog operations immediately.

Under the hood: intelligent agent-based classification

Automated business glossary assignment goes beyond simple metadata lookups using a reasoning-driven approach. The AI agent functions like a virtual data steward, following human-like reasoning patterns such as:

Reviews asset details and context
Searches the catalog for relevant terms
Evaluates whether results make sense
Refines strategy if initial searches don’t surface appropriate terms
Learns from each step to improve recommendations

Key approaches:

Reasoning over static queries – The agent interprets asset attributes and context rather than treating metadata as a fixed index, generating dynamic search intents instead of relying on predefined queries.
Iterative adaptive search – When initial results are weak, the agent automatically adjusts queries—broadening, narrowing, or shifting terms through a feedback loop that helps improve discovery quality.
Structured semantic search – The agent performs semantic querying across entity types, applies filtering and relevance scoring, and conducts multi-directional exploration until strong matches are found.

This allows the agent to explore multiple directions until strong matches are found, improving recall and precision over static methods like direct vector search when asset metadata is incomplete or ambiguous.

Things to keep in mind

This feature is only as strong as the glossary it sits on top of. If the glossary is incomplete or inconsistent, the suggestions reflect that. Producers should still review each recommendation, especially for regulatory labels. Governance teams should monitor how often suggestions are accepted or overridden to understand model accuracy and glossary gaps.

Prerequisites

To follow along, you must have an Amazon SageMaker Unified Studio domain set up with a domain owner or domain unit owner permissions. You must have a project that you can use to publish assets. For instructions on setting up a new domain, refer to the SageMaker Unified Studio Getting started guide. We will also use Amazon Redshift to catalog data. If you are not familiar, read Learn Amazon Redshift concepts to learn more.

Step 1: Define business glossary and terms

AI recommendations suggest terms only from glossaries and definitions already present in the system. As a first step we create high-quality, well-described glossary entries so the AI can return accurate and meaningful suggestions.

We create the following business glossaries in our domain. For information about how to create a business glossary, see Create a business glossary in Amazon SageMaker Unified Studio.

Domain: Terms – Customer Profile, Policy, Order, Invoice.

The following is the view of ‘Domain’ business glossary with all terms added.

Data sensitivity: Terms – PII, PHI, Confidential, Internal.

The following is the view of ‘Data sensitivity’ business glossary with all terms added.

Business Unit: Terms – KYC, Credit Risk, Marketing Analytics

The following is the view of ‘Business Unit’ business glossary with all terms added.

We recommend that you use glossary descriptions to make terms unambiguous. Ambiguous or overlapping definitions confuse AI models and humans equally.

Step 2: Create data assets

Create the following table in Amazon Redshift. For information about how to bring Amazon Redshift data to Amazon SageMaker Catalog, see Amazon Redshift compute connections in Amazon SageMaker Unified Studio.

CREATE TABLE  dev.public.customer_analytics_data (
    customer_id VARCHAR(50) NOT NULL,
    customer_full_name VARCHAR(200),
    customer_email VARCHAR(255),
    customer_phone VARCHAR(20),
    customer_dob DATE,
    customer_tax_id VARCHAR(256),
    policy_id VARCHAR(50),
    policy_type VARCHAR(100),
    policy_start_date DATE,
    policy_end_date DATE,
    policy_coverage_amount DECIMAL(18,2),
    order_id VARCHAR(50),
    order_date TIMESTAMP,
    order_status VARCHAR(50),
    order_total DECIMAL(18,2),
    invoice_id VARCHAR(50),
    invoice_date DATE,
    invoice_amount DECIMAL(18,2),
    invoice_payment_status VARCHAR(50),
    customer_profile_created_timestamp TIMESTAMP DEFAULT GETDATE(),
    customer_profile_updated_timestamp TIMESTAMP DEFAULT GETDATE(),

    PRIMARY KEY (customer_id, order_id)
)
DISTSTYLE KEY
DISTKEY (customer_id)
SORTKEY (customer_id, order_date);

Once the Redshift is onboarded with above steps, navigate to Project catalog from left navigation menu and choose Data sources. Run the Data Source to add the table to Project inventory assets.

‘customer_analytics_data’ should be Project Assets inventory.

Verify navigating to ‘Project catalog’ menu on the left and choose ‘Assets’.

Step 3: Generate classification recommendations

To automatically generate terms, select GENERATE TERMS in ‘GLOSSARY TERMS’ section of the asset.

AI recommendations for glossary terms automatically analyze asset metadata and context to determine the most relevant business glossary terms for each asset and its columns. Instead of relying on manual tagging or static rules, it reasons about the data and performs iterative searches across what already exists in the environment to identify the most relevant glossary term concepts.

After recommendations are generated, review the terms both at table and column level. Table level suggested terms can be viewed as shown in the following image:

Select the SCHEMA tab to review column level tags as shown in the following image:

Review and accept individually by selecting the AI icon shown in below image.

In this case, we select ACCEPT ALL and then select PUBLISH ASSET as shown below.

The tags are now added to the asset and columns without manual search and addition. Select PUBLISH ASSET.

The asset is now published to the catalog as shown in the following image in the upper left corner.

Step 4: Improve data discovery

Users can now experience enhanced search results and find assets in the catalog based on the associated terms.

Browse by TermsUsers can now explore the catalog and filter by terms as shown in left navigation “APPLY FILTER” section

Search and FilterUsers can also search assets by glossary terms as shown below:

Cleanup

To delete SageMaker domain, follow instructions at Delete domains
To delete your Redshift cluster follow steps at Shutting down and deleting a cluster

Conclusion

By standardizing terminology at publication, organizations can reduce metadata drift and improve discovery reliability. The feature integrates with existing workflows, requiring minimal process changes while helping deliver immediate catalog consistency improvements.

By tagging data at publication rather than correcting it later, data teams can spend less time fixing metadata and more time using it. For more information on SageMaker capabilities, see the Amazon SageMaker Catalog User Guide.

About the authors

Extract data from Amazon Aurora MySQL to Amazon S3 Tables in Apache Iceberg format

2026-03-23 Kunal Ghosh

Post Syndicated from Kunal Ghosh original https://aws.amazon.com/blogs/big-data/extract-data-from-amazon-aurora-mysql-to-amazon-s3-tables-in-apache-iceberg-format/

If you manage data in Amazon Aurora MySQL-Compatible Edition and want to make it available for analytics, machine learning (ML), or cross-service querying in a modern lakehouse format, you’re not alone.

Organizations often need to run analytics, build ML models, or join data across multiple sources. These are examples of workloads that can be resource-intensive and impractical to run directly against a transactional database. By extracting your Aurora MySQL data into Amazon S3 Tables in Apache Iceberg format, you can offload analytical queries from your production database without impacting its performance, while storing data in a fully managed Iceberg table store optimized for analytics. Built on the open Apache Iceberg standard, Amazon Simple Storage Service (Amazon S3) Table data is queryable from engines like Amazon Athena, Amazon Redshift Spectrum, and Apache Spark without additional data copies. You can also combine relational data with other datasets already in your data lake, enabling richer cross-domain insights.

Apache Iceberg and Amazon S3 Tables

Apache Iceberg is a widely adopted open table format that offers Atomicity, Consistency, Isolation, Durability (ACID) transactions, schema evolution, and time travel capabilities. It enables multiple engines to work concurrently on the same dataset, making it a popular choice for building open lakehouse architectures.

Amazon S3 Tables is a purpose-built, fully managed Apache Iceberg table store designed for analytics workloads. It delivers up to 3x faster query performance and up to 10x more transactions per second compared to self-managed Iceberg tables. It also automatically compacts data and removes unreferenced files to optimize storage and performance.

In this post, you learn how to set up an automated, end-to-end solution that extracts tables from Amazon Aurora MySQL Serverless v2 and writes them to Amazon S3 Tables in Apache Iceberg format using AWS Glue. The entire infrastructure is deployed using a single AWS CloudFormation stack.

Requirements

AWS offers zero-ETL integrations from Amazon Aurora to Amazon Redshift and Amazon SageMaker AI, enabling seamless data flow for analytics and machine learning workloads.

However, there isn’t yet a native zero-ETL integration between Amazon Aurora and Amazon S3 Tables. This means that organizations looking to use Amazon S3 Tables for their Lakehouse architecture currently face several requirements:

Setting up ETL pipelines to extract data from Amazon Aurora and transform it into Apache Iceberg format
Configuring networking and security for AWS Glue jobs to access Amazon Aurora databases in private subnets
Coordinating the provisioning of source databases, ETL pipelines, and target table stores
Managing the end-to-end workflow without native automation

Solution overview

In this solution, you automate the extraction of relational database tables from Amazon Aurora MySQL Serverless v2 to Amazon S3 Tables in Apache Iceberg format using AWS Glue 5.0. To help you get started and test this solution, a CloudFormation template is provided. This template provisions the required infrastructure, loads sample data, and configures the Extract, Transform, Load (ETL) pipeline. You can adapt this template for your own scenario.

Solution overview

Sample data

This solution uses the TICKIT sample database, a well-known dataset used in Amazon Redshift documentation. The TICKIT data models a fictional ticket sales system with seven interrelated tables: users, venue, category, date, event, listing, and sales. The dataset is publicly available as mentioned in the Amazon Redshift Getting Started Guide.

Solution flow

The solution flow as shown in the previous architecture diagram:

An AWS Lambda function downloads the TICKIT sample dataset (a fictional ticket sales system used in Amazon Redshift documentation) from a public Amazon S3 bucket to a staging S3 bucket.
A second Lambda function, using PyMySQL (a Python MySQL client library), loads the staged data files into the Aurora MySQL Serverless v2 database using LOAD DATA LOCAL INFILE.
The AWS Glue job reads seven TICKIT tables from Aurora MySQL through a native MySQL connection and writes them to Amazon S3 Tables in Apache Iceberg format using the S3 Tables REST catalog endpoint with SigV4 authentication.
You can query the migrated data in S3 Tables using Amazon Athena.

The solution consists of the following key components:

Amazon Aurora MySQL Serverless v2 as the source relational database containing the TICKIT sample dataset (users, venue, category, date, event, listing, and sales tables)
AWS Secrets Manager to store the Aurora MySQL database credentials securely
Amazon S3 staging bucket for the TICKIT sample data files downloaded from the public redshift-downloads S3 bucket
AWS Lambda functions using PyMySQL to load data into Aurora MySQL
AWS Glue 5.0 job (PySpark) to read tables from Aurora MySQL and write them to S3 Tables in Apache Iceberg format
Amazon S3 Tables as the target storage for the migrated Iceberg tables
Amazon VPC with private subnets and VPC endpoints for Amazon S3, S3 Tables, AWS Glue, Secrets Manager, AWS Security Token Service (AWS STS), CloudWatch Logs, and CloudFormation

Here are some advantages of this architecture:

Fully automated setup: A single CloudFormation stack provisions the required infrastructure, loads sample data, and configures the ETL pipeline.
Serverless and cost-efficient: Aurora MySQL Serverless v2 and AWS Glue both scale based on demand, minimizing idle costs.
Apache Iceberg table format: Data is stored in Apache Iceberg format, enabling ACID transactions, schema evolution, and time travel queries.
Network isolation and credential management: The resources run within private subnets with Virtual Private Cloud (VPC) endpoints, and database credentials are managed through AWS Secrets Manager.
Extensible pattern: The same approach can be adapted for other relational databases (PostgreSQL, SQL Server) and other target formats supported by AWS Glue.

Prerequisites

To follow along, you need an AWS account. If you don’t yet have an AWS account, you must create one. The CloudFormation stack deployment takes approximately 30-45 minutes to complete and requires familiarity with Amazon S3 Tables, AWS CloudFormation, Apache Iceberg, AWS Glue, Amazon Aurora. This solution will incur AWS costs. The main cost drivers are AWS Glue ETL job runs (billed per DPU-hour, proportional to data volume) and Amazon S3 Tables storage and request charges. Remember to clean up resources when you are done to avoid unnecessary charges.

CloudFormation parameters

You can configure the following parameters before deploying the CloudFormation stack:

Parameter	Description	Default	Required
S3TableBucketName	Name of the S3 Tables bucket to create (or use existing)		Yes
DatabaseName	Name of the initial Aurora MySQL database	tickit	No
MasterUsername	Master username for Aurora MySQL	admin	No
VpcCidr	CIDR block for the VPC	10.1.0.0/16	No
S3TableNamespace	Namespace for S3 Tables	tickit	No

Implementation walkthrough

The following steps walk you through the implementation. These steps are to deploy and test an end-to-end solution from scratch. If you are already running some of these components, you may skip to the relevant step. You can also refer to the aws-samples repository, sample-to-write-aurora-mysql-to-s3tables-using-glue for the entire solution.

Step 1: Deploy the CloudFormation stack

Deploy the CloudFormation template scripts/aurora-mysql-to-s3tables-stack.yaml using the AWS Console or the AWS Command Line Interface (AWS CLI). Provide a name for the S3 Tables bucket; the stack will create it automatically (or use an existing one if it already exists).

To deploy using the AWS Console (recommended), navigate to the AWS CloudFormation Console and use the CloudFormation template. Alternatively, to deploy using the AWS CLI first upload the template to an S3 bucket (the template exceeds the 51,200 byte limit for inline –template-body), then create the stack.

# Upload the template to S3
aws s3 cp scripts/aurora-mysql-to-s3tables-stack.yaml \
  s3://<your-s3-bucket>/aurora-mysql-to-s3tables-stack.yaml \
  --region <your-region>

# Create the stack using the S3 template URL
aws cloudformation create-stack \
  --stack-name aurora-mysql-tickit-stack \
  --template-url https://<your-s3-bucket>.s3.<your-region>.amazonaws.com/aurora-mysql-to-s3tables-stack.yaml \
  --parameters \
    ParameterKey=S3TableBucketName,ParameterValue=<your-s3-table-bucket-name> \
  --capabilities CAPABILITY_NAMED_IAM \
  --region <your-region>

The stack will automatically:

Create the S3 Tables bucket (or use existing if it already exists)
Create a VPC with private subnets and VPC endpoints
Provision an Aurora MySQL Serverless v2 cluster
Download TICKIT sample data from the public Amazon S3 bucket
Load the sample data into Aurora MySQL via a Lambda function using PyMySQL
Create a Glue job configured to migrate data to S3 Tables in Iceberg format

Note: The S3 Tables bucket is retained when the stack is deleted to preserve your data.

Step 2: Verify the Aurora MySQL data

Retrieve the AuroraClusterEndpoint, DatabaseName, and SecretArn values from the CloudFormation stack, make a note of the AuroraClusterEndpoint, DatabaseName, and SecretArn. You can navigate to the Amazon Aurora Console, choose the Query Editor, and enter the values from the CloudFormation stack to connect. You can also choose your preferred method of connecting to an Amazon Aurora DB cluster.

Use the AWS CLI to retrieve the stack outputs: –

aws cloudformation describe-stacks --stack-name aurora-mysql-tickit-stack --region <your-region> --query "Stacks[0].Outputs"

Then run the following SQL commands to verify the data load:

-- Verify if the tables are created
SELECT * FROM information_schema.tables WHERE table_schema = 'tickit';

-- Verify if the data is loaded
SELECT 'users' AS table_name, COUNT(*) AS record_count FROM tickit.users
UNION ALL SELECT 'venue', COUNT(*) FROM tickit.venue
UNION ALL SELECT 'category', COUNT(*) FROM tickit.category
UNION ALL SELECT 'date', COUNT(*) FROM tickit.date
UNION ALL SELECT 'event', COUNT(*) FROM tickit.event
UNION ALL SELECT 'listing', COUNT(*) FROM tickit.listing
UNION ALL SELECT 'sales', COUNT(*) FROM tickit.sales;

Step 3: Run the Glue job

Navigate to the AWS Glue Console, choose ETL jobs under Data Integration and ETL from the left panel. Select the AWS Glue job mysql-tickit-to-iceberg-job and choose Run job to start execution. You can also start the ETL job using the AWS CLI:

aws glue start-job-run --job-name mysql-tickit-to-iceberg-job --region <your-region>

The AWS Glue job performs the following operations for each of the seven TICKIT tables:

Reads the table from Aurora MySQL through the native MYSQL Glue connection
Converts the data to a Spark DataFrame
Creates the Iceberg table in the S3 Tables namespace using CREATE TABLE IF NOT EXISTS with the USING ICEBERG clause
Inserts the data using INSERT INTO (or INSERT OVERWRITE if the table already exists)
Verifies the record count and displays sample data

Step 4: Verify the results

After the AWS Glue job completes, verify that the tables have been created in your S3 Table bucket by navigating to the Amazon S3 Console. Choose Table buckets under Buckets and select your S3 Table bucket. You can also verify using the AWS CLI:

aws s3tables list-tables \
  --table-bucket-arn arn:aws:s3tables:<your-region>:<your-account-id>:bucket/<your-s3-table-bucket-name> \
  --namespace tickit \
  --region <your-region>

Select a table from the tickit namespace and choose Preview to inspect the data.

Preview S3 data

You can also query the migrated tables using Amazon Athena to validate the data.

Clean up resources

Remember to clean up resources when you no longer need them to avoid unnecessary charges.

Navigate to the CloudFormation console, search for your stack and choose Delete. Alternatively, use the AWS CLI:

aws cloudformation delete-stack --stack-name aurora-mysql-tickit-stack --region <your-region>

The S3 Tables bucket is retained by default. To delete it, use the Amazon S3 console or the AWS CLI to remove the table bucket separately. The staging S3 bucket will be automatically emptied and deleted as part of the stack deletion.

aws s3tables delete-table-bucket --table-bucket-arn arn:aws:s3tables:<your-region>:<your-account-id>:bucket/<your-s3-table-bucket-name> --region <your-region>

Summary

In this post, we showed you how to extract data from Amazon Aurora MySQL Serverless v2 and write it to Amazon S3 Tables in Apache Iceberg format using AWS Glue 5.0. By using the native Iceberg support of AWS Glue and the S3 Tables REST catalog endpoint, you can bridge the gap between relational databases and modern lakehouse storage formats. By automating the entire pipeline through CloudFormation, you can quickly set up and replicate this pattern across multiple environments.

As AWS Glue and Amazon S3 Tables continue to evolve, you can take advantage of future enhancements while maintaining this automated migration pattern.

If you have questions or suggestions, leave us a comment.

About the authors

From firefighting to building: How AI agents restored our team’s core productivity

2026-03-19 Grab Tech

Post Syndicated from Grab Tech original https://engineering.grab.com/from-firefighting-to-building

Abstract

Grab’s Analytics Data Warehouse (ADW) team supports over 1,000 users each month and manages an extensive repository of more than 15,000 tables, which powers approximately 50% of all queries within our data lake.
However, the manual process of addressing “quick questions” is time-consuming and labor-intensive, thus creating a bottleneck in our operations.

The team was drowning in repetitive requests, spending approximately 40% of their time or an equivalent of roughly 2 days every week, on tasks like:

Answering the same questions about data definitions
Tracing data sources and troubleshooting
Running quality checks to verify data integrity
Basic enhancement requests

We deployed a multi-agent AI system that autonomously answers simpler questions and collaboratively addresses more complex requests. This led us to reclaim significant engineering bandwidth and unlock hundreds of hours of productivity monthly.

Solution

Tech stack

FastAPI and LangGraph: We use FastAPI to handle requests and LangGraph to manage the complex state and cyclical logic required for multi-agent collaboration. Unlike simple Large Language Model (LLM) calls, LangGraph allows our agents to loop back, ask for more information, or hand off tasks to one another.
Redis & PostgreSQL: Redis handles our caching and real-time session needs, while PostgreSQL serves as the persistent memory, storing conversation history and agent metadata.

Hubble: A centralized metadata management platform and data catalog, built on open-source DataHub.
Genchi: A data quality observability platform that enforces data contracts.
Lighthouse: A platform that tracks execution status and monitors pipeline health.

From request to resolution

The journey begins in Slack. When a user submits a request, it is categorized into one of two streams:

Enhancement requests: These are routed to the Enhancement Agent, which interacts directly with our core engineering tools like GitLab, Apache Spark, and Airflow to propose and test code changes.
General questions: These are funneled through our investigation pathway. The system orchestrates a “huddle” between the Data Agent (querying Trino, Hive, or Delta Lake), the Code Search Agent (analyzing GitLab), and the On-call Agent (checking Confluence and Slack for ongoing incidents).

By decoupling the “brain” (the LLM) from the “hands” (the specialized agents and tools), we created a system that is both capable and easy to debug.

Why specialized agents beat a single “Super AI”

We could have built one massive AI trained to handle every question, but specialized agents are easier to build, maintain, and improve than a monolithic system.

The table below illustrates the comparison between a single AI system and a multi-agent system:

Approach	Advantages	Challenges
Single AI (Monolithic)	One model to maintain, single inference call	Hard to debug, changes affect everything, generalist performance
Multi-Agent System	Focused expertise, modular updates, specialist accuracy	Sequential execution adds latency, coordination complexity

We chose the multi-agent approach because maintainability and accuracy mattered more than shaving off a few seconds of latency. When you’re replacing a multi-hour manual investigation, taking a few minutes for a precise answer is a massive leap in operational throughput.

The architecture: Two pathways, five specialized agents

When a question arrives through Slack, the system first determines which pathway to take:

Enhancement pathway: Enhancement requests → Enhancement Agent (handles code changes)
Investigation pathway: Investigation questions → Classifier → Specialized agents → Summarizer agent

Figure 2. Agent workflows, using a Classifier that controls communication flow and task delegation.

Enhancement pathway: Semi-Automated code changes

For requests like “Can you add a new column for customer_segment?” or “We need to change the aggregation logic for revenue”, the Enhancement Agent handles the heavy lifting.

Enhancement Agent receives user requirements and proposes code changes:

Gathers context: schema, lineage, dependencies, existing codebase.
Generates code changes and creates a merge request (MR).
Runs changes in a test environment.
Flags governance concerns (Personally Identifiable Information (PII) classification, Service Level Agreements (SLAs), backward compatibility).

The workflow:

User creates a JIRA request.
Agent analyzes requirements and gathers context through interactive dialogue with the engineer.
Agent creates an MR with suggested code.
Engineer reviews the MR.
If valid, agent runs changes in test environment.
Engineer reviews results against test cases.
If tests pass, engineer merges the MR.

Why is the workflow semi-automated by design? Code changes to production pipelines require human judgment. The agent accelerates the process by doing the research, writing the code, and running tests, but humans make the final approval.

Investigation pathway: Four agents working together

For questions like “Why does this data look wrong?” or “Where does this metric come from?”, the system uses a coordinated team of specialists.

The Classifier is the first responder for investigation questions. It:

Parses the question to extract key information (tables, scripts, specific data requests).
Detects guardrail violations (PII requests, out-of-scope queries).
Determines which specialist agents are needed and in what sequence.
Provides reasoning and task descriptions for each recommended agent.

Example: For the question “Why does this ID look wrong?”, the Classifier routes the question to: Data Agent → Code Search Agent → On-call Agent (if needed).

Data Agent performs the data investigation:

Enhances the prompt’s context with the table and column metadata.
Executes queries with guardrails (PII detection, command validation).
Validates schemas to avoid unnecessary scans and hallucinations.
Retrieves sample data with LLM exploratory comments.

Example: It queries vehicle_id from the table to validate the user’s observation against the actual data.

Code Search Agent analyzes the code:

Traces column transformations through the codebase.
Follows table lineage through multiple transformation steps.
Generates plain-language explanations of transformation logic.
Highlights divergences from documentation or stakeholder expectations.

Example: It can trace a vehicle_id column from the final table back through 5 transformation steps to the original source, explaining each change along the way.

On-call Agent monitors production systems and assists with urgent issues:

Searches Slack channels for announcements about outages, source table failures, and delays.
Checks observability platforms for pipeline health, logs, and retry policies.
Validates data quality metrics (null counts, duplicates, range validation).
Produces incident notes and initial Root Cause Analysis (RCA) when issues are identified.

Example: If the Data Agent detects SLA breaches or missing partitions, it may consult the On-call Agent for production context.

Summarizer Agent refines responses from the previous agents:

Handles conflicting information.
Combines responses into a coherent narrative.
Makes the answer concise and structured.
Ensures consistency across agent findings.

Generating the summary is the final step before human review.

Seeing the system in action

The best way to understand how this multi-agent system works is to see it handle real scenarios. Let’s walk through two common situations our team faces daily.

Scenario 1: Adding a new column

The request: A stakeholder raises a JIRA ticket requesting, “Please add a customer_segment column to the rides table. Source data is available in the user_profiles table.”

In the traditional workflow, a data engineer would spend a significant portion of their afternoon clarifying requirements, developing and testing code, similar to the workflow steps in “Figure 2: Agent workflows”.

With the Enhancement Agent, the entire process is completed autonomously in minutes. The agent performs these tasks in sequence:

Read the JIRA ticket: Agent fetches the ticket details to understand the exact requirements: what column needs to be added, which table is involved, and where the source data comes from.
Discover the relevant code: Using intelligent search capabilities, it locates the specific pipeline files in our codebase that need modification. It navigates through the repository structure to find the right transformation scripts.
Run validation checks: Before making any changes, it validates:
- The requested column exists in the upstream source table.
- The column doesn’t already exist in the target table.
- Schema compatibility and data quality requirements are met.
Generate database schema changes: The agent references existing Data Definition Language (DDL) scripts to understand the standard format, then automatically generates the necessary schema modification scripts. These scripts are added to the MR alongside the code changes.
Create the MR: All changes, including code modifications and schema scripts, are packaged into an MR with proper documentation, making it ready for review.
Enable pipeline execution: Once the MR is validated, users can interact with the bot to trigger the data pipeline and start testing their changes on Airflow. They can optionally specify date ranges or other parameters to control the test runs.

The entire process, from ticket to deployable MR, completes autonomously in minutes, with full traceability at every step.

Scenario 2: Investigating faulty-looking data

The question: “Why is the ID in the vehicles table unreadable?”

Traditionally, the data engineer typically performs these steps:

Search through various data catalogs to locate relevant information.
Manually track the data’s origin and transformation path.
Validate SQL queries.
Examine logs.

This is how it looks with agents:

Step 1: Classifier analyzes the question

Parses the question: determines all three specialist agents are needed.
Plans the sequence: Data Agent → Code Search Agent → On-call Agent
Provides reasoning: “Need to verify data format, trace transformation logic, and check for production incidents”.

Step 2: Data Agent investigates

Retrieves metadata, which helps in building a SQL query for exploring samples.
Queries actual data. The result confirms the user’s observation with the actual sample and identifies that IDs appear in Universally Unique Identifier (UUID) format, and they’re “unreadable”.
Searches Grab’s data catalog to find dimension tables that can help decipher UUID in a more human-readable format.
Finds an appropriate dimension table and builds a join query to test readability.

Conclusion from Data Agent: “The ID column contains UUID format values. These can be joined with dim_vehicles table to get human-readable vehicle names. The format is consistent and valid—not corrupted data.”

Step 3: Code Search Agent traces lineage

Scans the transformation and lineage logic in the codebase to see exactly how the ID is extracted. It discovers that the ID is a raw UUID from a JSON payload directly from the source system.
Queries the source table for samples directly. The “unreadable” text pattern matches the data in the vehicles table, confirming that it is not a bug introduced by Spark transformations.

Conclusion from Code Search Agent: “The ‘unreadable’ UUID format comes directly from the source system. No transformation is applied. This is not a bug introduced by our Spark pipelines—it’s the native format from the upstream system”.

Step 4: On-call Agent checks production health

Checks Airflow pipeline status.
Searches Slack channels for incidents.
Checks data quality metrics.

Conclusion from On-call Agent: “No production incidents detected. Pipeline running successfully. Data quality metrics are within normal ranges. No recent complaints or issues reported in communication channels.”

Step 5: Summarizer Agent synthesizes the answer

User concern: ID values appear “unreadable”.
Data Agent finding: IDs are valid UUIDs, can be joined with dim_vehicles for readable names.
Code Search finding: UUID format comes directly from source system, not a transformation bug.
On-call finding: No production issues, pipeline healthy, data quality normal.

Provides a structured answer to: “Why is the ID in the vehicles table unreadable?”

Step 6: Human review and delivery
The answer is posted on Slack, and a data engineer can review the response and approve it.

The initial response time has been reduced to just a few minutes, in contrast to the previous hours-long manual search.

Step 7: Continue conversation
After an answer is posted, anyone can engage in a continued conversation with the agents, restarting the loop.

Optimizing the architecture

Building the system was one challenge. Making it production-ready was another.

Our initial prototype worked in controlled demos, but real-world usage revealed critical gaps. Users asked complex questions, conversations grew long, and edge cases exposed vulnerabilities. Here’s how we optimized the system to handle production demands while maintaining accuracy and safety.

Challenge 1: Excessive context

In multi-agent systems, context accumulates fast. Information is continuously passed from one agent to the next. Without careful management, excessive context and tokens cause performance degradation.

Our solution:
The orchestrator maintains a rich state throughout execution, tracking three critical elements:

Conversation and tooling history: Full message context for each agent.
Execution tracking: Which agents have run, current progress, and execution steps.
Agent responses: Structured responses from each agent, passed to subsequent agents.

This state is carefully managed to ensure each agent has the right context without overwhelming token limits.

Token tracking: Every message is counted using tiktoken, giving us real-time visibility into our token budget.
Intelligent summarization: When token limits are exceeded, earlier messages are automatically summarized while retaining information relevant to the original question. Recent messages and critical context remain unsummarized to preserve accuracy.
Retrieval-Augmented Generation (RAG) context pruning: We reduce context from tool outputs when enhancing prompts:
- Instead of passing full code files to the Code Search Agent, we use smaller LLM models to extract the most relevant code snippets and a short description.
- For database queries, we apply filters to retrieve only the top relevant results.
Handoffs Pattern: The previous agent returns its response to a central orchestrator. The orchestrator cleans the context, prunes unnecessary tokens, and invokes the next agent.

The result:
Agents can handle extended investigations without drowning in excessive context, maintaining performance even in complex, multi-turn conversations.

Challenge 2: Excessive tool usage

Our initial design presented a significant performance bottleneck due to excessive tool usage. Early models were equipped with a large and unwieldy set of over 30 distinct tools, each structured similarly to a generic API. Since tool calling is part of an agent’s prompt, agents had to process verbose tool descriptions and outputs, which degraded efficiency.

Our solution:
We focused on tool design based on real-world usage scenarios:

Included only the relevant portions required for decision-making.
Aggressively truncated verbose information from tool outputs.
Streamlined tool descriptions to be concise and actionable.

The result:
By significantly reducing the data load agents needed to process during inference, we achieved a substantial leap in system responsiveness and throughput.

Challenge 3: Risky code executions

AI agents with database access and code generation capabilities pose significant risks. Without proper safeguards, they could access sensitive PII data, execute dangerous SQL operations, run expensive queries, or generate breaking code changes. We needed to make the system safe.

Our solution:
We implemented multiple layers of safety to protect against misuse from both agents and users:

Layer 1: Input classification
Before any agent executes, the Classifier detects:

PII requests: Questions asking for personally identifiable information
Out-of-scope queries: Requests beyond the agent’s capabilities

Layer 2: SQL validation before execution
The Data Agent validates every query for:

PII column access: Checks against column metadata to ensure it doesn’t access confidential information.
Data definition and manipulation language (DDL/DML) operations: The agent doesn’t have access to DELETE, DROP, TRUNCATE, or UPDATE operations, but this check acts as an additional safeguard.
Slow queries: Detects missing partition filters or excessive date ranges that could cause expensive full-table scans.
Schema validation: Confirms tables and columns exist before execution.

Layer 3: Timeout protection
All database queries have strict execution limits to prevent runaway queries from impacting system performance.

Layer 4: Enhancement agent controls
For the Enhancement Agent, which generates code changes:

Cannot commit to master/main directly: All changes go through MRs.
Mandatory human review: A human reviewer must validate all inputs before execution.
Test environment first: Changes run in staging before production deployment.

The result:
A safe environment where AI agents can operate in production without compromising security or stability. Users and engineers trust the system because they know it has robust guardrails protecting critical data and systems.

Challenge 4: Ensuring user trust

Even with RAG and guardrails, AI agents aren’t perfect. Hallucinations, misinterpretations, and edge cases could erode user trust.

Our solution:
After generating a summarized response, the multi-agent system routes to human reviewers who can take five actions:

Approve: Post the response as-is and add a footnote that the response has been deemed accurate by a human reviewer.
Reject: Mark the response as incorrect and log it for improvement. The response will not be posted, protecting users from bad information.
Refine: Add a prompt to improve the summarized response from the sub-agents. The system regenerates the answer with additional guidance.
Re-route to Sub-Agents: Send the question to a specific agent with additional context. For example: “Data Agent, can you check the last 30 days instead of 7 days?”
Annotate: Provide structured feedback to the response, where it gets saved to a database for continuous improvement.

The result:
This human-in-the-loop model ensures answers are accurate and reliable, increasing user trust in the responses. The annotations help us iteratively improve the model’s future responses.

Challenge 5: Balancing speed and quality

Our initial design withheld AI-generated responses until authorized by an engineering team member. This introduced a bottleneck in the response process, potentially leaving inquiries unresolved for extended periods, particularly during peak workload times.

Our solution:
We redesigned the process to allow responses to be posted without immediate human review, provided they are clearly and prominently marked as unreviewed. All posts can still be reviewed and modified by the on-call engineer as needed, but users get answers immediately rather than waiting.

The result:
This approach maintains a crucial balance between response speed and quality:

Users get fast answers when they need them.
Transparency (unreviewed label) sets appropriate expectations.
Engineers still review all responses to catch errors and improve the system.
Feedback loop remains intact for continuous learning.

Challenge 6: Closing the feedback loop

Collecting feedback through annotations was just the first step. Without systematic analysis, we had a gold mine of information about what worked and what didn’t, but we weren’t learning from it. Every rejected response was a lesson unlearned, every annotation a pattern unrecognized. We needed to close the loop.

Our solution:
We transformed annotations from passive records into an active improvement engine through five mechanisms:

Automated evaluation: Random annotations are pulled to create test cases for offline evaluation. This ensures the system is tested against real-world failure scenarios, not just synthetic test cases we invented.
Pattern analysis: We analyze annotations to identify systemic issues:
- Is the Classifier consistently routing to the wrong agents?
- Does a specific agent have quality issues?
- Are certain types of queries prone to hallucinations?
- Do particular table schemas cause confusion?
Quality metrics: Tracking annotation rates over time measures system reliability and identifies regression. If the rejection rate suddenly increases, we know something has changed that needs investigation.
Targeted improvements: Annotations guide where to focus development effort:
- Improving prompts: Refining agent system prompts with better examples.
- Adding guardrails: Enhancing input classification to catch problematic queries earlier.
- Enhancing specific agents: Adding examples or tools to handle struggling query types.
Training data: Annotated failures can be used to:
- Fine-tune models on domain-specific patterns.
- Improve few-shot examples in prompts.
- Build regression test suites from actual failures.

The result:
The system transformed from static to continuous learning. Every mistake became an opportunity for improvement, and the system got smarter with each interaction. We had data-driven insights guiding our optimization priorities, ensuring we focused on the highest-impact improvements.

Impact

The deployment of this multi-agent system yielded transformative results across key performance indicators, shifting the team’s entire operational dynamic.

Automated resolution:The bots now autonomously handle the majority of standard user inquiries and a significant portion of common enhancement requests.
Velocity gains: The time required to resolve issues has seen an order-of-magnitude reduction, effectively eliminating the support backlog. Simple inquiries are autonomously answered and brought to a resolution within minutes.
Productivity gains: The team has successfully reclaimed several full-time equivalents (FTE) worth of engineering bandwidth, shifting hundreds of hours from reactive support to proactive roadmap delivery.

With this newfound capacity unlocked, the data engineering team pivots from reactive support to proactive, high-value work, ultimately leading to “happier downstream users.”

Conclusions

Our journey from overwhelmed data engineers to a team empowered by AI agents revealed three core principles that made this transformation possible:

Multi-Agent architecture: Specialists over generalists
Specialized AI agents outperform a single generalist by mastering specific domains (e.g., data quality, code analysis). This modularity allows for independent improvement, easy additions, and clear responsibilities, boosting maintainability and flexibility.

Strategic human oversight: Building trust through transparency
Routing AI responses through human reviewers achieved rapid adoption through trust and continuous system improvement by generating annotated training feedback.

Focus on augmentation: Automating repetitive tasks
AI agents operate autonomously on repetitive tasks (context gathering, running queries, checking logs) with human oversight if needed, and collaborate with us in augmenting higher-value work: architectural decisions and building new capabilities.

Join us

Grab is a leading superapp in Southeast Asia, operating across the deliveries, mobility, and digital financial services sectors. Serving over 900 cities in eight Southeast Asian countries: Cambodia, Indonesia, Malaysia, Myanmar, the Philippines, Singapore, Thailand, and Vietnam. Grab enables millions of people every day to order food or groceries, send packages, hail a ride or taxi, pay for online purchases or access services such as lending and insurance, all through a single app. We operate supermarkets in Malaysia under Jaya Grocer and Everrise, which enables us to bring the convenience of on-demand grocery delivery to more consumers in the country. As part of our financial services offerings, we also provide digital banking services through GXS Bank in Singapore and GXBank in Malaysia. Grab was founded in 2012 with the mission to drive Southeast Asia forward by creating economic empowerment for everyone. Grab strives to serve a triple bottom line. We aim to simultaneously deliver financial performance for our shareholders and have a positive social impact, which includes economic empowerment for millions of people in the region, while mitigating our environmental footprint.

Powered by technology and driven by heart, our mission is to drive Southeast Asia forward by creating economic empowerment for everyone. If this mission speaks to you, join our team today!

Best practices for Amazon Redshift Lambda User-Defined Functions

2026-03-18 Sergey Konoplev

Post Syndicated from Sergey Konoplev original https://aws.amazon.com/blogs/big-data/best-practices-for-amazon-redshift-lambda-user-defined-functions/

While working with Lambda User-Defined Functions (UDFs) in Amazon Redshift, knowing best practices may help you streamline the respective feature development and reduce common performance bottlenecks and unnecessary costs.

You wonder what programming language could improve your UDF performance, how else can you use batch processing benefits, what concurrency management considerations might be applicable in your case? In this post, we answer these and other questions by providing a consolidated view of practices to improve your Lambda UDF efficiency. We explain how to choose a programming language, use existing libraries effectively, minimize payload sizes, manage return data, and batch processing. We discuss scalability and concurrency considerations at both the account and per-function levels. Finally, we examine the benefits and nuances of using external services with your Lambda UDFs.

Background

Amazon Redshift is a fast, petabyte-scale cloud data warehouse service that makes it simple and cost-effective to analyze data using standard SQL and existing business intelligence tools.

AWS Lambda is a compute service that lets you run code without provisioning or managing servers, supporting a wide variety of programming languages, automatically scaling your applications.

Amazon Redshift Lambda UDFs allows you to run Lambda functions directly from SQL, which unlock such capabilities like external API integration, unified code deployment, better compute scalability, cost separation.

Prerequisites

AWS account setup requirements
Basic Lambda function creation knowledge
Amazon Redshift cluster access and UDF permissions.

Performance optimization best practices

The following diagram contains necessary visual references from the best practices description.

Use efficient programming languages

You can choose from Lambda’s wide variety of runtime environments and programming languages. This choice affects both the performance and billing. More performant code may help reduce the cost of Lambda compute and improve SQL query speed. Faster SQL queries could also help reduce costs for Redshift Serverless and potentially improve throughput for Provisioned clusters depending on your specific workload and configuration.

When choosing a programming language for your Lambda UDFs, benchmarks may help predict performance and cost implications. The famous Debian’s Benchmarks Game Team provides publicly available insights for different languages in their micro-benchmark results. For example, their Python vs Golang comparison shows up to 2 orders of magnitude run time improvement and twice memory consumption reduction if you could use Golang instead of Python. That may positively reflect on both Lambda UDF performance and Lambda costs for the respective scenarios.

Use existing libraries efficiently

For every language provided by Lambda, you can explore the whole collection of libraries to help you implement tasks better from the speed and resource consumption point of view. When transitioning to Lambda UDFs, review this aspect carefully.

For instance, if your Python function manipulates datasets, it might be worth considering using the Pandas library.

Avoid unnecessary data in payloads

Lambda limits request and response payload size to 6 MB for synchronous invocations. Considering that, Redshift is doing best effort to batch the values so that the number of batches (and hence the Lambda calls) would be minimal which reduces the communication overhead. So, the unnecessary data, like one added for future use but not immediately actionable, may reduce efficiency of this effort.

Keep in mind returning data size

Because, from the point of view of Redshift, each Lambda function is a closed system, it is impossible to know what size the returned data can possibly be before executing the function. In this case, if the returned payload is higher than the Lambda payload limit, Redshift will have to retry with the outbound batch of a lower size. That will continue until a fit return payload will be achieved. While it is the best effort, the process might bring a notable overhead.

In order to avoid this overhead, you might use the knowledge of your Lambda code, to directly set the maximum batch size on the Redshift side using the MAX_BATCH_SIZE clause in your Lambda UDF definition.

Use benefits of processing values in batches

Batched calls provide new optimization opportunities to your UDFs. Having a batch of many values passed to the function at once, allows to use various optimization techniques.

For example, memoization (result caching), when your function can avoid running the same logic on the same values, hence reducing the total execution time. The standard Python library functools provides convenient caching and Least Recently Used (LRU) caching decorators implementing exactly that.

Scalability and concurrency management

Increase the account-level concurrency

Redshift uses advanced congestion control to provide the best performance in a highly competitive environment. Lambda provides a default concurrency limit of 1,000 concurrent execution per AWS Region for an account. However, if the latter is not enough, you can always request the account level quota increase for Lambda concurrency, which might be as high as tens of thousands.

Note that even with a restricted concurrency space, our Lambda UDF implementation will do the best effort to minimize the congestion and equalize the chances for function calls across Redshift clusters in your account.

Restrict function concurrency with reserved concurrency

If you want to isolate some of the Lambda functions in a restricted concurrency scope, for example you have a data science team experimenting with embedding generation using Lambda UDFs and you don’t want them to affect your account’s Lambda concurrency much, you might want to set a reserved concurrency for their specific functions to operate with.

Learn more about reserved concurrency in Lambda.

Integration and external services

Call existing external services for optimal execution

In some cases, it might be worth considering using existing external services or components of your application instead of re-implementing the same tasks yourself in the Lambda code. For example, you can use Open Policy Agent (OPA) for policy checking, a managed service Protegrity to protect your sensitive data, there are also a variety of services providing hardware acceleration for computationally heavy tasks.

Note that some services have their own batching control with a limited batch size. For that we implemented a per-function batch row count setting MAX_BATCH_ROWS as a clause in the Lambda UDF definition.

To learn more on the external service interaction using Lambda UDFs refer the following links:

Conclusion

Lambda UDFs provide a way to extend your data warehouse capabilities. By implementing the best practices from this post, you may help optimize your Lambda UDFs for performance and cost efficiency.The key takeaways from this post are:

performance optimization, showing how to choose efficient programming languages and tools, minimize payload sizes, and leverage batch processing to reduce execution time and costs
scalability management, showing how to configure appropriate concurrency settings at both account and function levels to handle varying workloads effectively
integration efficiency, explaining how to benefit from external services to avoid reinventing functionality while maintaining optimal performance.

For more information, visit the Redshift documentation and explore the integration examples referenced in this post.

About the author

How Vanguard transformed analytics with Amazon Redshift multi-warehouse architecture

2026-03-18 Alex Rabinovich

Post Syndicated from Alex Rabinovich original https://aws.amazon.com/blogs/big-data/how-vanguard-transformed-analytics-with-amazon-redshift-multi-warehouse-architecture/

This is a guest post by Alex Rabinovich, Anindya Dasgupta, and Vijesh Chandran from Vanguard, Financial Advisor Services division, in partnership with AWS.

Vanguard stands as one of the world’s leading investment companies, serving more than 50 million investors globally. The company offers an extensive selection of low-cost mutual funds and ETFs with over 450 funds/ETFs along with comprehensive investment advice and related financial services. With a workforce of approximately 20,000 crew members, Vanguard has built its reputation on providing low-cost, high-quality investment solutions that help investors achieve their long-term financial goals.

Within this massive organization, Vanguard’s Financial Advisor Services (FAS) division stands as one of the most prominent B2B operations in the financial services industry. Operating at an extraordinary scale, FAS oversees a broad range and diverse range of assets through the intermediary channel while supporting a vast network of advisory firms and financial advisors across the country. This division delivers a full suite of investment products, model portfolios, research capabilities, and technology-driven support services designed to help financial advisors serve their clients more effectively.

Business use cases and initial architecture

The scale and complexity of FAS operations generate enormous amounts of data that require sophisticated analytics capabilities to drive business insights, regulatory compliance, and operational efficiency. To address this, Vanguard launched the FAS 360 initiative. This initiative aims to empower Financial Advisor Services (FAS) with a centralized cloud data warehouse that integrates both internal and external data sources into a unified, intelligent system.

Key business use cases:

Business operations – Enables sales goal setting, tracking, and compensation management to drive operational excellence. It delivers insights on product usage patterns across financial advisor clients.
Data science – Powers customer segmentation models and call transcription analytics to drive strategic insights. It also supports marketing campaign preparation and customer insights for sales call preparation.
Exploratory analytics – Enables ad-hoc leadership questions, what-if scenario analysis, and sales trend analysis for channel managers competitor comparative analysis.

By consolidating these use cases into a centralized system, FAS 360 enables consistent reporting and data-driven decision-making across Vanguard’s Financial Advisor Services division.

Centralized data warehouse FAS 360:

Vanguard’s first wave of modernization established FAS 360 as a centralized enterprise data warehouse, migrating from a fragmented “data swamp” of Parquet files on Amazon Simple Storage Service (Amazon S3) to a structured, unified system.

The following architecture diagram leverages Amazon S3 for raw data storage with Amazon Redshift serving as the core processing engine, providing integrated access for BI tools, analyst exploration, and data science workloads.

Here are the key benefits achieved with this architecture:

Single source of truth – Consolidated fragmented data sources into a unified system, minimizing multiple versions of truth and establishing consistent reporting practices across the organization
10x faster query performance – Dramatically improved query response times compared to the previous solution, helping enhance analyst productivity and enabling more complex analytical workloads
Seamless data lake integration – Maintained connectivity with the broader data lake environment while providing structured warehouse capabilities
Enhanced business agility – Increased trust in metrics and unlocked new use cases that were previously untenable, directing the new migration efforts toward the FAS360 system

This centralized architecture successfully addressed the limitations of Vanguard’s previous approach, where data was scattered across individuals with limited governance, and established a foundation for their subsequent architectural evolution.

Significant growth and expanding use cases

Vanguard FAS experienced remarkable growth in their data analytics requirements over a two-year period, demonstrating the rapid evolution of modern data needs:

Initial State:

20 AWS Glue ETL jobs processing daily data loads
Approximately 100 tables in their data warehouse
20 Tableau dashboards serving business users
Around 60 analysts accessing the system

Two Years Later:

20 TB in data volume in Amazon Redshift and another 150 TB in S3 data lake
600+ AWS Glue ETL jobs (a 30x increase) handling complex data transformations
300+ tables (3x growth) storing diverse business data
250+ Amazon Redshift materialized views optimizing query performance
Over 500 Tableau dashboards (25x expansion) serving various business functions
500,000+ user queries/months

This exponential growth reflected FAS’s increasing reliance on data-driven decision making across the business functions, from risk management and compliance to client service optimization and operational efficiency improvements.

Resource contention and performance bottlenecks

As Vanguard FAS’s data environment expanded, their initial architecture, a single Amazon Redshift provisioned cluster with 2 nodes (ra3.4xlarge), began experiencing severe performance challenges that threatened business operations:

ETL performance issues:

Frequent ETL SLA failures disrupting critical business processes
Tableau extract failures resulting in stale dashboard data
Resource conflicts between data ingestion and transformation workloads

End-user experience degradation:

Poor query performance during peak usage periods
Table and object locking issues preventing concurrent access
Frustrated analysts unable to perform deep data exploration
Limited ability to run long-running analytical queries

Operational challenges:

Resource contention between ETL workloads and interactive analytics
Inability to scale compute resources independently for different workload types
Single point of failure affecting the data operations
Difficulty in workload prioritization and resource allocation

These challenges were fundamentally limiting FAS’s ability to leverage their data assets effectively, impacting everything from daily operational reporting to strategic business analysis.

Solution overview

To address these critical challenges, Vanguard FAS implemented following multi-warehouse architecture that leverages the advanced data sharing capabilities of Amazon Redshift for workload isolation and independent scaling.

Producer – Amazon Redshift Provisioned Cluster

The central hub consists of the original Amazon Redshift provisioned cluster with RA3 nodes, optimized for consistent, predictable workloads:

Dedicated ETL processing: Handles data ingestion, transformation, and loading operations
Write workload optimization: Manages data writes and updates without interference
Cost optimization: Utilizes reserved instances for predictable, steady-state workloads
Data governance: Serves as the single source of truth for the enterprise data

Consumer – Amazon Redshift Serverless Workgroups

Multiple Amazon Redshift Serverless instances serve as specialized consumer endpoints which auto-scales compute resources based on demand:

Analyst Exploration: Dedicated environment for analyst data discovery and experimentation
BI Tools: Instance optimized specifically for Tableau dashboard and visualization workloads
Data Science: For complex and long running machine learning workloads in completely isolated environment

The solution leverages the native data sharing capabilities of Amazon Redshift to enable secure connectivity between the producer and consumers instances. Consumer clusters can access live data from the producer without data movement, providing real-time access to the most current information available. This zero-copy sharing approach alleviates the need for data duplication or complex synchronization processes, helping reduce both storage costs and operational complexity.

Results

The implementation of the multi-warehouse architecture delivered significant improvements across the key performance indicators:

Predictable Performance

Nightly ETL cycles now consistently complete before the 9 AM SLA, eliminating the previous SLA failures that disrupted business operations and ensuring fresh data is available for morning business activities. Dashboards and reports now reflect the most current data available, providing teams with up-to-date insights for decision-making.

Improved Analyst Productivity and Experience

The new architecture removed the restrictive 10-minute query timeout that previously prevented deep ad hoc exploratory queries. Analysts can now run complex analytical workloads exceeding 30 minutes in a fully isolated environment without impacting other users or ETL processes. This change, combined with significantly faster query response times, has led to higher analyst satisfaction and productivity across the team.

New Analytical Capabilities

The architecture introduced a dedicated “Data Lab” environment where analysts have write access to experiment with data using CREATE TABLE AS SELECT (CTAS) commands. Each workload type can now scale independently based on demand, with different consumer clusters optimized for specific use cases, enabling more sophisticated analytical approaches.

Operational Excellence

The separation of workloads enabled efficient utilization of compute resources across different patterns, leading to better cost control through appropriate sizing, serverless pay-as-you-go pricing, and reserved instance usage. The cleaner separation of concerns between ETL and analytics workloads has simplified overall management of the data platform.

Ongoing modernization: Evolution toward data mesh architecture

As Vanguard’s data environment matured and their success with the multi-warehouse architecture enabled broader adoption across the organization, they recognized an opportunity to evolve their architecture to match their organizational growth. The expanding portfolio of data products and increasing number of teams leveraging the system created new opportunities for innovation.

As Vanguard’s data environment grew, three key challenges emerged:

Centralized ownership bottleneck – Single-team data ownership couldn’t scale with the growing number of data products
Write workload contention – Resource contention persisted for write operations on shared endpoints
Cross-domain dependencies – Data object interdependencies across business domains slowed data product development

Rationale for Data Mesh

Vanguard’s decision to adopt Data Mesh was driven by the need to:

Decentralize data ownership by establishing data domains with dedicated stewards
Remove write contention by isolating each domain’s data loads to separate endpoints
Enable autonomous development allowing stewards to own the complete data product lifecycle and governance
Leverage modern data lake capabilities using AWS Glue and Apache Iceberg format for data product curation

This evolution supports Vanguard’s ability to scale organizationally while building on the technical foundation and operational excellence achieved with their multi-warehouse architecture. Building on the success of their Amazon Redshift multi-warehouse implementation, Vanguard FAS is now exploring on the next phase of their data architecture evolution, implementing following data mesh approach.

This new data mesh architecture has several key components that work together to enable scalable, domain-oriented data management.

Domain-Oriented Data Ownership

Vanguard is establishing distinct data domains aligned with business functions and assigning dedicated data stewards to each domain for clear ownership and accountability. This strategy shifts from centralized data management to a decentralized model where data ownership and responsibility can be distributed across business domains, enabling teams closer to the data to make informed decisions about their domain-specific needs.

Distributed Data Architecture

The new architecture isolates domain-specific data loads to separate compute endpoints and creates independent data processing pipelines for each domain. This approach helps reduce cross-domain dependencies and conflicts that previously slowed development cycles, allowing teams to iterate and deploy changes without waiting for coordination across the entire organization.

Data Product Approach

Vanguard is curating data products on the data lake using Apache Iceberg format and leveraging AWS Glue for metrics computation and data lake integration. This approach treats data as products with defined SLAs and quality metrics, helping facilitate reliable, high-quality data delivery that downstream consumers can depend on with confidence.

Self-Service Analytics

The implementation enables domain teams to manage their complete data product lifecycle independently while maintaining enterprise governance standards. Vanguard provides comprehensive tools and systems for independent data management, allowing teams to innovate quickly without compromising data quality or security, ultimately accelerating time-to-insight across the organization.This evolution represents a natural progression from centralized data warehouse to multi-warehouse architecture, and finally to a fully distributed, domain-oriented data mesh that can scale with Vanguard’s continued growth.

Conclusion

Vanguard Financial Advisor Services’ journey demonstrates that scaling analytics is no longer about scaling a single warehouse bigger, but about architecting for workload isolation, independent scaling, and organizational growth.

By evolving from a single 2-node RA3 provisioned cluster to a multi-warehouse architecture using Amazon Redshift Serverless and Provisioned, Vanguard achieved measurable, production-grade outcomes:

500,000+ monthly queries supported without ETL or dashboard contention
100% ETL SLA adherence, with nightly pipelines completing before 9 AM
25x growth in BI consumption (20 → 500+ Tableau dashboards) without performance degradation
8x growth in analyst population (60 → 500+) enabled through workload isolation
30x increase in ETL pipelines (20 → 600+) without re-architecting ingestion logic
Zero-copy Amazon Redshift data sharing across producer and consumer warehouses, minimizing data duplication and synchronization costs
Removal of 10-minute query limits, unlocking advanced exploratory and long-running analytics

Critically, these gains were not achieved by over-provisioning compute, but by right-sizing and specializing compute per workload, reserving capacity where demand was predictable (ETL) and using Amazon Redshift Serverless auto-scaling where demand was bursty (BI and ad-hoc analysis).

As Vanguard now progresses toward a domain-oriented data mesh, their experience reinforces a key lesson: Multi-warehouse architecture is a foundational enabler for organizational scale, data product ownership, and autonomous analytics.For organizations experiencing exciting growth in their data analytics requirements, Vanguard’s approach showcases the tremendous possibilities that await. With the right architecture and the help of AWS services, organizations can transform their data infrastructure to achieve remarkable improvements in performance, significant cost reductions, and unlock powerful new analytical capabilities that accelerate business value creation.

AWS encourages you to connect with your AWS Account Team to engage an AWS analytics specialist who can provide expert architectural guidance and tailored recommendations to help you achieve your data transformation goals.

© 2026 The Vanguard Group, Inc. and Amazon Web Services, Inc. All rights reserved. This material is provided for informational purposes only and is not intended to be investment advice or a recommendation to take any particular investment action.

About the authors

Scale fine-grained permissions across warehouses with Amazon Redshift and AWS IAM Identity Center

2026-03-18 Raghu Kuppala

Post Syndicated from Raghu Kuppala original https://aws.amazon.com/blogs/big-data/scale-fine-grained-permissions-across-warehouses-with-amazon-redshift-and-aws-iam-identity-center/

Amazon Redshift is a fully managed, petabyte-scale cloud-based data warehouse that you can use to scale analytics workloads effortlessly. As organizations expand their analytics capabilities across multiple business units, they need streamlined approaches for defining and managing fine-grained permissions for each warehouse. Many organizations use external identity providers (IdPs) like Microsoft Entra ID, Okta, or Ping to manage workforce identities centrally and need streamlined data warehouse integration with consistent access controls. We address these challenges by introducing Amazon Redshift federated permissions with AWS IAM Identity Center integration so that you can define security policies once and automatically enforce them across the warehouses in your account.

Amazon Redshift federated permissions are now supported with IAM Identity Center across multiple AWS Regions, where you can use identities from supported identity provider (IdP) such as Microsoft Entra ID, Okta, Ping Identity, or OneLogin across supported AWS Regions with IAM Identity Center. This enables you to align with business requirements including resiliency and proximity to users. You can now extend IAM Identity Center from your primary AWS Region to additional Regions of your choice based on your data residency requirements. In that region, you can get horizontal multi-warehouse scalability by adding new warehouses using Amazon Redshift federated permissions across multiple warehouses. With Redshift federated permissions, you define data permissions once from any Redshift warehouse in that region and automatically enforce them across all warehouses in the account in that region.

This post provides a comprehensive technical walkthrough for implementing Amazon Redshift federated permissions with AWS IAM Identity Center to help achieve scalable data governance across multiple data warehouses. It demonstrates a practical architecture where an Enterprise Data Warehouse (EDW) serves as the producer data warehouse with centralized policy definitions, helping automatically enforce security policies to consuming Sales and Marketing data warehouses without manual reconfiguration. You will learn how to do the following:

Configure IAM Identity Center connections for both data sharing producers and consumers
Register Amazon Redshift serverless namespaces with AWS Glue Data Catalog
Set up trusted identity propagation (TIP)
Create and attach Dynamic data masking policies to help protect personally identifiable information (PII) like customer dates of birth
Implement row-level security policies to control data visibility based on user roles
Map IdP groups to Amazon Redshift database roles for seamless access management

Prerequisites

Before you begin, verify that you have the following:

An AWS account with admin role privileges
Assign data lake admin permissions to above admin role. For instructions, see Create a data lake administrator
Enable IAM Identity Center integration using the Lake Formation
Review the blog post to understand the setup process of AWS IAM Identity Center integration with Amazon Redshift Query Editor v2
IAM Identity Center enabled in your AWS account, with users and groups created as listed under Solution overview section of User access (figure 2)
As an Amazon Redshift superuser, grant CONNECT, CREATE TABLE, INSERT, SELECT, and sys:secadmin permissions to AWSIDC:awssso-admin database role

An IAM role for IAM Identity Center access:

Step 1:Create an IAM policy for Amazon Redshift access. To integrate Amazon Redshift with IAM Identity Center, create an IAM policy (for example, aws-idc-policy) in the account where your Amazon Redshift data warehouse exists:

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Sid": "VisualEditor0",
      "Effect": "Allow",
      "Action": [
        "redshift:DescribeQev2IdcApplications",
        "redshift-serverless:ListNamespaces",
        "redshift-serverless:ListWorkgroups",
        "redshift-serverless:GetWorkgroup"
      ],
      "Resource": [
        "arn:aws:redshift-serverless:<AWS Region>:<AWS Account ID>:workgroup/*",
        "arn:aws:redshift-serverless:<AWS Region>:<AWS Account ID>:namespace/*"
      ]
    },
    {
      "Sid": "VisualEditor1",
      "Effect": "Allow",
      "Action": [
        "sso:DescribeApplication",
        "sso:DescribeInstance"
      ],
      "Resource": [
        "arn:aws:sso:::instance/<IAM Identity Center Instance ID>",
        "arn:aws:sso::<AWS Account ID>:application/<IAM Identity Center Instance ID>/*"
      ]
    }
  ]
}

Step 2: Create the IAM role. Create an IAM role (Amazon Redshift – Customizable) in the account where your Amazon Redshift data warehouse exists (for example, IAMIDCRedshiftRole).
Step 3: Attach IAM policies to the role. Attach the following two IAM policies to the previously mentioned role:
- aws-idc-policy
- AmazonRedshiftFederatedAuthorization

Step 4: Update the trust relationships. Update the trust relationships for this role with the following:

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Principal": {
        "Service": "redshift.amazonaws.com"
      },
      "Action": [
        "sts:AssumeRole",
        "sts:SetContext"
      ]
    }
  ]
}

Note: AmazonRedshiftFederatedAuthorization is a managed policy that provides the necessary permissions for running queries with Amazon Redshift federated authorization.

Attach above IAMIDCRedshiftRole IAM role to all Redshift serverless endpoints

Solution overview

The following architecture diagram demonstrates federated permissions in a multi-warehouse environment, enabling scalable data governance across Amazon Redshift warehouses by automatically enforcing security policies.

Figure 1: Sample architecture diagram

User access

Users can access data warehouses through Amazon Redshift Query Editor v2, third-party SQL editors (such as DBeaver and SQL Workbench), or custom client applications. The access methods help provide consistent security enforcement.

Figure 2: Solution overview flow

AWS IAM Identity Center integration

IAM Identity Center provides centralized authentication with single sign-on capabilities and automatically assigns role-based permissions based on organizational roles. This identity federation links corporate identities directly to AWS resources, making sure that authentication occurs at the identity layer before warehouse access.

Multi-warehouse architecture

This architecture uses three distinct data warehouses that serve different business functions while sharing centralized security policies.

Enterprise Data Warehouse (EDW)

The EDW serves as the central repository for enterprise data. In this architecture, customer and product data are stored in the Customer Profile Database (CPD), where administrators define two critical security policies:

Dynamic data masking (DDM) – Masks sensitive customer Date of Birth (DOB) fields for both Sales Analyst and Marketing Analyst roles, helping protect personally identifiable information (PII) while allowing analytical work
Row-level security (RLS) – Controls product visibility based on user roles. Sales Analysts view only launched products, while Marketing Analysts view both launched and planned products

The EDW registers with the AWS Glue Data Catalog, creating a unified metadata repository that makes data discoverable across the warehouses in the account. This registration establishes the foundation for federated permissions, enabling automatic policy propagation.

Sales data warehouse

When Sales Analysts query customer and product tables, the system automatically enforces policies defined in the EDW through federated permissions. The registered namespace from the EDW automatically mounts as an external database, alleviating the need to recreate or reattach policies. Customer DOB fields appear masked, and only launched products are visible without additional configurations.

Marketing data warehouse

The Marketing Data Warehouse automatically inherits and enforces EDW security policies. Customer DOB fields remain masked to help protect PII, but with RLS policies, Marketing Analysts can view both launched and planned products. This provides the broader visibility needed for marketing planning. This differentiated access control is automatically enforced based on user roles.

Walkthrough

In this walkthrough, you create two Amazon Redshift IAM Identity Center (IDC) connections:

Data sharing producer identity center connection – Assigned to the edw-wg Amazon Redshift serverless workgroup
Data sharing consumer identity center connection – Assigned to the cpd-sales-wg and cpd-marketing-wg Amazon Redshift serverless workgroups

Set up IDC connections for Amazon Redshift federated permissions

In this section, you configure the IAM Identity Center connections that enable federated authentication across your warehouses. You will create separate connections for the producer (policy-defining) warehouse and consumer warehouses.

Configure Amazon Redshift data sharing producer IDC connection

To create the producer IDC connection:

Open the Amazon Redshift Serverless console.
Choose IAM Identity Center connections by expanding the hamburger menu.
Choose Create application.
Verify that you see “Amazon Redshift connected to IAM Identity Center”, and then choose Next.
Configure the connection properties:
- For IAM Identity Center display name, enter a name.
- For Managed application name, enter rs-multicluster-producer.
- For Identity provider namespace, choose AWSIDC.
- For IAM role for IAM Identity Center access, choose the TIP IAM role that you created.
- For Query editor v2 application, choose Enable the query editor v2 application.
- For IAM Identity Center application type, choose Configure Amazon Redshift federated permissions using AWS IAM Identity Center (Recommended).
- Choose Next.
For Configure client connections that use third-party IdPs, choose No.
Choose Next.
Verify that the configuration details match your inputs and then choose Create Application.

Figure 3: Data sharing producer IDC connection

Configure data sharing consumer IDC connection

To create the consumer IDC connection:

Open the Amazon Redshift Serverless console.
Choose IAM Identity Center connections by expanding the hamburger menu.
Choose Create application.
Verify that you see “Amazon Redshift connected to IAM Identity Center”, and then choose Next.
Configure the connection properties:
- For IAM Identity Center display name, enter a name.
- For Managed application name, enter rs-multicluster-consumer.
- For Identity provider namespace, choose AWSIDC.
- For IAM role for IAM Identity Center access, choose the TIP IAM role that you created.
- For Query editor v2 application, you will see the notification “You already have a query editor v2 application.”
- For IAM Identity Center application type, deselect Configure Amazon Redshift federated permissions using AWS IAM Identity Center (Recommended).
- For Trusted identity propagation, choose AWS Lake Formation access grants and Amazon Redshift Connect.
- Choose Next.
For Configure client connections that use third-party IdPs, choose No.
Choose Next.
Verify that the configuration details match your inputs, and then choose Create Application.
Add your required users or groups to the IDC application for Amazon Redshift data sharing consumers.

Figure 4: Data sharing consumer IDC connection

Configure Amazon Redshift data sharing producer IDC connection for Amazon Redshift serverless namespace

To register the edw-ns namespace with federated permissions:

Open the Amazon Redshift Serverless Namespace console.
Choose your Amazon Redshift Serverless namespace.
Choose Actions, and then select Register with AWS Glue Data Catalog.
Choose Register with Amazon Redshift federated permissions.
Choose Amazon Redshift federated permissions using AWS IAM Identity Center.
Choose Register.

Figure 5: Amazon Redshift data warehouse registration with Glue Data Catalog

Figure 6: Amazon Redshift data warehouse registration with Glue Data Catalog

Note: IAM Identity Center managed application ARN Data sharing producer IDC connection created would be used.

Configure Amazon Redshift data sharing consumer IDC connection for existing serverless namespace

For cpd-sales-wg and cpd-marketing-wg serverless workgroups, gather the following information from your registered IAM Identity Center connection:

IAM Identity Center display name
Identity provider namespace
IAM Identity Center managed application ARN
IAM role for IAM Identity Center access

Run the following SQL command as a database administrator to enable the integration:

CREATE IDENTITY PROVIDER "<IAM Identity Center display name>" TYPE AWSIDC
NAMESPACE '<Identity provider namespace>'
APPLICATION_ARN '<IAM Identity Center managed application ARN>'
IAM_ROLE '<IAM role for IAM Identity Center access>';

To modify an existing identity provider, use the ALTER IDENTITY PROVIDER command:

ALTER IDENTITY PROVIDER "<IAM Identity Center display name>"
NAMESPACE '<Identity provider namespace>';
ALTER IDENTITY PROVIDER "<IAM Identity Center display name>"
IAM_ROLE default | '<IAM role for IAM Identity Center access>';

Data preparation and access setup from producer

In this section, you create the customer and product tables, load sample data, create DDM and RLS policies, attach the policies to database roles and grant SELECT permissions to the roles.

Prepare data on EDW

Connect to the EDW data warehouse as an IDC Admin user and run the following SQL commands.

Create the product table:

CREATE TABLE product (
  product_id VARCHAR(16) NOT NULL,
  product_desc VARCHAR(200),
  current_price NUMERIC(7,2),
  wholesale_cost NUMERIC(7,2),
  category_desc VARCHAR(50),
  launch_status VARCHAR(50)
);

Insert sample product data:

INSERT INTO product 
VALUES 
  ('AAAAAAAAAFNPEAAA','At least concerned authors adopt just brown, federal',7.12,4.12,'Jewelry','launched'),
  ('AAAAAAAAOAAGDAAA','Complex services may not find totally changing accountants. Tiny, available ministers could not know always systems. Hot, male speakers discer',8.08,5.49,'Shoes','planned'),
  ('AAAAAAAAMJJMCAAA','Rows could prevent political, old duties. Just international stairs would regret police. Conditions discard always interesting, warm years. Present jobs shall take nearby relatively dreadful',8.18,5.31,'Jewelry','launched'),
  ('AAAAAAAAKLBLBAAA','Suddenly external sentences believe then by the assets. Simultaneously young feet could not probe separately shortly new men. Forms work again individuals. Images',17.96,7.9,'Shoes','launched'),
  ('AAAAAAAAMBKMCAAA','Clubs see finally materials. Significant objectives sell fairly left, civil power',3.18,3.84,'Books','launched'),
  ('AAAAAAAACPCAAAAA','Perhaps past preferences tell rather to a accounts. Very common feet can command never available final years; minutes expect recent, due employers. Altogether english shoes',9.84,0.19,'Electronics','planned'),
  ('AAAAAAAAFOIABAAA','More responsible characters go left factors. Championships shall stand twice new, important shows. Books could receive too able, national pounds. Central',3.55,2.2,'Books','launched'),
  ('AAAAAAAAKGBIAAAA','High, political changes shall not',9.55,5.25,'Electronics','launched');

Create the customer table:

CREATE TABLE customer (
  customer_id VARCHAR(16),
  first_name VARCHAR(20),
  last_name VARCHAR(30),
  date_of_birth VARCHAR(32),
  birth_country VARCHAR(20),
  email_address VARCHAR(50)
);

Insert sample customer data:

INSERT INTO customer
VALUES
  ('AAAAAAAALAMKHGBA','Regina','Coleman','1926-12-17','GAMBIA','[email protected]'),
  ('AAAAAAAAMCMKHGBA','John','Bell','1980-01-07','PAPUA NEW GUINEA','[email protected]'),
  ('AAAAAAAANNMKHGBA','Jacqueline','Pierre','1951-12-18','SAMOA','[email protected]'),
  ('AAAAAAAANFNKHGBA','Frank','Mackay','1992-03-19','HONG KONG','[email protected]'),
  ('AAAAAAAAOGNKHGBA','Anthony','Miller','1948-02-26','ALGERIA','[email protected]'),
  ('AAAAAAAACPOKHGBA','Bradley','Sawyer','1956-12-25','ZAMBIA','[email protected]'),
  ('AAAAAAAAOIPKHGBA','Robert','Carter','1951-01-01','UNITED STATES','[email protected]'),
  ('AAAAAAAALJPKHGBA','Ola','High','1980-11-19','SUDAN','[email protected]');

Create DDM and RLS policies

Create the masking policy for customer date of birth:

CREATE MASKING POLICY mask_cust_dob  
WITH (date_of_birth VARCHAR(32))  
USING (sha2(date_of_birth, 256)::TEXT);

Create RLS policies for product launch status:

CREATE RLS POLICY product_launch_status  
WITH (launch_status VARCHAR(50))   
USING (launch_status = 'launched');
  
CREATE RLS POLICY product_launch_status_all
WITH (launch_status VARCHAR(50))   
USING (launch_status IN ('launched','planned'));

Create Amazon Redshift DB roles for Sales and Marketing groups

Create the database roles:

CREATE ROLE "AWSIDC:awssso-sales";
CREATE ROLE "AWSIDC:awssso-marketing";

Attach masking policies

Attach the masking policy to both roles:

ATTACH MASKING POLICY mask_cust_dob  
ON dev.public.customer (date_of_birth)  
TO ROLE "AWSIDC:awssso-marketing";
ATTACH MASKING POLICY mask_cust_dob  
ON dev.public.customer (date_of_birth)  
TO ROLE "AWSIDC:awssso-sales";

Attach RLS policies and enable RLS on product table

Attach the RLS policies and enable row-level security:

ATTACH RLS POLICY product_launch_status  
ON dev.public.product  
TO ROLE "AWSIDC:awssso-sales"; 
ATTACH RLS POLICY product_launch_status_all  
ON dev.public.product  
TO ROLE "AWSIDC:awssso-marketing";
ALTER TABLE dev.public.product ROW LEVEL SECURITY ON;

Grant access to tables to roles

Grant SELECT permissions to both roles:

GRANT SELECT ON dev.public.customer TO ROLE "AWSIDC:awssso-sales";
GRANT SELECT ON dev.public.customer TO ROLE "AWSIDC:awssso-marketing";
GRANT SELECT ON dev.public.product TO ROLE "AWSIDC:awssso-sales"; 
GRANT SELECT ON dev.public.product TO ROLE "AWSIDC:awssso-marketing";

Connect to SALES data warehouse using IAM Identity Center

To connect as a Sales Analyst:

Connect to cpd-sales-wg using the IAM Identity Center connection type as user sales-analyst, and then choose Continue.
Choose sales-analyst, and then choose Next.
Enter your password, and then choose Sign in.
Enter your MFA code, and then choose Sign in.

You are now connected to Amazon Redshift Query Editor V2 with a successful connection to cpd-sales-wg as sales-analyst.

Figure 7: Connect to Sales data warehouse as IDC user

Query shared data as Sales Analyst

Query the customer table with dynamic data masking applied:

SELECT * FROM "dev@edw-ns"."public"."customer";

You can successfully access the customer table, but the sensitive information in the date_of_birth column is encrypted.

Figure 8: Result set of customer table

Query the product table with row-level security enabled:

SELECT * FROM "dev@edw-ns"."public"."product";

You can successfully access the product table, but only view data for products with a launch_status value of launched.

Figure 9: Result set of product table

Note: To connect to the data sharing producer onboarded to Amazon Redshift federated permissions as an IDC user, a superuser is required to provide a CONNECT privilege to the IDC user trying to connect. For more information about how to grant the CONNECT privileges to the user, see Connect privileges in the Amazon Redshift Database Developer Guide.

Connect to Marketing data warehouse using IAM Identity Center

To connect as a Marketing Analyst:

Connect to cpd-marketing-wg using the IAM Identity Center connection type as user marketing-analyst, and then choose Continue.
Choose marketing-analyst, and then choose Next.
Enter your password, and then choose Sign in.
Enter your MFA code, and then choose Sign in.

You are now connected to Amazon Redshift Query Editor V2 with a successful connection to cpd-marketing-wg as marketing-analyst.

Figure 10: Connect to Marketing data warehouse as IDC user

Query shared data as Marketing Analyst

Query the customer table with dynamic data masking applied:

SELECT * FROM "dev@edw-ns"."public"."customer";

You can successfully access the customer table, but the sensitive information in the date_of_birth column is encrypted.

Figure 11: Result set of customer table

Query the product table with row-level security enabled:

SELECT * FROM "dev@edw-ns"."public"."product";

You can successfully access the product table and view data for products with launch_status values of both launched and planned.

Figure 12: Result set of product table

Additional resources

For more information about implementing federated permissions in your environment, see the following resources:

AWS Documentation

Amazon Redshift federated permissions

AWS Blogs

AWS Demo

Introducing Amazon Redshift Federated Permissions

Key benefits

Reduced administrative overhead – Centralized policy management removes manual replication
Consistent security enforcement – Policies apply uniformly across the warehouses and access methods
Seamless identity integration – Single sign-on with existing identity providers through trusted identity propagation and role-based access control

Conclusion

This post showed you how Amazon Redshift federated permissions with AWS IAM Identity Center integration helps streamline multi-warehouse data governance by centralizing security policy management. You define dynamic data masking and row-level security policies once in a central Enterprise Data Warehouse, and they automatically enforce across the connected data warehouses in the same account and Region.

About the authors

Investigating multi-vector attacks in Log Explorer

2026-03-10 Jen Sells

Post Syndicated from Jen Sells original https://blog.cloudflare.com/investigating-multi-vector-attacks-in-log-explorer/

In the world of cybersecurity, a single data point is rarely the whole story. Modern attackers don’t just knock on the front door; they probe your APIs, flood your network with “noise” to distract your team, and attempt to slide through applications and servers using stolen credentials.

To stop these multi-vector attacks, you need the full picture. By using Cloudflare Log Explorer to conduct security forensics, you get 360-degree visibility through the integration of 14 new datasets, covering the full surface of Cloudflare’s Application Services and Cloudflare One product portfolios. By correlating telemetry from application-layer HTTP requests, network-layer DDoS and Firewall logs, and Zero Trust Access events, security analysts can significantly reduce Mean Time to Detect (MTTD) and effectively unmask sophisticated, multi-layered attacks.

Read on to learn more about how Log Explorer gives security teams the ultimate landscape for rapid, deep-dive forensics.

The flight recorder for your entire stack

The contemporary digital landscape requires deep, correlated telemetry to defend against adversaries using multiple attack vectors. Raw logs serve as the “flight recorder” for an application, capturing every single interaction, attack attempt, and performance bottleneck. And because Cloudflare sits at the edge, between your users and your servers, all of these events are logged before the requests even reach your infrastructure.

Cloudflare Log Explorer centralizes these logs into a unified interface for rapid investigation.

Log Types Supported

Zone-Scoped Logs

Focus: Website traffic, security events, and edge performance.

HTTP Requests	As the most comprehensive dataset, it serves as the “primary record” of all application-layer traffic, enabling the reconstruction of session activity, exploit attempts, and bot patterns.
Firewall Events	Provides critical evidence of blocked or challenged threats, allowing analysts to identify the specific WAF rules, IP reputations, or custom filters that intercepted an attack.
DNS Logs	Identify cache poisoning attempts, domain hijacking, and infrastructure-level reconnaissance by tracking every query resolved at the authoritative edge.
NEL (Network Error Logging) Reports	Distinguish between a coordinated Layer 7 DDoS attack and legitimate network connectivity issues by tracking client-side browser errors.
Spectrum Events	For non-web applications, these logs provide visibility into L4 traffic (TCP/UDP), helping to identify anomalies or brute-force attacks against protocols like SSH, RDP, or custom gaming traffic.
Page Shield	Track and audit unauthorized changes to your site’s client-side environment such as JavaScript, outbound connections.
Zaraz Events	Examine how third-party tools and trackers are interacting with user data, which is vital for auditing privacy compliance and detecting unauthorized script behaviors.

Account-Scoped Logs

Focus: Internal security, Zero Trust, administrative changes, and network activity.

Access Requests	Tracks identity-based authentication events to determine which users accessed specific internal applications and whether those attempts were authorized.
Audit Logs	Provides a trail of configuration changes within the Cloudflare dashboard to identify unauthorized administrative actions or modifications.
CASB Findings	Identifies security misconfigurations and data risks within SaaS applications (like Google Drive or Microsoft 365) to prevent unauthorized data exposure.
Magic Transit / IPSec Logs	Helps network engineers perform network-level (L3) monitoring such as reviewing tunnel health and view BGP routing changes.
Browser Isolation Logs	Tracks user actions inside an isolated browser session (e.g., copy-paste, print, or file uploads) to prevent data leaks on untrusted sites
Device Posture Results	Details the security health and compliance status of devices connecting to your network, helping to identify compromised or non-compliant endpoints.
DEX Application Tests	Monitors application performance from the user’s perspective, which can help distinguish between a security-related outage and a standard performance degradation.
DEX Device State Events	Provides telemetry on the physical state of user devices, useful for correlating hardware or OS-level anomalies with potential security incidents.
DNS Firewall Logs	Tracks DNS queries filtered through the DNS Firewall to identify communication with known malicious domains or command-and-control (C2) servers.
Email Security Alerts	Logs malicious email activity and phishing attempts detected at the gateway to trace the origin of email-based entry vectors.
Gateway DNS	Monitors every DNS query made by users on your network to identify shadow IT, malware callbacks, or domain-generation algorithms (DGAs).
Gateway HTTP	Provides full visibility into encrypted and unencrypted web traffic to detect hidden payloads, malicious file downloads, or unauthorized SaaS usage.
Gateway Network	Tracks L3/L4 network traffic (non-HTTP) to identify unauthorized port usage, protocol anomalies, or lateral movement within the network.
IPSec Logs	Monitors the status and traffic of encrypted site-to-site tunnels to ensure the integrity and availability of secure network connections.
Magic IDS Detections	Surfaces matches against intrusion detection signatures to alert investigators to known exploit patterns or malware behavior traversing the network.
Network Analytics Logs	Provides high-level visibility into packet-level data to identify volumetric DDoS attacks or unusual traffic spikes targeting specific infrastructure.
Sinkhole HTTP Logs	Captures traffic directed to “sinkholed” IP addresses to confirm which internal devices are attempting to communicate with known botnet infrastructure.
WARP Config Changes	Tracks modifications to the WARP client settings on end-user devices to ensure that security agents haven’t been tampered with or disabled.
WARP Toggle Changes	Specifically logs when users enable or disable their secure connectivity, helping to identify periods where a device may have been unprotected.
Zero Trust Network Session Logs	Logs the duration and status of authenticated user sessions to map out the complete lifecycle of a user’s access within the protected perimeter.

Log Explorer can identify malicious activity at every stage

Get granular application layer visibility with HTTP Requests, Firewall Events, and DNS logs to see exactly how traffic is hitting your public-facing properties. Track internal movement with Access Requests, Gateway logs, and Audit logs. If a credential is compromised, you’ll see where they went. Use Magic IDS and Network Analytics logs to spot volumetric attacks and “East-West” lateral movement within your private network.

Identify the reconnaissance

Attackers use scanners and other tools to look for entry points, hidden directories, or software vulnerabilities. To identify this, using Log Explorer, you can query http_requests for any EdgeResponseStatus codes of 401, 403, or 404 coming from a single IP, or requests to sensitive paths (e.g. /.env, /.git, /wp-admin).

Additionally, magic_ids_detections logs can also be used to identify scanning at the network layer. These logs provide packet-level visibility into threats targeting your network. Unlike standard HTTP logs, these logs focus on signature-based detections at the network and transport layers (IP, TCP, UDP). Query to discover cases where a single SourceIP is triggering multiple unique detections across a wide range of DestinationPort values in a short timeframe. Magic IDS signatures can specifically flag activities like Nmap scans or SYN stealth scans.

Check for diversions

While the attacker is conducting reconnaissance, they may attempt to disguise this with a simultaneous network flood. Pivot to network_analytics_logs to see if a volumetric attack is being used as a smokescreen.

Identify the approach

Once attackers identify a potential vulnerability, they begin to craft their weapon. The attacker sends malicious payloads (e.g. SQL injection or large/corrupt file uploads) to confirm the vulnerability. Review http_requests and/or fw_events to identify any Cloudflare detection tools that have triggered. Cloudflare logs security signals in these datasets to easily identify requests with malicious payloads using fields such as WAFAttackScore, WAFSQLiAttackScore, FraudAttack, ContentScanJobResults, and several more. Review our documentation to get a full understanding of these fields. The fw_events logs can be used to determine whether these requests made it past Cloudflare’s defenses by examining the action, source, and ruleID fields. Cloudflare’s managed rules by default blocks many of these payloads by default. Review Application Security Overview to know if your application is protected.

^{Showing the Managed rules Insight that displays on Security Overview if the current zone does not have Managed Rules enabled}

Audit the identity

Did that suspicious IP manage to log in? Use the ClientIP to search access_requests. If you see a “Decision: Allow” for a sensitive internal app, you know you have a compromised account.

Stop the leak (data exfiltration)

Attackers sometimes use DNS tunneling to bypass firewalls by encoding sensitive data (like passwords or SSH keys) into DNS queries. Instead of a normal request like google.com, the logs will show long, encoded strings. Look for an unusually high volume of queries for unique, long, and high-entropy subdomains by examining the fields: QueryName: Look for strings like h3ldo293js92.example.com, QueryType: Often uses TXT, CNAME, or NULL records to carry the payload, and ClientIP: Identify if a single internal host is generating thousands of these unique requests.

Additionally, attackers may attempt to leak sensitive data by hiding it within non-standard protocols or by using common protocols (like DNS or ICMP) in unusual ways to bypass standard firewalls. Discover this by querying the magic_ids_detections logs to look for signatures that flag protocol anomalies, such as “ICMP tunneling” or “DNS tunneling” detections in the SignatureMessage.

Whether you are investigating a zero-day vulnerability or tracking a sophisticated botnet, the data you need is now at your fingertips.

Correlate across datasets

Investigate malicious activity across multiple datasets by pivoting between multiple concurrent searches. With Log Explorer, you can now work with multiple queries simultaneously with the new Tabs feature. Switch between tabs to query different datasets or Pivot and adjust queries using filtering via your query results.

When you correlate data across multiple Cloudflare log sources, you can detect sophisticated multi-stage attacks that appear benign when viewed in isolation. This cross-dataset analysis allows you to see the full attack chain from reconnaissance to exfiltration.

Session hijacking (token theft)

Scenario: A user authenticates via Cloudflare Access, but their subsequent HTTP_request traffic looks like a bot.

Step 1: Identify high-risk sessions in http_requests.

SELECT RayID, ClientIP, ClientRequestUserAgent, BotScore
FROM http_requests
WHERE date = '2026-02-22' 
  AND BotScore < 20 
LIMIT 100

Step 2: Copy the RayID and search access_requests to see which user account is associated with that suspicious bot activity.


SELECT Email, IPAddress, Allowed
FROM access_requests
WHERE date = '2026-02-22' 
  AND RayID = 'INSERT_RAY_ID_HERE'

Post-phishing C2 beaconing

Scenario: An employee clicked a link in a phishing email which resulted in compromising their workstation. This workstation sends a DNS query for a known malicious domain, then immediately triggers an IDS alert.

Step 1: Find phishing attacks by examining email_security_alerts for violations.

SELECT Timestamp, Threatcategories, To, Alertreason
FROM email_security_alerts
WHERE date = '2026-02-22' 
  AND Threatcategories LIKE 'phishing'

Step 2: Use Access logs to correlate the user’s email (To) to their IP Address.

SELECT Email, IPAddress
FROM access_requests
WHERE date = '2026-02-22'

Step 3: Find internal IPs querying a specific malicious domain in gateway_dns logs.


SELECT SrcIP, QueryName, DstIP, 
FROM gateway_dns
WHERE date = '2026-02-22' 
  AND SrcIP = 'INSERT_IP_FROM_PREVIOUS_QUERY'
  AND QueryName LIKE '%malicious_domain_name%'

Lateral movement (Access → network probing)

Scenario: A user logs in via Zero Trust and then tries to scan the internal network.

Step 1: Find successful logins from unexpected locations in access_requests.

SELECT IPAddress, Email, Country
FROM access_requests
WHERE date = '2026-02-22' 
  AND Allowed = true 
  AND Country != 'US' -- Replace with your HQ country

Step 2: Check if that IPAddress is triggering network-level signatures in magic_ids_detections.

SELECT SignatureMessage, DestinationIP, Protocol
FROM magic_ids_detections
WHERE date = '2026-02-22' 
  AND SourceIP = 'INSERT_IP_ADDRESS_HERE'

Opening doors for more data

From the beginning, Log Explorer was designed with extensibility in mind. Every dataset schema is defined using JSON Schema, a widely-adopted standard for describing the structure and types of JSON data. This design decision has enabled us to easily expand beyond HTTP Requests and Firewall Events to the full breadth of Cloudflare’s telemetry. The same schema-driven approach that powered our initial datasets scaled naturally to accommodate Zero Trust logs, network analytics, email security alerts, and everything in between.

More importantly, this standardization opens the door to ingesting data beyond Cloudflare’s native telemetry. Because our ingestion pipeline is schema-driven rather than hard-coded, we’re positioned to accept any structured data that can be expressed in JSON format. For security teams managing hybrid environments, this means Log Explorer could eventually serve as a single pane of glass, correlating Cloudflare’s edge telemetry with logs from third-party sources, all queryable through the same SQL interface. While today’s release focuses on completing coverage of Cloudflare’s product portfolio, the architectural groundwork is laid for a future where customers can bring their own data sources with custom schemas.

Faster data, faster response: architectural upgrades

To investigate a multi-vector attack effectively, timing is everything. A delay of even a few minutes in the log availability can be the difference between proactive defense and reactive damage control.

That is why we have optimized our ingestion for better speed and resilience. By increasing concurrency in one part of our ingestion path, we have eliminated bottlenecks that could cause “noisy neighbor” issues, ensuring that one client’s data surge doesn’t slow down another’s visibility. This architectural work has reduced our P99 ingestion latency by approximately 55%, and our P50 by 25%, cutting the time it takes for an event at the edge to become available for your SQL queries.

^{Grafana chart displaying the drop in ingest latency after architectural upgrades}

Follow along for more updates

We’re just getting started. We’re actively working on even more powerful features to further enhance your experience with Log Explorer, including the ability to run these detection queries on a custom defined schedule.

^{Design mockup of upcoming Log Explorer Scheduled Queries feature}

Subscribe to the blog and keep an eye out for more Log Explorer updates soon in our Change Log.

Get access to Log Explorer

To get access to Log Explorer, you can purchase self-serve directly from the dash or for contract customers, reach out for a consultation or contact your account manager. Additionally, you can read more in our Developer Documentation.

Scaling Global Storytelling: Modernizing Localization Analytics at Netflix

2026-03-06 Netflix Technology Blog

Post Syndicated from Netflix Technology Blog original https://netflixtechblog.com/scaling-global-storytelling-modernizing-localization-analytics-at-netflix-816f47290641

Valentin Geffrier, Tanguy Cornuau

Each year, we bring the Analytics Engineering community together for an Analytics Summit — a multi-day internal conference to share analytical deliverables across Netflix, discuss analytic practice, and build relationships within the community. This post is one of several topics presented at the Summit highlighting the breadth and impact of Analytics work across different areas of the business.

At Netflix, our goal is to entertain the world, which means we must speak the world’s languages. Given the company’s growth to serving 300 million+ members in more than 190+ countries and 50+ languages, the Localization team has had to scale rapidly in creating more dubs and subtitle assets than ever before. However, this growth created technical debt within our systems: a fragmented landscape of analytics workflows, duplicated pipelines, and siloed dashboards that we are now actively modernizing.

The Challenge: “Who Made This Dub?”

Historically, business logic for localization metrics was replicated across isolated domains. A question as simple as “Who made this dub/subtitle?” is actually complex — it requires mapping multiple data sources through intricate and constantly changing logic, which varies depending on the specific language asset type and creation workflow.

When this logic is copied into isolated pipelines for different use cases it creates two major risks: inconsistency in reporting and a massive maintenance burden whenever upstream logic changes. We realized we needed to move away from these vertical silos.

Our Modernization Strategy

To address this, we defined a vision centered on consolidation, standardization, and trust, executed through three strategic pillars:

1. The Audit and Consolidation Playbook

We initiated a comprehensive audit of over 40 dashboards and tools to assess usage and code quality. Our focus has shifted from patching frontend visualizations to consolidating backend pipelines. For example, we are currently merging three legacy dashboards related to dubbing partner KPIs (around operational performance, capacity, and finances), focusing first on a unified data and backend layer that can support a variety of future frontend iterations.

2. Reducing “Not-So-Tech” Debt

Technical debt isn’t just about code; it is also about the user experience. We define “Not-So-Tech Debt” as the friction stakeholders feel when tools are hard to interpret or can benefit from better storytelling. To fix this, we revamped our Language Asset Consumption tool — instead of reporting dub and subtitle metrics independently, we combine audio and text languages into one consumption language that helps differentiate Original Language versus Localized Consumption and measure member preferences between subtitles, dubs, or a combination of both for a given language. This unlocks more intuitive insights based on actual recurring stakeholder use cases.

3. Investing in Core Building Blocks

We are shifting to a write once, read many architecture. By centralizing business logic into unified tables — such as a “Language Asset Producer” table — we solve the “Who made this dub?” problem once. This centralized source now feeds into multiple downstream domains, including our Dub Quality and Translation Quality metrics, ensuring that any logic update propagates instantly across the ecosystem.

The Future: Event-Level Analytics

Looking ahead, we are moving beyond asset-level metrics to event-level analytics. We are building a generic data model to capture granular timed-text events, such as individual subtitle lines. This data helps us understand how subtitle characteristics (e.g. reading speed) affect member engagement and, in turn, refine the style guidelines we provide to our subtitle linguists to improve the member experience with localized content.

Ultimately, this modernization effort is about scaling our ability to measure and enhance the joy and entertainment we deliver to our diverse global audience, ensuring that every member, regardless of their language, has the best possible Netflix experience.

Scaling Global Storytelling: Modernizing Localization Analytics at Netflix was originally published in Netflix TechBlog on Medium, where people are continuing the conversation by highlighting and responding to this story.

Zero-ETL integrations with Amazon OpenSearch Service

2026-03-04 Omama Khurshid

Post Syndicated from Omama Khurshid original https://aws.amazon.com/blogs/big-data/zero-etl-integrations-with-amazon-opensearch-service/

Amazon OpenSearch Service is a fully managed service that reduces operational overhead, provides enterprise-grade security, high availability, and scalability, and enables you to quickly deploy real-time search, analytics, and generative AI applications. OpenSearch itself is an open-source, distributed search and analytics suite that supports a wide range of use cases, including real-time monitoring, log analytics, and full-text search. OpenSearch Service offers zero-ETL integrations with other Amazon Web Service (AWS) services, enabling seamless data access and analysis without the need for maintaining complex data pipelines.

Zero-ETL refers to a set of integrations designed to minimize or eliminate the need to build traditional extract, transform, load (ETL) pipelines. Traditional ETL processes can be time-consuming and difficult to develop, maintain, and scale. In contrast, zero-ETL integrations allow direct, point-to-point data movement and can also support querying across data silos without physically moving the data.

In this post, we explore various zero-ETL integrations available with OpenSearch Service that can help you accelerate innovation and improve operational efficiency. We cover following types of integrations, their key features, architecture, benefits, pricing, limitation and some general best practices.

Log and storage integrations
- Zero-ETL integration with Amazon Simple Storage Service (Amazon S3)
- Zero-ETL integration with Amazon CloudWatch
Database integrations
- Zero-ETL integration with Amazon DynamoDB
- Integration with Amazon Relational Database Service (RDS) and Amazon Aurora
- Zero-ETL integration with Amazon DocumentDB

The following diagram illustrates the zero-ETL integration architecture in AWS, showing how various AWS services feed data into OpenSearch Service and its associated dashboards:

Zero ETL with Amazon OpenSearch Service

Zero-ETL integration with Amazon S3

Amazon OpenSearch Service direct queries with Amazon S3 provides a zero-ETL integration to reduce the operational complexity of duplicating data or managing multiple analytics tools by enabling you to directly query their operational data, reducing costs and time to action.

Key features of this integration include:

In-place querying: You can use rich analytics capabilities of OpenSearch Service SQL and PPL directly on infrequently-queried data stored outside of OpenSearch Service in Amazon S3.
Selective data ingestion: You can choose which data to bring into OpenSearch Service for detailed analysis, optimizing costs and speeding up queries with indexes like skipping or covering indexes.

The zero-ETL integration with Amazon S3 supports OpenSearch Service. For more information on architecture and feature see the post Modernize your data observability with Amazon OpenSearch Service zero-ETL integration with Amazon S3.

In log analytics use cases, we categorize operational log data into two types:

Primary data includes the most recent and frequently accessed logs used for real-time monitoring and analysis.
Secondary data consists of historical logs that are accessed less frequently but retained for compliance or trend analysis.

You can offload infrequently queried data, such as archival or compliance data, to Amazon S3. With direct query, you can analyze analytics from Amazon S3 without data movement or duplication. However, query performance in OpenSearch Service might slow down when you’re accessing external data sources due to factors like network latency, data transformation, or large data volumes. You can optimize your query performance by using OpenSearch indexes, such as a skipping index, covering index, or materialized view.

While Amazon S3 direct query integration with OpenSearch Service provides on-demand access to data stored in Amazon S3, it is important to remember that OpenSearch’s alerting, monitoring, anomaly detection, and security analytics capabilities can only operate on data that has been explicitly ingested into OpenSearch Service indices. These capabilities would not work with direct query with Amazon S3. However, it will work if the data is indexed with covering or materialized index.

Benefits

With direct queries with Amazon S3, you no longer need to build complex ETL pipelines or incur the expense of duplicating data in both OpenSearch Service and Amazon S3 storage. You also save time and effort by not having to move back and forth between different tools during your analysis.

Pricing

OpenSearch Service separately charges for the compute needed to query your external data in addition to maintaining indexes in OpenSearch Service. Costs for Direct Query is based on the data volume scanned, query execution time, query frequency and frequency with which the indexed data in OpenSearch is kept updated. For more information, see Amazon OpenSearch Service Pricing.

Considerations

In case you are using OpenSearch service to query directly data on Amazon S3, consider the limitations with Direct Query.

Best practices

These are some general and Amazon S3 recommendations for using direct queries in OpenSearch Service. For more information, see Recommendations for using direct queries in Amazon OpenSearch Service.

Use the COALESCE SQL function to handle missing columns and ensure results are returned.
Use limits on your queries to ensure you aren’t pulling too much data back.
If you plan to analyze the same dataset many times, create an indexed view to fully ingest and index the data into OpenSearch Service and drop it when you have completed the analysis.
Drop acceleration jobs and indexes when they’re no longer needed.
Ingest data into Amazon S3 using partition formats of year, month, day, hour to speed up queries.
When you build skipping indexes, use Bloom filters for fields with high cardinality and min/max indexes for fields with large value ranges. Bloom filters are a space efficient probabilistic data structure that lets you quickly check whether an item is possibly in a set. For high-cardinality fields, consider using a value-based approach to improve query efficiency.
Use Index State Management to maintain storage for materialized views and covering indexes.

Zero-ETL integration with Amazon CloudWatch Logs

Amazon CloudWatch Logs serves as a centralized monitoring and storage solution for log files generated across various AWS services. This unified logging service offers a highly scalable platform where all your logging data converges into one manageable system. It provides comprehensive functionality for log management, including real-time viewing, pattern searching, field-based filtering, and secure archival capabilities. By presenting all logs chronologically in a unified stream, CloudWatch Logs eliminates the complexity of managing multiple log sources, transforming diverse logging data into a coherent, time-ordered sequence of events.

The zero-ETL integration between Amazon CloudWatch and Amazon OpenSearch Service enables direct log analysis and visualization while avoiding data redundancy, thereby reducing both technical complexity and costs. You can now leverage two additional query languages alongside the existing CloudWatch Logs Insights QL when using CloudWatch Logs, while as an OpenSearch user, you gain the ability to query CloudWatch logs directly.

Review New Amazon CloudWatch and Amazon OpenSearch Service launch an integrated analytics experience, to explore how the integration works between OpenSearch Service and Amazon CloudWatch Logs.

Benefits

The enhanced CloudWatch Logs Insights console now incorporates OpenSearch PPL and SQL functionality. Users can perform complex log analysis using SQL JOIN operations and various functions (including JSON, mathematical, datetime, and string operations). The PPL option provides additional data filtering and analysis capabilities.
The integration offers ready-to-use dashboards for various AWS services like Amazon Virtual Private Cloud (VPC), AWS CloudTrail, and AWS Web Application Firewall (WAF). These pre-configured visualizations enable quick insights into metrics such as flow patterns, top users, data transfer volumes, and temporal analysis, without requiring manual dashboard configuration.
You can now analyze CloudWatch logs through OpenSearch UI Discover and execute SQL and PPL queries. At the writing of this post, the query execution is limited to 50 log groups.
The direct access and analysis of CloudWatch data within OpenSearch Service removes the need for traditional ETL processes, eliminates separate data ingestion pipelines and avoids data duplication. This streamlined approach significantly reduces both storage expenses and operational complexity. It delivers a more efficient data management solution that simplifies the entire workflow while maintaining cost-effectiveness.

Pricing

When you use OpenSearch Service direct queries, you incur separate charges for OpenSearch Service and the resource used to process and store your data on Amazon CloudWatch Logs. As you run direct queries, you see charges for OpenSearch Compute Units (OCUs) per hour, listed as DirectQuery OCU usage type on your bill.

For interactive queries, OpenSearch Service handles each query with a separate pre-warmed job, without maintaining an extended session.
For indexed view queries, the indexed data is stored in an OpenSearch Serverless collection where you are charged for data indexed (IndexingOCU), data searched (SearchOCU), and data stored in GB.

You can find a pricing example on running an OpenSearch dashboard from either OpenSearch UI or CloudWatch Logs (pricing example n°7).

For more pricing information, see Amazon OpenSearch Service Direct Query pricing.

Considerations

In addition to the OpenSearch Service “direct queries” general limitations, if you are direct querying data in CloudWatch Logs, the following limitations apply:

The direct query integration with CloudWatch Logs is only available on OpenSearch Service collections and the OpenSearch user interface.
OpenSearch Serverless collections have networked payload limitations of 100 MiB.
CloudWatch Logs supports VPC Flow Logs, CloudTrail, and AWS WAF dashboard integrations installed from the console.

Best practices

Besides the general recommendations of OpenSearch Service direct querying, when using OpenSearch Service to direct query data in CloudWatch Logs, the following is recommended:

Specify the log group names within logGroupIdentifier in logGroups command to query multiple log groups in one query, see Multi-log group functions.
Enclose certain fields in backticks to successfully query them when using SQL or PPL commands. Backticks are needed for fields with special characters, such as `@SessionToken` or `LogGroup-A` (non-alphabetic and non-numeric). Refer to CloudWatch Logs Recommendations to see an example.

Zero-ETL integration with Amazon DynamoDB

Amazon DynamoDB zero-ETL integration with OpenSearch Service lets you perform a search on your DynamoDB data by automatically replicating and transforming it without custom code or infrastructure. This zero-ETL integration uses Amazon OpenSearch Ingestion to synchronize data between Amazon DynamoDB and OpenSearch Service cluster or OpenSearch Serverless collection within seconds of it being available.

It uses DynamoDB export to Amazon S3 to create an initial snapshot to load into OpenSearch Service. After the snapshot has been loaded, the plugin uses DynamoDB Streams to replicate any further changes in near real time. Turn on point-in-time recovery (PITR) for export and the DynamoDB Streams feature for ongoing replication.

This feature allows you to capture item-level changes in your table and push the changes to a stream. Every item in tables is processed as an event in OpenSearch Ingestion and can be modified with processors. You can also specify index mapping templates within ingestion pipelines to ensure that your Amazon DynamoDB fields are mapped to the correct fields in your OpenSearch indices.

To learn more, see DynamoDB zero-ETL integration with Amazon OpenSearch Service in the AWS documentation.

When configuring zero-ETL between DynamoDB and OpenSearch Service, consider the differences between the data models. You have the following options with data layout:

Passthrough: Each item in DynamoDB table is directly mapped to one document in OpenSearch Index.
Routing: A single DynamoDB table mapped to multiple OpenSearch Service indices. In DynamoDB, it is common to store denormalized data in one table to optimize for access patterns. For example, a single DynamoDB table containing both customer profiles and order information can be routed to separate OpenSearch Service indices:
- Customer attributes → ‘customers’ index
- Order attributes → ‘orders’ index
You can achieve this by using the conditional routing feature in the OpenSearch ingestion pipeline.
Merge: In some use cases, you need to combine data from multiple DynamoDB tables into a single OpenSearch index. You can use AWS Lambda integration with OpenSearch Ingestion to perform lookups on other DynamoDB tables and merge data from multiple DynamoDB tables.

Pricing

There is no additional cost to use this feature apart from the cost of the existing underlying components, including OpenSearch Ingestion charges OpenSearch Compute Units (OCUs) which is used to replicate data between Amazon DynamoDB and OpenSearch Service. Furthermore, this feature uses Amazon DynamoDB Streams for the change data capture (CDC), and you incur the standard costs for Amazon DynamoDB Streams.

Considerations

Consider the following limitations when you set up an OpenSearch Ingestion pipeline for DynamoDB:

At the writing of this post, the OpenSearch Ingestion integration with DynamoDB doesn’t support cross-Region and cross-account ingestion.
An OpenSearch Ingestion pipeline supports only one DynamoDB table as its source.

Best practices

For complete information, see Best practices for working with DynamoDB zero-ETL integration and OpenSearch Service

Integration with Amazon Aurora and Amazon RDS

Amazon RDS and Amazon Aurora integration with OpenSearch Service eliminates complex data pipelines and enables near real-time data synchronization between Amazon Aurora and Amazon RDS databases (including RDS for MySQL and RDS for PostgreSQL) with advanced search capabilities on transactional databases. You can use an OpenSearch Ingestion pipeline with Amazon RDS or Amazon Aurora to export existing data and stream changes (such as create, update, and delete) to OpenSearch Service domains and collections. The OpenSearch Ingestion pipeline incorporates change data capture (CDC) infrastructure to provide a high-scale, low-latency way to continuously stream data from Amazon RDS or Amazon Aurora.

This automated process keeps your data consistently up to date in OpenSearch Service, making it readily available for search and analysis purpose. The pipeline ensures data consistency by continuously polling or receiving changes from the Amazon Aurora cluster or Amazon RDS and updating the corresponding documents in the OpenSearch index. OpenSearch Ingestion supports end-to-end acknowledgement to ensure data durability. An OpenSearch Ingestion pipeline also maps incoming event actions into corresponding bulk indexing actions to help ingest documents. This keeps data consistent, so that every data change in Amazon RDS is reconciled with the corresponding document changes in OpenSearch.

For details on the architecture, refer to Integrating Amazon OpenSearch Ingestion with Amazon RDS and Amazon Aurora. To get started, refer to OpenSearch Ingestion pipeline with Amazon RDS or Using an OpenSearch Ingestion pipeline with Amazon Aurora.

Pricing

There is no additional charge for using this feature beyond the cost of your existing underlying resources, such as OpenSearch Service, OpenSearch Ingestion pipelines (OCUs), and Amazon RDS or Amazon Aurora. Additional costs may include storage used for enabling enhanced binlogs for MySQL and WAL logs for PostgreSQL for change data capture. You also incur storage costs for snapshot exports from your database to Amazon S3 used for the initial data.

Considerations

Consider the following limitations when you set up the integration for Amazon RDS or Amazon Aurora:

Support both Aurora MySQL or RDS for MySQL (8.0 and above) and Aurora PostgreSQL or RDS for PostgreSQL (16 and above).
Requires same-Region and same-account deployment, primary keys for optimal synchronization, and currently has no data definition language (DDL) statement support.
The integration only supports one Aurora PostgreSQL database per pipeline.
The existing pipeline configuration can’t be updated to ingest data from a different database and/or a different table. To update the database and/or table name of a pipeline, stop the pipeline and restart it with an updated configuration or create a new pipeline.
Ensure that the Amazon Aurora or Amazon RDS cluster has authentication enabled using AWS Secrets Manager, which is the only supported authentication mechanism.

Best practices

The following are some best practices to follow while setting up the integration with OpenSearch Service:

If a mapping template is not specified in OpenSearch, it automatically assigns field types using dynamic mapping based on the first document received. However, it is always recommended to define field types explicitly by creating a mapping template that suits your requirements.
To maintain data consistency, the primary and foreign keys of tables remain unchanged.
You can configure the dead-letter queues (DLQ) in your OpenSearch Ingestion pipeline. If you’ve configured the queue, OpenSearch Service sends all failed documents that can’t be ingested due to dynamic mapping failures to the queue.
Monitor recommended CloudWatch metrics to measure the performance of your ingestion pipeline.

Zero-ETL integration with Amazon DocumentDB

Amazon Document DB is a fully managed database service built for JSON data management at scale. It offers built-in text and vector search functionalities. By leveraging OpenSearch Service, you can execute search analytics, including features like fuzzy matching, synonym detection, cross-collection queries, and multilingual search capabilities on DocumentDB data.

The zero-ETL integration initiates the process with a full historical data extraction to OpenSearch using an ingestion pipeline. After the initial data load is completed, the pipelines read from Amazon DocumentDB change streams ensuring near real-time data consistency between the two systems. OpenSearch organizes the incoming data into indexes, with flexibility to either consolidate data from a DocumentDB collection into a single index or partition data across multiple indices. The ingestion pipelines synchronize all create, update, and delete operations from the DocumentDB collection, maintaining corresponding document modifications in OpenSearch. This ensures both data systems remain synchronised.

The pipelines offer configurable routing options, allowing data from a single collection to be written to one index or conditionally route to multiple indexes. Users can configure ingestion pipelines to stream data from Amazon DocumentDB to OpenSearch Service through three primary modes namely full load only, streaming change events without initial full load and full load followed by change streams. You can also monitor the state of ingestion pipelines in the OpenSearch service console. Additionally, you can use Amazon Cloudwatch to provide real-time metrics and logs and setting up alerts.

Pricing

There is no additional charge for using this feature apart from the cost of your existing underlying resources, including OpenSearch Service, OpenSearch Ingestion pipelines (OCUs), and Amazon DocumentDB. The integration performs an initial full load of Amazon DocumentDB data and continuously streams ongoing changes to OpenSearch Service using change streams. The change streams feature is disabled by default and does not incur any additional charges until the feature is enabled. Using change streams on a DocumentDB cluster incurs additional read and write input/output (I/O), as well as storage costs.

To learn more on pricing see the DocumentDB pricing page.

Considerations

The following are the limitations for the DocumentDB to OpenSearch Service integration:

Only one Amazon DocumentDB collection as the source per pipeline is supported.
Cross-region and cross-account data ingestion is not supported.
Amazon DocumentDB elastic clusters are not supported, only instance-based clusters are supported.
AWS Secrets Manager is the only supported authentication mechanism.
You can’t update an existing pipeline configuration to ingest data from a different database and/or a different collection. To update the database and/or collection name of a pipeline, create a new pipeline.

Best practices

The following are some best practices to follow while setting up the DocumentDB zero-ETL with OpenSearch Service:

Configure dead-letter queues (DLQ) to handle any failed document ingestion.
Configure AWS Secrets Manager and enable secrets rotation to provide the pipeline secure access.
If you’re using change streams in DocumentDB, it’s important to extend the retention period to up to 7 days. This ensures you don’t lose any data changes during the ingestion process.

To get started, see zero-ETL integration of Amazon DocumentDB with OpenSearch Service.

Benefits for Database Integrations

With zero-ETL integrations, you can use the powerful search and analytics features of OpenSearch Service directly on your latest database data. These include full-text search, fuzzy search, auto-complete, and vector search for machine learning (ML) workloads—enabling intelligent, real-time experiences that enhance your applications and improve user satisfaction. This integration uses change streams to automate the synchronisation of transactional data from Amazon Aurora, Amazon RDS, Amazon DynamoDB and Amazon DocumentDB to OpenSearch Service without manual intervention. Once the data is available in OpenSearch Service, you can perform real-time searches to quickly retrieve relevant results for your applications.This eliminates the need for manual Extract-Transform-Load (ETL) processes, reduces operational complexity, and accelerates time-to-insight for real-time dashboards, search, and analytics.

Conclusion

In this post, you learned that zero-ETL integrations represent a significant advancement in simplifying data analytics workflows and reducing operational complexity. As you’ve explored throughout this post, these integrations offer several advantages such as elimination of complex ETL pipelines and reduced infrastructure and operational costs by removing the need for intermediate storage and processing that enhance developer productivity.

It is time to accelerate your analytics journey with OpenSearch Service zero ETL – where your data flows seamlessly, eliminating complex pipelines and delivering real-time insights. Get started with Amazon OpenSearch Service or learn more about integrations with other services and applications in the AWS documentation.