Post Syndicated from Neela Kulkarni original https://aws.amazon.com/blogs/big-data/dbt-and-amazon-redshift-data-sharing-multi-cluster-pipelines-without-moving-a-byte/

fToday’s global, data-driven organizations treat data as an asset and use it across different lines of businesses (LOBs) to drive timely insights and better business decisions. This requires them to share and consume live, consistent data as a single source of truth without copying the data, regardless of where LOB users are located.

With Amazon Redshift, a fast, scalable, secure, and fully managed data warehouse, you can analyze your data using standard SQL. As organizations scale, they often deploy multiple Amazon Redshift clusters to isolate workloads, manage costs independently across teams, or meet data residency requirements across Regions. However, this multi-cluster approach traditionally required complex extract, transform, and load (ETL) pipelines to copy data between clusters, introducing latency and operational overhead. Amazon Redshift Data Sharing solves this problem. You can securely share live, transactionally consistent data from one Amazon Redshift cluster with another across accounts and Regions. The data is never copied or moved between clusters.

As data transformation pipelines grow in complexity across multiple clusters, teams need a way to manage transformations consistently, enforce governance, and maintain lineage across producer and consumer boundaries. With dbt (data build tool), teams can manage data transformation on Amazon Redshift and deploy analytics code following software engineering best practices such as modularity, continuous integration and continuous deployment (CI/CD), and embedded documentation.

dbt Cloud is a hosted service that helps data teams productionize dbt deployments. dbt Cloud offers turnkey support for job scheduling, continuous integration and continuous delivery (CI/CD) integrations, serving documentation, native Git integrations, monitoring and alerting, and an Integrated Development Environment (IDE) all within a web-based UI.

In this post, we discuss how you can set up Amazon Redshift Data Sharing and use dbt Cloud to transform your consumer data for analytics.

Internally, Amazon Redshift Data Sharing works at the namespace level. Each cluster (or Serverless workgroup) has a globally unique namespace identified by a namespace Amazon Resource Name (ARN). A producer creates a datashare object, effectively a named catalog of schemas, tables, and views and grants USAGE on it to one or more consumer namespaces (same account) or accounts (cross-account). The consumer then creates a local database from that datashare and reads live data directly from the producer’s managed storage. Same-account sharing grants to a namespace and takes effect immediately. Cross-account sharing grants to an account and adds an authorization and association handshake. For cross-Region sharing, it also adds data-transfer and latency considerations.

Solution overview

The use case for this solution is near real-time analytics on your data in a data lake with data mesh architecture. Your data from data lake will be loaded into a centralized Amazon Redshift producer data warehouse and the data will be shared to another Amazon Redshift consumer cluster in the same AWS account. Because of the data sharing between producer and consumer, the consumer always reads live data. You can then build business logic with SQL-based dbt models to analyze the data on your consumer cluster.

The following diagram illustrates the solution architecture.

Figure 1: Solution architecture for sharing data from a producer to a consumer Amazon Redshift cluster and transforming it with dbt Cloud

1. Source objects are loaded from the Amazon Simple Storage Service (Amazon S3) data lake.
2. AWS Glue or the Amazon Redshift COPY command ingests them into the producer cluster.
3. The producer shares the schema through a datashare (zero-copy, live data).
4. dbt Cloud connects over JDBC (5439) through an AWS PrivateLink endpoint.
5. dbt reads source models from the datashare database and materializes transformed models in the consumer cluster.
6. Amazon Quick Sight queries the transformed models.

Prerequisites

• Two Amazon Redshift serverless or provisioned RA3 clusters or the newer RG node types powered by AWS Graviton in the same account as Producer and Consumer clusters. Refer to the AWS Big Data Blog post on automating Amazon Redshift cluster creation on how to automate the process of creating an Amazon Redshift cluster. Note: DC2 and DS2 cluster types do not support data sharing.
• You have a dbt Cloud account. Sign up for one if you do not have one yet.
• A database user named dbt_user for dbt Cloud to connect to the cluster.
• dbt-redshift v1.11+ locally, or dbt Cloud on the Latest release track.
• dbt Cloud only connects to the consumer. The consumer’s security group must allow inbound TCP 5439 from the dbt Cloud IP addresses for your Region. If your consumer isn’t publicly accessible, connect through AWS PrivateLink or an SSH tunnel through a bastion host instead.
• For cross-account sharing, the following AWS Identity and Access Management (IAM) data sharing permissions are needed:
  • Producer administrator: redshift:AuthorizeDataShare, redshift:DeauthorizeDataShare.
  • Consumer administrator: redshift:AssociateDataShareConsumer, redshift:DisassociateDataShareConsumer, redshift:RejectDataShare.

Load sample data into your Amazon Redshift data warehouse

• For the purpose of this walkthrough, we use the sample TICKIT database. Follow the steps in the Amazon Redshift sample database guide to load the sample data into your producer Amazon Redshift data warehouse.

Creating a datashare

A datashare is created on the producer and granted to one or more consumers. You can set it up in the Amazon Redshift console or with SQL. Both produce the same datashare.

Find the namespace IDs

Data sharing grants access by namespace ID, a globally unique identifier for each Serverless namespace or provisioned cluster. You need the consumer’s namespace ID to grant the datashare and the producer’s namespace ID to consume it. Connect to each namespace in Query Editor v2 and run:

SELECT current_namespace;

Note both IDs before you continue. You can also find the ID on the console: on the Namespace configuration page, choose the namespace. The ID appears under General information as Namespace ID.

Figure 2: Finding the namespace ID on the Amazon Redshift namespace configuration page

In Query Editor v2, connect to the producer workgroup dbtdsproducer and choose the dev database, which holds the tables you’re sharing. Run the following steps as a superuser or as a user with the CREATE DATASHARE privilege.

Figure 3: Connecting to the producer workgroup in Query Editor v2

Step 1: Create the datashare

CREATE DATASHARE dbtdsdemo SET PUBLICACCESSIBLE TRUE;

This creates an empty datashare in the dev database. PUBLICACCESSIBLE TRUE lets consumers whose workgroups or clusters are publicly accessible query the datashare. If none of your consumers are publicly accessible, leave this setting out, because the default is FALSE.

Step 2: Add the schema and tables

Before you can share any tables, add the schema that contains them. Adding the schema alone doesn’t share its tables — the consumer sees only the tables you add explicitly, which lets you follow the principle of least privilege and share just what each consumer needs.

ALTER DATASHARE dbtdsdemo ADD SCHEMA dsdemo;
ALTER DATASHARE dbtdsdemo ADD TABLE dsdemo.event, dsdemo.sales, dsdemo.users;

If the consumer should get every table in the schema, including tables you create later, run the following statements instead of ADD TABLE:

ALTER DATASHARE dbtdsdemo ADD ALL TABLES IN SCHEMA dsdemo;
ALTER DATASHARE dbtdsdemo SET INCLUDENEW = TRUE FOR SCHEMA dsdemo;

Use INCLUDENEW = TRUE with care. Any table added to dsdemo afterward is shared automatically, so a table created later — including one that holds sensitive data — becomes available to consumers without a further ALTER DATASHARE. For most workloads, prefer sharing specific tables explicitly.

Step 3: Grant the datashare to the consumer

GRANT USAGE ON DATASHARE dbtdsdemo
  TO NAMESPACE '<consumer-namespace-id>';

Step 4: Verify the datashare

-- What's in the datashare
SELECT share_name, object_type, object_name, include_new
FROM svv_datashare_objects
WHERE share_name = 'dbtdsdemo'
ORDER BY object_type, object_name;

Figure 4: Verifying the schema and tables shared in the datashare

-- Who it's shared with
SELECT share_name, consumer_namespace, consumer_account
FROM svv_datashare_consumers
WHERE share_name = 'dbtdsdemo';

You should see one schema row (dsdemo), two table rows (dsdemo.event and dsdemo.sales), and one consumer row with your consumer’s namespace ID.

Figure 5: Verifying the consumer namespace that the datashare is shared with

Step 5: Confirm the datashare on the console

Follow these steps to access a datashare from the consumer:

• Select your Amazon Redshift consumer serverless namespace or cluster from the Amazon Redshift console.
• Connect to a local database in your consumer cluster.
• Switch to Datashares tab. You should be able to see the datashares in the Datashares from other namespaces and AWS accounts section.

Figure 6: Viewing the datashare on the consumer in the Amazon Redshift console Datashares tab

Cross-account sharing

If the consumer is in a different AWS account, the flow needs two more approvals.

First, on the producer, grant the datashare to the consumer’s account ID instead of a namespace:

GRANT USAGE ON DATASHARE dbtdsdemo TO ACCOUNT '<consumer-account-id>';

Next, in the producer account, an administrator authorizes the datashare:

aws redshift authorize-data-share \
  --data-share-arn arn:aws:redshift:<region>:<producer-account-id>:datashare:<producer-namespace-id>/dbtdsdemo \
  --consumer-identifier <consumer-account-id>

Finally, in the consumer account, an administrator associates the datashare with a namespace:

aws redshift associate-data-share-consumer \
  --data-share-arn arn:aws:redshift:<region>:<producer-account-id>:datashare:<producer-namespace-id>/dbtdsdemo \
  --consumer-arn arn:aws:redshift-serverless:<region>:<consumer-account-id>:namespace/<consumer-namespace-id>

For the IAM permissions these commands need, see Prerequisites. For more information, see Sharing data across accounts.

Accessing a datashare from the consumer

Once the producer grants the datashare, the consumer gets live, read-only access to the data in place — queries always return the current version.

Create the consumer database with SQL

In Query Editor v2, connect to the consumer workgroup dbtdsconsumer and choose the dev database.

Step 1: Confirm the datashare is visible

SELECT share_name, share_type, producer_namespace
FROM svv_datashares
WHERE share_name = 'dbtdsdemo';

The result has one row with share_type set to INBOUND and the producer’s namespace ID.

Step 2: Create the database

CREATE DATABASE tickit WITH PERMISSIONS
  FROM DATASHARE dbtdsdemo OF NAMESPACE '<producer-namespace-id>';

WITH PERMISSIONS lets you grant access to individual schemas and tables, the same way as for local objects. For a cross-account datashare, replace OF NAMESPACE '<producer-namespace-id>' with OF ACCOUNT '<producer-account-id>' NAMESPACE '<producer-namespace-id>'.

To query the shared data, connect to a regular database on the consumer, such as dev, and create a consumer database from the datashare. You then reference shared objects with three-part notation, database.schema.table. A single query can join tables in dev with tables in the consumer database.

Step 3: Grant access to the dbt user

In this post, dbt Cloud connects to the consumer as a dedicated database user (dbt_user), not as the admin user. That user needs to read the shared tables and to create schemas and tables in the two local databases where dbt builds models:

GRANT USAGE ON DATABASE tickit TO dbt_user;
GRANT USAGE ON SCHEMA tickit.dsdemo TO dbt_user;
GRANT SELECT ON ALL TABLES IN SCHEMA tickit.dsdemo TO dbt_user;

Step 4: Validate created database

• Once the database is created, refresh database connections in Query Editor v2. Expand your consumer serverless workgroup or cluster. You should see the consumer database being created with three tables.

Figure 7: The consumer database and its three shared tables in Query Editor v2

• Run the following SQL to query the consumer table. Use the database name you entered in Step 4 (Enter the Database name) instead of tickit.

SELECT * FROM tickit.dsdemo.sales;

Transforming your data with dbt Cloud

In this section, you connect dbt Cloud to your Amazon Redshift consumer, set up a project and a managed repository, and build dbt models against the shared data. Let’s begin by creating the connection.

Connect dbt Cloud to Amazon Redshift

1. From Account settings, choose + New project, and enter a name such as dbt-redshift-datasharing.
2. For the warehouse, choose Redshift and create a connection:
   • Hostname: your consumer endpoint, for example <workgroup>.<account-id>.<region>.redshift-serverless.amazonaws.com, without a protocol or port.
   • Port: 5439.
   • Database: dev.

Figure 8: Consumer endpoint/hostname on cluster workgroup

Figure 9: Additional Redshift connection settings in dbt Cloud

Configure development and deployment credentials

dbt Cloud keeps credentials separate for each environment:

• Development credentials are personal and used by the Studio IDE. Set them under Account settings → Credentials → your project.
• Deployment credentials are used by scheduled jobs and set on the deployment environment’s profile.

For both, enter:

• Authentication method: Username and password.
• Username: dbt_user.
• Password: the password for dbt_user.
• Schema: the schema where dbt builds your models, such as dbt_ for development or analytics for production. dbt creates this schema in dev.
• Extended Attribute: datasharing: true.

Choose Test connection. A password authentication failed for user “admin” error means the profile still points to the admin user instead of dbt_user.

Figure 10: Entering development credentials in dbt Cloud

Production authentication with IAM

For production, use IAM user authentication. dbt Cloud supports it through the environment’s Extended attributes. On Amazon Redshift Serverless, dbt ignores the user and password fields and authorizes the IAM identity instead. Store the keys as dbt Cloud environment variables prefixed with DBT_ENV_SECRET_ so that they’re masked in logs:

method: iam
region: us-east-2
access_key_id: "{{ env_var('DBT_ENV_SECRET_ACCESS_KEY_ID') }}"
secret_access_key: "{{ env_var('DBT_ENV_SECRET_SECRET_ACCESS_KEY') }}"

Scope the IAM user’s policy to redshift-serverless:GetCredentials on the consumer workgroup and rotate its access keys regularly. For local development and CI runners, store database credentials in AWS Secrets Manager and inject them as environment variables at runtime. Never commit credentials to profiles.yml.

Enable datasharing support

By default, dbt reads metadata from PostgreSQL catalog tables such as pg_tables, pg_views, and information_schema. These only show objects in the database you’re connected to. When your models read from tickit while you’re connected to dev, those metadata queries can’t see the shared objects.

Setting datasharing: true switches the adapter to Amazon Redshift cross-database system views and SHOW commands:

Database users can read these views by default. If you restrict system-view access in your organization, grant SELECT on them to dbt_user.

To turn it on in dbt Cloud for existing environments:

1. Go to Orchestration → Environments and edit each environment (development and deployment).
2. Under Extended attributes, add: datasharing: true.
3. Confirm that the environment is on the Latest release track.

For local dbt users, here’s the sample profiles.yml block:

redshift_datasharing:
  target: dev
  outputs:
    dev:
      type: redshift
      host: <consumer-endpoint>
      port: 5439
      dbname: dev  # regular database, not the datashare database
      user: "{{ env_var('DBT_USER') }}"
      password: "{{ env_var('DBT_PASSWORD') }}"
      schema: dbt_<your_name>
      datasharing: true   # cross-database metadata through SVV_* views

Set up a dbt Cloud managed repository

When you develop in dbt Cloud, you can use Git to version control your code. For the purpose of this post, you will use dbt Cloud-hosted managed repository.

To set up a managed repository:

• Under “Setup a repository”, select Managed.
• Enter a name for your repo such as dbt-redshift-workshop.
• Choose Create. It will take a few seconds for your repository to be created and imported.

Figure 11: Setting up a dbt Cloud managed repository

• Once you see the “Successfully imported repository,” choose Continue.

Figure 12: Confirmation that the managed repository imported successfully

• Choose Start developing in the Studio. In a new managed repository, choose Initialize dbt project, then Commit and sync with the message initial commit. Then choose Create branch and name the branch add-redshift-models.

Build your models

With the connection and repository in place, you can now define the dbt models that transform the shared data. The following steps configure the project, choose a materialization, declare the datashare as a source, and build the staging and reporting models. Open the project in Studio on dbt Cloud account.

Configure the project

Open dbt_project.yml and replace the models block at the bottom of the file. The key under models: must match the name: at the top of the file. A newly initialized project uses dbt_ds_demo.

models:
    dbt_ds_demo:
      staging:
        +materialized: table
        +tags: ['staging', 'datashare']
        +meta: {owner: 'analytics-eng'}
      dept_finance:
        +materialized: table
        +database: analytics
        +tags: ['finance', 'reporting']
  on-run-end:
    - "{{ log('dbt run completed for ' ~ target.name, info=true) }}"

Choose a materialization

Regular views can’t reference datashare objects on the consumer. Amazon Redshift rejects them with “External tables are not supported in views,” so dbt’s default view materialization fails on any model that selects from the datashare. You have these options:

This walkthrough uses table for both the staging and finance models.

Declare the datashare as a source

Create the staging and dept_finance folders under models. In staging, create sources.yml:

version: 2

sources:
  - name: ds
    description: "Consumer database created from the datashare (zero-copy from the producer)."
    database: tickit        # the database you created from the datashare
    schema: dsdemo          # the schema you added to the datashare
    tables:
      - name: event
        config:
          loaded_at_field: starttime
          freshness:
            warn_after: {count: 1, period: hour}
            error_after: {count: 24, period: hour}
        columns:
          - name: eventid
            data_tests: [not_null, unique]
      - name: sales
        config:
          loaded_at_field: saletime
          freshness:
            warn_after: {count: 1, period: hour}
            error_after: {count: 24, period: hour}
        columns:
          - name: salesid
            data_tests: [not_null, unique]

Create the staging models

Create staging_event.sql and copy the following code to build event model:

with source as (
    -- List columns explicitly to protect against upstream schema drift
    select eventid, venueid, catid, dateid, eventname, starttime
    from {{ source('ds', 'event') }}
)
select
    eventid::integer        as eventid,
    venueid::smallint       as venueid,
    catid::smallint         as catid,
    dateid::smallint        as dateid,
    eventname::varchar(200) as eventname,
    starttime::timestamp    as starttime,
    current_timestamp       as etl_load_timestamp
from source

Create staging_sales.sql and copy following SQL to create sales model.

with store_source as (
    select salesid, listid, sellerid, buyerid, eventid, dateid,
           qtysold, pricepaid, commission, saletime
    from {{ source('ds', 'sales') }}
)
select
    salesid::integer         as salesid,
    'store'                  as salestype,
    listid::integer          as listid,
    sellerid::integer        as sellerid,
    buyerid::integer         as buyerid,
    eventid::integer         as eventid,
    dateid::smallint         as dateid,
    qtysold::smallint        as qtysold,
    pricepaid::decimal(8,2)  as pricepaid,
    commission::decimal(8,2) as commission,
    saletime::timestamp      as saletime,
    current_timestamp        as etl_load_timestamp
from store_source

Create the finance reporting models

These models are written to the analytics database because of the +database setting in dbt_project.yml.

In dept_finance, create rpt_finance_qtr_total_sales_by_event.sql:

select
    date_part('year', a.saletime)    as year,
    date_part('quarter', a.saletime) as quarter,
    b.eventname,
    count(a.salesid)  as sales_made,
    sum(a.pricepaid)  as sales_revenue,
    sum(a.commission) as staff_commission,
    -- NULLIF returns NULL instead of failing when revenue is zero
    round(sum(a.commission) / nullif(sum(a.pricepaid), 0), 4) as commission_pcnt
from {{ ref('staging_sales') }} a
left join {{ ref('staging_event') }} b on a.eventid = b.eventid
group by year, quarter, b.eventname

Create rpt_finance_qtr_top_event_by_sales.sql:

select *
from (
    select
        *,
        -- ROW_NUMBER() returns exactly 3 events per quarter.
        -- To keep tied events, use RANK(), which can return more than 3 rows.
        row_number() over (partition by year, quarter order by sales_revenue desc) as row_num
    from {{ ref('rpt_finance_qtr_total_sales_by_event') }}
)
where row_num <= 3

Run and verify

In the IDE command bar, run:

dbt build

Figure 13: Running dbt build in dbt Studio

In Query Editor v2 on the Amazon Redshift console, refresh the consumer. You should see new dbt schema and tables created as shown in the following figure:

Figure 14: The new dbt schemas and tables created on the consumer

SELECT * FROM analytics.dbt_<your_name>.rpt_finance_qtr_total_sales_by_event;
SELECT * FROM analytics.dbt_<your_name>.rpt_finance_qtr_top_event_by_sales ORDER BY year, quarter, row_num;

Commit your changes

Under Version Control, choose Commit and sync, and enter a message such as Add consumer datashare models. Then choose Merge this branch to main or open a pull request if you connected GitHub.

Conclusion

In this post, we showed you how to set up Amazon Redshift Data Sharing between two clusters and use dbt Cloud to transform consumer-side data into analytics-ready models. Beyond convenience, this architecture delivers concrete benefits for a production analytics platform. The Amazon Redshift multiversion concurrency control (MVCC) model gives consumers transactionally consistent reads of live producer data. The zero-copy sharing model removes duplicate Amazon S3 storage and the ETL that would otherwise move data between clusters. And dbt maintains a single lineage graph that spans the producer and consumer boundary, so governance and documentation stay coherent across cluster ownership lines.

The combination of Amazon Redshift Data Sharing and dbt Cloud lets teams build high-performing data pipelines that use live, transactionally consistent data without the overhead of data copying — enabling near real-time analytics with clean separation of producer and consumer workloads.

Clean up

To avoid ongoing charges, remove the resources you created for this walkthrough once you are done:

-- On the producer, revoke and drop the datashare
DROP DATASHARE <datashareName>;

-- On the consumer, drop the database created from the datashare
DROP DATABASE <database>;

Then delete or pause the producer and consumer Amazon Redshift Serverless workgroups (or provisioned clusters) you provisioned for the walkthrough.

# Delete the workgroup (and its namespace)
aws redshift-serverless delete-workgroup --workgroup-name <name>
aws redshift-serverless delete-namespace --namespace-name <name>

Delete the dbt Cloud project if it is no longer needed. Deleting test clusters prevents ongoing compute and storage charges.

About the authors

Post Syndicated from Sidhanth Muralidhar original https://aws.amazon.com/blogs/big-data/run-an-automated-operational-review-with-the-amazon-redshift-mcp-server/

It’s the end of a strong quarter, and your Amazon Redshift workloads have grown with the business. Data volumes are up, new pipelines have shipped, and more teams are querying than when you first sized the cluster. Nothing is broken, but this is exactly when a periodic operational review pays off. It confirms the cluster is still tuned for how it’s used today, and surfaces ways to optimize cost and performance as you scale.

Amazon Redshift already automates significant operational complexity. Autonomics features such as automatic table optimization, automatic workload management, and automatic vacuum handle much of the complex work, so you can focus on writing queries rather than managing infrastructure. Amazon Redshift Serverless goes further, using AI-driven scaling that adapts compute to workload demand.

Even so, some decisions still benefit from human judgment. For example, table design choices may not have accounted for common join patterns, or query patterns may have shifted since the tables were built. Both are worth revisiting. Likewise, as workloads increase, it’s worth deciding whether the current deployment model is still correctly sized.

Many organizations have turned this into a recurring business process: monitoring dashboards and key performance indicators (KPIs), narrowing down long-running queries, collaborating across teams to resolve them, and coaching users on efficient query patterns. This isn’t unique to Amazon Redshift. It’s a best practice for any production data system.

AWS Enterprise Support helps customers with these reviews. But even with specialist assistance, the process typically consumes 4–8 hours of focused effort per cluster: assembling diagnostic queries, interpreting results, cross-referencing documentation, and compiling findings into a prioritized report. Most teams recognize the value, but consistently deprioritize it in favor of feature delivery and day-to-day operations.

In a previous post, we demonstrated how to query Amazon Redshift using natural language with Kiro and the Amazon Redshift Model Context Protocol (MCP) server. That approach replaced manual schema navigation and hand-written SQL with conversational analytics. This post takes the next step: from asking questions about your data to asking questions about your cluster’s operational health.

The review_cluster tool in the Amazon Redshift MCP server makes the entire diagnostic process available from a single natural-language request. It evaluates 12 diagnostic areas, identifies potential issues, and returns prioritized recommendations linked directly to AWS documentation, covering both provisioned clusters and serverless workgroups in one invocation. What previously required hours of specialist effort now completes in a few minutes. This makes it practical to review your cluster weekly, after significant schema changes, or before peak traffic events.

In this post, you learn how to:

1. Run an automated operational review of your Amazon Redshift cluster using natural language.
2. Interpret the structured findings and recommendations.
3. Act on specific findings conversationally, turning diagnostics into remediation without leaving Kiro, Claude, or any MCP-compatible client.
4. Incorporate periodic reviews into your operational workflow.

What is the review_cluster tool?

A single natural-language request triggers a comprehensive diagnostic assessment. It complements the server’s discovery and query tools: those give you conversational access to your data, while review_cluster gives you the same for your cluster’s health. The tool executes a curated set of queries against Amazon Redshift system views, evaluates the results against known best-practice thresholds, and returns structured findings with prioritized recommendations. Every recommendation includes direct links to the relevant AWS documentation, so you can move from identification to remediation without searching. The entire process is read-only. No data is modified, no configuration is changed, and no resources are created. The tool observes and reports. Remediation decisions remain with you.

The tool returns a structured result containing:

• Signals evaluated: The total number of diagnostic checks executed.
• Findings: A list of triggered conditions, each with a name, the number of affected objects (tables, queries, nodes), and linked recommendation IDs.
• Recommendations: A deduplicated list of corrective actions, ordered by effort, with documentation links.

What it inspects

The review currently evaluates your cluster across 12 diagnostic areas:

1. Automatic Table Optimization (ATO): Whether the automated tuning actions of Amazon Redshift (encoding, sort keys, distribution styles) are completing successfully or not.
2. Table design recommendations: Amazon Redshift Advisor recommendations for encoding, sort keys, and distribution that have not yet been applied.
3. COPY and data ingestion performance: File sizing, parallelism relative to slice count, and ingestion throughput patterns.
4. External query (Spectrum) performance: Partition pruning effectiveness, file sizes, and scan efficiency for queries against external tables.
5. Materialized view health: Staleness, auto-refresh status, and maintenance overhead of materialized views.
6. Node and storage utilization: Disk usage, node type currency, and whether the cluster would benefit from migration to the latest instance generation.
7. Table-level statistics: Vacuum status, stale statistics, sort key effectiveness, distribution skew, and compression encoding coverage.
8. Query performance: The longest-running queries, nested loop joins, and disk spill patterns.
9. Workload usage patterns: How intensively the cluster is used throughout the day and whether the workload suits the current deployment model.
10. Workload Management (WLM) configuration: Queue setup, concurrency scaling, short query acceleration, priority settings, and query monitoring rules.
11. Workload evaluation: Whether the cluster’s utilization pattern suggests it could benefit from a different deployment model such as serverless.
12. Serverless scaling: Whether a serverless workgroup’s observed compute range falls within the AI-driven scaling window.

Not every area applies to every deployment, because some checks target configuration that only exists in one model. Four checks are provisioned-only: COPY parallelism relative to slice count, node and storage utilization, WLM configuration, and workload evaluation. These tune constructs that Amazon Redshift Serverless manages for you. Serverless has no nodes or slices to size, and it always uses automatic WLM rather than user-configured queues, so there is nothing for those checks to act on. The serverless-scaling check is the reverse: it evaluates AI-driven Redshift Processing Unit (RPU) scaling, which is specific to serverless and has no equivalent on a provisioned cluster. As a result, a provisioned cluster evaluates 11 of the areas and a serverless workgroup evaluates 8, and the tool reports the number actually run as signals evaluated.

Running your first review

The following steps cover what you need in place and how to start a review.

Prerequisites

Before running a review, make sure that you have:

• An MCP-compatible client configured with the Amazon Redshift MCP server (for example, Kiro). See the Amazon Redshift MCP server README for installation and configuration steps.
• Valid AWS credentials with the AWS Identity and Access Management (IAM) permissions the server requires.
• The sys:monitor role granted to the connecting database user, the one additional database permission review_cluster needs.

To grant this access, a database administrator with superuser privileges runs:

GRANT ROLE sys:monitor TO "IAMR:YourRoleName";

The database user name for IAM identities follows the format IAMR:RoleName or IAM:UserName. Confirm yours with SELECT current_user in the Amazon Redshift Query Editor. The double quotes in the GRANT statement are required for IAM identity names.

With prerequisites in place, the review is a single prompt:

Run a review of analytics-cluster

The agent identifies the target cluster, connects to the database, and executes the diagnostic assessment.

The following example shows the output from a review of a serverless workgroup. Because four provisioned-only areas don’t apply, the tool evaluated 8 of the 12 diagnostic areas, identified 8 findings, and mapped them to 6 distinct recommendations:

Cluster Review: analytics-cluster (Serverless, eu-west-2 / London)
Ran the diagnostic --- result: 8 signals evaluated, 8 findings, 6 recommendations.
Findings
- Missing statistics --- 16 queries hit tables that have never been analyzed
- Dist/Broadcast alerts --- 10 queries reshuffle data across nodes
- Nested loop joins / cross-joins --- 5 long-running queries
- Small tables without ALL distribution --- 4 tables
- Small tables with a manual sort key --- 4 tables
- Large table with data skew --- 1 table (slice imbalance ≥4x)
- Large table distributed by date/datetime --- 1 table
- Variable serverless workload in the AI-scaling range --- 1 workgroup
Recommendations
1. REC_003 --- Analyze tables (fix missing stats). Effort: Low. Run or schedule ANALYZE on the 16 affected tables. Lowest-effort, highest-impact item; also improves the poor distribution/plan decisions the optimizer is making. Docs: https://docs.aws.amazon.com/redshift/latest/dg/t_Analyzing_tables.html
2. REC_007 --- Right-size sort keys. Effort: Low. The 4 small tables carry manual sort keys that add overhead for little benefit --- switch them to ALTER TABLE [table] ALTER SORTKEY AUTO. Docs: https://docs.aws.amazon.com/redshift/latest/dg/c_best-practices-sort-key.html
3. REC_008 --- Choose the right distribution style. Effort: Low--Medium. Small tables → DISTSTYLE AUTO/ALL; the skewed large table → pick a better DISTKEY (skew often from NULLs in the key); the date-distributed large table → reconsider the key or move to DISTSTYLE AUTO. This also resolves the 10 Dist/Broadcast query alerts. Docs: https://docs.aws.amazon.com/redshift/latest/dg/c_best-practices-best-dist-key.html
4. REC_030 --- Tune serverless AI-driven scaling. Effort: Low. Review your price-performance target (Optimizes for cost / Balanced / Optimizes for performance) and pair it with Max capacity + Max RPU-hours caps to keep cost predictable. Adapts over 1--3 days. Docs: https://docs.aws.amazon.com/redshift/latest/mgmt/serverless-capacity.html
5. REC_009 --- Remove nested loop joins (cross-joins). Effort: Medium. Add join conditions to the 5 cross-join queries. Docs: https://docs.aws.amazon.com/redshift/latest/dg/query-performance-improvement-opportunities.html#nested-loop
6. REC_019 --- Add QMR rules. Effort: Medium. Add query monitoring rules to catch nested loops and long runtimes going forward (log/hop/abort actions). Docs: https://docs.aws.amazon.com/redshift/latest/dg/cm-c-wlm-query-monitoring-rules.html
Suggested starting point
Start with REC_003 (ANALYZE) --- cheapest, highest-impact, and likely a root cause behind several slow-query findings. Then apply the AUTO table-optimization changes in REC_007 and REC_008, which are mostly one-line ALTER TABLE statements.
Want me to pull the specific table names behind each finding and draft the exact ANALYZE / ALTER TABLE remediation statements?

Each finding represents an independent diagnostic condition that was triggered. The affected_row_count indicates how many objects match that specific condition. These counts describe different dimensions of the cluster and are not additive across findings.

When the tool returns zero findings, the cluster is operating within best-practice thresholds across the evaluated areas.

From findings to fixes

The review output isn’t a static report. It’s a starting point for an interactive conversation. Each finding identifies a specific condition, and each recommendation provides a clear remediation path with documentation links. From here, you can continue working within the same Kiro session to plan and prepare your next steps.

Recommendations fall into four broad categories:

• Quick configuration changes: Actions such as enabling concurrency scaling or short query acceleration require a single parameter change in the Amazon Redshift console or a brief API call. These are low-risk, high-impact adjustments that can often be applied immediately.
• Batch table operations: Findings related to table design (distribution style, sort keys, compression encoding) typically affect multiple tables. You can ask Kiro to list the specific tables involved and generate the corresponding ALTER TABLE statements. Review the generated SQL, then apply it through the Amazon Redshift Query Editor or your preferred SQL client.
• Data ingestion optimization: Findings related to COPY performance identify inefficiencies in how data is loaded. For example, source files might be too small, or file counts might not align with the cluster’s slice count. Addressing these requires changes upstream in your extract, transform, and load (ETL) pipeline or Amazon Simple Storage Service (Amazon S3) staging process rather than within Amazon Redshift itself.
• Architectural decisions: Recommendations such as migrating to Amazon Redshift Serverless or resizing to RG instances require broader evaluation. These are not single-command fixes. They involve capacity planning, workload testing, and potentially migration steps. The recommendation text and linked documentation provide the context needed to begin that planning.

After presenting findings, Kiro proposes the next best step to act upon, typically starting with the lowest-effort, highest-impact recommendation. Alternatively, you can direct the conversation yourself. For example:

• “Which tables are affected by the distribution style finding?”
• “What would the ALTER TABLE statements look like for those tables?”
• “Explain what concurrency scaling does and how to enable it.”
• “What are the trade-offs of migrating this workload to serverless?”

Kiro retrieves the relevant details, generates SQL where applicable, and references the documentation. The tool diagnoses and recommends, but does not modify your cluster. The decision to apply changes remains with you.

Best practices

Tips for getting the most out of review_cluster.

1. Embed reviews in your DataOps practice. Regular table and cluster maintenance is a foundational best practice for any production data warehouse, and its value comes from consistency. Operational reviews are one component of a broader DataOps discipline: the practice of maintaining data systems that are clean, reliable, governed, and always available. Define KPIs for your cluster, such as query latency percentiles, disk spill frequency, and WLM queue wait times. Then use periodic review_cluster runs to track your query and workload optimization progress against them. Over time, this creates a feedback loop: findings inform remediation, KPIs measure impact, and the next review validates improvement.
2. Run reviews on a regular cadence. Treat operational reviews like testing: the more routinely you run them, the sooner you catch drift before it affects users. Consider running a review weekly, after significant schema changes, after major data loads, or before anticipated peak traffic events.
3. Follow the documentation links. Every recommendation includes direct links to the relevant AWS documentation. These pages provide detailed guidance, edge cases, and configuration examples that go beyond what the recommendation text can cover. Use them as your primary reference when planning remediation.
4. Start with low-effort wins. The recommendations are ordered by effort. Begin with quick configuration changes (concurrency scaling, short query acceleration, query monitoring rules) before moving to structural changes that require broader planning. Early wins build confidence and often improve cluster performance enough to create headroom for larger changes.
5. Use the review as a baseline. Run a review before and after significant changes to measure their effect. For example, after applying table design recommendations, a follow-up review should show fewer table-related findings. This before-and-after pattern helps validate that your changes had the intended impact.

Conclusion

In this post, you learned how to run an automated operational review of your Amazon Redshift cluster using natural language with Kiro. The review_cluster tool evaluates 12 diagnostic areas, from table design and workload management to node utilization and data ingestion performance. It returns prioritized, actionable recommendations linked directly to AWS documentation.

What previously required a specialist to assemble diagnostic scripts, interpret system view outputs, and compile findings over several hours now completes in a single request. This shift makes it practical to incorporate operational reviews into your regular workflow rather than treating them as an infrequent, resource-intensive exercise.

To get started:

1. Make sure the Amazon Redshift MCP server is configured in Kiro (see the setup post).
2. Grant the sys:monitor role to your database user.
3. Ask Kiro to review your cluster.

To go further:

• Visit the Amazon Redshift MCP server documentation for the full tool reference.
• Read the MCP protocol to understand how AI agents integrate with external tools.
• Explore Kiro for additional capabilities including steering files, hooks, and agent automation.

About the authors

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

Production data is always evolving. Tables gain and lose columns, outgrow their data types, and get re-partitioned as query patterns shift. Multiple engines often need to read the same data. These changes used to mean expensive data rewrites or rebuilt pipelines. Apache Iceberg makes them metadata-only operations, and Amazon Redshift now supports evolving schemas and partitioning layouts through ALTER statements, with no data rewrites and no pipeline rebuilds. You can also create AWS Lake Formation resource links in the catalog of Amazon S3 Tables, a capability of Amazon Simple Storage Service (Amazon S3), for centralized cross-engine governance.

In Part 1, you created Apache Iceberg tables and wrote data directly from Amazon Redshift to your data lake, setting up external schemas, creating tables in both Amazon Simple Storage Service (Amazon S3) and Amazon S3 Tables, and performing INSERT operations with full ACID (Atomicity, Consistency, Isolation, Durability) compliance. In Part 2, you performed DELETE, UPDATE, and MERGE operations to modify data at the row level and synchronize staging and production tables.

In this post, you use the customer and orders datasets from the previous posts to evolve Iceberg table schemas and partitioning with ALTER operations. You also create an AWS Lake Formation resource link in the S3 Tables catalog to share tables with other analytics engines under a single, centralized permission model.

Solution overview

This solution demonstrates ALTER operations for Apache Iceberg tables in Amazon Redshift and Lake Formation resource link creation for the S3 Tables catalog. The walkthrough includes the following key operations:

• ALTER TABLE RENAME COLUMN – Rename existing columns without changing data types or partition specs.
• ALTER TABLE ADD/DROP COLUMN – Add new columns or remove existing columns as metadata-only operations.
• ALTER TABLE ALTER COLUMN – Widen column data types (for example, INT to BIGINT) without rewriting data.
• ALTER TABLE SET TABLE PROPERTIES – Change compression type for future writes.
• ALTER TABLE ADD/DROP/REPLACE PARTITION FIELD – Evolve partition specs without re-partitioning existing data.
• Lake Formation resource link – Create a resource link in the S3 Tables catalog for centralized access governance.

The following diagram shows the end-to-end architecture:

Figure 1: Architecture showing Amazon Redshift performing ALTER operations on Iceberg tables in S3 Tables, with Lake Formation resource links providing access from Amazon Athena and other engines

Prerequisites

Complete the setup from Part 1 and Part 2, including:

• An Amazon Redshift data warehouse (provisioned or Serverless) on patch 201 or higher.
• The AWS Identity and Access Management (IAM) role (RedshifticebergRole) with permissions for Amazon S3, AWS Glue Data Catalog, and Lake Formation.
• The customer table in a standard Amazon S3 bucket (AWS Glue catalog: customer_db).
• The orders table in an Amazon S3 table bucket (iceberg-write-blog@s3tablescatalog).
• Access to an IAM role that is a Lake Formation data lake administrator.
• AWS Glue Data Catalog integrated with S3 Tables (s3tablescatalog exists).

Schema evolution with ALTER TABLE

With ALTER TABLE, you can change Iceberg table definitions, including schema, partition specs, and properties, without rewriting stored data. Each operation updates only metadata. The table structure changes instantly while existing data files remain untouched. This helps make schema evolution, partition adjustments, and property updates safe to run on production tables.

Add a column

You can add a new column to an Iceberg table using ALTER TABLE. Each new column is added with a unique field ID that Iceberg uses for column tracking across schema evolution. Existing rows return NULL for the newly added column.

Verify the current schema:

SHOW TABLE dev.demo_iceberg.customer;

Figure 2: SHOW TABLE output showing the current customer table schema

Add the column:

-- Add a loyalty_tier column to the customer table
ALTER TABLE dev.demo_iceberg.customer
ADD COLUMN loyalty_tier VARCHAR;

Verify the schema change:

SHOW TABLE dev.demo_iceberg.customer;

Figure 3: SHOW TABLE output showing the loyalty_tier column added to the schema

The following output shows the new loyalty_tier column as NULL for existing rows:

SELECT customer_id, customer_name, city, loyalty_tier
FROM dev.demo_iceberg.customer
ORDER BY customer_id;

Figure 4: Query results showing loyalty_tier as NULL for existing rows

Populate the new column by aggregating order totals from the orders table in S3 Tables:

-- Set loyalty_tier based on total spend from orders
UPDATE dev.demo_iceberg.customer
SET loyalty_tier = CASE
WHEN a.total_spend > 300 THEN 'Gold'
ELSE 'Silver'
END
FROM (
SELECT customer_id, SUM(total_order_amt) AS total_spend
FROM "iceberg-write-blog@s3tablescatalog".iceberg_write_namespace.orders
GROUP BY customer_id
) AS a
WHERE dev.demo_iceberg.customer.customer_id = a.customer_id;

The following output shows customer loyalty tiers after the update:

SELECT customer_id, customer_name, loyalty_tier
FROM dev.demo_iceberg.customer
ORDER BY customer_id;

Figure 5: Customer table showing Gold and Silver loyalty tiers

Note: Customer IDs 11, 13, and 15 show NULL for loyalty_tier because they have no matching orders in the orders table.

Drop a column

Remove columns that are no longer needed. The column is removed from the current schema, but data in existing files remains untouched and simply becomes invisible to queries.

Verify the current schema:

SHOW TABLE dev.demo_iceberg.customer;

Figure 6: SHOW TABLE output showing the current customer table schema before dropping loyalty_tier

Drop the column:

-- Drop the loyalty_tier column
ALTER TABLE dev.demo_iceberg.customer
DROP COLUMN loyalty_tier;

Verify the schema change:

SHOW TABLE dev.demo_iceberg.customer;

Figure 7: SHOW TABLE output showing the customer table schema after loyalty_tier is dropped

Verify the column is dropped:

SELECT * FROM dev.demo_iceberg.customer
ORDER BY customer_id;

Figure 8: Query results confirming the loyalty_tier column has been dropped

Note: To drop a column used in the current partition spec, first drop or replace the partition field, then drop the column.

Rename a column

Rename a column without affecting data types or partition specs:

-- Rename city to location
ALTER TABLE dev.demo_iceberg.customer
RENAME COLUMN city TO location;

The following output confirms the column has been renamed to location:

SELECT customer_id, customer_name, location
FROM dev.demo_iceberg.customer
ORDER BY customer_id;

Figure 9: Query results showing the renamed column location

Widen a column type

Widen a column’s data type without rewriting data. This is useful when your data outgrows the original precision, for example when order amounts exceed the original decimal range.

Verify the current column type:

SHOW TABLE "iceberg-write-blog@s3tablescatalog".iceberg_write_namespace.orders;

Figure 10: SHOW TABLE output showing total_order_amt as DECIMAL(10,2)

Now run the ALTER to widen the column:

-- Widen total_order_amt to support larger order values
ALTER TABLE "iceberg-write-blog@s3tablescatalog".iceberg_write_namespace.orders
ALTER COLUMN total_order_amt TYPE DECIMAL(18,2);

Verify the updated column type:

SHOW TABLE "iceberg-write-blog@s3tablescatalog".iceberg_write_namespace.orders;

Figure 11: SHOW TABLE output confirming total_order_amt widened to DECIMAL(18,2)

Note: Amazon Redshift supports safe type promotions (for example, INT to BIGINT, FLOAT to DOUBLE, DECIMAL(10,2) to DECIMAL(18,2)). Plan column types accordingly for future growth.

Set table properties

Change the compression type for future writes:

Verify the current compression type:

SHOW TABLE "iceberg-write-blog@s3tablescatalog".iceberg_write_namespace.orders;

Figure 12: SHOW TABLE output showing the current compression type before the update

Now run the ALTER to change the compression type:

-- Switch to zstd compression for better ratios
ALTER TABLE "iceberg-write-blog@s3tablescatalog".iceberg_write_namespace.orders
SET TABLE PROPERTIES ('compression_type'='zstd');

The following SHOW TABLE output confirms the updated compression setting:

SHOW TABLE "iceberg-write-blog@s3tablescatalog".iceberg_write_namespace.orders;

Figure 13: SHOW TABLE output showing compression_type set to zstd

Note: This affects only future writes. Existing data files retain their original compression.

Partition evolution

A powerful feature of Iceberg is partition evolution, the ability to change how a table is partitioned without rewriting existing data. Amazon Redshift writes new data with the updated partition scheme, while existing data remains in the old layout. Query engines handle both layouts transparently.

Adding a partition field

The orders table from Part 1 is partitioned by DAY(order_date). Add an additional bucket partition to distribute data across hash buckets:

Verify the current partition spec:

SHOW TABLE "iceberg-write-blog@s3tablescatalog".iceberg_write_namespace.orders;

Figure 14: SHOW TABLE output showing the current partition spec before adding a partition field

Add the partition field:

-- Add bucket partitioning on customer_id
ALTER TABLE "iceberg-write-blog@s3tablescatalog".iceberg_write_namespace.orders
ADD PARTITION FIELD bucket(16, customer_id);

After this change, new data is partitioned by both DAY(order_date) and bucket(16, customer_id), while existing data remains in the original day-only layout.

Verify the updated spec:

SHOW TABLE "iceberg-write-blog@s3tablescatalog".iceberg_write_namespace.orders;

Figure 15: SHOW TABLE output showing the updated partition spec with DAY(order_date) and bucket(16, customer_id)

Replacing a partition field

Instead of separately dropping and adding, use REPLACE PARTITION FIELD as a single atomic operation. This is the recommended approach when swapping one transform for another on the same source column, because it makes the intent explicit and avoids a transient state where the table is unpartitioned between operations.

Verify the current partition spec:

SHOW TABLE "iceberg-write-blog@s3tablescatalog".iceberg_write_namespace.orders;

Figure 16: SHOW TABLE output showing the current partition spec with DAY(order_date) and bucket(16, customer_id)

Replace the partition field:

-- Replace daily partitioning with monthly
ALTER TABLE "iceberg-write-blog@s3tablescatalog".iceberg_write_namespace.orders
REPLACE PARTITION FIELD DAY(order_date) WITH MONTH(order_date);

After this change:

• Existing data remains in day-based partition folders.
• Amazon Redshift writes new data into month-based partition folders.
• The query engine reads both layouts transparently.

Confirm the new partition spec:

SHOW TABLE "iceberg-write-blog@s3tablescatalog".iceberg_write_namespace.orders;

Figure 17: SHOW TABLE output confirming the partition field replaced with MONTH(order_date)

Insert new data and verify that both partition layouts are queryable:

-- New data follows monthly partitioning
INSERT INTO "iceberg-write-blog@s3tablescatalog".iceberg_write_namespace.orders
(order_date, order_id, customer_id, total_order_amt, total_order_tax_amt,
tax_pct, order_created_at_tz, is_active_ind)
VALUES
('2025-01-15', 1018, 3, 210.00, 16.80, 0.08, '2025-01-15 09:00:00-06:00', true);
-- Query spans both old (daily) and new (monthly) layouts transparently
SELECT order_id, order_date, total_order_amt
FROM "iceberg-write-blog@s3tablescatalog".iceberg_write_namespace.orders
WHERE order_date >= '2024-11-01'
ORDER BY order_date;

Figure 18: Query results spanning both partition layouts

Converting to a multi-level partition

Iceberg supports multi-level (composite) partition specs, where data is organized by more than one partition field. You can evolve an existing single-level spec into a multi-level spec by adding partition fields one at a time. Each ADD PARTITION FIELD is a lightweight metadata operation, and no data is rewritten.

The orders table is currently partitioned by MONTH(order_date) and bucket(16, customer_id). Add one more partition field to create a three-level spec:

Verify the current partition spec:

SHOW TABLE "iceberg-write-blog@s3tablescatalog".iceberg_write_namespace.orders;

Figure 19: SHOW TABLE output showing the current two-level partition spec of MONTH(order_date) and bucket(16, customer_id)

Add partition field to build the three-level spec:

-- Add a day-level partition field on order_created_at_tz
ALTER TABLE "iceberg-write-blog@s3tablescatalog".iceberg_write_namespace.orders
ADD PARTITION FIELD day(order_created_at_tz);

Verify the new multi-level partition spec:

SHOW TABLE "iceberg-write-blog@s3tablescatalog".iceberg_write_namespace.orders;

Figure 20: SHOW TABLE output showing the three-level partition spec of MONTH(order_date), bucket(16, customer_id), and day(order_created_at_tz)

After these changes:

• Existing data remains in the original single-level layout (month-based folders).
• Amazon Redshift writes new data into the multi-level layout (month, then bucket, then day folders).
• The query engine reads both layouts transparently.

Dropping partition fields from a multi-level partition

You can also evolve in the other direction by removing partition fields from a multi-level spec to simplify the partition layout. Like adding fields, dropping a partition field is a metadata-only operation and removes one field per statement.

Verify the current multi-level partition spec:

SHOW TABLE "iceberg-write-blog@s3tablescatalog".iceberg_write_namespace.orders;

Figure 21: SHOW TABLE output showing the three-level partition spec before dropping fields

Drop the partition fields one at a time:

-- Drop the bucket partition field
ALTER TABLE "iceberg-write-blog@s3tablescatalog".iceberg_write_namespace.orders
DROP PARTITION FIELD bucket(16, customer_id);
-- Drop the day-level partition field
ALTER TABLE "iceberg-write-blog@s3tablescatalog".iceberg_write_namespace.orders
DROP PARTITION FIELD day(order_created_at_tz);

Verify the table is back to its original single-level spec:

SHOW TABLE "iceberg-write-blog@s3tablescatalog".iceberg_write_namespace.orders;

Figure 22: SHOW TABLE output confirming the table is back to a single-level MONTH(order_date) partition spec

After dropping a partition field:

• Data written under the dropped field’s layout stays in place and remains queryable.
• Amazon Redshift writes new data using only the remaining partition fields.
• Queries that filtered on the dropped field still work, but they no longer benefit from partition pruning on that field for newly written data.

Supported partition transforms

The following table lists the partition transforms available for Iceberg tables in Amazon Redshift:

Note: A column that is already part of an existing partition field can’t be used in a new partition field. Drop or replace the existing field first.

Accessing S3 Tables with external schemas

Lake Formation resource links provide cross-engine access to your S3 Tables through centralized governance. You create a resource link in the default AWS Glue Data Catalog that points to your S3 Tables database. Amazon Redshift, Amazon Athena, Amazon EMR, and other engines can then discover and query the tables using a single permission model.

Figure 23: S3 Tables integration with AWS Glue Data Catalog and Lake Formation

For the complete setup walkthrough, including Lake Formation prerequisites, resource link creation, and permission grants, see Optimize Amazon S3 Tables queries with Amazon Redshift. For conceptual details on resource links and S3 Tables catalog integration, see About resource links and Creating an S3 Tables catalog.

The following steps show how to query S3 Tables through a resource link after completing the setup from the referenced blog.

Grant access to the resource link

In the Lake Formation console, the resource link appears as a database named iceberg_write_blog_rl (type: Resource link). To grant access to the resource link:

1. In the Lake Formation console, choose Databases.
2. Locate iceberg_write_blog_rl (type: Resource link).
3. Choose Actions, then Grant.
4. Grant DESCRIBE permission to RedshiftIcebergRole.

Create an external schema

With the resource link in place, create an external schema in Amazon Redshift for two-part notation access.

For IAM federated users:

CREATE EXTERNAL SCHEMA s3tables_iceberg
FROM DATA CATALOG
DATABASE 'iceberg_write_blog_rl'
CATALOG_ID '<ACCOUNT_ID>'
IAM_ROLE 'SESSION';

For database users and business intelligence (BI) tools:

CREATE EXTERNAL SCHEMA s3tables_iceberg
FROM DATA CATALOG
DATABASE 'iceberg_write_blog_rl'
IAM_ROLE 'arn:aws:iam::<ACCOUNT>:role/RedshifticebergRole';

Grant access to specific users or roles:

-- Grant to the IAM role used in this walkthrough
GRANT USAGE ON SCHEMA s3tables_iceberg TO "IAMR:RedshifticebergRole";

Figure 24: S3 Tables available through an external schema

Query with two-part notation

With the external schema created, query S3 Tables using two-part notation:

-- Instead of: "iceberg-write-blog@s3tablescatalog".iceberg_write_namespace.orders
SELECT * FROM s3tables_iceberg.orders;

Figure 25: Query results from S3 Tables through an external schema using two-part notation

Access methods comparison

The following table compares the available methods for accessing Iceberg tables in Amazon Redshift:

Bringing it together

Combine schema evolution with cross-engine access in a single workflow. The following example adds a column to the orders table and immediately queries it through the external schema:

-- 1. Add a column to the S3 Tables orders table
ALTER TABLE s3tables_iceberg.orders
ADD COLUMN fulfillment_status VARCHAR;
-- 2. Update the new column
UPDATE s3tables_iceberg.orders
SET fulfillment_status = 'shipped'
WHERE order_date < '2024-11-01';
UPDATE s3tables_iceberg.orders
SET fulfillment_status = 'pending'
WHERE order_date >= '2024-11-01';
-- 3. Query immediately via the external schema (no schema recreation needed)
SELECT o.order_id, o.order_date, o.fulfillment_status, c.customer_name
FROM s3tables_iceberg.orders o JOIN demo_iceberg.customer c
ON o.customer_id = c.customer_id
ORDER BY o.order_date DESC;

Figure 26: Cross-catalog join showing the evolved schema immediately visible through the external schema

The new column is visible through both the three-part notation and the external schema without any additional configuration, because the schema evolution in Iceberg propagates automatically.

Best practices

• Test ALTER operations in non-production first. While metadata-only, schema changes affect all readers immediately.
• Use REPLACE PARTITION FIELD instead of DROP + ADD. The atomic operation avoids a transient unpartitioned state.
• Monitor partition spec changes with SHOW TABLE. Verify the current spec after any partition evolution.
• Choose partition transforms based on query patterns. Use month() or day() for time-range filters. Use bucket() for high-cardinality join keys.
• Set table properties before bulk loads. Change compression type (zstd for better ratios, snappy for speed) before large INSERT operations.
• Run table maintenance after mutations. After performing multiple UPDATE, DELETE, or MERGE operations, run AWS Glue table optimizers to compact deletion files and improve read performance.
• Use Lake Formation for fine-grained access. Column-level and row-level security can be applied through Lake Formation on tables accessed through resource links.
• Grant schema access to specific users or roles. Avoid granting to PUBLIC. Use named IAM roles or database users for least-privilege access.
• Monitor query performance. Use Amazon Redshift query monitoring features to track performance of write operations and optimize partitioning strategies as needed.

Considerations

Keep the following in mind when working with ALTER TABLE and partition evolution on Iceberg tables:

• Plan for metadata-only behavior. ALTER TABLE operations update metadata instantly, and existing data files remain unchanged. All readers see the new schema immediately after the operation completes.
• Drop partition fields before dropping partitioned columns. To remove a column used in the current partition spec, first drop or replace the partition field, then drop the column.
• Use safe type promotions for ALTER COLUMN TYPE. Amazon Redshift supports widening within compatible families (INT to BIGINT, FLOAT to DOUBLE, DECIMAL(10,2) to DECIMAL(18,2)). Plan column types with future growth in mind.
• Account for mixed partition layouts after evolution. Partition evolution doesn’t re-partition existing data. Old files remain in their original layout, and the query engine reads both layouts transparently.
• Use external schemas for database user access. The auto-mounted three-part notation ("bucket@s3tablescatalog") requires IAM federated authentication. For database users and BI tools, create an external schema with an explicit IAM role.
• Use full three-part notation with awsdatacatalog. The USE statement isn’t supported with awsdatacatalog, so always specify the full path.
• Clean up S3 data separately after dropping tables. Dropping an Iceberg table removes only the catalog entry from AWS Glue Data Catalog. Delete the underlying S3 data files separately, or use AWS Glue table optimizers to remove orphaned files.

Clean up

To avoid ongoing charges, run the following:

-- Drop the fulfillment_status column added during testing
ALTER TABLE "iceberg-write-blog@s3tablescatalog".iceberg_write_namespace.orders
DROP COLUMN fulfillment_status;
-- Restore original partition spec (if changed)
ALTER TABLE "iceberg-write-blog@s3tablescatalog".iceberg_write_namespace.orders
REPLACE PARTITION FIELD MONTH(order_date) WITH DAY(order_date);
-- Drop external schema
DROP SCHEMA IF EXISTS s3tables_iceberg;

Conclusion

In this post, you evolved Apache Iceberg table schemas using ALTER TABLE operations. You added, dropped, and renamed columns, widened data types, changed compression, and evolved partition specs, all as metadata-only operations without rewriting data. You also created Lake Formation resource links to provide governed cross-engine access to S3 Tables, and simplified query syntax with external schemas.

This concludes the three-part series on getting started with Apache Iceberg write support in Amazon Redshift:

1. Part 1: Create Iceberg tables and perform INSERT operations.
2. Part 2: Run DELETE, UPDATE, and MERGE for row-level modifications.
3. Part 3: Evolve schemas with ALTER TABLE and add cross-engine access with Lake Formation resource links.

If you have questions or feedback about this series, leave a comment on this post.

Additional resources

• Amazon Redshift Iceberg integration – Complete syntax reference.
• Writing to Apache Iceberg tables – Detailed examples.
• ALTER TABLE for Iceberg – Full ALTER reference.
• Amazon S3 Tables – Managed Iceberg storage.
• AWS Lake Formation – Centralized data governance.
• Optimize S3 Tables queries with Amazon Redshift – Resource links and performance tuning.

About the authors

Post Syndicated from Ricardo Serafim original https://aws.amazon.com/blogs/big-data/upgrade-amazon-redshift-dc2-clusters-to-the-new-amazon-redshift-rg/

When you upgrade your Amazon Redshift DC2 (Dense Compute) clusters to RG instances powered by AWS Graviton, you gain access to capabilities that were never available on DC2. These include managed storage, data sharing, zero-ETL integrations, streaming ingestion, and faster query compilation. You also gain availability zone (AZ) features such as cross-AZ cluster relocation for disaster recovery (DR) and concurrency scaling for writes. RG also adds a built-in data lake engine for querying Apache Iceberg and Parquet tables directly on your cluster nodes.

This post covers the new features you gain when upgrading from DC2 to RG, the node mapping guidance for sizing your new cluster, the upgrade methods available, and validation options including Amazon Redshift Test Drive.

Why upgrade from DC2 to RG instances

As data volumes grow, DC2 customers face a choice: add extra compute nodes only to get more storage, or offload data elsewhere. The local SSD capacity on each node is fixed, and there is no managed storage tier to absorb growth. Both RA3 and RG instances solve this with Amazon Redshift Managed Storage, which decouples storage from compute. You can scale data volume independently of node count, paying only for the storage you use with no fixed ceiling per node. This means you no longer need to over-provision compute to accommodate data growth.

RG is the recommended upgrade path over RA3. RG instances run on AWS Graviton processors, delivering higher throughput for data warehouse and data lake workloads at a lower price per vCPU compared to RA3. Because both RA3 and RG share the same managed storage architecture and feature set, RG provides more performance for less cost. For current pricing details, visit Amazon Redshift pricing.

Amazon Redshift RG instances run on AWS Graviton processors. These processors provide more compute cores and lower memory latency compared to the previous-generation hardware behind DC2. This can translate to faster query execution for data warehouse workloads, particularly for large scans where memory throughput is the bottleneck. Exact performance improvements depend on workload characteristics, cluster size, and query complexity. Use Redshift Test Drive to measure the difference for your specific workload.

Data lake access: New with RG

DC2 clusters can query data in Amazon Simple Storage Service (Amazon S3) through Amazon Redshift Spectrum. However, Spectrum adds a per-TB scanning cost on top of your cluster pricing, and does not support enhanced VPC routing on DC2 provisioned clusters (requiring additional configuration for secure S3 access).

RG addresses these constraints with an integrated data lake engine that processes queries directly on your cluster’s dedicated compute nodes:

With RG, you can consolidate warehouse and data lake workloads on a single cluster with no extra per-query charges for data lake access.

Features available with RG

Upgrading from DC2 to RG gives you access to the full set of modern Amazon Redshift capabilities. Three of the most impactful for DC2 customers are data sharing, zero-ETL integrations, and managed storage. With data sharing, you can query live data from other Amazon Redshift clusters or accounts without copying or moving data, reducing storage duplication and keeping consumers always up to date. Zero-ETL integrations automatically replicate data from Amazon Aurora, Amazon Relational Database Service (Amazon RDS), and Amazon DynamoDB into Amazon Redshift without building or maintaining ETL pipelines. This reduces operational overhead and data freshness lag. Managed storage scales independently from compute, so you can grow your data without adding nodes and only pay for the storage you use.

Additional capabilities available with RG:

• Streaming ingestion – ingest data from Amazon Kinesis Data Streams and Amazon Managed Streaming for Apache Kafka (Amazon MSK) in near real-time, so you can build dashboards and analyze the latest data without batch delays.
• Concurrency scaling for writes – automatically add transient capacity during burst write workloads, so ingest operations don’t slow down your analytical queries.
• Cross-AZ cluster relocation – relocate your cluster to another Availability Zone with no endpoint changes, supporting disaster recovery without the cost of a standby cluster.
• Multi-AZ deployments – run your cluster across multiple Availability Zones as a single database delivering high availability (HA) and automatic failover without a passive standby.
• Faster query compilation – queries compile faster on Graviton processors, reducing cold-start latency for new or modified queries.

RG instance details and node mapping

This table shows the available RG instance configurations:

For current pricing, visit Amazon Redshift pricing for more information.

DC2 to RG node mapping guidance

Use this table to determine the recommended starting configuration when upgrading from DC2:

Extra nodes might be needed depending on workload requirements. Add or remove nodes based on the compute requirements of your required query performance. Validate your specific configuration using Redshift Test Drive before migrating production workloads.

Prerequisites

Before starting the upgrade, confirm the following:

• Snapshot availability — a recent snapshot of your DC2 cluster is required for all upgrade methods. If automated snapshots are disabled, create a manual snapshot before starting. Visit Amazon Redshift snapshots for more information.
• Network configuration — verify that your virtual private cloud (VPC), subnet groups, and security groups are configured to support the new RG cluster. If you use enhanced VPC routing, confirm your S3 endpoint and route table configuration. Visit Enhanced VPC routing for more information.
• Cluster version — your DC2 cluster must be running a supported Amazon Redshift version. Check the release notes for minimum version requirements.

Upgrade methods

Three methods are available for migrating from DC2 to RG instances. The right choice depends on your operational constraints: whether you need write access during migration, whether the target configuration supports elastic resize, and how much downtime your workload can tolerate.

Elastic resize (recommended)

Elastic resize is the fastest and most efficient path. Amazon Redshift creates a snapshot, provisions the RG cluster, and redirects the endpoint automatically. The cluster remains in read-only mode for a few minutes during the operation, and the endpoint doesn’t change, meaning no application-side updates are required. This is the recommended method when the target configuration is supported by elastic resize.

Classic resize

Use classic resize when the target configuration is not available through elastic resize, or when you need data slice rebalancing. Downtime is similar to elastic resize (a few minutes of read-only mode in Stage 1). In Stage 2, data redistributes to its original distribution patterns in the background without blocking queries. The advantage of classic resize is that it rebalances data slices evenly across nodes. This matters when you move to a different node type that might require a different number of slices. Stage 2 can take time on busy clusters, and the duration depends on data volume, cluster utilization, and target cluster size. Queries might run slower until redistribution completes.

Snapshot and restore with cluster identifier swap

This method uses snapshot and restore of the existing DC2 cluster to provision a new RG cluster with a different identifier. After validating the new cluster, you swap the cluster identifiers to redirect application traffic without changing the endpoint. This approach provides these benefits:

• Test and validate the RG cluster while the DC2 cluster continues serving production traffic.
• Roll back by reversing the identifier swap if issues arise.
• No application-side endpoint changes required after the swap.

The trade-off is that data written to the source cluster after the snapshot requires manual synchronization before the cutover. If your migration plan includes a write-freeze window, you can take the final snapshot at the start of that window and avoid synchronization entirely.

This AWS Command Line Interface (AWS CLI) command illustrates restoring a DC2 snapshot to an RG cluster:

aws redshift restore-from-cluster-snapshot \
    --cluster-identifier my-cluster-rg \
    --snapshot-identifier my-dc2-snapshot \
    --node-type rg.4xlarge \
    --number-of-nodes 3 \
    --cluster-subnet-group-name my-subnet-group \
    --vpc-security-group-ids sg-abc123 \
    --cluster-parameter-group-name my-param-group \
    --port 5439 \
    --no-publicly-accessible \
    --enhanced-vpc-routing \
    --iam-roles 'arn:aws:iam::111122223333:role/RedshiftRole'

After restoring, validate your workload on the new cluster. When ready, swap the cluster identifiers:

aws redshift modify-cluster \
    --cluster-identifier my-cluster \
    --new-cluster-identifier my-cluster-dc2-old

aws redshift modify-cluster \
    --cluster-identifier my-cluster-rg \
    --new-cluster-identifier my-cluster

Validating your target configuration

Before migrating production clusters, validate that your target RG configuration meets performance requirements. There are several ways to approach this depending on your needs:

Run your existing QA process on a test cluster. Create an RG cluster from a snapshot, then execute the same test suites and validation scripts you would use for any code or infrastructure change. This approach helps confirm basic compatibility and catch regressions.

Use lower environments first. Migrate your development or staging clusters to RG before production. This gives your team hands-on experience with the new instance type and surfaces any configuration differences in a low-risk setting.

Replay production workloads with Redshift Test Drive. For production-level validation with real traffic patterns, Redshift Test Drive is an open source utility that automates workload replay across multiple target configurations. It extracts queries from your source cluster’s audit logs and replays them against the target, then provides a comparison UI for latency, errors, and deviation.

For a detailed walkthrough, read Find the best Amazon Redshift configuration for your workload using Redshift Test Drive.

Best practices

Before migrating, run Amazon Redshift Advisor on your current cluster to identify optimization opportunities such as unused tables, missing sort keys, or distribution style changes. Drop unnecessary tables to reduce data transfer time, and schedule the migration during off-peak hours for minimal business impact. Removing tables that are no longer used (for example, tables with suffixes like _bkp, _tmp, or _old) also speeds up classic resize. These unused tables would otherwise be rebalanced across nodes during Stage 2, adding time to a process that delivers no value for data no one queries.

During migration, communicate the cutover window to stakeholders. Because the DC2 cluster remains active until the identifier swap, coordinate a brief write-freeze period before the final snapshot to minimize data synchronization effort.

After migration, monitor the cluster for 48–72 hours to identify any performance deviations and adjust node count if needed. Update your runbooks and operational documentation with the new cluster details, node types, and any endpoint changes if you used the snapshot and restore method. Once the migration is considered successful you may delete the DC2 cluster.

Conclusion

Upgrading from Amazon Redshift DC2 to RG instances powered by AWS Graviton gives you a Graviton-based architecture with managed storage and improved query performance. It also gives you access to the full suite of Amazon Redshift features that were never available on DC2: data lake queries, data sharing, zero-ETL, faster query compilation, and cross-AZ relocation. The snapshot restore and cluster identifier swap method provides a safe migration path with built-in rollback. Use Redshift Test Drive to validate your target configuration with real workload data before committing.

To get started, review the RG instance availability and pricing, determine your target configuration using the node mapping guidance, and run Redshift Test Drive against your production workload.

Related resources

• Meet Amazon Redshift RG – AWS Graviton-based instances with an integrated data lake query engine delivering up to 2.4x better performance at 30% lower price than RA3
• Amazon Redshift introduces AWS Graviton-based RG instances with an integrated data lake query engine
• Unlock cost savings with incremental snapshot billing for Amazon Redshift Serverless and Amazon Redshift RG
• Find the best Amazon Redshift configuration for your workload using Redshift Test Drive
• Redshift Test Drive on GitHub

About the authors