Processing hundreds of thousands of events per second while maintaining sub-second latency is a challenge many organizations face when building real-time data-driven applications. When filter policy changes propagate in up to 15 minutes, dynamic event routing becomes impractical, forcing teams to over-consume events and discard over 90% after costly per-event lookups. Smartsheet, a work management solution serving millions of users and processing hundreds of thousands of events per second to power features like live collaboration, workflows, and real-time notifications, faced exactly this problem.
In this post, you learn how Smartsheet built a Real-time Dynamic Filtering (RDF) system on Amazon Managed Service for Apache Flink, cutting messaging costs by over $40,000 per month and improving live collaboration latency by 1.8x.
The challenge: Static filter policies in a dynamic world
The Smartsheet event-driven architecture publishes hundreds of thousands of events per second to an Amazon Simple Notification Service (Amazon SNS) topic. Internal teams subscribe to this topic, typically by creating an Amazon Simple Queue Service (Amazon SQS) queue with an associated SNS filter policy defined through infrastructure as code (IaC). These filter policies are typically static and specify the types of events a consumer wants to receive, such as “sheet row created,” “sheet row updated,” or “sheet row deleted.”
Although SNS supports programmatic changes to filter policies, the SNS documentation notes that changes can take up to 15 minutes to take effect. This eventual consistency window created a significant problem for Smartsheet live collaboration feature.
Live collaboration requires knowing, in real time, which sheets have active collaborators. When a user opens a sheet, the system needs to immediately start receiving events for that sheet. When they close it, the system should stop. With a 15-minute propagation delay on filter policy changes, dynamic per-sheet filtering through SNS was impractical.
The workaround was brute force: subscribe to all events (hundreds of thousands per second), pull them into an SQS queue, and use compute to check each event against Amazon DynamoDB to determine whether the sheet had active collaborators. Over 90% of events were discarded after this lookup.
Figure 1: Before RDF — all events flow through SNS to SQS, with per-event DynamoDB lookups to filter. Over 90% of events are discarded after processing.
Every event published to the SNS topic is delivered to the SQS queue, regardless of whether any consumer needs it.
The consumer AWS Lambda reads every message from the SQS queue and must evaluate each one individually.
For each event, the consumer queries DynamoDB to check whether the sheet has active collaborators. This per-event lookup adds latency and DynamoDB read costs on the hot path.
After the DynamoDB lookup, over 90% of events are found to have no active collaborators and are discarded.
This approach had three compounding cost and performance problems:
SNS-to-SQS data transfer costs: approximately $10,000 per month to deliver all events to the queue
SQS costs: approximately $30,000 per month to receive, process, and delete the full event volume
DynamoDB costs and latency: per-event lookups to check collaborator status added load to DynamoDB and increased end-to-end data delivery latency
The solution: Real-time Dynamic Filtering with Apache Flink
To solve this, Smartsheet built a system called Real-time Dynamic Filtering (RDF) on Amazon Managed Service for Apache Flink. The core insight was to move the filtering logic into the stream processing layer itself, using Flink’s KeyedCoProcessFunction, a feature that joins and processes multiple streams by a shared key, to maintain dynamic filter policies in Flink state (RocksDB).
How it works
The RDF Flink application reads from two streams:
Filter policy stream, sourced from Amazon DynamoDB Streams. When a team calls the RDF client to change their filter policy (for example, “start receiving events for sheet X”), the change is written to a DynamoDB table and propagated through DynamoDB Streams to the Flink application.
Data stream, the stream of sheet events (creates, updates, deletes) that were previously delivered through SNS.
One challenge remained: some consumers need every event, regardless of sheet. When a consumer subscribes to all events, the system needs every parallel Flink task to know about it. The team solved this using Flink’s broadcast state, which replicates a small set of “subscribe to everything” policies across all tasks. Because only a handful of consumers use this mode, the memory overhead stays negligible.
Figure 2: After RDF — consumer teams update filter policies via client libraries. DynamoDB Streams propagates changes to the Flink application, which filters the data stream in real time using keyed state (RocksDB) for specific sheet subscriptions and broadcast state for “all sheets” subscriptions.
When a consumer team wants to start or stop receiving events for a specific sheet, it calls the RDF client, a thin wrapper over the DynamoDB SDK. The filter policy change is written to that consumer’s dedicated DynamoDB table. Each consumer has its own table, providing isolated permissions and preventing noisy neighbor issues.
DynamoDB Streams captures every filter policy change as a change data capture (CDC) record and streams it to the Flink application in real time.
Filter policy records
Filter policy records for specific sheets are routed to the KeyedCoProcessFunction, keyed by SheetID. This makes sure that filter state and event data for the same sheet are co-located in the same Flink parallel task. State is stored in the RocksDB backend, which uses memory when available and spills to disk when necessary, so the system to scale without JVM heap constraints.
Filter policy records where a consumer has called listenToAllEvents() are broadcast to all parallel Flink tasks via Flink’s broadcast state. Because broadcast state lives in JVM heap, it is used exclusively for these “all sheets” records (of which there are very few), keeping the heap footprint small.
The full stream of CDC events flows into the KeyedCoProcessFunction, partitioned by SheetID. Each parallel task receives only the events for the sheets it is responsible for and applies the corresponding filter state to decide whether to forward or drop each event.
The broadcast state (containing “all sheets” subscriptions) is made available to all parallel instances of the KeyedCoProcessFunction, so that consumers subscribed to all events are never filtered out regardless of which task processes their events.
Only events that match an active filter policy are forwarded to the consumer’s SQS queue. The result: sub-second filter policy propagation (p95 ≤1s), elimination of per-event DynamoDB lookups, and over $40,000/month in cost savings.
Critically, because the filter policy state is persisted in Flink’s RocksDB state backend, the application does not need to perform a DynamoDB lookup for every event. Within 1 second of a filter policy change, the Flink application reads the change from the DynamoDB Streams source, updates its internal state, and begins filtering the data stream accordingly.
Results
The impact of RDF was immediate and measurable across multiple dimensions:
Cost reduction
Cost category
Before RDF
After RDF
Monthly savings
SNS → SQS Data Transfer
~$10K/month
Eliminated
~$10K
SQS Event Ingestion
~$30K/month
~$2K
~$28K
DynamoDB Collaborator Lookups
Significant load
Eliminated (state in Flink)
Included in total
AWS Lambda
~$12K/month
~$5K/month
~$7K
Total
~$45K/month
Latency improvement
1.8x improvement in live collaboration data delivery latency. Users see changes from collaborators faster than before.
Filter policy propagation reduced from up to 15 minutes to a p95 of under 1 second
If your architecture follows a similar fan-out pattern where consumers discard a large percentage of events after per-event lookups, you could achieve comparable cost reductions by moving filtering into the stream processing layer. The savings scale with your event volume and the percentage of events currently discarded.
Key design decisions
Several architectural choices were critical to the success of this solution:
Keyed state with selective broadcast: Specific sheet subscriptions are stored in keyed state using the RocksDB state backend. The system scales to a large number of filter policies without JVM heap constraints. Flink’s broadcast state is used only for the small number of “all sheets” subscriptions, where every parallel task needs visibility. Because broadcast state is stored in JVM heap, limiting its use to these few records keeps the heap footprint manageable.
DynamoDB Streams as the filter policy source: Rather than building a custom control plane, the team used DynamoDB Streams to propagate filter policy changes. DynamoDB Streams gave the team durability, ordering guarantees, and a native Flink source connector integration.
RocksDB state backend: Persisting filter state in RocksDB eliminated the need for external lookups on the hot path, keeping per-event processing latency low even as the number of active filter policies grows.
Client library abstraction: Publishing internal Golang and Java clients lowered the adoption barrier. The client is a thin abstraction on top of the DynamoDB SDK. Each consumer has its own dedicated DynamoDB table and corresponding filter stream, which provides two benefits: it allows fine-grained AWS Identity and Access Management (AWS IAM) permissions per client, and it mitigates the noisy neighbor problem by isolating each consumer’s filter policy traffic. Teams don’t need to understand Flink internals. They interact with a simple API to manage their subscriptions.
Next steps
The live collaboration team was the first adopter of RDF, but the architecture was designed as a shared platform. Smartsheet is now expanding RDF to additional internal teams, including workflow automation and notification routing, where similar fan-out patterns exist. The team is also exploring automatic scaling policies to optimize Flink cluster costs during off-peak hours.
Conclusion
Smartsheet Real-time Dynamic Filtering system demonstrates how Amazon Managed Service for Apache Flink can solve problems that go beyond stream processing. By combining Flink’s broadcast state pattern with CoProcessFunction, Smartsheet replaced a costly and latency-bound SNS/SQS fan-out architecture with a sub-second dynamic filtering platform. The result: over $40,000 per month in savings, 1.8x improvement in live collaboration latency, and a reusable platform that multiple teams are now adopting.
If you process high-volume event streams and need to dynamically control which events reach specific consumers, this pattern can help you reduce costs and latency, whether for live collaboration, workflow automation, notification routing, or multi-tenant event delivery.
To learn more about the services used in this post, visit:
At Grab, analytics sits close to almost every decision that matters. Our north star is the democratization of intelligence, ensuring that anyone making a business call has immediate access to trustworthy answers.
Over the last two years, model capability has crossed a threshold enabling this shift. Agents now do in minutes what used to take a week: preparing the data, writing queries, running deep analysis and surfacing insights for business opportunities, designing the experiment and interpreting the results, drafting the commentary that follows, and more. Our throughput is no longer rate-limited by how fast an individual can write code, build a deck, or run a deep-dive. It is rate-limited by how fast we can frame the right problem, judge the right answer, and influence the right decision.
As autonomy climbs, an analyst’s center of gravity moves from producing the artifact to owning the question and the call behind it, and the role evolves to become part builder, part advisor, part strategist, owning the loop rather than running it. That unlocks two things at once: work we already do, faster and at lower marginal cost, and work we could never staff before, sitting beside every product manager, business owner, and operator at the moment they decide.
The ladder
We were heavily inspired by Dan Shapiro’s framing of five levels for AI coding. We use a similar ladder that defines how much of the loop an agent should own and where human judgment stays for every analytics loop.
One distinction runs across every level: who owns the loop, and where human judgment is required.
Level
What
Human role
Agent role
L2
AI-Assisted
Owns and executes every step; uses AI to draft, suggest, summarize
Drafts SQL, suggests a visualization
L3
Human plans, agent owns steps, human reviews
Frames the question, picks the metric, segment and comparison frame, reviews evidence, owns the recommendation
Discovers data, writes and runs the query, sanity checks, drafts the write-up, flags caveats
L4
Agent plans, agent owns workflows, human reviews
Sets intent and guardrails; reviews at gates (anomaly, novel scope, sensitive cut); owns the stakeholder relationship and sign-off
Orchestrates discovery through query, analysis, validation, narrative and publish; runs validation, escalates exceptions
Detects anomalies and opportunities, runs the loop, surfaces insight, evolves the metric layer, context and skills
Human judgment remains at every level, and autonomy never removes accountability. Humans own problem framing, canonical metric definitions, the causal story behind a move, business-case assumptions, the go/no-go, and the stakeholder relationship. A higher level means more of the mechanical loop sits with the agent and more human attention concentrates on the ambiguous, high-stakes work.
Making the climb
Five core capabilities move a workflow up the ladder. They also gate the climb in order: L3 needs execution and certified context, L4 needs gates and agentic review good enough that reviewing only at gates is honest, L5 needs a learning loop that closes.
Execution: A stack that runs the loop end to end rather than a notebook/workflow a human drives.
Knowledge: Metrics certified at the right grain, discoverable in our catalog, grounded in context an agent can read. Ambiguous definitions cause most analytics slop.
Control: Repeatable expectations become mechanical checks, while human review handles what a rule cannot.
Review and governance: Agents check their own output against the gates and escalate on defined triggers. We govern definitions, targets, risk and exceptions.
Learning: When an agent fails the same way twice, we encode the fix into context documents, golden datasets, evals and gates.
What this looks like in practice
What follows is a set of explorations from the last two years. Some run in production today, while others are still teaching us where the limits are.
Loops that run end to end
Spartan is our end-to-end agentic analytics workflow, embedded across surface areas (like Slack), and most of its usage comes from people who are not analysts. On any given day, the Slack channel carries ads salespeople pulling spend breakdowns for a named merchant, campaign managers sizing audiences for a target segment, and country teams asking why a number moved week on week. All of it in plain business language.
Two requests from July best demonstrate how it works. A commercial manager asked why revenue fell in the Philippines mid-market segment in the last two weeks of June. Separately, a product manager asked for a summary of a frequency-cap experiment on the ads surface. Both arrived as natural language questions in Slack, then took entirely different routes through the system.
The router reads the first as a root-cause question and sends it down the diagnostic path. It loads the analysis framework for ads revenue, which is codified knowledge of how the metrics in that domain relate to each other, which dimensions are worth decomposing, and what counts as a meaningful move. Then it works through segment, market, and campaign type against certified metrics to isolate what changed. The second question never touches the data lake. The router reads it as an experiment question, selects the experiment skill, pulls the pre-computed scorecard and the test’s own metadata from our experiment platform, and summarizes the read rather than recomputing it. This is powered through more than 50 skills and 120 analysis frameworks that sit behind that routing decision. There is an index that tells the agent what to search, the context tells it how to query, and the framework tells it how to think. Because the frameworks are shared rather than living in an analyst’s head, the interpretation compounds instead of being re-derived every time someone asks.
The second example of such loops is Scarlet, which powers near self-healing pipelines (L4). When a pipeline fails, an agent runs the root-cause analysis, triages, and then either fixes it or hands it to the team that owns the upstream problem. It escalates when the failure sits outside its documented runbooks or the predefined gates fire.
Figure 1.
Context that maintains itself
Context sets an agent’s ceiling. An agent that does not know a metric’s grain, its exclusions and its caveats will guess, and will produce outputs confidently and wrong with speed at scale.
Realizing the criticality of this, we have dedicated platform investment into this, as well as dedicated functional bandwidth to generate context docs. We maintain more than 5,000 certified tables and metrics, 4,000 context documents, and 2,000 golden records.
Figure 2.
Context goes out of date faster than anyone maintains it by hand, so we build the maintenance into our workflows. We built ContextIQ, and its Context Lifecycle Manager, to treat context as something with a lifecycle rather than a document somebody wrote once. A newer skill of ours reads an instrumentation spec alongside the existing context, proposes the SQL changes that follow from it, and updates the context document in the same pass. We work the problem from the other direction too. When we categorize an agent failure in production, we patch the context document behind it.
Two analysts recently used our internal bots to understand how packaging fee is stored as a configuration. Having found the answer, the bot opened a merge request that committed both a certified-context table reference and a golden-dataset test case, so the next agent to ask the same question would find the answer already documented and the check already in place. One of the analysts spotted a false positive in it. The bot corrected itself and reopened the merge request. That is the learning capability working as designed, and it happened without anyone setting out to demonstrate it.
Loops that run unattended
The step from L3 to L4 is mostly the step from interactive to scheduled, and it is where we go down the path of autonomous execution, because no human is watching at the moment the work runs.
We already run automated metric and OKR commentaries in production, both for working and leadership teams. Our OKR bots push commentaries directly to stakeholders, and we have made this available to every team as a platform service. The agent reasons the way an analyst would: it reads the certified metric, judges whether the move is meaningful against standard deviation over six months and year over year, then decomposes it: which funnel stage moved, which operational metrics moved alongside it, which holiday or campaign falls in the window. It compares the seasonal pattern against the same transition a year earlier, so it can say a Songkran dip is amplified rather than merely expected. Importantly, it also scans across internal context to understand changes on the ground: delivery fee and incentive moves, merchant visibility shifts, experiments shipped in the same period. And it grounds all of that in our own context documents, which is what keeps the narrative about the business rather than generic model output. The analytics owner is tagged on every report, and edits sync back so corrections land in the system.
Figure 3.
Analysts as builders
The clearest evidence that our center of gravity has moved is BriX, an internal portal we built and run ourselves.
Figure 4.
The premise is to configure once, host everywhere. We configure a system prompt, a set of context files, a model, the MCP connections and an interface once, and what comes out is a purpose-built analytics surface for a particular team or job. Each one inherits certified data, permissions and reusable agent skills rather than being wired up from scratch, and it runs wherever the work already happens: in Slack, invoked from inside an IDE, or on a schedule with nobody watching. We have grown usage more than tenfold since September 2025, and every function at Grab now has users on it. Our aim is to put L3 workflows in the hands of people who are not advanced users.
We run it without a product manager, a technical program manager or a designer. Our data engineers own the product, the platform, the support queue and the eval loop, with Claude Design doing the interface work and the builders triaging their own bugs. In the first half of this year they shipped 31 production deployments, 283 merged requests and 60 features.
Two of our apps show the range:
Insights Lab is the general-purpose surface: a stakeholder asks for a metric, a breakdown or a root-cause in natural language, and the agent loads a specialist skill and answers off certified metrics rather than from memory.
We built Funnelytics so people would stop asking us to rebuild funnels. A funnel question used to mean an analyst writing the query and then assembling the view in Tableau or Power BI, and doing it again the next time someone wanted a slightly different path through the app. Now a stakeholder picks the events they care about and Funnelytics queries the raw event stream, builds the Sankey and funnel views, and writes the summary. If they cannot find the right instrumentation, which happens often on products still being redesigned, a live debugger lets them tap through the app on their own phone and watch the events fire.
Figure 5.
Figure 6.
Outside the portal, the same instinct shows up in smaller ways. Our analysts have been building more bespoke tools that enable better workflows for themselves and stakeholders.
The path forward
In February, 44% of the tickets our analysts closed were mechanical (data preparation, alerting, reporting); by June, that share fell to 30%. That capacity was redirected to other higher-leverage work. Building tools with AI to improve productivity increased ~4x.
Figure 7.
Importantly, our cycle times reduced ~33%: median cycle time fell from 3 business days to 2, and the 75th percentile from 7 days to 6.
Figure 8.
The sharpest version of this sits in a Slack channel where self-serve bots are enabled. In March, an analyst had to step into half of them; by May, it was under a quarter. The share answered with no human involvement rose from 53% to 67% for metric questions, 63% to 90% for data pulls, and 50% to 81% for SQL requests. Just under three in four of the threads were started by someone outside the analytics team, and 85% of them got a first response inside a minute. Nearly every thread is logged as a ticket on the team’s board, and roughly two-thirds of the data exploration tickets on that board now arrive through the channel rather than through an analyst. Even on the conservative assumption of 1-2 days queuing each, that is 230 to 470 business days of stakeholder waiting that did not happen, and 233 questions that never entered anyone’s backlog.
None of these arrived on a roadmap. They came from analysts who saw a loop worth automating and built it, which is why the climb is uneven. These have been strong proof points for us to believe our investments are working as many of these workflows are starting to operate at scale. We will keep experimenting and iterating, and we expect to get a fair amount of it wrong. An analyst who owns a loop, sets its quality bar and reviews its exceptions is doing a different job from one who answers questions. Most of our team is somewhere in that transition today, and we truly believe it is changing what analytics is at Grab.
Join us
Grab is a leading superapp in Southeast Asia, operating across the deliveries, mobility, and digital financial services sectors. Serving over 900 cities in eight Southeast Asian countries: Cambodia, Indonesia, Malaysia, Myanmar, the Philippines, Singapore, Thailand, and Vietnam. Grab enables millions of people every day to order food or groceries, send packages, hail a ride or taxi, pay for online purchases or access services such as lending and insurance, all through a single app. We operate supermarkets in Malaysia under Jaya Grocer and Everrise, which enables us to bring the convenience of on-demand grocery delivery to more consumers in the country. As part of our financial services offerings, we also provide digital banking services through GXS Bank in Singapore and GXBank in Malaysia. Grab was founded in 2012 with the mission to drive Southeast Asia forward by creating economic empowerment for everyone. Grab strives to serve a triple bottom line. We aim to simultaneously deliver financial performance for our shareholders and have a positive social impact, which includes economic empowerment for millions of people in the region, while mitigating our environmental footprint.
Powered by technology and driven by heart, our mission is to drive Southeast Asia forward by creating economic empowerment for everyone. If this mission speaks to you, join our team today!
Amazon S3 Tables with Amazon Redshift gives you a powerful combination for analytical workloads on Apache Iceberg tables. But as query volumes grow, small inefficiencies compound. For example, repeated queries, such as dashboards refreshing hourly or analysts running the same joins throughout the day, scan data directly from Amazon Simple Storage Service (Amazon S3) every time. The fully qualified three-part table references ([email protected]) add friction for business intelligence (BI) tools and end users who expect simpler SQL syntax. And without tuning the way S3 Tables organizes your data files, queries read more files than necessary. When you address these three areas, your S3 Tables queries in Amazon Redshift become faster, simpler, and more cost-efficient, whether you’re powering a recurring dashboard or supporting ad hoc analysis at scale.
This is the third post in our S3 Tables and Amazon Redshift series. The first post covered getting started with querying Apache Iceberg tables, and the second post walked through enterprise-scale governance and access controls. In this post, you address those performance and usability gaps with three approaches:
Create external schemas to simplify queries from three-part notation down to two-part notation.
Build materialized views that store pre-computed results locally so repeated queries skip the S3 scan.
Configure S3 Tables compaction strategies so the data file layout matches your query patterns.
The following diagram shows how these three approaches work together. External schemas [1] simplify query syntax through AWS Lake Formation resource links [2], materialized views [3] store pre-computed results locally in Amazon Redshift, and S3 Tables compaction [4] optimizes the underlying file layout for your query patterns.
You can use this syntax, but it can be cumbersome in business intelligence (BI) tools, manually typing queries, and in application code. This syntax also requires the user to use IAM federation. By creating an external schema, you can reference the same tables with a concise two-part notation:
SELECT * FROM s3tables_schema.examples;
To set this up, you create a Lake Formation resource link that maps to your S3 Tables catalog, then create an external schema in Amazon Redshift that points to that resource link. Your setup differs slightly depending on whether your users authenticate through IAM federation or database credentials. While this doesn’t change query performance, it removes a common barrier to adoption by simplifying the reference.
Create a Lake Formation resource link
Both authentication methods require a resource link in Lake Formation that points to your S3 Tables database.
In the Lake Formation console, choose Databases under Data Catalog.
On the Create menu, choose Resource link.
Configure the resource link with the following settings:
Resource link name:s3tables_rl
Destination Catalog: Your account ID (for example, 111122223333)
Shared Database: Your S3 Tables database (for example, icebergsons3)
Shared Database’s Catalog ID: Your S3 Table bucket in the format 111122223333:s3tablescatalog/redshifticeberg
If your users connect to Amazon Redshift through IAM federation, create the external schema with the SESSION keyword. This passes the federated user’s credentials through to Lake Formation for access control:
CREATE EXTERNAL SCHEMA s3tables_schema
FROM DATA CATALOG
DATABASE 's3tables_rl'
CATALOG_ID '111122223333'
IAM_ROLE 'SESSION'
CATALOG_ROLE 'SESSION';
Lake Formation evaluates your permissions based on your federated user’s IAM role, and sees only the tables and columns their role allows. This is the recommended approach for new deployments because it provides fine-grained access control without additional role management.
Option B: External schema for database users
External applications like Tableau, PowerBI, and custom ETL tools often authenticate with database credentials instead of IAM federation. These users need an IAM role to access S3 Tables on their behalf.
Create an IAM service role to access S3 Tables:
You create a role (for example, S3TableAccessRole) with a trust policy that allows Amazon Redshift to assume it:
In the Lake Formation console, grant the S3TableAccessRole DESCRIBE access on the database and SELECT access on the tables for your resource link. For detailed steps, see Granting Lake Formation permissions.
CREATE EXTERNAL SCHEMA s3tables_schema
FROM DATA CATALOG
DATABASE 's3tables_rl'
IAM_ROLE 'arn:aws:iam::111122223333:role/S3TableAccessRole';
Then grant access to your database users:
GRANT USAGE ON SCHEMA s3tables_schema TO my_database_user;
Query with two-part notation
With either option, you can now query S3 Tables using the simpler two-part notation:
SELECT * FROM s3tables_schema.examples LIMIT 10;
You can use this notation in BI tools, JDBC/ODBC connections, and application code and no longer need to know the underlying catalog structure.
Accelerate queries with materialized views
When you repeatedly query S3 Tables, each execution scans the external data from S3. Materialized views store pre-computed results in Amazon Redshift, so subsequent queries read from local storage instead of scanning S3 on every run.
Redshift supports incremental refresh for materialized views on Apache Iceberg tables, including INSERT, DELETE, UPDATE, and table compaction operations. After the initial creation, Amazon Redshift processes only the rows that changed since the last refresh when you run subsequent refreshes, rather than recomputing the full result set. This helps reduce both the time and compute cost of keeping your views current, especially for large tables with frequent changes.
The following example creates a materialized view that joins the examples table in S3 Tables with a local categories table in Amazon Redshift. You can use a materialized view to pre-compute daily record counts and data samples per category:
CREATE MATERIALIZED VIEW mv_daily_category_summary
DISTSTYLE KEY
DISTKEY (category_id)
SORTKEY (insert_date)
AS
SELECT
c.category_id,
c.department,
e.insert_date,
COUNT(*) AS record_count,
COUNT(DISTINCT e.id) AS unique_ids
FROM s3tables_schema.examples e
JOIN public.categories c
ON c.category_id = e.category_id
GROUP BY c.category_id, c.department, e.insert_date;
Query the materialized view directly:
SELECT category_id, department, insert_date, record_count
FROM mv_daily_category_summary
ORDER BY record_count DESC
LIMIT 10;
Your query can now read from local Amazon Redshift storage and typically returns results without scanning S3 Tables:
Refresh strategies
You have two options for keeping materialized views current:
Automatic refresh: Set AUTO REFRESH YES in the view definition to have Amazon Redshift automatically refresh the view in the background when it detects changes to the base tables. This is a good fit for dashboards and reports that can tolerate a short delay between data changes and query results. Note that automatic refresh requires Option B (database user) when creating the external schema, and the default is AUTO REFRESH NO.
Manual refresh: Run REFRESH MATERIALIZED VIEW when you need to control the timing:
Use manual refresh when you need to coordinate updates with data loading pipelines or when you want to refresh during off-peak hours.
Tune S3 Tables compaction for your query patterns
S3 Tables automatically compacts small Parquet files into larger ones in the background. This compaction reduces the number of read requests your query engine must make, which can improve query performance. By default, compaction targets a file size of 512 MB, configurable between 64 MB and 512 MB. Four compaction strategies are available, and choosing the right one for your query patterns can make a measurable difference.
Compaction strategies
Strategy
When to use
How it works
Auto
You want S3 to decide for you
Selects sort compaction for sorted tables, binpack for unsorted tables
Binpack
General-purpose workloads, unsorted tables
Combines small files into larger files (100 MB+) and applies pending row-level deletes
Sort
Queries frequently filter on a single column (e.g., insert_date)
Organizes data by the table’s sort-order columns during compaction
Z-order
Queries filter on two or more columns together (e.g., insert_date and category_id)
Blends multiple column values into a single scalar for sorting
Binpack improves performance by reducing the number of files a query engine reads. Sort compaction goes further. By ordering data within files, it enables query engines to skip entire files based on column min/max metadata during predicate pushdown. This is effective for queries that filter on the sort column, such as date-range filters. Z-order extends this benefit to queries that filter on multiple columns simultaneously, at the cost of slightly less efficient pruning on any single column compared to a pure sort.
To use sort or z-order compaction, you first need to verify that the table is sorted by one (sort) or multiple (z-order) columns:
-- Sort
ALTER TABLE icebergsons3.examples WRITE ORDERED BY insert_date;
-- Z-Order
ALTER TABLE icebergsons3.examples WRITE ORDERED BY insert_date,category_id;
S3 Tables manage snapshots automatically. By default, it keeps a minimum of 1 snapshot and expires snapshots older than 120 hours (5 days). The snapshot retention is customized by setting minSnapshotsToKeep and maxSnapshotAgeHours. After a snapshot reaches the expiration time you configured in your retention settings, S3 Tables marks objects that only that snapshot references as noncurrent and removes them based on the unreferenced file removal policy.
You can adjust these settings if your workload needs more snapshots for time-travel queries or longer retention:
Keep in mind that retaining more snapshots increases storage costs. If a materialized view references an expired snapshot, Amazon Redshift falls back to a full recompute on the next refresh. Therefore, snapshot retention can directly affect your materialized view refresh behavior. Balance snapshot retention with your materialized view refresh frequency to avoid unnecessary full recomputes.
Choose the right access pattern for your users. Use IAM federation with SESSION credentials for new applications and interactive users. Reserve the IAM role approach for BI tools and extract, transform, and load (ETL) pipelines that can’t integrate with IAM federation directly. Plan to migrate database users to federated access over time.
Match compaction strategy to query patterns. Use sort compaction when your queries filter on a single column (such as date ranges). Use z-order when queries filter on two or more columns together. Stick with the auto default if your query patterns vary or you’re unsure.
Size materialized views for your refresh window. Materialized views that join large external tables with local tables take longer to refresh. If your data changes frequently, keep the materialized view focused on the specific aggregations your dashboards need rather than materializing entire tables.
Coordinate snapshot retention with materialized view refresh. If a materialized view references an expired Iceberg snapshot, Amazon Redshift performs a full recompute instead of an incremental refresh. Set your snapshot retention (maxSnapshotAgeHours) longer than your materialized view refresh interval.
Monitor compaction with AWS CloudTrail. S3 Tables logs compaction operations as CloudTrail management events. Track these to verify that compaction runs on schedule and to identify tables that might benefit from a different strategy.
Balance performance gains against storage costs. Materialized views store pre-computed results in Amazon Redshift, adding to your managed storage. Compaction reduces file counts, but z-order and sort compaction can increase overall storage because of data duplication across sort boundaries. Review your Amazon Redshift managed storage usage and S3 Tables storage metrics periodically to make sure the performance benefits justify the additional storage utilization.
Troubleshooting
Issue
Resolution
“Permission denied” when creating the external schema
Verify the IAM role has lakeformation:GetDataAccess permission. Confirm you associated the role with your Amazon Redshift cluster or workgroup. Also check that you granted the role access to the resource link database and its tables in Lake Formation.
“Schema not found” or “Database not found” errors
Confirm the resource link name in Lake Formation matches the DATABASE value in your CREATE EXTERNAL SCHEMA statement. Verify the catalog ID format uses the pattern account_id:s3tablescatalog/bucket_name.
“Table not found” when querying through the external schema
Check that Lake Formation permissions include table-level access, not just database-level. Verify the table exists in the S3 Tables catalog by querying it through the auto-mounted catalog first.
Materialized view refresh falls back to full recompute
Check if the referenced Iceberg snapshot has expired. Increase maxSnapshotAgeHours in the snapshot management configuration. Verify that the base table hasn’t exceeded 4 million position deletes in a single data file. Compaction resolves this.
Queries on S3 Tables are slow after data loading
Compaction runs on an automated schedule and may not have processed recent writes yet. Check CloudTrail for the latest compaction event. Verify the compaction strategy matches your query patterns. Switch from binpack to sort if you filter on specific columns.
Cleaning up
To avoid ongoing costs, remove the resources you created in this walkthrough:
-- Drop materialized views
DROP MATERIALIZED VIEW IF EXISTS mv_daily_category_summary;
-- Drop external schemas
DROP SCHEMA IF EXISTS s3tables_schema;
Also remove:
The IAM role (S3TableAccessRole) and its attached policies, if you created one for database users.
The Lake Formation resource link and associated permissions.
In this post, we showed how to optimize S3 Tables queries from Amazon Redshift using three approaches: external schemas that simplify query syntax from three-part to two-part notation, making it easier for BI tools and end users to work with S3 Tables. We also covered materialized views for pre-computed analytical results that reduce repeated S3 scans, and S3 Tables compaction strategies tuned to your query patterns for more efficient file access.
For new applications, design your access layer with IAM federation and external schemas from the start. Use materialized views to accelerate repeated analytical queries that join S3 Tables with local Amazon Redshift data. Match your compaction strategy to how your team queries the data. Use sort compaction for date-range filters and z-order when queries filter on multiple columns at once. Furthermore, the same S3 tables you optimize here are also accessible from Amazon Athena, Amazon EMR, and third-party engines.
Automating data security and analytics for legal documents presents a unique challenge when your legal team stores documents with strong access controls, organized by client and matter, encrypted at rest, and governed by well-defined policies. But what happens when you want to run analytics across those repositories? The typical path is extracting content into separate data pipelines or third-party tools, which fragments your governance model and introduces new risks. Law firms and corporate legal departments operate under distinct obligations that make data governance non-negotiable. Attorney-client privilege, work product doctrine, and professional conduct rules impose strict duties around how client information is handled, accessed, and disclosed. Governance failure in this context isn’t just a compliance gap, it can result in privilege waiver, disqualification from representation, or disciplinary action.
Legal professionals use ethical walls, also called information barriers, as structural safeguards that prevent the flow of confidential information between teams within a firm that represent adverse or potentially conflicting interests. Professional conduct rules mandate these barriers, and failure to maintain them can result in firm disqualification, malpractice liability, or regulatory sanctions.
Privilege boundaries are equally critical. Attorney-client privilege and work product protection apply only when you properly control access to the underlying material. If you expose privileged documents or metadata about their contents to unauthorized individuals, you risk losing your privilege protection. When organizations fail to maintain reasonable controls over privileged material, courts might find that they have waived their privilege. You should therefore actively manage your access governance, not only as a security concern but as a legal preservation requirement.When you extract content into separate analytics systems or grant broader access than your matter structures support, you create pressure on both protections. You gain visibility but lose confidence in your controls.
In this post, we show you a reference architecture that automates sensitive data discovery across legal document repositories on Amazon Web Services (AWS), demonstrate how to capture structured findings as a compliance dataset, and guide you through building a governed analytics workspace that maintains your security boundaries. You walk away with a practical model for building security and analytics into the same lifecycle, without moving documents outside their system of record.
Analytics shouldn’t weaken governance
Most legal organizations have invested heavily in securing their document repositories. You store documents in structured storage, organized by client and matter. You access controls map to matter boundaries (the organizational and access structures that separate one client engagement from another). You establish retention and hold policies.The difficulty starts when teams want to analyze what’s inside those repositories. Running analytics typically means copying content into a separate system, standing up a new data pipeline, or granting broader access than existing matter structures support. Each of these steps introduces governance gaps. Manual reporting fills some of the void, but it doesn’t scale and can’t provide continuous visibility. What’s missing is a model where security controls and analytics reinforce each other, where the act of discovering sensitive data also produces the dataset that you use for reporting, and where governance applies once and carries through every downstream operation.
Automation addresses this by combining continuous sensitive data discovery with governed analytics, built on discovery metadata rather than document copies. This automated approach delivers four key advantages:
No document movement. Your files stay in their system of record. Analytics runs against structured discovery metadata, not document content, so governance boundaries remain intact.
Continuous discovery instead of manual scanning. Automated classification identifies regulated and sensitive information on an ongoing basis, replacing periodic manual reviews with on demand visibility.
Unified governance. You define matter-aligned access policies once, and they carry through from document storage to findings analytics and compliance reporting.
Built-in audit readiness. A durable record of discovery findings and remediation actions accumulates automatically over time, giving you structured evidence for client reviews and regulatory inquiries.
Reference Architecture
The following architecture shows how continuous discovery, governance, and compliance operations can work together without copying legal documents into analytics systems.
Architecture walkthrough
Store and protect documents in Amazon Simple Storage Service (Amazon S3)
Store your legal documents in Amazon S3, which serves as the system of record for document content. Align your buckets and prefixes to client and matter structures so that access controls map directly to matter boundaries. Where your retention or legal hold requirements demand it, apply S3 Object Lock to enforce immutability. You can encrypt your data using AWS Key Management Service (AWS KMS), which gives you centralized control over encryption keys and policies.
Discover and classify sensitive data with Amazon Macie
You will configure Amazon Macie to continuously analyze your document repositories. Macie identifies regulated information such as personally identifiable information (PII), financial data, and other sensitive content and produces structured findings that describe what Macie identified and where it exists. This provides ongoing visibility into data exposure without requiring document movement or manual scanning.
Catalog and govern findings with AWS Glue and AWS Lake Formation
You will use AWS Glue to catalog the findings dataset and maintain its schema so it stays query-ready. Apply AWS Lake Formation tag-based policies to govern access, aligning tags to client, matter, and confidentiality tier. This approach enforces ethical walls and least-privilege access consistently across analytics and reporting activities.
AI-powered chat agent using Amazon Quick Suite
You can create custom chat agents to tailor conversational interfaces for specific legal business needs. These agents can be configured with legal-specific knowledge bases, connected to relevant document repositories, and customized with instructions appropriate for legal workflows. You can use this chat agent to interact with your legal documents through natural language conversation for capabilities like:
E-Discovery:Search and analyze large volumes of legal documents to quickly find relevant information across your document repository.
Contract Analysis:Review contracts and automatically extract key terms, clauses, and obligations to streamline your contract review process.
The chat agent can help you navigate complex document sets through conversational queries, making legal research and document review more efficient and accessible.
Analyze and report with Amazon Quick Sight
You will use Amazon Quick as your compliance operations workspace. Quick provides a unified environment where your teams can query findings, generate dashboards, track remediation actions, and produce audit-ready reports. The agentic AI capabilities of Amazon Quick can autonomously build analyses, surface anomalies across matters, generate executive summaries for client reviews, and proactively recommend remediation priorities based on finding severity and trends. Combined with built-in data stories for automated narrative generation and pixel-perfect paginated reports for regulatory submissions, Quick reduces the time from discovery to action while keeping your teams within a governed interface aligned to matter-based permissions. Rather than switching between separate visualization, workflow, and reporting tools, your legal and compliance teams can review findings, manage response activities, and collaborate all within a single workspace that respects ethical walls and privilege boundaries.
Escalate high-severity findings
For high-severity findings that demand immediate attention, route alerts through AWS Security Hub or Amazon Simple Notification Service (Amazon SNS) to trigger escalation workflows. This connects visibility directly to action when your teams identify sensitive data risks.
Why this approach works for legal
Documents stay where they belong. Your files remain in Amazon S3, aligned to client and matter boundaries. No content moves into separate analytics pipelines.Ethical walls remain intact. Because analytics is built on discovery findings and not document copies, you can govern access to findings using the same matter-aligned controls that apply to documents. Compliance and security teams gain visibility without expanding document access.Discovery runs continuously, not periodically. Rather than scheduling quarterly or annual scans, you maintain a current view of sensitive data across your repositories.
Governance applies once and carries through. Lake Formation tag-based policies govern findings access at the catalog level. You define your matter and confidentiality mappings once, and they carry through to every dashboard, query, and report.Audit readiness is built in. Instead of assembling reports manually before a client review or regulatory inquiry, you maintain a historical record of discovery findings and remediation actions. You can demonstrate your posture over time with consistent, structured evidence.
Security and analytics reinforce each other. Your analytics capability is built on top of your security controls, not alongside them. Strengthening one strengthens the other.
Cost considerations
The primary cost drivers for this architecture include:
Amazon Macie: You pay based on the number of S3 buckets evaluated and the volume of data inspected for sensitive data discovery. Review Amazon Macie pricing for current rates.
Amazon S3: Storage costs for both your document repositories and the compliance intelligence bucket. Consider S3 lifecycle policies to tier older findings into lower-cost storage classes.
AWS Glue and AWS Lake Formation: Charges for crawlers and catalog storage. For most implementations, these costs are modest.
Amazon QuickSight: Per-user pricing based on the edition that you select (Standard or Enterprise). Enterprise edition supports row-level and column-level security, which aligns well with matter-based governance.
Amazon EventBridge, AWS Security Hub, and Amazon SNS: Charges based on event volume and notifications delivered. For findings-based workflows, these costs are generally low.
Use the AWS Pricing Calculator to estimate costs based on your repository size, user count, and discovery frequency.
Getting started
Start by identifying a representative set of document repositories in Amazon S3. We recommend that you start with two or three matters that span different practice areas and confidentiality tiers.
Turn on Amazon Macie for those repositories and configure automated sensitive data discovery.
Catalog the findings dataset with AWS Glue and apply Lake Formation tag-based access policies aligned to your matter structure.
Build your first Amazon Quick Sight dashboard to visualize findings by matter, sensitivity type, and severity.
Define escalation rules in AWS Security Hub or Amazon SNS for high-severity findings.
After you validate this workflow against your initial repositories, expand gradually. Add more repositories to Macie discovery. Refine your governance tags to reflect practice areas and confidentiality tiers. Extend your dashboards from basic posture visibility to trend analysis and remediation tracking.The goal isn’t to build a comprehensive analytics solution all at once. Start with a secure foundation where discovery findings, governance, and reporting operate together in a way that aligns with your legal workflows, and then expand from there.
Conclusion
You don’t have to choose between protecting client data and understanding it. By building analytics on top of governed discovery findings and using a unified compliance workspace, you gain visibility into your data posture without weakening confidentiality boundaries.This approach brings security, governance, and analytics together in a way that reflects how legal work is actually structured. It provides continuous visibility, supports audit readiness, and delivers insight without requiring documents to move outside their system of record.
Since 2013, Amazon Redshift has given the full power of a data warehouse in the cloud, at a fraction of the on-premises cost. Every architectural generation—from dense compute to Amazon RA3 instances, from provisioned to Amazon Redshift Serverless—has made each query cheaper, faster, and more efficient than the last.
For over a decade, as data volumes have grown and analytics requirements have evolved, organizations increasingly leverage both data warehouse tables for structured, frequently-accessed data and data lakes for cost-effective storage of diverse datasets. Add AI agents to the mix and they query your data warehouse at a scale that dwarfs typical human usage, leading to spiraling operational costs.
Amazon Redshift has doubled down on its core strengths to meet the demands of any workload — whether driven by humans or AI agents. For example, in March 2026, Amazon Redshift improved the performance of business intelligence (BI) dashboards and ETL workloads by speeding up new queries by up to 7 times. This significantly improves the response times of low-latency SQL queries, such as those used in near-real-time analytics applications, BI dashboards, ETL pipelines, and autonomous, goal-seeking AI agents.
Today, we’re announcing Amazon Redshift RG instances, a new instance family powered by AWS Graviton. RG instances deliver better performance, running data warehouse workloads up to 2.2x as fast as RA3 instances at 30% lower price per vCPU. Their integrated data lake query engine lets you run SQL analytics across your data warehouse and data lake from a single engine with performance up to 2.4x as fast as RA3 for Apache Iceberg and up to 1.5x as fast as RA3 for Apache Parquet. This blend of speed, cost efficiency, and an integrated data lake query engine makes Redshift RG instances well-suited to handle the high query volumes and low-latency requirements of today’s analytics and agentic AI workloads.
You can compare new RG instances and current RA3 instances:
Current RA3 Instance
Recommended RG instance
vCPU
Memory (GB)
Primary Use Case
ra3.xlplus
rg.xlarge
4
32
Small cluster departmental analytics
ra3.4xlarge
rg.4xlarge
12 → 16 (1.33:1)
96 GB → 128 GB (1.33:1)
Standard production workloads, medium data volumes
This approach reduces total analytics costs for customers running combined data warehouse and data lake workloads, while simplifying operations through a single system for querying both warehouse tables and Amazon Simple Storage Service (Amazon S3) data lakes. We recommend using the AWS Pricing Calculator with your specific workload patterns to estimate savings.
Getting started with Amazon Redshift RG instances You can launch new clusters or migrate existing clusters through the AWS Management Console, AWS Command Line Interface (AWS CLI), or AWS API. The integrated data lake query engine is enabled by default.
In the Amazon Redshift console, you can choose new RG instances when you create a cluster.
You can migrate previous-generation instances to RG instances with optimal paths based on your cluster configuration to estimate costs, validate compatibility, and automate execution.
Elastic Resize—in-place migration with 10-15 minutes downtime for compatible configurations
Snapshot and Restore—create a RG cluster from an RA3 snapshot. This is best for customers who want to make configuration changes during the migration
Your external tables, schemas, and query syntax—including existing Spectrum queries—remain unchanged. There is no need to recreate external tables or modify application code. To learn more, visit the Redshift Management Guide.
Amazon Redshift now executes data lake queries on cluster nodes—the same compute that processes data warehouse workloads. As a result, Amazon Redshift Spectrum is no longer required. Data lake queries stay within your VPC boundary, use existing IAM roles, and incur zero per-terabyte scanning charges. This removes the $5/TB Spectrum scanning fees that previously added to total Redshift costs.
Now available Amazon Redshift RG instances are now available in the following AWS Regions: US East (N. Virginia, Ohio), US West (N. California, Oregon), Asia Pacific (Hong Kong, Hyderabad, Jakarta, Malaysia, Melbourne, Mumbai, Osaka, Seoul, Singapore, Sydney, Taiwan, Tokyo), Canada (Central), Europe (Frankfurt, Ireland, Milan, London, Paris, Spain, Stockholm), Middle East (UAE), and South America (São Paulo). For Regional availability and a future roadmap, visit the AWS Capabilities by Region. For Redshift Provisioned, you can select On-Demand Instances with hourly billing and no commitments or choose Reserved Instances for cost savings. To learn more, visit the Amazon Redshift Pricing page.
As organizations scale their data processing and analytics workloads on Amazon EMR on EC2, observability across cluster health, job execution, and resource usage becomes increasingly important. Teams often manage log collection across distributed nodes, correlate Amazon EMR steps with underlying YARN applications, and configure monitoring agents to capture the right level of detail for their environment.
With Amazon EMR release 7.11.0 and updates to the Amazon EMR console, Amazon EMR on EC2 introduces observability capabilities that streamline these workflows further. In this post, we walk you through five key enhancements: Amazon CloudWatch Logs integration, step-level Amazon Simple Storage Service (Amazon S3) logging controls, expanded console UIs for YARN and Tez, Amazon EMR step to YARN application ID mapping, and enhanced custom metrics with updated documentation.
What’s new
The following sections cover key improvements across the Amazon EMR console, logging, metrics collection, and documentation to give you deeper, end-to-end visibility into your Amazon EMR clusters and workloads.
1. CloudWatch Logs integration
Starting with Amazon EMR release 7.11.0, you can stream cluster logs to Amazon CloudWatch Logs in near real time without requiring custom bootstrap actions or manual agent configuration. With Amazon CloudWatch logging enabled, Amazon EMR automatically captures and streams Amazon EMR step execution logs, Spark driver, and Spark executor logs as they’re generated. This makes them immediately available for monitoring, troubleshooting, and post-mortem analysis through the CloudWatch console or API.
You can enable CloudWatch logging through the Amazon EMR console during cluster creation or programmatically using the AWS Command Line Interfaced (AWS CLI) and SDK by including the Amazon CloudWatch Agent in your application configuration and specifying your logging preferences in the configuration section.
With minimal configuration, Amazon EMR captures step logs and Spark driver logs by default, streaming them to a log group named /aws/emr/{cluster_id}. For production workloads requiring stricter organizational and security controls, you can customize the log group name, define a log stream prefix for streamlined filtering, enable encryption with an AWS Key Management Service (AWS KMS) key, and explicitly select which log types to capture. The following example demonstrates a fully customized configuration:
This configuration directs the logs to a custom log group (/my-company/emr/production), prefixes log stream names with cluster-prod for consistent identification across clusters, encrypts log data at rest using the specified KMS key, and captures the full set of available log types: step stdout/stderr, Spark driver, and Spark executor output. Because logs are streamed to CloudWatch as they’re written, you have near real-time visibility into job execution without waiting for log aggregation to S3 or establishing direct connectivity to cluster nodes. Combined with CloudWatch Logs Insights, you can run structured querying across log streams, making it straightforward to trace failures, correlate errors across driver and executor logs, and build metric filters or alarms based on specific log patterns.
2. Step-level S3 logging improvements
S3 logging capabilities now provide granular control over how step logs are organized and secured. You can now specify a dedicated S3 log destination and AWS KMS encryption key at the individual Amazon EMR step level. This allows different steps within the same cluster to write logs to separate S3 paths with independent encryption configurations. This is particularly useful for multi-tenant clusters or workflows with varying data classification requirements.
Step-level logging is configured through the StepMonitoringConfiguration parameter, which accepts an S3MonitoringConfiguration object where you can define the target S3 path and an AWS KMS key for encryption at rest:
3. Enhanced console with direct access to monitoring UIs
Additional live application UIs are accessible directly from the Amazon EMR Console. These console-hosted interfaces remove the need to configure SSH (Secure Shell) tunnels, set up proxies, or establish any direct network connectivity to cluster nodes to reach application web UIs. The newly added interfaces include:
YARN ResourceManager UI – Monitor cluster-wide resource allocation, queue usage, and application lifecycle states across running and completed YARN applications. This interface also provides direct access to container-level logs for running YARN applications, enabling real-time debugging without requiring node-level access.
Tez UI – Inspect Hive query execution plans, DAG visualizations, vertex-level performance metrics, and task-level counters for queries executed through the Tez execution engine (for example, Hive and Pig workloads).
These join the existing Spark History Server and YARN timeline interfaces already available through the console. By surfacing these UIs, administrators can grant developers and analysts visibility into cluster workloads and application diagnostics without exposing direct network access to cluster infrastructure while maintaining tighter security boundaries and preserving full observability into job execution and resource consumption.
With these additions, Amazon EMR now offers three complementary approaches to accessing application web interfaces, each suited to different operational requirements. Live Application UIs provide console-hosted access to web interfaces on running clusters. They’re recommended for environments where direct network connectivity to cluster nodes must be restricted from end users. On-Cluster Web UIs offer full, unrestricted access to the complete set of native application web interfaces running on cluster nodes, suited for administrators and engineers who require deep, low-level visibility. Persistent Web UIs retain application-level data beyond cluster lifetime, so you can analyze and troubleshoot workloads on terminated clusters. Together, these options give you the flexibility to balance security boundaries, access scope, and data retention based on your team’s specific monitoring and debugging workflows.
4. EMR step to YARN application ID mapping
The Amazon EMR console now surfaces the YARN Application ID directly within the EMR step details panel. For each step executing a Spark, Hive, or other YARN-based workload, the console displays the submitted YARN Application ID associated with that step, establishing a direct link between the EMR step abstraction and the underlying YARN application. With this mapping, you can:
Directly correlate EMR steps to YARN applications – when a step fails or exhibits unexpected behavior, you can immediately identify the exact YARN application to investigate rather than manually cross-referencing timestamps or job names across interfaces.
Access live monitoring tools – with the YARN application ID readily available, you can navigate directly to the YARN ResourceManager Live UI or the Spark History Server to inspect resource consumption, task-level execution details, and application state for both running and completed jobs.
Retrieve logs for detailed troubleshooting – the application ID serves as the key lookup for retrieving container-level logs persisted to Amazon S3, significantly reducing the time to root-cause failures or diagnose performance regressions.
To use this feature, open the Steps tab on your Amazon EMR cluster detail page and select the step that you want to investigate. The YARN Application ID appears in the step details panel. From there, you can use the ID to navigate to the YARN ResourceManager Live UI at http://resourcemanager-host:8088/cluster/app/<application_id>, open the corresponding view in the Spark History Server, or locate the associated container logs in your configured S3 log destination.
5. Enhanced custom metrics and observability documentation
By default, Amazon EMR automatically sends cluster-level metrics to Amazon CloudWatch at five-minute intervals, covering YARN application states, node health, HDFS utilization, and I/O activity. With Amazon EMR Release 7.0 and later, enabling the Amazon CloudWatch Agent extends this baseline with additional detailed metrics collected at one-minute intervals across cluster nodes. Furthermore, Amazon EMR 7.1 introduced custom metric classifications that you can use to define precisely which component-level metrics to collect from Hadoop, YARN, and HBase subsystems, like DataNode I/O activity, NodeManager JVM heap utilization, container resource consumption, and HBase performance counters. Each classification supports configurable export intervals, giving you control over collection granularity based on your monitoring requirements.
After enabled, custom metrics are accessible directly from the Monitoring tab in the Amazon EMR console, where you can use a classification filter to switch between HDFS, YARN, HBase custom metric groupings that you’ve defined. Metric configurations can also be updated on running clusters through the console’s reconfiguration workflow, so you can adapt your monitoring strategy as workload requirements evolve without cluster downtime. For environments using Prometheus, metrics can also be forwarded to Amazon Managed Service for Prometheus and visualized through Grafana dashboards.
The following documentation and tutorials are available to help you get the most out of these capabilities:
Enhanced Custom Metrics Guide provides step-by-step instructions for configuring CloudWatch Agent to publish custom metrics.
EMR Observability Best Practices provides a comprehensive guide covering monitoring strategies, metric selection, and troubleshooting workflows.
Service Status Monitoring provides a tutorial on monitoring and publishing Amazon EMR application status.
These observability improvements are available now for Amazon EMR on EC2. To get started:
CloudWatch Logs integration and step-level log configuration: To use these capabilities, launch a new cluster with Amazon EMR release 7.11.0 or later.
For console enhancements: Navigate to your existing Amazon EMR clusters in the AWS Console to access Live Application UI links and YARN Application ID mappings in step details, with no additional configuration required.
For custom metrics: Review our Enhanced Custom Metrics documentation to configure the CloudWatch Agent for publishing Hadoop, YARN, and HBase component metrics using custom classification files.
Conclusion
With these enhancements, Amazon EMR on EC2 provides deeper visibility into cluster health, job execution, and resource usage, helping you reduce time to root cause and focus on delivering value from your data. Note that enabling CloudWatch Logs integration and custom metrics incurs additional CloudWatch charges based on log ingestion volume and metric publishing frequency.
If you have feedback or questions, reach out to your AWS account team or post on the AWS re:Post.
HBase operations teams spend hours manually correlating logs, metadata, and consistency reports to identify root causes. Traditional approaches require deep expertise and extensive investigation across scattered data sources, directly impacting MTTR and operational efficiency. As HBase deployments scale and expertise becomes increasingly scarce, organizations face mounting pressure to maintain service reliability while managing growing operational complexity. The manual nature of troubleshooting creates bottlenecks that delay incident resolution, increase operational costs, and risk service degradation during critical business periods.
In this post, we show you how to build an AI-powered troubleshooting solution using Amazon OpenSearch Service vector search and intelligent analysis. This solution reduces HBase inconsistency resolution from hours to minutes and root cause identification from days to hours through natural language queries over operational data. This democratizes HBase troubleshooting capabilities across teams and reducing dependency on specialized expertise.
Solution overview
The solution addresses HBase troubleshooting challenges through data processing, vector search, and AI-powered analysis. It processes operational data from Amazon EMR clusters, generates semantic vector embeddings, and enables natural language queries for intelligent troubleshooting. Key components include:
Amazon EMR HBase: Runs HBase workloads with Amazon S3 as the HBase rootdir for durable, scalable storage
Data Processing: Extracts and processes HBase logs, HBCK reports, and metadata with vector embeddings
Amazon OpenSearch Service: Provides vector search capabilities with k-NN algorithms for semantic analysis
AI Analysis Interface: Enables natural language queries with context-aware recommendations
Custom Knowledge Base: Supports organization-specific runbooks and troubleshooting procedures by ingesting Git repositories via Kiro CLI‘s /knowledge add command, enabling the AI assistant to reference custom operational guides alongside HBase source code and operational tools
The preceding diagram illustrates how the HBase log analysis system troubleshoots inconsistencies through automated workflows across AWS services.
When an operations team needs to investigate HBase issues, the engineer connects over SSH to the Amazon EMR primary node and runs the error collection script, which gathers logs from HBase master and RegionServer nodes and uploads them to Amazon S3. Next, the engineer connects to the Analytics Amazon Elastic Compute Cloud (Amazon EC2) instance and executes the automated processing script, which downloads logs from Amazon S3, generates semantic vector embeddings, and injects them into Amazon OpenSearch Service for k-NN-based semantic search. The engineer then queries the Kiro CLI AI Assistant using natural language to investigate. Kiro searches Amazon OpenSearch Service for relevant log entries and uses Amazon Bedrock to analyze patterns, correlate errors across components, and provide actionable recommendations. This reduces troubleshooting time from hours to minutes. The system operates within an Amazon Virtual Private Cloud (Amazon VPC) with private subnets for Amazon EMR and Analytics Amazon EC2, AWS Identity and Access Management (AWS IAM) roles for access control, Parameter Store for configuration, and Amazon CloudWatch for monitoring.
Prerequisites
For this walkthrough, you need the following prerequisites:
AWS account setup
An AWS account with administrative access for initial deployment
AWS Command Line Interface (AWS CLI) configured with administrative credentials
Required AWS IAM permissions
For infrastructure deployment
Your deployment user or role needs the following permissions:
The user or role must have the ability to create AWS CloudFormation stacks.
Infrastructure deployment:
For infrastructure deployment, you need AWS CloudFormation stack management permissions.
You also require sufficient access to create and manage the following resources:
Amazon OpenSearch Service domains
Amazon EC2 instances, Amazon VPCs, security groups, and networking components
AWS IAM roles and policies
AWS Systems Manager Parameter Store entries
Amazon CloudWatch Logs groups
Amazon S3 bucket for access logs and session logs
Runtime service roles
The AWS CloudFormation stack automatically creates two specialized AWS IAM roles designed with least-privilege access principles.
The first role is the Amazon OpenSearch Service Role, which manages Amazon VPC networking and Amazon CloudWatch logging for the Amazon OpenSearch Service domain.
The second role is the Application Role, which provides minimal Amazon OpenSearch Service and Amazon S3 access specifically for log processing applications and secure log ingestion operations.
Network requirements
Amazon VPC with private subnets for secure Amazon OpenSearch Service deployment
NAT Gateway for outbound internet access from private subnets
Security groups configured for HTTPS-only communication
Running Kiro CLI on Amazon EC2
Kiro platform requirements:
Kiro subscription
Active Kiro License: Valid subscription to Kiro platform
User Account: Registered Kiro user account with appropriate permissions
API Access: Kiro API keys or authentication tokens for CLI access
Permission Sets: Configured permission sets for Kiro users with appropriate AWS access
User Assignment: Users assigned to relevant AWS accounts and permission sets
SAML/OIDC Configuration: Identity provider integration if using external identity systems
Additional prerequisites
Python 3.7+ and Node.js installed locally
Python 3.11+ for AWS Lambda runtime environment (required for OpenSearch MCP server compatibility)
Sufficient service quotas for Amazon OpenSearch Service instances and Amazon EC2 resources
Recommended access to the analysis instance via AWS Systems Manager Session Manager (recommended). Amazon EMR clusters running HBase workloads
EMR_EC2_Default_Role of Amazon EMR EC2 instance profile can execute describe-stacks on AWS CloudFormation stacks in us-east-1
Basic familiarity with HBase operations
The deployment follows AWS security best practices with resource-specific permissions, regional restrictions, and encrypted data storage. All AWS IAM policies implement least-privilege access patterns to help secure operation of the log analysis pipeline.
Walkthrough
This walkthrough demonstrates deploying and configuring the AI-powered HBase troubleshooting solution in five key steps:
Deploy AWS infrastructure using AWS CloudFormation
Kiro CLI and configuration with AWS IAM Identity Center authentication. The script will automatically add Apache HBase open source repo and Apache HBase open source operational tools to knowledge bases
HBase source repositories for your Amazon EMR version
Python dependencies and MCP server configuration
Add your own knowledge base to Kiro CLI
To enhance Kiro CLI’s analysis capabilities with Apache HBase open-source repositories, your organization’s HBase runbooks and troubleshooting guides, you can add your own knowledge base repositories. Here are the commands. Please periodically validate and maintain your runbook contents so that they remain accurate and up-to-date, reflecting any changes in your HBase environment, configurations, or operational procedures.:
Set up data collection from your Amazon EMR clusters to gather HBase logs, metadata, and consistency reports using the recommended direct collection method. To configure Amazon EMR log analysis collection
On your Amazon EMR cluster primary node, run the following commands to download the collection scripts:
# On EMR primary node
sudo su - hadoop
# Fork and clone the source code repository on GitHub: sample-emr-hbase-inconsistencies-detection-recovery-mcp-kiro
git clone https://github.com/YOUR_USERNAME/sample-emr-hbase-inconsistencies-detection-recovery-mcp-kiro.git hbase-analysis
cd hbase-analysis
Run the interactive collection wizard:
# Run collection wizard
python3 scripts/utilities/emr_log_collection/emr_cluster_wizard_v2.py
Input the parameters like the EMR cluster’s jobflow ID, the log analysis Amazon S3 bucket name, and the lookback hours. The default value of the lookback hours is 4 hours.
The collection wizard performs these actions:
Collects HBase logs from local filesystem. Please reference to prerequisites for the access permission.
Runs hdfs dfs -ls -R /hbase or aws s3 ls <hbase-root-dir> –recursive
Runs hbase shell <<< 'scan "hbase:meta"'
Creates properly named files matching analysis system requirements
Uploads to Amazon S3 with correct naming conventions
Here’s the data collection summary:
You can check the uploaded contents through AWS CLI.
aws s3 ls s3://<log-path> --recursive
Here’s a screenshot of the outputs.
On the Analysis Amazon EC2 instance, download collected files to the Analysis Amazon EC2 instance.
# On analytics EC2 instance
sudo su - ec2-user
# Download logs from S3
mkdir -p /tmp/hbase-log-analysis
cd /tmp/hbase-log-analysis
aws s3 sync s3://<S3-BUCKET-NAME>/emr-logs/<EMR-JOBFLOW-ID>/ .
You can get your jobflow ID from Amazon EMR console:
The generated files (hbase-hbase-master-ip-xxx-xxx-xxx-xxx.ec2.internal.log.gz, hbase-hbase-regionserver-ip-xxx-xxx-xxx-xxx.ec2.internal.log.gz, hbck_report.txt, hbase_rootdir_paths.txt, hbase_meta.txt, hbase_processes.txt, log_copy_summary.txt) should be aligned with the automated processing script requirements as following.
Step 4: Process and index data
Process the collected HBase data and create vector embeddings for intelligent search capabilities.To process and index the data, please navigate to the project directory on the Analysis EC2 instance, and run automated-log-processing.sh:
sudo su – ec2-user
cd ~/hbase-analysis
chmod +x ./scripts/processing/automated-log-processing.sh
./scripts/processing/automated-log-processing.sh \
--job-flow-id j-YOUR-JOB-FLOW-ID \
--stack-name dev-hbase-log-analysis
The processing scripts extract and parse HBase logs and generate dimensional vector embeddings from HBase log messages using sentence transformer models to enable semantic search beyond keyword matching. The system uses the all-MiniLM-L6-v2 model by default (producing 384-dimensional embeddings), but supports configurable models with different embedding dimensions, automatically adapting the OpenSearch vector index to match the chosen model’s output. The system processes comprehensive HBase operational data including region operations, compaction activities, Write-Ahead Log events, memstore operations, and cluster management information from HMaster and RegionServer logs. Vector embeddings capture error messages, exception stack traces, performance warnings, and multi-line log entries through intelligent text preprocessing. This semantic representation enables advanced troubleshooting where users can query conceptually for “region server performance issues” or “memory pressure” and receive contextually relevant results across different log files and time periods. The vector search capabilities support error correlation by grouping similar exceptions, performance analysis by identifying related bottlenecks, and operational pattern recognition. Each log entry is stored in Amazon OpenSearch Service with original metadata (timestamp, log level, source file, job flow ID) alongside the embedding vector, enabling both structured queries and AI-powered semantic analysis. This approach transforms raw HBase logs into a searchable knowledge base supporting anomaly detection, trend analysis, and predictive insights for proactive cluster management and troubleshooting.
All scripts use AWS IAM authentication automatically. Here’s a screenshot of the data processing outputs.
Step 5: Enable AI-powered analysis
Configure the AI analysis interface to enable natural language queries against your HBase operational data.
To set up AI-powered analysis
Launch Kiro CLI (already configured by automated setup):
kiro-cliCheck mcp and knowledge bases. /mcp list
/knowledge show
If you cannot see these 2 knowledge bases, you can manually add them through the following commands:
# Note: Large repositories (~500MB) may take a while to index. Check progress with: /knowledge show
/knowledge add --name "HBase operational tools" --path /opt/hbase-repositories/hbase-operator-tools"
/knowledge add --name "Apache HBase source code" --path /opt/hbase-repositories/hbase"
Use natural language queries to analyze your HBase data. The AI analysis uses both the OpenSearch MCP Server for querying indexed data and the Filesystem knowledge bases for accessing HBase source code. You can add your custom runbooks for Kiro’s reference as well.
For HBase inconsistency analysis:
# HBase Inconsistency Detection and Remediation Guidelines
## Search Strategy
- Use fuzzy search for case variations/typos, term query for exact region IDs, match_phrase for paths, query_string for logs
- Always use .keyword subfields for exact text matching
- Cross-reference filesystem (wildcard: {"wildcard": {"path": "*<region_id>*"}}) with hbase:meta (match: {"match": {"row_key": "<region_id>"}})
- The total region count in hbase meta must match the total matched document count of wildcard path like "*/.regioninfo" in hbase rootdir path.
- All terms of region_name.keyword for a region encoded name must match a wildcard path like "*/.regioninfo"
- All terms of table_name.keyword for a table must match a wildcard path like "*/.tabledesc*"
- 1595e783b53d99cd5eef43b6debb2682 is the master store region that will locate in <hbase-root-dir>/MasterData/data/master/store/1595e783b53d99cd5eef43b6debb2682/
- May cross check with the raw logs in /tmp/hbase-log-analysis/
## Issue Types
Orphan regions, missing .regioninfo, missing/extra regions in hbase:meta, rowkey holes, stuck RIT, master initialization failures
## Analysis Steps
### 1. Cross-Reference Meta vs Filesystem
- Filesystem regions NOT in hbase:meta → ORPHAN REGION
- Meta regions NOT in filesystem → MISSING REGION
### 2. Validate Region Chain Continuity
- Sort regions by STARTKEY, verify region[i].ENDKEY == region[i+1].STARTKEY
- First STARTKEY must be '', last ENDKEY must be ''
- Gaps → ROWKEY HOLE
### 3. Check Region States
- state != 'OPEN' → Check RIT
- Missing server assignment → UNASSIGNED
- Multiple servers → SPLIT BRAIN
- "deployed_servers" field must have only one region server address like "ip-xxx-xxx-xxx-xxx.ec2.internal,16020,1770781485397" . The value should not be null or have multiple values.
### 4. Validate .regioninfo Files
- Missing .regioninfo in region directory → CORRUPT REGION
### 5. Cross-Check HBCK Report
- Compare orphan counts, RIT regions, filesystem vs meta region counts
### 6. Analyze Logs
- Search: "updating hbase:meta row=<region>", "STUCK", "RIT", "Failed" + "<region>", "Split"/"Merge" + "<region>"
## Remediation
- Reference knowledge bases: "Apache HBase source code", "HBase operational tools"
- Use hbck2: /usr/lib/hbase-operator-tools/hbase-hbck2.jar
- Prefix commands with sudo -u hbase
- Use aws s3 for S3-based rootdir
- Wait 300s after creating holes before hbck fixMeta (catalog janitor cycle)
- Use unassign instead of deprecated close_region
- If the region does not have .regioninfo in <hbase-root-dir>/data/<namespace>/<table-name>/<region-encoded-name>/ but hbase:meta has that region's information and that region has been deployed on a healthy region server, you can use hbase shell to unassign and assign the region to re-generate .regioninfo
- Always add "sudo -u hbase hbase" before "hbase shell" and "hbase hbck" commands
## Job flow
Target: <your-job-flow-id>
Inconsistency to detect: All kinds of inconsistencies
You can trust or input “y” or “t” to grant Kiro to search through mcp and knowledge bases.
You may get some outputs like this: Kiro checked for any HBase issue.
Kiro summarized the examination results.
Kiro provided mitigation commands after Kiro summarized the issue.
Cleaning up
To avoid incurring future charges, delete the resources created during this walkthrough.
In this post, we showed you how to build an AI-powered HBase troubleshooting solution that transforms manual log analysis into an automated workflow. By combining Amazon OpenSearch Service vector search with Amazon Bedrock-powered analysis through the Kiro CLI, operations teams can resolve complex HBase inconsistencies faster and gain deeper operational insights. The solution demonstrates how AI augments human expertise to improve operational efficiency, reducing HBase inconsistency resolution from hours to minutes and root cause identification from days to hours. Ready to transform your HBase operations? Get started with the GitHub repository and explore the Amazon OpenSearch Service documentation for additional guidance on vector search capabilities.
Acknowledgments
The author would like to thank Xi Yang, Anirudh Chawla, and Sasidhar Puthambakkam for their contributions to developing the technical solution. Xi Yang is a Senior Hadoop System Engineer and Amazon EMR subject matter expert at AWS. Anirudh Chawla is an AWS Analytics Specialist Solution Architect who helps organizations empower businesses to harness their data effectively through AWS’s analytics platform. Sasidhar Puthambakkam is a Senior Hadoop Systems Engineer and Amazon EMR Subject Matter Expert who provides architectural guidance for complex BigData workloads.
Apache Iceberg has emerged as the open table format for data lakes. It handles petabyte-scale datasets, lets teams evolve schemas and partitions in place, and supports time travel and incremental processing for data lake management at scale. Amazon S3 Tables provide a fully managed Apache Iceberg table experience in Amazon S3, optimized for analytics workloads, and integrate with the AWS Glue Data Catalog so AWS analytics services such as Amazon Redshift, Amazon EMR, Amazon Athena, Amazon SageMaker, and AWS Glue query your data. Together, they form the foundation of a modern data lake architecture on AWS.
S3 Tables integrate with the AWS Glue Data Catalog using AWS Identity and Access Management (IAM) – based authorization. If you manage analytics workloads across these services, you can now define permissions across storage, catalog, and compute in a single IAM policy. This gives teams already using IAM a straightforward path to govern access to S3 Tables resources without changing their existing permission model. For fine-grained access controls, you can opt in to AWS Lake Formation at any time through the AWS Management Console, AWS Command Line Interface (AWS CLI), API, or AWS CloudFormation.
Iceberg materialized views created in the Glue Data Catalog extend this foundation by letting you store pre-computed query results as Iceberg data on Amazon S3. When a query repeats aggregations or joins across large datasets, the engine reads directly from the materialized view’s S3 location rather than reprocessing the base tables. A materialized view can reside in S3 Tables or in an S3 general purpose bucket, independent of where its base tables live, which lets you place pre-computed results wherever fits your access patterns and cost model best.
In this post, we walk through how to set up and manage S3 Tables in the AWS Glue Data Catalog, create and query Iceberg materialized views, and configure access controls that work across your analytics stack with IAM-based authorization.
Solution overview
The above architecture illustrates how S3 Tables integrate with AWS Glue Data Catalog using IAM-based authorization, so you can define the necessary permissions across storage, catalog, and query engines in a single IAM policy. This permission model accelerates onboarding for new teams and workloads.
Key architecture components include:
Storage Layer: Data stored as Iceberg tables in Amazon S3 Tables
Catalog Layer: AWS Glue Data Catalog serves as the single metadata repository.
Compute Layer – Amazon Athena, AWS Glue, Amazon Redshift, and Amazon EMR connect to a single data Catalog to access Iceberg tables.
Security: AWS IAM authorizes access to resources in storage, catalog, and compute layers.
Prerequisites:
To follow along with this post, you must have an AWS account and an IAM role or user with appropriate permissions and familiarity to the following services:
In this walkthrough, you will integrate S3 Tables with the AWS Glue Data Catalog, create Iceberg materialized views, and query data using multiple analytics engines. You will also learn to use materialized views when you have complex aggregations queried frequently but underlying data changes. You can follow these steps to implement the solution. It will take about 45–60 minutes to complete this walkthrough.
Setup S3 Tables and integrate with Glue Data Catalog
Navigate to Amazon S3 console:
On the left menu, select Table buckets.
Choose the Create table bucket button.
In the next screen, we will fill the name of the bucket as salesbucket. Please ensure the Enable Integration configuration is checked. This step integrates S3 Tables with AWS Glue Data Catalog.
Keep the other options as default and choose Create table bucket.
After it is created, you will be redirected back to the list of table buckets. Choose the table bucket salesbucket.
Select the Create table with Athena button.
Create a namespace in S3 Tables which is equivalent to a database in AWS Glue Data Catalog. Enter namespace (database) name as “sales” and click Create namespace.
Choose Create table with Athena, and a new tab will be open with the Amazon Athena console.
When the Amazon Athena console opens, you will see an example of a query to create a table and examples to insert rows in that table. You could use this query block by uncommenting the code and executing each statement individually by highlighting it. At the end, you will have data in the table.
Query S3 Tables and create materialized view using Amazon EMR:
To run the instruction on Amazon EMR, complete the following steps to configure the cluster:
Create an IAM role for the Amazon EMR instance profile following the Amazon EMR Management Guide. Add the following as policies and trust relationship for working on materialized views.
Replace ACCOUNT_ID with your AWS account ID, Instance_profile_role to the Amazon EMR instance profile role, and REGION with your AWS Region.
Launch an Amazon EMR cluster 7.12.0 or higher with instance profile role created in the previous step and with Iceberg enabled. For more information, refer to Use an Iceberg cluster with Spark.
Connect to the primary node of your Amazon EMR cluster by using SSH, and run the following command to start a Spark application with the required configurations:
CREATE MATERIALIZED VIEW sales_mv as
SELECT
product_category,
COUNT(*) as units_sold,
SUM(sales_amount) as total_revenue,
AVG(sales_amount) as average_price
FROM
glue_catalog.sales.daily_sales
GROUP BY
product_category;
A newly created materialized view is populated with the initial query results but does not update automatically as base table data changes. To keep it current, specify a REFRESH EVERY clause when creating the view. This accepts a time interval and unit, so you can define how often the materialized view is recomputed from the base tables.
Add refresh interval.
CREATE MATERIALIZED VIEW sales_mv
SCHEDULE REFRESH EVERY 2 HOURS as
SELECT
product_category,
COUNT(*) as units_sold,
SUM(sales_amount) as total_revenue,
AVG(sales_amount) as average_price
FROM
glue_catalog.sales.daily_sales
GROUP BY
product_category;
Alternatively, you can refresh them manually.
For manual full refresh, you can use the following command:
REFRESH MATERIALIZED VIEW sales_mv FULL;
For manual incremental refresh, you can use the following command:
After the Iceberg materialized views are created, you can access them using IAM principals that have required IAM permissions to Glue Data Catalog resource and its underlying storage.
Iceberg materialized views are flexible in how they combine base tables and access control modes. Base tables can reside in S3 general-purpose buckets (with IAM or Lake Formation access control), in S3 Tables (through the s3tablescatalog catalog), or a combination of these—all within a single materialized view definition. The materialized view itself can use either IAM or AWS Lake Formation access control, independently of its base tables.
Additionally, you can query the same materialized view from Athena SQL. The following image shows the same query run on Athena and the resulting output.
Query using Amazon Redshift:
To query the S3 Tables in AWS Glue Data Catalog using Amazon Redshift, you must create a database in the default catalog in Glue Data Catalog that points to the S3 Tables catalog.
On the AWS Glue console, choose Databases, and then choose Add Database.
Choose the Glue Database resource link option, add a name for the database, choose salesbucket on the target catalog and sales as the target database. Then select Create database.
After creating the database, we will see the “salesdb” resource link under Databases on AWS Glue Data Catalog.
Create IAM role with the following policy for the Amazon Redshift schema creation. Replace the AWS Region and account ID for your account.
To access the AWS Glue Catalog and the resource link, you can now log in to Amazon Redshift as a local user. We use the admin user and Amazon Redshift Query Editor v2.
To create the external schema, you must run the following command: Replace ACCOUNT_ID with your AWS Account ID, IAM_ROLE to IAM role created for schema access, and REGION with your AWS Region.
CREATE EXTERNAL SCHEMA salesdb
FROM DATA CATALOG DATABASE 'salesdb'
IAM_ROLE 'arn:aws:iam::<ACCOUNT_ID>:role/<IAM_ROLE>'
REGION '<REGION>'
CATALOG_ID '<ACCOUNT_ID>';
After you have created the external schema, it will show up on the left side, under the dev database. The table that we created, daily_sales, is available and we can query directly from Amazon Redshift using a local user.
Cleanup:
After completing the walkthrough, follow these steps to remove the resources and avoid ongoing charges. These cleanup steps will permanently delete the data, including the daily_sales table and sales_mv materialized view. Make sure that you have backed up the data that you need to retain before proceeding.
To avoid incurring future charges, clean up the resources that you created during this walkthrough:
Remove the Glue Data Catalog resources
Delete the table bucket
Terminate and Delete the Amazon Redshift cluster
Terminate and Delete the Amazon EMR cluster
Delete the IAM roles/policies created
Conclusion
Amazon S3 Tables now integrate with AWS Glue Data Catalog through IAM-based authorization via a single IAM policy. By consolidating permissions for storage, catalog, and query engines into one IAM policy, you can streamline authorization with AWS analytics services like Amazon Athena, Amazon EMR, and AWS Glue. You can use this streamlined IAM authorization model to build your data lake faster while maintaining enterprise-grade security. For organizations with additionally granular data access requirements, AWS Lake Formation remains available to layer fine-grained access controls on top of this foundation. This is configurable through the AWS Management Console, CLI, API, or CloudFormation. This integration allows AWS analytics users to use IAM and scale their analytics capabilities with reduced operational complexity.
This is a guest post by Supreet Padhi, Technology Architect, Strategic Technologies, and Rochelle Grubbs, Senior Director, Solution Architect at Precisely in partnership with AWS.
Business leaders face a critical challenge to enable real-time analytics. Their most valuable data sits in mainframe systems that reliably process billions of transactions daily, but extracting value for modern analytics and AI remains complex and costly. Traditional mainframe-to-cloud integration approaches require multi-step replication with intermediary systems, creating operational overhead, latency, and data integrity risks. This complexity delays insights, increases infrastructure costs, limits agility, and blocks organizations from using AI and machine learning on their mainframe data.
Organizations that can connect their mainframe environments with modern cloud platforms can gain advantages through improved agility, reduced operational costs, and enhanced analytics capabilities.For example, moving appropriate analytics and reporting workloads to the cloud can significantly reduce mainframe operational costs while maintaining performance and reliability. Real-time data access makes insights available within seconds rather than waiting for batch processing cycles, enabling faster responses to market changes and customer needs. Eliminating bulk data extracts and intermediary systems also reduces infrastructure and maintenance expenses. This frees IT resources to focus on higher-value initiatives.
However, implementing mainframe-to-cloud integrations presents unique technical challenges that require specialized solutions. These include converting mainframe character encoding (EBCDIC) to standard ASCII format and handling mainframe-specific data types such as packed decimal (COMP) fields. You also need to manage the complexity of VSAM (Virtual Storage Access Method) files that can store multiple record types in a single file, and maintain real-time synchronization without impacting mainframe performance.
Change Data Capture (CDC) technology addresses these challenges through incremental data movement that eliminates disruptive bulk extracts by streaming only changed data to cloud targets, minimizing system impact and ensuring data currency. Real-time synchronization keeps cloud applications in sync with mainframe systems, enabling immediate insights and responsive operations.
Precisely Connect: Real-time data replication to Amazon S3
With Precisely Connect, you can replicate data directly from mainframes to Amazon S3 in real time, eliminating the need for intermediaries and simplifying modernization.Data flows directly from mainframe sources, including Db2 z/OS, IMS, and VSAM, to Amazon S3, eliminating intermediary steps and reducing both latency and operational complexity. You can move mainframe data directly to Amazon S3 data lakes and analytics platforms without managing complex, multi-step replication processes.
The simplicity of this approach reduces maintenance overhead and integration complexity by removing the need for staging servers, middleware, or batch processing systems. After data lands in Amazon S3, it becomes immediately available for downstream AWS workloads. You can use Amazon Athena for SQL queries, AWS Glue for ETL and data cataloging, Amazon EMR for big data processing, Amazon SageMaker AI for machine learning, and Amazon Quick Sight for business intelligence dashboards.
By introducing direct S3 replication and streamlining deployment through the pre-configured AWS Marketplace AMI, you can deploy in minutes rather than weeks. This creates new possibilities for data distribution, transformation, and consumption. This architecture offers several key benefits:
Simplified deployment – Accelerate implementation using the preconfigured AWS Marketplace AMI
Direct replication – Eliminate intermediary systems by streaming data directly to Amazon S3, reducing latency and operational overhead
Real-time synchronization – Capture changes as they occur on the mainframe, ensuring downstream applications operate on current data
Flexible analytics options – Use S3 Tables for Iceberg-compatible tabular data storage
Comprehensive AWS integration – Gain immediate access to Amazon EMR, Amazon Athena, AWS Glue, Amazon SageMaker AI, and Amazon Quick Sight
Natural language data access – Through the MCP Server for Amazon S3 Tables, AI assistants can interact with structured data using conversational interfaces without needing to write SQL queries.
Prerequisites
To complete the solution, you need the following prerequisites:
Precisely components
AWS Mainframe Modernization – Data Replication for IBM z/OS – Deploy this Precisely Connect AMI from AWS Marketplace. This pre-configured image contains the Apply Engine and Controller Daemon components required for replicating mainframe data changes to Amazon S3.
Precisely Connect CDC Capture/Publisher – Deploy the Precisely Connect CDC Capture/Publisher on your mainframe environment. This component captures changes from Db2z logs and streams them to the Apply Engine over TCP/IP.
Have network connectivity established between your mainframe environment and AWS using your organization’s approved connectivity method (such as AWS Direct Connect or VPN).
Verify that firewall rules allow TCP/IP communication between the mainframe Capture/Publisher and the Apply Engine.
AWS analytics components (optional extension)
After mainframe data lands in Amazon S3, your organization can extend its analytics capabilities using AWS services. One approach is to use Amazon EMR streaming jobs to process and write data to Amazon S3 Tables. After the data is stored in S3 Tables, the data can be queried directly using Amazon Athena for ad-hoc SQL analysis. This extension is optional and represents one of several ways to consume and analyze mainframe data after it reaches Amazon S3.
The following diagram illustrates the solution architecture.
Capture/Publisher – Connect CDC Capture/Publisher captures Db2 changes from Db2 logs using IFI 306 Read and communicates captured data changes to a target engine through TCP/IP.
Controller Daemon – The Controller Daemon authenticates all connection requests, managing secure communication between the source and target environments.
Apply Engine – The Apply Engine receives the changes from the Publisher agent and applies the changed data to the target Amazon S3.
Amazon S3 – Serves as the scalable data lake foundation where replicated mainframe data lands.
Amazon EMR streaming job – As data arrives, an instance of the Amazon EMR streaming job writes the data to target tables in Amazon S3 Tables.
Amazon Athena – Queries data stored in Amazon S3 Tables using standard SQL.
This architecture provides a clean separation between the data capture process and the data consumption process, allowing each to scale independently. When CDC data arrives in Amazon S3, you can use Amazon S3 Tables to store Db2 z/OS, VSAM, and IMS data in an open table format (Apache Iceberg) that is ready for analytics, providing a flexible path to mainframe modernization.
Quantifiable business value
Organizations implementing this solution typically see significant reductions in mainframe operational costs by offloading analytics and reporting workloads to the cloud. The elimination of intermediary infrastructure reduces both capital and operational expenses. The reduced maintenance burden frees IT resources to focus on strategic initiatives rather than managing complex replication systems. Speed and agility improvements are equally significant. Near real-time data availability, measured in seconds to minutes rather than hours to days, enables organizations to respond rapidly to market changes and operational events. The rapid deployment of new analytics use cases without requiring mainframe changes accelerates innovation. Organizations gain access to the full breadth of AWS services that can be used immediately after data lands in Amazon S3.
From an analytics and AI perspective, the solution creates a unified data platform that brings together mainframe, cloud-native, and third-party data sources. This unified view enables advanced machine learning on historical and current data, delivering predictive insights that drive proactive decision-making across the organization.
Customer story
A leading global payments provider put this into practice. The payments provider was struggling to generate timely analytics and insights from Point of Sale (POS) transaction data. As one of the world’s largest payment providers, they process hundreds of thousands of transactions per second. Users expect to swipe their card and have their transaction approved in seconds. New architecture was needed to keep up with customer demands and volume. By streaming mission-critical mainframe data directly to AWS in real time using Precisely Connect and landing it in Amazon S3 Tables, the company used storage built on the Apache Iceberg open standard. This approach enables high-performance analytics directly on mainframe data alongside cloud-native sources.
Conclusion
In this post, we demonstrated how Precisely Connect enables real-time, direct data replication from mainframes to Amazon S3, eliminating intermediaries and simplifying mainframe modernization.
Your organization can further extend this foundation with Amazon S3 Tables, purpose-built storage for Apache Iceberg tables in S3, enabling analytical applications to query the most current mainframe data using tools such as Amazon Athena, Amazon EMR, and Amazon Redshift.
Get started by deploying AWS Mainframe Modernization – Data Replication for IBM z/OS from AWS Marketplace and use Amazon S3 as a target for your mainframe use cases. Learn more about Precisely’s mainframe data integration capabilities at precisely.com. Contact AWS and Precisely experts to discuss your specific modernization challenges and design a proof-of-concept that demonstrates business value quickly.
Building production-ready Apache Flink applications requires learning a complex ecosystem. The learning curve is steep for newcomers, and even experienced Flink developers encounter complexity when scaling applications or troubleshooting production issues. With the new KiroPower and Agent Skill for Amazon Managed Service for Apache Flink, you can get AI-assisted guidance for building, improving, and migrating streaming applications directly in your development environment, with recommendations that are grounded in best practices.
The Managed Service for Apache Flink Kiro Power and Agent Skill helps you navigate challenges across the Flink application lifecycle. For new development, the tool provides contextual guidance on application architecture, state management patterns, and connector selection. For existing application improvements, it analyzes your existing code to identify performance bottlenecks, reliability risks, and opportunities for improvement. If you’re upgrading from Apache Flink 1.x to 2.x, it detects compatibility issues and provides targeted refactoring steps to modernize your applications.
In this post, we walk through installing the Power and Skill, using Amazon Kinesis Data Streams to build a Kinesis Data Stream-to-Kinesis Data Stream streaming pipeline, and migrating an existing application to Flink 2.2. You can follow along with this use case to see how the Managed Service for Apache Flink Kiro Power can help you build a resilient, performant application grounded in best practices.
Flink 1.x to 2.2 migration guidance with state compatibility assessment
Connector-specific guidelines
The content is maintained in a single repository with use case specific entry points that are dynamically loaded depending on your needs.
Prerequisites
To use the tool, you need:
A development machine running macOS, Linux, or Windows with Java 11 or later (Java 17 for Flink 2.2) and Apache Maven installed
One of the following AI development tools:
Kiro IDE
Cursor
Claude Code
Other Agent Skills-compatible tools
Basic knowledge of Java and stream processing concepts (helpful but not required)
An AWS Identity and Access Management (IAM) role configured with access to create and run Managed Service for Apache Flink applications, create Amazon Simple Storage Service (Amazon S3) buckets for Flink application dependencies, create Kinesis Data Streams for streaming, and create IAM roles (required if deploying an application)
Verify that the power is listed in the installed powers in the Kiro IDE.
The Power is now installed and automatically activates when you work on Flink-related development activities.
Installing as an Agent Skill
Agent Skills are discovered automatically by compatible tools through the SKILL.md file. Installation varies by tool:
Per-project installation (available in one project):
# For Cursor
git clone https://github.com/awslabs/managed-service-for-apache-flink-agent-steering-files.git .cursor/skills/flink
# For Claude Code
git clone https://github.com/awslabs/managed-service-for-apache-flink-agent-steering-files.git .claude/skills/flink
# For other Agent Skills-compatible tools
git clone https://github.com/awslabs/managed-service-for-apache-flink-agent-steering-files.git .agents/skills/flink
Personal installation (available across projects):
# For Cursor
git clone https://github.com/awslabs/managed-service-for-apache-flink-agent-steering-files.git ~/.cursor/skills/flink
# For Claude Code
git clone https://github.com/awslabs/managed-service-for-apache-flink-agent-steering-files.git ~/.claude/skills/flink
To verify the installation, interact with the skill in your preferred tool. In Claude Code, you can invoke it with /flink. In Cursor, type / in Agent chat and search for flink. For more information about Agent Skills, see the Agent Skills documentation.
Example: Building a Kinesis-to-Kinesis streaming pipeline
Rather than listing best practices, the Power/Skill actively guides you through making the right architectural decisions at each stage of development.
The following walkthrough demonstrates building a Flink application that reads from Amazon Kinesis Data Streams, analyzes events, and writes to another Kinesis stream. To follow along, run the same prompts in your Kiro IDE or other development tool. In the following prompts, we focus on local development and don’t create AWS resources. However, if you prompt the agent to create and deploy AWS resources, they will incur additional costs.
Starting the conversation
In the Kiro IDE, we can open a new chat in Vibe mode and prompt: “Help me build a Flink application that reads from Kinesis, processes events with windowed aggregations, and writes results to another Kinesis stream”:
What happens next
The AI assistant loads relevant guidance and walks you through the development process:
1. Confirm project requirements and details
Kiro automatically loads the Power based on the context of your prompt. The assistant then asks you questions about your use case to make sure that it builds the right application for your needs:
For the demo, we can prompt for a financial services use case: “I’m in financial services, so let’s use that as the use case. Try calculating volatility in real-time. And let’s use Flink 1.20 for now.”.
Kiro then confirms its assumptions and asks to proceed:
2. Project setup
After we confirm, Kiro generates a project with Flink 1.20 dependencies, Kinesis connectors, and proper scope configuration for Managed Service for Apache Flink deployment. The assistant creates the application structure with proper configuration separation between local development and Managed Service for Apache Flink service-level settings. Then, it creates a Kinesis source with proper deserialization and the sink with partitioning strategy, and windowed aggregation logic with proper state management, TTL configuration, and error handling.
Kiro also compiles the code to verify that it builds correctly. We can then proceed by asking Kiro to help us with running the application locally for testing.
3. Testing the project locally
You can run the application locally to test the results. We can prompt: “Can we run this locally using something like LocalStack to test deploying the job and also see some example results?”
Kiro creates the necessary Docker resources, testing scripts, and deployment steps to run the application locally with synthetic resources. If it encounters bugs or detects issues during the local testing process, it fixes them so that your deployment runs smoothly:
We can also access our local Flink UI to view our application:
4. Deploying the application to Managed Service for Apache Flink
Now that our application is running and generating results end-to-end, we can use the Power for other tasks. For example, you can get guidance on KPU allocation and parallelism settings based on your expected throughput, configure monitoring with CloudWatch metrics, logging, and dashboards for operational visibility, or set up infrastructure as code (IaC) for deploying in Managed Service for Apache Flink. We can prompt: “This is great! Can you help me deploy this application to Managed Service for Apache Flink? I’d like to use CloudFormation for deployment.”
Using the generated AWS CloudFormation templates and deployment scripts, we can deploy our application to AWS with associated resources for Kinesis Data Streams, Amazon S3 buckets for application JAR files, CloudWatch log groups, and IAM roles. Deploying these resources requires IAM credentials with associated permissions and will incur cost for the associated resource usage.
In a traditional workflow, you build your application, deploy to Managed Service for Apache Flink, then discover performance issues or configuration problems in production. You spend time debugging checkpoint failures, serialization errors, or resource bottlenecks.With the Power/Skill, the AI assistant catches these issues during development. When you need complex aggregation and processing logic, it helps you to do so in a way that uses resources efficiently with Flink’s scaling model. When you create an application bug that would cause a crash in production, it helps you identify it early with local end-to-end testing. The Power is configured with guidance and best practices to help with the development process from start to finish.
Example: Migrating to Flink 2.2
The Managed Service for Apache Flink Kiro Power and Agent Skill provide contextual advice specific to your situation. For new developers, it walks through the complete workflow from project setup to deployment, explaining Managed Service for Apache Flink-specific concepts along the way. For migration projects, it analyzes your existing code for Flink 2.2 compatibility issues and provides targeted refactoring guidance. The following example shows how the tool helps with the complex task of migrating from Flink 1.x to 2.2.
1. Assessing migration compatibility
We can ask Kiro to help us upgrade our project from the previous example to Flink 2.2: “I need to migrate my Flink 1.x application to 2.2. Can you help me identify compatibility issues?”
The assistant loads the Managed Service for Apache Flink Kiro Power and analyzes our code to identify potential issues:
In this case, using our generated project on Flink 1.20, Kiro identified the following compatibility issues for the upgrade:
Java 11 must move to Java 17 (minimum for Flink 2.2)
Flink version 1.20.3 must update to 2.2.0
The Kinesis connector must update from 5.1.0-1.20 to 6.0.0-2.0
Time references must change to java.time.Duration in window and lateness calls
The LocalStreamEnvironment instance of check must be removed (class removed in 2.2)
The isEndOfStream() override must be dropped from PriceTickDeserializer (method removed)
implements Serializable must be added to PriceTick and VolatilityResult
It also verified that some parts of the project are already Flink 2.2 compatible. The project uses the new Source Sink V2 APIs, the logging is 2.2 ready, the POJOs with no collection fields are state migration safe, and there are no Kryo registrations or TimeCharacteristic usage.
2. Implementing the migration
We can then ask Kiro to provide a step-by-step migration plan, both for updating the code and deploying to Managed Service for Apache Flink: “Can you help me update the application for Flink 2.2, and help me figure out the steps to upgrade my running Managed Service for Apache Flink application?”
Kiro evaluates the entire application code base. It evaluates it against the Power’s migration guidance and best practices, and provides a comprehensive analysis of the breaking changes, risks, and potential issues that would arise in the upgrade. After we approve the changes, Kiro then proceeds to make the necessary updates to make our application compatible with Flink 2.2 and provide us with a step-by-step upgrade process for the running application:
Now that Kiro has prepared the application for Flink 2.2, highlighted migration risks, and provided us with a clear path to execute the upgrade, you can test the upgrade process with confidence. From here, we can proceed to run our Flink 2.2 application locally, test the upgrade process in a development environment in Managed Service for Apache Flink, and then execute the upgrade in our production environment. If we run into issues, we can return to the Kiro Power to get advice, resolve issues, and unblock our upgrade.
Cleanup
To remove the Power/Skill installation:
For Kiro:
Open Kiro IDE.
Navigate to the Powers tab.
Uninstall the Amazon Managed Service for Apache Flink Power.
For Agent Skills:
# Remove per-project installation
rm -rf .cursor/skills/flink # or .claude/skills/flink
# Remove personal installation
rm -rf ~/.cursor/skills/flink # or ~/.claude/skills/flink
If you created Managed Service for Apache Flink applications or associated resources during development, clean the resources up:
Delete the Managed Service for Apache Flink application from the AWS Console.
Remove associated resources for sources and sinks, if created for development.
Delete CloudWatch log groups if no longer needed.
Conclusion
In this post, we showed you how the Kiro Power and Agent Skill for Amazon Managed Service for Apache Flink brings AI-assisted development to stream processing. You can use the tool to overcome Flink’s learning curve, build applications following Managed Service for Apache Flink best practices, and migrate to Flink 2.2 with confidence. To get started, choose the path that fits your workflow:
If you use Kiro, install the Power from the Powers tab and start a new chat with a Flink-related prompt.
If you use Cursor, Claude Code, or another Agent Skills-compatible tool, clone the GitHub repository into your skills directory and reference the steering/ files for guidance.
Amazon Managed Streaming for Apache Kafka (Amazon MSK) is a fully managed streaming data service that handles Apache Kafka infrastructure and operations, so developers and DevOps managers can run Apache Kafka applications on AWS. Migrating to Amazon MSK requires no application code changes because Amazon MSK uses fully open source Apache Kafka, allowing existing applications and tools to work seamlessly. Amazon MSK with Express brokers streamlines Kafka management by providing up to 3x more throughput, 20x faster scaling, and 180x faster recovery with virtually unlimited storage, delivering resiliency and elasticity for mission-critical workloads.
SASL/SCRAM authentication for username and password-based authentication stored in AWS Secrets Manager.
When customers manage their own Kafka clusters and adopt mTLS, they typically rely on a third-party managed certificate authority (CA) to sign and verify both client and server certificates. This establishes a trust relationship where the CA acts as the trusted intermediary that validates the identity of both parties in the communication. When customers migrate their workloads to Amazon MSK, they must make sure that client certificates are signed by a CA that’s recognized and trusted by the MSK cluster. Amazon MSK recommends customers to use AWS Private Certificate Authority to create a private CA within AWS that MSK trusts. The migration path typically requires customers to either:
Generate new client certificates signed by an AWS Private CA that Amazon MSK recognizes, or
Establish a certificate chain where their existing third-party CA is subordinate to or trusted by an AWS-managed CA
In this post, we provide an approach to reuse your existing client certificates without reissuing them through AWS Certificate Manager (ACM) Private Certificate Authority. This solution enables an accelerated migration path by using your current third-party CA infrastructure. This removes the complexity and operational overhead of certificate re-issuance while maintaining the security posture that you’ve established with your existing mTLS implementation.
Solution overview
This approach involves four key steps to reuse your existing client certificates when migrating to Amazon MSK:
1. Create an Intermediate Certificate Using Your Third-Party CA
First, you generate an intermediate certificate authority (CA) certificate using your existing third-party CA infrastructure. This intermediate certificate acts as a bridge between your current certificate management system and AWS.
2. Import the Intermediate Certificate into AWS Certificate Manager as a Private CA
Next, you import this intermediate certificate into AWS Certificate Manager (ACM) as a Private Certificate Authority (PCA). This step establishes the intermediate CA within the AWS environment, making it recognizable to AWS services.
3. Integrate Amazon MSK with the PCA created from your Intermediate Certificate
You then configure your Amazon MSK cluster to use the ACM Private CA that contains your imported intermediate certificate. This integration enables Amazon MSK to recognize and trust certificates signed by your certificate authority.
4. Establish trust through common Certificate Authority
This approach works because both the AWS Private CA and your existing client certificates share the same root of trust—they’re both signed by your third-party CA. When Amazon MSK validates client certificates, it can trace the certificate chain back through the intermediate certificate in AWS Private CA to your trusted third-party CA, establishing a complete chain of trust without requiring certificate reissuance.This solution maintains your existing security architecture while enabling seamless migration to Amazon MSK, so your clients can continue using their current certificates without interruption.
Figure 1: Architecture diagram showing the integration of third-party Certificate Authority with Amazon MSK through AWS Certificate Manager Private CA
Implementation steps
In real-world scenarios, you already have a certificate authority that has issued certificates for your clients. For the purpose of this post, we use a code sample to create a self-signed certificate authority (using OpenSSL) to demonstrate the implementation steps. If you already have an existing certificate authority, you don’t need to create a root CA. You can generate an intermediate CA (Step 2) using your third-party CA and continue following the steps from where you import the intermediate CA certificate into AWS ACM as a Private Certificate Authority.
Step 1: Create a root Certificate Authority using OpenSSL
Cloning the repository
To clone the repository, complete the following steps:
You will be prompted to set up a password for the private key and the certificate. Here is an example of an output
Step 2: Create an intermediate CA for AWS ACM
In the AWS Private CA console, create a subordinate CA.
Enter distinguished name information matching your organization, Key algorithm and Create CA.
From the Actions menu, select Install CA certificate.
Download the Certificate Signing Request (CSR) file provided by AWS Private CA.
Download the CSR file to your local directory (“certs”) as “CSR.pem”.
Sign the ACM PCA issued CSR with your Root CA using the provided ./sign-acm-ca.sh in the code example.
Note: AWS Private CA retains the private key internally. You only sign their CSR and import the resulting certificate back to the AWS Private CA.
Step 3: Import signed certificate to AWS ACM Private CA
Go back to the AWS ACM console.
Select the CA that you created and select Install CA certificate.
Select External private CA as CA type.
Importing the certificate into AWS Certificate Manager
Open both files in a text editor:
acm-subordinate-ca-cert.pem
acm-ca-chain.pem
Do the following in the Certificate body field in AWS ACM:
Copy the entire content from the acm-subordinate-ca-cert.pem file and paste it into the text box.
Open the acm-ca-chain.pem file.
This file contains one certificate (The root CA certificate)
Do the following in the Certificate chain field in AWS ACM:
Copy the root CA certificate portion and paste it into the text box
Important: The certificate chain shouldn’t include the subordinate CA certificate itself—only the certificates above it in the chain (the root CA).
Choose Confirm and install to complete the process.
You should see the AWS Private CA turns into active state.
Step 4: Configure your MSK cluster for Mutual TLS authentication
Select your MSK cluster, go to Properties and edit the Security settings.
Select TLS client authentication through AWS Certificate Manager (ACM) as the access control method and choose the Subordinate CA that you created earlier. Then choose Save changes.
Step 5: Test your client
Run the certificate generation script
Execute the following command, replacing <client-name> with a descriptive name for your client (this will be used in the certificate filename):./generate-client-cert.sh <client-name>
Example:
./generate-client-cert.sh kafka-admin
Enter distinguished name information
When prompted, enter the distinguished name (DN) options. These should match your root CA settings except for the Common Name (CN):
Country (C): Match your root CA (for example, US)
State (ST): Match your root CA (for example, State)
Organization (O): Match your root CA (for example, Anycompany)
Organizational Unit (OU): Match your root CA (for example, IT)
Common Name (CN): Use a client-specific identifier (for example, kafka-admin or client)
Verify certificate files
After the certificate is generated, verify that the files were created successfully by running:ls ~/ca/certsYou should see files with your client name, including:
<client-name>.key (private key)
<client-name>.crt (certificate)
<client-name>.p12 (PKCS12 keystore)
Create Kafka client properties file
Create a new properties file for your Kafka client (for example, kafka-tls-client.properties) based on the provided kafka-admin-ssl.properties example file. Update the file paths to reference your newly generated client certificate files.
Example configuration:
security.protocol=SSL
ssl.keystore.location=/path/to/<client-name>.p12
ssl.keystore.password=your-keystore-password
ssl.key.password=your-key-password #omit if you didn’t set key password
ssl.keystore.alias=your-private-key-alias
Step 6: Testing the Kafka client connection
To test the Kafka client connection, do the following.
Set environment variables
First, set the required environment variables for your Kafka installation and MSK cluster:
Note: Replace <your-msk-bootstrap-servers> with your actual Amazon MSK cluster bootstrap server endpoints (for example, b-1.mycluster.abc123.kafka.us-east-1.amazonaws.com:9094,b-2.mycluster.abc123.kafka.us-east-1.amazonaws.com:9094)
Run the Kafka list topics command
Execute the following command to verify that your client can successfully connect to Amazon MSK using mutual TLS authentication:
Which certificates are being presented by both client and server
The certificate chain validation steps
Which certificate from your truststore is being used for authentication
When this is helpful:
When you have multiple certificates in your truststore and need to identify which one is being used
When troubleshooting certificate chain validation issues
When verifying that the correct client certificate is being presented during authentication
When diagnosing certificate mismatch or trust issues
Reading the debug output:
Look for lines containing:
***Certificate chain – Shows the certificates being presented
Found trusted certificate – Indicates which certificate in your truststore matched
Cert path validation – Shows the certificate chain validation process
To disable debug mode after troubleshooting, simply unset the environment variable:
unset KAFKA_OPTS
Conclusion
This post presents a solution for migrating TLS clients from self-managed Apache Kafka to Amazon MSK while reusing existing third-party CA-signed certificates. The approach removes the need for certificate reissuance by instead creating an intermediate CA from the existing third-party CA, importing it into AWS Certificate Manager as a Private CA, and integrating it with Amazon MSK. This maintains the established chain of trust through the common certificate authority, enabling seamless migration without operational disruption while preserving the existing security architecture and mTLS implementation. To read more about the Amazon MSK security model, see Security in Amazon MSK.
Optimizing the Airflow worker pool configuration in Amazon Managed Workflows for Apache Airflow (Amazon MWAA), the AWS fully managed Apache Airflow service, is an important yet often overlooked strategy for scaling workflow operations. Tasks queued for longer periods can create the illusion that additional workers are the solution, when in reality the root cause might lie elsewhere. The decision to scale isn’t always straightforward. DevOps engineers and system administrators frequently face the challenge of determining whether adding more workers will solve their performance issues or only increase operational cost without addressing the root cause.
This post explores different patterns for worker scaling decisions in Amazon MWAA, focusing on the task pool mechanism and its relationship to worker allocation. By examining specific scenarios and providing a practical decision framework, this post helps you determine whether adding workers is the right solution for your performance challenges, and if so, how to implement this scaling effectively.
Main patterns
This section discusses the most frequently seen problems that raise the question if adding additional workers would improve the health of your environment.
High CPU
Airflow serves as a workflow management platform that coordinates and schedules tasks to be run on external processing services. It acts as a central orchestrator that can trigger and monitor tasks across various data processing systems like AWS Glue, AWS Batch, Amazon EMR, and other specialized data processing tools. Rather than processing data itself, Airflow’s strength lies in managing complex workflows and coordinating jobs between different systems and services.
In Analytics and Big Data environments, there is a prevalent misconception that saturated resources automatically warrant adding more capacity. However, for Amazon MWAA, understanding your workflow characteristics and optimization opportunities should precede scaling decisions.
As you scale up your workflows, resource utilization of the Airflow clusters naturally increases. When workers consistently operate at full capacity, it may seem intuitive to add additional compute resources. However, this approach often masks underlying inefficiencies rather than resolving them.
For example, in Amazon MWAA if you are running a single task that is consuming 100% of the available CPU on your Amazon MWAA worker, adding additional workers will not resolve the problem as the task is not optimized nor split into smaller parts. As such, increasing the number of minimum workers will not bring the expected effect but will only increase the operating costs.
When your Amazon MWAA workers are consistently running above 90% CPU or Memory utilization, you’ve reached a critical decision point. Before taking actions, it is essential to understand the root cause. You have three primary options:
Scale horizontally by adding additional workers to distribute the load.
Scale vertically by upgrading to a larger environment class for more resources per worker.
Optimize your DAGs and scheduling patterns to be more efficient and consume fewer resources.
Each approach addresses different underlying issues, and choosing the right path depends on identifying whether you are facing a capacity constraint, resource-intensive task design, or workflow inefficiency. For guidance on optimization strategies, please refer to Performance tuning for Apache Airflow on Amazon MWAA.
Select a time window long enough to show usage patterns.
Set period to 1 Minute.
Set statistics to Maximum.
Long queue time
Sometimes Airflow tasks are stuck in a queued state for a long time, which prevents DAGs from completing on time.
In Amazon MWAA, each environment class comes with configured minimum and maximum worker nodes. Each worker provides a pre-configured concurrency, which is the number of tasks that can run simultaneously on each worker at any given time. The behavior is controlled through celery.worker_autoscale=(max,min).
For example, if you have minimum 4 mw1.small workers, with default Airflow configuration, you will be able to run 20 concurrent tasks (4 workers x 5 max_tasks_per_worker). If your system suddenly requires more than 20 tasks to execute concurrently, this will result in an autoscaling event. Amazon MWAA will decide how to scale your workers efficiently, and trigger the process. The autoscaling process, however, requires additional time to provision new workers resulting in additional tasks in queued status. To mitigate this queuing issue, consider the following:
If the CPU utilization on the workers is low, increasing the max value in celery.worker_autoscale=(max,min) can reduce the time tasks stay in queued state as each worker will be able to process more tasks concurrently. Airflow worker can take tasks up to the defined task concurrency regardless of the availability of its own system resources. As a result, the base worker may reach 100% CPU/Memory utilization before Autoscaling takes effect.
If you do not want to increase the task concurrency on the workers, increasing the minimum worker count can also be beneficial because having more available workers allows a higher number of tasks to run concurrently.
Scheduling delays
Adding new DAGs can not only affect your system resources, but it can also create uneven scheduling patterns. Some DAGs may experience delayed execution because of resource competition, even when the overall environment metrics appear healthy. This scheduling skew often manifests as inconsistent task pickup times, where certain workflows consistently wait longer in the queue while others execute promptly.
When Amazon CloudWatch metrics show increasing variance in task scheduling times, particularly during periods of high DAG activity, it signals the need for environment optimization. This scenario requires careful analysis of execution patterns and resource utilization to determine if:
While adding workers can help distribute the workload, this solution is most effective when the high utilization is primarily because of task execution load rather than DAG parsing or scheduling overhead. Adding more minimum workers will allow you to execute more tasks in parallel. For example, if you observe the value of AWS/MWAA/ApproximateAgeOfOldestTask to be steadily increasing, it means that the workers are not able to consume the messages from the queue fast enough. Additionally, you can also monitor the AWS/MWAA/QueuedTasks to identify similar patterns.
Upgrading the environment class would provide better scheduling capacity. If the Scheduler is showing signs of strain or if you’re seeing high resource utilization across all components, upgrading to a larger environment class might be the most appropriate solution. This provides more resources to both the Scheduler and Workers, allowing for better handling of increased DAG complexity and volume. To validate the same, use AWS/MWAA/CPUUtilization and AWS/MWAA/MemoryUtilization in the Cluster metrics and choose Scheduler,BaseWorker and AdditionalWorker metrics.
Restructuring DAG schedules would reduce resource contention.
The key is to understand your workflow patterns and identify whether the scheduling delays are because of insufficient worker capacity or other environmental constraints.
Anti patterns
This section showcases the most common anti patterns which make MWAA users think that adding more workers will improve performance.
Underutilized workers
When evaluating Amazon MWAA performance bottlenecks, it’s important to distinguish resource constraints and DAG design inefficiencies before scaling the environment.
Sometimes the Amazon MWAA environment has the capacity to run 100 tasks concurrently but your queue metrics (AWS/MWAA/RunningTasks) show only 20 tasks active most of the time with no tasks remaining in queued state. In such scenarios, you are advised to check Amazon CloudWatch for consistently low CPU and memory usage on existing workers during peak workload times. If this is confirmed, it is usually an indication of inefficiencies in DAG design, scheduling patterns, or Airflow configuration.
You have two primary options to address this:
1. Downsize: If you do not expect your workload to increase, it is safe to assume you have over-provisioned your cluster. Start by removing any extra workers first and finally resolve to downsizing your environment class.
2. Optimize: Fine tune your DAG scheduling and airflow configuration through Pools and Airflow configuration for concurrency to increase the throughput of your system.
Misconfigured Airflow configurations that create artificial bottlenecks
In Apache Airflow, performance bottlenecks often occur because of configuration settings, not actual resource constraints. At such times, DAG executions get delayed not because of insufficient compute, but because of incorrect concurrency configuration.
Efficient use of Amazon MWAA requires reviewing not only resource utilization for Workers and Schedulers but also concurrency configurations for artificially created bottlenecks. Sometimes one restrictive configuration prevents the scaling benefits of larger environment or additional workers. Always audit Airflow configurations if performance seems limited even when system metrics suggest spare capacity.
Important consideration: Amazon Managed Workflows for Apache Airflow (Amazon MWAA) does not automatically update the worker concurrency configuration when you change the environment class. This behavior is important to understand when scaling your environment. If you initially create an mw1.small environment, where each worker can handle up to 5 concurrent tasks by default. When you upgrade to a medium environment class (which supports 10 concurrent tasks per worker by default), the concurrency setting remains at 5 for in-place updated environments. You must manually update the concurrency configuration to take full advantage of the increased capacity available in the medium environment class.
Because of this you need to also update the Airflow configurations that control concurrency whenever you update the environment class. To update the concurrency setting after upgrading your environment class, modify the celery.worker_autoscale configuration in your Apache Airflow configuration options. This makes sure your workers can process the maximum number of concurrent tasks supported by your new environment class.
Other times, an Amazon MWAA environment can be constrained by max_active_runs or DAG concurrency controls instead of actual resource limits. These configuration-based throttles prevent tasks from running, even when the worker instances have available compute to handle the workload.
There is an important distinction between the two. Configuration limits act as artificial caps on parallelism, while true resource limits indicate that workers are fully utilizing their CPU or memory capacity. Understanding which type of constraint affects your environment helps you determine whether to adjust configuration settings or scale your infrastructure.
Adjusting Airflow configurations such as Pools, concurrency, max_active_runs solves performance problems without scaling workers. Some of the configurations you can use to control this behavior:
max_active_runs_per_dag (DAG level): Controls how many DAG runs for a given DAG are allowed at the same time. If set to 2, only 2 DAG runs can run concurrently, even if there is plenty of worker capacity left. Extra runs queue, making the DAG executions slow even though workers are idle.
max_active_tasks:Controls the concurrency field in a DAG definition (or setting at environment level) limits the number of tasks from the DAG running at any moment, regardless of overall system capacity or number of workers.
Pools:Pools restrict how many tasks of a certain type (often resource heavy) can run at once. A pool with only 3 slots will throttle any tasks above 3 assigned to that pool, leaving workers idle.
Execution timeouts and retries: If not tuned, failed tasks might fill up slots unnecessarily, stuck tasks can block worker slots and slow queue processing.
Scheduling intervals and dependencies: Overlapping or inefficient scheduling may cause idle periods or excess contention for resources, affecting real throughput.
How Airflow configurations can override each other
Airflow has multiple layers of concurrency and scheduling controls. Some at the environment level, some at the DAG/task level, and others for pools. Sometimes more restrictive settings override more permissive ones, resulting in unexpected queue buildup.
DAG level vs Environment level: If “max_active_runs_per_dag” (DAG level) is lower than the environment-level “max_active_runs_per_dag” or system wide concurrency, the DAG setting is used, throttling tasks even if the environment could do more.
Task level overrides: Individual task definitions can have their own parameters like “max_active_tis_per_dag” which can cap runs per task and create a bottleneck if set lower than global settings.
Order of precedence: The most restrictive relevant configuration at any level (Environment, DAG, Task) effectively sets the upper bound for parallel task execution.
Setting Location
Setting
Effect on task throughput
Environment Level
parallelism
Max total tasks running on Scheduler
DAG Level
max_active_runs
Max simultaneous DAG runs
Task Level
concurrency
Max concurrent task for that DAG
Performance issues often resemble resource exhaustion, but actually derive from overly restrictive configurations. Audit all the preceding parameters carefully. You can loosen restrictive values step by step and monitor their effect before deciding to scale your cluster further. This approach ensures optimal and cost-efficient usage of your cloud resources without paying for idle capacity.
Slow resource depletion from memory leaks
A common scenario for memory leak or slow resource depletion in Amazon MWAA is when DAGs and tasks begin to fail or slow down over time. Scaling workers or increasing environment size does not resolve the underlying issue. This happens because the root cause is not a lack of capacity but rather an application-level leak that causes persistent exhaustion.
For example, as Airflow continuously runs tasks and parses DAGs over time, memory consumption can steadily increase across the environment. This might manifest as an Amazon MWAA metadata database experiencing declining FreeableMemory metrics despite consistent or even reduced workloads. When this occurs, database query performance gradually declines as memory resources become constrained for scheduler/worker & metadata database, ultimately affecting overall environment responsiveness since Airflow depends heavily on its metadata database for critical operations. This scenario is similar to how an application might create database connections without properly closing them, leading to resource exhaustion over time.
Graph: Declining FreeableMemory and MemoryUtilization
Common causes:
Connection pool exhaustion: DAGs that fail to properly close database connections can lead to connection pool exhaustion and memory leaks in the database.
Resource-intensive operations: Complex, long-running queries or XCOM operations against the metadata database can consume excessive memory.
Inefficient DAG design: DAGs with numerous top-level Python calls can trigger database queries during DAG parsing. For instance, using variable.get() calls at the DAG level rather than at the task level creates unnecessary database load.
Recommended solutions:
Implement Amazon CloudWatch monitoring: Establish Amazon CloudWatch alarms for FreeableMemory with appropriate thresholds to detect issues early.
Regular database maintenance: Perform scheduled database clean-up operations to purge historical data that is no longer needed.
Optimize DAG code: Refactor DAGs to move database operations like variable.get() from the DAG level to the task level to reduce parsing overhead.
Connection management: Make sure all database connections are properly closed after use to prevent connection pool exhaustion.
By following the preceding recommendations you can maintain healthy memory utilization for the metadata database and maintain optimal performance of your Amazon MWAA environment without needing to scale workers.
Conclusion
The decision to add workers in Amazon MWAA environments requires careful consideration of multiple factors beyond simple task queue metrics. In this post, we showed that while adding workers can address certain performance challenges, it’s often not the optimal first response to system bottlenecks.
Key considerations before scaling workers include:
Root cause analysis
Verify whether high CPU/memory usage stems from task optimization issues.
Examine if queuing problems result from configuration constraints rather than resource limitations.
Investigate potential memory leaks or resource depletion patterns.
Configuration optimization
Review and adjust Airflow parameters (concurrency settings, pools, timeouts).
Understand the interaction between different configuration layers.
Optimize DAG design and scheduling patterns.
The most successful Amazon MWAA implementations follow a systematic approach: first optimizing existing resources and configurations, then scaling workers only when justified by data-driven capacity planning. This approach ensures cost-effective operations while maintaining reliable workflow performance.
Remember that worker scaling is only one tool in the Amazon MWAA optimization toolkit. Long-term success depends on building a comprehensive performance management strategy that combines proper monitoring, proactive capacity planning, and continuous optimization of your Airflow workflows.
In the next post, we discuss capacity planning and the steps you need to perform before adding additional DAGs in your environment so that you can plan for the additional load and make sure you have enough headroom.
Amazon OpenSearch Service now brings application monitoring, native Amazon Managed Service for Prometheus integration, and AI agent tracing together in OpenSearch UI‘s observability workspace. You can query Prometheus metrics with PromQL alongside logs and traces stored in Amazon OpenSearch Service, trace an AI agent’s full reasoning chain down to the failing tool call, and drill from a service-level health view to the exact span that caused a checkout failure, all without leaving the interface.
In this post, we walk through two real-world scenarios using the OpenTelemetry sample app: a multi-agent travel planner facing slow processing, and a checkout flow quietly failing on one microservice. We chase each one to its root cause using these new capabilities.
Scenario 1: An underperforming AI agent
Your multi-agent travel planner is live and users start reporting slow responses. With the new AI agent tracing capability in Amazon OpenSearch Service, you can trace the agent’s full processing path to pinpoint exactly where things went wrong.
In any observability workspace in OpenSearch UI, navigate to Application Map in the left navigation pane.
You can see the full topology of your system including the travel agent and the sub-agents it calls. The travel agent node shows elevated latency and occasional errors. Select it, and the side panel confirms that latency is up but the latency chart shows intermittent spikes rather than consistent degradation.
The application map tells you something is wrong, but understanding why an AI agent is underperforming requires seeing its reasoning chain. Select Agent Traces in the left navigation pane, then filter by service name and time range.
Select one of the traces to see the trace tree. Unlike a traditional span waterfall, this view organizes around the agent’s reasoning chain: the root agent span, the LLM calls it made, the tools it invoked, and how they nested each step color-coded by type. The trace map provides a visual directed graph of the same execution. You can see which model was called, how many input and output tokens were consumed, and the actual messages sent to and received from the model.
A tool call inside the weather agent errored out. The agent then spent additional time reasoning about the failure before returning a partial response explaining the intermittent latency spikes and occasional faults.
Why this matters for AI agents
Agents make autonomous decisions based on LLM responses, tool results, and chained reasoning. Unlike traditional microservices with deterministic code paths, agent behavior varies across executions. Without semantic tracing that captures these AI-specific signals, root-cause analysis is guesswork. The trace tree surfaced the model name, token counts, and failing tool call because the travel planner was instrumented with OpenTelemetry’s generative AI semantic conventions. The next section describes how.
Instrumenting AI agents
OpenTelemetry auto-instrumentation enriches spans with well-known attributes for HTTP, database, and gRPC calls. AI agents need a different set of attributes such as which LLM was called, what tokens were consumed, which tools were invoked, that standard instrumentation doesn’t cover.
The OpenTelemetry gen_ai semantic conventions define standard attributes for these signals, including gen_ai.operation.name, gen_ai.usage.input_tokens, gen_ai.request.model, and gen_ai.tool.name. When Amazon OpenSearch Service receives spans with these attributes, it categorizes them by operation type (agent, LLM, tool, embeddings, retrieval) and renders the agent trace tree and trace map views.
The Python SDK provides one way to generate these spans. To send traces to Amazon OpenSearch Ingestion, configure the SDK with AWS Signature Version 4 (SigV4) authentication. The AWSSigV4OTLPExporter cryptographically signs each HTTP request to help prevent unauthorized data ingestion. The calling identity needs an IAM policy that grants osis:Ingest on your pipeline’s ARN. Credentials are resolved through the standard AWS credential provider chain.
Use the @observe decorator to trace agent functions and enrich() to add model metadata:
@observe(op=Op.EXECUTE_TOOL)
def get_weather(city: str) -> dict:
return {"city": city, "temp": 22, "condition": "sunny"}
@observe(op=Op.INVOKE_AGENT)
def assistant(query: str) -> str:
enrich(model="gpt-4o", provider="openai")
data = get_weather("Paris")
return f"{data['condition']}, {data['temp']}C"
result = assistant("What's the weather?")
The SDK also supports auto-instrumentation for OpenAI, Anthropic, Amazon Bedrock, LangChain, LlamaIndex, and others. Because the instrumentation is built on OpenTelemetry standards, any agent framework that emits spans with gen_ai.* attributes is compatible with OpenSearch UI.
Scenario 2: Investigating a microservice issue
AI agents are only one part of most production environments. The same interface surfaces telemetry from conventional microservices, where the troubleshooting workflow follows a more familiar path.
Your ecommerce checkout begins paging during a busy traffic window. From OpenSearch UI, navigate to APM Services in the left navigation pane. Every instrumented service is listed alongside its health indicators. The checkout service shows an elevated error rate.
Select the affected service. The detail view shows Request, Error, and Duration (RED) metrics: request rate is climbing, fault rate has spiked in the last 15 minutes, and p99 duration has doubled. You can see exactly when the degradation started.
Drill into the correlated spans for the affected time window. The span list shows multiple failed requests, all hitting the same endpoint. Select one to see the full trace waterfall. The checkout service called prepareOrder, which failed trying to retrieve a product from the catalog. The error message in the span details tells you exactly what went wrong, that’s your root cause.
Checking the infrastructure with PromQL
In both scenarios, the natural next question is whether the problem originates in the application or in the infrastructure beneath it. With the new Amazon Managed Service for Prometheus integration, you can answer that question without leaving OpenSearch UI.
Prometheus metrics are now queryable directly from the same workspace using native PromQL syntax, alongside the logs and traces you’ve already been navigating.
For the database timeout in Scenario 2, run a PromQL query to check the database instance’s read/write throughput for the same time window. For the agent latency issue in Scenario 1, check the LLM endpoint’s response time metrics to see if the slowness originates from the model provider.
This is a key architectural decision: metrics continue to live in Amazon Managed Service for Prometheus, logs and traces continue to live in Amazon OpenSearch Service, and neither signal is copied or warehoused into a second store. Each backend remains the single store for the data type it’s purpose-built to handle, while OpenSearch UI federates queries across both at runtime. The cost, retention, and operational model of each store stay intact while the troubleshooting workflow collapses into a single interface.
To configure the OpenTelemetry Collector and OpenSearch Ingestion pipelines that route metrics into Amazon Managed Service for Prometheus, see Ingesting application telemetry.
How it’s wired together
The following diagram shows the end-to-end architecture. Applications instrumented with OpenTelemetry send traces, logs, and metrics over OTLP to Amazon OpenSearch Ingestion. OpenSearch Ingestion routes each signal to the appropriate store: traces and logs land in Amazon OpenSearch Service, while metrics flow into Amazon Managed Service for Prometheus. OpenSearch UI then queries both stores to render the Application Map, Services catalog, Agent Traces, and Metrics views.
The entire experience rests on open-source foundations, Prometheus for metrics, OpenSearch for logs and traces, and OpenTelemetry for instrumentation, so teams already running an OpenTelemetry collector can adopt it by updating the collector’s export configuration to point at Amazon OpenSearch Ingestion, with no proprietary agents or rewritten instrumentation required.
Getting started
To enable these capabilities, log in to OpenSearch UI’s observability workspace, select the Gear icon in the bottom left corner to open Settings and setup, and verify that the Observability:apmEnabled toggle is on under the Observability section. OpenSearch UI is available at no additional charge for Amazon OpenSearch Service customers.
Explore locally first. The OpenSearch Observability Stack gives you a fully configured environment including application monitoring, agent tracing, and Prometheus integration, running on your machine with a single install command. It ships with sample instrumented services, including a multi-agent travel planner, so you can explore the full workflow with real telemetry data out of the box.
For AI agent development.Agent Health is an open-source, evaluation-driven observability tool designed for local development. It gives you execution flow graphs, token tracking, and tool invocation visibility right in your development loop, before you push to production.
Migrating to Apache Flink 2.2 on Amazon Managed Service for Apache Flink gives you access to Java 17 runtime, faster checkpoints and recovery through RocksDB 8.10.0, and SQL-native artificial intelligence and machine learning (AI/ML) inference. If you run Flink 1.x today, you might be dealing with an aging Java 11 runtime that will no longer receive standard support by the end of this year, slower state backend performance, and a fragmented API surface split across DataSet, DataStream, and legacy connector interfaces. Flink 2.2 addresses these gaps in a single major version upgrade.
Apache Flink is an open source distributed processing engine for stream and batch data, with first-class support for stateful processing and event-time semantics. Amazon Managed Service for Apache Flink removes the operational overhead of running Flink. You provide your application code, and the service provisions, scales, checkpoints, and patches the infrastructure for you.
In this post, we explain what’s new in Amazon Managed Service for Apache Flink 2.2, provide a guided migration using CLI commands, console instructions, and code examples, and show you how to monitor the upgrade and roll back if needed.
What’s new in Amazon Managed Service for Apache Flink 2.2
This release spans runtime upgrades, SQL, and Table API capabilities. The following sections break down each area.
Runtime and performance
These changes improve application performance and bring your runtime up to current standards.
Java 17 runtime – Flink 2.2 requires Java 17. Build your application code with JDK 17 for better garbage collection, a more secure runtime, and modern language features like sealed classes and records. Java 11 is no longer supported.
Python 3.12 – Flink 2.2 requires Python 3.9+, with Python 3.12 as the default. Python 3.8 is no longer supported.
RocksDB 8.10.0 – Your stateful applications benefit from improved I/O performance with the upgraded state backend, resulting in faster checkpoints and recovery.
Dedicated collection serializers – Improved serializers for Map, List, and Set types reduce serialization overhead, which lowers checkpoint sizes for applications that use these data structures frequently.
Kryo 5.6 – Kryo upgrades from version 2.24–5.6. This has state compatibility implications covered in the migration section.
SQL and Table API highlights
With Flink 2.2, you can:
Call Machine Leaning (ML) models directly from SQL using ML_PREDICT and CREATE MODEL
Work with semistructured data through the native VARIANT type
You can upgrade a running Flink 1.x application to 2.2 using the UpdateApplication API, the AWS Management Console, AWS CloudFormation, the AWS SDK, and Terraform Modules. The upgrade preserves your application configuration, logs, metrics, tags, and, if your state and binaries are compatible.
Auto-rollback
With auto-rollback turned on, binary incompatibilities detected during job startup trigger an automatic revert to the previous Flink version within minutes, with no manual intervention required. For state incompatibilities that surface as restart loops after a successful upgrade, invoke the Rollback API to return to your previous version and state.
Unsupported open source features
The following Flink 2.2 features aren’t currently supported in Amazon Managed Service for Apache Flink because they’re still considered experimental: Materialized Tables, ForSt State Backend (disaggregated state storage), Java 21, and custom metric reporters/telemetry configurations. We continue to evaluate these features as they mature in the Apache Flink project and will share updates on availability. You can have a closer look to which features are supported in Apache Flink 2.2 features supported
Now that you know what’s changed, the next section walks through the migration process.
Prerequisites
Before starting the migration, confirm that you have the following in place:
An existing Apache Flink 1.x application running on Amazon Managed Service for Apache Flink.
JDK 17 installed in your local build environment.
The AWS Command Line Interface (AWS CLI) installed and configured with permissions to call the kinesisanalyticsv2 APIs (UpdateApplication, CreateApplicationSnapshot, DescribeApplication, RollbackApplication).
An Amazon Simple Storage Service (Amazon S3) bucket to upload your updated application JAR.
We recommend testing each phase on a non-production replica of your application before applying the same steps to production.
Step 1: Update your application code
Start by updating your Flink dependencies to version 2.2.0 and replacing deprecated APIs. The following sections show the most common changes.
Flink 2.2 removes the FlinkKinesisConsumer and FlinkKinesisProducer classes. The following example shows how to migrate to the FLIP-27 based KinesisStreamsSource.Before (Flink 1.x):
FlinkKinesisConsumer<String> consumer = new FlinkKinesisConsumer<>(
"my-stream",
new SimpleStringSchema(),
consumerConfig);
env.addSource(consumer);
Beyond connector updates, make the following code changes:
Replace DataSet API usage with the DataStream API or Table API/SQL.
Replace Scala API usage with the Java API.
Verify that your build targets JDK 17.
Build your updated application JAR and upload it to Amazon S3 with a different file name than your current JAR (for example, my-app-flink-2.2.jar).
Step 2: Check state compatibility
Before upgrading, assess whether your application state is compatible with Flink 2.2. The Kryo upgrade from version 2.24 to 5.6 changes the binary format of serialized state. Applications using POJOs with Java collections (HashMap, ArrayList, HashSet) are the most common source of incompatibility.
Quick compatibility check:
Serialization type
Compatible?
Avro (SpecificRecord, GenericRecord)
Yes
Protobuf
Yes
POJOs without collections
Yes
Custom TypeSerializers (no Kryo delegation)
Yes
POJOs with Java collections
No
Scala case classes
No
Types using Kryo fallback
No
Check your logs for Kryo fallback:
Search your application logs for this pattern, which indicates a type is falling back to Kryo serialization:Class class <className> cannot be used as a POJO type
Step 3: Turn on auto-rollback and automatic snapshots
Turn on auto-rollback so the service automatically reverts to the previous version if the upgrade fails. Also, verify that automatic snapshots are turned on. The service takes a snapshot before the upgrade that serves as your rollback point.
Run the upgrade while the application is in RUNNING or READY (stopped) state. The following example upgrades a running application and points to the new JAR.
Navigate to your application in the Amazon Managed Service for Apache Flink console.
Choose Configure.
Select the Flink 2.2 runtime.
Point to your new application JAR on Amazon S3.
Select the snapshot to restore from (use Latest to start from the most recent snapshot).
Choose Update.
AWS CloudFormation:
Update the RuntimeEnvironment field in your template. AWS CloudFormation now performs an in-place update instead of deleting and recreating the application.
Terraform:
If you manage your Flink application with Terraform, you can perform the same in-place upgrade by updating the runtime_environment and code reference in your aws_kinesisanalyticsv2_application resource. Note: Terraform support for FLINK-2_2 requires AWS provider version 6.40.0 or later (released April 8, 2026). Earlier provider versions don’t recognize this runtime value. First, update your provider version constraint:
Then run terraform init -upgrade to pull the new provider.Next, update your application resource. Change runtime_environment from “FLINK-1_20” to “FLINK-2_2” and point to your new JAR:
terraform plan # Review the in-place update
terraform apply # Apply the runtime change
Terraform will perform an in-place update of the application, changing the runtime version and code location. The application will restart with the new Flink 2.2 runtime. To roll back with Terraform, revert runtime_environment to “FLINK-1_20”, point file_key back to your original JAR, and run terraform apply again. Note that you cannot restore a Flink 2.2 snapshot on Flink 1.x, so the rollback will start from the last Flink 1.x snapshot.
Important Terraform considerations:
Auto-rollback and the RollbackApplication API aren’t directly exposed as Terraform resource attributes. If you need auto-rollback during the upgrade, enable it using the AWS CLI (Step 3) before running terraform apply, or use a provisioner/null_resource to call the CLI.
Always take a manual snapshot (Step 4) before running terraform apply for the upgrade. Terraform doesn’t automatically snapshot before updating the runtime.
Step 6: Monitor the upgrade
After initiating the upgrade, monitor the application to verify that it completes successfully.
Check application status:
The application should transition through RUNNING → UPDATING → RUNNING. Confirm the runtime version changed to 2.2:
During rollback, the application stops, reverts to the previous Flink version and application code, and restarts from the snapshot taken before the upgrade.
Important: You can’t restore a Flink 2.2 snapshot on Flink 1.x. Rollback uses the snapshot taken before the upgrade. This is why Steps 3 and 4 are critical.
If you’re evaluating Flink 2.2 features: Launch a new application on the Flink 2.2 runtime to explore SQL/ML capabilities, the VARIANT data type, and the new join operators. See the Amazon Managed Service for Apache Flink sample applications on GitHub for reference architectures.
With Apache Flink 2.2 on Amazon Managed Service for Apache Flink, you get a modern Java 17 runtime, SQL-native AI/ML inference, improved state management performance, and a streamlined API surface. In-place upgrades with state preservation and auto-rollback make the migration straightforward. Test on a replica, follow the steps in this post, and start building on Flink 2.2.
Data strategy can use geospatial data to provide organizations with insights for decision-making and operational optimization. By incorporating geospatial data (such as GPS coordinates, points, polygons and geographic boundaries), businesses can uncover patterns, trends, and relationships that might otherwise remain hidden across multiple industries, from aviation and transportation to environmental studies and urban planning. Processing and analyzing this geospatial data at scale can be challenging, especially when dealing with billions of daily observations.
In this post, we explore how to use Apache Sedona with AWS Glue to process and analyze massive geospatial datasets.
Introduction to geospatial data
Geospatial data is information that has a geographic component. It describes objects, events, or phenomena along with their location on the Earth’s surface. This data includes coordinates (latitude and longitude), shapes (points, lines, polygons), and associated attributes (such as the name of a city or the type of road).
Key types of geospatial geometries (and examples of each in parentheses) include:
Point – Represents a single coordinate (a weather station).
MultiPoint – A collection of points (bus stops in a city).
LineString – A series of points connected in a line (a river or a flight path).
MultiPolygon – Multiple polygons (national parks in a country).
Geospatial datasets come in different formats, each designed to store and represent different types of geographic information. Common formats for geospatial data are vector formats (Shapefile, GeoJSON), raster formats (GeoTIFF, ESRI Grid), GPS formats (GPX, NMEA), web formats (WMS, GeoRSS) among others.
Core concepts of Apache Sedona
Apache Sedona is an open-source computing framework for processing large-scale geospatial data. Built on top of Apache Spark, Sedona extends Spark’s capabilities to handle spatial operations efficiently. At its core, Sedona introduces several key concepts that enable distributed spatial processing. These include Spatial Resilient Distributed Datasets (SRDDs), which allow for the distribution of spatial data across a cluster, and Spatial SQL, which provides a familiar SQL-like interface for spatial queries. Some of the core capabilities of Apache Sedona are:
Efficient spatial data types like points, lines and polygons.
Spatial operations and functions such as ST_Contains (check if point is inside of a polygon), ST_Intersects (check if point is inside of a polygon), ST_H3CellIDs (geospatial indexing system developed by Uber, return the H3 cell ID(s) that contain the given point at the specified resolution).
Spatial joins to combine different spatial datasets.
Integration with Spark SQL (geospatial functions to run spatial SQL queries).
Spatial indexing techniques, such as quad-trees and R-trees, to optimize query performance.
For more information about the functions available in Apache Sedona, visit the official Sedona Functions documentation.
Use case
This use case consists of a global air traffic visualization and analysis platform that processes and displays real-time or historical aircraft tracking data on an interactive world map. Using unique aircraft identifiers from the International Civic Aviation Organization (ICAO), the system ingests trajectory records containing information such as geographic position (latitude and longitude), altitude, speed, and flight direction, then transforms this raw data into two complementary visual layers. The Flight Tracks Layer plots the routes traveled by each aircraft individually, allowing for the analysis of specific trajectories and navigation patterns. The Flight Density Layer uses hexagonal spatial indexing (H3) to aggregate and identify regions of higher air traffic concentration worldwide, revealing busy air corridors, aviation hubs, and high-density flight zones.
The dataset used for this use case is historical flight tracker data from ADSB.lol. ADSB.lol provides unfiltered flight tracker with a focus on open data. Data is also freely available via the API. The data contains a file per aircraft, a JSON gzip file containing the data for that aircraft for the day.
This is a JSON trace file format sample:
{
icao: "0123ac", // hex id of the aircraft
timestamp: 1609275898.495, // unix timestamp in seconds since epoch (1970)
trace: [
[ seconds after timestamp,
lat,
lon,
altitude in ft or "ground" or null,
ground speed in knots or null,
track in degrees or null, (if altitude == "ground", this will be true heading instead of track)
flags as a bitfield: (use bitwise and to extract data)
(flags & 1 > 0): position is stale (no position received for 20 seconds before this one)
(flags & 2 > 0): start of a new leg (tries to detect a separation point between landing and takeoff that separates flights)
(flags & 4 > 0): vertical rate is geometric and not barometric
(flags & 8 > 0): altitude is geometric and not barometric
,
vertical rate in fpm or null,
aircraft object with extra details or null,
type / source of this position or null,
geometric altitude or null,
geometric vertical rate or null,
indicated airspeed or null,
roll angle or null
],
]
}
For this use case, this is a simplified schema of the dataset after processing:
icao - Unique aircraft identifier
timestamp - Epoch timestamp of the observation (converted to readable format)
trace.lat / trace.lon - Latitude and longitude of the aircraft
trace.altitude - Aircraft altitude
trace.ground_speed - Ground speed
geometry - Geospatial geometry of the observation point (Point)
Solution overview
This solution enables aircraft tracking and analysis. The data can be visualized on maps and used for aviation management and safety applications. The process begins with data acquisition, extracting the compressed JSON files from TAR archives, then transforms this raw data into geospatial objects, aggregating them into H3 cells for efficient analysis. The processed data schema includes ICAO aircraft identifiers, timestamps, latitude/longitude coordinates, and derived fields such as H3 cell identifiers and point counts per cell. This structure allows detailed tracking of individual flights and aggregate analysis of traffic patterns. For visualization, you can generate density maps using the H3 grid system and create visual representations of individual flight tracks. The architecture data flow is as follows:
Data ingestion – Aircraft observation data stored as JSON compressed files in Amazon Simple Storage Service (Amazon S3).
Data processing – AWS Glue jobs using Apache Sedona for geospatial processing.
Data visualization – Spark SQL with Sedona’s spatial functions to extract insights and export data to visualize the information in a map on Kepler.gl.
The following figure illustrates this solution.
Prerequisites
You will need the following for this solution:
An AWS Account and a user with AWS Console access.
An IAM role for AWS Glue with list, read, and write permissions for Amazon S3 buckets.
An Amazon S3 Bucket for flight files. For this example, name the bucket blog-sedona-nessie-<account_number>-<aws_region>, using your account number and region.
An Amazon S3 bucket for artifacts and Sedona libraries. For this example, name the bucket blog-sedona-artifacts-<account_number>-<aws_region>, using your account number and region.
Download the AWS Glue script from AWS Sample to process the geospatial data.
Review the AWS Glue security best practices, especially IAM least-privilege, encryption for sensitive data at rest and in transit, and configuring VPC Endpoints to prevent data from routing through the public internet.
Solution walkthrough
From now on, executing the next steps will incur costs on AWS. This step-by-step walkthrough demonstrates an approach to processing and analyzing large-scale geospatial flight data using Apache Sedona and Uber’s H3 spatial indexing system, using AWS Glue for distributed processing and Apache Sedona for efficient geospatial computations. It explains how to ingest raw flight data, transform it using Sedona’s geospatial functions, and index it with H3 for optimized spatial queries. Finally, it also demonstrates how to visualize the data using Kepler.gl. For data processing, it is possible to use both Glue scripts and Glue notebooks. In this post, we will focus only on Glue scripts.
Upload the Apache Sedona libraries to Amazon S3
Open your OS terminal command line.
Create a folder to download the Sedona libraries and name it jar.
# Create a directory for the Sedona libraries (JARs files)
mkdir jar
# Go to the folder JARs folder
cd jar
# Download the geospatial flight files in the folder created
wget https://github.com/adsblol/globe_history_2025/releases/download/v2025.05.29-planes-readsb-prod-0tmp/v2025.05.29-planes-readsb-prod-0tmp.tar.aa
wget https://github.com/adsblol/globe_history_2025/releases/download/v2025.05.29-planes-readsb-prod-0tmp/v2025.05.29-planes-readsb-prod-0tmp.tar.ab
Extract the flight files.
# Combine the two the tar files together
cat v2025.05.29* >> combined.tar
# Extract the json flight files from the tar file
tar xf combined.tar
Copy the flight files to Amazon S3. In this case, we are using the S3 folder: s3://blog-sedona-nessie-<account_number>-<aws_region>/raw/adsb-2025-05-28/traces/.
# Copy the json flight files to Amazon S3
aws s3 cp ./traces/ s3://blog-sedona-nessie-<account_number>-<aws_region>/raw/adsb-2025-05-28/traces/ --recursive
Your Amazon S3 folder should now look similar to the following image.
Create an AWS Glue job and set up the job
Now, we are ready to define the AWS Glue job using Apache Sedona to read the geospatial data files. To create a Glue job:
On the Script screen, for the engine, choose Spark, then select the option Upload script.
Choose Choose file. Find the process_sedona_geo_track.py file, then choose Create script.
Rename the job from Untitled to process_sedona_geo_track.
Choose Save.
Now, let’s set up the AWS Glue job. Choose Job Details.
Choose the IAM Role created to be used with Glue. For this example, we use blog-glue.
Set the Glue version to Glue 5.0 and the Worker type as needed. For this example, G.1X is sufficient, but we use G.2X to speed up processing.
Now, let’s import the libraries for Apache Sedona.
In the Dependent JARs path, type the path of the JAR files for Apache Sedona that you uploaded in the preceding steps. For this example, we used s3://blog-sedona-artifacts-<account_number>-<aws_region>/jar/sedona-spark-shaded-3.5_2.12-1.7.1.jar,s3://blog-sedona-artifacts-<account_number>-<aws_region>/jar/geotools-wrapper-1.7.1-28.5.jar
In Additional Python modules path, enter the modules for Apache Sedona: apache-sedona==1.7.1,geopandas==0.13.2,shapely==2.0.1,pyproj==3.6.0,fiona==1.9.5,rtree==1.2.0
In the Job parameters section, in the Key field, type —BUCKET_NAME. For its Value, enter your bucket name. In this example, ours is blog-sedona-nessie-<account_number>-<aws_region>.
Choose Save.
Processing the geospatial flights data
Before we run the job, let’s understand how the code works. First, import the Apache Sedona libraries:
import json
import gzip
from sedona.spark import SedonaContext
Next, initialize the Sedona context using an existing Spark session:
sedona = SedonaContext.create(spark)
After that, create a function for handling compressed JSON data:
def parse_gzip_json(byte_content):
try:
decompressed = gzip.decompress(byte_content)
return json.loads(decompressed.decode('utf-8'))
except Exception as e:
print(f"Error during gzip parse: {str(e)}")
return None
Add a function to transform raw tracking data into a structured format suitable for a valid coordinates process:
def flatten_records(json_obj):
records = []
if "trace" in json_obj and isinstance(json_obj["trace"], list):
for point in json_obj["trace"]:
if len(point) >= 3:
lat, lon = float(point[1]), float(point[2])
if -90 <= lat <= 90 and -180 <= lon <= 180:
records.append(Row(
icao=json_obj.get("icao", None),
timestamp=json_obj.get("timestamp", None),
lat=lat,
lon=lon
))
return records
The flat_rdd variable applies these functions to the structured data from the original gzipped JSON. Each element in this RDD is a Row object representing a single data point from an aircraft’s trace, with fields for ICAO, timestamp, latitude, and longitude.
flat_rdd = raw_rdd.map(lambda x: parse_gzip_json(x[1])).filter(lambda x: x is not None).flatMap(flatten_records)
The ADSB trace files contain a deeply nested JSON structure where the trace field holds an array of mixed-type arrays, compressed in Gzip format. For this specific case, developing a UDF represented one of the most practical and efficient solutions. Since Gzip is a non-splittable format, Spark is unable to parallelize processing, constraining both methods to a single worker per file and processing the data multiple times across JVM decompression, full JSON parsing, and subsequent re-parsing operations. The UDF bypasses all of this by reading raw bytes and doing everything in a single Python pass: decompress → parse → extract → validate, returning only the small set of needed fields directly to Spark.
The Spark SQL query processes geographic trace data using the H3 hexagonal grid system, converting point data into a regularized hexagonal grid that can help identify areas of high point density. A resolution of 5 was adopted, producing hexagons of approximately 253 km² (roughly the same size as the city of Edinburgh, Scotland, which is approximately 264 km²), for its ability to effectively capture route density patterns at the city and metropolitan level.
h3_traces_df = spark.sql("""
WITH base_h3 AS (
SELECT
ST_H3CellIDs(geometry, 5, false)[0] AS h3_index,
lat,
lon
FROM traces
)
SELECT
COUNT(*) AS num, -- Count points in each H3 cell
h3_index,
AVG(lon) AS center_lon,
AVG(lat) AS center_lat
FROM base_h3
GROUP BY h3_index
""")
Finally, this code prepares the datasets for visualization purposes. The first dataset is based on the aircraft unique identifier. The complete dataset for a single day can contain more than 80 million data points. A random sampling rate of 0.1% was applied, which proves sufficient to illustrate route density patterns without overwhelming the Kepler.gl browser renderer. The second dataset aggregates trace points into hexagonal spatial cells (result from the query above).
On the ETL jobs >> Notebooks page, choose the job name process_sedona_geo_track.
Choose Run.
Now, it is possible to monitor the job by choosing the Runs tab.
It may take a few minutes to run the entire job. It took nearly 8 minutes to process approximately 2.50 GB (67,540 compressed files) with 20 DPUs. After the job is processed, you should see your job with the status Succeeded.
Now your data should be saved for a preview visualization demo in a folder named s3://blog-sedona-nessie-<account_number>-<aws_region>/visualization/.
Performance insights
The workload characterization of this job reveals a CPU-intensive profile, primarily because of the processing of small binary files with GZIP compression and subsequent JSON parsing. Given the inherent nature of this pipeline, which includes Python UDF serialization and partial single-partition write stages, linear scaling does not yield proportional performance gains. The following table presents an analysis of AWS Glue configurations, evaluating the trade-off between computational capacity, execution duration, and associated costs:
Duration
Capacity (DPUs)
Worker type
Glue version
Estimated Cost*
10 m 7 s
32 DPUs
G.1X
5
$2.34
11 m 50 s
10 DPUs
G.1X
5
$0.88
19 m 7 s
4 DPUs
G.1X
5
$0.59
8 m 19 s
20 DPUs
G.2X
5
$1.32
*Estimated Cost = DPUs x Duration (hours) x $0.44 per DPU-hour (us-east-1)
Visualizing and analyzing geospatial data with Kepler.gl
Kepler.gl is an open-source geospatial analysis tool developed by Uber with code available at Github. Kepler.gl is designed for large-scale data exploration and visualization, offering multiple map layers, including point, arc, heatmap, and 3D hexagon. It supports various file formats like CSV, GeoJSON, and KML. In this use case, we will use Kepler.gl to present interactive visualizations that illustrate flight patterns, routes, and densities across global airspace.
Downloading the geospatial files
Before we can view the graph, we will need to download the flight files to our local machine, unzip them, and rename them (to make it easier to identify the files).
Open your OS terminal command line.
Create the folders to download the data processed in the steps before. In this case, we create kepler and kepler_csv.
#create kepler folders: first folder is to download the files,
#second folder is to organize the files to use in the next step
mkdir kepler
mkdir kepler_csv
Replace the bracketed variables with your account and directory information, then download all the CSV files.
#copy the files from Amazon S3 to local machine
aws s3 cp s3://blog-sedona-nessie-<account_number>-<aws_region>/visualization/ /<user_directory>/kepler --recursive
Extract the files, rename them, and move them to another folder.
# Extract the files processed by Spark and Sedona
gzip -d ./kepler/kepler_h3_density/*.gz
gzip -d ./kepler/kepler_track_points_sample/*.gz
# Rename the Spark output files to more readable names
cd ./kepler/kepler_h3_density/
ls
mv part-00000-*.csv kepler_h3_density.csv
cd ..
cd ./kepler/kepler_track_points_sample/
ls
mv part-00000-*.csv kepler_track_points_sample.csv
cd ..
# Ensure the output folder exists
mkdir -p ../kepler_csv
# Copy the renamed CSV files to the folder that will be used as input in kepler.gl
cp ./kepler/kepler_h3_density/*.csv ../kepler_csv
cp ./kepler/kepler_track_points_sample/*.csv ../kepler_csv
Your kepler_csv folder should look similar to the return of the command below.
#list the files in the kepler_csv directory
ls -l
total 11684
-rw-rw-r-- 1 ec2-user ec2-user 8630110 Jun 12 14:47 kepler_h3_density.csv
-rw-rw-r-- 1 ec2-user ec2-user 3331763 Jun 12 14:47 kepler_track_points_sample.csv
Visualizing the data in a graph
Now that you have saved the data to your local machine, you can analyze the flight data through interactive map graphics. To import the data into the Kepler.gl web visualization tool:
Drag and drop both CSV files (flight_points and h3_density) into the upload area.
Confirm that both datasets are loaded successfully.
Delete all layers.
Create the Flight Density Layer:
Choose Add Layer in the left panel.
In Basic, choose H3 as the layer type, then add the following configuration:
Layer Name: Flight Density
Data Source: kepler_h3_density.csv
Hex ID: h3_index
In the Fill Color section:
Color: point_count
Color Scale: Quantile.
Color Range: Choose a blue/green gradient.
Set Opacity to 0.7.
In the Coverage section, set it to 0.9.
Create the Flight Tracks Layer:
Choose Add Layer in the left panel.
In Basic, choose Point as the layer type, then add the following configuration:
Layer Name: Flight Tracks
Data Source: kepler_track_points_sample.csv
Columns:
Latitude: lat
Longitude: lon
In the Fill Color section:
Solid Color: Orange
Opacity: 0.3
Set the Point’s Radius to 1
The layers should look similar to the following figure.
The graph visualization should now show flight density through color-coded hexagons, with individual flight tracks visible as orange points:
There you go! Now that you have knowledge about geospatial data and have created your first use case, take the opportunity to do some analysis and learn some interesting facts about flight patterns.
It is possible to experiment with other interesting types of analysis in Kepler.gl, such as Time Playback.
Clean up
To clean up your resources, complete the following tasks:
Delete the AWS Glue job process_sedona_geo_track.
Delete content from the Amazon S3 buckets: blog-sedona-artifacts-<account_number>-<aws_region> and blog-sedona-nessie-<account_number>-<aws_region>.
Conclusion
In this post, we showed how processing geospatial data can present significant challenges due to its complex nature (from big data to data structure format). For this use case of flight trackers, it involves vast amounts of information across multiple dimensions such as time, location, altitude, and flight paths, however, the combination of Spark’s distributed computing capabilities and Sedona’s optimized geospatial functions helps overcome those challenges. The spatial partitioning and indexing features of Sedona, coupled with Spark’s framework, enable us to perform complex spatial joins and proximity analyses efficiently, simplifying the overall data processing workflow.
The serverless nature of AWS Glue eliminates the need for managing infrastructure while automatically scaling resources based on workload demands, making it an ideal platform for processing growing volumes of flight data. As the volume of flight data grows or as processing requirements fluctuate, with AWS Glue, you can quickly adjust resources to meet demand, ensuring optimal performance without the need for cluster management.
By converting the processed results into CSV format and visualizing them in Kepler.gl, it is possible to create interactive visualizations that reveal patterns in flight paths, and you can efficiently analyze air traffic patterns, routes, and other insights. This end-to-end solution demonstrates how a modern data strategy in AWS with the support of open-source tools can transform raw geospatial data into actionable insights.
Migrating Apache Kafka workloads to the cloud often involves managing complex replication infrastructure, coordinating application cutovers with extended downtime windows, and maintaining deep expertise in open-source tools like Apache Kafka’s MirrorMaker 2 (MM2). These challenges slow down migrations and increase operational risk. Amazon MSK Replicator addresses these challenges, enabling you to migrate your Kafka deployments (referred to as “external” Kafka clusters) to Amazon MSKExpress brokers with minimal operational overhead and reduced downtime. MSK Replicator supports data migration from Kafka deployments (version 2.8.1 or later) that have SASL/SCRAM authentication enabled – including Kafka clusters running on-premises, on AWS, or other cloud providers, as well as Kafka-protocol-compatible services like Confluent Platform, Avien, RedPanda, WarpStream, or AutoMQ when configured with SASL/SCRAM authentication.
In this post, we walk you through how to replicate Apache Kafka data from your external Apache Kafka deployments to Amazon MSK Express brokers using MSK Replicator. You will learn how to configure authentication on your external cluster, establish network connectivity, set up bidirectional replication, and monitor replication health to achieve a low-downtime migration.
How it works
MSK Replicator is a fully managed serverless service that replicates topics, configurations, and offsets from cluster to cluster. It alleviates the need to manage complex infrastructure or configure open-source tools.
Before MSK Replicator, customers used tools like MM2 for migrations. These tools lack bi-directional topic replication when using the same topic names, creating complex application architectures to consume different topics on different clusters. Custom replication policies in MM2 can allow identical topic names, but MM2 still lacks bidirectional offset replication because the MM2 architecture requires producers and consumers to run on the same cluster to replicate offsets. This created complex migrations that required either migrating consumers before producers or big-bang migrations migrating all applications at once. When customers run into issues during the migration, the rollback process is error-prone and introduces large amounts of duplicate message processing due to the lack of consumer group offset synchronization. These approaches create risk and complexity for customers that make migrations difficult to manage.
MSK Replicator addresses these problems by supporting bidirectional replication of data and enhanced consumer group offset synchronization. MSK Replicator copies topics and offsets from an external Kafka cluster to MSK, allowing you to preserve the same topic and consumer group names on both clusters. MSK Replicator also supports creating a second Replicator instance for bidirectional replication of both data and enhanced offset synchronization, allowing producers and consumers to run independently on different Kafka clusters. Data published or consumed on the Amazon MSK cluster will be replicated back to the external cluster by the second Replicator. This feature works when producers and consumers are migrated regardless of order without worrying about dependencies between applications.
Because MSK Replicator provides bidirectional data replication and enhanced consumer group offset synchronization, you can move producers and consumers at your own pace without data loss. This reduces migration complexity, allowing you to migrate applications between your external Kafka cluster and Amazon MSK regardless of order. If you run into problems during the migration, enhanced offset synchronization allows you to roll back changes by moving applications back to the external Kafka cluster, where they restart from the latest checkpoint from the Amazon MSK cluster.
For example, consider three applications:
The “Orders” application, which accepts incoming orders and writes them to the orders Kafka topic
The “Order status” application, which reads from the “orders” Kafka topic and writes status updates to the order_status topic
The “Customer notification” application, which reads from the order_status topic and notifies customers when status changes
MSK Replicator enables these applications to be migrated between an on-premises Apache Kafka cluster and an Amazon MSK Express cluster with low downtime and no data loss, regardless of order. The “Order status” application can migrate first, receive orders from the on-premises “Orders” application, and send status updates to the on-premises “Customer notification” application. If issues arise during the migration, the “Order status” application can roll back to the on-premises cluster and its consumer group offsets for the orders topic will be ready for it to pick up from where it left off on the Amazon MSK cluster.
MSK Replicator supports data distribution across hybrid and multi-cloud environments for analytics, compliance, and business continuity. It is also configured for disaster recovery scenarios where Amazon MSK Express serves as a resilient target for your external Kafka clusters.
MSK Replicator supports Kafka deployments running version 2.8.1 or later as a source, including 3rd party managed Kafka services, self-managed Kafka, and on-premises or third-party cloud-hosted Kafka. MSK Replicator automatically handles data transfer, uses SASL/SCRAM authentication with SSL encryption, and maintains consumer group positions across both clusters. If you do not use SASL/SCRAM today, this can be configured as a new listener used for MSK Replicator allowing current clients to use their existing authentication mechanisms alongside MSK Replicator.
Prerequisites
To follow along with this walkthrough, you need the following resources in place:
AWS Secrets Manager configured to store your SASL/SCRAM credentials for the external cluster so that MSK Replicator can securely retrieve them at runtime
You can set up network connectivity between your external Kafka cluster and your AWS VPC using methods such as AWS Direct Connect for dedicated network connections, AWS Site-to-Site VPN for encrypted connections over the internet, and AWS VPC peering or AWS Transit Gateway for connections between AWS VPCs. Verify that IP routing and DNS resolution are properly configured between your external cluster and AWS.
To verify IP routing and DNS resolution, connect to your external Kafka cluster from inside of your VPC by using the Kafka CLI to list topics on the external cluster. If you can list topics from your VPC using the Kafka CLI, this means DNS resolution and IP routing are working successfully. If it fails, work with your network admins to troubleshoot network connectivity issues.
Step 2: Configure external cluster
In this step, you will set up authentication on your external Kafka cluster and store the credentials in AWS Secrets Manager so that MSK Replicator can connect securely.
Configure authentication
Using the external cluster admin user, configure SASL/SCRAM authentication for MSK Replicator using SHA-256 or 512 on your external Kafka cluster. Create a SASL/SCRAM user for MSK Replicator and give the user the following ACL permissions:
AWS Secrets Manager stores your SASL/SCRAM credentials securely so that MSK Replicator can retrieve them at runtime. The secret must use JSON format and have the following keys:
username – The SCRAM username that you configured in the authentication step above
password – The SCRAM password that you configured in the authentication step above
certificate – The public root CA certificate (the top-level certificate authority that issued your cluster’s TLS certificate) and the intermediate CA chain (intermediate certificates between the root and your cluster’s certificate), used for SSL handshakes with the external cluster
Optionally, you may create separate secrets for SCRAM credentials and the SSL certificate. This approach is useful when secrets for SCRAM credentials and certificates are provisioned in different stages, such as in Infrastructure as Code (IaC) pipelines.
Retrieve the cluster ID
As the admin user, use the Kafka CLI tools to retrieve the cluster ID of your external cluster. Run the following command, replacing your-broker-host:9096 with the address of one of your external cluster’s bootstrap servers:
The command returns a cluster ID string such as lkc-abc123. Take note of this value because you will need it when creating the replicator in Step 4.
Step 3: Create your MSK Express target cluster
With your external cluster configured, you can now set up the target. Create an Amazon MSK Express cluster with IAM authentication enabled. Make sure that the cluster is in subnets that have access to AWS Secrets Manager endpoints. See Get started using Amazon MSK for more information on creating an MSK cluster.
Step 4: Create the replicator
Now that both clusters are ready, you can connect them by setting up the MSK Replicator with the appropriate IAM role and replication configuration.
Set up an IAM role for MSK Replicator
MSK Replicator needs an IAM role to interact with your MSK Express cluster and retrieve secrets. Set up a service execution IAM role with a trust policy allowing kafka.amazonaws.com and attach the AWSMSKReplicatorExecutionRole permissions policy. Take note of the role ARN for creating the replicator.
Create and attach a policy for accessing your Secrets Manager secrets and reading/writing data in your MSK cluster. See Creating roles and attaching policies (console) for more information on creating IAM roles and policies.
The following is an example policy for reading and writing data to your MSK cluster and reading KMS-encrypted Secrets Manager secrets:
The kafka-clusters.json file defines the source and target Kafka cluster connection information, replication-info.json specifies which topics to replicate and how to handle consumer group offset synchronization, and log-delivery.json specifies the CloudWatch logging configuration. The following tables describe the required parameters:
CLI inputs:
CLI Parameter
Description
Example
replicator-name
The name of the replicator
external-to-msk
service-execution-role-arn
The ARN for the service execution IAM role you created
arn:aws:iam::123456789012:role/MSKReplicatorRole
kafka-clusters
The Kafka cluster connection info
See below
replication-info-list
The replication configuration
See below
log-delivery
The logging configuration
See below
Key kafka-clusters.json inputs:
CLI Parameter
Description
Example
ApacheKafkaClusterId
The cluster ID retrieved in Step 2
lkc-abc123
RootCaCertificate
The Secrets Manager ARN containing the public CA certificate and intermediate CA chain
The list of topics to replicate (use [“.*”] for all topics)
[“my-topic”]
ConsumerGroupsToReplicate
The list of consumer groups to replicate
[“my-group”]
StartingPosition
The point in the Kafka topics to begin replication from (either EARLIEST or LATEST)
EARLIEST
ConsumerGroupOffsetSyncMode
Whether or not to use enhanced bidirectional consumer group offset synchronization
ENHANCED
Note that startingPosition is set to EARLIEST in the configuration below, which means the replicator begins reading from the oldest available offset on each topic. This is the recommended setting for migrations to avoid data loss.
Key log-delivery.json inputs:
CLI Parameter
Description
Example
Enabled
Allows you to enable CloudWatch logging
true
LogGroup
The CloudWatch logs log group name to log to
/msk/replicator/my-replicator
Additional log delivery methods for Amazon S3 and Amazon Data Firehose are supported. In this post, we use CloudWatch logging.
The configs should look like the following for external to MSK replication.
Configure bidirectional replication from MSK to the external cluster
To enable bidirectional replication, create a second replicator that replicates in the opposite direction. Use the same IAM role and network configuration from Step 4, but swap the source and target. Replace SourceKafkaClusterId with TargetKafkaClusterId and TargetKafkaClusterArn with SourceKafkaClusterArn in a new msk-to-external-replication-info.json file:
Monitor your replication using Amazon CloudWatch metrics. Three key metrics to understand are MessageLag, SumOffsetLag, and ReplicationLatency. MessageLag measures how far behind the replicator is from the external cluster in terms of messages not yet replicated, while SumOffsetLag measures how far behind a consumer group is from the latest message in a topic. ReplicationLatency is the amount of latency between the source and target clusters in data replication. When the three reach a sustained low level, your clusters are fully synchronized for both data and consumer group offsets.
To troubleshoot MSK Replicator replication or errors, use the CloudWatch logs to get more details about the health of the replicator. MSK Replicator logs status and troubleshooting information which can be helpful in diagnosing issues like connectivity, authentication, and SSL errors.
Note that the replication is asynchronous, so there will be some lag during replication. The lag will reach zero once a client is shut down during migration to the target cluster. This takes about 30 seconds under normal operations, allowing a low downtime migration without data loss. If your lag is continually increasing or does not reach a sustained low level, this indicates that you have insufficient partitions for high-throughput replication. Refer to Troubleshoot MSK Replicator for more information on troubleshooting replication throughput and lag.
Key metrics include:
MessageLag – Monitors the sync between the MSK Replicator and the source cluster. MessageLag indicates the lag between the messages produced to the source cluster and messages consumed by the replicator. It is not the lag between the source and target cluster.
ReplicationLatency – Time taken for records to replicate from source to target cluster (ms)
ReplicatorThroughput – Average number of bytes replicated per second
ReplicatorFailure – Number of failures the replicator is experiencing
Your applications are ready to migrate when the following conditions are met. For most workloads, you should expect these metrics to stabilize within a few hours of starting replication. High-throughput clusters may take longer depending on topic volume and partition count.
ReplicatorFailure = 0
ConsumerGroupOffsetSyncFailure = 0
KafkaClusterPingSuccessCount = 1 for both source and target clusters
MessageLag < 1,000
Your sustained lag may be lower or higher depending on your throughput per partition, message size, and other factors
Sustained high message lag usually indicates insufficient partitions for high-throughput replication
ReplicationLatency < 90 seconds
Your sustained latency may be lower or higher depending on your throughput per partition, message size, and other factors
Sustained high latency usually indicates insufficient partitions for high-throughput replication
SumOffsetLag is at a sustained low level on both clusters
Offset values on the two clusters may not be numerically identical.
MSK Replicator translates offsets between clusters so that consumers resume from the correct position, but the raw offset numbers can differ due to how offset translation works. What matters is that SumOffsetLag is at a sustained low level.
If ConsumerGroupCount is zero or does not match the expected count, then there is an issue in the Replicator configuration or a permissions issue preventing consumer group synchronization
Migrating your applications
With bidirectional consumer offset synchronization, you can migrate your producers and consumers regardless of order. Start by monitoring replication metrics until they reach the target values described in the previous section. Then migrate your applications (producers or consumers) to use the MSK Express cluster endpoints and verify that they are producing and consuming as expected. If you encounter issues, you can roll back by switching applications back to the external cluster. The consumer offset synchronization makes sure that your applications resume from their last committed position regardless of which cluster they connect to.
For a comprehensive, hands-on walkthrough of the end-to-end migration process, explore the MSK Migration Workshop, which provides step-by-step guidance for migrating your Kafka workloads to Amazon MSK.
Security considerations
MSK Replicator uses SASL/SCRAM authentication with SSL encryption for secure data transfer between your external cluster and AWS. The solution supports both publicly trusted certificates and private or self-signed certificates. Credentials are stored securely in AWS Secrets Manager, and the target MSK Express cluster uses IAM authentication for access control.
When configuring security, keep the following in mind:
Make sure that the IAM role you create in Step 4 follows the principle of least privileges. Only attach AWSMSKReplicatorExecutionRole and an IAM policy for Secrets Manager with least-privileges access to read secret values and avoid adding broader permissions.
Verify that your Secrets Manager secret is encrypted with an AWS KMS key that the MSK Replicator service execution role has permission to decrypt.
Confirm that the security groups assigned to MSK Replicator allow outbound traffic to your external cluster’s broker ports (typically 9096 for SASL/SCRAM with TLS) and to the MSK Express cluster.
Rotate your SASL/SCRAM credentials periodically and update the corresponding Secrets Manager secret. MSK Replicator picks up the new credentials automatically on the next connection attempt.
Under the AWS shared responsibility model, AWS is responsible for securing the underlying infrastructure that runs MSK Replicator, including the compute, storage, and networking resources. You are responsible for configuring authentication mechanisms (SASL/SCRAM), managing credentials in AWS Secrets Manager, configuring network security (security groups and VPC settings), implementing IAM policies following least privilege, and rotating credentials. For more information, see Security in Amazon MSK in the Amazon MSK Developer Guide.
Cleanup
To avoid ongoing charges, delete the resources you created during this walkthrough. Start by deleting the replicators first, because they depend on the other resources:
After both replicators are deleted, you can remove the following resources if they were created solely for this walkthrough:
The MSK Express cluster (deleting a cluster also removes its stored data, so verify that your applications have fully migrated before proceeding)
The Secrets Manager secrets containing your SASL/SCRAM credentials and certificates
The IAM role and policies created for MSK Replicator
You can verify that a replicator has been fully deleted by running aws kafka list-replicators and confirming it no longer appears in the output.
Conclusion
Amazon MSK Replicator simplifies the process of migrating to Amazon MSK Express brokers and establishes hybrid Kafka architectures. The fully managed service alleviates the operational complexity of managing replication while bidirectional consumer offset synchronization enables flexible, low-risk application migration.
Next Steps
To get started using MSK Replicator to migrate applications to MSK Express brokers, use the MSK Migration Workshop for a hands-on, end-to-end migration walkthrough. The Amazon MSK Replicator documentation includes detailed configuration details to help configure MSK Replicator for your use case. From there, use MSK Replicator to migrate your Apache Kafka workloads to MSK Express broker.
Once your migration is complete, consider exploring multi-region replication patterns for disaster recovery, or integrating your MSK Express cluster with AWS analytics services such as Amazon Data Firehose and Amazon Athena. If you need help planning your migration, reach out to your AWS account team, AWS Support or AWS Professional Services.
Amazon Redshift now supports DELETE, UPDATE, and MERGE operations for Apache Iceberg tables stored in Amazon S3 and Amazon S3 table buckets. With these operations, you can modify data at the row level, implement upsert patterns, and manage the data lifecycle while maintaining transactional consistency using familiar SQL syntax. You can run complex transformations in Amazon Redshift and write results to Apache Iceberg tables that other analytics engines like Amazon EMR or Amazon Athena can immediately query.
In this post, you work with customer and orders datasets that were created and used in the previously mentioned post to demonstrate these capabilities in a data synchronization scenario.
Solution overview
This solution demonstrates DELETE, UPDATE, and MERGE operations for Apache Iceberg tables in Amazon Redshift using a common data synchronization pattern: maintaining customer records and orders data across staging and production tables. The workflow includes three key operations:
DELETE – Remove customer records based on opt-out requests
UPDATE – Modify existing customer information
MERGE – Synchronize order data between staging and production tables using upsert patterns
Figure 1: solution overview
The solution uses a staging table (orders_stg) stored in an S3 table bucket for incoming data and reference tables (customer_opt_out) in Amazon Redshift for managing data lifecycle operations. With this architecture, you can process changes efficiently while maintaining ACID compliance across both storage types.
Set up the required IAM role (RedshifticebergRole) with appropriate permissions
Create an Amazon S3 bucket and S3 Table bucket
Configure AWS Glue Data Catalog database and setting up access
Set up AWS Lake Formation permissions
Create the customer Apache Iceberg table in Amazon S3 standard buckets with sample customer data
Create the orders Apache Iceberg table in Amazon S3 Table buckets with sample order data
Amazon Redshift data warehouse on p200 version or higher
Data preparation
In this section, you set up the sample data needed to demonstrate MERGE, UPDATE, and DELETE operations. To prepare your data, complete the following steps:
You can now use the orders_stg and customer_opt_out tables to demonstrate data manipulation operations on the orders and customer tables created in the prerequisite section.
MERGE
MERGE conditionally inserts, updates, or deletes rows in a target table based on the results of a join with a source table. You can use MERGE to synchronize two tables by inserting, updating, or deleting rows in one table based on differences found in the other table.
To perform a MERGE operation:
Verify that the current data in the orders table for order IDs 1014, 1015, 1016, and 1017.You loaded this sample data in Part 1:
select * from "iceberg-write-blog@s3tablescatalog".iceberg_write_namespace.orders
where order_id in (1014,1015,1016,1017);
Figure 4: orders data for existing orders for orders in orders_stg
The orders table contains existing rows for order IDs 1014 and 1015.
Run the following MERGE operation using order_id as the key column to match rows between the orders and orders_stg tables:
MERGE INTO "iceberg-write-blog@s3tablescatalog".iceberg_write_namespace.orders
USING "iceberg-write-blog@s3tablescatalog".iceberg_write_namespace.orders_stg
ON orders.order_id = orders_stg.order_id
WHEN MATCHED THEN UPDATE
SET
customer_id = orders_stg.customer_id,
total_order_amt = orders_stg.total_order_amt,
total_order_tax_amt = orders_stg.total_order_tax_amt,
tax_pct = orders_stg.tax_pct,
order_date = orders_stg.order_date,
order_created_at_tz = orders_stg.order_created_at_tz,
is_active_ind = orders_stg.is_active_ind
WHEN NOT MATCHED THEN INSERT
VALUES
(orders_stg.customer_id,orders_stg.order_id,orders_stg.total_order_amt,orders_stg.total_order_tax_amt,orders_stg.tax_pct,orders_stg.order_date,orders_stg.order_created_at_tz,orders_stg.is_active_ind);
The operation updates existing rows (1014 and 1015) and inserts new rows for order IDs that don’t exist in the orders table (1016 and 1017).
Verify the updated data in the orders table:
select * from "iceberg-write-blog@s3tablescatalog".iceberg_write_namespace.orderswhere order_id in (1014,1015,1016,1017);
Figure 5: merged data on orders from orders_stg
The MERGE operation performs the following changes:
Updates existing rows – Order IDs 1014 and 1015 have updated total_order_amt and total_order_tax_amt values from the orders_stg table
Inserts new rows – Order IDs 1016 and 1017 are inserted because they don’t exist in the orders table
This demonstrates the upsert pattern, where MERGE conditionally updates or inserts rows based on the matching key column.
UPDATE
UPDATE modifies existing rows in a table based on specified conditions or values from another table.
Update the customer Apache Iceberg table using data from the customer_opt_out Amazon Redshift native table. The UPDATE operation uses the cust_rec_upd_ind column as a filter, updating only rows where the value is ‘Y’.
To perform an UPDATE operation:
Verify the current customer_name values for customer IDs 13 and 14 in customer_opt_out and customer (loaded this sample data in Part 1) tables:
select * from dev.public.customer_opt_out
where cust_rec_upd_ind = 'Y';
Figure 6: verify existing customer data for customers from customer_opt_out
select customer_id,customer_name from dev.demo_iceberg.customer
where customer_id in(13,14);
Figure 7: verify existing customer name for customers from customer_opt_out
Run the following UPDATE operation to modify customer names based on the cust_rec_upd_ind from customer_opt_out:
UPDATE dev.demo_iceberg.customerSET customer_name = customer_opt_out.customer_name
FROM dev.public.customer_opt_out
WHERE customer_opt_out.cust_rec_upd_ind = 'Y'and customer.customer_id = customer_opt_out.customer_id;
Verify the changes for customer IDs 13 and 14:
select customer_id,customer_name from dev.demo_iceberg.customer where customer_id in(13,14) order by 1;
Figure 8: updated customer names in customer table
The UPDATE operation modifies the customer_name values based on the join condition with the customer_opt_out table. Customer IDs 13 and 14 now have updated names (Customer13 Albon and Customer14 Oscar).
DELETE
DELETE removes rows from a table based on specified conditions. Without a WHERE clause, DELETE removes all the rows from table.
Delete rows from the customer Apache Iceberg table using data from the customer_opt_out Amazon Redshift native table. The DELETE operation uses the opt_out_ind column as a filter, removing only rows where the value is ‘Y’.
To perform a DELETE operation:
Verify the opt-out indicator data in the customer_opt_out table:
select * from dev.public.customer_opt_out
where opt_out_ind = 'Y';
Figure 9: verify customer records for opt out
Verify the current customer data for customer IDs 9 and 12:
select * from dev.demo_iceberg.customerwhere customer_id in(9,12);
Figure 10: verify existing customers data in customer table for opt out
Review the query execution plan:
EXPLAINDELETE FROM demo_iceberg.customerUSING public.customer_opt_out
WHERE customer.customer_id = customer_opt_out.customer_id
AND customer_opt_out.opt_out_ind = 'Y';
Figure 11: query plan for the DELETE query. The execution plan shows Amazon S3 scans for Apache Iceberg format tables, indicating that Amazon Redshift removes rows directly from the Amazon S3 bucket.
Run the following DELETE operation:
DELETE FROM demo_iceberg.customer
USING public.customer_opt_out
WHERE customer.customer_id = customer_opt_out.customer_id
AND customer_opt_out.opt_out_ind = 'Y';
Verify that the rows were removed:
select * from dev.demo_iceberg.customer where customer_id in(9,12);
Figure 12: result set from customer table for opt out customer after delete
The query returns no rows, confirming that customer IDs 9 and 12 were successfully deleted from the customer table.
Best practices
After performing multiple UPDATE or DELETE operations, consider running table maintenance to optimize read performance:
For AWS Glue tables – Use AWS Glue table optimizers. For more information, see Table optimizers in the AWS Glue Developer Guide.
For S3 Tables – Use S3 Tables maintenance operations. For more information, see S3 Tables maintenance in the Amazon S3 User Guide.
Table maintenance merges and compacts deletion files generated by Merge-on-Read operations, improving query performance for subsequent reads.
Conclusion
You can use Amazon Redshift support for DELETE, UPDATE, and MERGE operations on Apache Iceberg tables to build data architectures that combine warehouse performance with data lake scalability. You can modify data at the row level while maintaining ACID compliance, giving you the same flexibility with Apache Iceberg tables as you have with native Amazon Redshift tables.
In this post, we demonstrate how Notebooks in Amazon SageMaker Unified Studio help you get to insights faster by simplifying infrastructure configuration. You’ll see how to analyze housing price data, create scalable data tables, run distributed profiling, and train machine learning (ML) models within a single notebook environment.
Data scientists and analysts often spend days configuring infrastructure and managing authentication across multiple data sources before they can begin analysis. When working with data across Amazon Simple Storage Service (Amazon S3), Amazon Redshift, Snowflake, and local files, teams face repeated authentication setup, manual compute scaling decisions, and tool-switching overhead that delays insights.
Notebooks in Amazon SageMaker Unified Studio provide instant access to 12+ data sources, compute scaling from local to distributed processing, and AI-powered code generation within a single browser-based environment. You’ll learn to use polyglot programming, multi-engine compute, and AI-assisted development to accelerate your path from question to insight.
What are Notebooks in Amazon SageMaker Unified Studio?
Notebooks in Amazon SageMaker Unified Studio provide an interactive environment for data analysis, exploration, engineering, and machine learning workflows. It delivers five integrated capabilities:
Polyglot programming: Write code in Python and SQL interchangeably within the same notebook environment
Unified data access: Connect instantly to data stored in Amazon S3, AWS Glue Data Catalog, Apache Iceberg tables, and third-party sources like Snowflake and BigQuery
Native visualization: Create charts directly from Python and SQL results for immersive data analytics
AI-powered development: Generate code through natural language prompts using SageMaker Data Agent, with an intelligent chat interface for data analytics, data science, and ML tasks
Flexible compute: Scale from basic instances to GPU-powered environments as your needs grow
Architecture
This section covers the architecture of Notebooks, which delivers enterprise-scale analytics with browser-based simplicity through a cloud-native architecture that integrates multiple compute engines, diverse data sources, and AI-powered assistance.
Presentation layer
You access the notebook interface through Amazon SageMaker Unified Studio, interacting with a familiar interface featuring code cells for execution, markdown cells for documentation, and visualization cells for charts and tables.
Compute layer
A dedicated notebook server manages your kernel lifecycle and session state. Key components include a Language Server for code completion, a Python 3.11 runtime with pre-loaded data science libraries, and a Polyglot Kernel that handles your Python, PySpark, and SQL execution within the same notebook. Persistent Amazon Elastic Block Store (Amazon EBS) storage backs each notebook you create.
Execution layer
Notebooks support multiple execution engines, automatically routing your code to the optimal processing engine. In-memory execution handles your smaller datasets and rapid prototyping. Apache Spark via Amazon Athena provides distributed processing for your large-scale analytics via Spark Connect. Native connectivity to Amazon Athena (Trino), Amazon Redshift, Snowflake, and BigQuery processes your SQL queries.
Data Integration
You get unified access to 12+ data sources including AWS-native (Amazon S3, AWS Glue, Amazon Athena, Amazon Redshift) and third-party (Snowflake, BigQuery, PostgreSQL, MySQL) data sources. For the latest supported data sources, see Connect to data sources .
Security is embedded throughout the architecture to protect your work. Data access respects your AWS Identity and Access Management (AWS IAM) permissions. The notebook and the agent can only access data sources you’re authorized to use. Communication between components uses encrypted channels, and your notebook storage is encrypted at rest. The AI agent includes built-in guardrails to help prevent destructive operations and logs interactions for your compliance and auditing purposes.
Prerequisites
Before you begin, you need:
An AWS account with appropriate permissions to create Amazon SageMaker Unified Studio resources. See Set up IAM-based domains for complete permission requirements.
Basic familiarity with Python programming and SQL queries
Understanding of data analysis concepts and ML workflows
Access to the sample housing dataset (provided in the walkthrough)
You will be prompted either to select an existing AWS Identity and Access Management (AWS IAM) role that has access to your data and compute, or to create a new role. For this walkthrough, choose Create a new role and leave the other options at their defaults.
Choose Set up. It takes a few minutes to complete your environment.
Use case
In this post, you’ll use a Notebook and the SageMaker Data Agent to perform the following:
Working with dataset: Upload sample dataset housing.csv and explore with data explorer
Polyglot programming: Query dataframes with SQL via DuckDB
Multi-engine access via AWS Glue: Create an AWS Glue table to unlock Athena SQL/Spark engines for distributed processing
Advanced analytics: Use Athena Spark for data profiling
AI-assisted development: Generate profiling and ML code with Data Agent
ML workflow: Train Random Forest model and evaluate results
First, let’s walk through the interface and explore its core capabilities.
Understanding the interface
The Notebooks interface follows familiar notebook conventions with cells for code execution and markdown for documentation. Within the notebook, you’ll see your current programming environment (such as Python 3.11) and compute profile specifications. The interface allows you to:
Access your data by browsing files, exploring data catalogs, and managing third-party connections
Monitor variables created within your notebook context
Scale compute resources on demand by adjusting virtual CPUs and RAM based on your workload requirements, even scaling up to GPU instances
Manage packages by installing and configuring Python packages as needed
Working with the dataset
For this walkthrough, you’ll use the housing.csv sample dataset which you can download from this page. (the file is named canvas-sample-housing.csv on the linked page). Choose the Files icon in the left panel and choose the Local tab. Upload the CSV file to the notebook on the Local tab.
Notebooks provide you with instant access to your data assets. Using the data explorer, you can browse your AWS Glue Data Catalog, Amazon S3 table catalogs, Amazon S3 buckets, and configured third-party connections.
Choose the three-dot options menu.
Choose Read as dataframe, then run the inserted cell in the notebook to view the results.
import pandas as pd
<<df_csv_xxxx>> = pd.read_csv('housing.csv')
<<df_csv_xxxx>>
When you return a dataframe, Notebooks render it in a rich table format with automatic data profiling.
Polyglot programming: Python and SQL together
One of the most powerful features in Notebooks is the interoperability between Python and SQL. After you load data into a Python dataframe, you can immediately query it using SQL. For example, to calculate total population and household by ocean proximity, you can run:
select sum(population) ,sum(households),ocean_proximity
from<<df_csv_xxxx>>
group byocean_proximity
The notebook’s autocomplete functionality recognizes dataframes in your context, making SQL queries intuitive.
This SQL query runs on DuckDB (an in-memory SQL database engine), which requires no separate installation or server maintenance on your part. DuckDB’s lightweight design integrates into Python, Java, and other environments, making it ideal for your rapid interactive data analysis. For distributed processing needs, you can use engines such as Apache Spark or Trino after creating an AWS Glue table for this dataset.
Create an AWS Glue table for the dataset
After you create an AWS Glue table, you can query the dataset using various AWS Glue catalog-compatible engines, including Amazon Athena SQL (Trino) and Amazon Athena Spark. These engines deliver optimal price-performance for your specific workload requirements.
Start by creating an AWS Glue database. To do that, create a new cell in the notebook by choosing SQL and selecting Amazon Athena (SQL).
Run this SQL to create a database: create database demo;
Next, go to data explorer and choose +Add on the top left, then choose Create table. Choose the database you created earlier and enter a name for the table. Upload the housing.csv dataset file used earlier. Continue by choosing Next in the side panel to create the table.
Next, let’s run a sample SQL query in a new cell using Amazon Athena SQL:
select sum(population) , sum(households), ocean_proximity
fromdemo.housing
group by ocean_proximity
Advanced capabilities with Athena Spark
Before you can build an ML model to predict house prices, let’s analyze the dataset further and run data profiling for additional insights. For advanced exploration, you can use Amazon Athena Spark within your notebook.To do that, you’ll create a new Python cell which has a built-in Spark session. Run the following code to check the Spark version:
# Verify Spark version
spark.version
Using the SageMaker Data Agent for data profiling
Instead of writing boilerplate code manually, you can use the built-in generative AI capability.
Prompt: “Perform data profiling and create visualization for housing table”
The AI assistant generates comprehensive profiling code for you, including basic statistics calculation, column-level profiling, data type analysis, and missing value detection.
The agent accessed your AWS Glue Data Catalog, understood your housing table structure, and generated profiling code tailored to your specific columns and data types. This context awareness reduces the trial-and-error cycle you’d normally face when adapting generic code snippets to your environment. Review the generated code and run it. The fast response times help you iterate on your analysis efficiently.
If you encounter an error, you can resolve it using Fix with AI as shown in the following figure. When errors occur during execution, the “Fix with AI” feature analyzes the traceback, diagnoses the root cause, and generates corrected code, so you can keep your analysis moving forward.
Training ML models
Next, you’ll use the data agent to generate code for training a model that predicts housing prices.
Prompt: “Generate code to train a model that predicts housing prices. Use table housing.”
The AI assistant generates end-to-end code for you that:
Reads housing data from AWS Glue catalog using Amazon Athena Spark and converts to pandas
Converts string columns to numeric, encodes using one-hot encoding and removes missing values
Trains a Random Forest model to predict median house values
Evaluates model performance (RMSE, MAE, R-square)
Displays top 10 most important features for predictions
This multi-step orchestration saves you hours of development time by handling the entire workflow from data access to model evaluation.
If you encounter an error, you can resolve it using Fix with AI available in the results traceback section.
This workflow showcased Notebooks’ unified capabilities: you uploaded files locally, created AWS Glue tables for multi-engine access, used Amazon Athena Spark for distributed profiling, and used AI-assisted ML development to predict housing prices. All of this happened within a single notebook environment without switching tools.
Key benefits and best practices
Notebooks in Amazon SageMaker Unified Studio deliver several advantages:
Faster time to insights: With traditional environments, you might spend hours on configuration before analysis begins. Notebooks bypass this overhead, so you can start work immediately.
Improved collaboration: You can share notebooks with consistent environments, supporting reproducibility and reducing “works on my machine” issues.
Reduced complexity: You can access multiple data sources and compute engines from one interface rather than navigating separate tools for each data source or processing engine.
AI-accelerated development: Generate task-specific code and receive intelligent suggestions, reducing time spent on repetitive coding tasks.
Scalable performance: Handle datasets from megabytes to petabytes with appropriate compute resources. The system scales automatically as data volumes grow.
Best practices
Start with appropriate compute profiles by beginning with smaller instances and scaling up as your needs grow.
Use AI assistance with natural language prompts for your repetitive tasks and complex operations.
Combine engines strategically by using Amazon Athena Spark for your large-scale processing, Amazon Redshift for data warehousing and other specialized engines for your specific workloads.
Document your work using markdown cells to create living documentation alongside your code.
Organize using multiple cells by breaking the complex workflows into logical steps for better readability and debugging.
Cleaning up
To avoid incurring future charges, delete the resources you created in this walkthrough:
In the Amazon SageMaker Unified Studio console, navigate to the Notebook page
Delete the notebook
Delete the demo database and housing table from the AWS Glue Data Catalog
Delete Amazon SageMaker Unified Studio domain created during this walkthrough
If you created a new IAM role specifically for this walkthrough, delete it from the IAM console
Conclusion
In this post, we demonstrated how Notebooks in Amazon SageMaker Unified Studio help you work more efficiently and deliver insights more quickly. By combining familiar notebook interfaces with enterprise-scale compute, multi-engine support, and generative AI assistance, teams can streamline data and AI workflows.
The integration of Python and SQL, instant access to diverse data sources, and intelligent code generation capabilities make Notebooks a valuable tool for modern data teams. Teams can perform exploratory data analysis, build complex data pipelines, or train ML models with the flexibility and power needed within a single, intuitive environment.
In my last Week in Review post, I mentioned how much time I’ve been spending on AI-Driven Development Lifecycle (AI-DLC) workshops with customers this year. A common theme in those sessions is the need for better cost visibility. Teams are moving fast with AI, but as they go from experimenting to full production, finance and leadership really need to know who is using which resources and at what cost. That’s why I was so excited to see the launch of Amazon Bedrock new support for cost allocation by IAM user and role this week. This lets you tag IAM principals with attributes like team or cost center and then activate those tags in your Billing and Cost Management console. The resulting cost data flows into AWS Cost Explorer and the detailed Cost and Usage Report, giving you a clear line of sight into model inference spending. Whether you’re scaling agents across teams, tracking foundation model use by department, or running tools like Claude Code on Amazon Bedrock, this new feature is a game changer for tracking and managing your AI investments. You can get all the details on setting this up in the IAM principal cost allocation documentation.
Now, let’s get into this week’s AWS news…
Headlines Amazon Bedrock now offers Claude Mythos Preview Anthropic’s most sophisticated AI model to date is now available on Amazon Bedrock as a gated research preview through Project Glasswing. Claude Mythos introduces a new model class focused on cybersecurity, capable of identifying sophisticated security vulnerabilities in software, analyzing large codebases, and delivering state of the art performance across cybersecurity, coding, and complex reasoning tasks. Security teams can use it to discover and address vulnerabilities in critical software before threats emerge. Access is currently limited to allowlisted organizations, with Anthropic and AWS prioritizing internet critical companies and open source maintainers.
AWS Agent Registry for centralized agent discovery and governance now in preview AWS launched Agent Registry through Amazon Bedrock AgentCore, providing organizations with a private catalog for discovering and managing AI agents, tools, skills, MCP servers, and custom resources. The registry helps teams locate existing capabilities rather than duplicating them, with semantic and keyword search, approval workflows, and CloudTrail audit trails. It is accessible via the AgentCore Console, AWS CLI, SDK, and as an MCP server queryable from IDEs.
Last week’s launches Here are some launches and updates from this past week that caught my attention:
Announcing Amazon S3 Files, making S3 buckets accessible as file systems — Amazon S3 Files transforms S3 buckets into shared file systems that connect any AWS compute resource directly with your S3 data. Built on Amazon EFS technology, it delivers full file system semantics with low latency performance, caching actively used data and providing multiple terabytes per second of aggregate read throughput. Applications can access S3 data through both file system and S3 APIs simultaneously without code modifications or data migration.
Amazon OpenSearch Service supports Managed Prometheus and agent tracing —Amazon OpenSearch Service now provides a unified observability platform that consolidates metrics, logs, traces, and AI agent tracing into a single interface. The update includes native Prometheus integration with direct PromQL query support, RED metrics monitoring, and OpenTelemetry GenAI semantic convention support for LLM execution visibility. Operations teams can correlate slow traces to logs and overlay Prometheus metrics on dashboards without switching between tools.
Amazon WorkSpaces Advisor now available for AI powered troubleshooting— AWS launched Amazon WorkSpaces Advisor, an AI powered administrative tool that uses generative AI to help IT administrators troubleshoot Amazon WorkSpaces Personal deployments. It analyzes WorkSpace configurations, detects problems automatically, and provides actionable recommendations to restore service and optimize performance.
Amazon Braket adds support for Rigetti’s 108 qubit Cepheus QPU — Amazon Braket now offers access to Rigetti’s Cepheus-1-108Q device, the first 100+ qubit superconducting quantum processor on the platform. The modular design features twelve 9 qubit chiplets with CZ gates that offer enhanced resilience to phase errors. It supports multiple frameworks including Braket SDK, Qiskit, CUDA-Q, and Pennylane, with pulse level control for researchers.
For a full list of AWS announcements, be sure to keep an eye on the What’s New with AWS page.
Other AWS news Here are some additional posts and resources that you might find interesting:
Understanding Amazon Bedrock model lifecycle — Machine learning blog post that walks through the stages foundation models go through in Bedrock from availability through deprecation, helping teams plan for model updates and manage version dependencies in production.
Deploy OpenClaw on AWS: Choose the right options for your AI workload — Builder Center guide comparing four AWS deployment options for OpenClaw: Amazon Lightsail for individual developers, Amazon EC2 for startups needing deeper AWS integration, Amazon Bedrock AgentCore for serverless multiuser scenarios, and Amazon EKS for enterprises requiring VM level isolation and advanced orchestration.
We’re bringing back the Kiro startup credits program — Kiro is relaunching its startup credits initiative, offering eligible early stage companies complimentary access to Kiro Pro+ for up to one year. The three tier program (Starter, Growth, Scale) provides 2 to 30 users based on team size, with rolling applications accepted globally.
Upcoming AWS events Check your calendar and sign up for upcoming AWS events:
What’s Next with AWS(April 28, Virtual) Join this livestream at 9am PT for a candid discussion about how agentic AI is transforming how businesses operate. Featuring AWS CEO Matt Garman, SVP Colleen Aubrey, and OpenAI leaders discussing emerging agent capabilities, Amazon’s internal experiences, and new agentic solutions and platform capabilities.
Amazon Athena recently added support for reading Parquet Column Indexes in Apache Iceberg tables on November 21, 2025. With this optimization, Athena can perform page-level data pruning to skip unnecessary data within Parquet row groups, potentially reducing the amount of data scanned and improving query runtime for queries with selective filters. For data teams, this may help enable faster insights and help reduce costs when analyzing large-scale data lakes.
Data teams building data lakes often choose Apache Iceberg for its ACID transactions, schema evolution, and metadata management capabilities. Athena is a serverless query engine that allows you to query Amazon S3-based data lakes using SQL, and you don’t need to manage infrastructure. Based on the type of data and query logic, Athena can apply multiple query optimizations to improve performance and reduce costs.
In this blog post, we use Athena and Amazon SageMaker Unified Studio to explore Parquet Column Indexes and demonstrate how they can improve Iceberg query performance. We explain what Parquet Column Indexes are, demonstrate their performance benefits, and show you how to use them in your applications.
Overview of Parquet Column Indexes
Parquet Column Indexes store metadata that query engines can use to skip irrelevant data with greater precision than row group statistics alone. To understand how they work, consider how data is structured within Parquet files and how engines like Athena process them.
Parquet files organize data hierarchically by dividing data into row groups (typically 128-512 MB each) and further subdividing them into pages (typically 1 MB each). Traditionally, Parquet maintains metadata on the contents of each row group level in the form of min/max statistics, allowing engines like Athena to skip row groups that don’t satisfy query predicates. Although this approach reduces the bytes scanned and query runtime, it has limitations. If even a single page within a row group overlaps with the values you are searching for, Athena scans all pages within the row group.
Parquet Column Indexes help address this problem by storing page-level min/max statistics in the Parquet file footer. Row group statistics provide coarse-grained filtering, but Parquet Column Indexes enable finer-grained filtering by allowing query engines like Athena to skip individual pages within a row group. Consider a Parquet file with a single row group containing 5 pages for a column. The row group has min/max statistics of (1, 20), and each page for that column has the following min/max statistics.
When Athena runs a query filtering for values equal to 2, it first checks the row group statistics and confirms that 2 falls within the range (1, 20). Athena will then plan to scan the pages within that row group. Without Parquet Column Indexes, Athena scans each of the 5 pages in the row group. With Parquet Column Indexes, Athena examines the page-level statistics and determines that only page-0 and page-1 need to be read, skipping the remaining 3 pages.
How to use Parquet Column Indexes with Athena
Athena uses Parquet Column Indexes based on table type:
Amazon S3 Tables: Athena automatically uses Parquet Column Indexes by default when they are present.
Iceberg tables in S3 general purpose buckets: Athena does not use Parquet Column Indexes by default. To allow Athena to use Parquet Column Indexes, add an AWS Glue table property named use_iceberg_parquet_column_index and set it to true. Use the AWS Glue console or AWS Glue UpdateTable API to perform these actions.
Measuring Athena performance gains when using Parquet Column Indexes
Now that we understand what Parquet Column Indexes are, we’ll demonstrate the performance benefits of using Parquet Column Indexes by analyzing the catalog_sales table from a 3TB TPC-DS dataset. This table contains ecommerce transaction data including order dates, sales amounts, customer IDs, and product information. This dataset is a good proxy for the types of business analysis that you might perform on your own data, such as identifying sales trends, analyzing customer purchasing patterns, and calculating revenue metrics. We compare query execution statistics with and without Parquet Column Indexes to quantify the performance improvement.
Prerequisites
Before you begin, you must have the following resources:
A SageMaker Unified Studio IAM-based domain.
An Execution IAM Role configured within the SageMaker Unified Studio IAM-based domain with access to S3, AWS Glue Data Catalog, and Athena.
An S3 bucket in your account to store Iceberg table data and Athena query results.
Create catalog_sales Iceberg table
Complete the following steps using SageMaker Unified Studio notebooks. There, you can use SageMaker Unified Studio’s multi-dialect notebook functionality to work with your data using the Athena SQL and Spark engines. To create a catalog_sales Iceberg table in your account, follow these steps:
Navigate to Amazon SageMaker in the AWS Management Console and choose Open under Get started with Amazon SageMaker Unified Studio.
From the side navigation, select Notebooks and choose Create Notebook. The subsequent steps in this post will execute scripts in this notebook.
Create a new SQL cell in the notebook and set the connection type to Athena (Spark). Execute the following query to create a database for the tables in this post.
CREATE DATABASE parquet_column_index_blog;
Create a new SQL cell in the notebook and verify the connection type is Athena (Spark). Execute the following query to create a Hive table pointing to the location of the TPC-DS catalog_sales table data at the public S3 bucket.
Create a new SQL cell in the notebook and verify the connection type is Athena (Spark). Execute the following query to add the Hive partitions to the AWS Glue metadata.
Create a new SQL cell in the notebook and verify the connection type is Athena (Spark). Replace s3://amzn-s3-demo-bucket/athena_parquet_column_index_blog/catalog_sales/ with the S3 URI where you want to store your Iceberg table data, then execute the following query to create the catalog_sales Iceberg table from the Hive table.
CREATE TABLE parquet_column_index_blog.catalog_sales
USING iceberg
PARTITIONED BY (cs_sold_date_sk)
LOCATION 's3://amzn-s3-demo-bucket/athena_parquet_column_index_blog/catalog_sales/'
AS
SELECT * FROM parquet_column_index_blog.catalog_sales_hive;
Create a new SQL cell in the notebook and verify the connection type is Athena (Spark). Execute the following query to delete the catalog_sales_hive table, which was only needed to create the catalog_sales Iceberg table.
DROP TABLE parquet_column_index_blog.catalog_sales_hive;
Run an Athena query without Parquet Column Indexes
After creating the catalog_sales Iceberg table in the preceding steps, we run a simple query that analyzes shipping delays of the top 10 most ordered items. This type of analysis could be critical for ecommerce and retail operations. By identifying which popular items experience the greatest delays, fulfillment teams can focus resources where they matter most. For example, you can adjust inventory placement, change warehouse assignments, or address carrier issues. Additionally, popular items with significant shipping delays are more likely to result in order cancellations or returns, so proactively identifying these issues helps protect revenue.
SELECT cs_item_sk,
SUM(cs_quantity) as total_orders,
AVG(cs_ship_date_sk - cs_sold_date_sk) as avg_ship_delay_days,
MIN(cs_ship_date_sk - cs_sold_date_sk) as min_ship_delay,
MAX(cs_ship_date_sk - cs_sold_date_sk) as max_ship_delay,
SUM(
CASE
WHEN cs_ship_date_sk - cs_sold_date_sk > 7 THEN 1 ELSE 0
END
) as late_shipments,
SUM(
CASE
WHEN cs_ship_date_sk - cs_sold_date_sk > 7 THEN 1 ELSE 0
END
) * 100.0 / COUNT(*) as late_shipment_pct,
AVG(cs_ext_ship_cost) as avg_shipping_cost
FROM parquet_column_index_blog.catalog_sales
WHERE cs_item_sk IN (
SELECT cs_item_sk
FROM parquet_column_index_blog.catalog_sales
WHERE cs_item_sk IS NOT NULL
GROUP BY cs_item_sk
ORDER BY SUM(cs_quantity) DESC
LIMIT 10
)
AND cs_ship_date_sk IS NOT NULL
AND cs_sold_date_sk IS NOT NULL
GROUP BY cs_item_sk
ORDER BY avg_ship_delay_days DESC;
Additionally, this query is a good candidate for demonstrating the effectiveness of using Parquet Column Indexes because it has a selective filter predicate on a single column cs_item_sk. When Athena executes this query, it first identifies row groups whose min/max ranges overlap with the top 10 most ordered items. Without using Parquet Column Indexes, Athena has to scan every page of data within those matched row groups. However, when using Parquet Column Indexes, Athena can prune data further by skipping individual pages within those row groups whose min/max ranges do not overlap with the ids. Complete the following steps to establish baseline query performance when Athena does not use Parquet Column Indexes during the query.
Create a new Python cell in the notebook. Replace s3://amzn-s3-demo-bucket/athena_parquet_column_index_blog/query_results/ with the S3 URI where you want to store your Athena query results, then execute the following script. Note the runtime and bytes scanned that will be printed. The script will run the query five times with query result reuse disabled and chooses the minimum runtime and the corresponding bytes scanned among those iterations. See our numbers in the Run Athena query with Parquet Column Indexes section.
import boto3
import time
# Configuration
DATABASE = "parquet_column_index_blog"
OUTPUT_LOCATION = "s3://amzn-s3-demo-bucket/athena_parquet_column_index_blog/query_results/"
def run_athena_query(query: str, database: str, output_location: str):
athena_client = boto3.client('athena')
response = athena_client.start_query_execution(
QueryString=query,
QueryExecutionContext={'Database': database},
ResultConfiguration={'OutputLocation': output_location}
)
query_execution_id = response['QueryExecutionId']
while True:
result = athena_client.get_query_execution(QueryExecutionId=query_execution_id)
state = result['QueryExecution']['Status']['State']
if state in ['SUCCEEDED', 'FAILED', 'CANCELLED']:
break
time.sleep(5)
if state != 'SUCCEEDED':
raise Exception(f"Query failed with state: {state}")
stats = result['QueryExecution']['Statistics']
return {
'execution_time_sec': stats['EngineExecutionTimeInMillis'] / 1000,
'data_scanned_gb': stats['DataScannedInBytes'] / (1024 ** 3)
}
def benchmark_query(query: str, database: str, output_location: str, num_runs: int = 5):
results = []
for i in range(num_runs):
stats = run_athena_query(query, database, output_location)
results.append(stats)
best_run = min(results, key=lambda r: r['execution_time_sec'])
execution_time = round(best_run['execution_time_sec'], 1)
data_scanned = round(best_run['data_scanned_gb'], 1)
print(f"Execution time: {execution_time} sec")
print(f"Data scanned: {data_scanned} GB")
QUERY = """
SELECT cs_item_sk,
SUM(cs_quantity) as total_orders,
AVG(cs_ship_date_sk - cs_sold_date_sk) as avg_ship_delay_days,
MIN(cs_ship_date_sk - cs_sold_date_sk) as min_ship_delay,
MAX(cs_ship_date_sk - cs_sold_date_sk) as max_ship_delay,
SUM(
CASE
WHEN cs_ship_date_sk - cs_sold_date_sk > 7 THEN 1 ELSE 0
END
) as late_shipments,
SUM(
CASE
WHEN cs_ship_date_sk - cs_sold_date_sk > 7 THEN 1 ELSE 0
END
) * 100.0 / COUNT(*) as late_shipment_pct,
AVG(cs_ext_ship_cost) as avg_shipping_cost
FROM parquet_column_index_blog.catalog_sales
WHERE cs_item_sk IN (
SELECT cs_item_sk
FROM parquet_column_index_blog.catalog_sales
WHERE cs_item_sk IS NOT NULL
GROUP BY cs_item_sk
ORDER BY SUM(cs_quantity) DESC
LIMIT 10
)
AND cs_ship_date_sk IS NOT NULL
AND cs_sold_date_sk IS NOT NULL
GROUP BY cs_item_sk
ORDER BY avg_ship_delay_days DESC;
"""
# Run benchmark
benchmark_query(QUERY, DATABASE, OUTPUT_LOCATION, num_runs=5)
Sort the catalog_sales table
Before rerunning the query with Athena using Parquet Column Indexes, you need to sort the catalog_sales table by the cs_item_sk column. In the preceding query, there is a dynamic filter as a subquery on the cs_item_sk column:
cs_item_sk IN (
SELECT cs_item_sk
FROM parquet_column_index_blog.catalog_sales
WHERE cs_item_sk IS NOT NULL
GROUP BY cs_item_sk
ORDER BY SUM(cs_quantity) DESC
LIMIT 10
)
When executing this query, Athena pushes down the filter predicate to the data source level, fetching only rows that match the top 10 most ordered items. To maximize page pruning with Parquet Column Indexes, rows with the same cs_item_sk values should be stored near each other in the Parquet file. Without sorting, matching values could be scattered across many pages, forcing Athena to read more data. Sorting the table by cs_item_sk clusters similar values together, enabling Athena to read fewer pages.
Let’s examine the Parquet Column Indexes in one of the Parquet files to understand how the data in the catalog_sales table is currently organized. First, download the Parquet file from the cs_sold_date_sk = 2450815 partition and install the open-source parquet-cli tool on your local machine. Replace <local-path-to-parquet-file> with the path to the downloaded Parquet file, then run the following command on your local machine:
parquet column-index <local-path-to-parquet-file>
This displays Parquet Column Indexes for all columns. For brevity, only the first 11 pages of the cs_item_sk column from the first row group are shown in the following example:
Notice that nearly every page contains a wide range of values. This overlap means Athena cannot eliminate pages when filtering with Parquet Column Indexes on cs_item_sk. For example, searching for cs_item_sk = 100 requires scanning each of the 11 pages because the value 100 falls within every page’s min/max range. With this overlap, enabling Athena to use Parquet Column Indexes would provide no performance benefit. Sorting the data by cs_item_sk eliminates this overlap, creating distinct, non-overlapping ranges for each page. To make Parquet Column Indexes more effective, sort the table by completing the following step:
Create a new SQL cell in the notebook and verify the connection type is Athena (Spark). Execute the query to sort the cs_item_sk column values of the catalog_sales table in ascending order and to put all the null values in the last few Parquet pages. New Iceberg data files will be generated from this query.
Running the parquet column-index command on the sorted data file from the cs_sold_date_sk = 2450815 partition shows that the Parquet Column Indexes are now sorted and have non-overlapping ranges. The first 11 pages of the cs_item_sk column from the first row group are shown in the following example:
Now when searching for cs_item_sk = 100, Athena only needs to read page-0, skipping the remaining 10 pages entirely.
Run Athena query with Parquet Column Indexes
Now that the data is sorted to eliminate overlapping pages within the row groups for the cs_item_sk column, we run two experiments on the sorted data. The first measures the impact of sorting alone, and the second measures the combined effect of sorting with Parquet Column Indexes.
Create a new Python cell in the notebook. Execute the same script in the section Run Athena query without Parquet Column Indexes and take note of the query runtime and bytes scanned results. This measures the performance of querying sorted data without using Parquet Column Indexes.
Create a new Python cell in the notebook. Execute the following Python script to set the use_iceberg_parquet_column_index table property to true for the catalog_sales table in the AWS Glue Data Catalog.
import boto3
def add_iceberg_parquet_column_index(database_name: str, table_name: str):
glue_client = boto3.client('glue')
# Get current table definition
response = glue_client.get_table(DatabaseName=database_name, Name=table_name)
table = response['Table']
# Build TableInput with only allowed fields
table_input = {'Name': table['Name']}
allowed_fields = [
'Description', 'Owner', 'LastAccessTime', 'LastAnalyzedTime',
'Retention', 'StorageDescriptor', 'PartitionKeys', 'ViewOriginalText',
'ViewExpandedText', 'TableType', 'Parameters', 'TargetTable'
]
for field in allowed_fields:
if field in table:
table_input[field] = table[field]
# Add the property
if 'Parameters' not in table_input:
table_input['Parameters'] = {}
table_input['Parameters']['use_iceberg_parquet_column_index'] = 'true'
# Update the table
glue_client.update_table(DatabaseName=database_name, TableInput=table_input)
# Usage
add_iceberg_parquet_column_index("parquet_column_index_blog", "catalog_sales")
Create a new Python cell in the notebook. Execute the same script in the section Run Athena query without Parquet Column Indexes and take note of the query runtime and bytes scanned results. This measures the performance of querying sorted data using Parquet Column Indexes.
Athena query time and bytes scanned improvement
The following table summarizes the results from each experiment. The percentage improvements for the sorted experiments are measured against the unsorted baseline.
Experiment
Runtime (sec)
Bytes Scanned (GB)
Unsorted without Parquet Column Indexes
20.6
45.2
Sorted without Parquet Column Indexes
15.4 (25.2% faster)
27.8 (38.5% fewer bytes)
Sorted with Parquet Column Indexes
10.3 (50.0% faster)
13.0 (71.2% fewer bytes)
Recommendations
To maximize Athena’s ability to use Parquet Column Indexes and achieve optimal query performance, we recommend the following.
Sort data by frequently filtered columns. This allows Athena to efficiently read Parquet Column Indexes and skip irrelevant pages, potentially reducing scan time. When data is sorted by a filter column, similar values are clustered together within pages. Because Parquet Column Indexes store min/max values for each page, Athena can quickly determine which pages contain matching values and skip the rest.
Sort data by high-cardinality columns. This creates distinct value ranges between pages, maximizing the opportunity for Athena to skip pages during query execution. High-cardinality (many distinct values) columns produce non-overlapping min/max ranges across pages, allowing Athena to more effectively filter out irrelevant pages. In contrast, low-cardinality columns such as boolean or status fields result in overlapping ranges across many pages, reducing the number of skipped pages.
Clean up
When you have finished the steps in this post, complete the following cleanup actions to avoid incurring ongoing charges:
Create a new SQL cell in the notebook and set the connection type to Athena (Spark). Execute the following command to drop the parquet_column_index_blog database and the catalog_sales table.
DROP DATABASE parquet_column_index_blog CASCADE;
Delete the Iceberg table data and the Athena query results from your S3 bucket.
Delete the SageMaker Unified Studio IAM-based domain if it is no longer needed.
Conclusion
In this post, we showed you how Athena uses Parquet Column Indexes to speed up queries and reduce the number of bytes scanned. By using Parquet Column Indexes, Athena can skip irrelevant data pages to improve query performance, especially for queries with selective filters on sorted data. Refer to Optimize Iceberg tables to learn more about this feature and try it out on your own queries.
About the Author
The collective thoughts of the interwebz
Manage Consent
To provide the best experiences, we use technologies like cookies to store and/or access device information. Consenting to these technologies will allow us to process data such as browsing behavior or unique IDs on this site. Not consenting or withdrawing consent, may adversely affect certain features and functions.
Functional
Always active
The technical storage or access is strictly necessary for the legitimate purpose of enabling the use of a specific service explicitly requested by the subscriber or user, or for the sole purpose of carrying out the transmission of a communication over an electronic communications network.
Preferences
The technical storage or access is necessary for the legitimate purpose of storing preferences that are not requested by the subscriber or user.
Statistics
The technical storage or access that is used exclusively for statistical purposes.The technical storage or access that is used exclusively for anonymous statistical purposes. Without a subpoena, voluntary compliance on the part of your Internet Service Provider, or additional records from a third party, information stored or retrieved for this purpose alone cannot usually be used to identify you.
Marketing
The technical storage or access is required to create user profiles to send advertising, or to track the user on a website or across several websites for similar marketing purposes.
Yes
No
