Kusto: Materialized View Key Changes

Archived — the legacy netbox_matrix agent is retired after the prod cutover. Kept for historical reference only. For the live pipeline, see Matrix Connector Operations.

After the ENG-211 migration (#1377 / #1389), every Matrix MV deduplicates by a stable NetBox ID (NetboxDeviceId / NetboxCableId / NetboxSiteId). Both scenarios below should now be rare: NetBox IDs don’t mutate, so scenario A (key value drift) doesn’t apply, and scenario B (key columns themselves change) is reserved for future schema redesigns rather than the recurring “names drift” problem this agent used to have. New MVs should keep using stable NetBox IDs as the dedup key — see the schema authoring guide for the rule. Two patterns, depending on what changed:

Scenario	Pattern	When to use
A — Key value shape changed within the same column set	In-place delete (no MV recreate)	Existing dedup column(s) keep the same name and type, but their value composition flipped (e.g. orientation normalization, casing change, format change). Old rows in the MV become stale duplicates and need cleaning out.
B — Key columns themselves changed	Phased two-PR migration with `.drop` + `.create with (backfill=true)` via transition	The dedup column(s) are different than before (e.g. switching from a name-based string to a stable integer id). Kusto’s `.create-or-alter materialized-view` rejects group-by-expression changes outright, so the MV has to be dropped and recreated.

The two patterns are not interchangeable. Picking the wrong one either leaves stale duplicates (using A when you needed B) or causes deploy-time apply-schema failures (using B-like schema.kql edits without the transition).

Scenario A — In-place cleanup (no drop/recreate)

When a key change causes duplicate entries in a materialized view but the dedup columns themselves are unchanged, run the two deletes below. No drop/recreate required. See Kusto: Deleting Records for full details on the delete commands.

1. Find the cutoff — last sync before the code change

<TableName>_staging
| where <condition matching old key shape>
| summarize max(SyncTimestamp)

2. Delete from staging (cleans unprocessed delta records)

.delete table <TableName>_staging records <|
  <TableName>_staging
  | where <condition> and SyncTimestamp < datetime(<cutoff>)

3. Delete from view (cleans already-materialized records)

.disable materialized-view <ViewName>

.delete materialized-view <ViewName> records <|
  <ViewName>
  | where <condition> and SyncTimestamp < datetime(<cutoff>)

.enable materialized-view <ViewName>

4. Verify

<ViewName>
| summarize count() by <condition distinguishing old vs new key shape>

Example: IbSwitchLinks normalization (Feb 2026)

a84550c5 normalized IB_SERVER links so the IB switch is always on the start side, changing composite key values and creating duplicates. Same (StartDevice, StartPort, EndDevice, EndPort) columns as before — just stale rows on the wrong side of the new orientation. 1. Find cutoff

IbSwitchLinks_staging
| where ConnectionType == "IB_SERVER"
  and StartDevice startswith "sin1-p-phy-gpu"  // old orientation: server on start side
| summarize max(SyncTimestamp)
// Result: 2026-02-10T16:21:25Z

2. Delete from staging

.delete table IbSwitchLinks_staging records <|
  IbSwitchLinks_staging
  | where ConnectionType == "IB_SERVER"
    and SyncTimestamp < datetime(2026-02-10T16:22:00Z)

3. Delete from view

.disable materialized-view IbSwitchLinks

.delete materialized-view IbSwitchLinks records <|
  IbSwitchLinks
  | where ConnectionType == "IB_SERVER"
    and SyncTimestamp < datetime(2026-02-10T16:22:00Z)

.enable materialized-view IbSwitchLinks

4. Verify

IbSwitchLinks
| where ConnectionType == "IB_SERVER"
| summarize count() by IsOldOrientation=StartDevice startswith "sin1-p-phy-gpu"
// Should show only normalized (switch-first) records

Scenario B — Phased two-PR migration (drop + create with backfill)

When the columns used by summarize ... by ... themselves change — e.g. switching from by NodeName to by NetboxDeviceId — you cannot edit the MV body in schema.kql and ship it in a single PR. Kusto’s .create-or-alter materialized-view rejects group-by-expression changes:

Cannot create materialized view '<MV>': .alter materialized-view does not support
changing group-by expressions. Current: '<old>', New: '<new>'.

The next deploy’s apply-schema would fail and the Helm hook would abort. The migration has to be split across two PRs so the new key column is populated before anything tries to recreate the MV against it.

Phase 1: PR1 — code + column adds in `schema.kql` only

What this PR contains:

Application code changes that compute the new key value (e.g. device.id) and pass it into the Kusto pydantic model.
schema.kql: .create-merge adds the new key column to the staging table. Do not change the MV body in this PR — it must keep using the old group-by until phase 2 has rebuilt the MVs against populated staging.
No transition file.

Apply path: Helm hook runs apply-schema on deploy. Column add is idempotent. MV body is unchanged. Helm succeeds. Agent code rolls out and starts populating the new column on every record. Wait for the new column to be populated for ≥ one full sync cycle in every environment before moving to phase 2. Verify per env:

<TableName>_staging | where isnotnull(<NewKeyColumn>) | count   // expect > 0

Phase 2: PR2 — `schema.kql` MV body change + paired transition

What this PR contains:

schema.kql: change each affected MV body to dedup by the new key column.

A new transition file in schema/transitions/ with one block per affected MV:

.delete table <TableName>_staging records <|
    <TableName>_staging | where isnull(<NewKeyColumn>)

.drop materialized-view <ViewName> ifexists

.create async materialized-view with (backfill=true, AutoUpdateSchema=true)
    <ViewName> on table <TableName>_staging {
        <TableName>_staging
        | summarize arg_max(SyncTimestamp, *) by <NewKeyColumn>
    }

The body of the recreated MV must match exactly what schema.kql declares so subsequent apply-schema calls upsert into a no-op.

The pre-commit schema-lint enforces that any MV body change in schema.kql is paired with a newly added transition file. Operator workflow per env, in promotion order (dev → uat → prod), before merging PR2:

Confirm phase 1 completed in this env:

<TableName>_staging | where isnotnull(<NewKeyColumn>) | count   // > 0
<TableName>_staging | where isnull(<NewKeyColumn>) | count       // only pre-deploy rows; previewed before destructive .delete

Run the transition (admin SP creds, not ingest-only):
fleetctl infra netbox-matrix-agent run-transition YYYYMMDD_short_description.kql \ --cluster-url https://<target>.kusto.windows.net \ --database <target-db> \ --op-item-name azure-kusto-admin
The CLI extracts every OperationId returned by the script’s .create async ... commands, polls .show operations until each reaches State == "Completed" (or any fails), and only prints Transition applied. once they’re all done — so a successful return guarantees the live MV state matches the new schema.kql body. When the prod replica is enabled, run the transition against the primary database first, wait for kusto-copy to drain any new staging inserts, then run the same transition against the replica database. kusto-copy does not replicate management commands, MV recreates, or deletes.

Verify the new dedup key landed:

.show materialized-view <ViewName> cslschema   // first column is the new key
<ViewName> | summarize n=count(), distinct=dcount(<NewKeyColumn>)

Only after the transition has run successfully in every env, merge PR2. Each env’s next deploy will run apply-schema; the .create-or-alter MV upserts will be no-ops because the live MV already has the new group-by. Merging earlier strands an env in a state where the next deploy fails apply-schema; recoverable by running the transition late, but the merge gate avoids it.

Worked example: ENG-211 — switch all 7 MV dedup keys to NetBox stable IDs

Adding asset_tag to a NetBox device flipped DeviceName (which is asset_tag ?? name), creating duplicates in every name-keyed MV that persisted for the 7-day staging retention. Fix: dedup by stable NetBox object IDs (device.id / cable.id / site.id) instead of mutable name strings.

PR1 — column adds + code: #1377. Adds NetboxDeviceId / NetboxCableId / NetboxSiteId to all 7 staging tables; transforms populate the new column from device.id / cable.id / site.id. MV bodies unchanged.
PR2 — MV body change + transition: #1389. schema.kql MV bodies switch to by Netbox*Id. transitions/20260508_netbox_id_dedup_keys.kql does .delete-where-isnull + .drop + .create async ... with (backfill=true) for each of the 7 MVs.

Skim either PR’s description for the full operator runbook and dev-cluster verification queries.

Start Here

Operator Guide

Developer Guide

Relationships

Deployment Guide

Archived

API Reference

Kusto: Materialized View Key Changes

Scenario A — In-place cleanup (no drop/recreate)

1. Find the cutoff — last sync before the code change

2. Delete from staging (cleans unprocessed delta records)

3. Delete from view (cleans already-materialized records)

4. Verify

Example: IbSwitchLinks normalization (Feb 2026)

Scenario B — Phased two-PR migration (drop + create with backfill)

Phase 1: PR1 — code + column adds in `schema.kql` only

Phase 2: PR2 — `schema.kql` MV body change + paired transition

Worked example: ENG-211 — switch all 7 MV dedup keys to NetBox stable IDs

​Scenario A — In-place cleanup (no drop/recreate)

​1. Find the cutoff — last sync before the code change

​2. Delete from staging (cleans unprocessed delta records)

​3. Delete from view (cleans already-materialized records)

​4. Verify

​Example: IbSwitchLinks normalization (Feb 2026)

​Scenario B — Phased two-PR migration (drop + create with backfill)

​Phase 1: PR1 — code + column adds in schema.kql only

​Phase 2: PR2 — schema.kql MV body change + paired transition

​Worked example: ENG-211 — switch all 7 MV dedup keys to NetBox stable IDs

Scenario A — In-place cleanup (no drop/recreate)

1. Find the cutoff — last sync before the code change

2. Delete from staging (cleans unprocessed delta records)

3. Delete from view (cleans already-materialized records)

4. Verify

Example: IbSwitchLinks normalization (Feb 2026)

Scenario B — Phased two-PR migration (drop + create with backfill)

Phase 1: PR1 — code + column adds in `schema.kql` only

Phase 2: PR2 — `schema.kql` MV body change + paired transition

Worked example: ENG-211 — switch all 7 MV dedup keys to NetBox stable IDs