How to diagnose and resolve table replication failures in DynamoDB to Redshift zero-ETL integrations

Symptoms

The zero-ETL integration remains Active, but individual tables show a "Failed" status with the following message:

Replicating data into table "public"."my-table" failed with N errors.
Please use ALTER DATABASE command to refresh the integration.

Deleting and recreating the integration, or repeatedly running ALTER DATABASE ... INTEGRATION REFRESH, does not resolve the issue for the same table.

Diagnosis

Step 1: Identify failed tables (SVV_INTEGRATION_TABLE_STATE)

Connect to your Redshift data warehouse and query the system view:

SELECT
    table_name,
    table_state,
    reason,
    last_updated_timestamp
FROM SVV_INTEGRATION_TABLE_STATE
WHERE table_state = 'Failed'
ORDER BY last_updated_timestamp DESC;

The reason column provides a high-level error summary.

Step 2: Get the detailed error (SYS_LOAD_ERROR_DETAIL)

Query SYS_LOAD_ERROR_DETAIL to identify the specific cause:

SELECT
    start_time,
    column_name,
    column_type,
    error_code,
    error_message
FROM SYS_LOAD_ERROR_DETAIL
WHERE database_name = 'your_integration_database'
ORDER BY start_time DESC
LIMIT 20;

The most common error for DynamoDB sources is:

String value exceeds the max size of 65535 bytes

Root cause

In a DynamoDB zero-ETL integration, all DynamoDB data maps to a SUPER data type column in Redshift. While the SUPER type itself can store up to 16 MB of data, the zero-ETL replication engine enforces a VARCHAR maximum length of 65,535 bytes on individual values during the load process.

DynamoDB allows individual attribute values up to 400 KB, so data that is stored and queried normally in DynamoDB can hit this limit during zero-ETL replication. If even a single attribute in the table exceeds this size, replication fails for the entire table.

Common scenarios:

• A String attribute storing large JSON blobs, base64-encoded files, or lengthy text
• A Map or List attribute whose serialized size exceeds the limit

Resolution

Option A: Enable TRUNCATECOLUMNS (no source changes required)

Configure Redshift to automatically truncate values exceeding 65,535 bytes, allowing replication to continue:

ALTER DATABASE your_integration_database SET TRUNCATECOLUMNS TRUE;

Note: This option truncates the excess portion of the data. It is not suitable if you need full data integrity for the affected attributes.

Option B: Reduce attribute size at the DynamoDB source

Identify items with oversized attributes and either compress, truncate, or offload them:

aws dynamodb scan \
    --table-name your-table-name \
    --filter-expression "size(#attr) > :maxsize" \
    --expression-attribute-names '{"#attr": "large_attribute_name"}' \
    --expression-attribute-values '{":maxsize": {"N": "65535"}}' \
    --select COUNT

A recommended pattern is to move large payloads to Amazon S3 and store only the S3 URI in DynamoDB.

Option C: Exclude the table from the integration

If the table is not required in Redshift, use table filters to exclude it from the integration scope.

Recovery: Refresh the integration after fixing the source

After resolving the oversized attributes, trigger a resync for the failed table:

ALTER DATABASE your_integration_database INTEGRATION REFRESH TABLES "your-table-name";

Verify the table transitions to Synced:

SELECT table_name, table_state
FROM SVV_INTEGRATION_TABLE_STATE
WHERE table_name = 'your-table-name';