Forum Discussion
Helper IDAX/Query to Dedupe Data From Append Only BigQuery Warehouse
lemarcfj you are on the wrong path. Avoid dedup'ing on the client side as you would need to import all before doing so - unless PowerQuery is smart enough to unfold correctly (i.e. translate to native SQL).
You are better off dedup'ing on BQ side and import the filtered dataset instead.
1. Don't use a BigQuery View to dedup on-the-fly as this will likely be slow.
2. Have instad a separate table that contains the unique 'campaign' records - you can schedule such query in BQ to run daily for instance
3. once table is created/refreshed, query that one in PowerBI. You would avoid ODBC driver and can use the built-in BigQuery connector as-is (and leverage DirectQuery).
However, your depup logic is unclear and need rework - for instance I thought you would be interested in summing the spend instead of the last. Even so, you will probably end up using a window fuction like ROW_NUM() to sort by whatever and keep the first record of each window (e.g. campaign).
You can search on Stackoverflow as many have asked how to dedup already - or you can ask a new question if you are specific enough.
Helper ISo that SQL query was just a sample query that I found from the Slack blog post here. In my sample file I believe the dimension would we'd want to use instead of "id" would be campaign name as that is the dimension that I would like to view the data by and then a deduped spend metric.
Helper IHi,
Yes I do! As an FYI, that sample query I provided has no relation to my sample data set. The query needs to be adapted for my dataset and what I'm hoping to achieve. So while the query wil achieve deduplication, it isn't a query that was built specifically for me. That said, yes [Facebook Campaign] is my primary dimension.
Thanks a bunch for your assistance with this.
Could you please describe the logic you use to get Dedupe date based on sample data?
Regards,
Lydia- lemarcfj
Hi Lydia - let me know if this helps:
Grab the latest version of every row
In each Stitch-generated integration table, you’ll see a few columns prepended with _sdc. The column we’ll focus on here is the _sdc_sequence column. This column is a Unix epoch (down to the millisecond) attached to the record during replication and can help determine the order of all the versions of a row.
Stitch uses these sequence values in a few places to correctly order rows for loading, but it can be also used to grab the latest version of a record in an append-only table.
Let’s take a look at an example. Assume we have an orders table that contains:
- A Primary Key of id,
- The system _sdc columns added by Stitch, and
- Other order attribute columns
If you wanted to create a snapshot of the latest version of this table, you could run a query like this:
SELECT DISTINCT o.* FROM [stitch-analytics-bigquery-123:ecommerce.orders] o INNER JOIN ( SELECT id, MAX(_sdc_sequence) AS seq, MAX(_sdc_batched_at) AS batch FROM [stitch-analytics-bigquery-123:ecommerce.orders] GROUP BY id) oo ON o.id = oo.id AND o._sdc_sequence = oo.seq AND o._sdc_batched_at = oo.batch
This approach uses a subquery to get a single list of every row’s Primary Key, maximum sequence number, and maximum batched at timestamp. Since it’s possible to have duplicate records in your warehouse, the query also selects only distinct records of the latest version of the row. It then joins the original table to the Primary Key, maximum sequence, and maximum batched at, which makes all other column values available for querying