# PR #10 Incremental Load Data Integrity Report

Date: 2026-06-17

PR: https://github.com/pingcap-inc/dbt-dev/pull/10

## Summary

After implementing the partition-complete overwrite fix, the transition to incremental loading **does preserve data integrity** for the compared DBAAS billing outputs in the BigQuery sandbox.

The original PR #10 implementation did not preserve data integrity. It lost rows when `insert_overwrite` replaced day/month partitions with partial row slices. This report keeps the original failing evidence and adds the fixed-run retest results.

I ran real BigQuery sandbox comparisons between:

- Full-refresh baseline: `gcp-digital-bi-dev.pr10_full_b406`
- Incremental-state sandbox: `gcp-digital-bi-dev.pr10_incremental_b406`

Method:

1. Built the affected DBAAS billing graph and the three standalone TiDB Cloud incremental models with `--full-refresh` in `pr10_incremental_b406`.
2. Ran the same graph again without `--full-refresh` in `pr10_incremental_b406`, exercising PR #10's `merge` and `insert_overwrite` paths.
3. Rebuilt the same graph with `--full-refresh` in `pr10_full_b406` after the incremental run to reduce live-source drift.
4. Compared full vs incremental tables in BigQuery using row counts, row fingerprint checksums, and grouped row-fingerprint mismatches.

## Original PR Build Result

65 of 66 selected models built and were compared before the fix.

One PR-changed model did not build:

- `dws_cloud_tenant_monthly_billing_cost_distribution_group`

BigQuery error:

```text
Unrecognized name: billing_invoice_month_date; Did you mean billing_invoice_month?
```

Cause: the PR adds `partition_by={'field': 'billing_invoice_month_date', 'data_type': 'date'}` to this model, but the final select does not include `billing_invoice_month_date`. The column is present in `GROUP BY` but not selected into the output.

## Fixed-Retest Result

Implemented fixes:

- Added shared cutoff macros in `macros/dbt_project/cost_optimization_windows.sql`.
- Added `dwm_dbaas_billing_affected_cost_days` to identify complete affected `cost_date` partitions from the raw billing source.
- Changed day-partitioned `insert_overwrite` models to overwrite complete affected days.
- Changed monthly origin/downstream `insert_overwrite` models to overwrite complete affected months.
- Added `billing_invoice_month_date` to `dws_cloud_tenant_monthly_billing_cost_distribution_group` output so its partition config is valid.
- Added deterministic `row_number()` tie-breakers to the four rank tables that had equal row counts but unstable hashes.

Builds:

| sandbox | command shape | result |
|---|---|---|
| `gcp-digital-bi-dev.pr10_incremental_b406` | targeted graph with `--full-refresh` | PASS=67 WARN=0 ERROR=0 SKIP=0 |
| `gcp-digital-bi-dev.pr10_incremental_b406` | same graph without `--full-refresh` | PASS=67 WARN=0 ERROR=0 SKIP=0 |
| `gcp-digital-bi-dev.pr10_full_b406` | targeted graph with `--full-refresh` after incremental run | PASS=67 WARN=0 ERROR=0 SKIP=0 |
| both sandboxes | rank tie-breaker rebuild | PASS=4 WARN=0 ERROR=0 SKIP=0 per sandbox |

Broad full-vs-incremental comparison:

- 39 downstream/final tables compared using row counts, `BIT_XOR(FARM_FINGERPRINT(TO_JSON_STRING(t)))`, and grouped row-fingerprint mismatch counts.
- 35 of 39 tables matched exactly on the first fixed comparison.
- The remaining 4 rank tables had identical row counts but hash differences from nondeterministic equal-cost ties.
- After adding stable tie-breakers and rebuilding those 4 tables in both sandboxes, all 4 matched exactly.

Final comparison result: **all compared tables have `row_delta = 0`, `hash_groups_only_in_full = 0`, `hash_groups_only_in_incremental = 0`, `hash_groups_with_count_mismatch = 0`, and `rows_in_mismatched_hash_groups = 0`.**

Selected previously failing tables now match exactly:

| table | full rows | incremental rows | result |
|---|---:|---:|---|
| `dws_cloud_total_billable_cost_include_internal` | 59,495,426 | 59,495,426 | exact row/hash match |
| `dws_cloud_total_billable_cost` | 30,762,414 | 30,762,414 | exact row/hash match |
| `dws_cloud_tenant_monthly_billing_cost_distribution` | 6,401,403 | 6,401,403 | exact row/hash match |
| `dws_cloud_tenant_monthly_billing_cost_distribution_group` | 6,401,403 | 6,401,403 | exact row/hash match |
| `dws_cloud_tenant_monthly_billing_cost_distribution_include_internal` | 7,702,434 | 7,702,434 | exact row/hash match |
| `dws_cloud_tenant_monthly_invoice_billing_for_exploring_change` | 5,426,814 | 5,426,814 | exact row/hash match |

Fixed rank tables now match exactly:

| table | rows | result |
|---|---:|---|
| `dws_cloud_tenant_daily_billing_cost_tenant_busitype_rnk` | 251,075 | exact row/hash match |
| `dws_cloud_tenant_daily_billing_cost_tenant_rnk` | 179,201 | exact row/hash match |
| `dws_cloud_tenant_monthly_billing_cost_tenant_busitype_rnk` | 11,117 | exact row/hash match |
| `dws_cloud_tenant_monthly_billing_cost_tenant_rnk` | 8,120 | exact row/hash match |

## Original Integrity Findings

## Root Cause

There are two concrete `insert_overwrite` misuse patterns causing the loss.

### 1. Day partitions are overwritten from an incomplete `cost_date` slice

`dws_cloud_total_billable_cost_include_internal` is partitioned by `cost_date`, but the incremental predicate selects rows by either `cost_timestamp` or `updated_at`:

```sql
date(cost_timestamp) >= date_sub(current_date(), interval 120 day)
or date(updated_at) >= date_sub(current_date(), interval 120 day)
```

On 2026-06-17, the 120-day cutoff is 2026-02-17. The incremental query selected some rows in the February 1-16 partitions because they had `updated_at >= 2026-02-17`. BigQuery/dbt then overwrote those whole `cost_date` partitions with only the selected rows. Rows in those same partitions whose `updated_at < 2026-02-17` were deleted.

Direct proof from sandbox data:

| cost day | full rows | incremental rows | deleted rows | rows selected by PR predicate | rows not selected by PR predicate |
|---|---:|---:|---:|---:|---:|
| 2026-02-01 | 82,838 | 8,809 | 74,029 | 8,809 | 74,029 |
| 2026-02-02 | 94,889 | 9,337 | 85,552 | 9,337 | 85,552 |
| 2026-02-03 | 99,015 | 9,780 | 89,235 | 9,780 | 89,235 |
| 2026-02-04 | 99,988 | 11,566 | 88,422 | 11,566 | 88,422 |
| 2026-02-05 | 103,294 | 17,036 | 86,258 | 17,036 | 86,258 |
| 2026-02-06 | 112,492 | 13,992 | 98,500 | 13,992 | 98,500 |
| 2026-02-07 | 97,562 | 22,579 | 74,983 | 22,579 | 74,983 |
| 2026-02-08 | 96,020 | 22,393 | 73,627 | 22,393 | 73,627 |
| 2026-02-09 | 101,538 | 14,764 | 86,774 | 14,764 | 86,774 |
| 2026-02-10 | 112,579 | 24,090 | 88,489 | 24,090 | 88,489 |
| 2026-02-11 | 110,151 | 18,294 | 91,857 | 18,294 | 91,857 |
| 2026-02-12 | 118,754 | 24,153 | 94,601 | 24,153 | 94,601 |
| 2026-02-13 | 115,299 | 23,720 | 91,579 | 23,720 | 91,579 |
| 2026-02-14 | 101,914 | 22,206 | 79,708 | 22,206 | 79,708 |
| 2026-02-15 | 101,677 | 22,144 | 79,533 | 22,144 | 79,533 |
| 2026-02-16 | 106,886 | 31,623 | 75,263 | 31,623 | 75,263 |

For February 17 onward, the full and incremental row counts match because the `cost_date` itself is inside the cutoff window.

### 2. Month partitions are overwritten from a partial-month day filter

Several monthly models are partitioned by `billing_invoice_month_date`, but the first monthly origin models filter their input by `date(billing_cost_date) >= cutoff`.

Example:

```sql
partition_by={'field': 'billing_invoice_month_date', 'data_type': 'date'}
...
WHERE date(billing_cost_date) >= date_sub(current_date(), interval 120 day)
```

With the same 2026-02-17 cutoff, the incremental query includes only February 17-28 rows, but overwrites the entire `billing_invoice_month_date = 2026-02-01` partition. This deletes February 1-16 from monthly tables.

Direct proof from the full-refresh detail source:

| invoice month | filter bucket | cost-day range | detail rows | cost price | raw price |
|---|---|---|---:|---:|---:|
| 2026-02 | not selected by incremental filter | 2026-02-01 to 2026-02-16 | 1,322,923 | 2,868,075.898402 | 4,271,590.584996 |
| 2026-02 | selected by incremental filter | 2026-02-17 to 2026-02-28 | 1,110,031 | 2,129,638.120832 | 3,152,643.970267 |
| 2026-03 | selected by incremental filter | 2026-03-01 to 2026-03-31 | 3,697,658 | 5,986,236.093878 | 8,678,297.740138 |

The resulting February monthly origin table matches that partial input pattern:

| table | side | February rows | cost price | raw price |
|---|---|---:|---:|---:|
| `dws_cloud_tenant_monthly_billing_cost_distribution_origin` | full | 736,303 | 4,996,072.60 | 7,422,370.12 |
| `dws_cloud_tenant_monthly_billing_cost_distribution_origin` | incremental | 417,907 | 2,128,730.20 | 3,151,587.64 |

### Original Failure: `dws_cloud_total_billable_cost_include_internal`

This table is missing rows after the incremental run.

| metric | full-refresh | incremental | delta |
|---|---:|---:|---:|
| rows | 59,495,426 | 58,137,016 | -1,358,410 |

All missing rows are from `cost_date` February 1-16, 2026.

| cost month | missing rows | min cost day | max cost day | min updated | max updated | internal rows |
|---|---:|---|---|---|---|---:|
| 2026-02-01 | 1,358,410 | 2026-02-01 | 2026-02-16 | 2026-02-01 | 2026-02-16 | 315,258 |

Likely cause: the model uses `insert_overwrite` partitioned by `cost_date`, but its incremental filter can select rows by either `cost_timestamp` or `updated_at`:

```sql
date(cost_timestamp) >= date_sub(current_date(), interval 120 day)
or date(updated_at) >= date_sub(current_date(), interval 120 day)
```

If any recently updated rows exist in an old `cost_date` partition, dbt overwrites that whole `cost_date` partition with only the filtered subset, deleting older rows in the same partition that were not selected.

### Original Failure: Monthly Billing Distribution Chain

The monthly distribution tables lose February 2026 data after incremental overwrite.

For `dws_cloud_tenant_monthly_billing_cost_distribution`:

| month | full rows | incremental rows | row delta | cost price delta | raw price delta |
|---|---:|---:|---:|---:|---:|
| 2026-02-01 | 736,303 | 417,907 | -318,396 | -2,867,342.40 | -4,270,782.48 |

Affected compared tables include:

- `dws_cloud_tenant_monthly_billing_cost_distribution`
- `dws_cloud_tenant_monthly_billing_cost_distribution_origin`
- `dws_cloud_tenant_monthly_billing_cost_distribution_group_origin`
- `dws_cloud_tenant_monthly_billing_cost_distribution_include_internal`
- `dws_cloud_tenant_monthly_billing_cost_distribution_origin_include_internal`
- downstream monthly billing summary/rank/invoice tables

Likely cause: several monthly models are partitioned by `billing_invoice_month_date`, but their incremental filters are row-level filters on `billing_cost_date >= current_date - 120 days`. Since the current date is 2026-06-17, the 120-day cutoff lands mid-February. The incremental run overwrites the February monthly partition using only February 17+ rows, dropping February 1-16 rows from the monthly partition.

### Downstream Impact

For `dws_cloud_tenant_monthly_invoice_billing_for_exploring_change`:

| month | row delta | cost price delta | invoice amount delta |
|---|---:|---:|---:|
| 2026-02 | -101,416 | -2,781,818.18 | -9,648,086,669.55 |
| 2026-03 | -123,221 | 0.00 | -5,122,584,696.99 |

The March cost total matches, but prior-month-derived invoice fields differ because February is incomplete.

## Original Passing Comparisons

The three standalone `merge` models matched the full-refresh baseline exactly:

| table | rows | result |
|---|---:|---|
| `dwm_dbaas_billing_user_unit_costs` | 31,372,812 | exact row/hash match |
| `dwm_dbaas_sql_editor_message` | 3,433,569 | exact row/hash match |
| `dwm_dbaas_sql_editor_oplog` | 3,614,561 | exact row/hash match |

Important daily/detail billing tables also matched exactly, including:

- `dws_cloud_total_billable_cost`
- `dws_cloud_tenant_total_billable_cost_distribution_detail`
- `dws_cloud_tenant_total_billable_cost_distribution_detail_include_internal`
- `dws_cloud_tenant_daily_billing_cost_distribution`
- `dws_cloud_tenant_daily_billing_cost_distribution_include_internal`
- `dws_cloud_tenant_daily_billing_cost_distribution_origin`
- `dws_cloud_tenant_daily_billing_cost_distribution_origin_include_internal`
- daily invoice/raw-price/consumption-source downstream tables

Two daily rank tables had equal row counts but row-hash differences before the tie-breaker fix:

- `dws_cloud_tenant_daily_billing_cost_tenant_busitype_rnk`
- `dws_cloud_tenant_daily_billing_cost_tenant_rnk`

Their upstream daily distribution table matched exactly. The rank models use `row_number()` ordered only by cost descending, so ties can produce nondeterministic rank/accumulation ordering between rebuilds. I would not treat those as an incremental-state data-loss finding, but they are not byte-for-byte reproducible.

## Recommendation

The original PR #10 should not merge as-is. With the implemented changes in this worktree, the sandbox evidence supports merging the incremental-loading approach after normal code review.

The core invariant to preserve in future edits: every `insert_overwrite` model must select complete contents for every partition it overwrites.