Partitioning policy is not copied to DatabaseSchema.kql

[mra1]

I have added a custom partitioning policy to some materialized views and tables like this:

.alter materialized-view DimResponse policy partitioning "{\"PartitionKeys\":[{\"ColumnName\":\"OrgId\",\"Kind\":\"Hash\",\"Properties\":{\"Function\":\"XxHash64\",\"MaxPartitionCount\":128,\"Seed\":1,\"PartitionAssignmentMode\":\"Uniform\"}}],\"EffectiveDateTime\":\"2023-01-01T00:00:00.000000Z\",\"MinRowCountPerOperation\":0,\"MaxRowCountPerOperation\":0,\"MaxOriginalSizePerOperation\":0}"

I have connected to source control, but in DatabaseSchema.kql, the policy is not in the schema, is this a limitation or a bug?

My update policies are applied in the DatabaseSchema.kql, so something is working when altering materialized views and tables.

[tayloramy · Accepted solution]

Hi @mra1,

This is expected today: the Git-exported DatabaseSchema.kql doesn’t include partitioning policies. Keep them in a separate “post-deploy” script and apply them during deployment. You can retrieve the current policy with .show … policy partitioning and re-apply with .alter … policy partitioning. See the policy and command docs here: Partitioning policy, .alter table policy partitioning, .alter materialized-view policy partitioning, and for validation .show database extents partitioning statistics.

Why this happens
Git integration for Eventhouse/KQL DB focuses DatabaseSchema.kql on schema entities (tables, MVs, functions) and some policies (like update). Partitioning is a storage/engine policy and isn’t emitted into that file today, so it won’t show up even if it’s active. 

Recommended pattern (works for tables and MVs)

1. Export what you have now (for each table/MV):

   // Table 
   .show table DimResponse policy partitioning 
   // Materialized view 
   .show materialized-view DimResponse policy partitioning 

2. Check partitioning health (optional but useful):

   .show database extents partitioning statistics 

   Ref: command reference.
3. Store a post-deploy script in your repo, e.g. DatabasePolicies.kql, and apply it after DatabaseSchema.kql

// Table
.alter table DimResponse policy partitioning
{
  "PartitionKeys": [
    {
      "ColumnName": "OrgId",
      "Kind": "Hash",
      "Properties": {
        "Function": "XxHash64",
        "MaxPartitionCount": 128,
        "Seed": 1,
        "PartitionAssignmentMode": "Uniform"
      }
    }
  ],
  "EffectiveDateTime": "2023-01-01T00:00:00Z",
  "MinRowCountPerOperation": 0,
  "MaxRowCountPerOperation": 0,
  "MaxOriginalSizePerOperation": 0
}

// Materialized view
.alter materialized-view DimResponse policy partitioning
{
  "PartitionKeys": [
    {
      "ColumnName": "OrgId",
      "Kind": "Hash",
      "Properties": {
        "Function": "XxHash64",
        "MaxPartitionCount": 128,
        "Seed": 1,
        "PartitionAssignmentMode": "Uniform"
      }
    }
  ],
  "EffectiveDateTime": "2023-01-01T00:00:00Z"
}
​

Command refs: alter table policy partitioning, alter materialized-view policy partitioning.

• Automate
  Have your pipeline (or a manual step) run DatabaseSchema.kql first, then DatabasePolicies.kql. This keeps the policies source-controlled even though they aren’t serialized into the schema file.

Notes and tips
- Only use partitioning where it helps (high-cardinality/equality filters or heavy joins on the key). 
- Verify progress and coverage with: show database extents partitioning statistics.

If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, mark this post as the solution.