About default refresh types and lookback windows

While you have the flexibility to set the refresh method and lookback window for your data warehouse transfers at the transfer and table level, the system will automatically apply default settings if nothing is configured. This article explains the different default refresh types, how they are determined based on table structure, and the corresponding lookback windows.

Default refresh types based on table structure

The system automatically assigns a default refresh type based on the fields present in your table. These refresh types help ensure that your data is updated appropriately, depending on whether it's time-series data, aggregatable metrics, or non-aggregatable data.

Table structure: Both "Date" and "Today" included

• Default refresh type: Append all new rows (maximum lookback window)
• Description: This refresh type captures and appends a snapshot of all available data, split by date, to the existing table. It’s used when you want to track how aggregatable metrics evolve over time, such as tracking cost or impressions over a rolling period. The system defaults to this type when both "Date" and "Today" fields are included in your query.
• How it works: The system processes and stores each day’s data separately, appending the new data to the existing table. The lookback window is set to the maximum allowed by the data source, ensuring that all relevant historical data is captured and appended.
• Use case: Best for scenarios where you need to maintain a detailed history of how specific metrics have changed over time, especially in environments where large volumes of data are processed regularly.

Table structure: "Date" included, “Today” omitted

• Default refresh type: Append and deduplicate by date
• Description: The system defaults to this refresh type when the "Date" dimension is included in the query. This type of refresh updates each day of data individually, ensuring that the most recent data for each specific date is refreshed without affecting other dates. It's particularly useful for aggregatable, date-dependent metrics like clicks, impressions, and cost.
• How it works: The system processes data in reverse chronological order, one day at a time, replacing existing data for those dates. The refresh window determines how far back in time the data should be refreshed. For example, if you set a refresh window of 7 days, the last 7 days of data will be refreshed each time the transfer runs.
• Use case: Ideal for time-series data where you want to ensure accurate and up-to-date metrics for each specific date.

Table structure: "Today" included, "Date" omitted

• Default refresh type: Overwrite all rows (1-day lookback window)
• Description: This refresh type captures a snapshot of the most recent data (up until yesterday) and replaces the entire table's data each day. It's best suited for non-aggregatable metrics that are not tied to specific dates, such as follower counts or video views on social media. The system defaults to this type when the "Date" dimension is omitted but "Today" is included in the query.
• How it works: The system drops and recreates the table with the new data each day. The lookback window for this type is set to "Last 1 day," meaning only the data from the previous day is captured and refreshed.
• Use case: Useful for tracking metrics that do not provide historical data and need to reflect the latest available information each day.

Table structure: Both "Date" and "Today" omitted

• Default refresh type: Overwrite all rows (maximum lookback window)
• Description: This refresh type pulls all available historical data from the data source and replaces the entire dataset in your table. It is especially useful for capturing lifetime metrics or data from fields that are not time-series based, such as CRM contacts or deals. The system defaults to this type when both the "Date" and "Today" dimensions are omitted from the query.
• How it works: The system queries the maximum historical data allowed by the source and refreshes the entire table with this data. The lookback window is set to the maximum possible range, ensuring that all historical data is included.
• Use case: Ideal for retrieving comprehensive historical data, such as total reach and frequency for campaigns or lifetime metrics from CRM systems.