# PR #10 Incremental Load Fix Design

Status: Implemented and sandbox-validated

Date: 2026-06-17

Related report: [PR10_INCREMENTAL_LOAD_INTEGRITY_REPORT.md](./PR10_INCREMENTAL_LOAD_INTEGRITY_REPORT.md)

## Summary

PR #10 correctly targets expensive DBAAS billing models for incremental loading, but the current `insert_overwrite` implementation can overwrite a partition with an incomplete row set. The fix is to make every incremental query produce the complete contents of each partition it asks BigQuery/dbt to overwrite.

This design keeps the cost-reduction intent, but changes the incremental contract:

- Day-partitioned billing facts must overwrite complete day partitions.
- Month-partitioned rollups must overwrite complete month partitions.
- Keyed latest-row models should remain `merge` models because sandbox comparisons matched full-refresh exactly.

## Problem

The sandbox comparison showed real data loss after the transition into incremental state.

Observed failures:

- `dws_cloud_total_billable_cost_include_internal` lost 1,358,410 rows from February 1-16, 2026.
- Monthly billing distribution models lost February 2026 data after overwriting the February monthly partition with only February 17-28 input rows.
- `dws_cloud_tenant_monthly_billing_cost_distribution_group` does not build because it partitions by `billing_invoice_month_date` without selecting that column.

Root causes:

1. A model partitioned by `cost_date` used an incremental predicate that also selected rows by `updated_at`. This caused old `cost_date` partitions to be overwritten with only recently updated rows.
2. Models partitioned by `billing_invoice_month_date` used a partial-day cutoff on `billing_cost_date`, so the February month partition was overwritten with a partial February row set.
3. One model added a partition column requirement without including that column in the final projection.

## Goals

- Preserve byte-for-byte equivalence between incremental runs and full-refresh results for affected DBAAS billing tables.
- Keep most cost reduction from avoiding full-history rebuilds.
- Support late updates to source billing rows without deleting unaffected rows in old partitions.
- Make the partition overwrite contract easy to audit in future model changes.
- Provide repeatable sandbox validation before merge.

## Non-Goals

- Re-architect all DBAAS billing models.
- Convert unrelated downstream table models to incremental.
- Change business ranking semantics beyond deterministic tie ordering for equal-cost rows.
- Change business logic for billing amounts, credits, internal-user handling, or tenant enrichment.

## Design Principles

### Partition-Complete Overwrite

For `insert_overwrite`, the selected source rows must be complete for every target partition being overwritten.

Unsafe pattern:

```sql
where date(updated_at) >= cutoff
```

on a model partitioned by:

```sql
partition_by={'field': 'cost_date', 'data_type': 'datetime', 'granularity': 'day'}
```

Safe pattern:

1. Discover affected `cost_date` partitions from rows whose `cost_timestamp` or `updated_at` changed recently.
2. Re-select all source rows for those affected `cost_date` partitions.
3. Let `insert_overwrite` replace only those complete partitions.

### Complete Month Overwrite

For monthly partitions, cutoff logic must align to the month partition.

Unsafe pattern:

```sql
where date(billing_cost_date) >= date_sub(current_date(), interval 120 day)
```

on a model partitioned by:

```sql
partition_by={'field': 'billing_invoice_month_date', 'data_type': 'date'}
```

Safe pattern:

```sql
where billing_invoice_month_date >= date_trunc(date_sub(current_date(), interval 120 day), month)
```

or, in origin models that aggregate from daily/detail rows:

```sql
where date_trunc(date(billing_cost_date), month)
      >= date_trunc(date_sub(current_date(), interval 120 day), month)
```

This reprocesses all of February 2026 when the cutoff is February 17, 2026.

## Proposed Changes

### 1. Add Shared Incremental Window Macros

Create helper macros so every affected model uses the same cutoff semantics.

Proposed macros:

```sql
{% macro cost_optimization_cutoff_date() -%}
  date_sub(current_date(), interval {{ var('cost_optimization_reprocess_days', 120) }} day)
{%- endmacro %}

{% macro cost_optimization_cutoff_month() -%}
  date_trunc({{ cost_optimization_cutoff_date() }}, month)
{%- endmacro %}
```

Optional convenience predicates:

```sql
{% macro date_partition_in_reprocess_window(column_name) -%}
  date({{ column_name }}) >= {{ cost_optimization_cutoff_date() }}
{%- endmacro %}

{% macro month_partition_in_reprocess_window(column_name) -%}
  {{ column_name }} >= {{ cost_optimization_cutoff_month() }}
{%- endmacro %}
```

### 2. Fix `dws_cloud_total_billable_cost_include_internal`

Keep `insert_overwrite`, but switch from row-slice filtering to partition discovery.

Current unsafe behavior:

```sql
and (
  date(cost_timestamp) >= cutoff
  or date(updated_at) >= cutoff
)
```

Proposed shape:

```sql
with raw_source as (
  select *
  from {{ source('stg_metadb', 'stg_metadb_user_unit_costs') }}
),

affected_cost_days as (
  select distinct date(cost_timestamp) as cost_day
  from raw_source
  {% if is_incremental() %}
  where date(cost_timestamp) >= {{ cost_optimization_cutoff_date() }}
     or date(updated_at) >= {{ cost_optimization_cutoff_date() }}
  {% endif %}
),

source_data as (
  select ...
  from raw_source
  where cost_status = 'done'
    and ...
    {% if is_incremental() %}
    and date(cost_timestamp) in (select cost_day from affected_cost_days)
    {% endif %}
)

select * from source_data
```

Important detail: `affected_cost_days` must be derived from the raw source before applying `cost_status = 'done'`. That way, if a row is recently updated out of the `done` state, its partition is still refreshed and the row is correctly removed from the target partition.

Expected behavior:

- A recent update to an old `cost_date` causes the whole old day partition to refresh.
- Rows in that day partition that did not change are still re-selected, so they are not deleted.
- Rows that changed out of eligibility are removed correctly.

### 3. Fix Downstream Day-Partitioned Models

The following models are partitioned by a day field and can safely use direct day-window filters only if their upstream dependency is complete for the same day partitions:

- `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_origin`
- `dws_cloud_tenant_daily_billing_cost_distribution_origin_include_internal`
- `dws_cloud_tenant_daily_billing_cost_distribution`
- `dws_cloud_tenant_daily_billing_cost_distribution_include_internal`

However, because `dws_cloud_total_billable_cost_include_internal` may refresh old days due to `updated_at`, downstream day models should reuse the same affected day set, not only `date(cost_date) >= cutoff`.

Preferred implementation:

- Introduce a small intermediate model or repeated CTE that exposes affected billing days.
- Use those affected days in every downstream day-partitioned `insert_overwrite` model.

Example:

```sql
{% if is_incremental() %}
where date(billing_cost_date) in (
  select distinct date(cost_date)
  from {{ ref('dws_cloud_total_billable_cost_include_internal') }}
  where date(cost_date) >= {{ cost_optimization_cutoff_date() }}
)
{% endif %}
```

This example is not sufficient for late updates to old days unless the affected-day set is persisted or recomputed from the raw source. The safer option is to create a dedicated affected-days staging model.

Recommended new helper model:

- `dwm_dbaas_billing_affected_cost_days`

Materialization:

- view or ephemeral.

Definition:

```sql
select distinct date(cost_timestamp) as cost_day
from {{ source('stg_metadb', 'stg_metadb_user_unit_costs') }}
where date(cost_timestamp) >= {{ cost_optimization_cutoff_date() }}
   or date(updated_at) >= {{ cost_optimization_cutoff_date() }}
```

Then downstream models can use:

```sql
where date(billing_cost_date) in (select cost_day from {{ ref('dwm_dbaas_billing_affected_cost_days') }})
```

### 4. Fix Monthly Origin Models

Change monthly origin models to reprocess whole months.

Affected models:

- `dws_cloud_tenant_monthly_billing_cost_distribution_origin`
- `dws_cloud_tenant_monthly_billing_cost_distribution_origin_include_internal`
- `dws_cloud_tenant_monthly_billing_cost_distribution_group_origin`

Current unsafe predicate:

```sql
where date(billing_cost_date) >= {{ cost_optimization_cutoff_date() }}
```

Proposed predicate:

```sql
where date_trunc(date(billing_cost_date), month) >= {{ cost_optimization_cutoff_month() }}
```

This ensures that if the cutoff is February 17, 2026, the model reprocesses all rows from February 1, 2026 onward.

### 5. Keep Monthly Downstream Filters Aligned to Month Partitions

These models already use the safer month-partition predicate and should keep it:

- `dws_cloud_tenant_monthly_billing_cost_distribution`
- `dws_cloud_tenant_monthly_billing_cost_distribution_include_internal`
- `dws_cloud_tenant_monthly_billing_cost_distribution_group`

Predicate:

```sql
where billing_invoice_month_date >= {{ cost_optimization_cutoff_month() }}
```

### 6. Fix Missing Partition Column Projection

`dws_cloud_tenant_monthly_billing_cost_distribution_group` must include `billing_invoice_month_date` in the final select because it is used as the partition column.

Current issue:

- `billing_invoice_month_date` appears in `GROUP BY`.
- It does not appear in the selected columns from the inner aggregate.
- BigQuery rejects the table because the configured partition column is absent.

Fix:

```sql
select
  billing_invoice_month,
  billing_invoice_month_date,
  ...
```

### 7. Leave Proven `merge` Models As-Is

The following models matched full-refresh exactly in sandbox testing and should remain `merge` models:

- `dwm_dbaas_billing_user_unit_costs`
- `dwm_dbaas_sql_editor_message`
- `dwm_dbaas_sql_editor_oplog`

No design change is required for these models.

### 8. Deterministic Rank Tie-Breakers

Four rank models showed byte-level differences even though their upstream daily/monthly distribution tables matched exactly:

- `dws_cloud_tenant_daily_billing_cost_tenant_busitype_rnk`
- `dws_cloud_tenant_daily_billing_cost_tenant_rnk`
- `dws_cloud_tenant_monthly_billing_cost_tenant_busitype_rnk`
- `dws_cloud_tenant_monthly_billing_cost_tenant_rnk`

Cause: `row_number()` orders only by cost descending, so equal-cost ties can rank differently across runs.

Optional fix:

```sql
row_number() over (
  partition by billing_cost_date
  order by tenant_daily_billing_cost_price desc, billing_tenant_id, tenant_id
) as daily_tenant_rnk
```

This is not a data-loss fix, but it is required for byte-for-byte reproducibility.

## Alternatives Considered

### Alternative A: Use `merge` for all affected incremental billing models

Pros:

- Simple correctness model for keyed fact tables.
- Naturally supports late updates.

Cons:

- Many aggregate outputs do not have stable row-level unique keys.
- BigQuery merge on very large clustered/partitioned targets can still be expensive.
- Requires careful `unique_key` design for grouped tables.

Decision: do not use broadly. Keep it for latest-row/keyed models where uniqueness is clear.

### Alternative B: Remove `updated_at` from the base fact incremental predicate

Pros:

- Prevents old `cost_date` partition deletion.
- Cheaper than dynamic affected-partition discovery.

Cons:

- Late updates to old billing rows would be missed.
- Incorrect for source corrections, status changes, or backfills.

Decision: reject. It avoids deletion by ignoring valid updates.

### Alternative C: Increase reprocess window

Pros:

- Low code churn.

Cons:

- Still unsafe at whatever boundary exists.
- A 180-day or 365-day cutoff will eventually bisect a partition/month and repeat the same bug.

Decision: reject as a correctness fix.

## Validation Plan

Use the same sandbox method from the integrity report.

Datasets:

- `gcp-digital-bi-dev.pr10_full_b406`
- `gcp-digital-bi-dev.pr10_incremental_b406`

Steps:

1. Build full baseline with `--full-refresh` into `pr10_full_b406_fix`.
2. Build incremental sandbox with `--full-refresh` into `pr10_incremental_b406_fix`.
3. Run the same selection without `--full-refresh` into `pr10_incremental_b406_fix`.
4. Rebuild full baseline after the incremental run to reduce live-source drift.
5. Compare full vs incremental with row counts, full-row fingerprints, and numeric aggregates.

Required pass criteria:

- All affected PR models build successfully.
- `hash_groups_only_in_full = 0`.
- `hash_groups_only_in_incremental = 0`.
- `hash_groups_with_count_mismatch = 0`.
- Row counts match for all compared tables.
- Numeric totals match for cost/price/amount columns.
- `dws_cloud_tenant_monthly_billing_cost_distribution_group` builds successfully.

Target tables to compare:

- All PR-changed models.
- Direct downstream DBAAS billing final outputs used in the previous report.
- The three standalone TiDB Cloud `merge` models.

Focused regression checks:

- `dws_cloud_total_billable_cost_include_internal` February 1-16, 2026 must match full-refresh row counts exactly.
- `dws_cloud_tenant_monthly_billing_cost_distribution` February 2026 must match full-refresh row count and cost/raw totals exactly.
- `dws_cloud_tenant_monthly_invoice_billing_for_exploring_change` February and March 2026 must match full-refresh row count and invoice amount totals exactly.

## Rollout Plan

1. Patch macros and affected models.
2. Run `dbt parse --profile omni`.
3. Run targeted compile for affected models.
4. Run sandbox full-vs-incremental comparison in BigQuery.
5. Attach comparison report to the PR.
6. Merge only after exact comparison passes.

## Validation Result

The implementation was validated in BigQuery on 2026-06-17.

Builds:

- `dbt parse` passed.
- Targeted compile for the affected graph passed.
- Incremental sandbox full-refresh build passed: PASS=67 WARN=0 ERROR=0 SKIP=0.
- Incremental sandbox transition run passed: PASS=67 WARN=0 ERROR=0 SKIP=0.
- Fresh full-refresh baseline build passed: PASS=67 WARN=0 ERROR=0 SKIP=0.
- Rank tie-breaker rebuild passed in both sandboxes: PASS=4 WARN=0 ERROR=0 SKIP=0.

Comparison:

- 39 full-vs-incremental downstream/final tables were compared with row counts, row checksums, and grouped row-fingerprint mismatch counts.
- All data-loss regressions were eliminated.
- After deterministic rank tie-breakers, every compared table had `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`.

## Open Questions

- How far back can `updated_at` corrections target old `cost_timestamp` partitions in normal operations? The implemented affected-day design supports arbitrary old days as long as the updated source rows are inside the `updated_at` reprocess window.
- Do downstream table models that remain non-incremental need their own cost controls after the partition-safe upstream fix?

## Implementation Checklist

- [x] Add cutoff helper macros.
- [x] Add affected cost-day helper model or equivalent reusable CTE.
- [x] Update `dws_cloud_total_billable_cost_include_internal` to refresh complete affected `cost_date` partitions.
- [x] Update downstream day-partitioned models to use complete affected day partitions where late updates can propagate.
- [x] Update monthly origin models to use complete-month filters.
- [x] Add `billing_invoice_month_date` to `dws_cloud_tenant_monthly_billing_cost_distribution_group` output.
- [x] Add deterministic rank tie-breakers.
- [x] Re-run sandbox comparison and update the integrity report with fixed results.