Hi teams, I'm doing a poc with airbyte from Postgr...
# replication-troubleshooting
z
Hi teams, I'm doing a poc with airbyte from Postgres prod -> Postgres datawarehouse for my company in order to replace stitch. I'm using hourly replication frequency and principal sync mode is "*incremental append*". The synchronisation takes about 20mins each time. But after 10 days testing, I found there are data missing during the synchronization: for ex at 2023-01-27, I lost 2 rows of data. When I look into details, the missing data are generated at 2023-01-27 094458.782365+00 and 2023-01-27 094510.105975+00, while my sync slots are marked succeeded. Same problem happened also in 2 others tables I tracked, in different days. Could you pls tell me is that a normal problem for the tools? Any solution to avoid/correcte this problem? I'm willing to provide all the information if needed.
n
Hi, Zhaoxia! One of the reasons this may happen is because the data type is incorrect in some way. Could you please provide the sync logs?
z
Hi, for the data generated at 2023-01-27 094458 which sync slot should I share? the one at 9h42 or 10h42?
u
Either one works!
z
Just pm you :)
did you get the documents I shared?
n
Hi! Messaging individual team members goes against our code of conduct. Please make sure to review it: https://docs.airbyte.com/project-overview/slack-code-of-conduct/
The table is core_transaction
n
I think I've found the error:
Copy code
Schema validation errors found for stream public_core_transaction. Error messages: [$.authorization_no is of an incorrect type. Expected it to be string, $.order_no is of an incorrect type. Expected it to be string, $.billing_date is of an incorrect type. Expected it to be string, $.delivery_site_id is of an incorrect type. Expected it to be number, $.priced_at is of an incorrect type. Expected it to be string, $.previous_id is of an incorrect type. Expected it to be number, $.depot_id is of an incorrect type. Expected it to be number, $.warehouse_id is of an incorrect type. Expected it to be number, $.billing_entity_id is of an incorrect type. Expected it to be number, $.virtual_depot_id is of an incorrect type. Expected it to be number]
So it is indeed a type error! And there are a few more in there. You can also check the raw tables to see if the missing data exists there
z
Hello, thx for the investigation. After verifying the log, indeed, there is an error like 'Schema validation errors found for stream public_core_transaction.' But, I picked another sync log randomly from 1-31-2023 which I don't have any missing data in core_transaction table, I also found error 'Schema validation errors found for stream public_core_transaction.' I don't really understand, if this error does impact sync result, why other sync's results are good?
I also checked airbyte_raw_core_transaction table with 2 missing data id, but I couldn't find them...any ideas? Thanks a lot for helping me 🙂
n
What cursor field are you using? Another thought is that if two records are added concurrently and the cursor field is the same, one of them is not appended.
z
Hello the cursor field I used is 'updated_at'
Here is the updated_at values of 2 missing records, apparently they are note concurrent...
n
Hi, Zhaoxia! I apologize for the wait! Are you still pursuing this POC?
z
Yes