# Salesforce Snapshot Loader Strategy

## Purpose

`configs/tidb_load_business_phase1.json` now carries per-object load strategy in
`object_load_strategies`.

Each object can define:

- `mode`: current supported value is `chunked_load_data`
- `chunk_size`: rows per temporary CSV chunk
- `min_fallback_rows`: when a chunk shrinks to this size, switch from
  `LOAD DATA` to parameterized batch `INSERT`
- `notes`: operator hint

## Current behavior

For `chunked_load_data`:

1. build normalized temporary CSV chunks
2. try `LOAD DATA LOCAL INFILE`
3. if a chunk fails, recursively split it
4. when chunk size reaches `min_fallback_rows`, retry that chunk via batch
   parameterized `INSERT`
5. if fallback still fails, save the failed chunk for manual repair

Failed chunk artifacts are stored under:

`<snapshot-dir>/_load_failures/<ObjectName>/`

Each failed chunk keeps:

- `.csv`: the exact chunk content
- `.json`: object, table, row count, failure phase, and error text

## Suggested strategy tiers

- Large tables: `Lead`, `CampaignMember`, `org__c`
- Medium tables: `Account`, `Contact`, `Opportunity`, `invoice_collection__c`
- Fragile content tables: `ContentVersion`, `ContentDocumentLink`

## Hourly Delta Sync (v1)

Goal:

1. keep the existing `full_phase1` baseline snapshot as the bootstrap source
2. pull hourly Salesforce deltas on top of that snapshot
3. load deltas with `REPLACE` so upserts are idempotent
4. run a per-object source-vs-target window count check after load

Current v1 scope:

1. reuse the existing `full_phase1` business object set
2. use a watermark window on `SystemModstamp` with `LastModifiedDate` as the fallback
3. exclude `ContentVersion` and `ContentDocumentLink` from this first pass
4. treat delete propagation as a follow-up gap

Expected operator flow:

1. bootstrap from the existing full snapshot
2. run the hourly delta wrapper with a cached state file
3. compare source export counts against target table counts for the same window
4. refresh the saved watermark only after export, load, and checks all pass

The implementation for this pass lives in:

- `scripts/salesforce_migration/run_hourly_delta_sync.py`
- `scripts/salesforce_migration/configs/hourly_delta_sync.json`

## Hourly Backfill Export

When we need to rebuild the historical hourly source layer after a baseline
full export/import:

1. resolve the baseline end from `manifest_phase1_source_snapshot.json`
2. rewind a safety buffer, currently `2` hours
3. floor that rewind point to the UTC hour boundary
4. export one-hour windows to local disk and S3
5. defer TiDB load/replay until the hourly source layer has been audited

The implementation for this pass lives in:

- `scripts/salesforce_migration/run_hourly_delta_backfill.py`

Recommended operator flow:

1. point the script at the existing baseline snapshot directory
2. run once with `--dry-run` to confirm the planned windows
3. rerun without `--dry-run` to materialize hourly exports in S3
4. only after the source layer is complete, start replay/load by hour