BMW IVSR Order Bank Source

The ivsr source fetches BMW's order bank — orders a market has placed with BMW that have not yet been allocated, typically customer orders raised by dealers — from the IVSR OFE Order Locator. It is currently configured for the us market of projects/ai_bmw.

The Temporal workflow bmwUsOrderBankWorkflow in ppm-ordering-services (runners/data-collection) also writes ai-app-bmw.order_bank.bmw_us; the two paths write independently of each other.

Auth chain

Three hops, and the Dagster asset only owns the first:

ai_bmw raw asset
  │  POST {api_base_url}/ivsr/order/locator                 header: x-api-key
  ▼
ppm-ivsr API Gateway  (999655274916, us-east-1)
  ▼
bmw-ordering-service Lambda  (bmw-allocation-hub/lambda/)
  │  OAuth2 client_credentials → auth-i.bmwgroup.net
  │  POST /ivsr/rest/ofe/order/locator/ol                   header: Bearer <token>
  ▼
BMW IVSR OFE  (ivsrbei.bmwgroup.net)

The Lambda owns the BMW M2M credentials, the ivsrRequestHeader identity fields, and the nested controlData / order / vehicleSpecification / wholesale request envelope. This source sends the Lambda's flat parameter structure.

Credentials

The gateway stage URL is component config, not an env var:

api_base_url: https://shyxafkte6.execute-api.us-east-1.amazonaws.com/prod

It is not a secret, and base_url is read while Dagster builds defs — resolving it from the environment would make the entire ai_bmw code location fail to load anywhere the variable is unset (including a plain dg list defs).

Only the key is a secret, read at fetch time:

Env var	Value
BMW_US_IVSR_API_KEY_SECRET_ID	arn:aws:secretsmanager:us-east-1:999655274916:secret:ppm/prod/bmw-us/ivsr-api-key-hAKSd4

Set in projects/ai_bmw/container_context.yaml, with the ECS task role granted the secret in the bmw-secrets-read policy of deployments/aws/cloudformation/ecs-agent-vpc-private.yaml.

The secret is owned by ppm-ordering-services, not copied under dagster/bmw/. It lives in the same AWS account, and a second copy would silently drift the next time the key rotates. There is no corresponding secrets.tf resource here for the same reason.

A 401/403 is treated as terminal, not transient: a rejected key cannot be fixed by retrying, and retrying would exhaust the retry budget on every page.

Request

POST /ivsr/order/locator
{
  "statusRanges": [{ "from": "047", "to": "047" }],
  "startIndex": 0,
  "totalRequestedOrders": 500,
  "brand": "BM",
  "productType": "1",
  "sortCriteria": [{ "column": "mfOrderNumber", "ascending": true }]
}

• Status 047 is the order-bank window: placed, awaiting allocation. Note the wider [047, 112] range used by bmw-allocation-hub's risk-pool locator covers more than the order bank — don't reuse its description for 047 alone.
• No dealerIds — the sweep returns everything visible to the gateway's wholesaler.
• Pagination is startIndex += 500, stopping when a page returns fewer than 500 entries in searchResults. A hard ceiling of 100 pages (50k orders) raises rather than truncating; controlData.searchCount is not used as the bound.

sortCriteria fails silently — get the structure right

BMW's OLSortCriteriaVO wants {column, ascending}. A differently-shaped object, such as {field, direction}, is dropped without an error, which leaves startIndex paging unordered. Pages can then skip or duplicate orders if BMW reorders mid-sweep. The risk-pool locator in bmw-allocation-hub uses the same {column, ascending} structure.

Because it fails silently, the only proof it took effect is the data: within a partition, mfOrderNumber should ascend inside each page, pages should not overlap, and no order number should appear twice.

Response structure

BMW returns opaque records — field names have moved between OFE releases, so nothing is validated and the whole envelope is stored verbatim:

{
  "ivsrResponseHeader": { "returnCode": "...", "returnMessage": "..." },
  "messages": [ /* ... */ ],
  "controlData": { "searchSize": 500, "searchCount": 12345 },
  "searchResults": [ { /* order */ } ]
}

Fields confirmed present on an order: mfOrderNumber, wsModelCode, dealerId, orderStatus, rtRequestedProdWeek. Confirmed absent: orderEntryTimestamp and vin7/vin10. The OL response has no orderEntryTimestamp field, so the legacy BQ column of that name is always null. vin7/vin10 are unpopulated on every order (3,926 live and all 307,747 archived), consistent with the bank holding unallocated orders.

Only the order objects are common to both paths. ivsrResponseHeader and messages are envelope-level and the collector never stored them, so a backfilled row lacks both and carries a synthesized controlData.searchCount.

PII redaction

Each order carries a customer block of retail names and a salesPerson employee id. Both keys are removed, with everything nested under them, before the body reaches Iceberg. The backfill reshape strips archive rows the same way, so both paths write the same field set.

Stripping happens in the page generator (ivsr.strip_and_count) because pagination needs each page's order count, and that decision is made upstream of the raw tier's transform_content hook. One parse serves both, so the logged count describes the bytes that get stored.

Assets

Tier	Asset key	Notes
Source	bmw/us/sources/ivsr_order_bank	Carries the API metadata and the cron.
Raw	bmw/us/raw/ivsr_order_bank	source=ivsr, resource=order_bank, auth=api_key. One row per Order Locator page; _response_body is the OL envelope.

When the order count is an exact multiple of 500 the sweep needs one extra request to learn it has ended, and that page comes back empty. Its body is dropped so no zero-order row is written to the table, while the request still counts toward fetch_attempts. An empty first page is kept — an empty order bank is a real observation, not a pagination artifact.

Runs daily at 07:00 UTC — one hour after the legacy collector's 06:00 UTC run, so the two do not contend on the shared gateway key.

skip_past_partitions stays at its default True: the Order Locator reports current state only, so past partitions come from the backfill below.

There is no transformed tier. EntityType has no ORDERS member, and order-bank rows are unallocated orders with no VIN — mapping them onto INVENTORY would corrupt inventory counts and days-on-lot. Adding one means extending packages/ai_core, which every OEM depends on.

BQ backfill

Job	backfill_ivsr_order_bank_us
Source table	ai-app-bmw.order_bank.bmw_us
Target asset	bmw/us/raw/ivsr_order_bank
Reshape	ai_bmw.backfill._reshape_ivsr_order_bank
Horizon	2026-05-15 (ivsr_start_date)

The legacy table is DAY-partitioned on snapshot_date, a TIMESTAMP stamped at UTC start-of-day (hence bq_date_column_is_timestamp). Its columns are snapshot_date, fetched_at, production_number, vg_model_code, dealer_id, ivsr_order_status, order_entry_timestamp, and data — the redacted order row as a JSON string, with the five typed columns lifted out of it best-effort.

Three structure decisions follow from that:

• bq_aggregate_partition — the collector wrote one row per order; the live asset writes whole OL pages. Aggregating the partition into a single envelope keeps the stored structure an OL response in both paths. Only the raw row count differs: one for a backfilled partition, ceil(N/500) for a live one.
• bq_dedup_on: [production_number] — the collector paginated without sortCriteria, so page order was unstable and a single sweep re-served some orders across page boundaries. This is not an edge case: 74 of 80 archived days contain duplicates — 55,380 rows across 27,380 groups, 18% of the table, up to 3 copies of one order. The live asset sends the sort and produced 3,926 orders with 0 duplicates, so this is an archive-only concern. Verified safe: production_number is non-null on all 307,747 rows, so no null group can be collapsed. The ORDER BY tie-break is arbitrary (snapshot_date is constant within a partition), but measurably harmless — of the 27,380 groups, data differs in 1 and ivsr_order_status in 0.
• No bq_select_all — bq_aggregate_partition already emits TO_JSON_STRING(ARRAY_AGG(t)).

The reshape unpacks every data blob and wraps them as {"controlData": {"searchCount": N}, "searchResults": [...]}. searchCount is synthesized; the archive never stored BMW's own controlData. _fetched_at is the newest per-row fetched_at in the partition, since the collector stamped it per row rather than once per snapshot.

Historical partitions are unsorted

Everything in the archive was collected before sortCriteria was sent, so page boundaries were unstable. Duplicates are collapsed by bq_dedup_on, but orders that fell between page boundaries were never captured and no backfill can recover them. Do not read a backfilled partition as complete by construction.

Gaps in the archive

Five days inside the horizon have no rows at all — 2026-05-09, 05-10, 05-11, 05-12, and 05-14, all clustered right after the collector launched on 05-08. Those partitions read empty and are skipped. Use the job's carry_forward_days config if a stand-in snapshot is preferable to a hole; leave it at 0 to keep the gap visible.

Order volume runs 2,190–5,326 per day across the 80 collected days, so a live sweep is roughly 5–11 pages — nowhere near the 100-page ceiling.

First run on a fresh asset

backfill_asset_partitions probes the target with load_asset_value before writing when overwrite is false, which fails with NoSuchTableError if the Iceberg table does not exist yet. On a brand-new asset the live asset has to materialize once first (creating the table), or the first backfill run needs overwrite: true. The sibling backfill jobs never hit this because their live assets predate them.

Adding another market

IVSR is BMW's global OFE system, so other markets have an order bank. The Dagster side is ready: the gateway is per-market api_base_url config and the key resolves from market as BMW_<MARKET>_IVSR_API_KEY. A new market needs a defs.yaml entry (with its own api_base_url), a BMW_<MARKET>_IVSR_API_KEY_SECRET_ID in container_context.yaml, and that secret granted in the CloudFormation policy — no code change.

The blocker is upstream. The Order Locator's wholesaler is chosen by the Lambda deployment, not by the request: IVSROrderLocatorParams has no wholesaler field, and IVSROrderService.orderLocator reads this.config.wholesaler from IVSR_OFE_WHOLESALER (default 000004 = BMW USA). So a second market needs either its own gateway/Lambda deployment carrying that market's wholesaler, or a Lambda change accepting wholesalers per request.

Also note the Temporal collector only ever ran for BMW USA, and its table name is literally bmw_us. Another market has no legacy archive, so it would be live-only with start_date set to its first materialization.