Set-up cross account Zero-ETL Integration in the same region

Short description

A zero-ETL integration makes the data in your Aurora DB cluster available in Amazon Redshift within minutes of the data updates being written. Once that data is in Amazon Redshift, you can power your analytics, ML, and AI workloads using the built-in capabilities of Amazon Redshift, such as machine learning, materialized views, data sharing, federated access to multiple data stores and data lakes, and integrations with Amazon SageMaker, Amazon QuickSight, and other AWS services.

Solution overview

To create a cross-account zero-ETL integration, you specify an Aurora DB cluster as the source in an AWS account, and an Amazon Redshift data warehouse as the target in a different AWS account in the same region. The integration replicates data from the source database into the target data warehouse.

Zero-ETL Source

Please refer this documentation to setup the source.

Zero-ETL Target

Please refer this documentation to setup the target.

Security IAM

Step 1: Create IAM Policy and Role in Source Account

1. Create IAM Policy in Source Account:
   • Navigate to IAM console in the source account
   • Create a new policy with the following JSON (Replace region, source-db-account-id, target-db-account-id, source-db-identifier, target-db-namespace-uuid and source-db-role with actual values):

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": [
                "rds:CreateIntegration"
            ],
            "Resource": [
                "arn:aws:rds:{region}:{source-db-account-id}:cluster:{source-db-identifier}",
                "arn:aws:rds:{region}:{source-db-account-id}:integration:*"
            ]
        },
        {
            "Effect": "Allow",
            "Action": [
                "rds:DescribeIntegrations"
            ],
            "Resource": [
                "*"
            ]
        },
        {
            "Effect": "Allow",
            "Action": [
                "rds:DeleteIntegration"
            ],
            "Resource": [
                "arn:aws:rds:{region}:{source-db-account-id}:integration:*"
            ]
        },
        {
            "Effect": "Allow",
            "Action": [
                "redshift:CreateInboundIntegration"
            ],
            "Resource": [
                "arn:aws:redshift-serverless:{region}:{target-db-account-id}:namespace/{target-db-namespace-uuid}"
            ]
        }
    ]
}

2. Create IAM Role in Source Account:
   • Navigate to IAM console in the source account
   • Create a new role and attach the policy created in step 1
   • Add the following trust policy to allow the role to be assumed:

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Principal": {
                "AWS": [
                    "arn:aws:iam::{target-db-account-id}:root"
                ]
            },
            "Action": "sts:AssumeRole"
        }
    ]
}

Step 2: Create IAM Policy and Role in Target Account

1. Create IAM Policy in Target Account:
   • Navigate to IAM console in the target account
   • Create a new policy with the following JSON:

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": [
                "redshift:DescribeClusters",
                "redshift-serverless:ListNamespaces"
            ],
            "Resource": [
                "*"
            ]
        }
    ]
}

2. Create IAM Role in Target Account:
   • Navigate to IAM console in the target account
   • Create a new role and attach the policy created above
   • Add the following trust policy to allow cross-account access:

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Principal": {
                "AWS": [
                    "arn:aws:iam::{source-db-account-id}:root",
                    "arn:aws:iam::{source-db-account-id}:role/{source-db-role}"
                ]
            },
            "Action": "sts:AssumeRole"
        }
    ]
}

Zero-ETL Integration

1. Under Step1 Getting started, for Integration identifier, enter an integration name of your choice, for example cross-account-integration.

2. Under Step2 Select source, click on Browse RDS databases and choose the source aurora mysql cluster.

3. Capture the Redshift Serverless namespace ARN from the target account.

4. Under Step3 Select target, for Aws account select Use the different account and enter the Redshift Serverless destination namespace.

5. Under optional Step4 Add tags and encryption, take no action and click Next.

6. Under Step5 Review and create, scroll to the bottom, and click on Create zero-ETL integration.

7. Login to target account and create a database from the integration to start querying.

Monitoring System Tables

Use these system tables to monitor your zero-ETL integration:

• SVV_INTEGRATION - Shows information about zero-ETL integrations including status and configuration details
• SVV_INTEGRATION_TABLE_MAPPING - Displays the mapping between source tables and target tables in the integration
• SVV_INTEGRATION_TABLE_STATE - Provides the current state and synchronization status of individual tables in the integration