Hello everyone! I've been using the

tap-facebook

extractor to get data from the

adsinsight_default

data stream, but all my data has been duplicated for some reason as seen in the screenshot. Can anyone help me figure out what's happening? I've selected only this specific stream in my yml file and it seems like the first day of the extraction it's not duplicated, it feels like it's extracting data for Day 1 and Day 2, then Day 2 and Day 3, Day 3 and Day 4, and so on...

Can you check the

date_start

and

date_stop

cols for the duplicated rows? Are they the same values?

Hi Andy, thank you for the message. It seems to be the same date for both cols and in my yml file i've provided only the date start, date stop(for testing) and streams(the adsinsight_default.* was also a test)

Do you have a primary key(s) defined on the table? Is it possible you have run the tap twice and just appended the data? What is the query you are executing in bigquery?

I dont think i have a primary key defnied, the raw data comes as in the print and here's the data column and query

WITH META_ADS AS (
    SELECT
        data
    FROM my_database i
    WHERE STRING(JSON_EXTRACT(data , '$.campaign_name')) LIKE '%pace%'
),
BASE AS (
    SELECT
        DATE(PARSE_DATETIME('%Y-%m-%d', STRING(JSON_EXTRACT(data, '$.date_start')))) AS date,
        DATE(PARSE_DATETIME('%Y-%m-%d', STRING(JSON_EXTRACT(data, '$.date_stop')))) AS date_stop,
        'meta' AS channel,
        SPLIT(STRING(JSON_EXTRACT(data , '$.campaign_name')), '_')[SAFE_OFFSET(0)] AS campaign_id,
        SPLIT(STRING(JSON_EXTRACT(data , '$.ad_name')), '_')[SAFE_OFFSET(0)] AS ad_id,
        STRING(JSON_EXTRACT(data , '$.campaign_name')) AS campaign_name,
        STRING(JSON_EXTRACT(data , '$.ad_name')) AS ad_name,
        SAFE_CAST(STRING(JSON_EXTRACT(data , '$.spend')) AS FLOAT64) AS spend,
        SAFE_CAST(STRING(JSON_EXTRACT(data , '$.impressions')) AS INT64) AS impressions,
        SAFE_CAST(STRING(JSON_EXTRACT(data , '$.inline_link_clicks')) AS INT64) AS clicks,
        SAFE_CAST(JSON_VALUE(data, '$.video_15_sec_watched_actions[0].value') AS INT64) AS complete_views
    FROM META_ADS
)

SELECT 
  date,
  date_stop,
  campaign_id,
  ad_id,
  --COUNT(*) AS qtd_registros,
  --SUM(spend) AS soma_spend,
  --ARRAY_AGG(spend) AS valores_spend,
  spend,
  impressions
FROM BASE
WHERE ad_id LIKE '%200-09%'
AND date BETWEEN '2025-11-19' AND '2025-11-21'

When troubleshooting these kind of issues I like to step back away from my tap & target combination and try to pinpoint tap or target issue. Try running

meltano run tap-facebook target-jsonl

first and inspect the output jsonl file. Are there any duplicates present? If so it's a tap issue need to investigate how/why the API or tap produces them. If not then you know the issue is on the target side