Appending an update set to itself

Jbrorby Member
edited March 2023 in SQL DataFlows


I have a MySQL dataflow that is built from API reports from a vendors application. The idea is that we did a one time historical pull of the API as dataset 1. Then, the same API that only pulls 1 day of data and appends to the historical dataset as dataset 2.

But, every once and a while it seems as if data is dropping out. I've been struggling to determine if this is an error on the API side of things, meaning the data is missing from the report, or if its an issue with how the MySQL dataflow is built, or maybe a connector issue.

So I am just wondering, does anyone see any flaws/potential ways data could be removed/left out in this following configuration? The goal here is to simply have all data continually append every day.

First, I took the "all time historical" dataset into the MySQL dataflow and created the output of "BB_Billing_Activities_Raw". Then, I modified this MySQL dataflow and replaced the "all time historical" dataset with "BB_Billing_Activities_Raw", so that it is feeding into itself.

Then, I added the "daily update" dataset that just looks at one day. and then this is the transformation

(The part I don't understand, which the vendor told me to add, is the "where u.billing-acitivty-id' is null" because this will never be null, and so the top part will return nothing. But I'm assuming this is just to make sure schema matches)

select m.*

from `bb_billing_activities_raw`m 

left outer join `bb_billing_activities_update` u 

on m.`billing-activity-id` = u.`billing-activity-id`

where u.`billing-activity-id` is null

union ALL

select * from bb_billing_activities_update