airbytehq / airbytehq/airbyte

[source-mssql] Full refresh append produces duplicate records due to sampling query ignoring partition bounds

Abierto
#74,755 3 comentarios 0 reacciones 0 asignados Ver en GitHub
area/connectors autoteam community connectors/source/mssql needs-triage team/extensibility type/bug
Lenguaje dominante
Python
Estrellas
22.1k
Forks
5.3k
Métricas de merge de PR
Métricas de PR pendientes

Descripción

### Connector Name

source-mssql

### Connector Version

4.3.2

### What step the error happened?

During the sync

### Relevant information

### What's happening

Running a Full Refresh Append sync on a large MSSQL table (~26M rows) results in nearly double the expected records in the destination (~50M). The same table synced with Full Refresh Overwrite produces the correct count.

The problem is that when the connector splits work into partitions across multiple rounds, the sampling query used to determine split boundaries runs against the **entire table** - not just the range the partition is supposed to cover. This can cause round 2 to re-read most of the data that was already emitted in round 1.

### How to reproduce

1. Set up a Full Refresh Append sync for a large MSSQL table with an integer PK.
2. Run the sync.
3. Compare row counts - the destination will have roughly 2x the source table's row count.

### What I observed

I ran both Overwrite and Append syncs on the same table (~26M rows) and compared the results.

**Overwrite** (correct): one round, one partition, 26,198,821 records. Done.

**Append** (broken):

Round 1 looks fine - the table gets split into 4 partitions that together cover all 26,197,618 rows with proper non-overlapping ranges.

Round 2 is where things go wrong. It should only pick up the delta between round 1's max (`975893455`) and the current max (`975893498`) - roughly 43 rows. Instead, `JdbcConcurrentPartitionsCreator` runs `TABLESAMPLE` on the whole table and gets split boundaries like `845160028`, `848564112`, `...`. All well below the partition's lower bound. These get used as-is:

| Partition | Range | Records |
|-----------|-------|---------|
| P1 | `975893455 < PK <= 845160028` | 0 (inverted, lower > upper) |
| P2 | `845160028 < PK <= 848564112` | 609,917 |
| P3 | `848564112 < PK <= 927424618` | 15,045,460 |
| P4 | `927424618 < PK <= 961774658` | 6,068,779 |
| P5 | `961774658 < PK <= 970767282` | 1,680,230 |
| P6 | `970767282 < PK <= 975893498` | 1,064,833 |

P2 through P6 re-read ~24.5M rows that were already emitted in round 1. Grand total ends up at ~50.6M.

This values come from one of the syncs. I've had another full refresh append syncs that produced ~5x or ~10x times rows more compared to full refresh overwrite.

### Why this happens

There are two `samplingQuery()` implementations at play, and both have the same fundamental problem:

`MsSqlServerJdbcResumablePartition.samplingQuery()` uses `NoWhere`, intentionally dropping the partition's bounds from the sampling query entirely.

That way, `JdbcConcurrentPartitionsCreator` takes the sampled rows, extracts their PK values as split boundaries, and passes them to `split()`. Since those values come from across the whole table, the resulting sub-partitions have upper bounds far below the partition's lower bound. The WHERE clauses end up with overlapping or inverted ranges, and the connector re-reads most of the table.

### Impact

- Append syncs silently produce duplicate data (no errors raised)
- Heavily increased sync time and resource usage on large tables
- Overwrite syncs are unaffected, so this can go unnoticed until someone compares row counts

### Relevant log output

```shell

```

### Contribute

- [x] Yes, I want to contribute

---
**Internal Tracking:** https://github.com/airbytehq/oncall/issues/11627

Guía de contribución

Abrir la guía de contribución

Evaluación

Este issue todavía no se ha evaluado.

Recibe los nuevos issues en tu correo

Un resumen breve de issues de GitHub para principiantes.