Snowpipe Streaming Duplicate records / Not Exactly Once

[imrohankataria]

Snowpipe Streaming Duplicate records / Not Exactly Once

[sfc-gh-japatel]

Thanks
Few questions:

1. Will you be willing to share some logs along the timeline where you experienced duplicates?
2. Where have you seen duplicates? In kafka table on snowflake side?

[imrohankataria]

What exact you'd like to see? I can do a grep and get you exact logs

[imrohankataria]

Duplicate in Kafka Snowflake tables (where it dumps data in snowflake) @sfc-gh-japatel

[sfc-gh-japatel]

What version of SF KC are you using?
Logs to see if there was any exception or unique scenario to help us better understand where and how duplicates would occur

[imrohankataria]

Entire log would be huge, but if you can comment what exactly needs to be seen in logs? I can do a grep and get exact what you need
version - 2.0.0

[sfc-gh-japatel]

Few more questions/asks:

1. Can you use latest version https://github.com/snowflakedb/snowflake-kafka-connector/releases/tag/v2.0.1
2. Can you repro this issue everytime?
3. Logs around the time you see duplicate candidates in your table in SF. I want to see where because of which exception this is occurring.
4. I also see 38 topics in your single connector, do you have any conflicts here in terms of topic2tablemap? i.e does all of them go to same table or different tables?

[imrohankataria]

One thing I want to mention,

I try to reload data a lot of times, and what I do is recreate sink connector with new name, so it loads from beginning of the topic.
I dont change the base table name in snowflake, so it always puts data in same table, from same kafka topic, but a different sink connector name.

[sfc-gh-japatel]

I see, well this might be one of the reasons why you see duplicates. Can you always choose a new table in snowflake?

Also we have had bug in 2.0.0 which got fixed in 2.0.1 which could see missing data if you have lot of rebalances.
We havent seen duplicate issue but what you mention could be the reason.

[imrohankataria]

Again, The Way I have setted up the whole setup is, there are streams on top of tables.
Issue is what Im seeing on Kafka Broker/Eventhub, Data going out x2 times.

[imrohankataria]

Hope it doesn't have to do anything with the Offset resetting.
Also if there is a network error, doesn't it have to check the offset value before reloading data? @sfc-gh-japatel

[sfc-gh-japatel]

Wo do that today! please verify from snowflake side that offset is ingested twice on your landing table. metadata column can be used for that and you can run some queries to identify if you have duplicates.

But if you restart the connector with same name you are bound to get this issue since new connector name means new consumer group and it will start from offset 0.

I would recommend:

1. upgrading to newer version
2. see if you can repro this again.
3. send logs if you can.
4. Confirm how you are checking duplicate data on snowflake side. sql Query if possible.

Thanks

[imrohankataria]

How to check?

…

On Wed, Sep 27, 2023 at 1:46 PM Jay Patel ***@***.***> wrote: Wo do that today! please verify from snowflake side that offset is ingested twice on your landing table. — Reply to this email directly, view it on GitHub <#710 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AJ7PIEQN3R52EFPIYOIWHATX4RQ6PANCNFSM6AAAAAA5IA6FMA> . You are receiving this because you authored the thread.Message ID: ***@***.***>

[sfc-gh-japatel]

https://docs.snowflake.com/en/user-guide/kafka-connector-overview#schema-of-tables-for-kafka-topics

Here is what I use

select 
record_metadata:"topic"::string as TOPIC, 
record_metadata:"partition"::number as PARTITION_NO,
count(distinct record_metadata:"offset"::number) as unique_offsets_per_partition,
count(record_metadata:"offset"::number) as count_per_partitions,
min(record_metadata:"offset"::number) as min_offset,
max(record_metadata:"offset"::number) as max_offset,
(max_offset-min_offset+1) as diff_min_max_offsets,
case diff_min_max_offsets-unique_offsets_per_partition
    when 0 then 'Nothing Missing'
    else 'Missing Data/Duplicate Data'
    end as data_completeness
from <base_table>
group by TOPIC, PARTITION_NO
order by PARTITION_NO
;

[sfc-gh-japatel]

Hi @imrohankataria closing this since there has been no activity, feel free to open again with more details on how you were able to reproduce duplicate data. We would definitely take that as a priority. thanks