BigQuery Destination - Not Appending Data?

Morning all,

I have a bit of an odd problem and I can’t figure out where the issue is.

I have a MySQL integration, this is performing a full table replication across four different tables. This process runs just before midnight every day.

This process is working fine and successfully downloading and sending the data to my destination, BigQuery.

In BigQuery I can see my data is present, if I check the _sdc_received_at I can see this data loads at the correct time.

However, all my previous data is missing. Looking at the total number of rows I have only one days’ worth of data. The process has been ran three times, yet the data is only the most recent version.

Querying the table in bigquery to look for distinct _sdc_table_version shows only one version as well.

I thought that maybe I had configured something incorrectly and checked the table details inside bigquery. That confirmed the table was created a few days ago, and confirms with the last modified the last upload that happened.

I’ve tried searching around and I can’t see anyone else with this issue. I’m confused as I want the append-only feature of bigquery, collecting history for analysis is just want I need to do.

Is there a setting or option I am missing? Is this potentially something on BigQuery’s side, as I don’t see any real options or configuration that would matter inside of Stitch.

Thanks for any help or guidance you can offer.

Cheers,
Al

Managed to find the solution.

Summary: Data with a new table version
Scenario: A full set of data for a table (set to Full Table Replication) arrives
Expected Behavior: Table is truncated and new data is loaded in a single atomic action.

I need to change the MySQL extract to provide updated data.

Key-Based Incremental Replication, requires something which changes over time, I have a _uuid as the primary key but I don’t have a modified/updated column (that is controlled elsewhere.)

Looks like the best option is Log-Based Incremental Replication, I’ll go back to trying to get that setup.

Hopefully this helps anyone else who ran into this and didn’t see the expected behaviour.

1 Like