Real-time Change Data Capture from Aurora PostgreSQL to Snowflake with Openflow

Key Contributors:

Ankit Mathur, Partner Solutions Architect, AWS

Nidhi Gupta, Senior Partner Solutions Architect, AWS

Introduction

A retail analytics team needs to run dashboards on top of their order data, but the source of truth lives in a transactional database. Every morning, a batch Extract, Transform, Load (ETL) job copies yesterday’s data to the analytics warehouse — meaning the dashboards are always at least 24 hours behind. When a pricing error hits production, the team doesn’t see the impact until the next day. They need the data in near real-time, without adding load to the production database or building custom streaming infrastructure.

Change Data Capture (CDC) solves this problem by reading the database’s write-ahead log (WAL) and streaming every insert, update, and delete to the destination as it happens. Snowflake Openflow is a managed connector platform that handles the CDC pipeline end-to-end. With the Bring Your Own Cloud (BYOC) deployment model, the processing infrastructure runs in your AWS account — data stays within your VPC boundary until it reaches Snowflake.

AWS and Snowflake have partnered to deliver an integrated modern data platform that enables real-time analytics and AI-driven insights. Snowflake Openflow BYOC on AWS combines the operational simplicity of Aurora PostgreSQL for transactional workloads with Snowflake’s data warehouse performance for analytics — while keeping your data processing infrastructure under your control. This joint solution reduces the complexity of building streaming data pipelines and accelerates time-to-insight for data-driven applications.

In this post, we walk through building a complete CDC pipeline from Amazon Aurora PostgreSQL-Compatible Edition (Aurora PostgreSQL) to Snowflake using Openflow BYOC. This is a hands-on technical guide covering every step: Aurora database preparation, Snowflake object creation, CloudFormation deployment, VPC peering with DNS resolution, Lambda-based database setup, connector configuration with exact field values, and verification. We also document two commonly overlooked pitfalls and their fixes.

Solution overview

The solution consists of the following components:

• Aurora PostgreSQL (source) — Runs the transactional database with logical replication enabled. A publication defines which tables to replicate.
• Snowflake Openflow BYOC (processing) — Deploys an Amazon Elastic Kubernetes Service (Amazon EKS) cluster, Amazon Elastic Compute Cloud (Amazon EC2) agent, and supporting infrastructure in your AWS account through AWS CloudFormation. Apache NiFi-based connectors run as Kubernetes pods, reading the PostgreSQL CDC stream and writing to Snowflake through Snowpipe Streaming.
• Snowflake (destination) — Receives the replicated data into a dedicated database. The connector handles schema creation, snapshot loading, journal tables, and incremental merges automatically.
• Amazon VPC peering (networking) — Connects the Openflow VPC to the Aurora VPC, enabling private connectivity without exposing the database to the internet.

Press enter or click to view image in full size

Prerequisites

Before you begin, make sure you have the following in place.

AWS account requirements

• An AWS account with permissions to create AWS CloudFormation stacks, Amazon VPCs, Amazon EKS clusters, AWS Lambda functions, and AWS Identity and Access Management (IAM) roles
• An Aurora PostgreSQL cluster (version 13 or later) with a database and tables to replicate

Snowflake requirements

• A Snowflake account with ACCOUNTADMIN or equivalent privileges
• Access to the Snowflake Openflow console (Snowsight > Data > Ingestion)

Tools and software

• AWS CLI v2 installed and configured
• OpenSSL installed for RSA key generation
• Python 3.9+ (for the Lambda function)
• PostgreSQL JDBC driver (postgresql-42.7.5.jar or later)

Required knowledge

• Familiarity with Aurora PostgreSQL administration and SQL
• Basic understanding of VPC networking, peering, and security groups
• Familiarity with Snowflake database objects and role-based access control
• Basic understanding of AWS Lambda and IAM

Example values used throughout this guide:

Replace these with values appropriate for your environment.

AWS CLI profile: All aws commands in this guide use your default AWS CLI profile. If you use named profiles, ensure the correct one is active:

# Check which profile/account is active 
aws sts get-caller-identity  

# If wrong, set the correct profile for this terminal session 
export AWS_PROFILE=<your-profile-name>

Alternatively, append --profile <your-profile-name> to each aws command.

Step 1: Prepare Aurora PostgreSQL for CDC

CDC from PostgreSQL uses logical replication, which decodes the write-ahead log into a structured stream of row-level changes. This step enables logical replication, creates a publication, and grants the required replication privileges.

Enable logical replication

Run the following commands to create a parameter group and enable logical replication:

# Create a custom cluster parameter group
aws rds create-db-cluster-parameter-group \
    --db-cluster-parameter-group-name cdc-poc-aurora-params \
    --db-parameter-group-family aurora-postgresql16 \
    --description "Enable logical replication for CDC" \
    --region us-west-2

# Enable logical replication
aws rds modify-db-cluster-parameter-group \
    --db-cluster-parameter-group-name cdc-poc-aurora-params \
    --parameters "ParameterName=rds.logical_replication,ParameterValue=1,ApplyMethod=pending-reboot" \
    --region us-west-2

# Apply the parameter group to your cluster
aws rds modify-db-cluster \
    --db-cluster-identifier cdc-poc-aurora \
    --db-cluster-parameter-group-name cdc-poc-aurora-params \
    --region us-west-2

# Reboot the writer instance for the change to take effect
aws rds reboot-db-instance \
    --db-instance-identifier cdc-poc-aurora-writer \
    --region us-west-2

After the reboot completes, verify by connecting to the database and running SHOW wal_level; — it should return "logical".

Create a publication using a Lambda function

Aurora PostgreSQL in private subnets is not directly accessible from your local machine or AWS CloudShell. We use a temporary Lambda function deployed in the Aurora VPC to run the required SQL commands. This approach avoids setting up a bastion host or VPN.

Why Lambda? The Aurora cluster has no public access (a security best practice). Lambda can be deployed in the same VPC and subnets as Aurora, giving it direct network access to the database endpoint. After running the SQL, we delete the Lambda function — it is only needed once.

Create the Lambda execution role

Create an IAM role for the Lambda function with VPC access. Save the following as policy.json:

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": [
                "logs:CreateLogGroup",
                "logs:CreateLogStream",
                "logs:PutLogEvents",
                "ec2:CreateNetworkInterface",
                "ec2:DescribeNetworkInterfaces",
                "ec2:DeleteNetworkInterface"
            ],
            "Resource": "*"
        }
    ]
}

Create the role and attach the policy:

# Create the role
aws iam create-role \
    --role-name lambda-vpc-execution-role \
    --assume-role-policy-document '{"Version":"2012-10-17","Statement":[{"Effect":"Allow","Principal":{"Service":"lambda.amazonaws.com"},"Action":"sts:AssumeRole"}]}'

# Attach the policy
aws iam put-role-policy \
    --role-name lambda-vpc-execution-role \
    --policy-name vpc-access \
    --policy-document file://policy.json

Write the Lambda function

Create a file called lambda_function.py with the following code. Replace the host, dbname, user, and password with your Aurora details:

import json
import psycopg2

def lambda_handler(event, context):
    conn = psycopg2.connect(
        # Replace with your Aurora writer endpoint (found in RDS console or via:
        # aws rds describe-db-clusters --db-cluster-identifier <cluster> --query "DBClusters[0].Endpoint")
        host="<your-aurora-writer-endpoint>",
        port=5432,
        dbname="sales",
        user="cdcadmin",
        password="<your-password>"
    )
    conn.autocommit = True
    cur = conn.cursor()
    # Create publication for all tables
    cur.execute("""
        DO $$
        BEGIN
            IF NOT EXISTS (SELECT 1 FROM pg_publication WHERE pubname = 'openflow_pub') THEN
                CREATE PUBLICATION openflow_pub FOR ALL TABLES;
            END IF;
        END $$;
    """)
    # Grant replication role
    cur.execute("GRANT rds_replication TO cdcadmin;")
    # Verify
    cur.execute("SELECT * FROM pg_publication WHERE pubname = 'openflow_pub';")
    result = cur.fetchall()
    cur.close()
    conn.close()
    return {
        "statusCode": 200,
        "body": json.dumps({"publication": str(result), "status": "success"})
    }

Package and deploy the Lambda function

Package the function with the psycopg2 dependency and deploy it:

# Install psycopg2 binary for Lambda (Amazon Linux 2)
# Requires pip 22.3+. Upgrade with: pip3 install --upgrade pip
mkdir pg_lambda && cd pg_lambda
pip3 install --platform manylinux2014_x86_64 --python-version 3.12 \
    --only-binary=:all: --target . psycopg2-binary
cp lambda_function.py .
zip -r ../pg_lambda.zip .

# Create the Lambda function in the Aurora VPC
aws lambda create-function \
    --function-name openflow-pg-setup \
    --runtime python3.12 \
    --handler lambda_function.lambda_handler \
    --zip-file fileb://pg_lambda.zip \
    --role arn:aws:iam::<account-id>:role/lambda-vpc-execution-role \
    --vpc-config SubnetIds=<aurora-subnet-1>,<aurora-subnet-2>,SecurityGroupIds=<aurora-sg-id> \
    --timeout 30 \
    --region us-west-2

Run and clean up

Invoke the function, verify the result, then delete it:

# Invoke the function
aws lambda invoke \
    --function-name openflow-pg-setup \
    --region us-west-2 \
    /tmp/pg_result.json

# Check the result
cat /tmp/pg_result.json | python3 -m json.tool

# Delete the Lambda function (no longer needed)
aws lambda delete-function \
    --function-name openflow-pg-setup \
    --region us-west-2

The output should show the publication details and status “success”. You can also clean up the lambda-vpc-execution-role IAM role after confirming the publication was created.

Step 2: Configure Snowflake for Openflow

On the Snowflake side, create a dedicated database, warehouse, role, and service user with key pair authentication.

Generate an RSA key pair

Generate an RSA key pair. The connector uses this to authenticate with Snowflake without a password:

# Generate a 2048-bit RSA private key in PKCS8 format with no passphrase
openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out openflow_rsa_key.p8 -nocrypt

# Extract the public key
openssl rsa -in openflow_rsa_key.p8 -pubout -out openflow_rsa_key.pub

# Extract just the key body (without headers) for Snowflake
grep -v "BEGIN\|END" openflow_rsa_key.pub | tr -d '\n'

Create Snowflake objects

Run the following SQL in Snowsight. Each command is explained inline:

-- Run as ACCOUNTADMIN in Snowsight

-- 1. Create a dedicated role for the CDC pipeline
CREATE ROLE IF NOT EXISTS CDC_POC_ROLE;

-- 2. Create the destination database
CREATE DATABASE IF NOT EXISTS CDC_POC_DB;

-- 3. Create a warehouse (XSMALL is sufficient for CDC merges)
CREATE WAREHOUSE IF NOT EXISTS CDC_POC_WH
    WAREHOUSE_SIZE = 'XSMALL'
    AUTO_SUSPEND = 300
    AUTO_RESUME = TRUE;

-- 4. Grant privileges
GRANT USAGE ON WAREHOUSE CDC_POC_WH TO ROLE CDC_POC_ROLE;
GRANT ALL PRIVILEGES ON DATABASE CDC_POC_DB TO ROLE CDC_POC_ROLE;

-- 5. Create a service user with key pair authentication
-- Paste the public key body (without BEGIN/END headers) from the previous step
CREATE USER IF NOT EXISTS OPENFLOW_CDC_USER
    DEFAULT_ROLE = CDC_POC_ROLE
    DEFAULT_WAREHOUSE = CDC_POC_WH
    RSA_PUBLIC_KEY = 'MIIBIjANBgkqhki...your-public-key-here...IDAQAB';
GRANT ROLE CDC_POC_ROLE TO USER OPENFLOW_CDC_USER;

-- 6. Verify the setup
DESCRIBE USER OPENFLOW_CDC_USER;
SELECT CURRENT_ROLE();

Step 3: Download and deploy the Openflow BYOC CloudFormation template

Download the template from Snowflake

Follow these steps in Snowsight to get the CloudFormation template:

1. Navigate to Data > Ingestion > Openflow.
2. Click Create Deployment and select BYOC as the deployment model.
3. Select your cloud provider (AWS) and region (us-west-2).
4. Snowflake generates a CloudFormation template pre-configured with your account details (organization, account name, data plane key). Click Download Template.
5. Save the YAML file locally (for example, aws-rds-openflow.yaml).

Review the template (no modifications needed)

The downloaded template is ready to deploy as-is. It contains pre-populated values specific to your Snowflake account. Here are the key parameters embedded in the template:

• Snowflake Account URL: https://<org>-<account>.snowflakecomputing.com
• Snowflake Organization: Your Snowflake org name
• Snowflake Account: Your Snowflake account name
• Data Plane Key: A unique identifier generated by Snowflake (for example, pdckyx7b)
• Agent Image Version: The NiFi agent version (for example, 1.30.0)

Important: Do not modify the template parameters. They are generated by Snowflake to match your account configuration. Changing them will cause the deployment to fail or the agent to be unable to communicate with Snowflake.

Deploy the template

Deploy the template using the AWS CLI. The --capabilities CAPABILITY_NAMED_IAM flag is required because the template creates IAM roles:

aws cloudformation create-stack \
    --stack-name openflow-cdc-poc \
    --template-body file://aws-rds-openflow.yaml \
    --capabilities CAPABILITY_NAMED_IAM \
    --region us-west-2

# Monitor the deployment (takes approximately 15-20 minutes)
aws cloudformation wait stack-create-complete \
    --stack-name openflow-cdc-poc \
    --region us-west-2

# Verify the stack status
aws cloudformation describe-stacks \
    --stack-name openflow-cdc-poc \
    --query "Stacks[0].StackStatus" \
    --region us-west-2

What the stack creates

The stack provisions approximately 25 resources:

• Networking: A dedicated VPC (10.10.0.0/16) with 2 private subnets, 2 public subnets, an internet gateway, a NAT gateway, and an S3 gateway endpoint
• Compute: An Amazon EKS cluster and an Amazon EC2 agent instance (t3.medium) in a private subnet with no public IP
• Storage: An Amazon S3 bucket with all public access blocked, used for Terraform state
• Security: IAM roles and instance profiles for the agent, security groups restricted to the VPC CIDR
• Connectivity: An EC2 Instance Connect Endpoint for secure SSH access to the agent without a bastion host

Verify in Snowflake

After the stack completes, go back to Snowsight > Data > Ingestion > Openflow. Your deployment should show as ACTIVE. If it shows as PENDING, wait a few minutes for the agent to register with Snowflake.

Step 4: Establish network connectivity through VPC peering

The Openflow infrastructure runs in its own VPC (10.10.0.0/16), separate from your Aurora VPC (172.31.0.0/16). To allow the connector to reach the Aurora database over private IPs, create a VPC peering connection.

Pre-check: Ensure your Aurora VPC CIDR does not overlap with 10.10.0.0/16 (the Openflow VPC CIDR). VPC peering cannot be established between overlapping address spaces:

aws ec2 describe-vpcs --vpc-ids <aurora-vpc-id> --query "Vpcs[0].CidrBlock"

Create and accept the peering connection

Replace the VPC IDs with your actual values. You can find the Openflow VPC ID in the CloudFormation stack outputs:

# Create the peering connection
aws ec2 create-vpc-peering-connection \
    --vpc-id <openflow-vpc-id> \
    --peer-vpc-id <aurora-vpc-id> \
    --region us-west-2

# Accept the peering connection (same account, same region)
aws ec2 accept-vpc-peering-connection \
    --vpc-peering-connection-id <peering-id> \
    --region us-west-2

Add routes in all route tables

This is where many deployments fail. The Openflow CloudFormation stack creates three route tables (PrivateRouteTable1, PrivateRouteTable2, PublicRouteTable) plus the VPC main route table. You must add the peering route to every one of them.

Add routes in both directions:

# List all route tables in the OpenFlow VPC
aws ec2 describe-route-tables \
    --filters "Name=vpc-id,Values=<openflow-vpc-id>" \
    --query "RouteTables[].RouteTableId" \
    --region us-west-2

# Add route to Aurora CIDR in each OpenFlow route table
# Repeat for each route table ID returned above
# Note: 172.31.0.0/16 is the AWS default VPC CIDR. If your Aurora cluster
# is in a custom VPC, substitute your VPC's CIDR block (from the pre-check above).
aws ec2 create-route \
    --route-table-id <route-table-id> \
    --destination-cidr-block <aurora-vpc-cidr> \
    --vpc-peering-connection-id <peering-id> \
    --region us-west-2

# Add route to OpenFlow CIDR in the Aurora VPC route table
aws ec2 create-route \
    --route-table-id <aurora-route-table-id> \
    --destination-cidr-block 10.10.0.0/16 \
    --vpc-peering-connection-id <peering-id> \
    --region us-west-2

Update the Aurora security group

Allow inbound PostgreSQL traffic from the Openflow VPC CIDR on port 5432:

aws ec2 authorize-security-group-ingress \
    --group-id <aurora-security-group-id> \
    --protocol tcp --port 5432 \
    --cidr 10.10.0.0/16 \
    --region us-west-2

Enable DNS resolution across the peering connection

This step is commonly overlooked and causes a connection timeout. Without DNS resolution enabled on the peering connection, the Amazon Relational Database Service (Amazon RDS) endpoint hostname (for example, cdc-poc-aurora.cluster-xxxxx.us-west-2.rds.amazonaws.com) resolves to a public IP when queried from the Openflow VPC — instead of the private IP in the Aurora VPC. The routing and security groups are correct, but the connection times out because the resolved IP is unreachable.

Enable AllowDnsResolutionFromRemoteVpc on both sides:

# Enable DNS resolution from the OpenFlow VPC to the Aurora VPC
aws ec2 modify-vpc-peering-connection-options \
    --vpc-peering-connection-id <peering-id> \
    --requester-peering-connection-options AllowDnsResolutionFromRemoteVpc=true

# Enable DNS resolution from the Aurora VPC to the OpenFlow VPC
aws ec2 modify-vpc-peering-connection-options \
    --vpc-peering-connection-id <peering-id> \
    --accepter-peering-connection-options AllowDnsResolutionFromRemoteVpc=true

# Verify both VPCs have DNS settings enabled
aws ec2 describe-vpc-attribute --attribute enableDnsSupport --vpc-id <openflow-vpc-id>
aws ec2 describe-vpc-attribute --attribute enableDnsHostnames --vpc-id <openflow-vpc-id>

Both VPCs must have enableDnsSupport and enableDnsHostnames set to true. These are enabled by default on new VPCs.

Step 5: Create a runtime and install the PostgreSQL connector

Create an Openflow runtime

In Snowsight:

1. Navigate to Data > Ingestion > Openflow > your deployment.
2. Click Create Runtime.
3. Name it CDC_POC_RUNTIME.
4. Select the deployment you created in Step 3.
5. Wait for the runtime status to show ACTIVE.

Install the PostgreSQL connector

1. Open the runtime canvas.
2. Click the + button or drag from the connector catalog.
3. Search for “PostgreSQL” and select the PostgreSQL connector.
4. Drop it on the canvas. A process group named “PostgreSQL” appears.

Press enter or click to view image in full size

Step 6: Configure the connector parameters

The PostgreSQL connector uses three parameter contexts. Access them by right-clicking the canvas and selecting Parameters, or by clicking the gear icon. Below are the exact values for each field.

PostgreSQL source parameters

Click on PostgreSQL Source Parameters in the left panel. Set the following values:

PostgreSQL Connection URL

• Value: jdbc:postgresql://<aurora-cluster-endpoint>:5432/<database-name>
• The JDBC connection string pointing to your Aurora writer endpoint. Example: jdbc:postgresql://cdc-poc-aurora.cluster-xxxxx.us-west-2.rds.amazonaws.com:5432/sales

PostgreSQL Username

• Value: cdcadmin
• The database user with replication privileges (granted in Step 1).

PostgreSQL Password

• Value: <your-aurora-password>
• Click the field, enter the password, and set Sensitive value to Yes. This value is never exported.

PostgreSQL JDBC Driver

• Value: postgresql-42.7.5.jar
• Click the field, check the Reference asset checkbox, then upload the JDBC driver JAR file. Download it from https://jdbc.postgresql.org/download/ if you don’t have it.

Publication Name

• Value: openflow_pub
• The publication name created in Step 1.

Replication Slot Name

• Value: (leave empty)
• Optional. The connector auto-creates a replication slot if this is blank.

Press enter or click to view image in full size

Press enter or click to view image in full size

PostgreSQL destination parameters

Click on PostgreSQL Destination Parameters in the left panel. Set the following values:

Snowflake Account Identifier

• Value: <org>-<account>
• Your Snowflake account in org-account format. Example: ELPHBMX-AWSPARTNER. Find this in Snowsight under your account menu. Do not include .snowflakecomputing.com.

Snowflake Authentication Strategy

• Value: KEY_PAIR
• Required for BYOC deployments using a service user.

Snowflake Connection Strategy

• Value: STANDARD
• Use STANDARD for public Snowflake endpoints. Use PRIVATE_CONNECTIVITY only if you have AWS PrivateLink to Snowflake.

Snowflake Username

• Value: OPENFLOW_CDC_USER
• The service user created in Step 2.

Snowflake Role

• Value: CDC_POC_ROLE
• The role granted to the service user in Step 2.

Snowflake Warehouse

• Value: CDC_POC_WH
• The warehouse created in Step 2. Used for merge operations.

Destination Database

• Value: CDC_POC_DB
• The database created in Step 2. Tables are created here automatically.

Snowflake Private Key

• Value: <paste full PEM key content>
• Open openflow_rsa_key.p8 and paste the entire content including -----BEGIN PRIVATE KEY----- and -----END PRIVATE KEY----- lines. Set Sensitive value to Yes.

Snowflake Private Key File

• Value: (leave empty)
• Do NOT use this field. See the important note below.

Snowflake Private Key Password

• Value: (leave empty)
• Leave blank because we generated the key without a passphrase.

Important — use inline key, not file upload: In BYOC deployments, the NiFi runtime runs on Amazon EKS pods. When you upload a file through the Snowflake Private Key File parameter, the file is stored in a location that the EKS pods may not be able to access. This causes the Snowflake Connection Pool to remain stuck in “Enabling” state indefinitely, with no clear error message. The Snowpipe Streaming component (used for data upload) has its own authentication path and may work, but the JDBC Connection Pool (used for DDL operations like creating schemas and tables) will fail. Use the inline Snowflake Private Key parameter instead to avoid this issue.

PostgreSQL ingestion parameters

Click on PostgreSQL Ingestion Parameters in the left panel. Set the following values:

Included Table Regex

• Value: public\..*
• A regex matching fully qualified table names. This matches all tables in the public schema. For specific tables, use: public\.customers|public\.orders

Included Table Names

• Value: (leave empty)
• Alternative to regex. Use comma-separated names like: public.customers, public.orders, public.products

Ingestion Type

• Value: full
• “full” performs an initial snapshot of all existing rows, then switches to incremental CDC. Use “incremental” to skip the snapshot and only capture new changes.

Merge Task Schedule CRON

• Value: * * * * * ?
• Controls when journal-to-destination merges run. * * * * * ? means continuous. For hourly merges: * 0 * * * ?

Object Identifier Resolution

• Value: CASE_SENSITIVE
• Do not change after the first run. Determines if schema/table/column names are treated as case-sensitive. PostgreSQL uses lowercase by default, so CASE_SENSITIVE preserves the original casing.

Concurrent Snapshot Queries

• Value: 2
• Number of parallel queries during snapshot. Increase for more tables, but this adds load to Aurora.

Column Filter JSON

• Value: []
• Empty array means all columns are replicated. To filter: [{"schema":"public","table":"orders","includedPattern":".*"}]

Press enter or click to view image in full size

After setting all parameters, click Apply to save the changes.

Step 7: Start the connector and verify replication

Enable controller services and start

Follow this sequence to start the pipeline:

1. Right-click the canvas and choose Enable all Controller Services. Wait for all eight services to show ENABLED (green). The eight services are: Snowflake Connection Pool, Snowflake Private Key Service, PostgreSQL Connection Pool, JSON Reader, JSON Writer, Table State Store, Source Table Schema Registry, and Table Column Filter.
2. If any service gets stuck in “Enabling” for more than 30 seconds, check the troubleshooting section below.
3. Right-click the PostgreSQL process group and choose Start.
4. The status bar at the top shows the processor counts. You should see the running count increase to approximately 53–59 processors.

Press enter or click to view image in full size

Understand the pipeline phases

The connector operates in two phases:

Snapshot phase: The connector reads all existing rows from each source table and uploads them to Snowflake through Snowpipe Streaming. It creates the destination schema and tables automatically. For our test data (50 customers, 1,413 orders, 30 products), this completes in under a minute.

Incremental CDC phase: After the snapshot completes, the connector creates journal tables and streams in Snowflake, then starts reading the PostgreSQL replication slot for new changes. Every insert, update, and delete is captured and merged into the destination tables based on the CRON schedule.

Verify the replicated data

Verify the replicated data in Snowflake:

-- Run in Snowsight as CDC_POC_ROLE
USE DATABASE CDC_POC_DB;

-- Check schemas created
SHOW SCHEMAS;
-- Expected: INFORMATION_SCHEMA, PUBLIC, public

-- Check tables
SHOW TABLES IN SCHEMA "public";
-- Expected: customers, orders, products

-- Verify row counts
SELECT COUNT(*) FROM "public"."customers";  -- Expected: 50
SELECT COUNT(*) FROM "public"."orders";     -- Expected: 1,413
SELECT COUNT(*) FROM "public"."products";   -- Expected: 30

-- Preview the data
SELECT * FROM "public"."customers" LIMIT 10;

Press enter or click to view image in full size

Test real-time CDC

To verify that incremental changes are captured, insert a row in Aurora and check Snowflake:

-- Run on Aurora PostgreSQL (via Lambda or a connected client)
INSERT INTO public.customers (customer_id, customer_name, region, segment, credit_rating, onboarded_date)
VALUES (51, 'TestCDC Corp', 'Europe', 'Enterprise', 'A', '2026-03-26');

-- Wait 10-30 seconds, then check Snowflake
SELECT * FROM "CDC_POC_DB"."public"."customers" WHERE customer_id = 51;

Troubleshooting

Connection timeout to Aurora (SocketTimeoutException)

Symptom: The connector logs show java.net.SocketTimeoutException: Connect timed out when the PostgreSQL Connection Pool tries to reach Aurora.

Cause: AllowDnsResolutionFromRemoteVpc is not enabled on the VPC peering connection. The RDS endpoint resolves to a public IP from the Openflow VPC.

Fix: Run the modify-vpc-peering-connection-options commands from Step 4 to enable DNS resolution on both sides.

Snowflake Connection Pool stuck in “Enabling”

Symptom: The Snowflake Connection Pool service stays in “Enabling” state. Processors that depend on it show validation errors: “Controller Service [Connection Pool] state is Enabling”.

Cause: The Snowflake Private Key File upload is not accessible to the NiFi runtime on EKS. The file path cannot be resolved inside the Kubernetes pod.

Fix: Clear the Snowflake Private Key File parameter. Instead, paste the private key content into the Snowflake Private Key (inline text) parameter. Then disable all controller services, re-enable them, and start the connector.

Controller services not picking up parameter changes

Symptom: You changed a parameter value but the connector still shows the old error.

Fix: Stop the connector, disable all controller services (wait for all DISABLED), re-enable all controller services (wait for all ENABLED), then start. Services cache their configuration and need a full restart cycle.

Sensitive parameters showing as “not provided” in exports

Symptom: When you export the flow as JSON, sensitive fields like PostgreSQL Password and Snowflake Private Key show "provided": false.

Explanation: This is expected NiFi behavior. Sensitive values are never included in exports for security. The values are saved in the runtime. Re-enter them only if you import the flow into a new environment.

Security considerations

This architecture follows AWS and Snowflake Shared Responsibility Model principles. AWS and Snowflake provide security OF the cloud (infrastructure, compute isolation, physical security). You configure security IN the cloud (IAM roles, security groups, encryption keys, database credentials). Key security properties:

• The Openflow BYOC infrastructure runs in your AWS account. Data is processed within your VPC and does not transit through Snowflake-managed infrastructure.
• The Amazon EC2 agent and Amazon EKS nodes are deployed in private subnets with no public IP addresses.
• The Amazon S3 bucket created by the stack has all public access blocked by default.
• Aurora connectivity uses VPC peering over private IPs with no internet exposure.
• Snowflake authentication uses RSA key pair authentication. No passwords are stored for the Snowflake connection.
• The Lambda function used for database setup is temporary and deleted after use.
• Network ACLs and security groups follow least-privilege principles.

Cleanup

To avoid ongoing charges, delete the resources in reverse order:

1. Stop the connector in the Openflow canvas (right-click > Stop).
2. Delete the Openflow runtime and deployment in Snowsight.
3. Delete the CloudFormation stack:

aws cloudformation delete-stack --stack-name openflow-cdc-poc --region us-west-2

4. Delete the VPC peering connection:

aws ec2 delete-vpc-peering-connection --vpc-peering-connection-id <peering-id> --region us-west-2

5. Remove the Aurora security group ingress rule for 10.10.0.0/16.

6. Delete the Lambda IAM role (if not already deleted):

aws iam delete-role-policy --role-name lambda-vpc-execution-role --policy-name vpc-access 
aws iam delete-role --role-name lambda-vpc-execution-role

7. Drop the Snowflake objects:

DROP DATABASE CDC_POC_DB;
DROP WAREHOUSE CDC_POC_WH;
DROP USER OPENFLOW_CDC_USER;
DROP ROLE CDC_POC_ROLE;

8. Revert rds.logical_replication to 0 and reboot the Aurora writer instance if logical replication is no longer needed.

Conclusion

In this post, I showed you how to build a real-time CDC pipeline from Amazon Aurora PostgreSQL-Compatible Edition to Snowflake using Snowflake Openflow BYOC. You eliminated batch ETL delays by streaming every insert, update, and delete from your transactional database to your analytics warehouse within seconds — without custom code or adding load to production systems.

We covered seven steps: enabling logical replication on Aurora, configuring Snowflake objects and key pair authentication, downloading and deploying the Openflow AWS CloudFormation template, establishing private network connectivity through VPC peering with DNS resolution, creating the runtime and installing the connector, configuring all three parameter contexts with exact field values, and verifying the replicated data.

Along the way, we documented two commonly overlooked pitfalls: VPC peering DNS resolution (AllowDnsResolutionFromRemoteVpc) and the private key file-versus-inline handling in BYOC deployments. Both produce misleading symptoms and can take hours to diagnose without prior knowledge.

The BYOC deployment model gives you full control over the processing infrastructure while Snowflake manages connector updates and monitoring. Try extending this pipeline by adding more tables, testing schema evolution scenarios, or optimizing merge schedules to balance data freshness with warehouse costs. Leave a comment below with your questions or experiences implementing CDC pipelines.

To learn more:

• AWS and Snowflake partnership page
• Snowflake Openflow documentation
• Amazon Aurora PostgreSQL logical replication
• Amazon VPC peering guide
• AWS CloudFormation documentation