[AWS CDK] I tried connecting Salesforce data to Iceberg Table using AWS Glue zero-ETL

CloudFormation (cfn/prerequisites.yaml)
  ├── S3 Data Lake Bucket
  ├── Source IAM Role
  ├── Glue Database
  └── Secrets Manager

AWS Console (manual)
  └── Salesforce Connection (AWS Managed App)

Shell Script (scripts/setup-prereqs.sh)
  └── Catalog Resource Policy

CDK (cdk/)
  ├── Target IAM Role
  ├── Integration Resource Property
  └── Zero-ETL Integration

59_salesforce_glue_zeroetl/
├── cdk/                    # CDK infrastructure code
│   ├── bin/app.ts
│   ├── lib/
│   │   ├── parameter.ts
│   │   └── main-stack.ts
│   ├── package.json
│   └── tsconfig.json
├── cfn/
│   └── prerequisites.yaml  # CloudFormation prerequisite resources
├── scripts/
│   ├── config.sh
│   ├── setup-prereqs.sh
│   └── cleanup-prereqs.sh
└── README.md

AWSTemplateFormatVersion: "2010-09-09"
Description: Prerequisites for Salesforce Zero-ETL Integration

Parameters:
  ProjectName:
    Type: String
    Default: my-sf-zeroetl
    Description: Project name prefix
  EnvName:
    Type: String
    Default: dev
    Description: Environment name

Resources:
  # ========================================
  # S3 Data Lake Bucket
  # ========================================
  DataLakeBucket:
    Type: AWS::S3::Bucket
    DeletionPolicy: Delete
    Properties:
      BucketName: !Sub ${ProjectName}-${EnvName}-datalake
      BucketEncryption:
        ServerSideEncryptionConfiguration:
          - ServerSideEncryptionByDefault:
              SSEAlgorithm: AES256
      PublicAccessBlockConfiguration:
        BlockPublicAcls: true
        BlockPublicPolicy: true
        IgnorePublicAcls: true
        RestrictPublicBuckets: true

  # ========================================
  # Secrets Manager (for OAuth token storage)
  # ========================================
  SalesforceOAuthSecret:
    Type: AWS::SecretsManager::Secret
    DeletionPolicy: Delete
    UpdateReplacePolicy: Delete
    Properties:
      Description: Stores OAuth tokens for Salesforce connection (auto-populated by AWS Glue)
      SecretString: "{}"

  # ========================================
  # Source IAM Role (for Salesforce Connection)
  # ========================================
  ZeroETLSourceRole:
    Type: AWS::IAM::Role
    Properties:
      RoleName: !Sub ${ProjectName}-${EnvName}-source-role
      AssumeRolePolicyDocument:
        Version: "2012-10-17"
        Statement:
          - Effect: Allow
            Principal:
              Service: glue.amazonaws.com
            Action: sts:AssumeRole
      Policies:
        - PolicyName: GlueConnectionAccess
          PolicyDocument:
            Version: "2012-10-17"
            Statement:
              - Effect: Allow
                Action:
                  - glue:GetConnection
                  - glue:GetConnections
                  - glue:ListConnectionTypes
                  - glue:DescribeConnectionType
                  - glue:RefreshOAuth2Tokens
                  - glue:ListEntities
                  - glue:DescribeEntity
                Resource: "*"
        - PolicyName: SecretsManagerAccess
          PolicyDocument:
            Version: "2012-10-17"
            Statement:
              - Effect: Allow
                Action:
                  - secretsmanager:DescribeSecret
                  - secretsmanager:GetSecretValue
                  - secretsmanager:PutSecretValue
                Resource: "*"

  # ========================================
  # Glue Database (for Zero-ETL target)
  # ========================================
  GlueDatabase:
    Type: AWS::Glue::Database
    Properties:
      CatalogId: !Ref AWS::AccountId
      DatabaseInput:
        Name: !Sub
          - ${ProjectNameUnderscore}_${EnvName}
          - ProjectNameUnderscore: !Join ["_", !Split ["-", !Ref ProjectName]]
        Description: Zero-ETL target database with Iceberg tables
        LocationUri: !Sub s3://${DataLakeBucket}/data/

Outputs:
  DataLakeBucketName:
    Value: !Ref DataLakeBucket
  SourceRoleArn:
    Value: !GetAtt ZeroETLSourceRole.Arn
  GlueDatabaseName:
    Value: !Sub
      - ${ProjectNameUnderscore}_${EnvName}
      - ProjectNameUnderscore: !Join ["_", !Split ["-", !Ref ProjectName]]
  SalesforceOAuthSecretName:
    Value: !Ref SalesforceOAuthSecret
    Description: Select this secret in Glue Console when creating Salesforce connection

#!/bin/bash
set -e

SCRIPT_DIR="$(cd "$(dirname "$0")" && pwd)"
source "${SCRIPT_DIR}/config.sh"

# Use REGION from config.sh if set, otherwise use AWS CLI default
if [ -z "$REGION" ]; then
  REGION=$(aws configure get region)
fi
ACCOUNT_ID=$(aws sts get-caller-identity --query Account --output text)

echo "Setting Glue Catalog Resource Policy (Database: ${DATABASE_NAME})..."

# Glue Resource Policy has no CloudFormation support - must use CLI
cat > /tmp/catalog-policy.json << EOF
{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Principal": { "Service": "glue.amazonaws.com" },
      "Action": "glue:AuthorizeInboundIntegration",
      "Resource": "arn:aws:glue:${REGION}:${ACCOUNT_ID}:database/${DATABASE_NAME}"
    },
    {
      "Effect": "Allow",
      "Principal": { "AWS": "arn:aws:iam::${ACCOUNT_ID}:root" },
      "Action": "glue:CreateInboundIntegration",
      "Resource": "arn:aws:glue:${REGION}:${ACCOUNT_ID}:database/${DATABASE_NAME}"
    }
  ]
}
EOF

aws glue put-resource-policy --policy-in-json file:///tmp/catalog-policy.json
rm -f /tmp/catalog-policy.json

echo "Done."

import type { Environment } from 'aws-cdk-lib';

export interface AppParameter {
  env?: Environment;
  envName: string;
  projectName: string;
  syncFrequencyMinutes: number;
  dataFilter: string;
}

export const devParameter: AppParameter = {
  envName: 'dev',
  projectName: 'my-sf-zeroetl',
  syncFrequencyMinutes: 15, // 15 minutes
  // syncFrequencyMinutes: 1440,  // 24 hours
  dataFilter: 'include:Account,include:Contact',
  env: {
    account: process.env.CDK_DEFAULT_ACCOUNT,
    region: process.env.CDK_DEFAULT_REGION,
  },
};

import * as cdk from 'aws-cdk-lib';
import * as glue from 'aws-cdk-lib/aws-glue';
import * as iam from 'aws-cdk-lib/aws-iam';
import * as s3 from 'aws-cdk-lib/aws-s3';
import type { Construct } from 'constructs';
import type { AppParameter } from './parameter';

interface MainStackProps extends cdk.StackProps {
  parameter: AppParameter;
}

export class MainStack extends cdk.Stack {
  constructor(scope: Construct, id: string, props: MainStackProps) {
    super(scope, id, props);

    const { parameter } = props;

    // Resource naming conventions
    const dataLakeBucketName = `${parameter.projectName}-${parameter.envName}-datalake`;
    const databaseName = `${parameter.projectName.replace(/-/g, '_')}_${parameter.envName}`;
    const integrationName = `${parameter.projectName}-${parameter.envName}-integration`;
    const connectionName = `${parameter.projectName}-${parameter.envName}-salesforce-connection`;

    // Reference S3 bucket created by Prerequisites stack
    const dataLakeBucket = s3.Bucket.fromBucketName(this, 'DataLakeBucket', dataLakeBucketName);

    // External resource ARNs (created via CloudFormation / AWS Console)
    const databaseArn = `arn:aws:glue:${this.region}:${this.account}:database/${databaseName}`;
    const connectionArn = `arn:aws:glue:${this.region}:${this.account}:connection/${connectionName}`;
    const sourceRoleArn = `arn:aws:iam::${this.account}:role/${parameter.projectName}-${parameter.envName}-source-role`;

    // ========================================
    // 1. Target IAM Role (for S3/Glue Catalog)
    // ========================================
    const targetRole = new iam.Role(this, 'ZeroETLTargetRole', {
      roleName: `${parameter.projectName}-${parameter.envName}-target-role`,
      assumedBy: new iam.ServicePrincipal('glue.amazonaws.com'),
    });

    // S3 permissions
    dataLakeBucket.grantReadWrite(targetRole);

    // Glue Data Catalog permissions
    targetRole.addToPolicy(
      new iam.PolicyStatement({
        effect: iam.Effect.ALLOW,
        actions: [
          'glue:GetDatabase',
          'glue:GetDatabases',
          'glue:GetTable',
          'glue:GetTables',
          'glue:CreateTable',
          'glue:UpdateTable',
          'glue:DeleteTable',
          'glue:GetPartitions',
          'glue:BatchCreatePartition',
          'glue:BatchDeletePartition',
        ],
        resources: [
          `arn:aws:glue:${this.region}:${this.account}:catalog`,
          `arn:aws:glue:${this.region}:${this.account}:database/${databaseName}`,
          `arn:aws:glue:${this.region}:${this.account}:table/${databaseName}/*`,
        ],
      })
    );

    // CloudWatch Logs permissions
    targetRole.addToPolicy(
      new iam.PolicyStatement({
        effect: iam.Effect.ALLOW,
        actions: ['logs:CreateLogGroup', 'logs:CreateLogStream', 'logs:PutLogEvents'],
        resources: [
          `arn:aws:logs:${this.region}:${this.account}:log-group:/aws-glue/*`,
          `arn:aws:logs:${this.region}:${this.account}:log-group:/aws-glue/*:*`,
        ],
      })
    );

    // ========================================
    // 2. Integration Resource Properties (source and target)
    // ========================================
    // Source: Salesforce Connection with Source Role
    const sourceResourceProperty = new glue.CfnIntegrationResourceProperty(
      this,
      'SourceResourceProperty',
      {
        resourceArn: connectionArn,
        sourceProcessingProperties: {
          roleArn: sourceRoleArn,
        },
      }
    );

    // Target: Glue Database with Target Role
    const targetResourceProperty = new glue.CfnIntegrationResourceProperty(
      this,
      'TargetResourceProperty',
      {
        resourceArn: databaseArn,
        targetProcessingProperties: {
          roleArn: targetRole.roleArn,
        },
      }
    );

    // ========================================
    // 3. Zero ETL Integration
    // ========================================
    // Note: Salesforce Connection must be created via AWS Console before CDK deploy
    const zeroEtlIntegration = new glue.CfnIntegration(this, 'ZeroETLIntegration', {
      integrationName: integrationName,
      sourceArn: connectionArn,
      targetArn: databaseArn,
      description: 'Salesforce to S3 (Iceberg) Zero-ETL Integration',
      dataFilter: parameter.dataFilter,

      integrationConfig: {
        continuousSync: true,
        refreshInterval: `${parameter.syncFrequencyMinutes}`,
      },
      tags: [
        { key: 'Environment', value: parameter.envName },
        { key: 'DataFormat', value: 'Iceberg' },
      ],
    });

    zeroEtlIntegration.node.addDependency(sourceResourceProperty);
    zeroEtlIntegration.node.addDependency(targetResourceProperty);

    // ========================================
    // Outputs
    // ========================================
    new cdk.CfnOutput(this, 'IntegrationArn', {
      value: zeroEtlIntegration.attrIntegrationArn,
      description: 'Zero-ETL Integration ARN',
    });

    new cdk.CfnOutput(this, 'TargetRoleArn', {
      value: targetRole.roleArn,
      description: 'Target Role ARN',
    });
  }
}

aws cloudformation deploy \
  --template-file cfn/prerequisites.yaml \
  --stack-name my-sf-zeroetl-dev-prerequisites \
  --capabilities CAPABILITY_NAMED_IAM \
  --profile YOUR_AWS_PROFILE

cd scripts
AWS_PROFILE=YOUR_AWS_PROFILE ./setup-prereqs.sh

cd cdk
pnpm install
pnpm cdk deploy --all --require-approval never --profile YOUR_AWS_PROFILE

SELECT * FROM "my_sf_zeroetl_dev"."zetl_integration_table_state" limit 10;