About refresh settings 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 settings tailored to specific types of data and reporting needs. 

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

Refresh method overview

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

• Append and deduplicate by date
• Append and deduplicate by today
• Full refresh

Append and deduplicate by date

This method 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 method 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 method ensures your reports are accurate without duplicating data. The refresh window setting will determine how many past days are included in these updates.

Append and deduplicate by today

This method 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. This method 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.

Full refresh

This method 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 method 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 method

1. On the Supermetrics Hub, go to Storage → Transfers, and select a transfer to modify.
2. Scroll down to the Table group section and select a table group to modify. This opens a popup where you can view and change the fields and the refresh settings.
3. On the Refresh settings tab, select the most appropriate refresh method based on your data and reporting needs:
   • Append and deduplicate: Set the refresh window for the transfer to control how many past days will be updated.
   • Full refresh: 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 method is essential for ensuring your data is managed efficiently and accurately. By understanding the differences between Append and deduplicate by date, Append and deduplicate by today, and Refresh all, 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.