About refresh modes in data warehouse transfers

When you're transferring data to your data warehouse using Supermetrics, you can use the flexible options to manage how your data refreshes, including different refresh modes tailored to specific types of data and reporting needs. 

This article explains the 3 available refresh modes, how to configure them, and gives examples of when each mode is most suitable.

Refresh mode overview

There are 3 refresh modes available in the data warehouse system:

• Append and deduplicate by date
• Append all new rows
• Overwrite all rows

Append and deduplicate by date

This mode replaces the existing data for specific dates with new data while keeping all other data unchanged. The refresh window setting on the transfer level determines how far back in time the data will be refreshed. It's a good practice to match the refresh window with your attribution window to ensure that the data being refreshed accurately reflects the period during which conversions are attributed.

Example use cases:

• Aggregatable metrics in ad networks: When reporting on metrics like impressions and clicks across different ad networks, this mode is ideal. These metrics are often aggregated by date, and you need to ensure that the data for each date reflects the most recent totals. The refresh window allows you to control how many days back the system should update, ensuring only the necessary data is refreshed.
• Daily metrics: For daily sales or revenue reports, where each day's data might be updated with the latest numbers, this mode ensures your reports are accurate without duplicating data. The refresh window setting will determine how many past days are included in these updates.

Append all new rows

This mode adds all new rows of data to the existing dataset. The rows that were updated by Supermetrics during the same day will be replaced with other rows left untouched. The mode is available only when the Today field is present in the table, and that field will be used to deduplicate the data.

Example use cases:

• Rolling metrics in social media: For metrics like 30-day reach and frequency in Facebook Ads, where new data is appended without altering historical data.
• Lifetime metrics: Append the most recent follower count to track social media growth over time.

Overwrite all rows

Description: This mode deletes all existing data in the dataset and replaces it with the new data. The Lookback window is used to determine the range of historical data that will be fully overwritten. It's best suited for entity-based data where a complete refresh is necessary to ensure the data is up-to-date and accurate.

Example use case:

• Entity-based data in CRM systems: For data like Salesforce opportunities, where each record represents a distinct entity and you need the most current information, this mode is ideal. A full refresh ensures that your dataset reflects all changes, such as updated opportunity stages or values.
• Product inventories: In scenarios where you need to refresh an entire product inventory to reflect the latest stock levels, pricing, or availability, a full refresh guarantees that no outdated data is left behind.

How to set the refresh mode

1. On the Supermetrics Hub, go to Storage → Transfers, and select a transfer to modify.
2. Go to the schema section and select a schema. This open a popup where you can view and the fields and the refresh settings.
3. On the Refresh settings tab, select the most appropriate refresh mode based on your data and reporting needs:
   • Overwrite specific dates: Set the refresh window to control how many past days will be updated.
   • Append all new rows and Overwrite all rows: Use the lookback window to specify how far back in time data should be appended or overwritten.
4. To save your configuration, first click Apply, and then save the entire transfer configuration.

Selecting the correct refresh mode is essential for ensuring your data is managed efficiently and accurately. By understanding the differences between Overwrite specific dates, Append all new rows, and Overwrite all rows, and by properly setting the refresh window or lookback window according to your needs, you can optimize your data warehouse operations to fit your specific use cases.