> ## Documentation Index
> Fetch the complete documentation index at: https://support.detrics.io/llms.txt
> Use this file to discover all available pages before exploring further.

# Sync Modes

> Choose between Incremental, Full Refresh, and Full Append for each table

<Info>**Last updated:** March 18, 2026</Info>

When creating a transfer, you assign a **sync mode** to each table in the table group. The sync mode determines how data is written to and maintained in BigQuery. Choosing the right sync mode for each table is one of the most important decisions you'll make.

## Overview

| Sync Mode        | Behavior                                     | Best For                                                    | BigQuery Cost                              |
| ---------------- | -------------------------------------------- | ----------------------------------------------------------- | ------------------------------------------ |
| **Incremental**  | Delete + insert within a rolling date window | Time-series data (daily ad metrics, analytics)              | Lowest (processes \~1-3% of data per sync) |
| **Full Refresh** | Replace the entire table                     | Snapshot data (campaign settings, product catalogs)         | Higher (replaces full dataset each sync)   |
| **Full Append**  | Append without removing anything             | Tracking changes over time (metric evolution, audit trails) | Table grows indefinitely                   |

***

## Incremental (Append + Dedup)

Incremental is the most common sync mode and the best choice for ongoing daily data.

### How It Works

On each sync, Detrics:

1. Fetches data for the last **N days** (the refresh window, for example the last 3 days)
2. **Deletes** all existing rows in BigQuery that fall within that date window for the synced accounts
3. **Inserts** the fresh data

This is done atomically using a staging table. If the insert fails, the delete is rolled back and your existing data remains untouched.

### Why a Refresh Window?

Marketing platforms don't finalize data instantly. Attribution data, conversion tracking, and billing adjustments can take 1-7 days to settle:

* **Google Analytics 4**: Data typically finalizes within 24-48 hours
* **Meta Ads**: Attribution windows can extend up to 7 days depending on your settings
* **Google Ads**: Conversion data may update for up to 7 days

The refresh window ensures that Detrics re-fetches and corrects this data as it finalizes. A 3-day window covers most platforms' finalization periods.

### When to Use Incremental

* Daily ad performance metrics (spend, impressions, clicks, conversions)
* Analytics data (sessions, pageviews, events)
* Any time-series data where you want the latest values without duplicates

### Constraints

* Time aggregation must be set to **Daily** (one row per day per dimension combination)
* The table must include a date-based dimension

### Example

A table with a 3-day refresh window, syncing daily:

```
Day 1 (Initial sync):  Fetches Mar 1-15 → Inserts all rows
Day 2 (Scheduled sync): Fetches Mar 14-16 → Deletes Mar 14-16, inserts fresh
Day 3 (Scheduled sync): Fetches Mar 15-17 → Deletes Mar 15-17, inserts fresh
```

Data older than the refresh window is never touched. Only the window rows are refreshed.

***

## Full Refresh

Full Refresh replaces the entire BigQuery table on each sync. Simple, predictable, and ideal for data that doesn't have a meaningful time dimension.

### How It Works

On each sync, Detrics:

1. Fetches the full configured date range from the platform
2. **Replaces** the entire BigQuery table with the new data (using BigQuery's `WRITE_TRUNCATE`)

This is atomic at the BigQuery level. The old data is only removed once the new data is fully loaded.

### When to Use Full Refresh

* Campaign settings and structure (campaign names, status, budgets, targeting)
* Product catalogs (Shopify products, Google Merchant Center listings)
* Account-level configuration data
* Any table where you want a clean, current snapshot
* Tables with **Total** time aggregation (no date breakdown)

### Cost Considerations

Full Refresh processes the entire dataset on every sync. For a table with 12 months of daily campaign data across 50 ad accounts, this means re-loading the full dataset each time. Consider using **Incremental** for large historical tables to reduce costs.

### Example

```
Day 1 (Initial sync):   Fetches all data → Creates table with 10,000 rows
Day 2 (Scheduled sync):  Fetches all data → Replaces table with 10,200 rows
Day 3 (Scheduled sync):  Fetches all data → Replaces table with 10,150 rows
```

The table always reflects exactly what the platform API returns right now.

***

## Full Append

Full Append adds new rows on every sync without deleting or updating anything. The table grows over time, preserving every snapshot.

### How It Works

On each sync, Detrics:

1. Fetches the full configured date range from the platform
2. **Appends** all rows to the existing BigQuery table

No rows are ever deleted or updated. Each sync adds a complete snapshot.

### When to Use Full Append

* **Social media follower snapshots**: Take daily snapshots of Instagram followers, Facebook page likes, or TikTok followers to track growth over time
* **Campaign settings change history**: Track when budgets, statuses, or targeting changed on your campaigns and ad sets by comparing snapshots across sync dates
* **E-commerce inventory tracking**: Monitor stock level changes over time for Shopify, WooCommerce, or Google Merchant Center products
* **Tracking metric evolution**: See how lifetime ROAS, total reach, or cumulative spend changed day-over-day by comparing snapshots from different sync dates
* **Audit trails**: Keep a record of what the platform reported at each point in time
* **Non-overlapping data**: When you're sure each sync produces unique rows that don't overlap with previous syncs

### Important Warning

<Warning>
  Full Append tables grow indefinitely. If you sync daily with a 12-month date range, after one year you'll have 365 copies of each data point. Use this mode intentionally and understand the storage implications.
</Warning>

### How to Identify Which Sync Produced Each Row

Every row includes `_detrics_sync_id` and `_detrics_synced_at`, so you can always distinguish between snapshots:

```sql theme={null}
-- Compare campaign spend between two snapshots
SELECT
  a.campaign_name,
  a.spend AS spend_yesterday,
  b.spend AS spend_today,
  b.spend - a.spend AS daily_change
FROM `project.dataset.campaign_metrics` a
JOIN `project.dataset.campaign_metrics` b
  ON a.campaign_name = b.campaign_name
  AND a.date = b.date
WHERE a._detrics_synced_at = '2026-03-16'
  AND b._detrics_synced_at = '2026-03-17'
```

***

## Choosing the Right Sync Mode

Use this decision tree to pick the right mode for each table:

1. **Does this table have daily time-series data?** (metrics that change day by day)
   * Yes → **Incremental**
   * No → Continue to 2

2. **Do you want to track how values change over time?** (e.g., daily snapshots of Instagram followers to track growth)
   * Yes → **Full Append**
   * No → Continue to 3

3. **Is this snapshot/reference data?** (campaign settings, product catalog, entity metadata)
   * Yes → **Full Refresh**

### Quick Reference by Data Type

| Data Type                                           | Recommended Mode            | Why                                                                   |
| --------------------------------------------------- | --------------------------- | --------------------------------------------------------------------- |
| Daily ad performance (spend, impressions, clicks)   | Incremental                 | Deduplicates by date, handles attribution delays                      |
| Campaign/ad group settings (names, status, budgets) | Full Refresh                | No date dimension, want current state                                 |
| Product catalog (Shopify products, inventory)       | Full Refresh                | Want current snapshot                                                 |
| Analytics sessions and events                       | Incremental                 | Daily time-series data                                                |
| Social media followers/reach snapshots              | Full Append                 | Daily snapshots to track follower growth over time                    |
| Campaign/ad set settings change history             | Full Append                 | Track budget changes, status changes, and targeting updates over time |
| E-commerce inventory levels                         | Full Append                 | Track stock level changes over time for products                      |
| Lifetime/all-time metrics (total ROAS, reach)       | Full Refresh or Full Append | Full Refresh for current state; Full Append to track changes          |
| Order history (Shopify, WooCommerce)                | Incremental                 | Time-series, deduplicates by date                                     |
| Email campaign performance                          | Incremental                 | Daily engagement metrics                                              |

## Historical Timeframe & Initial Sync

When you create a transfer, Detrics runs an **initial sync** that fetches historical data. The amount of history fetched is controlled by the **historical sync range** configured on each table (e.g., last 3 months, last 12 months, or all time).

How the initial sync works depends on the sync mode:

* **Incremental**: The initial sync fetches the full historical range and loads it into BigQuery. After this first load, subsequent syncs only fetch the refresh window (for example, the last 3 days) and deduplicate within that window. Data outside the window is preserved and never re-fetched
* **Full Refresh**: The initial sync and all subsequent syncs behave the same way: fetch the full configured date range and replace the table. The historical range determines how far back the data goes on every sync
* **Full Append**: The initial sync fetches the full historical range and appends it. Every subsequent sync also fetches the full range and appends again, creating a new snapshot each time

<Tip>
  For Incremental tables, the historical sync range only matters for the first sync. After that, only the refresh window is re-fetched. For Full Refresh and Full Append, the historical range determines the scope of every sync.
</Tip>

## Mixing Sync Modes in One Transfer

When creating a transfer, you can assign different sync modes to each table. For example, a Meta Ads transfer might include:

* **Campaign Daily Performance** → Incremental (daily metrics)
* **Campaign Settings** → Full Refresh (names, status, budgets)
* **Lifetime ROAS Tracking** → Full Append (track how ROAS evolves)

All three tables sync together on the same schedule, each using its own mode.