We have a full refresh overwrite table within a sync that runs every 2h. The ingestion sometimes misses some records that exist in the source. What could be causing that?

The issue you're experiencing could be due to the nature of the Full Refresh - Overwrite mode in Airbyte. In this mode, new syncs will destroy all data in the existing destination table and then pull the new data in. Therefore, data that has been removed from the source after an old sync will be deleted in the destination table [[source](https://docs.airbyte.com/understanding-airbyte/connections/full-refresh-overwrite/)]. If the source data is changing frequently, it's possible that some records are being removed from the source between syncs, which would explain why they're missing from the destination. Another possible reason could be related to the limitations of the Incremental Sync mode, which is not able to handle source schema changes yet. For example, when a column is added, renamed or deleted from an existing table etc. It is recommended to trigger a Full refresh - Overwrite to correctly replicate the data to the destination with the new schema changes [[source](https://docs.airbyte.com/understanding-airbyte/connections/incremental-append/)]. If you're using Postgres as your source and you're operating under the CDC mode, it's also possible that the issue is related to the removal of Write-Ahead Logs (WALs) by Postgres. This can occur when there are lots of database updates resulting in more WAL files than allowed in the

pg_wal

directory, or when the Postgres connector successfully reads the WAL and acknowledges it to Postgres, but the destination connector fails to consume the data [[source](https://docs.airbyte.com/integrations/sources/postgres/postgres-troubleshooting)]. If none of these scenarios apply, it would be helpful to have more information about your specific setup to provide a more accurate answer.

@kapa.ai we are using CDC with MySQL and which is weird is that the ingestion is leaving some records behind instead of doing a full pull of the data in the table

There could be several reasons why some records are not being ingested during the Change Data Capture (CDC) process with MySQL. Here are some potential issues and solutions: 1. CDC Limitations: CDC has certain limitations that could impact the data ingestion. For instance, CDC is only supported for tables with primary keys. If your table doesn't have a primary key, you might want to consider using Full Refresh or non-CDC incremental replication. Also, the modifications you are trying to capture must be made using `DELETE`/`INSERT`/`UPDATE`. Changes made from `TRUNCATE`/`ALTER` won't appear in logs and therefore in your destination. Schema changes are not supported automatically for CDC sources. If you make a schema change, you should reset and resync data. You can read more about these limitations in the [Airbyte CDC documentation](https://docs.airbyte.com/understanding-airbyte/cdc). 2. Binary Logging: For MySQL replication, you must enable binary logging. The binary logs record transaction updates for replication tools to propagate changes. You can configure your MySQL server configuration file with the following properties:

server-id = 223344       log_bin = mysql-bin       binlog_format = ROW       binlog_row_image = FULL       binlog_expire_logs_seconds = 864000

3. Issues with the Source or Destination: There could be issues with the specific versions of the MySQL source or the destination you are using. For instance, a user reported on the [Airbyte forum](https://discuss.airbyte.io/t/3329) that they were having issues with CDC replication with Aurora MySQL (5.7.0) 2.10.2 and Snowflake 0.4.40. The first sync worked (full-refresh) but subsequent syncs did not pick any new records. If you are using similar versions, you might want to consider upgrading or downgrading your source or destination versions. 4. Performance Issues: Another user reported on the [Airbyte forum](https://discuss.airbyte.io/t/3933) that they were experiencing slow ingestion times with CDC. They found that refreshing the connector helped solve the issue. If you are experiencing similar issues, you might want to try refreshing your connector. If none of these solutions work, I would recommend reaching out to the Airbyte community on their [forum](https://discuss.airbyte.io/) or [Slack](https://airbytehq.slack.com) for further assistance.