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

1. Create a role in the source account with below policy (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:DescribeIntegration"
            ],
            "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 a role in the target account with below policy.

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

And update trust policy as below,

{
    "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.