## Status
Historical backfill, Redshift publication, replacement refreshes, and reconciliation completed on 2026-07-29. PRs #1007 and #1008 have been consolidated into this PR. This PR is ready for review.
## Summary
- add custbody_taxtotal, createdFrom, employee, and nextApprover to the governed NetSuite raw contracts
- add staging_finance_netsuite.raw_next_transaction_line_link from direct NextTransactionLineLink SuiteQL evidence
- create the event-driven netsuite-saved-search-refresh pipeline after netsuite-raw
- replace All Vendor Invoices (customsearch113761) with core_finance_netsuite.accounts_payable_accounting_line
- replace All Vendor POs (customsearch113763) with core_finance_netsuite.vendor_purchase_order
- publish through atomic stored procedures, validate target grain immediately, and retain immutable replay evidence in S3
## Why
Klair vendor-management flows currently depend on saved-search-derived staging_netsuite.vendor_invoices and staging_netsuite.vendor_po. This PR reconstructs both contracts from authoritative SuiteQL raw tables so those saved-search dependencies can be retired after consumer cutover.
The replacement runner uses an ordered registry so additional saved-search replacements can be added without creating another pipeline.
## Backfill design
The historical operation used a purpose-built, resumable, column-only backfill instead of re-fetching or rewriting complete NetSuite records.
- NetSuite concurrency: capped at 10 SuiteQL readers. The live account concurrency limit observed for the run was 40. No request-limit or throttling errors occurred.
- Minimal projections:
- Transaction tax: id, custbody_taxtotal
- Purchase Order employees: id, employee, nextApprover
- Transaction-line PO linkage: uniquekey, createdFrom
- Partitioning: numeric cursor ranges were pre-counted and recursively split below 80,000 rows per partition, keeping each SuiteQL result below the pagination ceiling.
- Incremental durability: every source page, normalized load part, partition receipt, and partition plan was written to an immutable run-specific S3 key. Completed partitions could be resumed without repeating NetSuite calls.
- Source growth handling: planned counts were treated as a lower bound; the run accepted newly arrived rows but rejected row loss. TransactionLine.createdFrom grew by one row after planning and the checkpointed count was used for publication/reconciliation.
- Redshift efficiency: derived parts were loaded in grouped COPY operations into explicitly approved work tables. Work tables were distributed on the update key, updates changed only the new columns, target publication was serialized, and affected targets were analyzed afterward.
- Safety: complete source/work counts, duplicate-key checks, missing-target-key counts, and exact post-update value comparisons were required before success was recorded.
### Run identifiers and immutable evidence
- Column backfill run: saved-search-backfill-20260729-v1
- Column backfill S3 root: s3://netsuite-data/surtr/netsuite-saved-search-field-backfill/saved-search-backfill-20260729-v1/
- PO-link raw run: saved-search-backfill-20260729-po-links-v1
- PO-link manifest: s3://netsuite-data/surtr/netsuite-raw/saved-search-backfill-20260729-po-links-v1/raw_next_transaction_line_link/manifest-732bd6ce81d1754ce77cb7797d90dcc4c60cccc2f880b9282639868314602469.json
## Historical backfill results
| Source field / object | Planned rows | Checkpointed rows | Existing raw keys updated/published | Source keys absent from raw target | Post-update mismatches |
|---|---:|---:|---:|---:|---:|
| raw_transaction.custbody_taxtotal | 552,399 | 552,399 | 552,394 | 5 | 0 |
| raw_transaction.employee + next_approver for POs | 28,675 | 28,675 | 28,675 | 0 | 0 |
| raw_transaction_line.created_from | 99,812,722 | 99,812,723 | 99,806,624 | 6,099 | 0 |
| raw_next_transaction_line_link | 312,607 | 312,607 | 312,607 | n/a | 0 duplicate keys |
### Partition detail
| Task | Partitions | Notes |
|---|---:|---|
| Transaction custom tax | 196 | Exact planned/checkpointed count |
| Purchase Order employees | 185 | Exact planned/checkpointed count |
| Transaction-line createdFrom | 2,533 | Source grew by one row after planning |
| Purchase Order transaction links | 187 | 425.2 seconds, 735.2 rows/second, 10 readers, zero throttling/failures |
### Source keys not present in the raw targets
The backfill updates existing raw rows; it does not synthesize incomplete raw records containing only a key and one new column.
- Five tax-bearing source transactions were not yet in the raw transaction snapshot: 48879315, 48879713, 48879812, 48880218, and 48880318. They were newer Vendor Bills and remain the responsibility of normal incremental ingestion.
- 6,099 TransactionLine source keys with non-null createdFrom did not have complete rows in staging_finance_netsuite.raw_transaction_line. This population includes newer source rows and pre-existing historical raw-row gaps.
- Four legacy bills (VENDBILL118786 through VENDBILL118789; transaction IDs 47403361, 47403457, 47403744, and 47404140) account for 12 confirmed historical missing raw lines. Live SuiteQL shows createdFrom = 47373811 (PO 30039) on all 12 lines. Their lineLastModifiedDate is 2026-04-05, one day before the original raw seed boundary, explaining why normal incremental ingestion did not repair them.
- The Vendor PO reconciliation retains historical PO 30081 as an accepted raw-line availability gap.
## New Purchase Order link raw table
staging_finance_netsuite.raw_next_transaction_line_link uses NextTransactionLineLink WHERE previousType = 'PurchOrd'. Purchase Order edges are intentionally separate from the existing accounts-receivable NextTransactionAccountingLineLink subset because NetSuite exposes the needed PO relationship on a different record type.
- Published rows: 312,607
- Distinct composite keys: 312,607
- Composite key: previous_doc, previous_line, next_doc, next_line, link_type
- Null or blank key components: 0
- Distribution key: previous_doc
- Watermark: last_modified_date
- Publication: immutable manifest-backed incremental raw table
### Link-type evidence
| Previous type | Next type | Link type | Edges | Distinct POs |
|---|---|---|---:|---:|
| ItemRcpt | ShipRcpt | receipt/shipment | 41,420 | 16,515 |
| VendAuth | PurchRet | return | 12 | 6 |
| VendBill | OrdBill | order billing | 163,544 | 25,008 |
| VendBill | ShipRcpt | receipt billing | 107,622 | 8,436 |
| VendCred | OrdBill | credit/order billing | 9 | 5 |
## Applying Transaction criterion
The legacy saved-search criterion Applying Transaction: Type is not Bill filters joined applying rows, not Purchase Order headers. Applying an anti-join against Bill links at the replacement model's one-row-per-PO grain would incorrectly remove valid Purchase Orders.
Observed membership against the legacy output:
- Bill-only link POs: 8,872 present in the legacy result; 29 not present
- POs with a non-Bill applying link: 16,515 present in the legacy result
- POs with no link rows: 3,257 present in the legacy result; 4 not present
The replacement therefore selects Purchase Order headers directly and retains the link table as authoritative lineage/evidence rather than using it to exclude headers.
## Redshift cleanup
All three user-approved temporary work tables were dropped after final reconciliation:
- staging_finance_netsuite_metadata.saved_search_backfill_transaction_tax_20260728
- staging_finance_netsuite_metadata.saved_search_backfill_transaction_po_20260728
- staging_finance_netsuite_metadata.saved_search_backfill_transaction_line_created_from_20260728
Final catalog verification: 0 approved work tables remaining.
An accidental redundant local replay was detected while it was scanning immutable partition receipts. It was stopped before Redshift load/update; final warehouse and catalog verification remained unchanged.
## Replacement refresh results
The reviewed production DDL and comments were applied, followed by both atomic replacement procedures:
| Procedure | Runtime | Published rows | Unique grain keys |
|---|---:|---:|---:|
| core_finance_netsuite.sp_refresh_accounts_payable_accounting_line() | 18.1 seconds | 204,132 | 204,132 tal_key |
| core_finance_netsuite.sp_refresh_vendor_purchase_order() | 8.5 seconds | 28,677 | 28,677 netsuite_transaction_id |
Additional final coverage:
- Accounts Payable rows with created_from_po_id: 86,563
- Accounts Payable rows with custom tax: 52,723
- Current raw Purchase Orders: 28,677
- Purchase Orders with requestor: 27,730
- Purchase Orders with non-sentinel next approver: 2,289
- Current raw_transaction rows: 5,825,907
- Current raw_transaction_line rows: 136,190,310
- Current raw transaction-line rows with created_from: 99,806,625
Repeated procedure executions were idempotent.
## Legacy reconciliation — Vendor Purchase Orders
The comparison covered 28,644 shared PO rows.
| Field | Matches / compared | Result / explanation |
|---|---:|---|
| Date | 28,644 / 28,644 | Exact |
| Type | 28,644 / 28,644 | Exact |
| Vendor ID | 28,644 / 28,644 | Exact |
| Currency | 28,644 / 28,644 | Exact |
| Created date (day precision) | 28,644 / 28,644 | Exact |
| Terms | 28,644 / 28,644 | Exact |
| Created by | 28,644 / 28,644 | Exact |
| Requestor | 28,644 / 28,644 | Exact after backfill |
| Item | 28,644 / 28,644 | Exact |
| Quantity | 28,644 / 28,644 | Exact |
| Next approver | 28,634 / 28,644 | 10 legacy rows retained an approver while current raw held -1/null; all were last modified on 2026-07-27 or 2026-07-28, indicating a stale legacy snapshot |
Known non-backfill representation differences:
- Vendor name: 28,642 / 28,644
- Class, business unit, department, and subsidiary: 28,643 / 28,644
- Foreign amount: 19,113 / 28,644 because the replacement preserves valid raw decimals that the legacy parsing path corrupted
- Amount: 28,626 / 28,644
- Memo: 28,429 / 28,644
- Status: 28,596 / 28,644
These differences are documented source/legacy representation behavior, not failed historical column updates.
## Legacy reconciliation — Accounts Payable
The aggregate comparison covered 184,423 matched transactions.
- PO linkage matched on 184,419 / 184,423 transactions.
- The four linkage exceptions are VENDBILL118786 through VENDBILL118789, explained by the 12 historical raw transaction-line gaps documented above.
- Custom tax matched the legacy value exactly on 167,890 / 184,423 transactions.
- After applying the legacy integer-truncation behavior, tax matched on 184,421 / 184,423 transactions.
- The remaining two tax differences are VENDBILL120333 and VENDBILL121485, where the legacy value is null and the authoritative raw value is zero.
The replacement intentionally preserves authoritative raw decimal precision instead of reproducing lossy legacy parsing.
## Operational concurrency and scheduling
- NetSuite source reads completed before the 10:00 IST NetSuite pipelines began.
- The backfill used at most 10 of the live account limit of 40 concurrent requests.
- No request throttling, source-query failure, Redshift publication failure, or partial-success state occurred.
- Redshift writes were serialized after source extraction, so parallel NetSuite reads did not create parallel target mutations.
## Validation
- 143 passed in pipelines/runners/netsuite-raw
- 20 passed in pipelines/runners/netsuite-saved-search-refresh
- Ruff format check passed for every changed Python file
- Ruff check passed for every changed Python file
- git diff --check passed
- Target row counts equal distinct grain-key counts for both replacement Core tables
- PO-link row count equals distinct composite-key count
- All three historical column tasks finished with zero matched-value mismatches
- All temporary work tables were removed after reconciliation