Ensuring Zero-ETL Performance and Reliability

Maintaining optimal Zero-ETL performance requires proactive monitoring and operational best practices. The following recommendations can help minimize disruptions, maintain service-level objectives (SLOs), and ensure consistent data replication between source and target systems.

1. Avoid Table Resynchronizations

Frequent table resynchronizations can be disruptive to Zero-ETL operations. When a table enters a "Failed" state, administrators must manually initiate a refresh using commands such as:

ALTER DATABASE sample_integration_db INTEGRATION REFRESH ALL TABLES;

Resynchronizations can temporarily interrupt replication activity and negatively impact overall performance.

To minimize the likelihood of tables entering a "Resync Required" state, we recommend limiting the following conditions on Zero-ETL target tables:

• Frequent or extensive DDL changes (ALTER, DROP, or schema modifications)
• Large transaction backlogs caused by sustained high-volume data ingestion
• Unsupported data types
• Missing primary keys on source tables. For more information, see Handling Tables Without Primary Keys While Creating Amazon Aurora MySQL or Amazon RDS for MySQL Zero-ETL Integrations with Amazon Redshift.

Regularly reviewing table design and schema changes can significantly reduce the need for manual resynchronization.

2. Monitor Table Growth and Size

As Zero-ETL target tables grow, replication workloads require additional processing time and resources. Excessive table growth can result in ingestion latency that exceeds established service-level agreements (SLAs). To proactively identify large or rapidly growing tables, periodically review table size metrics using the following query:

SELECT *
FROM SVV_TABLE_INFO
WHERE database = '<zero-etlDB>'
ORDER BY size DESC;

We recommend logging and tracking the output over time to identify growth trends and determine when table maintenance, archival strategies, or cluster scaling may be required.

You can further log integration activity such as lag or resync frequency by using the SVV_INTEGRATION system table for replication lag and SYS_INTEGRATION_TABLE_STATE_CHANGE_HISTORY for tracking resync events.

VACUUM is an excellent alternative for combating exponential table growth. Zero-ETL performs constant UPDATE/DELETE operations, leaving dead rows on tables that can cause tables to grow out of control.

3. Minimize Resource Contention During Zero-ETL Activity

Running resource-intensive workloads concurrently with Zero-ETL ingestion processes can create significant contention for cluster resources, including CPU, memory, and I/O bandwidth. This contention can increase ingestion latency and cause replication times to exceed expected thresholds.

To reduce the impact on Zero-ETL performance:

• Monitor workload concurrency and resource utilization during periods of increased replication latency.
• Ensure cluster capacity is appropriately sized to support both business workloads and Zero-ETL activity.
• Concurrency Scaling support (P200 onwards) - Since patch 200, Zero-ETL ingestion can run on Concurrency Scaling clusters. The cluster parameter max_concurrency_scaling_clusters is now relevant for reducing resource contention.

When investigating inflight Zero-ETL jobs during queue buildup, you can use the following query as Zero-ETL jobs run as COPY commands under user ID 100 (rdsdb):

SELECT query_id, query_text, start_time, elapsed_time
FROM SYS_QUERY_HISTORY
WHERE user_id = 100
  AND status = 'running'
  AND query_text LIKE '%COPY%'
ORDER BY start_time;

Reviewing active workloads can help identify resource bottlenecks and ensure that Zero-ETL replication processes receive sufficient resources to complete efficiently