About lookback windows in data warehouse transfers

The lookback window is a crucial feature in our data warehouse system and it allows you to control how much historical data is refreshed during each transfer run. This article explains what the lookback window is, how it works, the available settings, and how it differs from the refresh window. Understanding these settings will help you optimize your data transfers to ensure efficiency and accuracy.

Lookback window

The lookback window is a setting that determines how far back in time your data should be refreshed during each transfer run. It specifies the range of historical data that will be included in the refresh process, starting from a specific date up until yesterday. The lookback window is particularly useful when you need to update recent data only, and preserve data source quotas and limit the transfer payloads.

How does the lookback window work?

When you configure a data transfer, you can set the lookback window to define the period of historical data that needs to be refreshed. This setting is especially helpful for large datasets, as it allows you to limit the amount of data being processed, thus saving on data source API quotas and reducing system load.

For example, if you set the lookback window to last 30 days, each transfer run will refresh data for the past 30 days up until yesterday. This ensures that your most recent data is always up-to-date without unnecessarily reprocessing older data.

Available lookback window settings

When setting up the lookback window, you can choose from several options depending on your data and reporting needs:

• Last x days/weeks/months: You can specify the number of days, weeks, or months to look back when refreshing data. For instance, setting it to last 7 days means each transfer run will refresh data for the past 7 days up until yesterday. Last 2 weeks means that each transfer refreshes data for the current week and the previous full week.
• Maximum: This option refreshes all available historical data up until yesterday, using the maximum date range allowed by the data source. While this ensures comprehensive data updates, it can involve moving large amounts of data, potentially consuming significant API quotas. For backwards compatibility, the maximum setting is the default option.

Using the lookback window

The lookback window is most effective in scenarios where:

• You need to update recent data only: If your data doesn't change frequently over long periods, the lookback window allows you to focus on refreshing only the most recent data, saving resources.
• You're working with rolling metrics: For example, in social media reporting, where metrics like 30-day reach and frequency are tracked, the lookback window can be used to ensure that only the relevant period is updated.
• You want to control data source usage: By limiting the date range for data refreshes, you can reduce the amount of data moved during transfers, which helps manage your data source API quotas effectively.

Difference between lookback window and refresh window

While the lookback window and refresh window may seem similar, they serve different purposes and are used in different contexts within the data warehouse system.

Lookback window

• Used in: Append all new rows and overwrite all rows modes.
• Function: Determines how far back in time the data will be refreshed during each transfer run. It’s used to define the period of historical data that should be included in the refresh.
• Best for: Limiting the scope of data updates, especially when dealing with large datasets or rolling metrics.

Refresh window

• Used in: Overwrite specific dates mode.
• Function: Controls how far back in time data should be updated for specific dates. It’s used to replace data for specific past dates with new data while keeping other data unchanged.
• Best for: Time-series data where you want to ensure that specific dates (such as daily metrics) are updated with the latest information. It's also 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.

How to set the lookback window

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 lookback window option.
   Choose the appropriate duration (such as Last 7 days or Last 30 days) or the maximum option based on your data and reporting needs.
4. To save your configuration, first click Apply, and then save the entire transfer configuration.