> ## Documentation Index
> Fetch the complete documentation index at: https://docs.internal.nscale.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Kusto: Materialized View Key Changes

> Migrate Matrix materialized views to a new dedup key, choosing between in-place cleanup and a phased drop+recreate.

<Warning>
  **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](../../operator-guide/matrix-connector-operations).
</Warning>

After the ENG-211 migration ([#1377](https://github.com/nscaledev/fleet-manager/pull/1377) / [#1389](https://github.com/nscaledev/fleet-manager/pull/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](https://github.com/nscaledev/fleet-manager/tree/main/src/fleet_manager/agents/netbox_matrix/schema#materialized-view-dedup-keys-prefer-stable-netbox-ids) 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](./kusto-deleting-records) for full details on the delete commands.

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

```kql theme={null}
<TableName>_staging
| where <condition matching old key shape>
| summarize max(SyncTimestamp)
```

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

```kql theme={null}
.delete table <TableName>_staging records <|
  <TableName>_staging
  | where <condition> and SyncTimestamp < datetime(<cutoff>)
```

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

```kql theme={null}
.disable materialized-view <ViewName>

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

.enable materialized-view <ViewName>
```

### 4. Verify

```kql theme={null}
<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**

```kql theme={null}
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**

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

**3. Delete from view**

```kql theme={null}
.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**

```kql theme={null}
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:

```kql theme={null}
<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:

  ```kql theme={null}
  .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**:

1. Confirm phase 1 completed in this env:

   ```kql theme={null}
   <TableName>_staging | where isnotnull(<NewKeyColumn>) | count   // > 0
   <TableName>_staging | where isnull(<NewKeyColumn>) | count       // only pre-deploy rows; previewed before destructive .delete
   ```

2. Run the transition (admin SP creds, not ingest-only):

   ```bash theme={null}
   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.

3. Verify the new dedup key landed:

   ```kql theme={null}
   .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](https://github.com/nscaledev/fleet-manager/pull/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](https://github.com/nscaledev/fleet-manager/pull/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.


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.