AGGREGATION POLICY

Config path: /<database>/<schema>/aggregation_policy/<name>.yaml

Example:

body: |-
  CASE WHEN IS_ROLE_IN_SESSION('SYSADMIN') THEN NO_AGGREGATION_CONSTRAINT()
       ELSE AGGREGATION_CONSTRAINT(MIN_ROW_COUNT => 5, MIN_ENTITY_COUNT => 2)
  END

references:
  - object_type: TABLE
    object_name: test_table_1
    columns: [id]

  - object_type: VIEW
    object_name: test_view_1
    columns: [id]

comment: my aggregation policy

Schema

• body (str) - policy SQL expression

• references (dict)

  • object_type (str) - reference object type (e.g. TABLE, VIEW)

  • object_name (ident) - reference object name

  • columns (list)

    • {items} (ident) - optional reference column names defining "entity"

• comment (str)

Usage notes

1. Management of aggregation policies requires active warehouse due to unavoidable POLICY_REFERENCES table function calls.

2. Make sure to allow aggregation for role SYSADMIN, especially if aggregation policy is being applied to views. Otherwise SnowDDL will have to re-create VIEW on every run due to inability to verify column data types. Example of check: CASE WHEN IS_ROLE_IN_SESSION('SYSADMIN') THEN NO_AGGREGATION_CONSTRAINT() ELSE ... END

3. Parameter references is deprecated since 0.33.0. Use policy reference parameters directly in TABLE or VIEW configs instead.

Links

• CREATE AGGREGATION POLICY

• SHOW AGGREGATION POLICIES

• DESC AGGREGATION POLICY

• Parser & JSON Schema (GitHub)