> For the complete documentation index, see [llms.txt](https://docs.quanti.io/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.quanti.io/key-concepts/data-insertion-strategies/insertion-method-selection-guide.md).

# Insertion method selection guide

## Introduction

Choosing the appropriate insertion method is crucial for data quality and costs. This guide helps you select the right method through a series of simple questions.

While **Quanti automatically selects the optimal method for standard connectors**, this guide is essential when configuring **custom connectors** or understanding Quanti's choices.

***

## Decision Guide: 4 Questions

{% stepper %}
{% step %}
**What type of data are you synchronizing?**

A) Events or transactions that never change once created

* Examples: clicks, impressions, page views, purchases, API calls
* → Go to [INSERT Mode](/key-concepts/data-insertion-strategies/insert-mode.md)

B) Metrics or measurements tied to a date

* Examples: daily impressions, daily spend, daily conversions
* → Go to Question 2

C) Attributes or properties of entities

* Examples: campaign names, product prices, customer information, account settings
* → Go to Question 3
  {% endstep %}

{% step %}
**Metrics - Does the source recalculate these metrics?**

Context: Many advertising platforms (Google Ads, Meta, etc.) recalculate metrics retroactively due to attribution windows, processing delays, or corrections.

A) YES - Metrics may be updated for past dates

* Examples: Google Ads conversions (7-day attribution), GA4 metrics (processing delays)
* → Go to [REPLACE Mode](#answer-replace-mode-for-facts)

B) NO - Metrics are final once created

* Examples: Real-time sensor data, minute-level logs
* → Go to [INSERT Mode](/key-concepts/data-insertion-strategies/insert-mode.md)

C) Unsure

* → Default to [REPLACE Mode](/key-concepts/data-insertion-strategies/replace-mode-delete-and-insert.md) (safer for most advertising/analytics platforms)
  {% endstep %}

{% step %}
**Attributes - Do you need to track how these attributes change over time?**

Context: Do you need to know the historical values? For example: "What was the campaign status on January 15th?"

A) YES - I need complete history of changes

* → Go to [UPSERT Mode WITH historization](/key-concepts/data-insertion-strategies/upsert-mode-update-and-insert.md)

B) NO - I only need the current state

* → Go to Question 4
  {% endstep %}

{% step %}
**Current state only - Do entities deleted from source need to be removed from your warehouse?**

Context: UPSERT Mode keeps all records (even if deleted from source), while REPLACE Mode removes them.

A) YES - Deleted entities must be removed immediately

* Example: Product catalog must exactly match current source state
* → Go to [REPLACE Mode for dimensions](/key-concepts/data-insertion-strategies/replace-mode-delete-and-insert.md)

B) NO - I want to keep deleted entities for historical reference

* Example: Keep deleted campaigns to preserve historical performance data
* → Go to [UPSERT Mode WITHOUT historization](/key-concepts/data-insertion-strategies/upsert-mode-update-and-insert.md) ⭐ **Recommended default**
  {% endstep %}
  {% endstepper %}

***

## Methods Comparison

| Aspect                       | INSERT Mode                    | REPLACE Mode                                                 | UPSERT Mode (no hist)          | UPSERT Mode (with hist)         |
| ---------------------------- | ------------------------------ | ------------------------------------------------------------ | ------------------------------ | ------------------------------- |
| **How it works**             | Append new rows                | Delete scope + Insert                                        | Update or Insert (based on PK) | Insert new version              |
| **Deletion scope**           | None                           | <p>• Facts: reference date<br>• Dimensions: entire table</p> | None                           | None                            |
| **Duplicate risk**           | ⚠️ High                        | ✅ None                                                       | ✅ None (hash optimization)     | ✅ None                          |
| **Storage cost**             | ❌ High (grows continuously)    | ✅ Stable                                                     | ✅ Stable                       | ❌ High (versions accumulate)    |
| **Query cost**               | ⚠️ High (deduplication needed) | ✅ Low (direct queries)                                       | ✅ Low (direct queries)         | ❌ Very High (scan all versions) |
| **Processing cost**          | ✅ Lowest (no WHERE clause)     | ⚠️ Medium (DELETE ops)                                       | ⚠️ Medium (MERGE + hash check) | ⚠️ Medium (MERGE + hash check)  |
| **Historical tracking**      | ⚠️ Manual (via timestamps)     | ❌ Lost                                                       | ❌ No                           | ✅ Complete                      |
| **Referential integrity**    | ⚠️ At risk (duplicates)        | ⚠️ At risk (deletions)                                       | ✅ Preserved                    | ✅ Preserved                     |
| **Hash optimization**        | ❌ N/A                          | ❌ N/A                                                        | ✅ Skips unchanged rows         | ✅ Skips unchanged rows          |
| **Query complexity**         | ❌ High (must deduplicate)      | ✅ Simple                                                     | ✅ Simple                       | ❌ High (must filter latest)     |
| **Handles source updates**   | ❌ Creates duplicates           | ✅ Replaces cleanly                                           | ✅ Updates cleanly              | ✅ Creates new version           |
| **Handles deleted entities** | N/A                            | ✅ Removes them                                               | ⚠️ Keeps them                  | ⚠️ Keeps them                   |

***

## When to use each method

Use cases and recommendations:

* Use INSERT Mode when:
  * Events are truly immutable (never updated by source)
  * Raw landing zone before transformation
  * High-granularity timestamped data
  * Example: click events, API logs, sensor readings
* Use REPLACE Mode when:
  * Facts: Source recalculates metrics (with lookback window)
  * Dimensions: Deleted entities must be removed immediately
  * Clean slate needed for each sync
  * Example: Google Ads metrics, product catalogs (exact match)
* Use UPSERT Mode (no historization) when:
  * Default choice for dimension tables ⭐
  * Need to reflect updates while preserving data
  * Want to keep deleted entities for historical reference
  * Table size or query volume makes historization too expensive
  * Example: campaigns, customers, products (90% of dimensions)
* Use UPSERT Mode (with historization) when:
  * Table is small (< 10,000 rows)
  * Query volume is low
  * Strong compliance/audit requirement
  * Understand and accept the query cost implications
  * Example: small account hierarchies, limited configuration tables

***


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.quanti.io/key-concepts/data-insertion-strategies/insertion-method-selection-guide.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.