Understanding the Upsert Writing Mode behavior in BigQuery
What is the Upsert Writing Mode
Upsert is a
write mode available in our BigQuery product, which has a special and somewhat more complex behavior than Append and Replace. In this article, you will learn everything you need to know to use Upsert in your transfers.
- Learn more about the Dataslayer BigQuery platform and the other writing modes in this article.
How the Upsert mode works
Upsert updates the old data and adds the new data at the end of the table. It will always update and add the data within the date range selected in the query configuration.
This option takes longer to complete as it must update everything in the date range selected.
It is important to take into account that Upsert updates the metrics of all the information previously inserted, within the date range, but NOT the dimensions when updating the old data.
This means that if a campaign has changed its name, for example, an extra row of data will be added to this campaign as Upsert updates the metrics of the original dimensions, but doesn't detect if a dimension (like campaign name) has changed. So if any dimension changes, it will detect it as a new one and add a new line instead of updating the old one.
The system cannot detect if there has been a change in the dimension's data or if it is really new data. So, any change in the dimensions will be inserted as new rows in the table.
Exceptions
Some dimensions CAN be updated as well as the metrics are updated, these are Status dimensions.
If you want these dimensions to be updated you must select the "Update special dimensions in upsert operation" option in the query configuration.
Once the option is selected the Upsert mode will update the Status dimensions that you have selected in the query as well as the metrics of the previously inserted data, in addition to adding the new data at the end of the table.
Update special dimensions in upsert section
What are the Status dimensions?
In the following table, you can see all the Status dimensions for the different connectors.
Status Dimensions in Google Ads
|
Google Ads
|
| Campaign State |
| Serving Status |
| Ad group status |
| Keyword Status |
| Criterion serving status |
| Budget Status |
| Budget Usage |
| Ad Status |
| Ad Approval Status |
| Asset Group Status |
| Asset Status |
| Asset Approval Status |
| Asset Review Status |
| Search term Status |
| Call status |
| Audience Status |
| Bidding Strategy Status |
Status Dimensions in Amazon Ads
| Amazon Ads |
| Ad Keyword Status |
| Ad Group Status |
| Campaign Status |
Status Dimensions in Apple Search Ads
| Apple Search Ads |
| Campaign status |
| Campaign serving status |
| Campaign display status |
| Ad Group status |
| Ad Group serving status |
| Keyword status |
| Keyword display status |
| Creative Set display status |
| Creative Set status |
Status Dimensions in Microsoft Advertising
| Microsoft Advertising (Bing ads) |
| Account Status |
| Campaign Status |
| Budget Status |
| Budget Association Status |
| Ad Group Status |
| Ad Status |
| Keyword Status |
| Association Status |
Status Dimensions in Criteo
Status Dimensions in DV360
| DV360 |
| Advertiser Status |
| Creative Status |
| Insertion order status |
| Line item status |
| Partner Status |
| Trueview iar parental status |
| Trueview parental status |
Status Dimensions in DCM
| DCM |
| Ad status |
| Activity delivery status |
| Click delivery status |
| Impression delivery status |
Status Dimensions in Facebook Insights
| Facebook Insights |
| Post status type |
| Video live status |
| Video post publish status |
Status Dimensions in Facebook Ads
| Facebook Ads |
| Account status |
| Campaign status |
| Campaign Configured Status |
| AdSet status |
| AdSet Configured status |
| Ad status |
| Ad Configured Status |
Status Dimensions in Facebook Public Data
|
Facebook Public Data
|
| Verification status |
Status Dimensions in Google Ad Manager
| Google Ad Manager |
| Order Status |
| Order is archived |
| Ad Unit status |
Status Dimensions in Hubspot
Status Dimensions in Klaviyo
| Klaviyo |
| Flow status |
| Campaign status |
Status Dimensions in LinkedIn Ads
| Linkedin Ads |
| Campaign Group Status |
| Campaign Status |
| Creative Status |
| Creative Review Status |
Status Dimensions in Mailchimp
| Mailchimp |
| Campaign Status |
| Delivery status enable |
| Delivery status name |
| Member status |
| Automation status |
Status Dimensions in Pinterest
| Pinterest |
| Campaign Status |
| Ad group status |
| Ad group summary status |
| Ad status |
| Ad summary status |
Status Dimensions in Search Ads 360
|
Search Ads 360
|
| Account Status |
| Engine Status |
| Campaign Status |
| Ad Group Status |
Status Dimensions in Shopify
| Shopify |
| Order Fulfillment Status |
Status Dimensions in Snapchat
|
Snapchat
|
| Account Status |
| Campaign Status |
| Ad squad Status |
| Ad Status |
| Ad review status |
| Creative Packaging Status |
| Creative Review Status |
| Creative Status |
| Media Status |
Status Dimensions in Stripe
| Stripe |
| Charge Status |
| Transaction Status |
| Payout status |
| Invoice Status |
| Subscription Status |
| Refund Status |
Status Dimensions in TikTok Ads
| TikTok Ads |
| Advertiser status |
| Campaign status |
| Campaign operation status |
| Ad group status |
| Ad group operation status |
| Ad status |
| Ad operation status |
| Page status |
| Playable Status |
Status Dimensions in Twitter Ads
| Twitter Ads |
| Funding Instrument Status |
| Campaign status |
| Ad Group Status |
| Tweet Status |
| Tweet Schedule Status |
Status Dimensions in Twitter Insights
| Twitter Insights (organic) |
| Tweet scheduled status |
Status Dimensions in Woocommerce
|
Woocommerce
|
| Orders status |
| Products status |
| Products stock status |
Status Dimensions in YouTube
| Youtube |
| Subscribed Status |
| Video Privacy |
As always, please contact us via our live chat on our website or via email if you still have doubts or questions. We are happy to help!
