Let's understand the architecture first.

1. Understand the architecture and monitoring flow

Example architecture: A zero-ETL integration connects a transactional source to an analytics target and downstream consumers:

• Transactional source — Amazon Aurora PostgreSQL (or Amazon RDS for MySQL/PostgreSQL) handles your operational writes.
• Zero-ETL integration — replicates committed changes into the target in near real time, with no pipeline to build or manage.
• Analytics target — Amazon Redshift receives the data and makes it queryable within seconds.
• Analytics & AI/ML consumers — Amazon QuickSight (BI and reporting), Amazon SageMaker (ML and AI), and SQL clients/applications read from Redshift.

2. End-to-End monitoring flow

Observability is layered on top of that path: the Zero-ETL integration (Aurora/RDS → Redshift) emits signals to monitoring & observability (Amazon CloudWatch integration metrics + Redshift system views such as SVV_INTEGRATION; those feed alerts & events (Amazon EventBridge state-change events and Amazon SNS notifications) which reach operators & tools (the data-platform team, dashboards, and runbooks). Keep this flow in mind — the rest of this article walks each layer.

Monitor with Amazon Redshift system views: The richest source of truth lives in the Redshift system views on the target warehouse. Query them like any other table.

Check overall health and lag:

SELECT integration_id,
       target_database,
       source,
       state,
       current_lag,
       last_replicated_checkpoint
FROM   svv_integration;

Join config + activity for a fuller picture:

SELECT si.integration_id,
       si.target_database,
       si.source,
       si.state,
       si.current_lag,
       sa.checkpoint_bytes,
       sa.modified_tables,
       sa.integration_end_time
FROM   svv_integration si
JOIN   sys_integration_activity sa
       ON si.integration_id = sa.integration_id;

Find tables that aren't fully synced:

SELECT *
FROM   svv_integration_table_state
WHERE  integration_state <> 'Synced';

3. View CloudWatch metrics for the integration

In the Redshift console, choose Zero-ETL integrations in the navigation pane and select your integration ID to see graphs. The same metrics are published to Amazon CloudWatch under the AWS/Redshift namespace, all carrying a single dimension( IntegrationId) and refreshed every 60 seconds. The integration surfaces two tabs: Integration metrics (the CloudWatch graphs below) and Table statistics (per-table status, last-updated time, row count, and size).

The eight Integration-metrics

Multi-database note:

 For integrations replicating multiple databases, IntegrationLatestDetectedChange and IntegrationLatestAppliedChange report the minimum across all databases (the least-recently-updated one). Per-database values live in the latest_detected_change_time and latest_applied_change_time columns of SVV_INTEGRATION

Example A — Alarm when replication lag exceeds 5 minutes. IntegrationLag is in seconds, so 300 = 5 minutes. Use Average over a 5-minute period and require several consecutive breaches to avoid flapping on brief spikes:

aws cloudwatch put-metric-alarm \
  --alarm-name "zetl-lag-over-5min" \
  --namespace AWS/Redshift \
  --metric-name IntegrationLag \
  --dimensions Name=IntegrationId,Value=<your-integration-id> \
  --statistic Average \
  --period 300 \
  --evaluation-periods 3 \
  --threshold 300 \
  --comparison-operator GreaterThanThreshold \
  --treat-missing-data notBreaching \
  --alarm-actions arn:aws:sns:us-east-1:<acct>:zetl-alerts

Example B — Alarm the instant any table fails. IntegrationNumTablesFailedReplication should sit at 0, so alarm on > 0 with a tight window:

aws cloudwatch put-metric-alarm \
  --alarm-name "zetl-table-failures" \
  --namespace AWS/Redshift \
  --metric-name IntegrationNumTablesFailedReplication \
  --dimensions Name=IntegrationId,Value=<your-integration-id> \
  --statistic Maximum \
  --period 60 \
  --evaluation-periods 1 \
  --threshold 0 \
  --comparison-operator GreaterThanThreshold \
  --alarm-actions arn:aws:sns:us-east-1:<acct>:zetl-alerts

Example C — Derive lag yourself from the epoch metrics. The two new Change metrics(IntegrationLatestDetectedChange, IntegrationLatestAppliedChange) are Unix epoch seconds. The gap between them is effectively the apply lag, and you can compute it with a CloudWatch metric math expression rather than reading the graphs by eye:

-- Metric math in a CloudWatch dashboard/alarm:
--   m1 = IntegrationLatestDetectedChange  (epoch seconds)
--   m2 = IntegrationLatestAppliedChange   (epoch seconds)
-- Apply gap in seconds:
e1 = m1 - m2

-- Worked numbers:
--   m1 = 1,758,750,000  (last change detected)
--   m2 = 1,758,749,910  (last change applied)
--   e1 = 90  -> the target is ~90s behind the staged change

If IntegrationLag and this derived gap both climb together while IntegrationDataTransferred spikes, you're typically looking at a large burst of source writes rather than a stuck integration. You can confirm by checking whether IntegrationLatestAppliedChange is still advancing.

Reading the metrics together — quick interpretation guide:

4. Alert on metrics with CloudWatch and EventBridge:

All integration-related CloudWatch metrics originate from Amazon Redshift. Amazon RDS/Aurora does not publish integration metrics to CloudWatch itself. Redshift also sends integration events to Amazon EventBridge, which you can route to notifications(SNS) or automation. A common pattern is to watch lag or failure signals and fan out through Lambda + Amazon SNS for alerting.

5. Validate row-level correctness:

Metrics tell you replication is healthy but a quick reconciliation confirms the data is correct. After the initial load settles, compare counts on source and target:

-- On Aurora (source)
SELECT count(*) FROM sales.orders;

-- On Redshift (target)
SELECT count(*) FROM zetl_analytics.sales.orders;

6. Build a CloudWatch dashboard

Pull the integration metrics into a single CloudWatch dashboard so operators can see integration health at a glance. A practical layout combines status tiles across the top with time-series graphs below:

• Status tiles — Integration status (Active), current lag (e.g. 8 sec), tables replicated (150 / 150), and failed tables (0).
• Integration lag (seconds) — line graph of IntegrationLag to spot trends before they breach.
• Data transferred (GB) — column graph of IntegrationDataTransferred to correlate lag with load.
• Tables replicated & failed — stacked columns of IntegrationNumTablesReplicated and IntegrationNumTablesFailedReplication.
• Duplicate rows detected — column graph of IntegrationDuplicateRowsDetected.

7. Recommended alarm strategy

Turn the metrics into action with a small set of CloudWatch alarms, each notifying an Amazon SNS topic:

8. Quick Troubleshooting workflow

When the data is not flowing(aka lag or alarms) through Zero-ETL integration , then we need to quickly isolate the issue to Source or Target, so that we can focus on that respective area to remediate it

1. Check integration status: query SVV_INTEGRATION for state and current lag.
2. Check table state & errors: SVV_INTEGRATION_TABLE_STATE and SYS_INTEGRATION_TABLE_ACTIVITY to isolate which table and what kind of activity.
3. Review source & target health: Aurora/RDS metrics and Redshift metrics for load or resource pressure.
4. Identify root cause & take corrective action: Common causes are table issues (missing primary key, unsupported type), a workload spike, or schema changes.
5. Is it Source(example: Aurora) or Target issue? : To answer this, and to detect stall earlier and measure replication lag on your integrations, two new Amazon CloudWatch metrics for zero-ETL integrations were added , available already in Amazon Redshift Patch 204. Together with the existing IntegrationLag metric, you now have three signals that answer three different questions:

IntegrationLatestDetectedChange (new) -The timestamp of the most recent CDC checkpoint that Redshift has received from the source. Answers: “is the source pipeline producing new data?”
IntegrationLatestAppliedChange (new) - The timestamp of the most recent CDC checkpoint that has been successfully ingested into the target tables on Redshift. Answers: “is Redshift consuming that data?”
IntegrationLag (existing)- The current replication lag, in seconds. Answers: “how far behind is Redshift?“, but only while the pipeline is actively applying data. If ingestion stops entirely, IntegrationLag stops reporting new datapoints and CloudWatch shows “insufficient data” rather than a growing lag.Is metric *IntegrationLatestDetectedChange* moving/advancing? If it is is flat while you know the source is actively writing, suspect the source or the change-capture layer.

References - Monitoring zero-ETL integrations - (https://docs.aws.amazon.com/redshift/latest/mgmt/zero-etl-monitoring.html) - Viewing and monitoring Amazon RDS zero-ETL integrations - https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/zero-etl.describingmonitoring.html.