SQL Query to overwrite record if it exists using Bigquery

Can someone please help me with an SQL query? I'm using the NetSuite connector to pull new transaction data and the Google BigQuery connector to add the data to BigQurey. 

The issue I am having is that I'm getting duplicate records in BigQurey. I believe I'm using a generic connector so there is no built-in record search for duplicates that I've seen with others. 

My assumption is I'll need to add a SQL Query on the BigQuery side to check for an existing record using the NetSuite ID and then overwriting that record if it exists. 

Can someone point me in the right direction to get that query?

TYIA!

0

Comments

3 comments
Date Votes
  • I have never used BigQuery, but I am pretty familiar loading data from NetSuite into Snowflake. The pattern we use for Snowflake is the following.

    1. Flow 1 exports all NS data and bulk inserts it into a 'netsuite_staging' table in Snowflake.
    2. Flow 2 merges the data in 'netsuite_staging' with 'netsuite'.
    3. Flow 2 deletes all the data in 'netsuite_staging' after the merge command completes.
    4. Flow 2 should auto run when Flow 1 completes, etc...

    The merge command is super powerful and does the equivalent of an insert vs update based on the data already existing in the final table.  i.e. you can merge millions of records in a couple seconds.

    Here are some pics.

    0
  • Thank you Scott, this is great. I'll give it a shot.

    0
  • In case it helps, I got confirmation from an engineer familiar with BigQuery that the same Snowflake patterns should work for BigQuery.

    Also, here is a pic of my Flow 2 that does the merge and delete SQL commands. It is worth noting that the source bubble in this flow only serves the purpose of checking if the staging table has any new data, and I am limiting the output of this dummy query to 1 because the steps in the flow dont care about the data being exported, because this flow servers the purpose of running independent SQL commands.  Hope this makes sense.  It is a handy pattern.

    0

Please sign in to leave a comment.

 

Didn't find what you were looking for?

New post