Just spent the last three hours untangling a mess in our daily campaign pull, and it looks like the culprit is the Google Ads connector in Ideogram. Again.
We're seeing what appears to be a systematic duplication of rows, but it's not a simple 2x multiplier. It seems to be creating near-duplicate records for certain campaigns and ad groups on specific days, with identical metrics except for a bizarre, incremental internal ID that isn't part of the source schema. This isn't a "refresh overlap" issue; we've checked the incremental logic and the date windows are clean.
Before I dive into writing a custom pre-aggregation layer to dedupe on our dime, has anyone else run into this? Specifically:
- Duplication that doesn't align with any obvious grain in the source data.
- Inconsistent occurrence—some days are fine, others are a 20% bloat.
- The mysterious `_sdc_sequence` or similar fields showing unexpected patterns.
I'm skeptical of any "it's the API" hand-waving unless someone has a reproducible test that isolates the problem to Google's side. My bet is on the connector's handling of `finalized` vs. `non-finalized` data or a join gone wrong in the underlying query logic.
Data skeptic, not a data cynic.
Your mention of an incremental internal ID that's not in the source schema is the critical clue. I've seen this exact pattern when a connector's internal state management for change data capture gets confused, often around mid-day data refreshes or schema alterations in the source. That `_sdc_sequence` field is a tell; it's usually a timestamp for ordering changes, and if it's incrementing on duplicate rows, the connector believes it's receiving distinct updates for the same logical row.
Before you build a dedupe layer, check the raw data lake or staging area if Ideogram provides one. Often, the duplication is introduced in the initial ingestion, and any transformation built on top just perpetuates it. A quick test is to run a direct extract for a problematic day twice, back-to-back. If the duplicate count increases, the issue is in the sync logic, not the API. I ended up having to use a custom SQL extraction clause in the connector to force a stable grain, which is less than ideal but stopped the bleeding.
It's usually the finalized data flag, but not in the way you'd think. The connector often pulls non-finalized data, then later fetches the 'finalized' version for the same period. If the logic for marking rows as superseded is flawed, you get both. That incremental `_sdc_sequence` is the smoking gun - it's the connector's attempt to order these perceived changes, not an API artifact.
Check the raw extraction logs for overlapping time windows, even if your incremental logic seems clean. The connector's definition of the window and the API's data readiness might be out of sync. Building a dedupe layer is just treating the symptom; you'll be fighting a losing battle on data volume and cost.
Consider forcing a full refresh on a small, problematic date range. If the duplication disappears, your incremental settings are the culprit. If it persists, open a ticket with the vendor and attach the raw payload snippet. They've seen this before.
Your fancy demo doesn't scale.
Yeah, that `_sdc_sequence` field sounds like a ghost in the machine. I'm new to Ideogram but I've seen similar weird IDs in other connectors. It's like it's tracking something we can't see.
Your point about `finalized` vs. `non-finalized` data is interesting. Could it be pulling a provisional row, then the final one, but the logic to replace the first one just... fails? So you get both, with a sequence number bump.
Is there a setting in the connector to only pull finalized data? Might be a dumb question, but sometimes the obvious toggle gets missed.