Why is distinct not working in Redshift zeroETL table

The behavior you're experiencing with duplicate records in your Zero ETL table is likely related to the ongoing synchronization process rather than an issue with the DISTINCT keyword itself.

When a Zero ETL integration is actively syncing data, the table may contain temporary duplicate records during the replication process. This is because Zero ETL uses a continuous data replication mechanism, and during synchronization, the same record might appear multiple times until the sync process completes and reconciles the data.

The DISTINCT keyword in Redshift works correctly, but if the underlying data actually contains duplicate rows (even temporarily during sync), DISTINCT will only remove duplicates if the entire row is identical. If there are any hidden differences in the rows (such as internal metadata columns used by the Zero ETL process), they would be treated as distinct records.

Here are a few things to consider:

1. Wait for sync completion: If your table is still actively syncing, allow the Zero ETL integration to complete its initial synchronization. The duplicates should resolve once the sync process stabilizes.

2. Check sync status: Monitor the status of your Zero ETL integration to ensure it's functioning properly and has completed its initial load.

3. Query timing: Queries executed during active synchronization may return inconsistent results until the data replication catches up.

The issue should resolve itself once the Zero ETL synchronization process completes and the data stabilizes in your Redshift database.
Sources
Understanding Auto Materialized Views with Amazon Redshift Zero ETL | AWS re:Post

This is a normal behavior. During zero-ETL sync, Redshift processes continuous data replication from your source. The same record can temporarily appear multiple times until replication completes. Even though you're selecting only the id column, internal metadata used by zero-ETL makes these rows technically distinct. You can check the status after the sync is complete by running the sql -

SELECT * FROM svv_integration_table_state;

Once tables show "Synced" state, duplicates will resolve automatically.