https://linen.dev logo
Join Slack
Powered by
Hello everyone, I'm using Airbyte to ingest SQL Se...
# replication-troubleshooting
m
Hello everyone, I'm using Airbyte to ingest SQL Server CDC events and ran into a strange issue. Some events were fired at the exact same moment, but with different values. As seen in the screenshots, I have two registers with the exact same 
emmited_at
values, with different 
ab_id
and different values in the column 
dt_fim_transacao_preset
with one being 
null
. I'm not even sure how to determine which one is the newest value. Does anyone have any insights on what might have happened?
Can anyone shed some light about this?
Can someone help me with this issue? We've been trying for a long time with no luck. I've also posted it to the forum. On our SQL Server, I checked the CDC and confirmed there are indeed events from the same transaction with the same timestamp:
Copy code
+-------------------------+------------+------------------------+----------------+
| start_time              | end_time   | __$seqval              | __$operation   |
|-------------------------+------------+------------------------+----------------|
| 2023-02-04 02:11:48.880 | NULL       | 0x0002D69200000DF1000C | 3              |
| 2023-02-04 02:11:48.880 | NULL       | 0x0002D69200000DF1000C | 4              |
| 2023-02-04 02:11:54.223 | NULL       | 0x0002D692000013460004 | 3              |
| 2023-02-04 02:11:54.223 | NULL       | 0x0002D692000013460004 | 4              |
| 2023-02-04 02:11:54.527 | NULL       | 0x0002D6920000137F0003 | 3              |
| 2023-02-04 02:11:54.527 | NULL       | 0x0002D6920000137F0003 | 4              |
| 2023-02-04 02:15:02.687 | NULL       | 0x0002D6920001622F0003 | 3              |
| 2023-02-04 02:15:02.687 | NULL       | 0x0002D6920001622F0003 | 4              |
| 2023-02-04 02:15:06.270 | NULL       | 0x0002D6920001F8DC0003 | 3              |
| 2023-02-04 02:15:06.270 | NULL       | 0x0002D6920001F8DC0003 | 4              |
+-------------------------+------------+------------------------+----------------+
However, as can be seen, the 
__$operation
is different for each one. Consulting the Microsoft documentation, it seems like the 
__$operation=3
rows refer to the data pre-update, while the 
__$operation=4
rows refer to the data post-update, meaning only 
__$operation=4
rows are relevant to us. Also, there seems to be no other fields on the generated Airbyte files that are uploaded to Google Cloud Storage besides 
_airbyte_ab_id
, and 
_airbyte_emitted_at
. This is very strange as the Airbyte documentation for SQL Server CDC states that 
INSERT
, 
UPDATE
and 
DELETE
operations are caught and ingested. How are those identified without an 
operation
field in the output file? How can I make it so that Airbyte ignores the 
__$operation=3
events? Or at least, how can I identify the operation so that I can filter these on my ingested csv and avro files?
hello everyone. can anyone help with this?
hey everyone, we've been looking for a solution for this for a while now, can anyone shed some light?
n
Hi, Matheus! So sorry we missed this - the team has been understaffed since November and we're only just catching up! Could you please give me sync logs from a run or give me the Airbyte & connector versions? That would be really helpful in figuring out what's going on as we just went through a big refactor. I really encourage you to update everything
m
I see! Everything you asked is on the forum thread
s
Hey @Matheus Pinheiro, this is the best resource I'm aware of for understanding CDC in Airbyte: https://airbyte.com/tutorials/incremental-change-data-capture-cdc-replication#view-the-destination-database Here's an excerpt that I think will answer most of your questions:
In addition to the field called __airbyte_data_ that contains a JSON representation of the source data, there are two additional fields in the raw table:
• __airbyte_emitted_at_ which tells you what time airbyte sent the record to the destination.
• __airbyte_ab_id_ is a UUID value added by the destination connector to each record before it is sent to the destination. This is a UUID (not a hash) and therefore it changes for each row after each sync, even if the data has not been modified.
Highly recommend checking out the whole blog series, the one I linked above is last in a three-part series on synchronization modes in Airbyte. The first two parts are on full refresh replication and incremental replication.
m
Hey @Sunny Hashmi (Airbyte), thank you for responding! I have read those, and the tutorials makes sense, but I can't seem to replicate them. The tutorial states:
Copy code
CDC sync with Deduped + history was chosen as the Sync mode, and so the normalization SQL creates two tables from the raw data: a history table (cdc_tut_scd) and a deduplicated table (cdc_tut).
However, in my case (SQL Server source ->GCS destination), only one table seems to be created (the _`cdc_tut_scd`_ equivalent). The 
cdc_tut
equivalent, which would contain the information about records metadata, doesn't appear on my Cloud Storage bucket. How should this work for the GCS destination? Shouldn't there be two blobs on my bucket, one for each table (one path containing files for the data table, and the other containing files for the metadata table)?
s
Wait... you're not seeing the final deduplicated table? Which output are you using with GCS? Could you share the full logfile from one of the syncs?
Nevermind, found a logfile in your forum post and checking it out now. please update if that one is not applicable anymore
Hey @Matheus Pinheiro, Since it's blob storage, it looks like the in-flight normalization tables don't match the linked tutorial which is misleading. Sorry about that, I haven't looked closely at blob storage normalization before. I'll see if I can find a better resource octavia thinking The logs in your forum post indicate that you're using the 
Incremental - Append
sync mode with the GCS destination connector. In this case, the following should be true: • After each sync, the destination bucket should contain a new file with the latest synced data alongside of output files from previous syncs. In the provided logfile, the sync output file is named 
2023_02_07_1675782171903_
. You can find the full path for this file by searching for 
fullOutputPath
in your sync log • Each individual stream should have its own folder in the configured bucket. • Since root level flattening is enabled, we should see your data unpacked to columns in the output files. Does your final data appear in the destination as I've described? Is anything missing? If so, please submit a bug report and share the link here so I can get it prioritized.
m
@Sunny Hashmi (Airbyte) that's what it looks like, I'm not seeing any other files besides the one with the raw data and `ab_id`and 
ab_emitted_at
. Regarding your points: • After each sync, the destination bucket should contain a new file with the latest synced data alongside of output files from previous syncs. In the provided logfile, the sync output file is named 
2023_02_07_1675782171903_
. You can find the full path for this file by searching for 
fullOutputPath
in your sync log ◦ There is one new file per sync, which is that one, but no extra files. The 
fullOutputPath
says 
SQLSERVER-AIRBYTE/DBName/NMSPTEST/redacted/2023_02_07_1675782171903_
, but there's only a single file 
2023_02_07_1675782171903_0.csv
(see screenshot attached). • Each individual stream should have its own folder in the configured bucket. ◦ What do you mean by that? There's a folder for each table, and inside a csv file for each sync, but no extra files with the metadata. • Since root level flattening is enabled, we should see your data unpacked to columns in the output files. ◦ If all columns should be in one file, that is not working, the only airbyte created columns are 
airbyte_ab_id
and 
airbyte_emmited_at
as you can see in the screenshot. Does your final data appear in the destination as I've described? Is anything missing? It does not. I don't see a file nor columns containing the metadata as stated in the tutorial. I only see the csv with the data from my SQL Server source rows and two airbyte created columns: 
airbyte_ab_id
and 
airbyte_emmited_at
If so, please submit a bug report and share the link here so I can get it prioritized. That's done already! The link was also shared in this thread. Here it is: https://github.com/airbytehq/airbyte/issues/22563
s
Thanks for reposting the github issue @Matheus Pinheiro, sorry I didn't spot it before. I'm going to work on reproducing this and will follow up in there.
m
That's alright @Sunny Hashmi (Airbyte), thanks for looking into this!
Do let me know when you have any updates! Thank you!
hello @Sunny Hashmi (Airbyte) , do you have any updates regarding this issue?
5 Views
Open in Slack
PreviousNext
Powered by