AWS Big Data Blog

Discover and govern Snowflake data using SageMaker Unified Studio

Marco Duarte — Wed, 16 Sep 2026 15:19:51 +0000

Many organizations operate in hybrid data environments where critical assets live in Snowflake while analytics workloads run on AWS, which can create governance gaps, discovery friction, and duplicated efforts when the two aren’t connected.

With Amazon SageMaker Unified Studio, you can govern data across Snowflake and AWS through its integrated catalog and AWS Glue Data Quality, a capability of AWS Glue. You connect directly to Snowflake tables without moving data, apply quality rules using AWS Glue Visual ETL, and publish validated assets to Amazon SageMaker Catalog, maintaining consistent governance across your entire distributed data estate.

Without this integration, cataloging Snowflake data requires building extraction pipelines, often taking days. With SageMaker Unified Studio connected to Snowflake, you can query, catalog, and validate the quality of federated data in 5–15 minutes. No data replication or custom ETL code required.

In this post, we show you how to connect Snowflake to Amazon SageMaker Unified Studio, register data assets in Amazon SageMaker Catalog, configure data quality validation using AWS Glue Visual ETL, and publish assets for unified collaboration. By following these steps, you enrich federated assets with data quality scores so that consumers across your organization can discover and trust the data, all while keeping it in Snowflake.

Solution overview

This solution integrates Snowflake with Amazon SageMaker Unified Studio for centralized data cataloging and quality validation.

The architecture uses an AWS Glue connection to federate the Snowflake catalog into Amazon SageMaker Unified Studio. Tables become available in the project catalog without complex storage configurations. You can query data directly using SQL analytics, publish datasets to Amazon SageMaker Catalog for organization-wide discovery, and apply data quality rules through AWS Glue Visual ETL pipelines.

The workflow consists of the following steps:

Figure 1: Architecture for federating Snowflake into SageMaker Unified Studio and validating data quality

Snowflake connection creation on Amazon SageMaker Unified Studio — Amazon SageMaker Unified Studio uses an AWS Glue connection to federate Snowflake tables and views into its open data lakehouse architecture. The federated catalog entry is registered in AWS Glue Data Catalog and governed by AWS Lake Formation for centralized access control, without moving data out of Snowflake.
Federate Snowflake tables into the Amazon SageMaker publisher project — The Amazon SageMaker publisher project discovers the federated Snowflake tables through the AWS Glue Data Catalog integration.
Publish the dataset to Amazon SageMaker Catalog — The publisher project publishes the dataset as a governed asset to the Amazon SageMaker Catalog, making it discoverable for data consumers across the organization.
Validate data quality — AWS Glue Data Quality runs validation rules against the federated Snowflake data and publishes the data quality results directly to the corresponding asset in Amazon SageMaker Catalog.
Consume data — Users access Snowflake data through two paths:
1. Publisher project users — Query data with SQL Analytics — Users in the publisher project can query the Snowflake data directly using Amazon SageMaker Unified Studio SQL Analytics for interactive exploration and analysis, without copying or moving data.
2. Consumer project users — Discovery and subscription through SageMaker Catalog — Other Amazon SageMaker consumer projects discover the published asset in the Amazon SageMaker Catalog, subscribe to it, and consume the data for their analytics and machine learning workloads.

Prerequisites

To follow along, you need:

An active Snowflake account with administrator access.
Tables or views created within a schema inside a Snowflake database.
An Amazon SageMaker Unified Studio and project created.
An Amazon Simple Storage Service (Amazon S3) bucket for AWS Glue assets.
Appropriate AWS Identity and Access Management (IAM) permissions configured (Amazon SageMaker Catalog is built on Amazon DataZone, so the IAM actions use the datazone: prefix.)

Your AWS Glue job execution role requires specific permissions to interact with Amazon SageMaker Catalog.

Required IAM policies for the AWS Glue job role

1. Amazon SageMaker Catalog search and listing permissions: Attach a policy that allows the AWS Glue job to search and list assets in Amazon SageMaker Catalog.

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Action": [
        "datazone:SearchListings",
        "datazone:GetListing",
        "datazone:ListDomains",
        "datazone:GetDomain"
      ],
      "Resource": "arn:aws:datazone:<REGION>:<ACCOUNT_ID>:domain/<DOMAIN_ID>"
    }
  ]
}

2. Amazon SageMaker Catalog time series data posting permissions: Add permissions to post data quality metrics:

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Action": [
        "datazone:PostTimeSeriesDataPoints",
        "datazone:GetAsset",
        "datazone:ListAssetRevisions"
      ],
      "Resource": "arn:aws:datazone:<REGION>:<ACCOUNT_ID>:domain/<DOMAIN_ID>"
    }
  ]
}

Configure the AWS Glue job role as an Amazon SageMaker domain user

Configure the IAM role used by your AWS Glue job as a domain user. In the Amazon SageMaker console, navigate to your domain, choose Access management, and add the AWS Glue job execution IAM role as a domain user.

Project-level permissions

Add the AWS Glue job execution role as a project member with Owner permissions. Navigate to your project, go to Project settings > Members, and add the role.

For more information about IAM roles for AWS Glue, see the AWS Glue security documentation. For Amazon SageMaker Unified Studio permissions, refer to the Amazon SageMaker Unified Studio administrator guide.

Querying Snowflake datasets from Amazon SageMaker Unified Studio

The following sections walk you through connecting Snowflake to Amazon SageMaker Unified Studio and running data quality validation with results displayed in Amazon SageMaker Catalog.

Identifying information in Snowflake

First, gather your Snowflake connection details. You need a Snowflake account with tables or views created at the schema level within a database.

To obtain Snowflake connection information:

Navigate to your Snowflake environment and sign in with administrator credentials.
Choose your user account and choose Connect a tool to Snowflake.
Note the Account/Server URL displayed on the screen.
Choose the Config File tab, select values for Warehouse, Database, and Schema, and copy these values for use in the next section.

Creating the connection in Amazon SageMaker Unified Studio

The Add Connection feature stores Snowflake connectivity details including credentials, server, and database information. Amazon SageMaker Unified Studio uses this connection to federate the Snowflake catalog through AWS Glue, so you can query data within minutes of setup.

You need an Amazon SageMaker Unified Studio domain and a project, which acts as a data producer project.

To create the Snowflake connection:

In your Amazon SageMaker Unified Studio project, go to Overview.
Choose Data.
Choose + Add, then choose Add Connection.
Choose Next.
Select Snowflake and choose Next.
Complete the connection details:
- Name: snowflake-connection.
- Description (Optional): Enter a description for your connection.
- Host: Your Snowflake account URL (for example, XXXXXXXXX-XXX000000.snowflakecomputing.com).
- Port: 443.
- Database: Your database name (for example, sm_demo).
- Warehouse: Your warehouse name (for example, COMPUTE_WH).
- Schema: Your schema name (for example, demo).
- Additional Properties:
  - Register in AWS Glue Data Catalog: Turn on checkbox.
  - Case conflict handling: Select the option based on Snowflake naming syntax.
- Authentication:
  - Username: Your Snowflake username.
  - Password: Your Snowflake password.
Choose Add Data.

After creating the connection, wait a few minutes for the federated connection to be established. Search within Amazon SageMaker Unified Studio for the database and created objects.

With the Snowflake connection established and the federated tables registered in AWS Glue Catalog, you’re now ready to query Snowflake data directly from Amazon SageMaker Unified Studio, without moving or replicating any data.

How federated queries work

When you run a query in the Amazon SageMaker Unified Studio query editor against a federated Snowflake table, Amazon Athena runs the request. Athena is the underlying query engine integrated into Amazon SageMaker Unified Studio. Athena reads the table definition from AWS Glue Catalog, connects to Snowflake through the established connection, and pushes the query down for execution. Athena returns results directly to the query editor while Snowflake processes the data in place, and only the query results travel across the connection. Amazon SageMaker Unified Studio doesn’t copy data to S3 or any intermediate storage.

After you’ve validated that queries return the expected results, the next step is to publish this dataset to Amazon SageMaker Catalog, making it discoverable and shareable across your organization.

Publishing Snowflake datasets to the SageMaker Catalog

Now that your Snowflake connection is configured, you can publish your datasets to the Amazon SageMaker Catalog, making them discoverable and shareable across your organization.

Creating data assets in SageMaker Catalog

Data assets in Amazon SageMaker Catalog are the cataloged representation of your data resources. They help teams discover, govern, and share data across your organization.

In this section, you create a data asset associated with a Snowflake table. This process transforms a technical Snowflake table into a cataloged resource enriched with business metadata.

To create a data source:

In your Amazon SageMaker Unified Studio project, go to Manage.
Choose Data Sources.
Choose Create Data Source.
Select the AWS Glue option.
Turn on the Import data lineage checkbox and select the connection: project.default_lakehouse.
Complete the form and choose Next:
- Catalog: Select Enter the catalog name and enter snowflake-connection.
- Database name: Enter your database name (for example, movies).
- Table selection criteria: Enter * for all tables in the database, or enter a specific table name.
Keep the default options and choose Next until you reach the summary screen.
Review your settings and choose Create.

To extract metadata and publish assets:

Choose Run to start extracting metadata from AWS Glue Data Catalog.
Wait for the run to complete.
Go to Assets to view the Asset Inventory.

The following screenshot shows the asset inventory after the data source run completes.

Choose an asset to view its details.

At this point, you can enrich the business context by choosing Generate Descriptions. Amazon SageMaker Catalog analyzes the asset’s technical structure and generate:

Business descriptions in natural language for the asset.
Contextual definitions for each field/column.
Suggested glossary terms that could be applied.

After your asset has been enriched with the necessary business metadata, you can publish it to the Amazon SageMaker Catalog by choosing Publish Asset.

The Snowflake enriched asset is now available to data consumers across your organization. Other users can discover it, subscribe to it, and consume it without data replication.

Implementing data quality rules with AWS Glue Data Quality

This section explains how to apply data quality validations to Snowflake data using AWS Glue Data Quality and visualize results in Amazon SageMaker Catalog.

Setting up the custom transform

Upload two files to an Amazon S3 bucket in the same AWS account where you run AWS Glue:

post_dq_results_to_datazone.py – Contains the transform logic that reads data quality rule outcomes and posts them to SageMaker Catalog.
post_dq_results_to_datazone.json – Defines the transform configuration that AWS Glue Studio uses to render the visual node.

Copy both files to your AWS Glue assets S3 bucket in the transforms folder (s3://aws-glue-assets-<account-id>-<region>/transforms). AWS Glue Studio reads all JSON files from this folder to register custom visual transforms.

In the following sections, we walk you through the steps of building an ETL pipeline for data quality validation using AWS Glue Studio.

Creating the AWS Glue Visual ETL job

AWS Glue for Spark provides built-in support for reading from Snowflake data sources.

To create a new visual ETL job:

Open the AWS Glue console at https://console.aws.amazon.com/glue/. Choose ETL jobs, then Visual ETL.

Establishing the Snowflake connection

To add a Snowflake source:

In the job pane, choose Snowflake as your source. For Snowflake connection, select the connection that you created earlier. Specify the relevant schema and table for data quality checks.

The visual editor displays the Data source properties panel where you select your connection, database, and enter a custom query targeting your Snowflake table.

Applying data quality rules

After establishing the Snowflake connection, configure the data quality evaluation step using the Data Quality Definition Language (DQDL).

To add data quality validation:

Choose Transform and choose Evaluate Data Quality.
Define domain-specific data quality rules using DQDL. For more information, see the AWS DQDL documentation.
Choose to output the data quality results. Optionally, store outcomes in Amazon S3 or publish to Amazon CloudWatch with alert notifications.

The preview of the data quality results from the ruleOutcomes node shows the outcomes of each rule.

Post the data quality results to Amazon SageMaker Catalog

To configure the custom transform:

Add the Datazone DQ Result Sink transform to your job.
Connect the ruleOutcomes node output to this transform.
Complete the parameters:
- Role to assume (Optional): Only needed for associated accounts.
- Domain ID: Your Amazon SageMaker Unified Studio domain ID (found in the Amazon SageMaker Unified Studio portal).
- Table name and Schema name: Same values used when creating the Snowflake source transform.
- Data quality ruleset name: The name you want to give to the ruleset in Amazon SageMaker Catalog.
- Max results: Maximum number of assets to return in case of multiple matches.

The following image shows the complete job graph with the Datazone DQ Result Sink transform configured.

The visual editor displays four nodes connected sequentially: the Snowflake data source, the Evaluate Data Quality transform, the ruleOutcomes SelectFromCollection transform, and the Datazone DQ Result Sink transform.

To configure job parameters:

Choose Job details.
In Job parameters, add the following key-value pair:
- --additional-python-modules
- boto3>=1.34.105
Save and run the job.

Visualizing data quality results in the SageMaker Catalog

After the AWS Glue ETL job completes, you can view the data quality information directly in Amazon SageMaker Catalog. This is the key outcome of running data quality on a federated source: the asset gains quality scores and metadata without ever leaving Snowflake. This makes it trustworthy and ready for other teams across your organization to use. Data consumers can now discover this asset in Amazon SageMaker Catalog and evaluate its quality before subscribing, without needing direct access to Snowflake or running their own validation.

To view data quality results:

Open the Amazon SageMaker Unified Studio console.
Navigate to your project.
Go to Assets.
Choose the Snowflake data asset.
View the data quality information displayed on the asset page.

The following image shows the asset page in Amazon SageMaker Catalog with the data quality score populated.

The Data Quality tab shows an overall score of 100 and lists the rule set movies with a Passed result (1/1). This confirms that the data quality checks from AWS Glue posted successfully to Amazon SageMaker Catalog.

Clean up

To avoid ongoing charges, remove the resources you created during this walkthrough:

Delete the AWS Glue ETL job — Open the AWS Glue console, choose ETL jobs, select your job, and then choose Delete.
Remove the AWS Glue connection — In the AWS Glue console, go to Connections, select the Snowflake connection, and then choose Delete.
Delete the data source in SageMaker Catalog — In your Amazon SageMaker Unified Studio project, go to Data Sources, select the data source you created, and then choose Delete.
Remove S3 assets — Delete the custom transform files from your s3://aws-glue-assets-<account-id>-<region>/transforms/ bucket.
Remove IAM policies — Detach and delete the IAM policies you attached to the AWS Glue job execution role. Remove the role as a domain user and project member.

Conclusion

In this post, we showed you how to connect Snowflake to Amazon SageMaker Unified Studio for centralized data cataloging and quality validation. This approach maintains consistent governance without replicating data. Key benefits include:

Query without data movement: Access Snowflake data directly from Amazon SageMaker Unified Studio through federated queries, using the interoperable data architecture of AWS and eliminating time-consuming data replication.
Centralized governance: Maintain a single source of truth for data discovery, quality metrics, and governance policies across your distributed data estate.
Automated quality validation: Apply consistent data quality rules using AWS Glue Data Quality and visualize results directly in Amazon SageMaker Catalog.
Unified collaboration: Support data discovery and sharing across your organization through the publishing capabilities of Amazon SageMaker Catalog.

To get started, open the Amazon SageMaker Unified Studio console. To learn more about related topics, see Cross-account lakehouse governance with Amazon S3 Tables and SageMaker Catalog and Get started with AWS Glue Data Quality dynamic rules for ETL pipelines.

About the authors

Connect Amazon SageMaker Unified Studio to Microsoft Power BI – Part 1: IAM Identity Center (IDC)-based domains

Ramesh H Singh — Tue, 15 Sep 2026 20:55:26 +0000

Connecting Power BI to your Amazon SageMaker Unified Studio data catalogs typically required third-party bridges. These bridges added complexity and licensing costs. In this post, you create a direct connection using new authentication modes in the Amazon Athena ODBC driver, removing those dependencies entirely. If your organization uses Power BI as its business intelligence (BI) tool, your analysts can configure access to governed data in Amazon SageMaker Unified Studio without changing their tools or workflows. As an AWS alternative, Amazon Quick Sight provides serverless BI integration with Amazon SageMaker Unified Studio at pay-per-session pricing.

A previous post showed the connection method using a third-party ODBC-JDBC bridge. The Amazon Athena ODBC driver (version 2.2.0 and later) now supports Amazon SageMaker Unified Studio authentication directly, eliminating the need for customers to configure third-party bridge components previously required for this connection. This bridge also created additional components and required ongoing maintenance. The native connection simplifies the architecture by reducing these requirements.

Customer Spotlight

UC Irvine, a top-ten U.S. public research university, consolidates student data from systems across multiple departments into a single governed repository that supports reporting, research, and analytics for decision-making at the strategic, tactical, and operational levels. Many of their analysts rely on Power BI to explore and visualize this governed data.

“Our users rely on Power BI for data visualization and reporting, but connecting to governed data in AWS previously required workarounds. The ODBC connection feature gives a direct path from Power BI into our SageMaker Unified Studio projects—no bridge software, no extra licensing, just a connection string and we’re ready to go.”

— Bernadette Theologidy, Manager, Student Analytics, UC Irvine

The Athena ODBC driver introduces two new authentication modes for SageMaker Unified Studio:

SageMakerBrowserIdc (for IDC-based domains): The driver opens a browser window and authenticates through AWS IAM Identity Center (and your external identity provider, if configured). No local AWS credentials are needed.
SageMakerIam (for AWS Identity and Access Management (IAM)-based and IDC-based domains): The driver uses AWS credentials from the default credential provider chain. For this walkthrough, we use AWS IAM Identity Center to provide those credentials.

You connect Microsoft Power BI to Amazon SageMaker Unified Studio through Athena. The Athena ODBC driver supports using two connection methods that use these authentication modes:

Method 1: DSN-based (Athena Power BI connector): You configure an ODBC Data Source Name (DSN) and use the Athena connector in Power BI. This method supports DirectQuery and Import mode with both SageMakerBrowserIdc and SageMakerIam authentication.

Method 2: DSN-less (Power BI ODBC connector): You use the Power BI ODBC connector with a connection string, requiring no DSN configuration. This method supports Import mode only with SageMakerIam authentication. DirectQuery isn’t available because the Power BI ODBC connector doesn’t support it. The connection string in Power BI Desktop must match exactly the one on Power BI Service. Because the gateway runs as a Windows service without interactive browser access, both ends must use SageMakerIam.

Feature	Method 1: DSN-based	Method 2: DSN-less
Power BI Connector	Amazon Athena connector	ODBC connector
Data connectivity mode	DirectQuery and Import	Import only
Requires DSN configuration	Yes	No
Data freshness	Real-time (DirectQuery) or scheduled (Import)	Scheduled refresh only
Authentication types	SageMakerIam and SageMakerBrowserIdc	SageMakerIam only
Domain types supported	IAM-based and IDC-based	IAM-based and IDC-based
Best for	Dashboards requiring live data	Scenarios where DSN management is not possible or scheduled refresh is acceptable

This is Part 1 of a two-part series. This post covers IDC-based domains using both connection methods. Part 2 covers IAM-based domains.

Solution overview

In this walkthrough, you take the role of a data analyst at an energy company. You need to understand the current state and future direction of the U.S. power generation fleet using the Public Utility Data Liberation Project, available on the Registry of Open Data on AWS. Our goal is to analyze generation capacity and identify where new investment is flowing. We connect Power BI to Athena through Amazon SageMaker Unified Studio and query the EIA-860 generators dataset directly from our data catalog. The result is a single visualization that reveals the energy transition.

The following diagram illustrates the solution architecture for connecting Power BI to Amazon SageMaker Unified Studio through Amazon Athena.

Figure 1: Architecture diagram

The following architecture demonstrates a six-step workflow.

Data engineers and analysts connect Power BI Desktop to Athena as a data source.
They build their reports locally.
They then publish them to the Power BI Service.
Microsoft On-Premises Data Gateway on an Amazon Elastic Compute Cloud (Amazon EC2) instance connects to Athena using the instance’s attached IAM role.
The Power BI Service then uses this gateway connection.
Report viewers access the published reports through Power BI Service to make data-driven decisions.

On the AWS side, Athena queries the data catalog managed by AWS Glue Data Catalog. The catalog references data stored in Amazon Simple Storage Service (Amazon S3). An Amazon SageMaker Unified Studio project governs all access.

In an IDC-based domain (covered in this post), Power BI Desktop uses SageMakerBrowserIdc for Method 1 and SageMakerIam for Method 2. Power BI Desktop can run on-premises or on an EC2 instance. The gateway always uses SageMakerIam (it runs as a Windows service without browser access) and authenticates using instance profile credentials, which rotate automatically. The gateway can only query data within projects where its IAM role has been added as a member. For IAM-based domains, see Part 2.

Prerequisites

Before connecting Power BI to Amazon SageMaker Unified Studio, verify that your environment meets these requirements:

Athena ODBC driver – The latest Amazon Athena ODBC driver (version 2.2.0 or more recent) for Windows 64-bit.
Microsoft Power BI Desktop – The latest version installed on your Windows machine.
Microsoft Power BI Pro License – Required for publishing reports and configuring the on-premises data gateway.
Microsoft Power BI on-premises data gateway – The latest version installed on the EC2 instance.
Amazon SageMaker Unified Studio – An Amazon SageMaker Unified Studio IDC-based domain.

You need an Amazon SageMaker Unified Studio project with data assets. For detailed instructions, refer to the Amazon SageMaker Unified Studio User Guide.

The following screenshot shows the Amazon SageMaker Unified Studio project Query Editor interface, which runs a preview query against the EIA-860 generators dataset.

Figure 2: SageMaker Unified Studio project with the EIA-860 generators dataset available in the data catalog

Method 1: DSN-based connection (Athena Power BI connector)

This method uses the Amazon Athena Power BI connector with an ODBC Data Source Name (DSN), supporting DirectQuery and Import mode.

You configure Power BI Desktop to connect to your data assets in Amazon SageMaker Unified Studio using the SageMakerBrowserIdc authentication mode. The driver opens a browser window and authenticates through IAM Identity Center (and your external identity provider, if configured).

Add your SSO user as a member of your SageMaker Unified Studio project

Your single sign-on (SSO) user needs project-level access to query data with Athena. Verify your user is listed as a project member or add it by following Add project members in the Amazon SageMaker Unified Studio User Guide.

The following screenshot shows the SageMaker Unified Studio project user management page, where project owners can add or remove project users and roles.

Figure 3: Members of a SageMaker Unified Studio project

Gather configuration values to configure your Amazon Athena ODBC DSN

Gather the following values from your Amazon SageMaker Unified Studio project:

Open your Amazon SageMaker Unified Studio project.
In the top right, select the three dots.
Choose Project details.
Select JDBC and ODBC details.
Under ODBC connection details copy the following information: IDC issuer URL, domain ID, project ID, Athena workgroup name and AWS Region.

The following screenshot shows the Amazon SageMaker Unified Studio project overview page, where you can copy these details.

Figure 4: ODBC connection details

Configure the ODBC DSN

Create a System DSN using the Amazon Athena ODBC driver. For the general DSN creation steps, see Configuring a data source name on Windows in the Amazon Athena User Guide.

Enter the following values:

Field	Value
Data Source Name	Name your datasource (for example, `pbi-idcdomain`)
Region	The AWS Region where your Amazon SageMaker domain is provisioned (for example, `us-east-1`)
Catalog	`AwsDataCatalog`
Database	`default`
Workgroup	Your Athena workgroup name (for example, `workgroup-abcdefghij-klmexample`)

In the Authentication Options, configure the following values:

Field	Value
Authentication Type	`SageMakerBrowserIdc`
SSO Start URL	IAM Identity Center entry point (for example, `https://identitycenter.amazonaws.com/ssoins-0example`)
SSO Region	Region of IAM Identity Center (for example, `us-east-1`)
SageMaker Domain ID	`dzd-123456example`
SageMaker Project ID	`abcd12example`
SageMaker Domain Region	Region of your Amazon SageMaker Unified Studio project (for example, `us-east-1`)

Choose OK, then Test to verify the connection. Choose Allow Access when prompted by the browser.

The following screenshot shows the consent prompt.

Figure 5: Browser consent prompt

The following screenshot shows the successful connection test.

Figure 6: Successful connection test in the ODBC DSN configuration with SageMakerBrowserIdc authentication

Connect Power BI Desktop to your data

With the DSN configured, you can connect Power BI Desktop to your data catalog and load the generators dataset.

Open Power BI Desktop.
Open the Get Data menu and select More.
Search for and select Amazon Athena and choose Connect.
For Data Source Name (DSN), enter pbi-idcdomain.
Select DirectQuery.
Choose OK.
Choose Use Data Source Configuration and then Connect.
In the AwsDataCatalog folder, navigate to your database.
Select the core_eia860__scd_generators table.
Choose Load.

The following screenshot shows Power BI Desktop successfully connected to the AWS data catalog.

Figure 7: Power BI Desktop connected to the data catalog with the generators table loaded using SageMakerBrowserIdc authentication

Create your dashboard and publish it

You can create a dashboard to visualize U.S. power generation data. To create a visualization, complete the following steps:

In the Visualizations pane, choose the Stacked bar chart.
Assign the Y-Axis: Drag technology_description to the Y-Axis.
Assign the X-Axis (Values): Drag capacity_mw to the X-Axis (automatically summed).
Assign the Legend (Stack): Drag operational_status to the Legend field.
Choose Publish.
Give your report a name (for example, generation-idcdomain) and choose Save.
Sign in and choose a destination workspace.

Figure 8: Power BI Desktop report using the EIA-860 generators dataset

After publishing, the report structure is available on Power BI Service.

Method 2: DSN-less connection (Power BI ODBC connector)

In this method, you use the Power BI ODBC connector with a connection string (no DSN required). This method supports Import mode only and SageMakerIam authentication. Because the gateway cannot perform browser authentication, both Desktop and gateway must use SageMakerIam. If your workflow requires SageMakerBrowserIdc, use Method 1.

If your machine already has AWS credentials through another method in the default credential provider chain, skip the following setup.

Administrator setup

Create a custom permission set named SageMakerDataAnalyst in IAM Identity Center with the following inline policy. For detailed steps, see Create a permission set in the AWS IAM Identity Center User Guide.

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "SageMakerAccess",
            "Effect": "Allow",
            "Action": [
                "datazone:GetConnection",
                "datazone:ListConnections",
                "datazone:GetDomain",
                "datazone:GetProject"
            ],
            "Resource": "*"
        },
        {
            "Sid": "STSForDriver",
            "Effect": "Allow",
            "Action": [
                "sts:GetCallerIdentity"
            ],
            "Resource": "*"
        }
    ]
}

Assign your user to this permission set for the AWS account containing your SageMaker Unified Studio domain. Then configure your AWS Command Line Interface (AWS CLI) SSO profile by running aws configure sso. For the full CLI configuration walkthrough with detailed steps, see Part 2. After your profile is configured, run aws sso login to authenticate.

Add the IAM identity as a member of SageMaker Unified Studio project

The IAM identity providing credentials needs both domain-level and project-level access to query data through Athena.

Add AWSReservedSSO_SageMakerDataAnalyst_1234example as a domain IAM user: see Managing users in the Amazon SageMaker Unified Studio Admin Guide. Choose Current account.

Figure 9: List of users of your SageMaker Unified Studio domain including the IAM identity

Add AWSReservedSSO_SageMakerDataAnalyst_1234example as a project member: see Add project members in the Amazon SageMaker Unified Studio User Guide.

Figure 10: Members of a SageMaker Unified Studio project including the IAM identity

Gather configuration values

Gather the following connection values from your Amazon SageMaker Unified Studio project:

Open your Amazon SageMaker Unified Studio Project.
On the navigation pane, choose Overview.
Select JDBC and ODBC details.
Select the Using IAM auth toggle.
Copy the ODBC connection string.

Figure 11: ODBC connection string on the SageMaker Unified Studio project overview

Connect Power BI Desktop to your data and publish

With the configuration parameters of your project, you can connect Power BI Desktop to your data catalog and load the generators dataset.

Open Power BI Desktop.
Open the Get Data menu and select More.
Search for and select ODBC and choose Connect.
For Data Source Name (DSN), select (None).
Expand Advanced Options.
In the Connection string field, enter your connection string. For example, Driver={Amazon Athena ODBC (x64)};AwsRegion=us-east-1;Catalog=AwsDataCatalog;Schema=default;Workgroup=workgroup-abcdefghij-klmexample;SageMakerDomainId= dzd-123456example;SageMakerProjectId= abcd12example;SageMakerDomainRegion=us-east-1;AuthenticationType=SageMakerIam;
Choose OK.
Choose Default or Custom and then Connect.
In the AwsDataCatalog folder, navigate to your database.
Select the core_eia860__scd_generators table.
Choose Load.

When publishing, name your report generation-idcdomain-dsnless.

Configure the on-premises data gateway and view your report on Power BI Service

After creating your reports in Power BI Desktop, configure the on-premises data gateway to view your report on Power BI Service.

You can configure the gateway using either a DSN or a DSN-less connection string, matching the method you used in Power BI Desktop.

Create and attach an IAM role to the Power BI Gateway EC2 instance

Create an IAM role for the EC2 instance that will host your Power BI gateway. Name the role pbi-gateway-role (or a name of your choice). The role must use EC2 as the trusted entity and include the following inline policy:

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "SageMakerAccess",
            "Effect": "Allow",
            "Action": [
                "datazone:GetConnection",
                "datazone:ListConnections",
                "datazone:GetDomain",
                "datazone:GetProject"
            ],
            "Resource": "*"
        },
        {
            "Sid": "STSForDriver",
            "Effect": "Allow",
            "Action": [
                "sts:GetCallerIdentity"
            ],
            "Resource": "*"
        }
    ]
}

Attach this role to your Power BI Gateway EC2 instance. For detailed steps on creating and attaching an IAM role to an EC2 instance, refer to IAM roles for Amazon EC2 in the Amazon EC2 User Guide.

Add the Power BI Gateway IAM role as a member of SageMaker Unified Studio project

The gateway IAM role needs project-level access to query data through Athena.

Add the IAM pbi-gateway-role role as a domain IAM user: see Managing users in the Amazon SageMaker Unified Studio Admin Guide. Choose Current account (or Associated account if your gateway is deployed in a different account).

The following screenshot, from the Amazon SageMaker page of the AWS Management Console, shows the list of users of your Amazon SageMaker Unified Studio domain, including the IAM gateway role.

Figure 12: List of users of your SageMaker Unified Studio domain including the IAM gateway role

Add the IAM pbi-gateway-role role as a project member: see Add project members in the Amazon SageMaker Unified Studio User Guide.

The following screenshot shows the Amazon SageMaker Unified Studio project user management page listing the project members.

Figure 13: Members of a SageMaker Unified Studio project including the IAM gateway role

Configure the data source on Power BI Gateway

How you configure the data source depends on the method you used in Power BI Desktop.

Method 1 (DSN-based)

Configure a System DSN on the gateway EC2 instance following the same ODBC DSN steps described in Method 1. When configuring, make sure that:

You use the System DSN tab (not User DSN) because the gateway runs as a Windows service under a separate account.
The authentication type is set to SageMakerIam regardless of what you used on Desktop.
The DSN name matches exactly the one configured on Power BI Desktop (for example, pbi-idcdomain)

Method 2 (DSN-less)

No configuration is needed on the gateway machine itself. You configure the data source directly in Power BI Service.

Configure the data source and view your report on Power BI Service

To view your report, complete the following steps:

Open the workspace where you saved your report.
Search the Semantic Model which has the same name as your report (for example, generation-idcdomain) and choose the More options icon (three dots).
Choose Settings.
Expand Gateway and Cloud Connection.
Choose View Datasources (play icon) on your gateway.
Choose Manually add to gateway.
Add a connection name (for example, pbi-idcdomain).

The next step depends on the method that you chose:

Method 1 (DSN-based)

Add the DSN (for example, pbi-idcdomain) that matches exactly the one configured on Power BI Desktop.

Method 2 (DSN-less)

In the Connection string field, enter the connection string that matches exactly the one used in Power BI Desktop.

Next, continue with the configuration:

Select Anonymous as Authentication Method.
Choose Create.
Expand again Gateway and Cloud Connection.
For Maps to, choose the connection that you created (for example, pbi-idcdomain).
Choose Apply.
Return to the workspace where you saved your report.
On the Content section, choose your report (for example, generation-idcdomain).

The following screenshot shows a Power BI report on Power BI Service.

Figure 14: Power BI report on Power BI Service

You can now see your report online with the data from your Amazon SageMaker Unified Studio project.

Clean up

To avoid additional charges after testing, delete the Amazon SageMaker Unified Studio domain and EC2 instances. Refer to Delete domains and Terminate Instances for instructions.

Conclusion

In this post, you connected Microsoft Power BI to Amazon SageMaker Unified Studio using an IDC-based domain with both DSN-based and DSN-less methods. This provides a direct connection, with no third-party licensing, that maintains data governance. In Part 2, we cover IAM-based domains.

You can automate many steps of this process. For information about automating DSN creation on the Power BI Gateway or Service, refer to How ENGIE automates the deployment of Amazon Athena data sources on Microsoft Power BI. If you don’t want users adding the gateway IAM role directly, you can create a custom blueprint as a self-service tool for gateway role addition. The blueprint uses a ProjectMembership resource with a configurable parameter that project owners can activate at project creation, automatically adding the gateway role as a project contributor.

For additional best practices, refer to the Using Microsoft Power BI with the AWS Cloud Whitepaper. To learn more, visit Amazon SageMaker Unified Studio and Amazon Athena.

About the authors

Connect Amazon SageMaker Unified Studio to Microsoft Power BI – Part 2: IAM-based domains

Ramesh H Singh — Tue, 15 Sep 2026 20:55:16 +0000

In Part 1 of this series, we connected Microsoft Power BI to Amazon SageMaker Unified Studio using an IAM Identity Center (IDC)-based domain. The Amazon Athena ODBC driver (version 2.2.0 and later) supports Amazon SageMaker Unified Studio authentication natively, removing the third-party ODBC-JDBC bridge previously required. We walked through both the DSN-based connection and the DSN-less connection, from Power BI Desktop through the on-premises data gateway to Power BI Service, where report viewers access published dashboards.

In this post, you create the same direct connection using an AWS Identity and Access Management (IAM)-based domain. The walkthrough covers the same two connection methods. The differences are the Amazon SageMaker Unified Studio console navigation paths, the configuration values, and an additional administrator setup that provides AWS credentials through AWS IAM Identity Center. This is Part 2 of a two-part series. For a detailed comparison of the two connection methods, see Part 1.

Customer Spotlight

“Our users rely on Power BI for data visualization and reporting, but connecting to governed data in AWS previously required workarounds. The ODBC connection feature gives a direct path from Power BI into our SageMaker Unified Studio projects—no bridge software, no extra licensing, just a connection string and we’re ready to go.”

— Bernadette Theologidy, Manager, Student Analytics, UC Irvine

Solution overview

The architecture is the same as the previous post (see the architecture diagram and walkthrough scenario in Part 1). Power BI Desktop connects to Amazon Athena through the ODBC driver and the Amazon SageMaker Unified Studio project governs all data access. At the same time, the on-premises data gateway on an Amazon Elastic Compute Cloud (Amazon EC2) instance bridges the connection to Power BI Service so report viewers can access published dashboards.

The difference is in authentication: An IAM-based domain uses SageMakerIam authentication for both connection methods. The driver retrieves credentials from the AWS default credential provider chain. For this walkthrough, AWS IAM Identity Center provides those credentials through a custom permission set. Power BI Desktop can run on-premises or on an EC2 instance in the AWS Cloud. The gateway EC2 instance authenticates using its attached IAM role.

Prerequisites

Complete the prerequisites from Part 1. Additionally, you need:

AWS Command Line Interface (AWS CLI) – The latest version of the AWS CLI installed on your Windows machine. In this post series, the ODBC driver uses the AWS IAM Identity Center profile configured through the CLI for authentication.
Amazon SageMaker Unified Studio – An Amazon SageMaker Unified Studio IAM-based domain with AWS IAM Identity Center single sign-on (SSO) enabled.

The following screenshot shows the Amazon SageMaker Unified Studio (IAM-based domain) project Query Editor interface. It runs a preview query on the EIA-860 generators dataset.

Figure 1: SageMaker Unified Studio (IAM-based domain) project with the EIA-860 generators dataset available in the data catalog

Administrator setup

This section configures AWS IAM Identity Center to provide credentials for the SageMakerIam authentication mode. It applies to Method 1 (IAM-based domain) and Method 2 (both domain types). If your machine already has AWS credentials available through another method in the default credential provider chain, you can skip this section and proceed directly to the method of your choice. For the full list of credential sources, refer to Credential providers in the AWS SDKs and Tools Reference Guide.

Create a permission set in IAM Identity Center

Create a custom permission set named SageMakerDataAnalyst in IAM Identity Center with the following inline policy. For detailed steps, see Create a permission set in the AWS IAM Identity Center User Guide.

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "SageMakerAccess",
            "Effect": "Allow",
            "Action": [
                "datazone:GetConnection",
                "datazone:ListConnections",
                "datazone:GetDomain",
                "datazone:GetProject"
            ],
            "Resource": "*"
        },
        {
            "Sid": "STSForDriver",
            "Effect": "Allow",
            "Action": [
                "sts:GetCallerIdentity"
            ],
            "Resource": "*"
        }
    ]
}

The "Resource": "*" is required because these API actions do not support resource-level permissions. For more information, see Actions, resources, and condition keys for Amazon DataZone.

This doesn’t grant broad access to your data. These are read-only metadata actions that allow the ODBC driver to discover connection details and retrieve temporary Athena credentials. The actual data access is governed by Amazon SageMaker Unified Studio project membership: Users can only query data within projects where they have been explicitly added as members. The Amazon SageMaker Unified Studio project IAM role provides Athena and Amazon S3 permissions separately.

Assign users to the permission set

To assign users or groups to the target AWS account, complete the following steps:

In the IAM Identity Center console, choose AWS accounts.
Select the target account where your Amazon SageMaker Unified Studio IAM-based domain is deployed.
Choose Assign users or groups.
Select the SSO users or groups that need access.
Select the SageMakerDataAnalyst permission set.
Choose Submit.

Configure AWS IAM Identity Center profile

To configure the AWS IAM Identity Center profile, run the following command in your terminal on Windows:

aws configure sso

When prompted, enter the following values:

Prompt	Value
SSO session name	For example, `smus`
SSO start URL	The IDC issuer URL. For example, `https://identitycenter.amazonaws.com/ssoins-0example`
SSO region	The SSO Region. For example, `us-east-1`
SSO registration scopes	`sso:account:access`

A browser window opens for authentication. After authentication, select your account and the SageMakerDataAnalyst role.

The following screenshots show the consent window and the successful authentication message.

Figure 2: Browser consent prompt

Figure 3: Browser authentication successful message

When prompted, enter the following values:

Prompt	Value
Default client Region	None
CLI default output format	None
Profile Name	Change value by default

The resulting ~/.aws/config file should look like the following:

[default]
sso_session = smus
sso_account_id = 1234example
sso_role_name = SageMakerDataAnalyst

[sso-session smus]
sso_start_url = https://identitycenter.amazonaws.com/ssoins-0example
sso_region = us-east-1
sso_registration_scopes = sso:account:access

Verify authentication and daily use

To verify that your SSO profile is working correctly, run the following command:

aws sts get-caller-identity

You should receive a response like the following:

{
    "UserId": "AROARHJJNFBQD6EXAMPLE:user@example-domain.com",
    "Account": "111122223333",
    "Arn": "arn:aws:sts::111122223333:assumed-role/AWSReservedSSO_SageMakerDataAnalyst_1234example/user@example-domain.com"
}

For daily use, no passwords or EC2 instance roles are required. When your SSO session expires, run the following command to quickly refresh it:

aws sso login

Add your IAM identity as a member of your Amazon SageMaker Unified Studio project

The IAM identity providing credentials to the ODBC driver needs project-level access to query data through Athena. If you completed the administrator setup, this is the SSO role associated with your permission set (for example, AWSReservedSSO_SageMakerDataAnalyst_1234example). If you’re using another credential source, add the IAM role or user that provides those credentials. For detailed steps, see Managing users for IAM-based domains in the Amazon SageMaker Unified Studio Administrator Guide.

The following screenshot shows the Amazon SageMaker Unified Studio domain management page, which lists the members in a project.

Figure 4: List of members of your SageMaker Unified Studio project

Gather the information to authenticate

To get the parameters that you need to authenticate, complete these steps:

Open your Amazon SageMaker Unified Studio Project.
Open Domain Management.
Choose Users.
Choose View SSO connection.
Copy the end of the Instance ARN, so we can build the Instance URL like https://identitycenter.amazonaws.com/ssoins-0example

The following screenshot shows the Amazon SageMaker Unified Studio domain management page with SSO connection details.

Figure 5: AWS IAM Identity Center information

Choose the user icon and copy the Region as shown in the following screenshot.

Figure 6: User icon with the Region information

Method 1: DSN-based connection (Athena Power BI connector)

In this method, you configure an ODBC Data Source Name (DSN) and use the Amazon Athena connector in Power BI. This method uses SageMakerIam authentication mode and supports both DirectQuery and Import mode.

This section covers IAM-based domains. For IDC-based domains, see Part 1.

Gather configuration values to configure your Amazon Athena ODBC DSN

Before configuring the ODBC DSN, gather the following connection values from your Amazon SageMaker Unified Studio project:

Open your Amazon SageMaker Unified Studio Project.
Top right, select the three dots.
Choose Project details.
Select JDBC and ODBC details.
Copy the following values: domain ID, Amazon SageMaker project ID, AWS Region, and Athena workgroup.

The following screenshot shows the Amazon SageMaker Unified Studio project overview page, which provides the project details to copy.

Figure 7: Project details with SageMaker domain ID, SageMaker project ID, Region, and Athena workgroup

Configure the ODBC DSN

Create a System DSN using the Amazon Athena ODBC driver. For the general DSN creation steps, see Configuring a data source name on Windows in the Amazon Athena User Guide. Enter the following values:

Field	Value
Data Source Name	Name your datasource (for example, `pbi-iamdomain`)
Region	The AWS Region where your Amazon SageMaker domain is provisioned (for example, `us-east-1`)
Catalog	`AwsDataCatalog`
Database	`default`
Workgroup	Your Athena workgroup name (for example, `workgroup-abcdefghij-klmexample`)

In the Authentication Options, configure the following values:

Field	Value
Authentication Type	`SageMakerIam`
SageMaker Domain ID	`dzd-123456example`
SageMaker Project ID	`abcd12example`
SageMaker Region	Region of your SageMaker Unified Studio project (for example, `us-east-1`)

Choose OK, then Test to verify the connection. Choose Allow Access when prompted by the browser.

The following screenshot shows the successful connection test.

Figure 8: Successful connection test in the ODBC DSN configuration with SageMakerIam authentication

Connect Power BI Desktop to your data

With the DSN configured, you can connect Power BI Desktop to your data catalog and load the generators dataset.

Open Microsoft Power BI Desktop.
Open the Get Data menu and select More.
Search for and select Amazon Athena and choose Connect.
For Data Source Name (DSN), enter pbi-iamdomain.
Select DirectQuery.
Choose OK.
Choose Use Data Source Configuration and then Connect.
In the AwsDataCatalog folder, navigate to your database.
Select the core_eia860__scd_generators table.
Choose Load.

The following screenshot shows Power BI Desktop successfully connected to the data catalog.

Figure 9: Power BI Desktop connected to the data catalog with the generators table loaded using SageMakerIam authentication

Create your dashboard and publish it

You can create a dashboard to visualize U.S. power generation data. To create a visualization, complete the following steps:

In the Visualizations pane, choose the Stacked bar chart.
Assign the Y-Axis: Drag technology_description to the Y-Axis.
Assign the X-Axis (Values): Drag capacity_mw to the X-Axis (automatically summed).
Assign the Legend (Stack): Drag operational_status to the Legend field.
Choose Publish.
Give your report a name (for example, generation-iamdomain) and choose Save.
Sign in and choose a destination workspace.

The following screenshot shows the Power BI dashboard with U.S. power generation data.

Figure 10: Power BI dashboard with U.S. power generation data

After you publish, the report structure becomes available on Microsoft Power BI Service.

Method 2: DSN-less connection (Power BI ODBC connector)

In this method, you use the Power BI ODBC connector with a connection string (no DSN required). This method supports Import mode only and SageMakerIam authentication. Because the gateway can’t perform browser authentication and connection strings need to match, both Desktop and gateway must use SageMakerIam.

This section covers IAM-based domains. For IDC-based domains, see Part 1.

Gather configuration values to configure your DSN-less connection

Gather the following connection values from your Amazon SageMaker Unified Studio project:

Open your Amazon SageMaker Unified Studio Project.
Top right, select the three dots.
Choose Project details.
Select JDBC and ODBC details.
Copy the ODBC connection string.

The following screenshot shows the Amazon SageMaker Unified Studio project overview page with the ODBC connection string to copy.

Figure 11: Project details with ODBC connection string

Connect Power BI Desktop to your data and publish

With the configuration parameters of your project, you can connect Power BI Desktop to your data catalog and load the generators dataset.

Open Power BI Desktop.
Open the Get Data menu and select More.
Search for and select ODBC and choose Connect.
For Data Source Name (DSN), select (None).
Expand Advanced Options.
In the Connection string field, enter your connection string. For example, Driver={Amazon Athena ODBC (x64)};AwsRegion=us-east-1;Catalog=AwsDataCatalog;Schema=default;Workgroup=workgroup-abcdefghij-klmexample;SageMakerDomainId= dzd-123456example;SageMakerProjectId= abcd12example;SageMakerDomainRegion=us-east-1;AuthenticationType=SageMakerIam;
Choose OK.
Choose Default or Custom and then Connect.
In the AwsDataCatalog folder, navigate to your database.
Select the core_eia860__scd_generators table.
Choose Load.

When publishing, name your report generation-iamdomain-dsnless.

Configure the gateway and view your report on Power BI Service

After creating your reports in Power BI Desktop, configure the on-premises data gateway to view your report on Power BI Service.

You can configure the gateway using either a DSN or a DSN-less connection string, matching the method you used in Power BI Desktop.

Create and attach an IAM role to the Power BI Gateway EC2 instance

Create an IAM role for the EC2 instance that will host your Power BI gateway. Name the role pbi-gateway-role (or a name of your choice). The role must use EC2 as the trusted entity and include the following inline policy:

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "SageMakerAccess",
            "Effect": "Allow",
            "Action": [
                "datazone:GetConnection",
                "datazone:ListConnections",
                "datazone:GetDomain",
                "datazone:GetProject"
            ],
            "Resource": "*"
        },
        {
            "Sid": "STSForDriver",
            "Effect": "Allow",
            "Action": [
                "sts:GetCallerIdentity"
            ],
            "Resource": "*"
        }
    ]
}

Attach this role to your Power BI Gateway EC2 instance. For detailed steps on creating and attaching an IAM role to an EC2 instance, refer to IAM roles for Amazon EC2 in the Amazon EC2 User Guide.

Add the Power BI Gateway IAM role as a member of SageMaker Unified Studio project

The gateway IAM role needs project-level access to query data through Athena. The steps to add the role differ depending on your domain type.

IAM-based domain

Open your Amazon SageMaker Unified Studio Project.
Open Domain Management.
Choose your Project Name.
Choose Members.
Choose Add members.
Select the IAM role of your Power BI gateway (for example, pbi-gateway-role).
Choose Add.

The following screenshot shows the Amazon SageMaker Unified Studio project domain management page with options to add members to a project.

Figure 12: List of members of a SageMaker Unified Studio project with the IAM gateway role

Configure the data source on Power BI Gateway

How you configure the data source depends on the method you used in Power BI Desktop.

Method 1 (DSN-based)

Configure a System DSN on the gateway EC2 instance following the same ODBC DSN steps described in Method 1. When configuring, make sure that:

You use the System DSN tab (not User DSN) because the gateway runs as a Windows service under a separate account.
The authentication type is set to SageMakerIam.
The DSN name matches exactly the one configured on Power BI Desktop (for example, pbi-iamdomain).

Method 2 (DSN-less)

No configuration is needed on the gateway machine itself. You configure the data source directly in Power BI Service.

Configure the data source and view your report on Power BI Service

To view your report, complete the following steps:

Open the workspace where you saved your report.
Search the Semantic Model which has the same name as your report (for example, generation-iamdomain) and choose the More options icon (three dots).
Choose Settings.
Expand Gateway and Cloud Connection.
Choose View Datasources (play icon) on your gateway.
Choose Manually add to gateway.
Add a connection name (for example, pbi-iamdomain).

The next step depends on the method that you chose:

Method 1 (DSN-based)

Add the DSN (for example, pbi-iamdomain) that matches exactly the one configured on Power BI Desktop.

Method 2 (DSN-less)

In the Connection string field, enter the connection string that matches exactly the one used in Power BI Desktop.

Next, continue with the configuration:

Select Anonymous as Authentication Method.
Choose Create.
Expand again Gateway and Cloud Connection.
For Maps to, choose the connection that you created (for example, pbi-iamdomain).
Choose Apply.
Return to the workspace where you saved your report.
On the Content section, choose your report (for example, generation-iamdomain).

The following screenshot shows a report on Power BI Service.

Figure 13: Power BI report on Power BI Service

You can now see your report online with the data from your Amazon SageMaker Unified Studio project.

Clean up

To avoid additional charges after testing, delete the Amazon SageMaker Unified Studio domain and EC2 instances. Refer to Delete domains and Terminate Instances for instructions.

Conclusion

In this two-part series, you connected Power BI to Amazon SageMaker Unified Studio through Amazon Athena. Part 1 covered IDC-based domains. This post covered IAM-based domains using SageMakerIam authentication. This provides a direct connection path, with no third-party licensing, while maintaining data governance and security.

For additional best practices, refer to the Using Microsoft Power BI with the AWS Cloud Whitepaper. To learn more, visit Amazon SageMaker Unified Studio and Amazon Athena.

About the authors

How United Airlines uses Amazon Redshift and AWS Glue Data Catalog federation to query Databricks-managed data

Vaibhav Agrawal — Tue, 15 Sep 2026 15:59:48 +0000

This post was co-written with Ankit Aggarwal and Raja Kalluri from United Airlines.

United Airlines processes billions of events daily across its data platform, which spans Amazon Redshift and Databricks with Unity Catalog. To bridge these platforms without duplicating data, the team turned to AWS Glue Data Catalog federation.

In this post, we walk through how to configure AWS Glue Data Catalog federation to connect with Databricks Unity Catalog, so you can run live SQL queries from Amazon Redshift without moving or duplicating data.

Why United Airlines needed catalog federation

United Airlines curates petabytes of data through a medallion architecture (bronze to silver to gold) on Amazon Simple Storage Service (Amazon S3). The airline user interaction data layer alone is several double-digit terabytes of near real-time streamed data. Teams use it to measure customer engagement patterns, feature adoption, and conversion behavior across web and mobile touchpoints. Analysts need to query this curated data through Amazon Redshift Serverless. As part of the existing data platform architecture these data tables are cataloged in Databricks Unity Catalog, not in the AWS Glue Data Catalog. As a result, Amazon Redshift has no native visibility into them. Without catalog federation, the only way to make this data queryable from Amazon Redshift would have been to duplicate it into Amazon Redshift Managed Storage (RMS) and build pipelines to keep it in sync.

AWS Glue Data Catalog federation removed this need. Amazon Redshift users now query the gold layer stored in Amazon S3 directly, with Iceberg metadata resolved from Unity Catalog at query time and no data movement. AWS Glue Data Catalog federation connects Amazon Redshift to external catalogs like Unity Catalog, so analysts query cross-platform data without building sync pipelines or duplicating storage.

Amazon Redshift Serverless is powered by the same Graviton-based query engine used in the new RG instance family, which delivers up to 2x faster data lake query performance compared to prior generations. This engine is purpose-built for reading Apache Iceberg tables directly from Amazon S3, making it well-suited for such federated query workloads.

United Airlines is taking a phased approach to adopting AWS Glue Data Catalog federation across its data platform. The initial focus is the most heavily used user interaction data tables, with 30 tables currently federated in production and 70 more in active rollout. Several hundred additional tables across different business domains are planned for production in the coming months.

Solution overview

AWS Glue Data Catalog federation bridges these platforms at the metadata layer. Here’s how the architecture works.

The architecture follows a four-layer federation chain:

Databricks Unity Catalog exposes tables through its Iceberg REST API endpoint. For Delta tables, you can turn on UniForm format to make them Iceberg compatible.
AWS Glue Data Catalog creates a federated catalog that connects to Databricks Unity Catalog, making metadata visible within AWS without data movement.
A resource link database in the default AWS Glue catalog acts as a bridge, pointing to the federated catalog database. This is required for Amazon Redshift compute.
Amazon Redshift Serverless references the resource link database through an external schema. When a query runs, Amazon Redshift traverses the link, calls AWS Glue Federation, and reads the Iceberg data through the Databricks Unity Catalog REST API. AWS Lake Formation governs permissions throughout this chain.

Key services or service features used in this solution:

AWS Glue Data Catalog – Federated catalog and resource link in the default catalog.
AWS Lake Formation – Permission vending and fine-grained access control.
Amazon Redshift Serverless – Queries through external schema.
AWS Identity and Access Management (IAM) – Role-based permissions for Amazon Redshift namespace and SAML-authenticated users.
Databricks Unity Catalog – Source catalog exposing Iceberg tables through a REST API.

Figure 1: Federation chain from Databricks Unity Catalog to Amazon Redshift Serverless through AWS Glue and Lake Formation

The architecture follows a six-step flow:

A SQL analyst submits a query to Amazon Redshift Serverless.
Amazon Redshift resolves the external schema through the AWS Glue Data Catalog (resource link to federated catalog).
The AWS Glue federated catalog calls the Databricks Unity Catalog Iceberg REST API to retrieve current table metadata.
The namespace IAM role calls AWS Lake Formation GetDataAccess to obtain scoped, temporary S3 credentials.
Lake Formation evaluates fine-grained access policies and vends credentials for the authorized data files.
Amazon Redshift Serverless reads the Iceberg data files directly from S3 and returns results to the analyst.

Prerequisites

Before you begin, make sure the following are in place:

A Databricks workspace with Unity Catalog enabled and at least one catalog, schema, and table. Databricks uses UniForm to generate Iceberg metadata on Delta Lake tables on Amazon S3.
An AWS account with permissions to manage AWS Glue, AWS Lake Formation, Amazon Redshift Serverless, and IAM.
An Amazon Redshift Serverless workgroup and namespace already provisioned.
AWS Lake Formation set up with a data lake administrator.
AWS Command Line Interface (AWS CLI) configured with appropriate credentials.
Familiarity with Amazon Redshift Query Editor v2 or a SQL client.

Note: For setting up the Databricks Unity Catalog side (Phase 1), follow the steps in the AWS blog post Access Databricks Unity Catalog data using catalog federation in the AWS Glue Data Catalog. This walkthrough picks up after the federated catalog has been created in AWS Glue.

Solution walkthrough

The walkthrough is organized into six steps covering Lake Formation configuration, the resource link pattern, IAM role setup, and querying Databricks tables from Amazon Redshift.

Step 1: Configure AWS Lake Formation

1a. Add a data lake administrator

In Lake Formation, choose Administration, then choose Administrators and add your admin IAM user or role.

1b. Confirm the federated catalog is registered

Choose Data Catalog, then Catalogs and verify that databricks-federated-catalog is visible and registered.

Step 2: Create a resource link in the default AWS Glue catalog

This step is the key architectural detail in the walkthrough. Amazon Redshift resolves CREATE EXTERNAL SCHEMA only against the default AWS Glue Data Catalog. The federated catalog (databricks-federated-catalog) is a separate, non-default catalog object. To give Amazon Redshift a path to the federated data, you create a resource link database in the default catalog that points to the federated catalog’s database.

A resource link does not copy data or metadata. It’s a pointer that Lake Formation resolves at query time.

To create the resource link in the Lake Formation console:

Choose Data Catalog, Databases, Create database. Then select Resource link.
For Resource link name, enter databricks_federated_db_link.
For Target catalog, enter databricks-federated-catalog.
For Target database, enter the database name that was discovered by the AWS Glue crawler (for example, databricks_federated_db).

Alternatively, use the AWS CLI:

aws glue create-database \
  --database-input '{
    "Name": "databricks_federated_db_link",
    "TargetDatabase": {
      "CatalogId": "<account-id>:databricks-federated-catalog",
      "DatabaseName": "databricks_federated_db"
    }
  }'

Step 3: Configure the Amazon Redshift Serverless namespace IAM role

When Amazon Redshift queries through the resource link, it uses the IAM role attached to the Amazon Redshift Serverless namespace to call the Lake Formation GetDataAccess API. Lake Formation permissions must be granted to this namespace role.

Choose one of these two approaches:

Option A – Update your existing namespace role by adding the following policy inline.
Option B – Create a new dedicated role (named RedshiftServerlessNamespaceRole) and attach it to the namespace alongside existing roles.

Attach the following IAM policy to the role:

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Action": [
        "glue:GetDatabase",
        "glue:GetDatabases",
        "glue:GetTable",
        "glue:GetTables",
        "glue:GetPartitions",
        "glue:GetCatalog",
        "glue:GetCatalogs"
      ],
      "Resource": "*"
    },
    {
      "Effect": "Allow",
      "Action": "lakeformation:GetDataAccess",
      "Resource": "*"
    }
  ]
}

Note: The Resource: “*” in this policy is shown for simplicity. In production, scope resources to specific AWS Glue catalog ARNs, database ARNs, and table ARNs based on your use case.*

After creating or updating the role, associate it with your Amazon Redshift Serverless namespace:

In the Amazon Redshift Serverless console, choose Namespaces, select [your namespace], then choose Security and encryption, then Manage IAM roles.
If you use Option A, the existing role already has the new permissions, so no change is needed.
If you use Option B, add the new role alongside the existing roles.

Step 4: Grant Lake Formation permissions to the Amazon Redshift namespace role

4a. Grant DESCRIBE on the resource link database (default catalog)

In Lake Formation, choose Permissions, Data lake permissions, then Grant.
Principal: RedshiftServerlessNamespaceRole.
Resources: Named Data Catalog resources, Default catalog, databricks_federated_db_link (resouce link).
Database permissions: DESCRIBE.

4b. Grant SELECT and DESCRIBE on the target tables (Grant on Target)

Resource links permit only DESCRIBE and DROP permissions on the link itself. To allow Amazon Redshift to actually read data, you must separately grant SELECT on the target tables in the federated catalog. This is the Lake Formation Grant on Target pattern.

Principal: RedshiftServerlessNamespaceRole.
Resources: Named Data Catalog resources, databricks-federated-catalog, databricks_federated_db, then Tables.
Table permissions: SELECT, DESCRIBE.
Catalog permission: DESCRIBE.

Important: SELECT must be granted on the TARGET tables in the federated catalog, not on the resource link. Granting SELECT only on the resource link won’t work. This is a common configuration error.

Step 5: Create an external schema in Amazon Redshift

With the resource link in place and permissions granted, you can now create an external schema in Amazon Redshift that points to the resource link database. The external schema is the query interface. When a user runs SQL against it, Amazon Redshift traverses the link to the federated catalog and retrieves metadata and data from Databricks Unity Catalog.

The DATABASE parameter must reference the resource link database name in the default AWS Glue catalog (databricks_federated_db_link), not the federated catalog name directly. The CATALOG_ARN parameter isn’t required here because the resource link lives in the default catalog and Amazon Redshift resolves it automatically.

Connect to your Amazon Redshift cluster as a superuser (for example, using Amazon Redshift Query Editor v2) and run:

CREATE EXTERNAL SCHEMA databricks_schema
FROM DATA CATALOG
DATABASE 'databricks_federated_db_link'
IAM_ROLE '<iam-role-arn>'
REGION '<region>';

A key design principle in this architecture is the clear separation between data physically stored in Amazon Redshift and data accessed externally through federation. External schemas provide a transparent abstraction layer, so Amazon Redshift users can query data stored in S3 without ingestion. For consistency and clarity, United Airlines follows a standard naming convention for all federated schemas in Amazon Redshift: {domain}_iceberg. This convention makes it immediately clear that the data isn’t natively stored within Amazon Redshift but is accessed by using federation through AWS Glue and Lake Formation. This distinction is critical for analysts and engineers, because it improves discoverability, avoids ambiguity between storage layers, and reinforces architectural discipline when working across hybrid data environments.

The User Interactions domain exposes curated datasets representing customer interaction activity, engagement behavior, and channel usage patterns. Operational datasets follow the same pattern, providing governed access to supporting business events and reference information through a common federation framework.

You create a view layer over each external schema using WITH NO SCHEMA BINDING, so that analysts always resolve the freshest schema on each query execution. For example:

CREATE VIEW analytics.clickstream_events AS
SELECT * FROM {domain}_iceberg.interaction_events
WITH NO SCHEMA BINDING;

Step 6: Verify and query Databricks tables from Amazon Redshift

After creating the external schema, verify that the Databricks tables are visible and run a test query.

Verify table visibility

-- Confirm federated tables are visible in Redshift
SELECT * FROM SVV_EXTERNAL_TABLES
WHERE schemaname = 'databricks_schema';

Query a Databricks Unity Catalog table

-- Query a Databricks Unity Catalog table via the federated catalog
SELECT *
FROM databricks_schema.<table_name>
LIMIT 10;

When a query runs, Amazon Redshift calls Lake Formation GetDataAccess using the namespace IAM role to obtain temporary credentials. It then contacts the AWS Glue federated catalog, which in turn calls the Databricks Unity Catalog Iceberg REST API to retrieve metadata and read table data. The result is returned to the Amazon Redshift user transparently.

For SAML-authenticated users, connect using your IdP JDBC plugin:

jdbc:redshift:iam://<workgroup-name>.<account-id>.<region>.redshift-serverless.amazonaws.com:5439/<database>
?plugin_name=com.amazon.redshift.plugin.<YourIdPPlugin>
&idp_host=<your-idp-host>
&preferred_role=arn:aws:iam::<account-id>:role/RedshiftSAMLUserRole
&ssl=true

The Amazon Redshift JDBC driver handles authentication automatically. It authenticates with your IdP, receives a SAML assertion, and calls sts:AssumeRoleWithSAML for temporary IAM credentials. It then calls redshift-serverless:GetCredentials to connect as the mapped database user.

Business impact

AWS Glue Data Catalog federation delivered measurable architectural and operational improvements for United Airlines:

Area	Before	After	Impact
Data access	Delta Lake and Amazon Redshift data were completely siloed, so Amazon Redshift users had no access to curated datasets on Databricks-managed S3 data	Amazon Redshift users get real-time access to Databricks-managed data through AWS Glue Data Catalog federation	~100 analysts gained access to user interaction data tables in the first phase without adding new pipelines.
Disaster recovery	Cross-Region DR relied on Amazon Redshift snapshots every 3 hours (recovery point objective, or RPO, of 3 hours or more)	Amazon S3 cross-Region replication on the Delta Lake provides a near-continuous RPO. A new Amazon Redshift Serverless workgroup in the DR Region can federate to the same S3 data	More resilient architecture. Reduces cost for Amazon Redshift snapshot and copy maintenance across Regions
Architecture simplification	Data processing happened in both Databricks and Amazon Redshift, requiring manual catalog synchronization between the two platforms which was operationally expensive and prone to drift	With the federated architecture, data processing is consolidated in Databricks, and Amazon Redshift acts solely as a query engine powering user queries and dashboards through catalog federation	Single processing platform, zero sync pipelines, single source of truth
Infrastructure cost	Running dedicated Amazon Redshift ETL cluster with RMS storage, snapshots, and compute for data processing	For this use case with federation, Amazon Redshift is not needed for ETL but only as a query engine. No RMS storage duplication, no snapshot replication required	~$30K/month in redundant ETL infrastructure cost reduced

Security considerations

At United Airlines, identity governance is unified through Azure Active Directory groups. On the AWS consumption side, users authenticate to Amazon Redshift Serverless through SAML federation. AD group membership determines database-level access to federated schemas. On the Databricks side, the same AD groups govern access to Unity Catalog schemas. This single-identity model provides consistent access control across both platforms without requiring separate user provisioning. Lake Formation handles credential vending for S3 data access during federated queries, while schema-level access decisions are managed through the AD group mappings on each platform.

The architecture also provides multiple layers of security controls built into the federation chain:

AWS Lake Formation governs fine-grained access control throughout the federation chain, so that principals can only access authorized databases, tables, and columns.
IAM roles follow least-privilege principles. The Amazon Redshift namespace role is scoped only to AWS Glue metadata operations and Lake Formation GetDataAccess.
SAML-based authentication integrates enterprise identity providers, so that users authenticate through existing SSO infrastructure before accessing federated data.
All Amazon Redshift connections enforce TLS encryption (ssl=true), protecting data in transit between clients and the Amazon Redshift endpoint.
Lake Formation permission vending issues short-lived, scoped credentials for each query execution rather than long-lived static credentials.

Other considerations

Review the catalog federation service limitations before deploying. Key requirements:

Delta Lake tables must have UniForm enabled to expose Iceberg-compatible metadata.
We recommend that source tables be well-partitioned and regularly compacted, because the federated query performance reflects how efficiently the data is organized at write time.

Clean up

To avoid ongoing charges for resources created in this walkthrough, remove them in the following order. This teardown doesn’t affect Databricks metadata or your underlying data stored in Amazon S3.

Drop the external schema in Amazon Redshift: DROP SCHEMA databricks_schema;.
Delete the resource link database in the default AWS Glue catalog (databricks_federated_db_link).
Revoke Lake Formation permissions granted to the Amazon Redshift namespace role on both the resource link database and the target tables in the federated catalog.
Delete the federated catalog in AWS Glue (databricks-federated-catalog).
Deregister the AWS Glue connection for the Databricks Unity Catalog if no longer needed.
Optionally, remove the IAM role (RedshiftServerlessNamespaceRole) if it was created solely for this walkthrough.

Conclusion

In this post, we showed how United Airlines uses AWS Glue Data Catalog federation to give Amazon Redshift Serverless analysts real-time access to double-digit terabytes of curated user interaction data on Amazon S3, without duplicating a single byte or building sync pipelines.

The architecture uses the Iceberg REST API, resource link databases, and Lake Formation credential vending to create a governed query path between Amazon Redshift and Unity Catalog. For United Airlines, this eliminated redundant ETL infrastructure costs, removed the need for catalog synchronization, and turned Amazon Redshift Serverless into a dedicated high-performance query engine for analysts and dashboards.

For questions or feedback, leave a comment on this post.

About the authors

Scale down Kinesis Data Streams on-demand capacity with ODA warm throughput

Pratik Patel — Mon, 14 Sep 2026 15:36:36 +0000

Customers have been using Amazon Kinesis Data Streams to stream data at any scale. Some use On-demand Standard to let the service manage capacity, while others with predictable traffic patterns use On-demand Advantage and warm throughput to ensure streams can handle instant throughput increases. Streaming workloads rarely run at peak volume all the time: flash sales end, batch migrations complete, and telemetry bursts subside. However, manual intervention is often required to scale back down after the burst subsides. Amazon Kinesis Data Streams now supports scaling down ingest capacity for on-demand Advantage streams with warm throughput, which optimizes downstream compute costs and performance by removing excess capacity. You configure this by turning on On-demand Advantage mode (ODA) and setting a new warm throughput value that is equal to or smaller than the existing amount.

With this launch, you can now proactively reduce write throughput capacity, optimizing costs while maintaining performance and giving you more control over your stream’s provisioning.

In this post, we explore the warm throughput scale-down capability. We cover the challenge it addresses, how it works, how to monitor stream behavior with Amazon CloudWatch metrics, and best practices for using it effectively.

The challenge: Excess capacity after traffic spikes

Amazon Kinesis Data Streams on-demand mode automatically scales to handle increases in data throughput. When your stream experiences a traffic spike, Kinesis Data Streams splits shards to accommodate the higher volume. This automatic scaling helps your applications keep pace with data during surges.

However, many real-world workloads experience transient bursts that don’t represent sustained throughput needs. Consider a retail platform that processes a flash sale event, a healthcare system that ingests a large batch of patient records during a migration window, or an Internet of Things (IoT) fleet that transmits a high-volume firmware update telemetry burst. In each scenario, the stream scales up to accommodate the spike, but the elevated capacity remains long after the burst has subsided. Although Kinesis on-demand Advantage doesn’t charge for the elevated capacity, your consuming applications may see a higher cost and lower performance.

Consider a Kinesis data stream running with 100 MB/s ingest throughput that requires 100 shards. A traffic spike of an additional 50 MB/s forces on-demand mode to scale streams to 150 shards. The spike subsides within minutes, but those 150 shards remain.

If your AWS Lambda consumer uses a parallelization factor of 2, you go from 200 concurrent invocations (2 × 100 shards) to 300 (2 × 150 shards). This is a 50 percent jump in concurrent Lambda execution, even though ingest throughput has returned to 100 MB/s. Those extra 100 AWS Lambda invocations consume compute, count against your concurrent execution quota, and add cost while processing data with small batch sizes.

Kinesis Client Library (KCL) consumers incur operational overhead. KCL tracks one lease per shard in Amazon DynamoDB, so 50 additional shards mean 50 more leases to scan, renew, and checkpoint every heartbeat cycle. The result is more Amazon DynamoDB overhead for lease management and reduced consumption performance overall.

Before this launch, you had limited options to address this excess capacity:

Switch to provisioned mode to manually set shard count, losing the benefits of automatic scaling.
Accept the higher capacity and associated costs until the stream self-adjusted.

These approaches either introduced operational overhead or resulted in paying for capacity that exceeded your workload’s actual requirements.

The solution: Warm throughput scale-down

With on-demand capacity reduction, you can now set a lower or equal warm throughput value on your on-demand stream to trigger a capacity reduction. The stream adjusts to the requested capacity or the amount needed to support peak data ingest usage within the last hour, whichever is higher. This safeguard helps your stream retain sufficient capacity for current traffic while releasing the excess you no longer need.

This capability is available at no additional cost for all on-demand streams that have On-demand Advantage mode turned on.

How it works

Warm throughput provides bidirectional capacity management for on-demand streams:

Scale up (existing capability): If you forecast an upcoming traffic event, you can configure warm throughput to a higher value to prepare the stream in advance so that capacity is available when data arrives without throttling.
Scale down (new capability): If a transient burst has caused the stream to scale significantly beyond its steady-state needs, you can trigger a scale-down by setting warm throughput to a lower value.

When you set a warm throughput value that is equal to or lower than the current value on an on-demand stream, Kinesis Data Streams evaluates the request against your stream’s recent traffic. The resulting capacity is the greater of:

The warm throughput value you requested.
The capacity needed to support peak data ingest usage within the last hour.

This mechanism prevents you from accidentally reducing capacity below what your current workload demands. If data traffic increases after a scale-down has completed, on-demand mode can still expand stream ingest capacity through reactive scaling to avoid rate limiting.

Getting started

Prerequisites

To follow along, you need the following:

An existing Kinesis data stream in on-demand mode.
On-demand Advantage mode turned on.
AWS Command Line Interface (AWS CLI) installed and configured.
AWS Identity and Access Management (IAM) permissions for kinesis:UpdateStreamMode.

To trigger a scale-down, set a lower warm throughput value on your on-demand stream using the AWS CLI:

aws kinesis update-stream-mode \
--stream-arn arn:aws:kinesis:us-east-1:111122223333:stream/my-stream/my-stream \
--warm-throughput-in-mb 50

Monitoring stream behavior with Amazon CloudWatch

To observe the effects of a scale-down operation and understand your stream’s capacity and shard count, Amazon CloudWatch provides several key metrics. Monitoring these metrics helps you make informed decisions about when and how much to scale down.

Key metrics to monitor

The following table summarizes the CloudWatch metrics most relevant to warm throughput scale-down:

Metric	Namespace	Description
`IncomingBytes`	AWS/Kinesis	Total bytes ingested per period. Use the Sum statistic to see aggregate throughput across all shards.
`IncomingRecords`	AWS/Kinesis	Total records ingested per period. Helps identify traffic patterns and burst frequency.
`WriteProvisionedThroughputExceeded`	AWS/Kinesis	Number of records rejected because of throttling. A non-zero value after scale-down indicates capacity is set too low.

Observing shard count behavior during scale-down

To track shard count changes resulting from a scale-down, use the DescribeStreamSummary API, which returns the OpenShardCount field in its response. Note that OpenShardCount is not a CloudWatch metric. It’s available through the API and is also displayed on the Kinesis Data Streams console. You can poll this value periodically or build a custom CloudWatch metric using an AWS Lambda function to track shard count over time.

Here is how you can expect the stream to behave:

Before the burst: Your stream operates at steady-state with a baseline shard count appropriate for your normal traffic. For example, a stream handling 20 MiB/s of write throughput might have approximately 67 open shards.
During the burst: As traffic spikes, Kinesis Data Streams automatically splits shards to accommodate the increased load. The OpenShardCount rises, and IncomingBytes increases correspondingly.
After the burst (before scale-down): Traffic returns to baseline, but the OpenShardCount remains elevated because the stream retains capacity for up to double the recently observed peak.
After triggering scale-down: After you set a lower warm throughput, the OpenShardCount decreases as Kinesis Data Streams merges shards to match the requested capacity (subject to the one-hour peak safeguard). You can observe this transition by polling DescribeStreamSummary or on the Kinesis console.

Figure 1: Amazon Kinesis Data Streams shard count over time during a scale-down event, showing the incoming-data spike and the resulting change in shard count

Best practices

When using warm throughput scale-down, consider the following recommendations:

Analyze traffic patterns before scaling down. Review at least 24 hours of IncomingBytes and IncomingRecords CloudWatch metrics to understand your baseline throughput before setting a lower warm throughput value. This helps you avoid setting capacity below your actual steady-state needs.
Set warm throughput above your observed steady-state peak. Because on-demand streams accommodate up to double the observed peak, set your target warm throughput at or above your typical peak rather than your average. This maintains headroom for normal traffic variability without throttling.
Monitor throttling after scale-down. Watch WriteProvisionedThroughputExceeded closely in the hours following a scale-down. If throttling occurs, increase the warm throughput value. The stream will automatically scale back up, but proactive monitoring reduces the duration of any impact.
Use scale-down after known transient events. The feature is most effective when you can identify that a traffic spike was temporary, for example, after a planned batch migration, marketing event, or scheduled data backfill. Avoid scaling down during periods of uncertain or growing traffic.
Use the one-hour safeguard. The system won’t reduce capacity below what’s needed to serve peak ingest from the last hour. If you’re unsure about the right target, you can set a low warm throughput value and rely on this safeguard to prevent under-provisioning for active traffic.

Conclusion

Amazon Kinesis Data Streams now supports scaling down ingest capacity with warm throughput, giving you elastic control over On-demand Advantage stream capacity. With this capability, you can release excess capacity after transient traffic bursts, improving cost efficiency while maintaining the automatic scaling benefits of on-demand mode.

To get started, turn on On-demand Advantage mode for your stream and use the warm throughput setting to manage capacity. Track shard count with DescribeStreamSummary to observe capacity changes and confirm your stream keeps appropriate headroom for your workload. Try warm throughput scale-down today in the Amazon Kinesis console, and to learn more, see Amazon Kinesis Data Streams on-demand capacity mode in the Developer Guide.

About the authors

How to migrate from Amazon CloudSearch to Amazon OpenSearch Serverless

Prasad Nadig — Thu, 10 Sep 2026 16:06:42 +0000

If you run search on Amazon CloudSearch, now is the time to plan your migration to Amazon OpenSearch Serverless. Modern search has moved on to capabilities beyond what CloudSearch provides: semantic and hybrid search, Retrieval Augmented Generation (RAG), and agentic search. OpenSearch Serverless gives you all of these with automatic scaling on a pay-for-what-you-use basis. You don’t need to choose or maintain infrastructure. OpenSearch Serverless maintains the hands-off, operational simplicity of CloudSearch.

This post shows you how to migrate your CloudSearch domain to an Amazon OpenSearch Serverless collection. We walk you through assessing your CloudSearch configuration, creating an OpenSearch Serverless collection with explicit index mappings, converting your documents and queries, configuring security policies, loading your data with Amazon OpenSearch Ingestion, and validating the migration before cutting over.

Key differences to note

Independent scaling – OpenSearch Serverless scales indexing and search compute independently and can scale compute to zero when a collection is idle (you still pay for storage). For the cost structure, see Managing capacity limits for Amazon OpenSearch Serverless.
Layered security model – OpenSearch Serverless applies encryption, network, and data access policies at separate layers. For the security model, see Security in Amazon OpenSearch Serverless.

Prerequisites

To follow along with this post, you need the following:

An AWS account.
An existing Amazon CloudSearch domain with indexed data.
Source data available in a durable store such as Amazon Simple Storage Service (Amazon S3) or Amazon DynamoDB (CloudSearch doesn’t provide a built-in export or backup feature, so your original source data is required to re-ingest into OpenSearch).
AWS Identity and Access Management (IAM) permissions to create and manage Amazon OpenSearch Serverless collections, encryption policies, network policies, and data access policies.
An Amazon OpenSearch Ingestion pipeline (or alternative ingestion method) for loading data.

Plan the migration

Planning is where you decide what success means: minimal downtime, no data loss, current functionality preserved, and custom configurations carried over. You don’t need to plan for infrastructure because OpenSearch Serverless provisions and scales compute for you. Your main planning task is to assess your current CloudSearch configuration so you can reproduce its behavior on the target.

Document your existing setup from the Amazon CloudSearch console. Record the current instance type, the partition count, and the replication count. Capture the total document count and overall data size, and record every field definition, including field types and the search, facet, and sort settings for each field. Note any analyzers, synonyms, stopwords, or custom rank expressions. Note whether you use the 2011 or the 2013 CloudSearch API version, because the 2013 API added faceting and filtering features that change how you model the target.

OpenSearch Serverless is the right target for most CloudSearch workloads, but not all of them. If your workload needs very low read-after-write latency (a short refresh interval), tight and predictable query response times, or direct control over instance configuration, choose an Amazon OpenSearch Service managed clusters deployment instead and size it from your workload profile.

The migration involves four main concerns: your source data format, your queries, your field definitions, and your access policies. Before you plan the details, it helps to see the whole migration at once. The following diagram maps the migration across four phases: your source CloudSearch environment, the migration pipeline that converts and moves your data, the OpenSearch Serverless target, and cutover and operations.

Figure 1: The migration workflow across four phases

In the source environment, you assess your CloudSearch configuration and back up your source data (Amazon S3, Amazon DynamoDB, or another store). Note the Source Data Format (SDF), the URL-based query syntax, and the IAM access policies you need to carry over. In the migration pipeline, you map field types, convert the data format from CloudSearch JSON to OpenSearch-compatible JSON, convert your queries to the OpenSearch query domain-specific language (DSL), configure security, bulk-ingest the data, and validate the result. The OpenSearch Serverless target holds the collection, index mappings, ingested documents, and the encryption, network, and data access policies, and it scales with your workload on a pay-per-use basis. In cutover and operations, you update your application to the new endpoint and clients, monitor with Amazon CloudWatch, and decommission CloudSearch once no traffic remains.

Model your data in OpenSearch Service

OpenSearch Service uses index mappings to define the fields and data types in an index. Because you know your CloudSearch schema, define the target mapping explicitly when you create the index. Create the index and set its mapping in a single request, and set dynamic to strict so OpenSearch rejects any document that contains a field you did not define. Strict mapping catches schema drift at ingest time, avoiding the default OpenSearch behavior of creating new mappings for undefined fields.

PUT /imdb_movies
{
  "mappings": {
    "dynamic": "strict",
    "properties": {
      "title": {
        "type": "text",
        "fields": {
          "keyword": { "type": "keyword" }
        }
      },
      "genres": { "type": "keyword" },
      "rating": { "type": "float" },
      "release_date": { "type": "date" }
      ...
    }
  }
}

Field type mapping

The following table maps CloudSearch field types to their OpenSearch Service equivalents.

CloudSearch	OpenSearch Service equivalent	Notes
text	text	Text is tokenized. Stemming, synonyms, and stopwords apply. Good for matching user terms.
literal	keyword	Not tokenized. Good for exact-match search.
int	integer	Use for ranking, faceting, and narrowing.
double	float or double	.
date	date	.
boolean	boolean	.
latlon	geo_point	.
text-array	text	OpenSearch handles arrays natively, so map to the base text type.
literal-array	keyword	OpenSearch handles arrays natively, so map to the base keyword type.
multi-value	nested or object	.
long	long	.
binary	binary	.

Two mapping details deserve attention. First, pick the smallest numeric type that fits your data rather than copying the widths CloudSearch uses. CloudSearch stores integers as 64-bit values, but few datasets hold numbers that large. A long or a double consumes more disk than an integer, a short, or a float with no benefit when the values are small. Evaluate the actual range of each field and choose the narrowest type that holds it. Reserve long for values that genuinely exceed the roughly 2.1 billion ceiling of integer, and use float instead of double unless you need double precision. Smaller types shrink your index and speed up queries.

Second, if you sort or aggregate on a text field, add a keyword sub-field. The preceding example mapping has a keyword subfield for the title field. You access the field using dot notation: title.keyword. OpenSearch doesn’t sort or aggregate analyzed text fields by default.

As noted earlier, if you run several CloudSearch domains, model each one as a separate index within a single OpenSearch Serverless collection to consolidate them.

Move your data

Migrating to OpenSearch Service is a re-ingestion: you convert your source documents and index them into the collection you created. CloudSearch doesn’t provide a built-in backup or snapshot feature. It relies on the documents you send through the indexing process, so before you migrate, make sure your source data is available in a durable store such as Amazon S3, Amazon DynamoDB, or another database.

The conversion is a format translation. CloudSearch accepts data in SDF as JSON or XML, where a document batch is a collection of add and delete operations. The JSON that CloudSearch uses differs from the JSON that OpenSearch Service expects, so you must transform each source document into an OpenSearch document whose fields match the index mapping you defined earlier. Handle the same details the mapping calls out: emit each numeric value so it fits the narrow type you chose for its field rather than a wide long or double, format dates to match your date mapping, and drop or rename any field that your strict mapping doesn’t define.

Figure 2: CloudSearch batch format (left) compared to OpenSearch batch format (right)

You can write a small conversion script. Have the script write its output to an Amazon S3 bucket so the converted documents live in a durable store you can re-ingest from as many times as you need.

With your converted documents in Amazon S3, use Amazon OpenSearch Ingestion to load them. Amazon OpenSearch Ingestion is a feature of Amazon OpenSearch Service that you can use to ingest, filter, transform, enrich, and route data to an Amazon OpenSearch Service domain or an OpenSearch Serverless collection. Configure an OpenSearch Ingestion pipeline with an Amazon S3 source (you can use an OpenSearch Ingestion blueprint to get started) that reads your converted documents. Let its built-in processors apply any final transformation before the pipeline writes to your collection. A managed pipeline reading from Amazon S3 gives you a repeatable, restartable load without operating ingestion infrastructure, which makes it the recommended path for most migrations.

If you prefer to load data directly, OpenSearch Service exposes a REST API, so you can index documents with a standard client such as curl or with the OpenSearch client libraries for many languages. Direct indexing is convenient for a small dataset or a quick test, but an Amazon S3 source with OpenSearch Ingestion is the better choice for a production migration.

Convert your queries

CloudSearch uses a URL-based query format. You pass a query parameter in the URL and submit either a simple string search or a JSON-formatted query. OpenSearch Service uses a REST API and the OpenSearch query DSL in the request body, which gives you compound queries, function scoring, and richer relevance control. You can use generative AI coding assistants to help with this translation. Provide your CloudSearch query patterns, and the model generates the equivalent OpenSearch query DSL, which you then validate against your test cases.

Query syntax changes

CloudSearch appends parameters such as sort to the query URL, while OpenSearch expresses sorting, filtering, and boosting as explicit elements of the request body. For example, a title search for “shakespeare” in CloudSearch looks like the following.

https://my-cloudsearch-domain.us-east-1.cloudsearch.amazonaws.com/2013-01-01/search?q=shakespeare&size=10

The equivalent query in OpenSearch Service uses the query DSL.

GET /imdb_movies/_search
{
  "query": {
    "match": { "title": "shakespeare" }
  }
}

To keep result sets consistent after migration, set the default operator to AND in OpenSearch to match the default query behavior of CloudSearch. The following table shows common CloudSearch query patterns and their OpenSearch Service equivalents, using a sample IMDB movies dataset.

Query type	CloudSearch (Lucene syntax)	OpenSearch Service query DSL
Compound AND	`title:"Inception" AND genres:"Sci-Fi"`	`{"query":{"bool":{"must":[{"match":{"title":"Inception"}},{"match":{"genres":"Sci-Fi"}}]}}}`
Compound NOT	`title:"Star Wars" AND NOT genres:"Comedy"`	`{"query":{"bool":{"must":[{"match":{"title":"Star Wars"}}],"must_not":[{"match":{"genres":"Comedy"}}]}}}`
Wildcard	`title:Batman*`	`{"query":{"wildcard":{"title":{"value":"batman*"}}}}`
Numeric range	`rating:[7 TO 9]`	`{"query":{"range":{"rating":{"gte":7,"lte":9}}}}`
Date range (after)	`release_date:[2015-01-01T00:00:00Z TO *]`	`{"query":{"range":{"release_date":{"gte":"2015-01-01T00:00:00Z"}}}}`
Boosting	`title:"The Matrix"^6 OR genres:"Sci-Fi"^4`	`{"query":{"bool":{"should":[{"query_string":{"query":"title": \"The Matrix\"^6","fields":["title"]}},{"query_string":{"query":"genres:\"Sci-Fi\"^4","fields":["genres"]}}]}}}`
Sorting	`title:"Batman" sort=release_date desc`	`{"query":{"match":{"title":"Batman"}},"sort":[{"release_date":{"order":"desc"}}]}`

Sorting and boosting

Boosting is useful when you want certain fields or terms to carry more weight in relevance scoring. A higher boost value means the term contributes more to the score. OpenSearch also supports sorting by _score (relevance), which is the default when you specify no sort. For the full query language, see the OpenSearch query DSL documentation.

Configure security

CloudSearch uses AWS Identity and Access Management policies to control access to its configuration and domain service APIs. You attach user-based policies to an IAM role, user, or group, and the document, search, and suggest actions in those policies control access to the CloudSearch APIs.

OpenSearch Serverless applies security through policies at several layers.

Collections: Encrypted at rest by default, using either an AWS owned key or a customer managed key defined in an encryption policy.
Network policies: Define whether a collection is reachable privately through a virtual private cloud (VPC) endpoint or over the internet.
Data access policies: Control which IAM principals and Security Assertion Markup Language (SAML) identities can create indexes and read or write data in the collection.

Amazon OpenSearch Service provisioned domains also offer fine-grained access control, with role-based access control and security at the index, document, and field level. For OpenSearch Serverless, data access policies provide collection-level and index-level permissions, controlling which IAM principals and SAML identities can create, read, or write data within a collection.

Validate the migration

Validation confirms that the migration is complete and correct before you send production traffic to OpenSearch Serverless. Work through five kinds of validation.

Documents: Check your document count. Your OpenSearch Serverless indexes should have the same count as your CloudSearch indexes.
Queries: Translate your most important queries and run them manually against your collection. Spot check the output for the presence of important results.
Ranking: Check the order of results, especially for queries with custom rank functions or field weighting. Results might not match exactly, so look for anything that’s incorrect.
Latency: Ideally you should tee your production traffic to your Serverless collection to get real latency metrics. Worst case, generate at least 100,000 synthetic queries across all your query types and run them. Monitor OpenSearch Compute Unit (OCU) consumption with Amazon CloudWatch to understand your cost profile.

To validate search functionality, run the same query against both systems and compare the results. Reuse the query pairs from the conversion step so you exercise the syntax differences directly. For example, to check a numeric range against the sample IMDB movies dataset, run the following query in CloudSearch.

https://my-cloudsearch-domain.us-east-1.cloudsearch.amazonaws.com/2013-01-01/search?q=rating: [7 TO 9]&size=10

Run the equivalent query DSL against your OpenSearch Serverless collection.

GET /imdb_movies/_search
{
  "query": {
    "range": { "rating": { "gte": 7, "lte": 9 } }
  }
}

Confirm that both queries return the same set of movies. Then repeat the comparison for a query that exercises relevance, such as the boosted query from the conversion step, and confirm the top results appear in the same order.

GET /imdb_movies/_search
{
  "query": {
    "bool": {
      "should": [
        { "match": { "title": { "query": "The Matrix", "boost": 6 } } },
        { "match": { "genres": { "query": "Sci-Fi", "boost": 4 } } }
      ]
    }
  }
}

Cut over and operate

When validation passes, update your application to use the OpenSearch Serverless endpoint and the query DSL, and switch from the CloudSearch SDK to the OpenSearch client libraries. After cutover, confirm that no application still points to a CloudSearch endpoint, retain your source data backups in Amazon S3 for rollback, and then delete the CloudSearch domain.

Operating OpenSearch Serverless in production is lighter than operating a domain, because OpenSearch Serverless scales compute for you and you do not tune shards, instance types, or capacity. Your focus shifts to cost and search quality. Monitor OCU consumption and search latency with Amazon CloudWatch, and set alarms on the thresholds that matter to you. Review OCU usage patterns to understand cost and find optimization opportunities, and set capacity limits on the collection to cap the maximum OCUs it can consume. For guidance, see Managing capacity limits for Amazon OpenSearch Serverless and Monitoring Amazon OpenSearch Serverless.

Cost considerations

With OpenSearch Serverless, you pay only for the compute and storage your workload consumes, and OpenSearch Serverless charges for compute and storage separately. OpenSearch Serverless scales indexing compute and search compute independently, so a write-heavy or a read-heavy workload scales only the dimension it needs, and compute can scale to zero when a collection is idle, in which case you pay only for storage. To share hardware across workloads, place collections in a collection group so they draw from the same compute rather than each provisioning its own. For pricing and unit details, see Amazon OpenSearch Service pricing.

Clean up

Because you’re migrating to OpenSearch Serverless, the resources that you’ve created will likely become your production resources. If not, delete any OpenSearch Serverless collections and S3 buckets you created to avoid incurring ongoing cost.

Conclusion

In this post, you saw how Amazon CloudSearch and Amazon OpenSearch Serverless compare, and how the concepts you rely on in CloudSearch (field types, query syntax, autoscaling, and access control) translate into OpenSearch Service. You assess your CloudSearch configuration, model your data with explicit OpenSearch mappings, move your converted documents into the collection with OpenSearch Ingestion, convert your URL-based queries into the OpenSearch query DSL, configure security, and validate before cutover. OpenSearch Serverless gives you the hands-off operational model you have with CloudSearch, and adds richer query capabilities, granular data access policies, and automatic scaling. To get started, create an OpenSearch Serverless collection on the AWS Management Console and follow the steps in this post.

To learn more, see the following resources:

About the authors

Accelerating Spark queries with Iceberg materialized views

Yuzhou Sun — Thu, 10 Sep 2026 16:05:45 +0000

In this post, you learn how to reduce Apache Spark query execution time with Apache Iceberg materialized views without changing a single SQL query.

Organizations running analytical workloads on their data lakes often hit a common wall: queries that are slow and costly, yet difficult to rewrite by hand. Multi-table joins, heavy aggregations, and window functions over large fact tables all drive up execution times, but the SQL behind them often can’t be changed. It might come from business intelligence (BI) dashboards, packaged independent software vendor (ISV) applications, or legacy reports, where editing the source introduces regression risk that outweighs the performance gain.

Starting with Amazon EMR 7.12.0 and AWS Glue 5.1, you can accelerate these queries without rewriting them. Automatic query rewrite analyzes the logical plan of each incoming query and compares it against a metadata cache of available MVs. When the optimizer finds a materialized view (MV) that satisfies all or part of a query, it rewrites the plan to read from that MV instead of the base tables. Matches can be structural (aggregations and joins) or exact (more complex patterns like window functions). If no MV matches, the original query runs unchanged with no impact on correctness.

If you have previously tried to speed up slow analytical queries, you might have considered one of the following alternatives. Here is how automatic query rewrite compares:

Query modification approach	Stored results	Refreshes	Modification to existing queries
Standard views in AWS Glue	No (re-runs each time)	n/a	Required
Custom ETL pipeline	Yes	Manual	Required
Hand-rolled rewrite	Yes	Manual	Required
Materialized views with automatic rewrite enabled	Yes	Automatically through AWS Glue Data Catalog on a schedule when configured	Not required when supported

In this post, we:

Give a high-level overview of how automatic query rewrite works in Apache Spark.
Walk through a concrete example, showing how the same query can benefit from MVs at different levels of coverage.
Discuss the trade-offs so you can choose the right MV shape for your workload.

Prerequisites

To use automatic query rewrite with Iceberg materialized views, you need:

Amazon EMR release 7.12.0 or later, or AWS Glue 5.1 or later.
Source tables in Apache Iceberg or Parquet format, registered in the AWS Glue Data Catalog, in the same AWS Region and account as the materialized view. Parquet source tables are supported for automatic query rewrite starting with Amazon EMR 7.14.0 and AWS Glue 8.1.
An Amazon Simple Storage Service (Amazon S3) Tables (a capability of Amazon S3) bucket, or an S3 general purpose bucket, for the materialized view data.
Permissions for the definer role. You can use AWS Identity and Access Management (IAM) policies or AWS Lake Formation.
Automatic query rewrite turned on in your Spark session: --conf spark.sql.optimizer.answerQueriesWithMVs.enabled=true.
For Parquet source tables, set spark.sql.materializedView.v1SourceTables.enabled=true and spark.sql.materializedView.v1ETagVersioning.enabled=true.

For more Spark configurations, see Introducing Apache Iceberg materialized views in AWS Glue Data Catalog.

How it works

Here is how MVs and automatic query rewrite work together:

You define a SQL query with aggregations, joins, or filters across your supported source tables.
AWS Glue Data Catalog stores the precomputed results as an Apache Iceberg table in your Amazon S3 bucket. You can store it in a general purpose S3 bucket or in Amazon S3 Tables. Any Apache Iceberg-compatible query engine can read the materialized view, including Amazon Athena, Amazon EMR, AWS Glue, Amazon Redshift, and Iceberg-compatible third-party query engines. Automatic query rewrite is available on the AWS optimized Spark runtime in Amazon Athena, Amazon EMR, and AWS Glue. Other engines can query the materialized view directly, but they don’t rewrite queries to use it automatically.
Automatic refresh keeps the MV current on a schedule that you define, for example SCHEDULE REFRESH EVERY 1 DAY. You set it at creation time or later with ALTER MATERIALIZED VIEW ... ADD SCHEDULE REFRESH. At that scheduled time, the refresh process checks the current Apache Iceberg snapshot ID or Parquet file ETags and refreshes the MV when it detects source-table changes.
Automatic query rewrite redirects matching queries to the MV at query optimization time. Automatic query rewrite in Apache Spark uses two matching strategies:
- Structural rewrite (adapted from Amazon Redshift) handles an MV defined as a single SELECT-FROM-WHERE-GROUP-BY block over INNER joins. The optimizer can roll up an MV’s aggregates to a coarser grain and pull extra query predicates up onto the MV scan.
- Exact-match rewrite handles MVs defined as other shapes, such as window functions and outer joins, by matching a canonicalized form of the MV body against subtrees of the query plan.

When the optimizer evaluates a query, it consults a metadata cache of MVs from the configured catalogs and chooses the best match. It also checks MV staleness during optimization. It skips stale MVs, so rewrite won’t return stale results. If no MV matches, the original query runs unchanged.

Note that automatic query rewrite is opt-in: set spark.sql.optimizer.answerQueriesWithMVs.enabled=true when creating the Apache Spark session.

Example: One query with three potential MVs

An MV doesn’t need to cover an entire query to help it. Automatic query rewrite in Apache Spark operates on subtrees: when an MV matches a portion of your query plan, the rewriter substitutes that subtree and lets the rest of the query run on the rewrite output unchanged. The same query can therefore be served by many possible MV designs, each making a different trade-off between per-query speedup, storage cost, and reuse across other queries.

To make this concrete, consider a typical analytics query: “Top 100 preferred US customers by total store spending.” It joins fact and dimension tables, applies two selective filters on the customer dimension, aggregates per customer, ranks the result with a window function, and keeps only the top 100:

SELECT c_customer_id, total_revenue, num_transactions, avg_purchase, revenue_rank
FROM (
    SELECT cust.c_customer_id,
        SUM(sales.ss_quantity * sales.ss_sales_price) AS total_revenue,
        COUNT(*) AS num_transactions,
        AVG(sales.ss_quantity * sales.ss_sales_price) AS avg_purchase,
        RANK() OVER (ORDER BY SUM(sales.ss_quantity * sales.ss_sales_price) DESC) AS revenue_rank
    FROM base_catalog.base_db.store_sales sales
    INNER JOIN base_catalog.base_db.customer cust
        ON sales.ss_customer_sk = cust.c_customer_sk
    WHERE cust.c_birth_country = 'UNITED STATES'
        AND cust.c_preferred_cust_flag = 'Y'
    GROUP BY cust.c_customer_id
) ranked
WHERE revenue_rank <= 100
ORDER BY revenue_rank;

Query 1: The original query. Top 100 preferred US customers by total store spending, before any materialized view.

Three MV designs cover progressively more of this query, from a single-table pre-aggregate to the full query body itself:

Tier 1: Pre-aggregate store_sales only, no join, no filter. This tier is a single-table aggregate of store_sales at customer-surrogate-key grain. The query still must join the customer table, apply both filters, re-aggregate at c_customer_id grain, and run the window function.

CREATE MATERIALIZED VIEW mv_catalog.mv_db.customer_tier_1 AS
SELECT ss_customer_sk,
    SUM(ss_quantity * ss_sales_price) AS sum_revenue,
    COUNT(ss_quantity * ss_sales_price) AS count_revenue,
    COUNT(*) AS num
FROM base_catalog.base_db.store_sales
GROUP BY ss_customer_sk;

Tier 1 MV: Single-table pre-aggregate of store_sales by customer surrogate key (no join, no filter).

The following plans compare the original query plan to the rewritten plan:

Window, filter, Sort
+- Aggregate by c_customer_id
:  total_revenue = SUM(ss_quantity * ss_sales_price)
:  num_transactions = COUNT(*)
:  avg_purchase = AVG(ss_quantity * ss_sales_price)
+- Project
   +- Join Inner ON ss_customer_sk = c_customer_sk
      :- BatchScan store_sales <- reads the large store_sales table
      +- Filter c_birth_country='UNITED STATES' AND c_preferred_cust_flag='Y'
         +- BatchScan customer

Plan 1: Original plan. Scans the large store_sales table.

Window, filter, Sort
+- Aggregate by c_customer_id <- rolls up pre-aggregated sums
:  total_revenue = SUM(sum_revenue) <- sum of sum_revenue
:  num_transactions = SUM(num) <- sum of num
:  avg_purchase = SUM(sum_revenue) / SUM(count_revenue) <- sum of sum_revenue / sum of count_revenue
+- Project
   +- Join Inner ON ss_customer_sk = c_customer_sk
      :- BatchScan customer_tier_1 <- reads pre-aggregated MV
      +- Filter c_birth_country='UNITED STATES' AND c_preferred_cust_flag='Y'
         +- BatchScan customer

Plan 2: Rewritten plan (Tier 1). Reads the pre-aggregated customer_tier_1 MV.

Tier 2: Pre-join store_sales x customer, pre-apply one filter (c_preferred_cust_flag = ‘Y’). The middle tier pre-joins both tables and bakes in the preferred-customer filter. The query still must apply the country filter as a residual on the MV scan and run the RANK() window.

CREATE MATERIALIZED VIEW mv_catalog.mv_db.customer_tier_2 AS
SELECT cust.c_customer_id, cust.c_birth_country,
    SUM(sales.ss_quantity * sales.ss_sales_price) AS sum_revenue,
    COUNT(sales.ss_quantity * sales.ss_sales_price) AS count_revenue,
    COUNT(*) AS num
FROM base_catalog.base_db.store_sales sales
INNER JOIN base_catalog.base_db.customer cust
    ON sales.ss_customer_sk = cust.c_customer_sk
WHERE cust.c_preferred_cust_flag = 'Y'
GROUP BY cust.c_customer_id, cust.c_birth_country;

Tier 2 MV: Pre-joins store_sales and customer, with the preferred-customer filter applied.

Rewritten query plan:

Window, filter, Sort
+- Aggregate by c_customer_id <- rolls up pre-aggregated sums
:  total_revenue = SUM(sum_revenue) <- sum of sum_revenue
:  num_transactions = SUM(num) <- sum of num
:  avg_purchase = SUM(sum_revenue) / SUM(count_revenue) <- reads pre-aggregated MV
+- Filter c_birth_country='UNITED STATES' [residual filter on MV scan]
   +- BatchScan customer_tier_2 <- reads pre-aggregated MV

Plan 3: Rewritten plan (Tier 2). Country filter applied as a residual on the MV scan.

Tier 3: Match the entire query, including the window function and top N filter. This is the most specific tier. The MV body is the target query verbatim (minus the top-level ORDER BY, which is meaningless for a stored set). The MV stores the top-ranked rows the query asks for (rank ≤ 100).

CREATE MATERIALIZED VIEW mv_catalog.mv_db.customer_tier_3 AS
SELECT c_customer_id, total_revenue, num_transactions, avg_purchase, revenue_rank
FROM (
    SELECT cust.c_customer_id,
        SUM(sales.ss_quantity * sales.ss_sales_price) AS total_revenue,
        COUNT(*) AS num_transactions,
        AVG(sales.ss_quantity * sales.ss_sales_price) AS avg_purchase,
        RANK() OVER (ORDER BY SUM(sales.ss_quantity * sales.ss_sales_price) DESC) AS revenue_rank
    FROM base_catalog.base_db.store_sales sales
    INNER JOIN base_catalog.base_db.customer cust
        ON sales.ss_customer_sk = cust.c_customer_sk
    WHERE cust.c_birth_country = 'UNITED STATES'
        AND cust.c_preferred_cust_flag = 'Y'
    GROUP BY cust.c_customer_id
) ranked
WHERE revenue_rank <= 100;

Tier 3 MV: Stores the exact ranked output of the query (exact-match path).

This tier exercises the exact-match rewrite path: the rewriter canonicalizes the MV body and matches it against the query’s logical plan.

Rewritten plan:

Sort revenue_rank ASC
+- BatchScan customer_tier_3 <- reads around 100 stored rows

Plan 4: Rewritten plan (Tier 3). Reads around 100 stored rows.

The trade-off

The three tiers trade per-query speedup against reuse and storage. In our testing on TPC-DS 3 TB, we observed the following:

MV design	Pre-computed	Reuse	Per-query speedup	MV size
Baseline (no MV)	nothing	n/a	1x	n/a
Tier 1: store_sales agg by customer surrogate key	aggregate of all sales per customer	broadest: any per-customer aggregation	~5x faster	0.07% of store_sales for TPC-DS 3 TB
Tier 2: store_sales x customer agg, one filter pre-applied	join + aggregate, preferred customers only	medium: any country filter, preferred customers	~10x faster	0.04% of store_sales for TPC-DS 3 TB
Tier 3: entire query body verbatim (exact-match)	exact ranked output of this query	narrowest: only this exact query shape	20x+ faster	negligible (only 100 rows)

Performance measured on TPC-DS 3 TB. Speedup is the ratio of baseline execution time to MV-accelerated execution time. Results might vary based on data characteristics, cluster size, and query complexity.

In addition, MVs incur additional cost. Each one runs a query against your source tables once and stores the result. The more pre-computation it does (joining more tables, applying more filters), the more time it takes.

The following chart plots per-query speedup and creation time for the three tiers in our testing on TPC-DS 3 TB. Per-query speedup rises steadily, from about 5x at Tier 1 to over 20x at Tier 3. Creation time doesn’t follow the same pattern: it peaks at Tier 2. Tier 2 pre-joins and aggregates all preferred customers across every country, so it materializes the most data work. Tier 3 applies both filters, so it processes far fewer rows and costs less to create.

Figure 1: Per-query speedup and creation time across the three materialized view tiers, measured on TPC-DS 3 TB

Start by identifying one expensive query that runs repeatedly with stable filters. It is likely a good candidate for an exact-match MV.

Validating automatic query rewrite

To confirm that your query benefited from automatic rewrite:

Query plan inspection: Check the query’s optimized logical plan or physical plan for a leaf scan node referencing the MV (for example, BatchScan mv_catalog.mv_db.your_mv_name). If the MV appears as a scan source, rewrite succeeded.
Log confirmation (Amazon EMR 7.14.0+): Look for INFO-level log entries such as AQMV outcome: rewritten=true, mvs=[mv_name], duration=12ms.
No-rewrite diagnostics (Amazon EMR 7.14.0+): If rewrite didn’t occur, check the MVRewriteMetricsEvent in the Apache Spark Event Log for the specific reason the optimizer skipped the MV.

If you have set spark.sql.optimizer.answerQueriesWithMVs.enabled=true but your query still runs against the base tables, check the following common causes:

Write commands block rewrite by default. INSERT and MERGE statements don’t trigger rewrite. Set spark.sql.optimizer.answerQueriesWithMVs.commandBlockingEnabled=false to turn on rewrite within write command subqueries.
The MV is stale. Rewrite skips the MV when one or more source tables have changed since its last refresh. Wait for the next scheduled refresh, or force an immediate refresh with REFRESH MATERIALIZED VIEW <mv_name>.
Heuristic candidate filtering. The optimizer uses heuristic checks to narrow the set of MV candidates before attempting a full match. In some cases, an MV that could benefit the query might be filtered out early by these heuristics.
Spark version mismatch (Amazon EMR 7.13.0+). Automatic query rewrite skips MVs whose stored IMV_sparkVersion does not match the cluster’s current Apache Spark version. To bypass this check, set spark.sql.materializedView.sparkVersionCompatibilityCheck.enabled=false.
MV metadata cache not loaded. The metadata cache loads lazily during optimization of the first rewritable query in a Spark session. If your critical query fires before the cache is warm, the MV will not be available. Run a small warm-up query (for example, SELECT 1 FROM <some_iceberg_table>) at session start to pay this cost off the critical path.
MV metadata cache memory limit reached. If the cache was disabled or stopped loading MVs because of reaching its memory limit, increase spark.driver.memory.
Too many tables in configured catalogs. If there are many tables or MVs in the configured catalogs, the cache might not finish loading before your query starts. Place MVs in a dedicated catalog, add it to spark.sql.materializedViews.additionalCatalogs, and set spark.sql.materializedViews.scanCurrentCatalog=false to skip scanning the current catalog.
Parquet base tables have additional limitations and configuration requirements. For automatic query rewrite with Parquet base tables, set spark.sql.materializedView.v1SourceTables.enabled=true and spark.sql.materializedView.v1ETagVersioning.enabled=true. Without ETag versioning, Spark can’t determine a usable source-table version and skips the MV. Partitioned Parquet base tables are also subject to additional validation limits.

Performance considerations

Turning on automatic query rewrite has overhead: it introduces trade-offs that might affect some queries negatively:

Optimization overhead. Enabling rewrite adds processing time during query optimization as the optimizer evaluates MV candidates against the query plan. This overhead applies to every query in the session, including those that ultimately don’t match any MV.
Reduced task parallelism. Reading from an MV instead of the original base table might produce fewer tasks or introduce data skew, depending on the MV’s data layout. This reduces parallelism compared to a direct scan of the larger, more evenly distributed source table.

Conclusion

In this post, we showed how automatic query rewrite can accelerate your existing Apache Spark workloads. It uses Apache Iceberg materialized views in the AWS Glue Data Catalog, without changing a single line of SQL. By storing precomputed results as managed Apache Iceberg tables, the AWS Glue Data Catalog lets the Apache Spark optimizer transparently substitute matching query plans. You get the performance benefit of pre-aggregation without the application-level rewiring. BI dashboards, ISV-generated reports, and legacy pipelines all benefit the moment a matching MV exists.

We walked through three MV designs for the same analytical query, each striking a different balance between per-query speedup, storage footprint, and reuse across your workload. As the trade-off table shows, our testing found that a narrow, exact-match MV delivered 20x+ acceleration for a single query shape. A broader pre-aggregate served an entire family of queries at a more modest ~5x gain. The right choice depends on how many queries share the same join-and-aggregate pattern and how frequently your source data changes.

To get started:

Launch an Amazon EMR 7.12.0+ cluster or an AWS Glue 5.1+ job.
Create an MV over your most expensive repeating query using CREATE MATERIALIZED VIEW in the AWS Glue Data Catalog.
Turn on automatic query rewrite by setting spark.sql.optimizer.answerQueriesWithMVs.enabled=true in your Spark session configuration.
Verify the rewrite by inspecting the optimized query plan for an MV scan node, or by checking INFO-level logs on Amazon EMR 7.14.0+.

Queries with multi-table joins, heavy aggregations, or window functions over large fact tables are strong initial candidates. Start with one high-cost, frequently executed query. Validate the speedup, then expand to broader MVs as you identify shared patterns across your workload.

Special thanks to everyone who contributed to the automatic query rewrite feature and this blog: Andre Hernich, Leon Lin, Yiyang Chen, Geeta Krishna Panda, Ashok Chintalapati, Muhammad Malik, Rishabh Bhatia, and Giovanni Fumarola.

References

For more detail, see the following resources:

About the authors

Every team is a data team — bring Amazon Redshift analytics to ChatGPT Work

Naresh Chainani — Thu, 10 Sep 2026 15:14:01 +0000

Today, AWS is announcing the AWS Data Analytics plugin for the new Data agent in ChatGPT Work. The plugin helps teams across an organization ask questions in natural language, analyze governed data across their Amazon Redshift data warehouse and data lakes, and create shareable dashboards. All this happens from a conversation in ChatGPT Work.

Tens of thousands of customers choose Amazon Redshift every day to run their most demanding workloads, because it delivers analytics at scale with industry-leading price performance. They love how Amazon Redshift provides access to their data warehouses and data lakes together in one place. Teams can combine curated business data with the broader operational, historical, and third-party data stored in open formats like Apache Iceberg in their data lakes. This gives them a complete picture to make business-critical decisions across their data.

Customers have asked AWS for a way to put that trusted data in the hands of more of their people. That means not only the analysts and engineers who write SQL, but also the sales leaders, operations managers, and finance teams who depend on the results. A sales leader wants to know how the customer pipeline has changed this quarter. An operations manager wants to understand why fulfillment times changed over the past month. That’s why we built the AWS Data Analytics plugin, bringing the power of Amazon Redshift and AWS analytics to ChatGPT Work.

“Business teams can make decisions faster when they can source their own analytics and build the dashboards they need. Our work with AWS gives more people that ability, helping them understand changes in performance and decide where to focus. The AWS Data Analytics plugin connects Amazon Redshift to the Data agent in ChatGPT Work, so employees can analyze trusted company data simply by asking, with their organization’s existing access controls in place.”

— Arpan Shah, General Manager, Technology at OpenAI

The new plugin helps shorten the path from question to decision for everyone. Using the Data agent in ChatGPT Work, employees can explore the data they are authorized to access in Amazon Redshift by asking questions in everyday language. They can then refine the analysis, investigate changes, and turn the results into a dashboard without leaving ChatGPT Work. The plugin works with both Amazon Redshift provisioned clusters and Serverless workgroups. Customers can integrate it into their existing multi-cluster or multi-workgroup environments and benefit from the cost and security controls they’ve already set up.

Consider Maya, a business analyst supporting a revenue operations team. She wants to understand the revenue performance across various segments and regions.

Maya starts by loading the AWS Data Analytics plugin in ChatGPT Work, and then asking:

What are the revenue metrics for the past 30 days compared to the previous 30-day period?

Figure 1: Asking for revenue metrics in ChatGPT Work using the AWS Data Analytics plugin

The plugin translates her question into SQL, or a sequence of queries if needed, and runs them against the relevant data in Amazon Redshift. It returns key revenue performance metrics based on the same curated revenue data that her analytics team maintains.

Figure 2: Revenue performance metrics returned from Amazon Redshift

Maya notices that gross margin is declining and asks a follow-up question:

What is my revenue breakdown by product category and region for the past 90 days?

Figure 3: Revenue breakdown by product category and region for the past 90 days

The plugin carries the context forward, segments the results, and helps Maya understand each segment’s performance for the past 90 days. She can inspect the analysis and ask additional questions to drill down even further to understand why certain regions are lagging or why certain segments are outperforming others.

This conversational workflow doesn’t replace the data models, metric definitions, or governance practices that the analytics team has established. It helps more employees use that data directly, giving analysts more time for high-value work.

The AWS Data Analytics plugin connects ChatGPT Work to Amazon Redshift and uses the context of the connected analytics environment to help answer questions with the Data agent. During a conversation, it can:

Discover the schemas, tables, columns, and data types available to the user.
Translate a natural-language question into Amazon Redshift SQL.
Run the query against the customer’s Amazon Redshift environment.
Present the results in a table or concise explanation.
Use follow-up questions to filter, compare, or drill into the results.
Turn an analysis into an interactive dashboard that teams can share and explore.

Because the analysis runs against the customer’s existing data, teams can continue to use the curated datasets and business definitions they already maintain in Amazon Redshift. Customers whose Amazon Redshift environments query data in both a warehouse and a data lake can also make that data available through the governed datasets exposed to the plugin. The AWS Data Analytics plugin also supports our broader AWS data and analytics services. This includes the ability to work with AWS Glue Data Catalog, Amazon S3 Tables (a capability of Amazon Simple Storage Service (Amazon S3)), Amazon Athena, and vector search on AWS.

Natural-language analytics requires more than passing a prompt to a database. The agent needs to understand SQL specific to Amazon Redshift, discover metadata, choose the right tables and columns, and construct queries that follow service best practices. The plugin was built using Amazon Redshift skills from the Agent Toolkit for AWS. These skills provide tested procedures and service-specific guidance that agents can use when working with Amazon Redshift.

To get started, install the AWS Data Analytics plugin in ChatGPT Work to connect it to Amazon Redshift. Give your teams a conversational path to governed insights across your data warehouse and data lake today.

To learn more, see the following resources:

About the author

Build declarative ETL pipelines with AWS Glue 6.0

Syed Humair — Wed, 09 Sep 2026 17:38:22 +0000

Data teams commonly build the extract, transform, and load (ETL) pipelines that turn raw order events into analyst-ready aggregates as a bronze, silver, and gold sequence, the medallion architecture. Bronze holds raw ingested records, silver holds cleaned and validated data, and gold holds the business-level aggregates that analysts query. Today you build this on AWS Glue with an orchestrator such as Amazon Managed Workflows for Apache Airflow (Amazon MWAA) or AWS Step Functions coordinating the stages. Many teams run production pipelines exactly this way. As a pipeline grows, the coordination work grows with it: you wire job dependencies, manage intermediate checkpoints, and add retry logic stage by stage.

AWS Glue 6.0, powered by Apache Spark 4.1, introduces Spark Declarative Pipelines (SDP), which simplifies this further. Instead of orchestrating jobs by hand, you declare what each dataset should contain and let the declarative framework resolve dependencies, manage checkpoints, and orchestrate execution order automatically. The result runs as a single declarative job, with no manual directed acyclic graph (DAG) wiring or imperative orchestration code.

In this post, you build a single AWS Glue 6.0 job that turns raw order records into validated, aggregated, analytics-ready tables through the bronze, silver, and gold sequence. You do this without writing any orchestration logic. This walkthrough uses the AWS Command Line Interface (AWS CLI), and the same operations are available through the AWS SDKs.

Solution overview

You build a single AWS Glue 6.0 job that reads raw order records from a CSV file in Amazon Simple Storage Service (Amazon S3). The job flows them through three declared datasets. These are a bronze materialized view (ingest as-is), a silver materialized view (type, validate, and classify), and a gold SQL materialized view (aggregate by region). With AWS Glue Data Catalog integration turned on, all three land as Data Catalog tables, queryable with standard SQL tooling such as Amazon Athena. SDP resolves the dependency order from the dataset references in your code, so you never orchestrate the steps yourself.

Two ways to build the pipeline

Before you build the pipeline, let’s understand this new way of writing ETL pipelines with a quick comparison of the imperative and declarative approaches.

With the imperative approach, you need three AWS Glue jobs, plus an orchestrator to handle sequencing and error handling. A typical pipeline therefore has two layers: an orchestration layer and the ETL processing layer. The following diagram shows this two-layer imperative pipeline.

Figure 1: The two-layer imperative pipeline, with three AWS Glue jobs coordinated by an orchestrator.

Compared to that, the declarative approach runs as a single ETL job with SDP. The following diagram mirrors the previous one, but here it is a single AWS Glue ETL job instead of three jobs plus an orchestrator.

Figure 2: The declarative pipeline, a single AWS Glue job running the bronze, silver, and gold layers with SDP.

The declarative approach reduces more than the number of jobs. It removes the boilerplate that surrounds them. An orchestrator such as Amazon MWAA or AWS Step Functions already handles retries and parallelism, but only at the granularity of a whole job. To get finer control, teams often split a pipeline into several jobs and then hand-wire the dependencies between them. With SDP, you no longer hand-wire a DAG, manage per-stage checkpoints, or split the pipeline into separate jobs for retries and parallelism. SDP derives the dependency graph from your table references and coordinates execution at the level of individual tables. You can still invoke an SDP job from an orchestrator when a broader workflow calls for it, but the pipeline’s internal coordination is no longer code you write and maintain.

SDP separates the what from the how: you declare datasets (the outputs you want), and SDP builds the flows that produce them and runs them as one pipeline, resolving dependencies and execution order automatically.

You declare these abstractions through Python decorators. This post covers three of them, @dp.table, @dp.materialized_view, and @dp.temporary_view, each with its own purpose:

@dp.table defines a streaming table, which processes new data incrementally on each run. Typical use cases are raw event ingestion and change data capture (CDC) feeds.
@dp.materialized_view defines a materialized view for batch use cases. Today, this dataset type fully recomputes on each run. Common uses include parsing, aggregations, and machine learning (ML) feature engineering.
@dp.temporary_view is for temporary computations and aggregations. It’s pipeline-scoped and isn’t persisted outside the pipeline. Use it for enrichment lookups and subqueries.

Streaming tables append only new arrivals. Materialized views fully recompute. This post uses @dp.materialized_view for all three layers to keep the walkthrough focused. In production, you would typically use @dp.table for the bronze layer to process only new files as they arrive rather than re-reading the full source each run.

Running and refreshing the pipeline

When you rerun a pipeline, you don’t always want the same work to happen. Sometimes you only want to confirm the pipeline is well-formed before spending compute. Other times you want to run it but recompute only the datasets that changed rather than the entire graph. SDP handles both cases through two independent controls, and it helps to keep them separate:

Execution mode (the spark.glue.sdp.jobMode key) answers run or only validate?
Refresh scope (the spark.glue.sdp.runMode key) answers given that I’m running, what do I recompute?

Execution mode. VALIDATE runs the pipeline in dry-run mode: SDP checks the YAML syntax, dependency resolution, and SQL and Python compilation without writing any data. Use it to verify your pipeline is well-formed before committing compute. RUN (the default) executes the pipeline normally, resolving the dependency graph and materializing datasets.

# Dry run: validate the graph, write nothing
aws glue start-job-run \
  --job-name "${JOB_NAME}" \
  --arguments '{"--conf":"spark.glue.sdp.jobMode=VALIDATE"}' \
  --region "${AWS_REGION}"

# Normal execution
aws glue start-job-run \
  --job-name "${JOB_NAME}" \
  --arguments '{"--conf":"spark.glue.sdp.jobMode=RUN"}' \
  --region "${AWS_REGION}"

Refresh scope. By default, a RUN recomputes every materialized view. You can narrow or widen that with spark.glue.sdp.runMode:

--refresh <datasets> updates only the named datasets (comma-separated, no spaces).
--full-refresh <datasets> resets and recomputes only the named datasets (for streaming tables, this also clears their checkpoints).
--full-refresh-all resets and recomputes every dataset in the pipeline.

# Selective refresh of named datasets
aws glue start-job-run \
  --job-name "${JOB_NAME}" \
  --arguments '{"--conf":"spark.glue.sdp.jobMode=RUN --conf spark.glue.sdp.runMode=--refresh silver_orders,gold_sales_summary"}' \
  --region "${AWS_REGION}"

# Full reset and recompute of the entire pipeline
aws glue start-job-run \
  --job-name "${JOB_NAME}" \
  --arguments '{"--conf":"spark.glue.sdp.jobMode=RUN --conf spark.glue.sdp.runMode=--full-refresh-all"}' \
  --region "${AWS_REGION}"

Selective refresh is useful during development, so you can iterate on a single layer without reprocessing the entire graph. Note that --refresh and --full-refresh each take an explicit list of datasets. To reset the whole pipeline, use --full-refresh-all. Because materialized views hold no incremental state, resetting a materialized view and refreshing it both fully recompute it. The reset-versus-refresh distinction matters for streaming tables, where a refresh processes only new data and a reset clears the checkpoint and reprocesses from scratch.

The multiple values are passed as a single --conf argument string ("spark.glue.sdp.jobMode=RUN --conf spark.glue.sdp.runMode=..."). This is the serialization the AWS Glue SDP mode expects for the run.

Materialized views: Batch transforms with automatic dependency resolution

Materialized views recompute their full result set on each run. SDP infers dependencies from table references: in this pipeline, silver_orders references bronze_orders, so SDP runs bronze first, as shown in the following diagram.

Figure 3: SDP infers the dependency order from table references and runs bronze before silver.

The core pattern is a decorated function that returns a DataFrame:

@dp.materialized_view(comment="Raw orders loaded from CSV")
def bronze_orders() -> DataFrame:
    return spark.read.schema(ORDERS_SCHEMA).option("header", "true").csv(ORDERS_PATH)

The silver layer references bronze_orders through spark.table("bronze_orders"), with no explicit dependency declaration. SDP builds the DAG by analyzing table references in your code and runs bronze first automatically.

Bronze reads every column as a string by design: the bronze layer preserves raw source data without coercion. Type casting, validation, and filtering happen in the silver layer.

SQL and Python coexistence

SDP supports both Python and SQL definitions in the same pipeline project. A SQL materialized view can reference a Python-defined table directly, for example the gold layer aggregating the silver table:

CREATE MATERIALIZED VIEW gold_sales_summary
COMMENT 'Completed-order metrics by region'
AS
SELECT
  region,
  COUNT(*) AS order_count,
  CAST(ROUND(SUM(amount), 2) AS DECIMAL(10, 2)) AS total_sales,
  CAST(ROUND(AVG(amount), 2) AS DECIMAL(10, 2)) AS average_order_value
FROM silver_orders
GROUP BY region;

In this post, Python files define ingestion and validation logic, and SQL files define reporting views and aggregations. SDP discovers both through the libraries glob pattern in the pipeline specification and resolves the cross-language dependencies automatically. The complete source for all three layers follows in the step-by-step walkthrough.

Build the pipeline: Step by step

The rest of this post is a hands-on walkthrough. You build a single AWS Glue 6.0 job that reads orders.csv and processes it through the bronze, silver, and gold layers. The steps are:

Prerequisites: AWS account, AWS Identity and Access Management (IAM) role, and S3 bucket.
Set up sample data: create orders.csv and upload it to Amazon S3.
Build the pipeline files (the spark-pipeline.yml specification plus the three transformation files).
Package the pipeline into a zip and upload it to Amazon S3.
Create the database: a Data Catalog database with an S3 location.
Configure the job: create the AWS Glue 6.0 job with the SDP flag.
Validate: run in dry-run mode to verify the graph.
Run the pipeline to materialize all datasets.
Query results: inspect the tables with Amazon Athena.
Clean up: delete the resources you created.

Step 1 – Prerequisites

To follow along, you need:

An AWS account with access to AWS Glue 6.0.
A dedicated IAM role trusted by glue.amazonaws.com (set up in the following section).
A private, encrypted Amazon S3 bucket with Block Public Access enabled.
The AWS CLI configured with credentials for a non-production account.

IAM role for the pipeline

Create a role that AWS Glue can assume, with the following trust policy:

{
  "Version": "2012-10-17",
  "Statement": [{
    "Effect": "Allow",
    "Principal": { "Service": "glue.amazonaws.com" },
    "Action": "sts:AssumeRole"
  }]
}

Attach the AWS managed policy AWSGlueServiceRole, which grants the AWS Glue Data Catalog and Amazon CloudWatch Logs access the job needs. Then add an inline policy that scopes Amazon S3 access to your bucket, covering the input data, the pipeline zip, the pipeline storage (state) path, and the warehouse location:

{
  "Version": "2012-10-17",
  "Statement": [{
    "Effect": "Allow",
    "Action": ["s3:GetObject", "s3:PutObject", "s3:DeleteObject", "s3:ListBucket"],
    "Resource": [
      "arn:aws:s3:::amzn-s3-demo-bucket",
      "arn:aws:s3:::amzn-s3-demo-bucket/*"
    ]
  }]
}

For a full breakdown of the baseline permissions, see Setting up IAM permissions for AWS Glue.

Set the walkthrough variables

Set the following variables, replacing the example values (us-east-1, amzn-s3-demo-bucket, the account ID 111122223333, and the role name) with your own:

export AWS_REGION="us-east-1"
export BUCKET="amzn-s3-demo-bucket"
export PREFIX="simple-sdp-demo"
export DATABASE="simple_sdp_demo_db"
export ROLE_ARN="arn:aws:iam::111122223333:role/AWSGlueServiceRole-sdp-demo"
export JOB_NAME="simple-sdp-demo"

Step 2 – Set up sample data

The pipeline reads a CSV of order records. Save the following as orders.csv:

order_id,customer_id,region,amount,status,order_ts
O-1001,C-101,EMEA,120.50,COMPLETE,2026-07-23T08:00:00Z
O-1002,C-102,AMER,750.00,COMPLETE,2026-07-23T08:15:00Z
O-1003,C-103,EMEA,-10.00,INVALID,2026-07-23T08:30:00Z
O-1004,C-104,APAC,320.25,COMPLETE,2026-07-23T09:00:00Z
O-1005,C-105,AMER,250.00,COMPLETE,2026-07-23T09:15:00Z
O-1006,C-106,EMEA,90.00,COMPLETE,2026-07-23T09:30:00Z

Upload the file to the input/ location under your project prefix, which is where the bronze layer reads it (the ORDERS_PATH in 01_bronze.py, shown in Step 3). Use the variables you exported in Step 1:

aws s3 cp orders.csv \
  "s3://${BUCKET}/${PREFIX}/input/orders.csv" \
  --region "${AWS_REGION}"

The file includes one invalid order (O-1003, a negative amount), which the silver layer filters out to demonstrate the validation step. The AMER and EMEA regions each have two completed orders, so the gold layer’s order_count and average_order_value are meaningful aggregations rather than single-row passthroughs.

Step 3 – Build the pipeline files

The pipeline project uses the structure introduced earlier: a transformations/ folder holding the three layer definitions (01_bronze.py, 02_silver.py, 03_gold.sql), plus the spark-pipeline.yml specification. The following screenshot shows this layout in a code editor.

Figure 4: The pipeline project layout in a code editor.

The complete contents of each file follow.

3a. spark-pipeline.yml

The specification names the pipeline, points to the Data Catalog database, configures state storage, and discovers transformation files. As with the transformation files, it uses the __DATABASE__, __BUCKET__, and __PREFIX__ tokens, which you substitute at packaging time in Step 4:

name: simple_sdp_demo
catalog: spark_catalog
database: __DATABASE__
storage: s3://__BUCKET__/__PREFIX__/state/
libraries:
  - glob:
      include: transformations/**
configuration:
  spark.sql.shuffle.partitions: "4"

3b. transformations/01_bronze.py

Bronze preserves the raw source as strings. No coercion, no filtering:

"""Bronze layer: preserve source order records as strings."""
from pyspark import pipelines as dp
from pyspark.sql import DataFrame, SparkSession
from pyspark.sql.types import StringType, StructField, StructType

spark = SparkSession.active()

ORDERS_PATH = "s3://__BUCKET__/__PREFIX__/input/orders.csv"

ORDERS_SCHEMA = StructType([
    StructField("order_id", StringType(), True),
    StructField("customer_id", StringType(), True),
    StructField("region", StringType(), True),
    StructField("amount", StringType(), True),
    StructField("status", StringType(), True),
    StructField("order_ts", StringType(), True),
])


@dp.materialized_view(comment="Raw orders loaded from CSV")
def bronze_orders() -> DataFrame:
    return (
        spark.read
        .schema(ORDERS_SCHEMA)
        .option("header", "true")
        .csv(ORDERS_PATH)
    )

The path uses the tokens __BUCKET__ and __PREFIX__ rather than hardcoded values. AWS Glue reads these files from the packaged zip at runtime, so shell variables like ${BUCKET} are not expanded inside them. You substitute the tokens with your real values when you package the project in Step 4, which keeps every file consistent with the variables you exported in Step 1.

3c. transformations/02_silver.py

Silver casts types, filters to complete orders with positive amounts, and derives an amount_band classification:

"""Silver layer: type, validate, and classify complete orders."""
from pyspark import pipelines as dp
from pyspark.sql import DataFrame, SparkSession
from pyspark.sql.functions import col, to_timestamp, trim, when

spark = SparkSession.active()


@dp.materialized_view(comment="Validated complete orders with typed values")
def silver_orders() -> DataFrame:
    typed = (
        spark.table("bronze_orders")
        .select(
            trim(col("order_id")).alias("order_id"),
            trim(col("customer_id")).alias("customer_id"),
            trim(col("region")).alias("region"),
            col("amount").cast("double").alias("amount"),
            trim(col("status")).alias("status"),
            to_timestamp("order_ts", "yyyy-MM-dd'T'HH:mm:ss'Z'").alias("order_ts"),
        )
        .filter(
            col("order_id").isNotNull()
            & col("region").isNotNull()
            & col("order_ts").isNotNull()
            & (col("status") == "COMPLETE")
            & (col("amount") > 0)
        )
    )
    return typed.select(
        "*",
        when(col("amount") >= 500, "large")
        .when(col("amount") >= 100, "medium")
        .otherwise("small")
        .alias("amount_band"),
    )

Silver reads bronze with spark.table("bronze_orders"), so SDP infers the dependency and runs bronze first. Two details matter here:

The to_timestamp call passes an explicit format, "yyyy-MM-dd'T'HH:mm:ss'Z'". The source timestamps are ISO 8601 with a Z suffix. Giving the format treats Z as a literal and produces the same wall-clock value regardless of the job’s session time zone, which keeps the result deterministic.
The transformation runs in two projections: the first casts and filters, and the second derives amount_band from the already-typed amount column. Deriving columns with .select(...) rather than a separate .withColumn(...) step keeps SDP’s reference to bronze_orders resolvable as a pipeline dependency. This way, SDP consistently orders the bronze layer before the silver layer. The order matters here too. Spark 4.1 enables ANSI mode by default, so comparing the raw string amount against a number would fail. amount_band therefore reads the already-cast amount.

3d. transformations/03_gold.sql

The gold layer aggregates order metrics by region using SQL:

CREATE MATERIALIZED VIEW gold_sales_summary
COMMENT 'Completed-order metrics by region'
AS
SELECT
  region,
  COUNT(*) AS order_count,
  CAST(ROUND(SUM(amount), 2) AS DECIMAL(10, 2)) AS total_sales,
  CAST(ROUND(AVG(amount), 2) AS DECIMAL(10, 2)) AS average_order_value
FROM silver_orders
GROUP BY region;

Step 4 – Package the project

Substitute the __BUCKET__, __PREFIX__, and __DATABASE__ tokens with the values you exported in Step 1. Then package spark-pipeline.yml and the transformations/ folder into a zip with both at the zip root. Because AWS Glue reads these files from the zip at runtime, the substitution has to happen now, at packaging time, not through shell variables at run time:

# Render the tokens into a build/ copy, leaving your source files untouched
rm -rf build/package && mkdir -p build/package/transformations

sed -e "s|__BUCKET__|${BUCKET}|g" \
    -e "s|__PREFIX__|${PREFIX}|g" \
    -e "s|__DATABASE__|${DATABASE}|g" \
    spark-pipeline.yml > build/package/spark-pipeline.yml

sed -e "s|__BUCKET__|${BUCKET}|g" \
    -e "s|__PREFIX__|${PREFIX}|g" \
    transformations/01_bronze.py > build/package/transformations/01_bronze.py
cp transformations/02_silver.py transformations/03_gold.sql build/package/transformations/

# Zip with the spec and transformations at the zip root
(cd build/package && zip -r -q ../simple-sdp-demo.zip spark-pipeline.yml transformations)

# Upload
aws s3 cp build/simple-sdp-demo.zip "s3://${BUCKET}/${PREFIX}/pipeline/simple-sdp-demo.zip" --region "${AWS_REGION}"

Only spark-pipeline.yml and 01_bronze.py carry tokens, so the other files are copied as-is. The uploaded object is named simple-sdp-demo.zip, which is the same name the job references in Step 6.

Step 5 – Create the database

The database named in spark-pipeline.yml must already exist in the AWS Glue Data Catalog, with an S3 location URI, before the pipeline runs. SDP does not create it automatically:

aws glue get-database --name "${DATABASE}" --region "${AWS_REGION}" >/dev/null 2>&1 \
|| aws glue create-database \
--database-input "{\"Name\":\"${DATABASE}\",\"LocationUri\":\"s3://${BUCKET}/${PREFIX}/warehouse/\"}" \
--region "${AWS_REGION}"

Step 6 – Configure the job

Create an AWS Glue 6.0 job with the zip as ScriptLocation and the SDP flag enabled:

aws glue create-job \
--name "${JOB_NAME}" \
--role "${ROLE_ARN}" \
--command "{\"Name\":\"glueetl\",\"ScriptLocation\":\"s3://${BUCKET}/${PREFIX}/pipeline/simple-sdp-demo.zip\",\"PythonVersion\":\"3\"}" \
--glue-version "6.0" \
--worker-type "G.1X" \
--number-of-workers 2 \
--default-arguments "{\"--enable-spark-declarative-pipeline\":\"true\",\"--enable-glue-datacatalog\":\"true\"}" \
--region "${AWS_REGION}"

Key arguments:

Argument	Purpose
`--enable-spark-declarative-pipeline`	Activates the SDP executor (required)
`--enable-glue-datacatalog`	Uses the AWS Glue Data Catalog as the Spark Hive metastore, so the pipeline’s output tables register in the catalog
`ScriptLocation`	Points to the pipeline zip, not a .py file

Table 2: Key arguments for the create-job command.

The create-job command sets ScriptLocation to the pipeline zip. You can also point it to an Amazon S3 prefix: upload the unzipped spark-pipeline.yml and transformations/ to a prefix and set ScriptLocation to that prefix (with a trailing /). No other change is needed, and the --enable-spark-declarative-pipeline flag stays the same. The zip keeps the upload to a single object.

Step 7 – Validate (dry run)

Run the job in validation mode first to verify the dependency graph without materializing data:

aws glue start-job-run \
  --job-name "${JOB_NAME}" \
  --arguments '{"--conf":"spark.glue.sdp.jobMode=VALIDATE"}' \
  --region "${AWS_REGION}"

Validation analyzes the project structure, dependency graph, and SQL and Python compilation without creating tables, executing transforms, or writing data. Confirm that the database has no tables after validation completes.

On AWS Glue, validation runs as a job (jobMode=VALIDATE), so you create the job in Step 6 and then validate it here. If you develop locally with the open source spark-pipelines CLI, you can run its dry-run against the project before packaging and uploading.

Step 8 – Run the pipeline

Start the pipeline in normal execution mode:

aws glue start-job-run \
  --job-name "${JOB_NAME}" \
  --arguments '{"--conf":"spark.glue.sdp.jobMode=RUN"}' \
  --region "${AWS_REGION}"

After the run completes, list the materialized tables:

aws glue get-tables \
  --database-name "${DATABASE}" \
  --region "${AWS_REGION}" \
  --query 'TableList[].Name' \
  --output table

Expected tables: bronze_orders, silver_orders, gold_sales_summary.

After the run, the AWS Glue console shows the three output tables in the simple_sdp_demo_db database. The database’s Location is the warehouse path you configured, s3://amzn-s3-demo-bucket/simple-sdp-demo/warehouse/, and each table stores its data under that prefix. The following screenshot shows the database properties and the three tables (bronze_orders, silver_orders, and gold_sales_summary), each registered in the AWS Glue Data Catalog.

Figure 5: The three output tables in the AWS Glue Data Catalog.

Step 9 – Query results

Query the tables with Amazon Athena. If this is your first time using Athena in this Region, set an Amazon S3 query-results location for your workgroup first (Athena console, Settings). Also make sure your identity can read the simple_sdp_demo_db tables in the Data Catalog and the underlying S3 data.

-- Bronze preserves all 6 source rows
SELECT * FROM simple_sdp_demo_db.bronze_orders ORDER BY order_id;

-- Silver retains the 5 complete orders with positive amounts
SELECT * FROM simple_sdp_demo_db.silver_orders ORDER BY order_id;

-- Gold aggregates by region
SELECT * FROM simple_sdp_demo_db.gold_sales_summary ORDER BY region;

Expected gold result:

region	order_count	total_sales	average_order_value
AMER	2	1000.00	500.00
APAC	1	320.25	320.25
EMEA	2	210.50	105.25

Table 3: Gold layer aggregation results by region.

Running the query in the Amazon Athena console returns the aggregated result. The following screenshot shows the gold query and its three result rows (AMER, APAC, and EMEA), matching the values in the preceding table.

Figure 6: The gold table results in the Amazon Athena console.

Cost considerations

AWS Glue 6.0 bills ETL jobs by the data processing unit (DPU)-hour, per second, with a 1-minute minimum per run. AWS Glue 6.0 is also priced 30 percent lower per DPU-hour than AWS Glue 5.1, with no change to your workload, so the same job costs less to run on 6.0. This walkthrough runs on 2 G.1X workers (2 DPUs), reads a 6-row CSV, and completes each run in about 2 minutes. It produces three tables in one AWS Glue Data Catalog database.

To estimate the cost of a run, multiply the 2 DPUs by the run time in hours by your Region’s AWS Glue 6.0 DPU-hour rate. You can find that rate on the AWS Glue pricing page, and rates differ by AWS Region. The Amazon S3 objects created are the 6-row CSV, the pipeline zip, and the three tables’ data. To stop further charges, delete the resources when you finish, as shown in the next step.

Step 10 – Clean up

To avoid ongoing charges, delete the resources you created:

# Delete the AWS Glue job
aws glue delete-job --job-name "${JOB_NAME}" --region "${AWS_REGION}"

# Delete the Data Catalog database and its table metadata
aws glue delete-database --name "${DATABASE}" --region "${AWS_REGION}"

# Remove the S3 objects
aws s3 rm "s3://${BUCKET}/${PREFIX}/" --recursive --region "${AWS_REGION}"

What’s next

You now have a single pipeline that turns raw order records into validated, aggregated analytics tables, without writing orchestration logic. From here you can:

Extend: Add transformation stages (additional @dp.materialized_view functions) and connect them by referencing upstream tables. The pipeline picks up the new dependency automatically.
Scale: This walkthrough uses materialized views throughout, so every layer fully recomputes on each run (materialized views don’t support incremental refresh). To process only new data as it arrives, convert the bronze layer to a streaming table, which maintains state across runs with checkpoints. For that cross-run state to persist, a streaming table’s data and checkpoint state must not be stored locally. Hive or AWS Glue managed tables require the database’s LocationUri to point to an Amazon S3 path, while Apache Iceberg tables manage their table metadata themselves.
Govern: Protect the Data Catalog tables SDP produces with AWS Lake Formation fine-grained access control. It enforces table-, row-, column-, and cell-level permissions on read queries in AWS Glue Spark jobs (Glue 5.0 and later, for Hive and Iceberg tables). Because this enforcement covers batch reads, it applies to SDP’s materialized views but not to streaming tables, which read through Spark Structured Streaming.
Automate: Store the pipeline project in source control. Have your continuous integration and continuous delivery (CI/CD) pipeline package and upload it to Amazon S3 so each job run maps to a known build. Version the zip by object key, or upload the unzipped project to an S3 prefix and turn on Amazon S3 bucket versioning.
Monitor: Use Amazon CloudWatch metrics and AWS Glue job run insights for pipeline observability, latency tracking, and failure alerting.

Conclusion

In this post, you used Spark Declarative Pipelines, the declarative alternative to explicitly orchestrated ETL, now available in AWS Glue 6.0. Two decorated Python functions and one SQL file define the bronze, silver, and gold datasets, and SDP resolves the dependencies and manages execution order for you.

With SDP, you declare what each dataset should contain and the declarative framework handles ordering and execution. A three-layer pipeline that would otherwise need separate transform and orchestration logic runs as one job that you can ship and maintain.

To get started, open the AWS Glue console and build the walkthrough pipeline, or adapt the pattern to your own bronze, silver, and gold datasets. For the full set of features, see the AWS Glue 6.0 launch announcement. To move existing jobs to the Spark 4.1 runtime, see Upgrade AWS Glue jobs to AWS Glue 6.0 with AI-powered Spark upgrades. For job configuration details, see the AWS Glue Developer Guide.

About the authors

AWS recognized as a Leader in the 2026 Gartner Magic Quadrant for Strategic Cloud Platform Services for the 16th consecutive year

Erika Ehrli — Tue, 08 Sep 2026 16:46:49 +0000

On September 1, Gartner published its Magic Quadrant for Strategic Cloud Platform Services (SCPS). Amazon Web Services (AWS) is the longest-running Leader in this Magic Quadrant, with Gartner naming AWS a Leader for the sixteenth consecutive year.

In the report, Gartner once again placed AWS highest on the Ability to Execute axis. We believe this reflects our commitment to help customers innovate faster, operate more securely, and build at any scale, particularly as agentic AI drives the need for a data foundation that is production-ready.

Here is the graphical representation of the 2026 Magic Quadrant for Strategic Cloud Platform Services.

For the full evaluation and methodology, download the complete 2026 Gartner Magic Quadrant report and read our lead announcement post.

Your AI strategy is only as good as your data strategy

Your agents are only as powerful as the data they rely upon. Agents need access to your data and shared context to reason accurately and deliver reliable responses.

Today the knowledge agents need is scattered across databases, data lakes, warehouses and third-party applications with no shared context or governance. And the scale of the problem is new. Agents generate 10 to 100x more queries than humans. This means your data architecture must be agent-ready from day one. If it isn’t, your AI investments underperform.

AWS gives your agents an open data foundation with governed context intelligence, built to scale while optimizing the cost of AI. Agentic data capabilities meet industry-specific compliance, security, and schematic requirements so you can move to production with confidence.

An open data architecture for your data and AI

Agents need to discover and access your data, wherever it is stored. That’s why AWS delivers an open architecture on Apache Iceberg so agents can use data across these silos. We offer the broadest native Iceberg support of any major cloud provider, with native Iceberg compatibility across every layer of the data stack – ingestion, storage, catalog, and analytics.

Amazon Simple Storage Service (Amazon S3) supports Apache Iceberg natively. S3 Tables delivers fully managed Apache Iceberg tables that automate compaction and maintenance as data grows. It works with any Iceberg-compatible engine, from Spark to Redshift, and supports natural language queries through MCP.

Amazon SageMaker lakehouse architecture is built with Apache Iceberg. It enables Amazon S3, Amazon Redshift, Amazon OpenSearch Service, Amazon EMR, and Amazon Athena to access the same Iceberg tables through a unified catalog, from a single governance layer. Zero-ETL integrations and federated querying remove remaining barriers across on-premises and third-party cloud sources.

AWS MCP Server, part of the Agent Toolkit for AWS, gives any tool (Amazon Quick, a third-party agent, or a developer’s IDE) governed access to your data through a single path with inherited permissions. It standardizes tool discovery, authentication, and contextual data access for AI agents interacting with AWS services.

AWS embraces open standards for flexibility and the best value. This includes PostgreSQL via Amazon Aurora and Amazon RDS, Apache Kafka via Amazon MSK, OpenSearch via Amazon OpenSearch Service, Apache Spark via Amazon EMR and Trino via Amazon Athena.

From data to contextual intelligence

Agents need more than data access to be accurate. They need contextual understanding of your data and the business rules governing how it should be used before they can make trusted decisions.

This is why we introduced AWS Context, a new service that automatically maps the relationships across your existing data into a knowledge graph and provides agentic search so AI agents in the organization can access governed data relationships, business rules, and domain knowledge at runtime.

For governance, AWS Glue Data Catalog provides a single catalog for AWS and third-party Iceberg tables, while AWS Lake Formation enforces row-, column-, and cell-level access control so the right data reaches the right agent with the right permissions. AWS Glue Data Quality and SageMaker ML Lineage Tracking add the governance layer that production AI demands.

Foundational excellence at scale

Agentic AI workloads require resources that are always available, dynamically allocated, and optimized for price-performance. AWS delivers the most powerful combination of services and capabilities for automatic resource allocation, zero-tuning price performance, and the reliability that millions of customers have trusted for over 20 years.

AWS Databases offer a high-performance, secure foundation to power agentic AI and data-driven applications at any scale. Amazon Aurora delivers unparalleled high performance and availability at global scale for PostgreSQL, MySQL, and DSQL. Amazon DynamoDB and Amazon ElastiCache serve up to tens of billions of requests per second at microsecond to single-digit millisecond latency at any scale, operating at agent speed. With native vector search built into Aurora PostgreSQL, DynamoDB, and ElastiCache, you can perform vector search — from billions to trillions of vectors — and integrate effortlessly across AWS services to build agentic applications.

Amazon S3 has evolved to support the demands of AI with purpose-built storage tiers. S3 Files gives agents a shared file system directly on S3 data, so an entire agent fleet can read inputs, write outputs, and persist memory with no duplicated data and no new APIs to learn. S3 Vectors is the first cloud object store with native support to store and query vectors. It cuts the cost of uploading, storing, and querying vector data by up to 90%, making it practical to build the large-scale vector datasets that give AI agents memory, context, and semantic search.

For search and retrieval, AWS provides purpose-built vector engines that bring intelligent search to your data where it already lives. With OpenSearch Service Serverless, your agents take advantage of lexical, vector, hybrid, and agentic search in a single system with high throughput, low latency, and relevant results at scale.

Key takeaways

The companies moving fastest with AI are the ones that treated data readiness as strategy from the start. We believe the Gartner recognition of AWS as a Leader for 16 consecutive years reflects the breadth and deepest set of core public cloud services and capabilities, including the data foundation that makes this possible.

Ready to see the full evaluation? Download the 2026 Gartner Magic Quadrant for Strategic Cloud Platform Services.

Gartner does not endorse any company, vendor, product or service depicted in its publications, and does not advise technology users to select only those vendors with the highest ratings or other designation. Gartner publications consist of the opinions of Gartner’s business and technology insights organization and should not be construed as statements of fact. Gartner disclaims all warranties, expressed or implied, with respect to this publication, including any warranties of merchantability or fitness for a particular purpose.

Gartner and Magic Quadrant are trademarks of Gartner, Inc., and/or its affiliates.

This graphic was published by Gartner, Inc. as part of a larger research document and should be evaluated in the context of the entire document. The Gartner document is available to download: Complete 2026 Gartner Magic Quadrant report.

Gartner, Magic Quadrant for Strategic Cloud Platform Services, By Alessandro Galimberti, Carolin Zhou, Douglas Toombs, Dennis Smith, Ed Anderson, Tobi Bet, Chuck Lawton , 1 September 2026

About the author

How Sony LIV built real-time video streaming analytics with AWS

Rahul Sureka — Tue, 08 Sep 2026 16:43:05 +0000

This guest post was co-written with Mukund Acharya from Sony LIV.

Real-time data analytics is transforming how streaming applications understand and serve their audiences. In this post, we share how Sony LIV used Amazon Kinesis Data Streams for sub-second processing and AWS services to build a comprehensive streaming analytics solution on AWS. In this post, we share how SonyLIV built a comprehensive streaming analytics solution on AWS using Amazon Kinesis Data Streams for sub-second event ingestion, Amazon Data Firehose for reliable delivery to storage, Amazon EMR with Apache Spark for scalable batch and micro-batch processing, and Apache Iceberg on Amazon S3 for ACID-compliant, queryable data lake tables. Together, these services enable SonyLIV to capture millions of concurrent viewer events, process them cost-efficiently at scale, and surface actionable insights from real-time engagement metrics to historical trend analysis — all within a fully managed, serverless-friendly architecture.

About Sony LIV

Sony LIV, operated by Sony Pictures Networks India, is one of India’s leading over-the-top (OTT) streaming applications offering premium content including live sports, original series, movies, and TV shows to millions of users across mobile, web, and Smart TV devices.

As part of the latest game broadcast rights for Asia Cup, Sony LIV built a streaming analytics solution using AWS services that successfully processed millions of concurrent sessions in real-time.

The challenge of batch processing

As Sony LIV’s audience grew and live sports events attracted increasingly large viewership, the team identified an opportunity to move from batch-based analytics to a real-time data application. The existing architecture processed data reliably in scheduled batches, but the growing scale of live events called for faster, more granular insights. The team set out to address three key areas:

Real-Time Visibility: Track frontend user events, journeys, and conversion funnels in near real-time, especially during high-stakes live sports streams, to see peak concurrent viewership and respond to engagement patterns as they happen.
Comprehensive Quality Monitoring: Implement end-to-end monitoring of video quality key performance indicators (KPIs) including buffering rates, playback failures, video start time, and rebuffering events to proactively enhance the viewer experience.
Unified Data Architecture: Consolidate data from multiple sources into a unified architecture to enable comprehensive customer views, supporting future personalization and machine learning (ML)-driven recommendations.

Sony LIV needed to evolve from reactive batch processing to a real-time data application to deliver actionable engagement insights at scale and establish the foundation for unified customer profiles.

Streaming analytics architecture

Figure 1: Sony LIV streaming analytics architecture on AWS

Data from mobile, web, and Smart TV applications flows through Application Load Balancers to Amazon Elastic Kubernetes Service (Amazon EKS) pods for validation and preprocessing. The architecture implements parallel processing paths to balance speed and cost efficiency.

Real Time Path: Priority events requiring immediate action such as playback failures, user interactions, and live sports engagement signals stream through Amazon Kinesis Data Streams for sub-second processing. These events flow directly to ClickHouse using the ClickHouse connector, enabling real-time analytics with minimal latency.

Batch Path: High-volume batch events are ingested via Amazon Data Firehose into a raw landing zone on Amazon Simple Storage Service (Amazon S3), partitioned by event type and time. Amazon EMR running Apache Spark then processes this raw data performing schema validation, deduplication, and transformations — and writes the curated output as Apache Iceberg tables back to S3

Unified Data Layer: AWS Glue catalogs data across streaming and batch sources, creating a unified schema that powers the customer data application. This unified data layer serves as the foundation for building comprehensive customer profiles and enabling personalized recommendations.

Analytics and Monitoring: ClickHouse serves as the low-latency analytics engine, powering real-time dashboards and video quality monitoring. Operations teams track critical KPIs including peak concurrent viewership, buffering rates, video start time, and playback failures, enabling rapid response to issues during live events.

Custom dashboards and Datadog provide visualization for business metrics and application performance monitoring, while Amazon CloudWatch tracks infrastructure health, delivering end-to-end visibility across the application.

Results and business impact

The results: product, analytics and engineering teams now operate from a single unified dashboard, making real-time decisions with data that is seconds, not hours old. Auto scaling and serverless design have optimized costs while providing reliability at peak load. At the foundation, a centralized data catalog now serves as a single source of truth for business intelligence, machine learning workloads and operational monitoring.

The transformation helped Sony LIV process millions of concurrent sessions in real time, particularly during high-profile events such as the Asia Cup 2025.

Accelerated insights: Data latency dropped from hours to seconds, enabling instant insights into viewer behavior, content performance, and streaming health.
Unified decision-making: Product, analytics, and engineering teams now use unified dashboards for real-time decision-making.
Optimized operations: The solution auto scaling and serverless design optimized costs while supporting reliability and fault tolerance.

Beyond performance gains, the new architecture established a strong foundation for personalized content recommendation and operational agility. By integrating a centralized data catalog and scalable analytics layer, the solution now provides a single source of truth for business intelligence, machine learning workloads, and monitoring.

Future innovations

Sony LIV plans to expand its analytics capabilities by integrating advanced ML models for real-time recommendations, user churn prediction, and anomaly detection. The team will also focus on building unified customer profiles to enable hyper-personalized experiences.

The AWS based architecture provides a strong foundation for future growth and innovation, enabling Sony LIV to deliver highly personalized experiences to an expanding viewer base.

Conclusion

By adopting AWS, Sony LIV transformed its analytics architecture into a real-time, scalable, and insight-driven solution. The solution reduced data latency from hours to seconds, enabled processing of millions of concurrent sessions during peak events, and positioned Sony LIV as a leader in streaming analytics innovation in India.

To learn more about how AWS can help your media organization implement real-time streaming analytics solutions, explore the following resources:

AWS for Media & Entertainment – Discover how AWS powers content delivery, streaming, and analytics for media companies worldwide.
Amazon Kinesis Data Streams – Learn about the fully managed service for real-time data streaming used in this solution.
Data Streaming Analytics on AWS (AWS Blog) – Read related posts on how customers are building streaming analytics pipelines on AWS.

About the authors

From silos to insights: Federated data access patterns for AI agents

James Wu — Fri, 04 Sep 2026 21:35:21 +0000

Enterprise data today is scattered across specialized systems, each with its own tools and expertise. Querying a database requires SQL. Accessing batch data on Amazon Simple Storage Service (Amazon S3) requires compute engines such as Amazon Athena and Trino. Consuming real-time streams from Amazon Kinesis requires streaming expertise. Each software as a service (SaaS) application has its own API, authentication model, and query language. Today, only data engineers can navigate this landscape, and business users file tickets, wait for reports, or rely on dashboards that answer yesterday’s questions. When a leader needs a one-time answer spanning multiple systems, they’re back in the ticket queue.

Consider a streaming media company: customer profiles, content catalogs, and ad campaign performance are stored as batch data on Amazon S3. Viewership telemetry such as device type, stream quality, watch duration, and buffering events flows in real time through Amazon Kinesis. Subscriber management and support tickets live in a relational customer relationship management (CRM) database. Leaders routinely ask questions like:

Which titles drove the most subscriber growth last quarter?
How does marketing spend correlate with viewing completion rates?
Is churn spiking among users who haven’t engaged with new content?

Answering these questions faces two challenges:

The data silo problem. The data lives in multiple places with batch stores on S3, real-time streams in Kinesis, and an online transaction processing (OLTP) database, each with its own access patterns, query language, and authentication model. Organizations traditionally solve this by building data lakes or adopting a data mesh, but both require significant data engineering investment and ongoing maintenance.

The access gap. The expertise to navigate the enterprise systems is concentrated in the hands of few data engineers, creating a bottleneck that no dashboard or business intelligence (BI) tool fully resolves. Every new one-time requirement means more engineering work, and it’s not self-service.

A fundamentally different approach is emerging: instead of moving all data to one place or building bespoke integrations for each source, let AI agents talk directly to the systems where data lives. Model Context Protocol (MCP) makes this possible, an open protocol that standardizes how AI applications connect to external data sources and tools. MCP servers wrap diverse systems behind a uniform interface for tool discovery, invocation, and response handling. Any user can ask a question in natural language and the agent reaches the right data without knowing which system holds it, what API to use, or what query language is required.

In this post, we propose reference architectures for accessing data stored in different systems and datastores using MCP and Amazon Bedrock AgentCore. The patterns apply to enterprises with mixed data sources, but we ground the narrative in our streaming media company example described earlier to make the problem concrete.

Solution overview

Our solution is a federated data foundation for a streaming media company. It supports real-time and batch analytics using MCP servers and Amazon Bedrock AgentCore, and it makes analytics accessible across the organization. The following reference architecture shows the complete picture from data ingestion through governance and compute layers to the generative AI layer where agents orchestrate across MCP servers. The demo uses synthetic data: batch datasets are generated with Python scripts, and streaming telemetry is produced by AWS Lambda. The complete source code is available in the accompanying GitHub repository, so you can deploy and try it yourself.

Figure 1: Reference architecture for federated data access across batch, streaming, and relational sources

Walkthrough

This section covers the prerequisites and then walks through how a user request flows end to end through the reference architecture.

Prerequisites

AWS account with access to Amazon Bedrock AgentCore and other AWS services. For more information, review Permissions for AgentCore Runtime documentation.
AWS Command Line Interface (AWS CLI) configured.
Python 3.10 or later.
Docker or Finch installed.

Request flow

User request: A user submits a natural-language question through a React application served by Amazon CloudFront with static assets on Amazon S3.
Authentication: Amazon Cognito authenticates the user and issues an identity token that travels with the request to the agent layer.
Agent orchestration: The request reaches a Strands agent running on AgentCore runtime, a capability of Amazon Bedrock AgentCore. The agent reasons over the question and determines which data sources to query.
Gateway routing: Amazon Bedrock AgentCore Gateway, a capability of Amazon Bedrock AgentCore, aggregates all three MCP servers behind a single endpoint, handling tool discovery, authentication, and routing.
MCP server execution: The agent routes the query to the appropriate MCP server(s), each running on Amazon Bedrock AgentCore runtime behind Amazon Bedrock AgentCore Gateway. The Data Processing MCP server queries AWS Glue Data Catalog and Amazon Athena for batch and streaming data on S3, the Amazon Aurora MCP server translates tool calls into SQL against the Amazon Aurora MySQL CRM database, and the AWS Documentation MCP server provides AWS service context.
Data sources: The architecture deliberately spans multiple storage systems to reflect how enterprise data is typically fragmented across teams and technologies. Batch data (customer profiles, content titles, and ad campaigns) is generated by AWS Lambda on an Amazon EventBridge schedule and lands as Parquet files on Amazon S3. Streaming viewership telemetry (what users watch, when they pause, where they drop off) flows through Amazon Kinesis Data Streams and Amazon Data Firehose to S3. CRM records (subscriber plans, support tickets, account status) live in an Amazon Aurora MySQL database. AWS Glue Data Catalog registers the S3-based sources under a unified metadata layer, and AWS Lake Formation enforces fine-grained access policies across the catalog. This mix of batch, streaming, and relational sources is what makes federated access essential. No single query engine can reach all datasets natively.
Response: Results flow back through Amazon Bedrock AgentCore Gateway to the agent, which composes a natural-language answer and delivers it to the user through the front end.

For deploying our reference architecture, follow the instructions in the code repository.

Design patterns for federated data access

Within our architecture, we propose three design patterns for federated data access, each on a spectrum between centralized governance and direct access flexibility.

Pattern 1: Catalog-first access

AWS Glue Data Catalog registers all S3 sources under a unified metadata layer: schemas, business context, data quality metrics, and lineage. The AWS Data Processing MCP server, hosted on Amazon Bedrock AgentCore runtime, wraps AWS Glue Catalog metadata and Amazon Athena query capabilities behind standard MCP tool calls. So when a user asks “Which ad campaigns drove the most subscriber activations last quarter?”, the agent discovers tables through catalog tools and resolves business terms from column metadata. It then executes the join through Athena without ever calling a Glue API directly.

The following diagram traces how a single user request flows through the federated data access architecture: from the agent, through the MCP server, and down to the data in Amazon S3.

Figure 2: Request flow for the catalog-first access pattern

Internally, our agent built using Strands Agent framework has three components: a system prompt, a large language model (LLM), and a set of MCP tools. We use Claude Haiku 4.5 powered by Amazon Bedrock as the foundation LLM with tools discovered through the Amazon Bedrock AgentCore Gateway. The system prompt teaches the agent how to use those tools not by listing every column in every table, but by providing intent-based routing rules and a mandatory schema discovery workflow. Here’s an extract from the system prompt:

TOOL DISCOVERY & ROUTING:

You access tools via the MCP Gateway. Use x_amz_bedrock_agentcore_search
to find the right tool by keyword when unsure.

Routing by intent:
- Telemetry/streaming/viewing data → Glue catalog tools, then Athena query tools
- CRM/support tickets/ratings → MySQL tools (run_query, get_table_schema)
- AWS service questions → documentation search tools

SCHEMA DISCOVERY (MANDATORY before writing SQL):

Before writing any Athena query, retrieve the table schema:
→ Use manage_aws_glue_tables with operation='get-table',
database_name='acme_telemetry', table_name='<table>'

This returns all columns, data types, partition keys, and storage details.

To see this in action, consider what happens when a user asks “How many streaming events in February 2026 by event type?”:

The agent’s routing rules match “streaming events” to the AWS Glue Catalog and Athena query path. If unsure which tool to use, the Gateway’s semantic search discovers tools by keyword rather than requiring exact names.
The agent calls manage_aws_glue_tables exposed by the Data Processing MCP server to retrieve the full schema: column names and types, partition keys (year, month, day, hour), and storage format.
With the schema in hand, the agent writes Presto/Trino SQL with partition filters (WHERE year='2026' AND month='02').
The agent executes the query, retrieves results, and composes a natural-language answer. The user never sees SQL, Glue APIs, or partition strategies.

This discover-then-query workflow is what makes the pattern self-service. The Amazon Bedrock AgentCore Gateway provides unified tool discovery as new MCP servers appear without updating routing logic. The AWS Glue Data Catalog provides a live metadata layer for new tables and columns to appear immediately.

This pattern isn’t unique to AWS. Other platforms adopt the same model. For example, Databricks offers managed MCP servers for Unity Catalog, letting agents discover and query governed datasets, AI models, and functions registered in Unity Catalog. The common trade-off across all of them: all data must be cataloged before agents can access it, which can bottleneck rapidly changing environments.

Figure 3: Catalog-first access with AWS Glue Data Catalog and Amazon Athena

Pattern 2: Direct source access

Agents access source systems directly through dedicated MCP servers (no intermediate catalog). The Aurora MCP server, hosted on Amazon Bedrock AgentCore runtime, queries the Amazon Aurora CRM database directly. Therefore, a question like “How many open support tickets from premium subscribers?” routes to the MCP server, which translates the tool call into SQL against Aurora. The agent never constructs a database connection or manages credentials. The MCP server handles authentication through AWS Secrets Manager and exposes only two tools: run_query for SQL execution and get_table_schema for schema inspection.

Figure 4: Direct source access to the Amazon Aurora CRM database

Internally, the same agent architecture as Pattern 1 applies: a system prompt, an LLM, and a set of MCP tools. We use Claude Haiku 4.5 powered by Amazon Bedrock as the foundation LLM with tools discovered through the Amazon Bedrock AgentCore Gateway. There’s no catalog layer to query first. The system prompt provides lightweight schema hints: table names and key enum values needed for WHERE clauses so the agent can route correctly and write valid filters without a round trip:

MYSQL CRM DATA (Aurora MySQL via RDS Data API):

Database: acme_crm

Tables:
- support_tickets: status (open|in_progress|resolved|closed),
  priority (low|medium|high|critical),
  category (billing|technical|content|account)
- content_ratings: rating (1-5), review_text

Use get_table_schema to verify full column details before complex queries.
Use run_query(sql='SELECT...') to execute. Default to read-only SELECT.
Use standard MySQL syntax (not Presto/Trino).

For straightforward queries, the agent writes SQL directly from these hints. For complex queries such as multi-table joins or unfamiliar columns, the agent calls get_table_schema first to verify the full schema, mirroring the discover-then-query discipline from Pattern 1 but against the source database rather than a catalog. To see this in action, consider “Show me open critical support tickets by category”:

The agent’s routing rules match “support tickets” to the MySQL CRM path and call run_query with a SELECT against support_tickets filtered by status='open' and priority='critical'.
The Aurora MCP server translates this into a query against Amazon Aurora through the RDS Data API.
Results return through the AgentCore Gateway and the agent composes a formatted answer with ticket counts, categories, and so on.

The direct access pattern trades catalog governance for simplicity. There’s no metadata registration step. The MCP server queries the database as-is, which means schema changes in Aurora are immediately visible. This makes it ideal for operational databases where the schema is stable and well-understood, and where the overhead of cataloging every table would slow down access without adding value.

Earlier this year, the AWS MCP Server became generally available. It’s part of the Agent Toolkit for AWS, a suite of tooling that includes the MCP Server, skills, and plugins that help coding agents build more effectively and efficiently on AWS. Rather than exposing a fixed set of per-service tools, the server provides generic AWS API access: aws___run_script executes Python in a sandboxed environment with credentialed access to the AWS APIs, authenticated with SigV4 and authorized by your existing AWS Identity and Access Management (IAM) policies. Because that reaches most of AWS APIs, you can connect your agents to relational data in Aurora through the RDS Data API or to real-time streaming data in Kinesis Data Streams, using boto3 calls such as GetShardIterator and GetRecords.

Pattern 3: Hybrid access

In practice, most organizations won’t pick only one pattern because the data landscape is too diverse. That’s exactly the case for our streaming media company: batch and streaming data on S3 benefits from catalog-first governance (Pattern 1), while the Aurora CRM database is better served by direct access (Pattern 2). Our reference architecture combines both patterns under a single orchestrator agent. Governed sources route through the catalog. Operational sources are accessed directly and both paths coexist behind the same agent. The key insight: both paths use the same protocol. Amazon Bedrock AgentCore runtime hosts the MCP servers, and AgentCore Gateway handles tool discovery, authentication, and routing. Organizations can start with whichever pattern fits their current data maturity and grow into unified access as they onboard more sources.

Validate the deployment

Access the CloudFront URL from the stack outputs, log in with your test user credentials, and try these queries:

Query 1 – Customer analytics with visualization:

“Build a chart on customer breakup by subscription type?”

The agent queries the customers table in Athena and generates bar and pie charts showing the distribution across subscription tiers.

Figure 5: Customer distribution across subscription tiers

Query 2 – CRM operational breakdown:

“Show me the breakdown of support tickets by category and priority.”

This routes entirely to the MySQL MCP server, querying the Aurora CRM database for ticket distribution without touching S3 or Athena.

Figure 6: Support ticket breakdown by category and priority

Query 3 – Federated cross-source query:

“What are the top five highest-rated titles and how many streaming hours do they have?”

This requires the agent to query content_ratings from Aurora for ratings, then correlate with streaming_events and titles in Athena.

Figure 7: Top five highest-rated titles and their streaming hours

Things to consider

Consider these additional factors when you deploy the preceding architecture patterns to production:

Application security: Our architecture patterns use Amazon Cognito for identity access and control. However, you should carefully review the identity used by the agent to interact with backend systems.
Data lineage and access control: Consider using AWS Lake Formation for data governance, authentication, and authorization of data assets in the agentic AI application.
Semantic layer for agents: Agentic response quality can be improved by providing agents with the right business context and building an independent semantic layer. AWS has recently announced support for business context and semantic search. This can help the agent discover and understand data by semantic meaning, improve response quality and avoid hallucination, and many other issues.

Clean up

To avoid ongoing charges, destroy both AWS Cloud Development Kit (AWS CDK) stacks (agent stack first, then data stack) and remove any orphaned resources such as Kinesis streams and Amazon CloudWatch log groups. For detailed clean-up instructions, visit the repository’s README.

Conclusion

Enterprise data stays locked behind silos and an access gap. Every one-time question routes through a handful of data engineers while the insight goes stale. MCP flips the model. Instead of centralizing data or wiring bespoke integrations, you deploy MCP servers that wrap each source behind a standardized protocol and let AI agents query them on behalf of the user. Whether you choose catalog-first access, direct access, or both unified behind a single agent, the agent navigates the complexity so the user doesn’t have to. Adding a new data source means deploying a new MCP server, not redesigning the pipeline.

Open questions remain, for example, data lineage across agent-composed outputs, identity and authorization when agents are the primary data consumers, and audit trails that capture not only what an agent accessed but why. This landscape is growing fast: AWS Labs MCP Servers, AWS MCP documentation, and the MCP Gateway Registry.

Deploy the reference architecture, experiment with the patterns, and contribute back what you learn.

Acknowledgements

We would like to thank Yadgiri Pottabathini for his effort in testing the repository.

About the authors

Network connectivity patterns for the next generation of Amazon OpenSearch Serverless

Salman Ahmed — Thu, 03 Sep 2026 16:26:58 +0000

Network connectivity patterns for private access to Amazon OpenSearch Serverless used to require considerable setup. You had to create virtual private cloud (VPC) endpoints in every consumer VPC and configure Amazon Route 53 Profiles for cross-account DNS. You also had to maintain custom private hosted zones with CNAME records and deploy resolver inbound endpoints for on-premises connectivity. The next generation of OpenSearch Serverless changes this. It uses standard AWS PrivateLink interface endpoints with native private DNS support. Connectivity patterns that previously required multi-step DNS orchestration now work with the same endpoint mechanics you already use for other AWS services.

Collections use resource-based endpoints on the on.aws domain in two formats. The per-collection endpoint (<collectionId>.aoss.<region>.on.aws) reaches a single collection, and the hostname itself identifies which collection you want, so no additional routing information is needed. The per-account Regional endpoint (<accountId>.aoss.<region>.on.aws) reaches any collection in your account through one hostname. Because the hostname alone does not identify a specific collection, you add the x-amz-aoss-collection-name header (or x-amz-aoss-collection-id) to each request to name the target collection. The AWS SDKs include this header automatically when they sign the request with Signature Version 4 (SigV4).

Both formats use standard AWS PrivateLink. You create the VPC endpoint from the Amazon Virtual Private Cloud (Amazon VPC) console or the Amazon Elastic Compute Cloud (Amazon EC2) CreateVpcEndpoint API, using the service name com.amazonaws.<region>.aoss-data. It is the same interface endpoint you create for any other AWS service.

In this post, each pattern shows the architecture, the DNS resolution flow, and the data traffic path. Patterns 1 through 8 operate within a single Region across one or more accounts, labeled Region A in the diagrams, so the repeated Region A boxes in a cross-account pattern are the same Region. Only Pattern 9 spans Regions, shown as Region A and Region B.

These patterns apply to the collection (data) endpoint only. When you create a collection, you also receive an OpenSearch UI endpoint. That endpoint uses a separate PrivateLink mechanism today, with its own VPC endpoint and access policy, and is on a path to move to the standard PrivateLink model. OpenSearch UI connectivity is out of scope for this post.

Prerequisites

A collection group and at least one collection. Create it on the console with Express Create, or with the AWS Command Line Interface (AWS CLI) using --generation NEXTGEN.
A network access policy that lists the VPC endpoints allowed to reach the collection.
A data access policy that grants AWS Identity and Access Management (IAM) principals permission to index and query.

DNS resolution

When you create a standard VPC endpoint for com.amazonaws.<region>.aoss-data with private DNS enabled, AWS creates a private hosted zone for *.aoss.<region>.on.aws and associates it with your VPC. This zone maps collection hostnames to the endpoint’s private elastic network interface (ENI) IP addresses. Your compute’s DNS query reaches the VPC’s Amazon Route 53 Resolver at VPC+2, which resolves the hostname to ENI IPs.

One endpoint serves every collection hostname in the Region. The following AWS CLI command creates that interface endpoint, and the --private-dns-enabled flag turns on the private DNS resolution described here.

aws ec2 create-vpc-endpoint \
  --vpc-id vpc-abc123 \
  --service-name com.amazonaws.us-east-1.aoss-data \
  --vpc-endpoint-type Interface \
  --subnet-ids subnet-111 subnet-222 \
  --security-group-ids sg-xxx \
  --private-dns-enabled

In Regions that support Federal Information Processing Standards (FIPS), the same endpoint also resolves *.aoss-fips.<region>.on.aws for FIPS-compliant access.

OpenSearch Serverless has no per-collection Dashboards endpoint. Use OpenSearch UI applications to explore and visualize collection data.

The diagrams in the following patterns use an Amazon EC2 instance to represent the compute client. Any compute in the VPC reaches a collection the same way, including EC2 instances, AWS Lambda functions attached to the VPC, and containers on Amazon Elastic Container Service (Amazon ECS) or Amazon Elastic Kubernetes Service (Amazon EKS). The connectivity, DNS resolution, and access policies are the same regardless of the compute type.

Pattern 1: Private access from a single VPC

Compute in a VPC needs private access to collections in the same account. The following diagram shows the architecture for private access from a single VPC.

Figure 1: Private access from a single VPC

Create a standard VPC endpoint in the VPC where your compute runs, then reference its ID in the collection’s network policy.

For the DNS resolution flow, (1) compute queries <collectionId>.aoss.<region>.on.aws, and the VPC Route 53 Resolver at VPC+2 returns the endpoint ENI IP addresses because private DNS is enabled on the endpoint.

For the data traffic path, (2) compute connects to the ENI, and (3) PrivateLink forwards the request to the service, which routes to the collection by hostname.

Pattern 2: Multiple VPCs in the same account

Several VPCs, split by environment, tier, or team, need private access to the same collections. The following diagram shows how each VPC uses its own endpoint to reach the same collections.

Figure 2: Multiple VPCs in the same account

Each VPC needs exactly one aoss-data endpoint with private DNS enabled, and that single endpoint already reaches every collection in the Region. DNS resolves independently within each VPC, so there is no cross-VPC DNS dependency. Adding a new VPC takes two steps. Create the endpoint, then add its endpoint ID to the collection’s network policy. Do not create a second aoss-data endpoint with private DNS enabled in the same VPC. Both endpoints share the same private hosted zone, which causes a conflict and the creation fails.

For the DNS resolution flow, (1) compute in each VPC queries the collection hostname, and that VPC’s Route 53 Resolver at VPC+2 returns the endpoint ENI IP addresses because private DNS is enabled on the endpoint.

For the data traffic path, (2) compute connects to its local ENI, and (3) PrivateLink forwards the request to the service, which routes to the collection by hostname.

Pattern 3: On-premises access from a single account

On-premises clients reach collections over AWS Direct Connect or AWS Site-to-Site VPN, which connect to the VPC through AWS Transit Gateway or AWS Cloud WAN. The following diagram shows the DNS and data path for on-premises access.

Figure 3: On-premises access from a single account

On-premises DNS servers sit outside the VPC and cannot resolve PrivateLink private DNS names directly. Place an Amazon Route 53 Resolver inbound endpoint in the VPC that holds the aoss-data VPC endpoint. On-premises DNS forwards queries for aoss.<region>.on.aws to that inbound endpoint. The inbound endpoint resolves them against the private hosted zone. The inbound endpoint’s security group must allow TCP/UDP port 53 from your on-premises resolver ranges.

For the DNS resolution flow, (1) the client queries the on-premises resolver. (2) The on-premises conditional forwarder for *.aoss.<region>.on.aws sends the query over Direct Connect or VPN, through Transit Gateway or Cloud WAN, to the inbound endpoint. The inbound endpoint uses the VPC Route 53 Resolver to return the endpoint’s private ENI IPs.

For the data traffic path, (3) the client sends an HTTPS request with the Transport Layer Security (TLS) Server Name Indication (SNI) header set to the collection hostname, over Direct Connect or VPN through Transit Gateway or Cloud WAN. (4) Traffic crosses the VPC’s attachment ENI, (5) reaches the VPC endpoint ENIs, and (6) PrivateLink forwards the request to the service.

Pattern 4: Cross-account access with an endpoint in each consumer VPC

A central account hosts collections, and compute in spoke accounts needs private access. Many enterprises start here. The following diagram shows the cross-account endpoint architecture.

Figure 4: Cross-account access with an endpoint in each consumer VPC

Each spoke creates its own endpoint. The collection owner’s network policy references the spoke’s endpoint ID. The data access policy grants the spoke’s IAM role. PrivateLink carries the traffic end to end, with no Transit Gateway and no peering.

The endpoint lives in the spoke account, not the collection account. The spoke team creates a standard interface VPC endpoint in the spoke VPC for the service name com.amazonaws.<region>.aoss-data with private DNS enabled. The collection owner does not create this endpoint. After the endpoint is ready the spoke shares its endpoint ID with the collection owner, who adds that ID to the collection network policy under SourceVPCEs. A network policy accepts endpoint IDs from accounts across your organization. Each spoke creates its own endpoint and shares the ID rather than peering VPCs or routing through another account’s endpoint.

Network access and data access stay separate. The network policy authorizes the endpoint, and the data access policy authorizes the identity. A serverless data access policy grants principals from the collection’s own account. For a spoke in another account, you create an IAM role in the collection account and grant that role in the data access policy. The spoke role then assumes it to sign requests.

The following network access policy lists the two spoke endpoint IDs under SourceVPCEs and sets AllowFromPublic to false, so only those endpoints reach the collection and the policy denies public access.

[
  {
    "Description": "Cross-account access from spoke",
    "Rules": [
      {
        "ResourceType": "collection",
        "Resource": [
          "collection/my-collection"
        ]
      }
    ],
    "AllowFromPublic": false,
    "SourceVPCEs": [
      "vpce-spoke-b-id",
      "vpce-spoke-c-id"
    ]
  }
]

For the DNS resolution flow, (1) spoke compute queries the VPC Route 53 Resolver at VPC+2, which returns the local endpoint ENI IPs because private DNS is enabled on the endpoint.

For the data traffic path, (2) compute connects to the local ENI. (3) PrivateLink forwards the request to the service, which checks the network policy for the endpoint ID and the data access policy for the IAM role before routing. Adding a spoke takes one API call and two policy edits.

Pattern 5: Centralized shared endpoint with Amazon Route 53 Profiles over Transit Gateway

You want fewer PrivateLink endpoints, so you run one shared endpoint in a networking VPC and reach it from spoke accounts over Transit Gateway or AWS Cloud WAN, with no endpoint in each spoke. The following diagram shows this centralized architecture.

Figure 5: Centralized shared endpoint with Amazon Route 53 Profiles over Transit Gateway

Pattern 5 consolidates access through a single shared endpoint in a central networking VPC rather than creating one per spoke. Because spoke VPCs have no local endpoint, they cannot resolve *.aoss.<region>.on.aws on their own. You share the endpoint’s private DNS with spoke VPCs using Amazon Route 53 Profiles, shared through AWS Resource Access Manager (AWS RAM). This is the one pattern where you still manage DNS propagation.

For the DNS resolution flow, (1) the spoke resolves the hostname through the shared Route 53 Profile, which returns the networking-VPC endpoint ENI IPs.

For the data traffic path, (2) traffic leaves the compute through the spoke VPC’s attachment ENI, (3) crosses Transit Gateway or Cloud WAN into the networking VPC’s attachment ENI, (4) reaches the shared endpoint ENIs, and (5) PrivateLink forwards the request to the service.

Pattern 6: Cross-account centralized networking with on-premises

A central account hosts collections. A separate networking account owns Direct Connect or VPN and Route 53. On-premises clients reach the collections through the networking account. The following diagram shows this architecture.

Figure 6: Cross-account centralized networking with on-premises

The networking account runs the standard VPC endpoint and a Route 53 Resolver inbound endpoint. The collection owner’s network policy references the networking account’s endpoint ID.

For the DNS resolution flow, (1) the on-premises client queries its resolver. (2) The on-premises conditional forwarder sends the query over Direct Connect or VPN, through Transit Gateway or Cloud WAN, to the inbound endpoint. The inbound endpoint uses the VPC Route 53 Resolver to return the endpoint’s private ENI IPs.

For the data traffic path, (3) the client sends HTTPS over Direct Connect or VPN, through Transit Gateway or Cloud WAN. (4) Traffic crosses the networking VPC’s attachment ENI, (5) reaches the networking-VPC endpoint ENIs, and (6) PrivateLink forwards the request to the service in the central account. The two teams coordinate through one artifact, the endpoint ID.

Pattern 7: Distributed multi-business-unit with spoke-account access

Spoke accounts such as analytics or application teams need collections spread across several business unit accounts, and each unit manages its own collections. The following diagram shows the distributed multi-business-unit architecture.

Figure 7: Distributed multi-business-unit with spoke-account access

Each spoke creates one standard endpoint, which resolves every collection hostname in the Region. Each business unit’s network policy lists the spoke endpoint IDs. Access control decides which collections a spoke reaches. DNS does not.

For the DNS resolution flow, (1) spoke compute queries the VPC Route 53 Resolver at VPC+2, which returns the endpoint ENI IPs because private DNS is enabled on the endpoint.

For the data traffic path, (2) compute connects to the local ENI, and (3) PrivateLink forwards the request to the service, which routes to the correct business unit collection by hostname.

Action	Required change
New collection in any BU	No networking change is needed because in the network policy `collection/*` wildcard, already covers any new collection
New spoke account	Spoke creates an endpoint, and BUs add its ID to their policies
Remove spoke access	BUs remove the endpoint ID and the IAM principal

Pattern 8: Distributed multi-business-unit with on-premises access

Several business units own collections in separate accounts. On-premises clients reach collections across all of those accounts through a central networking account. The following diagram shows this architecture.

Figure 8: Distributed multi-business-unit with on-premises access

The networking account runs one standard endpoint that resolves *.aoss.<region>.on.aws hostnames, regardless of which account owns the collection. Each business unit’s network policy includes the networking endpoint ID.

For the DNS resolution flow, (1) the on-premises client queries its resolver. (2) The on-premises conditional forwarder for *.aoss.<region>.on.aws sends the query over Direct Connect or VPN, through Transit Gateway or Cloud WAN, to the networking VPC’s inbound endpoint. The inbound endpoint uses the VPC Route 53 Resolver to return the shared endpoint’s private ENI IPs.

For the data traffic path, (3) the client sends HTTPS over Direct Connect or VPN, through Transit Gateway or Cloud WAN. (4) Traffic crosses the networking VPC’s attachment ENI, (5) the request arrives at the shared endpoint ENIs, and (6) the service routes to business unit 1 or business unit 2 by hostname, as long as that business unit’s policy lists the networking endpoint ID. Adding a collection in any business unit needs no networking change if the network policy uses a collection/* wildcard, since the wildcard already covers it.

Pattern 9: Cross-Region access strategies

Consumers in Region B need data that lives in collections in Region A. The following diagram shows cross-Region access strategies.

Figure 9: Cross-Region access strategies

Collections are Regional. No built-in cross-Region endpoint or replication exists. Deploy independent collections in each Region, each with its own endpoint and policies, then synchronize data with one of these approaches.

Dual-write. The application writes to both Regions at ingestion time.
Amazon OpenSearch Ingestion pipeline. A pipeline replicates index operations to the secondary Region with near real-time lag. The pipeline creates its own PrivateLink endpoint to the destination collection. It adds the endpoint to that collection’s network policy automatically. You only need to name the network policy and grant the pipeline role.
Amazon Simple Storage Service (Amazon S3) Cross-Region Replication with re-ingestion. Cross-Region Replication copies objects, and an OpenSearch Ingestion pipeline loads them into the local collection. Lag runs in minutes, at the lowest cost of these approaches.

For the DNS resolution flow, DNS resolves locally in each Region, the same as Pattern 1. Each collection hostname carries its Region, so a hostname in Region A resolves through Region A’s own endpoint and a hostname in Region B resolves through Region B’s own endpoint, with no cross-Region DNS.

For the data traffic path, (1) compute in each Region uses that Region’s own endpoint to reach its local collection. Writes land in the primary Region and the sync approach you choose replicates them to the secondary Region, where local readers query the replica. The replicate arrow shows that cross-Region movement, such as an OpenSearch Ingestion pipeline that writes into the secondary-Region collection.

Scale-to-zero changes the economics. An idle secondary-Region collection costs only storage until requests arrive.

Summary

Pattern	Components
1. Same VPC	Standard endpoint and network policy
2. Multiple VPCs	Endpoint per VPC and a policy listing all IDs
3. On-premises	Endpoint, Route 53 inbound endpoint, on-premises forwarder, and Transit Gateway or Cloud WAN
4. Cross-account	Endpoint per consumer, network policy, and data policy
5. Centralized shared endpoint	Shared endpoint, Route 53 Profiles through RAM, and Transit Gateway or Cloud WAN
6. Central networking with on-premises	Networking endpoint, Route 53 inbound, forwarder, Transit Gateway or Cloud WAN, and policies
7. Multi-BU with spoke access	Endpoint per spoke, and each BU policy lists spoke IDs
8. Multi-BU with on-premises	One networking endpoint reached through Transit Gateway or Cloud WAN, and each BU policy lists its ID
9. Cross-Region	Independent collections per Region and a data-sync approach

Across each private pattern, the VPC endpoint resolves all *.aoss.<region>.on.aws hostnames through standard PrivateLink private DNS. Network policies control which endpoints reach a collection, and data access policies control which principals operate on the data. Only Pattern 5 asks you to manage DNS.

Cost considerations

The connectivity pattern you choose drives recurring cost, so match it to your scale instead of adding infrastructure you do not need. The two charges that come up most often, a Route 53 Resolver inbound endpoint and Route 53 Profiles, are both optional for access that stays inside AWS.

A Route 53 Resolver inbound endpoint is needed only for the on-premises patterns (3, 6, and 8), where an on-premises resolver forwards queries into the VPC. Traffic that stays inside AWS never uses it. Route 53 Profiles apply only when a VPC has no endpoint of its own, as in Pattern 5, where the profile carries the shared endpoint’s private DNS to the spoke. When each VPC runs its own interface endpoint, DNS resolves locally through the VPC Route 53 Resolver at no extra charge, so neither the inbound endpoint nor a profile is required.

For most multi-account and multi-Region deployments, an interface endpoint in each consumer VPC (Pattern 4) is the least complex and often the least expensive option. You pay for the interface endpoints you already need for private access, and local DNS resolution adds nothing. Because collections are Regional and each Region resolves on its own, this scales across Regions with no cross-Region DNS.

Centralizing on one shared endpoint (Pattern 5) lowers the number of interface endpoints. However, it adds Transit Gateway or Cloud WAN data processing charges and the cost of sharing DNS. You share that DNS either through Route 53 Profiles or through a private hosted zone that you associate across accounts and maintain yourself. A smaller endpoint count is not automatically cheaper because transit data processing can exceed the savings. Compare both designs against your own traffic before you decide.

Scale to zero also shapes cost. An idle collection, such as a secondary-Region replica in Pattern 9, releases its compute and bills only for storage until requests arrive. For current rates, see AWS PrivateLink pricing, Amazon Route 53 pricing, and Amazon OpenSearch Service pricing.

Conclusion

OpenSearch Serverless uses standard AWS PrivateLink for private connectivity. You create a VPC endpoint, enable private DNS, and reference the endpoint ID in your network policy. The model scales from single-VPC access to multi-account and multi-business-unit designs, and only Pattern 5 adds DNS infrastructure, where you share the endpoint’s private DNS with Route 53 Profiles. The per-account regional endpoint goes further and serves any collection in an account through one hostname and connection pool. To get started, create your first collection in the OpenSearch Serverless console, or explore the OpenSearch Serverless documentation for detailed API references and tutorials.

About the authors

How Moovit achieved 33% cost optimization through architectural modernization

Saar Porat — Thu, 03 Sep 2026 16:23:46 +0000

Moovit, part of Mobileye (Nasdaq: MBLY), is a leading Mobility-as-a-Service (MaaS) solutions provider and the creator of a leading urban mobility app. Moovit’s iOS, Android, and web apps offer users a smart mobility experience to get to their destination using any mode of public and shared transportation. Transit riders can benefit from mobile ticketing to plan, pay, and ride with transit services. Introduced in 2012, Moovit now serves over 1.7 billion users in more than 3,500 cities across 112 countries, in 45 languages.

Behind these user-facing experiences is a data platform that processes large volumes of mobility, application, and operational data to support product analytics, business intelligence (BI), monitoring, and data science. As the platform grew, Moovit needed to keep analytical workloads reliable and cost-efficient without slowing down teams that depend on fresh data every day.

Over several years, Moovit’s Amazon Redshift cluster grew continuously. It started with an expanding fleet of DC2 nodes, migrated to RA3 nodes, and scaled multiple times to keep pace with growing data demands, ultimately becoming the backbone of their entire data platform.

To address this growth, Moovit transformed their data architecture by building an optimal multi-engine lakehouse architecture and assigning each workload to the most suitable option. This modernization reduced their Amazon Redshift cluster by 50 percent, while establishing a flexible, multi-engine architecture ready for future use cases.

In this post, we share how Moovit gained visibility into workload patterns, cleaned up unnecessary load, selected candidates for offloading, and ran a successful proof of concept (POC) on Amazon EMR Serverless. Moovit ultimately divided the workload between multiple engines, building a modern and cost-optimized data platform that combines provisioned Amazon Redshift, Amazon Redshift Serverless, and Amazon EMR.

The challenge: Outgrowing a single-engine data platform

The Amazon Redshift engine handled a wide variety of workloads, including:

Heavy ETL processing: Raw data ingestion from Amazon Simple Storage Service (Amazon S3) followed by complex aggregation pipelines (daily user-aggregation running once per day with a 3-day lookback, and weekly 10-day-lookback jobs).
Near-real-time operational monitoring: Queries executing every 20 minutes against raw data for system-health dashboards.
Business-intelligence reporting: Tableau extracts and live dashboards.
Data-science workloads: Exploratory analysis and model-feature engineering.
Ad-hoc analysis: Non-recurring queries done by analysts and engineers.

With business growth, storage grew by orders of magnitude over the past decade as the platform expanded. All these varied workloads competed for the same engine and pushed it to its limits. Jobs experienced increasing queue times, service level agreements (SLAs) were at risk, and adding nodes provided minimal performance gains, creating a need to isolate workloads.

Gaining visibility: Measuring workload impact

Moovit’s first modernization milestone was to create a trusted measurement foundation before changing any workloads. Instead of treating warehouse activity as a single opaque stream, the team implemented automated query attribution that continuously classified each query by workload owner and execution context. The classification combined multiple signals: who executed the query (user or service account), recognizable query-signature patterns, and metadata emitted by orchestration frameworks and scheduled processes.

This produced a historical, query-level map of platform usage that answered three critical questions: who is generating load, what kind of workload is running, and how expensive each workload is in runtime and resource terms. With that baseline in place, the team made offload decisions from evidence rather than assumptions. This approach prioritized the largest and most stable optimization opportunities first and reduced the risk of moving business-critical workloads without visibility.

These classifications and workload metrics were reflected in a Tableau report that aggregated query activity by classification label and execution context. The view exposed operational dimensions such as classification, time granularity, service class, execution-time bucket, unload flags, and sample-query context, supporting both trend monitoring and root-cause drill-down.

The worksheet was parameterized to support multiple measurement modes over the same grouped workload population: total execution time, execution plus queue time, total CPU time, average execution time per query, and ratio-based efficiency views (execution/CPU and CPU/execution). This let the team compare “heavy by volume” workloads against “inefficient by behavior” workloads without creating separate artifacts.

For decision-making, CPU time was used as the primary impact metric because it best represented sustained compute pressure. Execution time, queue time, query-count normalization, and workload-management segmentation were treated as secondary evidence to distinguish:

compute-heavy but healthy workloads
queue-constrained workloads
high-frequency/low-cost workloads
noisy or weakly classified workloads that required attribution cleanup first

Using this framework, prioritization became systematic: first improve classification coverage, then rank workloads by CPU contribution, then validate with queue and workload management (WLM) signals, and finally choose the action path per workload (optimize SQL, reschedule, isolate, retire, or move to another engine).

The following figure shows an example of one of the dashboard widgets (CPU time by query).

Figure 1: CPU time by query, highlighting the most resource-intensive queries and their usage patterns

Cleanup: Reducing unnecessary data warehouse load

With a long-running data platform, in most cases the workloads will start accumulating, some of which become irrelevant at some point. For example, a report which was created and scheduled, yet it became irrelevant after a few years, but still running since no one disabled it. It’s important to indicate these workloads in general to reduce unnecessary load, yet even more critical before doing any significant architectural changes or migrations. Before migrating any workloads, Moovit first reduced unnecessary warehouse load.

The team:

Removed unused processes that were still consuming cluster resources.
Reduced unnecessary frequency where possible: some jobs ran more often than downstream consumers needed.
Reviewed workload-management guardrails to verify resource allocation matched actual priorities.

This cleanup phase was a prerequisite to migration. By removing waste first, the team verified that the workloads eventually selected for offloading were genuinely heavy rather than simply unoptimized or unnecessary.

The no-longer-relevant processes consumed around 7 percent of overall CPU time and were removed before the optimization work began.

Workload selection: Choosing what to offload

With a clear picture of workload patterns, Moovit faced a common decision point: continue scaling the existing Redshift cluster, or re-architect towards a multi-engine approach. The team evaluated two main paths:

Re-architect with Redshift multi-cluster and data sharing: Identify workloads that could benefit from resource isolation, then redistribute processing and queries between multiple Redshift clusters, combining both serverless and provisioned options. This would redistribute load across use-case-optimized clusters and potentially save costs through better resource use.
Re-architect with purpose-built engines: Identify workloads that could benefit from alternative processing frameworks and offload them to more suitable engines. This would reduce pressure on Amazon Redshift while building a more flexible, cost-efficient architecture.

Moovit decided to do both, because while some workloads benefited from being offloaded, others benefited from isolated Amazon Redshift compute.

The measurement data revealed a primary candidate for offloading: raw-data aggregation pipelines. This workload loaded raw data into Amazon Redshift from Amazon S3, then performed heavy sessionization and aggregation transformations. Raw tables were still used for ad-hoc and exploratory analysis, but recurring production consumers primarily depended on aggregated outputs, making these transformations strong candidates for offloading.

Proof of concept: Offloading to EMR Serverless with Spark SQL

With target workload identified, Moovit initiated a POC using Amazon EMR Serverless with Spark SQL. The choice of EMR Serverless was driven by several factors:

Spark SQL compatibility: The existing Redshift SQL logic could be ported with minimal changes to Spark SQL syntax.
Serverless simplicity: No cluster-management overhead during the evaluation phase.
Data-lake native: Processing could occur directly on data in Amazon S3.

The POC defined quantified success criteria measured over five or more consecutive runs:

Runtime reduction: Greater than or equal to 40 percent reduction for the transform portion of selected pipelines.
Amazon Redshift cost reduction: Greater than 30 percent reduction in Redshift RA3 compute with no performance degradation for remaining workloads.
Data-quality parity: Exact match between Spark and Amazon Redshift outputs on row counts, distinct users, and all published metrics over a frozen parity window.

Overcoming initial performance challenges

The first POC attempts exposed significant challenges. Early Spark jobs with 100 executors took approximately 4 hours, far exceeding the 30–40-minute baseline on Amazon Redshift. Beyond raw performance, the team encountered memory pressure, data-parity gaps between Spark and Amazon Redshift outputs, and subtle SQL behavior differences between the two engines.

The team systematically diagnosed and resolved these issues:

Execution-plan analysis: Reviewing the Spark execution plan revealed suboptimal query patterns that generated excessive data shuffles.
Query rewrites: Rewriting specific SQL constructs to align with Spark’s distributed processing model, including splitting large monolithic logic into staged transformations.
Reducing or rewriting expensive DISTINCT patterns: Identifying and eliminating unnecessary DISTINCT operations that created heavy shuffle pressure.

After applying these optimizations, execution time dropped from 4 hours to approximately 10 minutes, and the required executors dropped to fewer than 50, surpassing the original performance.

Validation: Ensuring data parity before cutover

Before transitioning any workload to production, Moovit implemented a rigorous validation process. The new Spark output was compared with the previous Amazon Redshift output using multiple dimensions:

Row counts: ensuring no data was lost or duplicated.
Distinct users: verifying entity-level completeness.
Metric parity: all published business metrics matched.
Daily trends: time-series patterns remained consistent.
Row-level checks: spot-checking individual records for correctness.

Only after all validation checks passed consistently over multiple consecutive runs did the team proceed with cutover for each workload.

Moving to production: Expanding workload offloading

With a successful POC demonstrating both performance gains and cost savings, Moovit progressively moved additional workloads from Amazon Redshift to EMR:

Heavy-aggregation jobs: The primary daily and weekly aggregation pipelines transitioned fully to EMR.
Data-transformation stages: Preprocessing steps that previously consumed Redshift compute moved to Spark, with only final aggregated results loaded back into Amazon Redshift for BI consumption.
Weekly batch workloads: Large batch jobs that previously created resource contention during weekend processing windows.

The transition used a measured approach: each workload was migrated individually, with data-quality validation confirming parity before decommissioning the equivalent jobs which were running on Redshift.

Additional optimizations: Redshift Serverless, workload isolation, and Amazon EMR on Amazon EC2

Beyond EMR offloading, Moovit implemented further architectural improvements to isolate workloads and optimize costs.

Amazon Redshift rightsizing: Iterative cluster optimization

With heavy workloads successfully offloaded and isolated, Moovit proceeded to right-size the Redshift cluster. Rather than a single resize, the team reduced the cluster incrementally, two nodes at a time, using elastic resize. At each step, they validated that:

Existing BI workloads maintained acceptable performance.
Queue wait times remained within SLA thresholds.
No workload degradation was observed under peak loads.

This iterative approach minimized risk and allowed the team to find the optimal cluster size with confidence.

Workload isolation with Redshift Serverless

Amazon Redshift persisted as the engine of choice for serving curated BI data. However, not all Amazon Redshift workloads needed provisioned capacity:

Ad-hoc analyst queries: Moved to Redshift Serverless, isolating unpredictable workloads from the provisioned cluster through data sharing.
Data-science workloads: Transitioned to Redshift Serverless for flexible exploration without impacting production.

This workload isolation through Redshift Serverless provided resource separation without requiring additional provisioned capacity. The architecture now used data sharing to provide a unified view across provisioned and serverless clusters.

Moovit also refined workload isolation by rebalancing WLM priorities on the provisioned cluster. Because the ETL queue mainly handled raw data loading from Amazon S3 (which was not the bottleneck after heavy aggregations moved to Spark), its priority was reduced. At the same time, with most human users moved to Redshift Serverless, Tableau serving workloads on provisioned Redshift were prioritized higher to keep dashboard performance predictable. The final result: a 50% reduction in provisioned Redshift capacity.

Transitioning to EMR on EC2

EMR Serverless proved efficient for the POC phase: it allowed fast iteration without cluster management overhead. However, for longer-term recurring production workloads, Moovit moved to EMR on EC2 to better fit their production cost and infrastructure model, using existing compute reservations.

The transition between EMR deployment options required zero application code changes, demonstrating the flexibility of the EMR deployment options.

AI-assisted SQL translation

Additionally, Moovit used AI-assisted development tools, Claude Code and Cursor, to accelerate parts of the SQL transition process. These tools helped engineers identify Redshift SQL and Spark SQL syntax differences, suggest rewrites, and debug migration issues, while validation and production approval remained under engineer review.

Results: A modern multi-engine architecture

The architectural modernization delivered measurable outcomes:

Cluster size reduction: Redshift cluster size reduced to 50 percent of the initial capacity.
Performance improvement: Key aggregation jobs ran faster and more consistently on EMR (50 percent execution time reduction for p90).
Workload isolation: No single workload type could impact others through resource contention.
33 percent overall data pipeline cost reduction: Combined savings from cluster reduction, transition to EMR, and efficient serverless usage.
Future flexibility: The multi-engine architecture provided pathways for additional use cases without architectural changes.

The following figures compare aggregation-job performance before and after the transition.

Figure 2: Aggregation-job execution times before and after the transition

Figure 3: Wall-clock time for job executions by percentile, before and after the transition

The resulting architecture assigned each workload to the engine that fits it best:

Workload type	Engine	Rationale
Heavy ETL and aggregation	Amazon EMR (Spark SQL)	Distributed processing on Amazon S3. No data warehouse load required
Ongoing processing and BI reporting	Amazon Redshift provisioned	24/7 running processes
Ad-hoc queries	Amazon Redshift Serverless	Burst capacity with workload isolation
Data science	Amazon Redshift Serverless	Flexible exploration without impacting production

Lessons learned

The Moovit modernization journey produced several key insights applicable to similar architectural transitions:

Measure before you move: Establishing baseline metrics and automated classification was essential for identifying true offloading candidates. Without granular workload-level measurements, the team would not have identified which specific processes were exhausting the cluster.
Clean up before you migrate: Reducing unnecessary load first verified that migration efforts targeted genuinely heavy workloads rather than simply unoptimized or unused processes.
Small SQL changes, big impact: Moving from Redshift SQL to Spark SQL required relatively minor syntax adjustments. The core business logic remained intact, and most transformations translated directly with minimal refactoring.
Optimize for the engine: Porting SQL queries to Spark without optimization produced initially poor results for some workloads. Understanding Spark’s distributed execution model and optimizing for it was critical for achieving target performance.
Validate rigorously: Multi-dimensional data-parity checks (row counts, distinct users, metrics, daily trends, and row-level spot checks) gave the team confidence to cut over without data-quality regressions.
Moving between EMR options is straightforward: EMR Serverless proved very efficient for starting fast and evaluating Spark. When Moovit needed to move to EMR on EC2 to use existing reservations, the transition required no application code changes.
Iterative cluster rightsizing: Rather than a single resize, Moovit reduced the Redshift cluster incrementally (two nodes at a time) using elastic resize, validating performance at each step before proceeding further.

Conclusion

Looking ahead, as another potential optimization, Moovit will be evaluating the new Amazon Redshift RG instances for provisioned clusters, providing up to 2.2x better price performance and priced 30% lower than RA3, powered by AWS Graviton.

The broader takeaway is that AWS provides multiple purpose-built engines that can be used in a single data platform. In Moovit’s case, the biggest improvement came from assigning each workload to the engine that fit it best: Amazon Redshift for curated analytical serving, Redshift Serverless for isolated exploratory workloads, and Amazon EMR for large-scale transformations over data in Amazon S3. This architecture gives Moovit a foundation for future optimization and flexibility as data volumes grow and new analytical use cases emerge.

About the authors

Query Amazon S3 Tables from Amazon EMR Trino using the Iceberg REST endpoint

Shubham Purwar — Wed, 02 Sep 2026 19:01:30 +0000

Organizations running analytics on Amazon Simple Storage Service (Amazon S3) data lakes often struggle with the operational overhead of managing Apache Iceberg tables, including compaction, snapshot expiration, and metadata tracking, while still needing fast, interactive SQL access across large volumes of data. Amazon S3 Tables, a capability of Amazon S3, addresses this by providing a purpose-built storage layer with native Apache Iceberg support and automated table maintenance. When you query S3 Tables from Amazon EMR using Trino and the Iceberg REST endpoint, you get a fully managed, open-standards-based analytics stack without the undifferentiated heavy lifting of table upkeep.

When paired with Amazon EMR running Trino, organizations gain access to a high-performance distributed SQL query engine capable of processing large-scale datasets. Trino’s ability to query data across multiple sources, combined with the automated optimization features of S3 Tables, creates a flexible analytics platform. The integration uses Apache Iceberg’s REST catalog specification, providing a standardized interface that supports compatibility across different compute engines while maintaining full control over query execution and data processing logic.

This architectural pattern is particularly valuable for organizations seeking to modernize their data platforms without vendor lock-in, as it relies on open standards and formats. The solution delivers high-throughput query performance with distributed SQL execution while significantly reducing the operational burden of managing table metadata, compaction, and snapshot lifecycle management. In this post, we show you how to create and query Amazon S3 Tables using Trino on Amazon EMR through the Apache Iceberg REST catalog endpoint.

Solution overview

This implementation demonstrates a complete integration between the Trino distribution on Amazon EMR and Amazon S3 Tables through the Apache Iceberg REST catalog endpoint. The architecture uses several key AWS services working in concert:

Amazon EMR serves as the managed compute layer, providing a scalable Hadoop framework that hosts the Trino query engine. Amazon EMR handles cluster provisioning, configuration management, and automatic scaling, allowing teams to focus on analytics rather than infrastructure management.

Apache Trino acts as the distributed SQL query engine, offering ANSI SQL compatibility and the ability to process queries across massive datasets with low latency for interactive workloads. Its connector architecture supports integration with various data sources, including the Iceberg REST catalog.

Amazon S3 Tables provides the storage and catalog layer, managing Apache Iceberg tables with built-in optimization. The service automatically handles compaction, snapshot expiration, and metadata management, reducing operational overhead while maintaining query performance. S3 Tables exposes a REST API endpoint that conforms to the Apache Iceberg REST catalog specification, which provides standardized integration with any Iceberg-compatible engine.

Apache Iceberg REST endpoint serves as the communication protocol between Trino and S3 Tables. This RESTful interface handles catalog operations including namespace management, table creation, metadata retrieval, and transaction coordination. The endpoint supports AWS Signature Version 4 authentication for secure access to table resources.

The data flow follows this pattern: Users submit SQL queries through the Trino CLI or JDBC interface. Trino’s Iceberg connector communicates with the S3 Tables REST endpoint to retrieve table metadata and plan query execution. The query engine then reads data directly from S3 using optimized file formats (Parquet, ORC) while using Iceberg’s metadata layer for partition pruning and predicate pushdown. Write operations follow a similar path, with Trino coordinating with S3 Tables to commit new data files and update table metadata atomically.

This architecture delivers several key benefits: separation of compute and storage for independent scaling, automated table maintenance reducing operational costs, open-source format compatibility preventing vendor lock-in, and fine-grained access control through AWS Identity and Access Management (IAM) and AWS Lake Formation integration.

Figure 1: Solution architecture for querying Amazon S3 Tables from Trino on Amazon EMR

Prerequisites

Before getting started, make sure that you have the following:

An active AWS account with billing enabled.
An AWS Identity and Access Management (IAM) user with specific permissions to create and manage resources, such as a virtual private cloud (VPC), subnet, security group, IAM roles, Amazon EMR, Interface VPC endpoints, S3 Tables bucket and S3 buckets.
Sufficient VPC capacity in your chosen AWS Region.

For this post, we create the solution resources in the US East (N. Virginia) Region (us-east-1) using AWS CloudFormation templates. In the following sections, we show you how to configure your resources and implement the solution.

Note: Querying Amazon S3 Tables through Trino on Amazon EMR requires Trino version 475 or later, available in Amazon EMR 7.11 and later.

Part A: Configure Amazon S3 Tables integration with Trino on Amazon EMR using AWS CloudFormation

In this post, you use the CloudFormation template emr-trino-s3tables.yaml.

This template deploys the following resources: a VPC with one private subnet, an S3 Tables interface VPC endpoint for private access, and an Amazon EMR cluster running Trino integrated with Amazon S3 Tables through the Apache Iceberg REST catalog endpoint.
It also creates an S3 Tables bucket, a general-purpose S3 bucket, IAM roles, and security groups.
At deploy time, it dynamically generates the Trino catalog configuration and bootstrap script.

To create the solution resources, complete the following steps:

Launch the stack emr-trino-s3tables.yaml using the CloudFormation template.

Provide the parameter values as listed in the following table.

Parameters	Description	Sample value
Stack Name	Name of CloudFormation stack	emr-s3tables-trino
VPC CIDR block	IP range (CIDR notation) for this VPC.	10.0.0.0/16
Private Subnet CIDR block	IP range (CIDR notation) for the private subnet in the second Availability Zone.	10.0.1.0/24
Resource name Prefix	Short prefix applied to every resource name	emr-s3tables
S3 Tables bucket name	Name of S3 table Bucket	trinoemrs3tablebuck
EMR release	Release version of Amazon EMR	EMR 7.12

The stack creation process can take approximately 15 minutes to complete. You can check the Outputs tab for the stack after the stack is created, as shown in the following screenshot.

Figure 3: CloudFormation stack outputs

Understanding the deployment

The CloudFormation template performs several key tasks:

Infrastructure provisioning: Sets up the Amazon EMR cluster with Trino, VPC, subnet, security group, and S3 table bucket.
Configuration: Creates necessary Trino configuration files.
Integration configuration: Sets up the Iceberg REST connector for S3 Tables.

Part B: Connecting Trino to Amazon S3 Tables with Iceberg REST endpoint

The CloudFormation template automatically configures the S3 Tables catalog in Trino on Amazon EMR. In the next section, we examine the configuration that drives this integration.

1. Catalog configuration details

A catalog in Trino on Amazon EMR is the configuration that grants access to a specific data source. Each Trino on Amazon EMR cluster can have multiple catalogs configured, allowing access to different data sources simultaneously.

As part of this setup, the CloudFormation template creates a catalog properties file at /etc/trino/conf/catalog/s3tables_irc.properties with the following configuration:

connector.name=iceberg
iceberg.catalog.type=rest
iceberg.rest-catalog.uri=https://s3tables.<REGION>.amazonaws.com/iceberg
iceberg.rest-catalog.warehouse=arn:aws:s3tables:AwsRegion:<ACCOUNT-ID>:bucket/<BUCKET-NAME>
iceberg.rest-catalog.sigv4-enabled=true
iceberg.rest-catalog.signing-name=s3tables
iceberg.rest-catalog.view-endpoints-enabled=false
fs.hadoop.enabled=false
fs.native-s3.enabled=true
s3.region=us-east-1
s3.iam-role=arn:aws:iam::<ACCOUNT-ID>:role/service-role/<ROLE-NAME>

2. S3 Tables Iceberg REST endpoint configuration properties

The following table lists the key properties in the catalog configuration on Trino:

Property name	Description
iceberg.rest-catalog.uri	REST server API endpoint URI (necessary).
iceberg.rest-catalog.warehouse	Warehouse ID or location for the catalog (necessary). For S3 Tables, this is the ARN for the S3 table bucket as shown in the preceding properties example.
iceberg.rest-catalog.sigv4-enabled	Must be set to ‘true’ (necessary)
iceberg.rest-catalog.signing-name	Must be set to ‘s3tables’ (necessary)
iceberg.rest-catalog.view-endpoints-enabled	Must be set to ‘false’ (necessary)
fs.hadoop.enabled	Must be set to ‘false’
fs.native-s3.enabled	Must be set to ‘true’
s3.iam-role	Amazon Resource Name (ARN) of the IAM role with permissions to S3 Tables. In this post, we use the same role, which is the service role for Amazon EMR.
s3.region	AWS Region, for example us-east-1

This configuration establishes a connection between Trino and the S3 Tables REST endpoint. You can have multiple catalogs registered, one per S3 table bucket, which is determined by the iceberg.rest-catalog.warehouse property.

3. Configure Amazon EMR service IAM role trust relationships

The Amazon EMR service role requires proper trust relationships to function correctly. Navigate to the IAM console and configure the trust policy for your Amazon EMR service role:

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Principal": {
                "Service": "elasticmapreduce.amazonaws.com"
            },
            "Action": "sts:AssumeRole"
        },
        {
            "Effect": "Allow",
            "Principal": {
                "AWS": "arn:aws:iam::<ACCOUNT-ID>:role/service-role/AmazonEMR-InstanceProfile"
            },
            "Action": "sts:AssumeRole"
        }
    ]
}

This trust policy establishes two critical relationships:

The Amazon EMR service can assume the role to manage cluster operations.
The EC2 instance profile can assume the role to access S3 Tables with elevated permissions.

4. Working with S3 Tables in Trino on Amazon EMR

Now that you have Trino on Amazon EMR set up and configured to work with S3 Tables, you can explore how to work with this integration.

4.1. Connecting to Trino on Amazon EMR

Navigate to Amazon EMR and select Connect to the primary node using AWS Systems Manager Session Manager for passwordless SSH.

Figure 4: Connecting to the primary node with Session Manager

When you’re connected, you can use the Trino CLI with your S3 Tables catalog:

sudo su - hadoop
trino-cli --catalog s3tables_irc

This connects you to the Trino on Amazon EMR using the S3 Tables integration you configured.

Figure 5: Trino CLI connected to the S3 Tables catalog

4.2. Examples: Creating and querying tables

In this section you run through some example queries to demonstrate the functionality.

4.2.1 Creating a namespace

First, you create a namespace (schema) in S3 Tables. A namespace in S3 Tables is a logical container or organizational unit that helps group related tables and objects together.

CREATE SCHEMA blog_namespace;
USE blog_namespace;

4.2.2 Creating a table

Create a table with various data types. You don’t need to specify the table type as Iceberg explicitly because you’re connecting to the Iceberg catalog. You can use all standard Iceberg capabilities, such as partitioning and sorting. Furthermore, some of the important Iceberg table properties that support table maintenance operations are configured with default values. You also have the option to edit the configurations using S3 Tables maintenance APIs.

CREATE TABLE IF NOT EXISTS customers (
customer_sk INT,
customer_id VARCHAR,
salutation VARCHAR,
first_name VARCHAR,
last_name VARCHAR,
preferred_cust_flag VARCHAR,
birth_day INT,
birth_month INT,
birth_year INT,
birth_country VARCHAR,
login VARCHAR
) WITH (
format = 'PARQUET',
sorted_by = ARRAY['customer_id']
);

Table property explanation:

format = 'PARQUET': Specifies Parquet as the file format for optimal compression and query performance.
sorted_by = ARRAY['customer_id']: Defines sort order within data files, improving query performance for customer_id filters.

Verify the table creation:

SHOW TABLES;

You should see customers in the output, confirming the table exists in the S3 Tables catalog.

4.2.3 Inserting data

You can insert some sample data into your table. You can also use an existing table in any of the catalogs configured in Trino on Amazon EMR to read data and write into the S3 table with an INSERT INTO ... SELECT statement.

INSERT INTO customers VALUES
(1, 'AAAAA', 'Mrs', 'Martha', 'Rivera', 'Y', 8, 4, 1984, 'US', 'mrivera'),
(2, 'AAAAB', 'Mr', 'Mateo', 'Jackson', 'N', 22, 6, 2001, 'US', 'mjackson'),
(3, 'BAAAA', 'Ms', 'Mary', 'Major', 'Y', 16, 2, 1999, 'US', 'mmajor'),
(4, 'BBAAA', 'Mr', 'Paulo', 'Santos', 'N', 30, 3, 1973, 'US', 'psantos'),
(5, 'AACAA', 'Ms', 'Ana', 'Silva', 'N', 2, 6, 1982, 'CA', 'asilva'),
(6, 'ABAAA', 'Mr', 'Alejandro', 'Rosalez', 'N', 5, 12, 1988, 'US', 'arosalez'),
(7, 'BBAAA', 'Ms', 'Nikki', 'Wolf', 'N', 6, 1, 2006, 'MX', 'nwolf'),
(8, 'ACAAA', 'Mr', 'Arnav', 'Desai', 'N', 15, 7, 1976, 'US', 'adesai');

This INSERT operation demonstrates Trino’s ability to write data to S3 Tables. Behind the scenes, Trino:

Writes data files in Parquet format to S3.
Communicates with the S3 Tables REST endpoint to register the new files.
Atomically commits the transaction, updating table metadata.

4.2.4 Querying data

Execute a SELECT query to retrieve and verify the inserted data:

SELECT * FROM customers LIMIT 10;

The query should return all eight customer records with proper formatting. You can also execute more complex analytical queries:

-- Count customers by country
SELECT birth_country, COUNT(*) as customer_count
FROM customers
GROUP BY birth_country
ORDER BY customer_count DESC;

-- Find customers born after 1990
SELECT first_name, last_name, birth_year
FROM customers
WHERE birth_year > 1990
ORDER BY birth_year;

These queries demonstrate Trino’s SQL capabilities and the integration with S3 Tables for both read and write operations.

4.3 Explore advanced features

S3 Tables with Iceberg provides several features for data management:

4.3.1 Time travel queries

Step 1: Check available snapshots.

-- Query table as of a specific timestamp. Check available snapshots
SELECT * FROM "customers$snapshots";

Step 2: Query the table as of a specific snapshot.

SELECT * FROM customers FOR VERSION AS OF <snapshot_id_from_step1>;

4.3.2 Schema evolution

-- Add a new column
ALTER TABLE customers ADD COLUMN email VARCHAR;

-- Rename a column
ALTER TABLE customers RENAME COLUMN login TO username;

Cleaning up

To clean up the resources, navigate to CloudFormation and delete the stack that you created.

Conclusion

This solution demonstrates an integration between Amazon EMR Trino and Amazon S3 Tables using the Apache Iceberg REST catalog specification. In this post, we showed you how to create and query S3 Tables from Trino on Amazon EMR. The architecture delivers several advantages for modern data platforms:

Operational simplicity: S3 Tables eliminates the complexity of managing Iceberg table metadata, compaction schedules, and snapshot lifecycle policies. The service handles these operations automatically, allowing data teams to focus on analytics rather than infrastructure maintenance.

Performance at scale: The architecture is designed for large-scale workloads. Trino distributes query execution across the cluster while Iceberg’s metadata layer helps the engine locate only the relevant data files. Features like partition pruning, predicate pushdown, and columnar file formats can help improve performance for both interactive and batch workloads.

Cost efficiency: This architecture separates compute and storage, so you can scale each independently based on workload requirements. S3 Tables automatically compacts small files to help reduce storage overhead, and Amazon EMR clusters can scale dynamically so you pay for compute only when needed.

Open standards and portability: By using Apache Iceberg’s open table format and REST catalog specification, this solution avoids vendor lock-in. Other Iceberg-compatible engines can access tables created in S3 Tables including Apache Spark, Apache Flink, and Dremio, providing flexibility in tool selection.

Fine-grained access control: Integration with IAM and resource-based policies provides access control at the table bucket, namespace, and table level. For fine-grained access at the column and row level, you can integrate with AWS Lake Formation. AWS Signature Version 4 authentication supports secure communication between Trino and S3 Tables.

ACID transactions: Iceberg’s transaction model guarantees atomicity, consistency, isolation, and durability for all table operations. This supports reliable concurrent reads and writes, making the platform suitable for production workloads requiring data consistency.

This architectural pattern is particularly well-suited for organizations building modern data lakehouses, migrating from traditional data warehouses, or consolidating multiple analytics platforms. The combination of the managed compute of Amazon EMR, Trino’s versatile query engine, and the automated table management of S3 Tables creates a strong foundation for data-driven decision making.

To learn more about the services and features discussed in this post, see the following resources:

About the authors

Building medallion architecture with Iceberg materialized views in Amazon SageMaker

Gaurav Sharma — Wed, 02 Sep 2026 18:34:32 +0000

Building a Medallion Architecture today typically means that you must build three separate systems working in concert: extract, transform, and load (ETL) jobs to transform data between layers, an orchestrator (such as Apache Airflow or AWS Step Functions) to sequence those jobs in the correct order, and custom change-data-capture (CDC) logic to make sure that each job processes only new or modified records. Each component must be authored, tested, deployed, and maintained independently and when one breaks, the entire pipeline stalls.

In this post, we show how Apache Iceberg materialized views in Amazon SageMaker collapse transformation, orchestration, and incremental processing into a single SQL definition per layer. You declare what each layer should contain, and the system handles when and how it refreshes based on your refresh configuration. With this approach, you can build a Bronze → Silver → Gold pipeline with three SQL statements. This reduces the complexity of maintaining separate orchestration code, CDC logic, and job artifacts.

What is medallion architecture

The medallion architecture organizes data into three progressive layers:

Bronze layer – Captures raw data as-is from source systems, preserving the original format for auditability and replay.
Silver layer – Applies cleaning, deduplication, type casting, and business logic to produce validated, query-ready datasets.
Gold layer – Aggregates Silver data into business-level metrics, key performance indicators (KPIs), and dimensional models optimized for analytics and reporting.

Each layer builds on the previous one, creating clear lineage from raw ingestion to business insight.

Traditional versus declarative approach

The two approaches differ in how much infrastructure you build and maintain.

Traditional approach

You write an ETL job such as Apache Spark script for Bronze to Silver layer and another for Silver to Gold layer. You build a directed acyclic graph (DAG) in Apache Airflow or a Step Functions state machine to run them in order. You implement CDC logic like tracking high watermarks, comparing snapshots, or consuming change streams such that each job processes only new data.

Declarative approach with Iceberg materialized views

You write one CREATE MATERIALIZED VIEW statement per layer with a SCHEDULE REFRESH EVERY N HOURS clause. The AWS Glue managed Spark compute executes the refresh, but you don’t author, version, or deploy a job artifact. Iceberg’s row-level change tracking (position-delete and equality-delete files) identifies which rows changed since the last refresh and AWS Glue processes only those rows. The dependency chain is implicit in the SQL definitions. The only code you maintain is the SQL transformation logic itself.

Apache Iceberg and materialized views

Apache Iceberg is an open-source, high-performance table format designed for petabyte-scale analytic datasets in data lakes. It provides ACID transactions, time travel, schema evolution, and hidden partitioning.

With an Iceberg materialized view, you can define each layer of a medallion architecture as a SQL statement. Under the hood, AWS Glue uses Iceberg’s change-tracking metadata to identify which rows changed since the last refresh, then processes only those rows using managed Spark compute. You configure scheduling and incremental processing through SQL definitions, and the system executes atomic refreshes without requiring you to write pipeline code.

When refreshed, the Gold materialized view reads incrementally from the Silver materialized view, which in turn reads from the Bronze table. This creates a declarative dependency chain: each layer’s definition points to the layer below it, and the system resolves which data to reprocess at each refresh.

Service support for Iceberg materialized views

At time of publication, the following services support creating and refreshing Iceberg materialized views:

For the latest version requirements, see the AWS Glue materialized views documentation.

Technical architecture

The architecture uses Amazon S3 Tables, a capability of Amazon Simple Storage Service (Amazon S3), as the storage layer. Amazon S3 Tables is a managed Apache Iceberg offering that alleviates the administrative overhead of maintaining Iceberg tables. AWS Glue Data Catalog manages table metadata, and Amazon SageMaker Unified Studio provides the AI-powered notebook environment with AWS Glue 5.1 for authoring and executing materialized view definitions.

The diagram illustrates a three-tier data lakehouse pipeline built on Apache Iceberg. The Bronze layer contains raw trip data (trips_bronze table on S3 Tables with fields: trip_id, city, vehicle_type, fare, status) that you ingest through INSERT/Append operations.

An incremental REFRESH feeds the Silver layer, where a materialized view (mv_trips_silver) performs timestamp conversion, null filtering, and computes derived columns like revenue_per_mile and rating_category. It processes only new or changed rows.

The Silver layer then refreshes two Gold layer materialized views on a daily schedule: mv_city_daily_metrics (city, date, trips, drivers, revenue, tips) and mv_vehicle_performance (vehicle_type, city, trips, revenue, distance). The Gold layer serves downstream consumers including Amazon Athena, Amazon Quick Sight, Amazon Redshift, and first-party (1P) or third-party (3P) compute engines supporting the Iceberg REST API.

The pipeline flows as follows:

Figure 1: The three-tier medallion pipeline from the Bronze table through Silver and Gold materialized views to analytics consumers

Prerequisites

Before starting, verify that you have the following:

An AWS account with permissions for Amazon SageMaker Unified Studio, AWS Glue, S3 Tables, and AWS Lake Formation.
An Amazon SageMaker Unified Studio domain.

Step 1: Initialize the environment

Open the AWS Management Console and navigate to Amazon SageMaker.

Figure 2: The Amazon SageMaker console landing page

Choose Get Started to set up Amazon SageMaker Unified Studio.

Figure 3: The Get Started page for setting up SageMaker Unified Studio

Choose Open to launch Amazon SageMaker Unified Studio.

Figure 4: The option to open and launch SageMaker Unified Studio

After you’re in SageMaker Unified Studio, choose Data in the left pane to create the S3 Tables bucket (a managed Apache Iceberg feature of Amazon S3) and a database. Choose Add, then choose Create S3 Tables Catalog, and provide a catalog and a database name. Finally, choose Create Catalog.

Figure 5: The Create S3 Tables Catalog dialog with catalog and database name fields

After the catalog creation is complete, in the left navigation pane, choose Notebooks.

Figure 6: The Notebooks option in the SageMaker Unified Studio navigation pane

Choose Create Notebook.

Figure 7: The Create Notebook button in SageMaker Unified Studio

Before using the notebook, select either Athena Spark or Glue Spark compute connection as the runtime engine for your notebook.

Figure 8: Selecting Athena Spark or Glue Spark as the notebook runtime engine

Use the following code samples in individual notebook cells. You can also provide transformation requirements in natural language, and the SageMaker Data Agent will generate SQL code for you.

Figure 9: The SageMaker Data Agent generating SQL from a natural language request

Add each code block in a new cell by choosing the SQL button:

Figure 10: The SQL button for adding a code block to a notebook cell

Choose Athena Spark or Glue Spark as your compute from the cell menu.

Figure 11: The compute selection in the notebook cell menu

If you encounter errors after cell execution, use the data agent chatbot or the Fix with AI button to resolve them.

Figure 12: The Fix with AI button for resolving cell execution errors

Step 2: Ingest data into Bronze

Generate 300 realistic ride-sharing trips and insert them directly into the Bronze Iceberg table. This simulates a raw data ingestion layer. In production, you generally configure a streaming source or batch load based on your requirements.

Copy the following code into the first notebook cell (use a Python cell type).

import random
from datetime import datetime, timedelta

CITIES = {
    "San Francisco": {"lat_range": (37.70, 37.82), "lon_range": (-122.52, -122.38), "surge_prob": 0.3},
    "Austin": {"lat_range": (30.22, 30.40), "lon_range": (-97.80, -97.68), "surge_prob": 0.15},
    "Chicago": {"lat_range": (41.85, 41.95), "lon_range": (-87.70, -87.60), "surge_prob": 0.2},
    "Seattle": {"lat_range": (47.55, 47.68), "lon_range": (-122.40, -122.28), "surge_prob": 0.25},
}
VEHICLE_TYPES = ["UberX", "Comfort", "XL", "Black"]
PAYMENT_METHODS = ["credit_card", "debit_card", "apple_pay", "google_pay", "cash"]
STATUSES = ["completed"] * 4 + ["cancelled_rider", "cancelled_driver"]
BASE_FARES = {"UberX": 2.50, "Comfort": 3.50, "XL": 4.00, "Black": 7.00}
PER_MILE = {"UberX": 1.75, "Comfort": 2.25, "XL": 2.50, "Black": 3.75}
PER_MIN = {"UberX": 0.35, "Comfort": 0.45, "XL": 0.50, "Black": 0.65}

rows = []
for i in range(300):
    city_name = random.choice(list(CITIES.keys()))
    city = CITIES[city_name]
    vehicle = random.choice(VEHICLE_TYPES)
    duration = random.randint(5, 45)
    distance = round(random.uniform(1.0, 20.0), 1)
    surge = round(random.uniform(1.0, 2.5), 1) if random.random() < city["surge_prob"] else 1.0
    base = BASE_FARES[vehicle]
    fare = round((base + distance * PER_MILE[vehicle] + duration * PER_MIN[vehicle]) * surge, 2)
    tip = round(fare * random.choice([0, 0, 0.1, 0.15, 0.2, 0.25]), 2)
    status = random.choice(STATUSES)
    day = random.randint(0, 2)
    hour = random.choices(range(24),
        weights=[1,1,1,1,1,2,4,8,10,8,6,5,6,5,5,5,6,8,10,8,6,4,2,1])[0]
    trip_time = datetime(2025, 12, 1) + timedelta(days=day, hours=hour, minutes=random.randint(0, 59))

    rows.append((
        f"TRIP-{i+1:06d}",
        f"DRV-{random.randint(1000, 5000)}",
        f"RDR-{random.randint(10000, 99999)}",
        city_name, vehicle,
        round(random.uniform(*city["lat_range"]), 6),
        round(random.uniform(*city["lon_range"]), 6),
        round(random.uniform(*city["lat_range"]), 6),
        round(random.uniform(*city["lon_range"]), 6),
        trip_time.isoformat(),
        (trip_time + timedelta(minutes=duration)).isoformat(),
        duration, distance, surge, base, fare, tip, round(fare + tip, 2),
        random.choice(PAYMENT_METHODS),
        random.choice([None, 3, 4, 4, 5, 5, 5]) if status == "completed" else None,
        status,
    ))

schema = ("trip_id STRING, driver_id STRING, rider_id STRING, city STRING, "
    "vehicle_type STRING, pickup_lat DOUBLE, pickup_lon DOUBLE, "
    "dropoff_lat DOUBLE, dropoff_lon DOUBLE, trip_start_time STRING, "
    "trip_end_time STRING, duration_minutes INT, distance_miles DOUBLE, "
    "surge_multiplier DOUBLE, base_fare DOUBLE, trip_fare DOUBLE, "
    "tip_amount DOUBLE, total_amount DOUBLE, payment_method STRING, "
    "rating INT, status STRING")

df = spark.createDataFrame(rows, schema)
df.writeTo("{CATALOG_NAME}.{NAMESPACE_NAME}.trips_bronze").createOrReplace()

print(f"Created Table and Inserted {len(rows)} trips into Bronze layer")

Step 3: Explore Bronze

Run a preview on the bronze table. The output should look like the following screenshot:

Figure 13: A preview of raw trip records in the Bronze table

You should see raw, unprocessed trip records with string timestamps and nullable fields. This is exactly what the Silver layer will clean up.

Now, verify the ingested data by querying the Bronze table for basic statistics.

SELECT COUNT(*) as total_trips, COUNT(DISTINCT city) as cities,
COUNT(DISTINCT vehicle_type) as vehicle_types,
MIN(trip_start_time) as earliest, MAX(trip_start_time) as latest
FROM ({CATALOG_NAME}.{NAMESPACE_NAME}.trips_bronze

The output should look like the following screenshot:

Figure 14: Bronze table statistics showing total trips, distinct cities, and vehicle types

Step 4: Create the Silver materialized view

This SQL statement defines the Silver layer as a materialized view that cleans, transforms, and derives new columns from the Bronze table. Note that this is only a definition. The system processes the data at refresh time.

CREATE MATERIALIZED VIEW IF NOT EXISTS {CATALOG_NAME}.{DATABASE}.mv_trips_silver
COMMENT 'Silver layer: Cleaned trip data with proper types and derived columns'
SCHEDULE REFRESH EVERY 1 DAY
AS
SELECT
trip_id, driver_id, rider_id, city, vehicle_type,
pickup_lat, pickup_lon, dropoff_lat, dropoff_lon,
CAST(trip_start_time AS TIMESTAMP) as trip_start_timestamp,
CAST(trip_end_time AS TIMESTAMP) as trip_end_timestamp,
duration_minutes, distance_miles, surge_multiplier,
base_fare, trip_fare, tip_amount, total_amount,
payment_method, rating, status,
CASE WHEN distance_miles > 0 THEN total_amount / distance_miles ELSE 0 END as revenue_per_mile,
CASE WHEN rating >= 4 THEN 'High' WHEN rating >= 3 THEN 'Medium' ELSE 'Low' END as rating_category
FROM {CATALOG_NAME}.{DATABASE}.trips_bronze
WHERE trip_id IS NOT NULL AND driver_id IS NOT NULL AND rider_id IS NOT NULL
AND total_amount >= 0 AND distance_miles >= 0

print("Silver MV created: urbanride.mv_trips_silver")

Verify the Silver layer output:

SELECT trip_id, city, vehicle_type, total_amount,
ROUND(revenue_per_mile, 2) as rev_per_mile, rating_category
FROM {CATALOG_NAME}.{DATABASE}.mv_trips_silver LIMIT 5

Notice how the Silver layer now has proper timestamps, derived revenue_per_mile, and rating categories: clean, typed, and ready for you to aggregate.

The output should look like the following screenshot:

Figure 15: Silver materialized view results with typed timestamps and derived columns

Step 5: Create Gold materialized views

Gold materialized views read incrementally from the Silver materialized view. This is a nested materialized view pattern: a materialized view built on top of another materialized view.

Gold 1: City daily metrics

With this materialized view, you can aggregate trip data by city and date with a scheduled daily refresh.

CREATE MATERIALIZED VIEW IF NOT EXISTS {CATALOG_NAME}.urbanride.mv_city_daily_metrics
COMMENT 'Gold layer: Daily aggregated metrics by city'
SCHEDULE REFRESH EVERY 1 DAY
AS
SELECT
city, DATE(trip_start_timestamp) as trip_date,
COUNT(*) as total_trips,
COUNT(DISTINCT driver_id) as active_drivers,
COUNT(DISTINCT rider_id) as active_riders,
SUM(total_amount) as total_revenue,
SUM(distance_miles) as total_distance,
SUM(tip_amount) as total_tips
FROM {CATALOG_NAME}.{DATABASE}.mv_trips_silver
WHERE status = 'completed'
GROUP BY city, DATE(trip_start_timestamp)

print("Gold MV created: mv_city_daily_metrics (reads from Silver MV, refreshes daily)")

Gold 2: Vehicle performance

With this materialized view, you can aggregate performance metrics by vehicle type and city.

CREATE MATERIALIZED VIEW IF NOT EXISTS {CATALOG_NAME}.{DATABASE}.mv_vehicle_performance
COMMENT 'Gold layer: Vehicle type performance metrics'
SCHEDULE REFRESH EVERY 1 DAY
AS
SELECT
vehicle_type, city,
COUNT(*) as trip_count,
SUM(total_amount) as total_revenue,
SUM(distance_miles) as total_distance,
SUM(tip_amount) as total_tips
FROM {CATALOG_NAME}.{DATABASE}.mv_trips_silver
WHERE status = 'completed'
GROUP BY vehicle_type, city

print("Gold MV created: mv_vehicle_performance (reads from Silver MV, refreshes daily)")

Dependency chain

The complete pipeline dependency is:

trips_bronze (table)
└── mv_trips_silver (materialized view)
    ├── mv_city_daily_metrics (MV on MV, daily schedule)
    └── mv_vehicle_performance (MV on MV, daily schedule)

Each layer is defined by a single SQL statement. There are no DAGs to maintain, no job definitions to deploy, and no watermark tracking to implement.

Step 6: Query the Gold layer

Query the Gold materialized views to see aggregated business metrics.

City daily metrics Gold table

SELECT city, trip_date, total_trips, active_drivers,
ROUND(total_revenue, 2) as revenue,
ROUND(total_revenue / total_trips, 2) as avg_per_trip
FROM {CATALOG_NAME}.{DATABASE}.mv_city_daily_metrics
ORDER BY trip_date DESC, revenue DESC LIMIT 15

The output should look like the following screenshot:

Figure 16: City daily metrics from the Gold materialized view

Vehicle performance Gold table

SELECT vehicle_type, city, trip_count,
ROUND(total_revenue, 2) as revenue,
ROUND(total_revenue / trip_count, 2) as avg_per_trip
FROM {CATALOG_NAME}.{DATABASE}.mv_vehicle_performance
ORDER BY revenue DESC

The output should look like the following screenshot:

Figure 17: Vehicle performance metrics from the Gold materialized view

The Gold layer gives you pre-aggregated, business-ready metrics without writing aggregation jobs.

Step 7: Data propagation demo

This section demonstrates how changes propagate through the layers using INSERT, UPDATE (MERGE), and DELETE operations followed by incremental refresh. In production, the scheduled refresh handles this automatically. We trigger it manually here for demonstration purposes.

INSERT new records

Insert new trip records into the Bronze table.

INSERT INTO {CATALOG_NAME}.{DATABASE}.trips_bronze VALUES
('DEMO_TRIP_001', 'DRIVER_999', 'RIDER_888', 'Seattle', 'UberX',
47.6062, -122.3321, 47.6205, -122.3493,
'2024-12-15 14:30:00', '2024-12-15 14:50:00',
20, 5.2, 1.0, 10.0, 15.0, 3.0, 18.0, 'credit_card', 5, 'completed'),
('DEMO_TRIP_002', 'DRIVER_888', 'RIDER_777', 'Seattle', 'XL',
47.6101, -122.3300, 47.6550, -122.3080,
'2024-12-15 15:00:00', '2024-12-15 15:35:00',
35, 8.5, 1.5, 15.0, 30.0, 5.0, 35.0, 'cash', 4, 'completed'),
('DEMO_TRIP_003', 'DRIVER_777', 'RIDER_666', Portland, 'Comfort',
30.2672, -97.7431, 30.2800, -97.7400,
'2024-12-15 16:00:00', '2024-12-15 16:15:00',
15, 3.0, 1.0, 8.0, 12.0, 2.0, 14.0, 'credit_card', 5, 'completed')

print("Inserted 3 new trips into Bronze")

Refresh Silver (incremental)

Refresh the Silver materialized view. Iceberg materialized view processes only three new records.

REFRESH MATERIALIZED VIEW {CATALOG_NAME}.{DATABASE}.mv_trips_silver"

Verify the new records propagated

SELECT trip_id, city, total_amount, ROUND(revenue_per_mile, 2) as rev_per_mile, rating_category
FROM {CATALOG_NAME}.{DATABASE}.mv_trips_silver
WHERE trip_id LIKE 'DEMO_TRIP_%' ORDER BY trip_id

The output should look like the following screenshot:

Figure 18: The Silver materialized view showing the three newly inserted demo trips

Refresh Gold (cascading from the Silver materialized view)

Refresh the Gold materialized view. It reads from the refreshed Silver materialized view and processes only the incremental changes.

REFRESH MATERIALIZED VIEW {CATALOG_NAME}.{DATABASE}.mv_city_daily_metrics

Verify the Gold layer reflects the new trips

SELECT city, trip_date, total_trips, ROUND(total_revenue, 2) as revenue
FROM {CATALOG_NAME}.{DATABASE}.mv_city_daily_metrics
WHERE trip_date = '2024-12-15' ORDER BY city

The output should look like the following screenshot:

Figure 19: City daily metrics reflecting the new trips for 2024-12-15

UPDATE through MERGE

Use MERGE to update existing records in Bronze, then refresh incrementally.

MERGE INTO {CATALOG_NAME}.{DATABASE}.trips_bronze AS target
USING (SELECT 'DEMO_TRIP_002' as trip_id, 5 as new_rating, 20.0 as new_tip) AS source
ON target.trip_id = source.trip_id
WHEN MATCHED THEN UPDATE SET
target.rating = source.new_rating,
target.tip_amount = source.new_tip,
target.total_amount = target.trip_fare + source.new_tip

Refresh Silver and verify

REFRESH MATERIALIZED VIEW {CATALOG_NAME}.{DATABASE}.mv_trips_silver")

SELECT trip_id, rating, rating_category, tip_amount, total_amount,
ROUND(revenue_per_mile, 2) as rev_per_mile
FROM {CATALOG_NAME}.{DATABASE}.mv_trips_silver WHERE trip_id = 'DEMO_TRIP_002'

print("UPDATE propagated: rating 4->5, tip $5->$20, total $35->$50")

The output should look like the following screenshot:

Figure 20: The Silver materialized view showing the updated rating and tip for the demo trip

Step 8: Cleanup

Drop materialized views, tables, the namespace, and delete the S3 Tables bucket to fully clean up resources.

# Drop MVs (Gold first, then Silver, due to dependency order)
spark.sql(f"DROP MATERIALIZED VIEW IF EXISTS {CATALOG_NAME}.{DATABASE}.mv_city_daily_metrics")
spark.sql(f"DROP MATERIALIZED VIEW IF EXISTS {CATALOG_NAME}.{DATABASE}.mv_vehicle_performance")
spark.sql(f"DROP MATERIALIZED VIEW IF EXISTS {CATALOG_NAME}.{DATABASE}.mv_trips_silver")
print("All materialized views dropped")

# Drop base table
spark.sql(f"DROP TABLE IF EXISTS {CATALOG_NAME}.{DATABASE}.trips_bronze")
print("Base table dropped")

# Drop the namespace
spark.sql(f"DROP NAMESPACE IF EXISTS {CATALOG_NAME}.{DATABASE} ")
print("Namespace dropped")

# Delete the S3 table bucket
import boto3
s3tables_client = boto3.client("s3tables")

# List and delete all remaining tables in the bucket
tables_response = s3tables_client.list_tables(
    tableBucketARN=TABLE_BUCKET_ARN, namespace="{DATABASE}"
)
for table in tables_response.get("tables", []):
    s3tables_client.delete_table(
        tableBucketARN=TABLE_BUCKET_ARN, namespace="{DATABASE}", name=table['name']
    )
    print(f" Deleted table: {table['name']}")

# Delete the namespace and bucket
s3tables_client.delete_namespace(tableBucketARN=TABLE_BUCKET_ARN, namespace="urbanride")
s3tables_client.delete_table_bucket(tableBucketARN=TABLE_BUCKET_ARN)
print(f"S3 table bucket deleted: {TABLE_BUCKET_NAME}")

Limitations and considerations

While materialized views remove most orchestration code, note the following:

No sub-hour freshness. The minimum schedule granularity is one hour (SCHEDULE REFRESH EVERY 1 HOUR).
Cascading refresh isn’t automatic. Refreshing Silver doesn’t trigger Gold in the same operation. Each layer refreshes on its own schedule or must be triggered sequentially.
Deletes require a FULL refresh. An incremental REFRESH that feeds the Silver layer detects inserts and updates through Iceberg metadata but cannot detect row removals. Use REFRESH ... FULL when delete propagation is needed.
SQL subset only. Some window functions, user-defined functions (UDFs), and complex expressions might not be supported in materialized view definitions.
Schema evolution requires recreation. If the source schema changes in a way that affects the materialized view definition, you must drop and recreate it.
AWS-specific extension. Iceberg materialized views are not part of the open-source Apache Iceberg specification. They aren’t portable to non-AWS environments.

Pricing

AWS bills materialized view auto-refresh at USD $0.44 per DPU-hour (4 vCPU, 16 GB memory), billed per second with a 1-minute minimum. When you configure scheduled refresh, the AWS Glue Data Catalog uses managed Spark compute to incrementally update the materialized view. You pay only for the compute time of each refresh run.

There are no separate charges for storing materialized view metadata in the Data Catalog (covered under standard catalog pricing: first million objects at no additional cost, then $1.00 per 100K objects/month). The materialized view data itself is stored as Iceberg files in S3 Tables or Amazon S3, charged at standard Amazon S3 storage rates.

Manual refreshes triggered from Spark (through Amazon Athena, Amazon EMR, or AWS Glue notebooks) are billed under those services’ respective compute pricing rather than the materialized view auto-refresh rate. For the latest pricing details, see the AWS Glue pricing page.

Estimated cost for this tutorial: Running through all steps once with 300 records typically consumes less than 0.5 DPU-hours total (~$0.22 in AWS Glue compute plus negligible Amazon S3 storage).

Summary

In this post, you built a Bronze → Silver → Gold medallion architecture using three SQL statements with nested materialized views and no orchestration code. The full pipeline creation took under 2 minutes, and incremental refreshes processed only changed data with no watermarks, no DAGs, no CDC plumbing.

To get started with your own data, create an Amazon SageMaker Unified Studio project, define your Bronze table, and express your transformation logic as Iceberg materialized views. For more information, see the Apache Iceberg materialized views documentation in the AWS Glue Developer Guide.

References

Using materialized views with AWS Glue

Query AWS Glue Data Catalog materialized views

Using materialized views with Amazon EMR

Working with Amazon S3 Tables and table buckets

About the authors

Build a dynamic streaming data lake with Apache Iceberg and Apache Flink

Francisco Morillo — Wed, 02 Sep 2026 18:28:38 +0000

Handling upstream schema changes is a common operational challenge in streaming data pipelines that write to a data lake. When a source schema changes, teams often face a difficult choice: restart the pipeline or perform a manual migration. A restart can pause ingestion and delay or lose in-flight data. A manual migration consumes engineering time and introduces the risk of schema inconsistencies while the data lake falls behind the source.

For example, consider an Apache Flink job that ingests order_events and writes to an Iceberg table. On Monday, the pipeline runs normally. By Wednesday, the upstream team adds a new loyalty_tier field and introduces a new interaction_events event type. Traditionally, you would need to stop the Flink job, update your schema definitions, and redeploy. With Apache Iceberg’s Dynamic Iceberg Sink on Amazon Managed Service for Apache Flink, the pipeline can handle both changes at the record level without disruption. The DynamicSink routes each event to the right Iceberg table and evolves table schemas as new columns appear, with no operator intervention.

Managed Service for Apache Flink is a fully managed AWS service that you can use to build and deploy streaming applications without setting up infrastructure and managing resources. Apache Flink’s distributed processing engine with exactly once processing guarantees through checkpointing paired with Apache Iceberg’s two-phase commit provides end-to-end consistency without duplications or data loss.

In this post, we show you how to build a dynamic streaming data lake that adapts to new event types and schema changes without stopping the pipeline. Using Apache Flink 2.3 and Apache Iceberg 1.11.0 on Managed Service for Apache Flink, we walk through the DataStream API patterns for per-record table routing and automatic schema evolution. The complete implementation is available in this GitHub repository.

Apache Iceberg dynamic sink

The Dynamic Iceberg Sink allows Flink to dynamically route records to multiple Iceberg tables based on user-defined logic. It also creates and updates tables on the fly and evolves both table schemas and partition specs during streaming execution, controlled through the DynamicRecord class, which eliminates the need for Flink job restarts when requirements change.

Per-record table routing with DynamicIcebergSink

The DynamicIcebergSink resolves the target table at the record level rather than at pipeline configuration time. Records flow through a DynamicRecordGenerator that, for each input, emits one or more DynamicRecord values. Each DynamicRecord carries its own target table ID, schema, partition spec, and row payload, so the sink knows where to write and how the table should look:

DynamicIcebergSink.forInput(events)
    .generator(generator)
    .catalogLoader(catalogLoader)
    .immediateTableUpdate(true)
    .cacheMaxSize(cacheMaxSize)
    .cacheRefreshMs(cacheRefreshMs)
    .append();

The generator receives each record and emits a DynamicRecord targeting a resolved table that looks as follows:

return new DynamicRecord(
    tableId,
    tableBranch,
    icebergSchema,
    rowData,
    partitionSpec,
    distributionMode,
    1);

The sink creates the table if it does not exist and evolves its schema when a record carries new columns. cacheMaxSize and cacheRefreshMs bound the sink’s per-table metadata cache, so a job that writes to many tables does not reload metadata on every record. immediateTableUpdate(true) controls how those catalog changes are applied, which the following section on automatic schema evolution explains. A single Flink job can ingest and route order_events, interaction_events, user_events, and future event types without additional sink definitions.

However, the sink also needs to know what the table looks like. That is why every DynamicRecord also carries the Iceberg schema so that DynamicIcebergSink can create the table on first sight and evolve it as new fields appear. The schema information can be inferred from the data or read from a schema registry.

Automatic schema evolution

Streaming sources add new fields over time, and DynamicIcebergSink handles them without a restart. Before writing each record, it compares the record’s schema against the target table. If the record has a new field, Iceberg adds it as an optional column and commits the change with the next data file. Existing files stay valid and no table rewrite is needed. When you query older files, the new column returns null.

The immediateTableUpdate setting controls where the catalog change happens. The GitHub sample repository sets immediateTableUpdate=true, so the writer subtask that sees the new schema applies the create or alter inline, before it emits the record. This gives the lowest latency but makes more concurrent calls to the catalog. When set to false, records that require a table change take a detour. Records whose table, schema, and partition spec already match the sink’s cached metadata go straight to the writers. Records that do need a change are routed, keyed by table name, to an update operator, so updates for the same table apply one at a time. Once the update commits and the cache refreshes, subsequent records match again and skip the detour. In steady state, with no schema changes arriving, this path adds no extra shuffle. Either way, the schema comparison and the resulting table change are the same.

Schema changes are non-destructive by default. The sink can add new columns, widen existing types (for example, int to long or float to double), relax a required column to optional, and drop columns. Importantly, DynamicIcebergSink does not support renaming columns at the time of writing.

Source schemas are identified in two ways: inferring the schema from source records (for example, JSON inference) and reading serialized records from a schema registry (for example, AWS Glue Schema Registry (GSR)). Schema evolution behavior for the Iceberg sink table depends on the schema source. JSON inference adds any new field it sees, with no contract. For example, this allows the job to initially infer a schema as an integer, and later expand to a long when larger values are detected. Schema registry serialized records define the policy using the registry’s compatibility rules (for example, BACKWARD). This means that incompatible producer changes are rejected when the schema is registered rather than at write time.

The partition spec travels on each DynamicRecord, so the sink applies it when it creates or updates the table. How our sample derives that spec is covered in the partitioning section.

Solution overview

The following diagram illustrates the solution architecture. A data generator (a local Java application) writes events to an Amazon Kinesis Data Stream. In Avro mode it also registers each event schema in the AWS Glue Schema Registry. A Managed Service for Apache Flink application consumes the stream, resolves a target Iceberg table for each record, and writes to Iceberg tables in Amazon S3, cataloged either in the AWS Glue Data Catalog or, for fully managed tables, in Amazon S3 Tables, a capability of Amazon S3.

Figure 1: Solution architecture for routing streaming records to per-event Iceberg tables on Managed Service for Apache Flink

At a high level, a single Managed Service for Apache Flink application reads raw records from Kinesis and resolves a target Iceberg table for each record. It uses the DynamicIcebergSink to create and evolve tables on demand. The same job handles many event types because the destination is decided per record, not per sink.

A note on stream topology: the examples assume one Kinesis stream carrying multiple event types, which keeps the walkthrough focused. This is not a requirement for the pattern. If your events arrive on separate streams (for example, one stream per producer or per domain), create one KinesisStreamsSource per stream and union them into a single DataStream before the sink. The routing generator chooses the destination table from the record itself, so many sources can fan into one DynamicIcebergSink and still land in the correct tables.

Unioning does not add shuffle cost. The sink always re-distributes records by an internal per-table writer key, so a unioned stream and N separate pipelines incur the same per-record exchange. The distribution mode each DynamicRecord carries only changes which writer subtask a row lands on, not whether a shuffle occurs. The real tradeoff is isolation. All tables share one writer pool, one commit aggregator, and one committer. A hot stream’s backpressure and checkpoint alignment therefore couple to every other stream, and writer parallelism is a single job-wide setting. Prefer one unioned pipeline when you have many small-to-medium event types that should pool capacity. Split into separate applications when one stream is high-volume enough to need its own writer parallelism and failure isolation.

DynamicIcebergSink needs a schema for every record. The sample provides two interchangeable ways to obtain it, implemented as two generator variants: Option 1 infers the schema from each JSON record at runtime. Option 2 reads the registered schema from AWS Glue Schema Registry. Everything downstream (routing, table creation, and schema evolution) is identical, and only the generator changes.

Option 1: Infer the schema from the JSON record

SchemaAgnosticRoutingGenerator implements Iceberg’s DynamicRecordGenerator. Its generate method maps the routing field to a table name, infers the schema, derives a partition spec, and emits a DynamicRecord through the collector:

@Override
public void generate(JsonNode json, Collector<DynamicRecord> out) {
    String tableName = determineTableName(json); // routing field -> table name
    TableIdentifier tableId = TableIdentifier.of(database, tableName);
    Schema schema = inferSchemaFromJson(json); // cached by schema signature
    RowData rowData = convertJsonToRowData(json, schema);
    PartitionSpec spec = buildPartitionSpec(schema); // cached per schema
    out.collect(new DynamicRecord(
        tableId, "main", schema, rowData, spec, DistributionMode.NONE, 4));
}

The table name comes from an explicit table-name field when present, otherwise from the routing field (event_type by default).

For schemaless or semi-structured JSON, the generator infers an Iceberg schema directly from each record. This is convenient, but inference is fundamentally lossy because JSON does not carry type information. The generator therefore applies deliberately conservative rules and selects a stable type rather than the narrowest one:

JSON value	Iceberg type
Integer	LongType (all integral values are widened to long)
String	StringType
Floating-point values	DoubleType
Boolean	BooleanType
ISO-8601 timestamps	TimestampType (microseconds)
Nested JSON object	StructType (with fields inferred recursively)
JSON array	ListType (with element type inferred from array contents)

Partitioning the routed tables

Partitioning is decided by our generator, not by the sink, and the same mechanism applies to both schema options: the JSON-inference and schema-registry generators share the partition-candidate logic. The open source DynamicIcebergSink applies whatever PartitionSpec each DynamicRecord carries. Our sample’s SchemaAgnosticRoutingGenerator builds that spec at runtime: it reads a list of candidate partition fields from the partition.candidates application property and derives a per-table spec from the fields it observes. For each table, buildPartitionSpec walks that list and keeps only the candidates present in the table’s schema.

The same list adapts to each table. A table with event_date and region is partitioned by identity(event_date) and identity(region). A table with none of the candidates is created unpartitioned. The resulting spec travels on each DynamicRecord, so the sink applies it when it first creates the table.

For example, with partition.candidates = event_time,region,product: a table whose schema has event_time and product is created partitioned by those two. A table with only event_time gets identity(event_time). A table with none of the candidates is created unpartitioned. Partition specs are not frozen at creation time either: the sink evolves them through Iceberg partition-spec evolution, adding a candidate field when it later appears in the table’s schema and removing one that disappears. This is a metadata-only change, so existing data files keep the spec they were written with.

Two operational practices follow. First, always include your event-time field among the candidates so every table is at least time-partitioned, and monitor for unpartitioned tables through the table’s $partitions metadata or its spec in the catalog: a producer that emits create_timestamp instead of event_time will silently create unpartitioned tables until the candidate list is updated. Second, be deliberate with generic fields like region. If a source produces high-cardinality values for a candidate field, you can correct the spec later. Evolution applies to newly written files only, so the small files already written remain until compaction rewrites them.

Note that the candidate list is global, not per table. It tracks every field you might partition on, and each table takes only the ones it has.

Option 2: Read the schema from a schema registry

Inference is convenient but lossy, and it offers no contract: nothing stops a producer from silently changing a field’s type or meaning. The second option removes the guesswork by reading the schema from a registry instead of the data. Many production streaming platforms standardize on strongly typed Avro schemas managed through AWS Glue Schema Registry. With GSR, producers register schemas explicitly, each record on Kinesis is Avro-encoded and prefixed with a schema-version ID, and the consumer decodes against the exact registered schema. That gives you three things JSON inference cannot: precise types (a long stays a long, a timestamp-micros stays a timestamp-micros), a governed evolution policy enforced at registration, and a single source of truth shared across producers and consumers.

The pattern works with any schema registry that gives consumers the writer’s schema per record. The sample implements it with AWS Glue Schema Registry, but the same generator shape applies to other registries.

The dynamic-sink-avro-sample module applies GSR-managed Avro schemas to the same dynamic routing and schema evolution pattern. For each record, AvroToDynamicRecordGenerator reads the schema-version ID and fetches the writer schema from GSR, caching it after the first lookup. It then converts that schema to an Iceberg schema, decodes the payload into RowData, and emits a DynamicRecord, exactly as the JSON generator does:

The sink wiring is identical to option 1. Only the generator changes, and because the source carries raw Avro bytes the input stream is byte[] rather than parsed JSON:

AvroToDynamicRecordGenerator generator = new AvroToDynamicRecordGenerator(
    awsRegion, registryName, database, partitionCandidates, branch);
DynamicIcebergSink.forInput(eventBytes)
    .generator(generator)
    // identical catalogLoader, immediateTableUpdate(true), cache, and write settings as option 1
    .append();

Because the schema comes from GSR rather than from inspecting bytes, the Avro-to-Iceberg type mapping is exact:

Category	Avro type	Iceberg type
Primitive	int	IntegerType
Primitive	long	LongType
Primitive	float	FloatType
Primitive	double	DoubleType
Primitive	string	StringType
Primitive	boolean	BooleanType
Logical	timestamp-millis	TimestampType (preserves millisecond precision)
Logical	timestamp-micros	TimestampType (preserves microsecond precision)
Logical	decimal	DecimalType
Complex	record	StructType (nested fields mapped recursively)
Complex	array	ListType (element type inferred from items schema)
Complex	map	MapType (keys are always StringType)

The GSR integration handles schema versioning transparently. As soon as a producer registers a new schema version containing additional fields, the Flink consumer deserializes the updated payload and evolves the Iceberg table to match, with no job restart.

Prerequisites

To follow along, you need the following:

An AWS account with permissions to create Amazon Kinesis Data Streams, Managed Service for Apache Flink applications, AWS Glue resources, and Amazon S3 buckets (plus Amazon S3 Tables if you choose that catalog).
The AWS Command Line Interface (AWS CLI) configured with credentials.
Node.js 18 or later and the AWS Cloud Development Kit (AWS CDK) CLI.
Java 17 or later and Apache Maven 3.9 or later, to build the data generator.
Docker running locally. The CDK build bundles the Flink application jars inside a Maven image.

Deploy and test the solution

The accompanying repository provisions everything through a single parameterized AWS CDK stack.

Install the CDK dependencies and bootstrap your environment (first time only):

cd cdk-infrastructure && npm install
npx cdk bootstrap aws://<account>/<region>

Deploy the variant you want to try:
```
npx cdk deploy -c appType=dynamic -c tableFormatVersion=2 # JSON inference variant
npx cdk deploy -c appType=dynamic-avro -c tableFormatVersion=2 # GSR Avro variant
```
Add -c catalogType=s3tables to either command to use Amazon S3 Tables instead of the AWS Glue Data Catalog. The walkthrough sets tableFormatVersion=2 so you can query the results with a broad range of engines. Omit it to use the default, Iceberg format version 3, when you query with a v3-aware engine such as Spark on Amazon EMR 7.12+ or AWS Glue ETL.

Start the application using the ApplicationName value from the stack outputs:

aws kinesisanalyticsv2 start-application --application-name <ApplicationName> --run-configuration 'ApplicationRestoreConfiguration={ApplicationRestoreType=SKIP_RESTORE_FROM_SNAPSHOT}'

Send test events with the included data generator. Start with the v1 payloads, which create the tables without the optional fields:
```
java -jar data-generator/target/data-generator-1.0-SNAPSHOT.jar <stream-name> <region> 100 60 v1
```
Then send v2 payloads, which add the userAgent and scrollDepth fields. This second run is the schema evolution you observe in the next step:
```
java -jar data-generator/target/data-generator-1.0-SNAPSHOT.jar <stream-name> <region> 100 60 v2
```
For the Avro variant, the generator registers each schema version in the AWS Glue Schema Registry as it sends:
```
java -jar data-generator/target/data-generator-1.0-SNAPSHOT.jar avro <stream-name> <region> <registry-name> 100 60
```
Query the routed tables in Amazon Athena. You should see one Iceberg table per event type appear in the database within a checkpoint interval, and after sending v2 events, the new fields (userAgent, scrollDepth) show up as optional columns on the same tables. The Iceberg metadata tables (for example, SELECT * FROM "db"."table$snapshots") show each commit the sink makes.

Clean up

When you finish testing, delete the resources to stop incurring charges:

cd cdk-infrastructure && npx cdk destroy

CDK removes the Kinesis Data Stream, the Managed Service for Apache Flink application, and the stack-created AWS Identity and Access Management (IAM) roles. Additionally, empty and delete the S3 warehouse bucket to remove the Iceberg data and metadata files, delete any schemas the Avro variant registered in the AWS Glue Schema Registry, and delete the table bucket contents if you used the S3 Tables catalog.

Conclusion

With Apache Iceberg 1.11.0 and Flink 2.3, you can build streaming data lake architectures that adapt to change without stopping the pipeline. With per-record routing, a single Flink application can write multiple event types to separate Iceberg tables, while automatic schema evolution keeps table definitions aligned with changing source data. Choosing AWS Glue Schema Registry over runtime JSON inference adds precise types and a governed evolution contract, and a configurable partition-candidate list keeps each routed table partitioned correctly without pre-declaring its schema.

The result is fewer pipeline redeployments, reduced operational overhead, and a data lake that remains synchronized with evolving application schemas.

To get started, follow the deploy and test section, then adapt the routing field and partition candidates to your own event types.

The full sample code is available in the accompanying GitHub repository.

About the authors

Observing and evaluating production agents using OpenSearch Agent Health

Ulrich Hinze — Tue, 01 Sep 2026 16:19:02 +0000

As AI agents are moving from experimental prototypes to production workloads, teams need visibility into what agents are doing and a systematic way to measure whether they’re doing it well. Traditional testing methodologies like unit and integration tests fall short for this task, as measuring an agent’s quality isn’t a straightforward true/false decision. Instead, agent observability and evaluations (evals for short) provide a two-legged solution to this problem. Agent observability captures the details of an agent’s behavior, and evals compare this behavior to the behavior that you want. With this approach, teams can monitor their agent’s quality over time and introduce agent-specific quality gates in their software development lifecycle.

In this post, we show how to combine an AI agent running on AWS with OpenSearch Agent Health for observability and evals. You will deploy an agent and its observability data pipeline to AWS, then use Agent Health as a local development tool connecting to your cloud resources.

Overview of solution

Agent observability and evaluations rely on OpenTelemetry traces to understand agent behavior. Traces describe the flow of a request through components of a system. OpenSearch Agent Health is a purpose-built tool for analyzing agent traces and running evaluations against an agent for quality control. Although Agent Health works with any open source OpenSearch installation, many AWS customers choose Amazon OpenSearch Ingestion and Amazon OpenSearch Service for ingesting and storing their OpenTelemetry data. You can connect OpenSearch Agent Health to these AWS resources to fetch live data and store its own configuration and evaluation history.

The following diagram shows the overall architecture of the solution presented in this post:

Figure 1: Solution overview

The individual parts are:

AWS Amplify for hosting an assistant-ui chat interface. Connects to the agent backend using the Agent-User Interaction (AG-UI) protocol.
Sample ecommerce AI agent using Strands Agents SDK, deployed to Amazon Bedrock AgentCore runtime, exposing an AG-UI Server-Sent Events (SSE) endpoint. This agent has access to multiple tools, such as product search and shopping basket operations. For this sample project, the tool calls are all simulated within the agent runtime rather than including API calls to other systems. The agent emits messages, reasoning steps, and tool calls as OpenTelemetry traces.
Large language models (LLMs) on Amazon Bedrock. One model (Amazon Nova 2 Lite) is used to power the agent, the other model (Anthropic Claude Opus 4.6) is used to evaluate the agent behavior.
Amazon OpenSearch Ingestion for collecting and transforming the raw agent traces and loading them into an Amazon OpenSearch Service domain. Agent traces have the same structure as regular OpenTelemetry traces, with the addition of generative AI semantics (for example, tool calls and token usage). This means a regular OpenTelemetry pipeline configuration can be used to process agent traces.
OpenSearch Agent Health for analyzing traces and running evaluation test cases and benchmarks against the agent. Agent Health uses the same AG-UI endpoint as the front-end application. It authenticates to the application, to Amazon Bedrock for model functionality, and to Amazon OpenSearch Service using AWS SigV4 authentication.

Walkthrough

In this walkthrough, we showcase how you can use Agent Health and Strands to measure and improve your agent’s quality over time.

We follow these steps:

Deploy solution to AWS and test the application.
Start Agent Health locally and connect it to cloud resources.
Explore agent traces and run evaluations.

We have created a GitHub repository for you to follow along.

Prerequisites

For this walkthrough, you should have the following prerequisites:

An AWS account
Git
Node.js
AWS Cloud Development Kit (AWS CDK)

Deploy solution to AWS and test the application

In this section, you check out the repository and deploy the infrastructure to AWS. Be aware that these steps create AWS resources that incur cost. We cover cleanup steps at the end of this post.

First, clone the repository to a local directory:

git clone https://github.com/aws-samples/sample-agent-health-with-amazon-opensearch-service && cd sample-agent-health-with-amazon-opensearch-service

Switch to the infra folder and install dependencies:

cd infra && npm install

Before you can start the deployment, determine the AWS Identity and Access Management (IAM) user or role that you will use to start Agent Health later on. In many cases, this will be the same role that you use to deploy the infrastructure. Set this ARN in your environment by issuing the following command:

export AGENT_HEALTH_READER_ARN=arn:aws:iam::<YOUR_ACCOUNT_ID>:role/<YOUR_ROLE_NAME>

Bootstrap your AWS account for use with AWS CDK:

cdk bootstrap -c agentHealthReaderArn=$AGENT_HEALTH_READER_ARN

Run the infrastructure deployment. Review and acknowledge IAM statement changes when prompted. This takes around 25 minutes to complete:

cdk deploy -R -c agentHealthReaderArn=$AGENT_HEALTH_READER_ARN

The -R parameter defines that if something fails during this deployment, the successfully provisioned resources are retained. Be aware that this command creates AWS resources, incurring cost. Review the cleanup section at the end of this post for removing all created resources.

When the deploy command finishes successfully, you should see an output like the following:

...
AgentObservabilityStack

✨ Deployment time: 1402.42 s

Outputs:
AgentObservabilityStack.AgentEndpoint = https://bedrock-agentcore.us-east-1.amazonaws.com/runtimes/arn%3Aaws%3Abedrock-agentcore%3Aus-east-1%3A123456789012%3Aruntime%2Fretail_agent-abcdefghij/invocations?qualifier=AgentObservabilityStAgentRuntimeEndpointABCDEFGH
AgentObservabilityStack.ChatUrl = https://main.abcdefghijklmn.amplifyapp.com
...

Next, create a user for your application. Retrieve the CDK output value for AgentObservabilityStack.UserPoolId. Create a user for the application using the user pool ID, an email address, and a strong password (minimum eight characters including uppercase, lowercase, letter, and digit):

export COGNITO_EMAIL=<YOUR_EMAIL>
export COGNITO_PASSWORD=<YOUR_PASSWORD>
export USER_POOL=<YOUR_USER_POOL_ID>
aws cognito-idp admin-create-user --user-pool-id $USER_POOL --username $COGNITO_EMAIL --message-action SUPPRESS --user-attributes Name=email_verified,Value=true
aws cognito-idp admin-set-user-password --user-pool-id $USER_POOL --username $COGNITO_EMAIL --password "$COGNITO_PASSWORD" --permanent

You can now access the retail agent application. From the CDK output values, retrieve the value for AgentObservabilityStack.ChatUrl. Copy and paste this URL into your browser. Log in with your email and password. You should now see the agent interface:

Figure 2: Sample retail agent user interface

Experiment with the application. Here is an example sequence of queries you can put in:

Do you have books on Python?
Is this in stock?
Put it into my basket.
What else can you do for me?

Start Agent Health

Now that you have the infrastructure running, you can start OpenSearch Agent Health locally and connect it to your cloud resources.

The CDK infrastructure deployment created a file cdk-output.json, which contains all relevant configuration values for Agent Health. We’ve already created a file agent-health/agent-health.config.ts that pulls these values dynamically in your environment, so you can start Agent Health without any further configuration.

Open a terminal and start Agent Health by running the following command:

cd ../agent-health && npm install
npx @opensearch-project/agent-health

Open http://localhost:4001 in your browser to access Agent Health UI. Choose Agent Traces in the sidebar menu to access your agent’s traces. You should see traces from your previous interactions:

Figure 3: Agent traces. As Agent Health is in active development, this interface might have changed since the time of writing

Expand the trace and explore the information it contains, such as token count and agent trajectory (sequence of messages, reasoning steps, and tool calls).

If you’re unable to access the application or see any traces, verify the following:

Check Agent Health logs in your terminal for any errors. Also check whether Agent Health is running on an alternative port, like 4002 instead of 4001.
If there are permission errors when accessing traces from OpenSearch, verify that the AWS credentials in your terminal match the principal (user or role) that you specified under the agentHealthReaderArn CDK parameter during cdk deploy. This principal must have ESHttpGet:* IAM permissions. Agent Health uses your current AWS credentials to access the OpenSearch API for querying traces. The OpenSearch API is guarded by both IAM and OpenSearch fine-grained access control.

Create and run a test

Choose Test Cases and New Test Case. Fill out the required fields with the following information:

Name: Should add to cart.
Initial Prompt: Add some wireless headphones to my cart. Take any that you have in stock.
Expected Outcomes: PROD-001 added to cart.

Back in the test cases overview, select the created test case and choose Run Test. In the Configure Run dialog, choose Retail Assistant (production) for Agent, Tool Usage Efficiency for Evaluator, Claude Opus 4.8 for Judge Model, and choose Start Run.

Agent Health now runs the configured initial prompt against the agent. The agent completes the task and sends execution traces to OpenSearch. Agent Health uses an evaluation model to check both agent responses and traces on successful execution, according to the defined expected outcomes. After the test is completed, go through the different tabs to check the test results.

Figure 4: Agent Health evaluation report

If you’re unable to run the test, check the following:

Agent Health automatically creates an Amazon Cognito token for your user upon start, but this token can expire. Restarting Agent Health creates a new token. Verify that both the COGNITO_EMAIL and COGNITO_PASSWORD variables are still set in your terminal environment.

Beyond test cases

After running a single test case, choose Benchmarks in the sidebar menu. With Benchmarks, you can run multiple test cases in parallel and summarize their results. You can compare benchmark runs by choosing Evaluation Runs in the sidebar, where you can analyze trends in pass rate, cost, and duration over time. Lastly, choose Evaluators to define your own evaluation logic beyond the predefined ones.

You can also run Agent Health tests with its command-line interface, which is handy for automation and continuous integration (CI). The equivalent command of running the preceding test is:

npx @opensearch-project/agent-health run -t <TEST_CASE_ID> -a "Retail Assistant" -e system-tool-usage --judge-model claude-opus-4.8 -e system-tool-usage

where TEST_CASE_ID can be retrieved from the browser URL when you visit the Agent Health UI (test case IDs start with tc-).

Agent Health stores all test cases, other configuration, and reports locally on disk in the agent-health/agent-health-data directory.

Cleaning up

To avoid incurring future charges, delete the resources:

cd ../infra && cdk destroy -c agentHealthReaderArn=$AGENT_HEALTH_READER_ARN

Conclusion

In this post, you learned to set up and use OpenSearch Agent Health for production agent observability and evaluations. To discover more features, see the Agent Health documentation pages. You can discuss and request additional features, and get help with setup, through the issues in the GitHub project. For more information, see the observability documentation for Amazon OpenSearch Service, where you can learn about the features available to build observability for both agents and traditional systems using OpenSearch. To investigate issues in production AI agents, see the recent post Unified observability in Amazon OpenSearch Service.

About the authors

Accelerate Apache Spark debugging on Amazon EMR with AWS DevOps Agent

Kalyan Janaki — Tue, 01 Sep 2026 16:06:44 +0000

When an Apache Spark job fails on Amazon EMR, the root cause can hide in executor logs, memory profiles, or application code. As data pipelines grow in complexity, correlating logs, metrics, and traces across multiple services requires significant operational effort. AWS DevOps Agent handles this investigation autonomously while keeping operators in the loop to review findings and approve fixes. From a single chat prompt, it produces a root cause and mitigation plan, often without any human involvement beyond the initial question.

The native AWS API tools in AWS DevOps Agent don’t extend into Spark-internal artifacts. Sometimes those tools can’t reach the evidence that pins down the root cause: a Spark History Server event log, executor Python worker memory, or a line of code that allocated too much. In these cases, AWS DevOps Agent can describe symptoms (“the executor exited with code 1”) but can’t identify the actual antipattern that caused them.

This post shows how to extend AWS DevOps Agent to investigate failures in Apache Spark workloads on Amazon EMR. You register the Apache Spark Troubleshooting Agent for Amazon EMR, a managed Model Context Protocol (MCP) server hosted by AWS, as a custom capability provider in your AWS DevOps Agent space. You route the traffic over AWS PrivateLink so MCP calls never traverse the public internet. Then you watch a single agent chat session investigate a deliberately failing Spark job, from Amazon CloudWatch alarm to line-numbered root cause, in about two minutes.

Prerequisites

Before you begin, make sure you have the following:

An AWS account with permissions to deploy AWS CloudFormation stacks that create AWS Identity and Access Management (IAM) roles and Amazon Virtual Private Cloud (Amazon VPC) resources.
The latest version of the AWS Command Line Interface (AWS CLI), Boto3, and Botocore, installed and configured with credentials for the same account and AWS Region.
This walkthrough assumes you are familiar with AWS DevOps Agent and know how to trigger an investigation. For an introduction, see Getting started with AWS DevOps Agent.

How AWS DevOps Agent discovers custom tools through MCP

Model Context Protocol (MCP) is an open standard that defines how AI agents discover and invoke external tools. AWS DevOps Agent supports connecting to custom MCP servers, which means you can expose new capabilities to it without modifying the agent itself. When you connect an MCP server to AWS DevOps Agent, the agent automatically discovers the available tools, understands their schemas, and calls them as part of its investigation workflow. You build and connect the MCP server, and the agent handles the rest.

MCP tools sit alongside the agent’s built-in AWS API tools. During a single investigation, the agent can interleave calls to cloudwatch.describe-alarms, emr-serverless.get-job-run, and a custom MCP tool such as analyze_spark_workload. The agent picks the right one for each subtask. You augment the agent’s reach without replacing what it already does.

For this integration, you don’t build an MCP server. The Apache Spark Troubleshooting Agent for Amazon EMR is itself a managed MCP server, hosted by AWS at a regional endpoint. Your job is to register that endpoint with AWS DevOps Agent and authorize the agent to call it. This requires a network path from the agent to the endpoint, plus an IAM role for AWS Signature Version 4 request signing.

Why Spark internals visibility matters

The actual root cause for a Spark failure usually lives somewhere none of those APIs (such as Amazon CloudWatch Logs Insights, AWS CloudTrail, or Amazon EMR step-status calls) can reach:

The Apache Spark Troubleshooting Agent for Amazon EMR reads the following sources.

The Spark History Server event log is a per-job archive in Amazon Simple Storage Service (Amazon S3) with stage timings, task-level metrics, executor utilization, shuffle read/write volumes, and garbage-collection pauses. Amazon EMR exposes this data through the Spark UI on Amazon EMR Serverless, Amazon EMR on Amazon Elastic Compute Cloud (Amazon EC2), and Amazon EMR on Amazon Elastic Kubernetes Service (Amazon EKS), but interpreting signals like data skew, executor memory pressure, or stages that take significantly longer than expected requires familiarity with Spark internals.

The Spark query plan — the logical and physical plan the driver compiled. Without it, you can’t identify antipatterns such as unnecessary data repartitioning or missing broadcast hints that trigger expensive shuffles.
The application source code in Amazon S3 — the .py or .jar code artifact the job ran. Without it, you can’t quote the offending line of a mapPartitions user-defined function or an inefficient collect().
The Python worker process telemetry — the PySpark worker is a separate Python subprocess outside the Java Virtual Machine’s (JVM) managed memory. When it crashes from spark.executor.pyspark.memory exhaustion, the JVM driver sees a generic “executor exited unexpectedly” message. The actual cause is invisible to standard JVM-level logs.

When the agent invokes analyze_spark_workload during an investigation, it returns a structured analysis with the antipattern identified at the line level, the offending stage isolated, and a concrete fix: both code changes and configuration changes.

Integrating AWS DevOps Agent with Apache Spark Troubleshooting MCP

This section explains how AWS DevOps Agent connects to the Apache Spark Troubleshooting Agent through a private MCP endpoint and orchestrates the investigation workflow.

How it works

Figure 1: Integration architecture between AWS DevOps Agent and the Apache Spark Troubleshooting Agent for Amazon EMR over AWS PrivateLink

You submit an investigation prompt in AWS DevOps Agent.
AWS DevOps Agent sends a SigV4-signed MCP call into your Amazon VPC through the AWS DevOps Agent private connection.
The private connection forwards the request to the Interface VPC Endpoint.
The endpoint routes the request over AWS PrivateLink to the Apache Spark Troubleshooting Agent for Amazon EMR, which AWS manages.
The MCP service reads from your data sources (Amazon EMR, Amazon S3, Amazon CloudWatch Logs) using the same IAM role AWS DevOps Agent assumed for the call.
When a CloudWatch alarm transitions to ALARM state (for example, a failed-jobs alarm for your Amazon EMR Serverless application), AWS DevOps Agent automatically triggers an investigation without manual intervention.
AWS DevOps Agent decides which tools to call based on the prompt. For a Spark failure, that includes the Apache Spark Troubleshooting MCP server you registered as a capability provider.
Each MCP request is signed with AWS Signature Version 4 using the IAM role assigned to the capability provider. The request travels from AWS DevOps Agent into your Amazon VPC through the private connection. This private connection is a managed VPC Lattice resource gateway you created during setup.
From the resource gateway, the request flows to the Interface VPC Endpoint for the Amazon SageMaker Unified Studio MCP service, then on to the Apache Spark Troubleshooting Agent. The traffic stays entirely on the AWS network.
The MCP server reads the inputs it needs from your AWS account using the IAM role that you assigned to the capability provider during MCP server registration. This role grants access to the Spark History Server event log and application source code in Amazon S3, the driver and executor stdout streams in Amazon CloudWatch Logs, and the job-run metadata from Amazon EMR Serverless.
The MCP server returns its diagnostic findings to AWS DevOps Agent. The agent then analyzes the results, identifies the root cause, and presents recommended fixes both code-level and configuration-level in your chat.

Setting up the demo

As part of this demo, this post includes a sample AWS CloudFormation template, tested in the us-east-1 Region, that provisions the following resources for the walkthrough:

A dedicated Amazon Virtual Private Cloud (Amazon VPC) with two private subnets in Availability Zones supported by the Apache Spark Troubleshooting Agent for Amazon EMR.
An Interface VPC Endpoint for the Apache Spark Troubleshooting Agent for Amazon EMR.
An IAM role that AWS DevOps Agent assumes to invoke the Apache Spark Troubleshooting MCP server with AWS Signature Version 4.
A deliberately failing PySpark workload running on Amazon EMR Serverless, including the Amazon EMR Serverless application, the Spark execution role, and the demo logs stored in Amazon S3 bucket.
An Amazon CloudWatch alarm that fires when the demo job fails. This alarm is used as the trigger for the agent investigation later in this section.

Step 1: Clone the repository

Clone the git repository for the CloudFormation template, PySpark script, and Parquet data.

git clone https://github.com/aws-samples/sample-aws-data-processing-and-analytics.git

Step 2: Deploy the AWS CloudFormation stack

Deploy the template using the following AWS CLI command.

cd sample-aws-data-processing-and-analytics/blogs/devops-agent-spark-mcp-integration

aws cloudformation create-stack \
  --stack-name spark-troubleshooting-demo \
  --template-body file://cloudformation/spark-troubleshooting-devops-agent-blog.yaml \
  --capabilities CAPABILITY_NAMED_IAM \
  --region us-east-1

The stack reaches CREATE_COMPLETE in approximately 4–6 minutes. When it does, capture the following stack outputs, which you paste into the AWS DevOps Agent console in the next two steps:

DemoVpcId — the VPC ID for the AWS DevOps Agent private connection.
DemoSubnetIds — the two subnet IDs for the AWS DevOps Agent private connection.
SMUSVpcEndpointSecurityGroupId — the security group ID.
TroubleshootingRoleArn — the IAM role Amazon Resource Name (ARN).
MCPEndpointURL — the MCP endpoint URL to register.
FailedJobsAlarmName — the CloudWatch alarm name to reference in your investigation prompt.
DemoBucket — the S3 bucket name where you copy the demo script and Parquet data.

To retrieve all outputs at once, use the following AWS CLI command.

aws cloudformation describe-stacks \
  --region us-east-1 \
  --stack-name spark-troubleshooting-demo \
  --query "Stacks[0].Outputs" --output table

# Get your bucket name from the stack outputs
DEMO_BUCKET=$(aws cloudformation describe-stacks --stack-name spark-troubleshooting-demo --region us-east-1 --query 'Stacks[0].Outputs[?OutputKey==`DemoBucket`].OutputValue' --output text)

# Copy the script
cd sample-aws-data-processing-and-analytics/blogs/devops-agent-spark-mcp-integration
aws s3 cp scripts/customer_events_aggregator.py s3://$DEMO_BUCKET/customer_events_aggregator.py

# Copy the Parquet data
aws s3 cp data/ s3://$DEMO_BUCKET/data/ --recursive

Step 3: Create an agent space

The agent space defines which AWS account and Region the agent monitors, which IAM role it assumes, and which capability providers, including MCP servers, it can call.

Follow the steps in Creating an Agent Space in the AWS DevOps Agent User Guide. When completing those steps, use the following values:

Parameter	Value
Name	data-pipeline-troubleshooting
Region	us-east-1
Agent Space role	Choose Auto-create a new DevOps Agent role — the console generates a DevOpsAgentRole-AgentSpace* role with AIOpsAssistantPolicy attached
Optional integrations	Not required

After the agent space reaches Active status, proceed to create the private connection.

Step 4: Create the AWS DevOps Agent private connection

AWS DevOps Agent uses the private connection to reach into your Amazon VPC. Follow the steps in Connecting to privately hosted tools in the AWS DevOps Agent User Guide. You can use either the console or the AWS CLI command documented under Create a private connection.

When completing those steps, use the following values from your CloudFormation stack outputs:

Parameter	Value
Name	A descriptive name (for example, spark-private)
VPC	DemoVpcId from your stack outputs
Subnets	Both subnet IDs from DemoSubnetIds
Security group	SMUSVpcEndpointSecurityGroupId
TCP port ranges (Advanced configuration)	443
Host address (Service target details)	sagemaker-unified-studio-mcp.us-east-1.api.aws
DNS resolution	In VPC (private DNS)
Certificate public key	None

After the connection reaches Active status, proceed to Step 5.

Step 5: Register the Apache Spark Troubleshooting MCP server as a capability provider

With the private connection in place, register the MCP server as a capability provider. Follow the steps in Registering an MCP server at the account level in the AWS DevOps Agent User Guide.

When completing those steps, use the following values:

Parameter	Value
Name	spark-troubleshooting
Endpoint URL	MCPEndpointURL from your stack outputs
Connect to endpoint using a private connection	Selected

Step 6: Add the MCP server to the agent space

With the MCP server registered, you need a workspace where investigations run. The agent space defines which AWS account and Region the agent monitors, which IAM role it assumes, and which capability providers, including MCP servers, it can call.

In the MCP Server section, choose Add.

Figure 2: MCP Server section of the agent space detail page

In the Add a capability dialog, locate spark-troubleshooting in the list of registered MCP servers and choose Add.

Figure 3: Add a capability with the spark-troubleshooting MCP server listed

On the Select MCP server tools page, both tools that the Apache Spark Troubleshooting Agent for Amazon EMR publishes are listed: analyze_spark_workload and analyze_spark_history_server_endpoint. Select both checkboxes, then choose Save.

Figure 4: The Select MCP server tools page with both Spark troubleshooting tools selected

The agent space connects to the MCP server, lists its tools, and displays 2 Available / 2 Connected. Both tools are now part of your agent’s catalog.

Figure 5: MCP Server section showing spark-troubleshooting connected with both tools available

Seeing it in action

To see the integration end to end, you submit a PySpark job, watch the CloudWatch alarm move to ALARM, and then ask AWS DevOps Agent to investigate using the alarm name.

The failing workload

The CloudFormation template provisioned an Amazon EMR Serverless application called analytics-events-platform and configured a sample PySpark job, customer_events_aggregator.py. The script simulates a common Python-side memory bug: a mapPartitions user-defined function accumulates 11 copies of every input row in an in-memory Python list before yielding results, while the job runs with spark.executor.pyspark.memory=256m. The Python worker process exceeds the 256 MB cap, the kernel kills it, Spark retries four times, and the stage is marked failed.

Submit the failing job

Run the DemoSubmitJobCommand from your stack outputs in your terminal. It looks like this:

aws emr-serverless start-job-run \
  --region us-east-1 \
  --application-id <DemoApplicationId> \
  --execution-role-arn <DemoExecutionRoleArn> \
  --name daily-customer-events-rollup \
  --job-driver '{"sparkSubmit":{"entryPoint":"s3://<DemoBucket>/customer_events_aggregator.py","entryPointArguments":["<DemoBucket>"],"sparkSubmitParameters":"--conf spark.executor.cores=2 --conf spark.executor.memory=1g --conf spark.executor.pyspark.memory=256m --conf spark.executor.instances=2"}}' \
  --configuration-overrides '{"monitoringConfiguration":{"s3MonitoringConfiguration":{"logUri":"s3://<DemoBucket>/logs/"}}}'

The command returns a jobRunId. Note it down. You will see it later in the agent’s investigation.

The job goes through PENDING to SCHEDULED to RUNNING to FAILED and reaches FAILED state in roughly four minutes.

Watch the CloudWatch alarm fire

The CloudFormation template also created a CloudWatch alarm named <DemoApplicationId>-FailedJobs (the exact name is in the FailedJobsAlarmName stack output). The alarm watches the FailedJobs metric in the AWS/EMRServerless namespace, scoped to your demo application, and flips to ALARM within a minute or two of the job failing.

Open the Amazon CloudWatch console, choose Alarms in the left navigation pane, and confirm the alarm is in In alarm state.

Figure 6: The Amazon CloudWatch alarm detail page showing the FailedJobs alarm in the In alarm state

Ask AWS DevOps Agent to investigate

Open your AWS DevOps Agent space.
In the left navigation pane, choose Operator Access, then choose Incidents.
Choose Start an investigation.
Paste the following prompt, replacing <FailedJobsAlarmName> with the value from your stack outputs:

CloudWatch alarm in us-east-1 just went into ALARM state. Investigate why and recommend a fix

Figure 7: AWS DevOps Agent Start an investigation panel with the Amazon CloudWatch alarm investigation prompt

The agent’s investigation chains together native AWS API tools and the Apache Spark Troubleshooting MCP tool you registered:

use_aws cloudwatch describe-alarms — fetches the alarm definition and reads its metric dimensions, identifying that the alarm is scoped to Amazon EMR Serverless application <DemoApplicationId>.
use_aws emr-serverless list-job-runs — finds the most recent FAILED job run on that application.
use_aws emr-serverless get-job-run — pulls the FAILED run’s metadata and last-known error.
spark-troubleshooting analyze_spark_workload — invokes the Apache Spark Troubleshooting Agent for Amazon EMR through the MCP capability provider, passing the application ID and job run ID. This is where the deep analysis happens.

Review the root cause and fix

When the investigation completes, AWS DevOps Agent presents the results across two tabs: Investigation timeline and Root cause.

The Investigation timeline shows every step the agent took: skills loaded, native AWS API calls made, and the moment it called the analyze_spark_workload MCP tool to analyze the failed Spark job. Each entry is expandable so you can audit the inputs and outputs.

Figure 8: Investigation timeline tab showing the sequence of agent tool calls and the spark-troubleshooting MCP invocation

The Root cause tab is where the answer lands. It is organized into three sections that mirror what an experienced engineer would write in an incident report:

Figure 9: The Root cause tab showing the impact summary, identified root causes, and key findings for the Spark memory exhaustion failure

Impact — what failed, when, and for how long. For our demo, this calls out that the daily-customer-events-rollup job on the analytics-events-platform application failed with a MemoryError and that the alarm transitioned to ALARM state at the time of the failure.
Root causes — the actual antipattern. The agent identifies that customer_events_aggregator.py combines three compounding issues: an expand_event function (line 23) that amplifies each input row 11×, a repartition(1) that funnels all data into a single partition on a single executor, and a collect() (line 31) that pulls the amplified dataset back to the driver. All three run with only 1 GB of executor memory.
Key findings — supporting facts behind the diagnosis, including the executor memory configuration, the application’s maximum capacity, and how the agent confirmed each fact from the analyzed artifacts.

Both the antipattern identification and the supporting evidence come from artifacts the agent could only reach through the MCP tool: the application source code in Amazon S3, the Spark History Server event log, and the query plan. Without the Apache Spark Troubleshooting Agent for Amazon EMR plugged in, AWS DevOps Agent would have stopped at “the executor exited with a memory error.”

Clean up

To avoid ongoing charges, delete the resources you created. Some resources are managed by the AWS DevOps Agent console and must be removed there first. Otherwise, the CloudFormation stack deletion fails.

In the AWS DevOps Agent console, open your data-pipeline-troubleshooting agent space, choose the MCP Server section, select spark-troubleshooting, and choose Remove.
From the Agent spaces list, select data-pipeline-troubleshooting and choose Delete.
In Capability Providers, select spark-troubleshooting and choose Deregister.
In Capability Providers → Private connections, select smus-spark-private and choose Delete.
Delete the AWS CloudFormation stack. This removes the Amazon VPC, the Interface VPC Endpoint, the security group, the IAM role, the Amazon EMR Serverless application, the Spark execution role, the Amazon CloudWatch alarm, and the demo logs bucket.

aws cloudformation delete-stack \
  --region us-east-1 \
  --stack-name spark-troubleshooting-demo

Conclusion

In this post, you connected the Apache Spark Troubleshooting Agent for Amazon EMR to AWS DevOps Agent as a custom MCP capability provider. You kept the traffic on the AWS network with AWS PrivateLink, and ran a failing PySpark job to see the integration end to end. A CloudWatch alarm fired, you asked the agent to investigate, and a single chat session returned the root cause along with code and configuration fixes.

You can extend this pattern beyond the demo scenario. Consider connecting the MCP server to agent spaces that monitor your production Amazon EMR environment. Any Spark job that writes a History Server event log becomes diagnosable through the same workflow.

To continue learning, explore the following resources:

AWS DevOps Agent documentation — learn how to create agent spaces, configure integrations, and manage investigations.
Apache Spark Troubleshooting Agent for Amazon EMR setup guide — detailed prerequisites and configuration options for the MCP server.
Connecting MCP servers to AWS DevOps Agent — register additional MCP servers to expand your agent’s capabilities.
Sample code on GitHub — clone the CloudFormation template, PySpark script, and Parquet data used in this walkthrough.

If you’ve already integrated the Apache Spark Troubleshooting Agent into your operational workflow, or if you’re exploring other MCP-based extensions for AWS DevOps Agent, we want to hear about your experience. Share your thoughts and questions in the comments.

About the authors

Deliver real-time data to streaming tables for Apache Iceberg with Amazon Kinesis Data Streams

Nikit Pednekar — Mon, 31 Aug 2026 23:03:25 +0000

Amazon Kinesis Data Streams now supports streaming tables, a fully managed capability that continuously delivers your streaming data as queryable Apache Iceberg tables on Amazon S3 Tables. Amazon S3 Tables is a capability of Amazon Simple Storage Service (Amazon S3). Streaming tables reduce data delivery costs to S3 Tables by up to 50% compared to self-managed alternatives and reduce downstream query costs by up to 30% through intelligent inline compaction that eliminates the small file problem. You need no custom applications, no self-managed compute, and no operational overhead.

Customers increasingly want to unify streaming data with Apache Iceberg for near-real-time analytics, fraud detection, personalization, and artificial intelligence and machine learning (AI/ML) feature pipelines. But integrating the two has meant operating complex custom connectors, managing format conversions, and contending with the performance impact of many small Parquet files that slow queries and increase costs. Streaming tables solve this: configure delivery in a few steps from the console or through APIs, and your data becomes queryable from Amazon Athena, Amazon Redshift, and Apache Spark within minutes. Tables are automatically registered in AWS Glue Data Catalog, making them immediately discoverable for analytics engines and AI agents.

For workloads that don’t require Iceberg table format, you can also deliver streaming data to Amazon S3 general purpose buckets. Delivery is in the source data format, ideal for archival, backup, and ML training data pipelines, with the same serverless, fully managed delivery and no infrastructure to operate.

Challenges with delivering streaming data to Apache Iceberg

Customers today face three challenges when integrating streaming data with Apache Iceberg.

Operational complexity: Connecting Kinesis Data Streams to Iceberg tables today requires deploying and maintaining custom connectors, Apache Flink jobs, or consumer applications. Teams must manage pipeline failures, handle format conversions, scale infrastructure, and monitor delivery reliability. These operational tasks consume significant engineering time and introduce ongoing risk of downtime.

Resiliency and the small file problem: Without proper coordination, simultaneous writes from multiple high-throughput shards can conflict, leading to failed commits, data freshness delays, and degraded performance. Streaming ingestion of high-volume data creates large numbers of small Parquet files in Iceberg tables, forcing a difficult trade-off between data freshness and query efficiency.

Cost: Customers typically spend up to $28/TB operating streaming extract, transform, and load (ETL) pipelines from Kinesis Data Streams using self-managed alternatives based on internal analysis. This creates a high price barrier to getting streaming data into queryable formats and makes cost unpredictable as volume grows.

How delivery to streaming tables solves these challenges

Streaming tables are a native capability built directly into Amazon Kinesis Data Streams. There is no separate service to deploy, no connector to version, and no consumer application to maintain. You enable delivery in a few steps from the console or through APIs.

Zero operational overhead: Streaming tables remove the need to build and operate custom consumer applications for data delivery. No pipeline infrastructure to provision, no scaling logic to write, no failure handling to implement. The capability automatically scales to process gigabytes per second of throughput.

Built-in resiliency: Streaming tables provide write coordination and exactly once delivery semantics across all shards in your stream, resolving concurrent writer conflicts and ensuring data integrity without manual intervention.

Intelligent compaction, no trade-offs: During ingestion, streaming tables perform inline compaction that produces query-optimized Parquet files, eliminating the small file problem while maintaining minute-level data freshness. This reduces downstream query costs by up to 30 percent compared to uncompacted delivery.

Consumption-based pricing: You pay only for data delivered: $14/TB for Iceberg delivery to S3 Tables in US East (N. Virginia) Region (us-east-1) (50% savings compared to self-managed alternatives) and $11/TB for general purpose S3 delivery (60% savings compared to self-managed alternatives). When your stream is idle, you pay nothing for delivery. Combined with Kinesis Data Streams On-Demand Advantage pricing, which eliminates per-shard charges and scales automatically, the entire path from ingestion to queryable Iceberg tables operates on a pure consumption model.

End-to-end managed streaming analytics architecture

With delivery to streaming tables, you now have a fully managed end-to-end real-time data architecture from data ingestion through storage to analytics. Your producers publish events to a Kinesis Data Stream, which continuously delivers data as optimized Iceberg read-only tables in S3 Tables. From there, you can query your streaming data using analytics engines like Amazon Athena, Amazon Redshift, Amazon EMR (Apache Spark), or Apache Flink. You can also let AI agents discover and reason over your data through Glue Data Catalog semantic search. This managed experience removes the intermediate infrastructure that customers previously assembled: separate connector clusters, compaction jobs, and custom consumers. It replaces them with a single, serverless pipeline from stream to insight.

The following diagram illustrates this end-to-end architecture.

Figure 1: End-to-end managed streaming analytics architecture from ingestion to query

Getting started

To get started, sign in to the Amazon Kinesis Data Streams console, navigate to your streams, and enable delivery to streaming tables in a few steps. Specify the stream you want to deliver, configure your schema settings using AWS Glue Schema Registry, and choose your destination S3 Tables location. After you enable it, delivery to streaming tables immediately begins materializing your streaming data as queryable Iceberg tables in S3 with no further intervention required. There’s no infrastructure to provision and no minimum commitment. You pay only for data delivered.

Additionally, you can use Amazon Kinesis Data Streams APIs to programmatically set up, update, or delete delivery to streaming tables configurations for your data streams. With these APIs, teams can build agentic workflows and infrastructure-as-code patterns to manage configurations across multiple data streams at scale.

Getting started with the Kinesis Data Streams Agent Skill

The Kinesis Data Streams Agent Skill provides AI-assisted guidance for setting up streaming tables integrations for your existing or new data streams. The skill helps you configure delivery to S3 Tables (Iceberg) or S3, including schema registry setup, AWS Identity and Access Management (IAM) role configuration, and validation.

Installing as an Agent Skill

Agent Skills are discovered automatically by compatible tools through the SKILL.md file. Refer to the Agent Toolkit for AWS Skill Installation Guide to install the managing-amazon-kinesis-data-streams Agent Skill. We also recommend you install the AWS MCP Server in your developer tool of choice, which exposes tools for searching AWS documentation, blogs, and Skills dynamically at runtime. These capabilities make agents more accurate and powerful for AWS related development and operational tasks, and make skill discovery and installation more flexible. Refer to Setting up the AWS MCP Server for guidance on installing the AWS MCP Server in your environment.

For example:

aws configure agent-toolkit
aws agent-toolkit add-skill --skill-name managing-amazon-kinesis-data-streams

To verify the installation, interact with the skill in your preferred tool.

To start delivering data from your data streams to Apache Iceberg tables in real time, prompt “Create me a streaming table on my events data stream” to your agent of choice:

Figure 2: Prompting the agent to create a streaming table

The agent dynamically loads the managing-amazon-kinesis-data-streams skill and starts by gathering the available resources in your AWS account for the streaming tables integration. After it gathers that data, it confirms the resources to use or create, and creates the integration:

Figure 3: The agent confirming resources before creating the integration

After creating the integration, the agent summarizes the status and can then help with any other operational tasks with your data. For example, the agent can help you set up AWS Lake Formation permissions to query the data in S3 Tables with Athena, or configure your table maintenance behavior in S3 Tables:

Figure 4: The agent offering follow-up operational tasks

Conclusion

Streaming tables are available in all AWS Regions where Amazon Kinesis Data Streams is offered. Pricing is $14/TB for delivery to S3 Tables (Apache Iceberg) and $11/TB for delivery to general purpose S3 buckets. To learn more, visit the documentation and pricing pages.