Building a dynamic pivot query view

This guide walks through building a query view in Omni that takes sparse key-value data and fills in the gaps so the result can be cleanly pivoted in a workbook. It covers setting up the SQL using a cross join, wiring up a scoped templated filter, and defining the YAML dimensions and measures that make pivoting work. This is especially relevant if:

Your source table has rows shaped like entity_id | attribute_name | attribute_value, and not every entity has a row for every attribute name
You want to pivot attribute names into columns in an Omni workbook without missing rows collapsing silently
You need a dynamic filter that scopes the query to a subset of data (for example, by a parent ID) and populates its dropdown from the database

Understanding the pattern

If your data looks like this:

entity_id	attribute_name	attribute_value
abc	Evidence A	Pass
abc	Evidence C	Fail
xyz	Evidence B	Pass

A direct pivot would silently drop the missing combinations. The cross-join pattern fills them in first, so the result looks like this before pivoting:

entity_id	Evidence A	Evidence B	Evidence C
abc	Pass	NULL	Fail
xyz	NULL	Pass	NULL

The query view does this by cross-joining all distinct entity IDs against all distinct attribute names, then left-joining back to the source table to pick up values where they exist.

The fill_fields query view parameter solves a related but different problem. It fills missing values from an enumerated or date series. The cross-join approach here is for cases where both the entities and attributes are dynamic and sourced from the data itself.

Requirements

To follow the steps in this guide, you’ll need:

Modeler or Connection Admin permissions on the model
The fully qualified path to your table (DATABASE.SCHEMA.TABLE_NAME)
A table with sparse key-value rows
This guide uses Snowflake to walk through examples. If using a different database, you may need to adjust the SQL to your database’s syntax.

Building the query view

Confirm the table path

Before you start working in Omni, confirm the fully qualified path to your table in Snowflake. If you’re unsure, run the following in Snowflake:

SHOW TABLES LIKE '%YOUR_TABLE_NAME%' IN ACCOUNT;

Then confirm the exact database and schema:

SELECT table_catalog, table_schema, table_name
FROM information_schema.tables
WHERE table_name = 'YOUR_TABLE_NAME';

Your full table path will be: DATABASE.SCHEMA.TABLE_NAME

Create the query view file

Query views can be created two ways in Omni: through the model IDE (recommended), or from a workbook.

In the IDE (recommended)
In the workbook

Open the Omni model IDE.
Create a file named your_query_view.query.view (the .query.view extension is required).
Paste the YAML from the next section into the file.
Click Save.

Build an initial query in a workbook.
Click Model > Save query as view.
Enter the name for your view (for example, your_query_view).
Click Create query view. A new View tab will open.
Replace the auto-generated YAML with the template from the next step, then click Save changes to view.

Add the YAML template

Paste the following template into your query view file. Every value in ALL_CAPS is a placeholder you’ll replace with a real value in the next step — except CREATED_AT, which is an optional column you can rename or remove (see the inline comment in the template).

The placeholder table in the next step covers all substitutions. For example, if your scoping column is ACCOUNT_ID, replace every instance of SCOPE_COLUMN with ACCOUNT_ID and rename scope_id_dim to account_id_dim everywhere it appears — including in suggest_from_field.

your_query_view.query.view

# Replace "your_query_view" everywhere — including inside the templated filter references in the SQL.
# Example: if your file is my_pivot_view.query.view, use my_pivot_view here and in the SQL.
sql: |-
  WITH all_attribute_names AS (
    SELECT DISTINCT "ATTRIBUTE_NAME_COLUMN"
    FROM DATABASE.SCHEMA.YOUR_TABLE
    WHERE {{# your_query_view.scope_filter.filter }} "SCOPE_COLUMN" {{/ your_query_view.scope_filter.filter }}
  ),
  all_entity_ids AS (
    SELECT DISTINCT "ENTITY_ID_COLUMN"
    FROM DATABASE.SCHEMA.YOUR_TABLE
    WHERE {{# your_query_view.scope_filter.filter }} "SCOPE_COLUMN" {{/ your_query_view.scope_filter.filter }}
  ),
  all_combinations AS (
    SELECT
      c."ENTITY_ID_COLUMN",
      a."ATTRIBUTE_NAME_COLUMN"
    FROM all_entity_ids c
    CROSS JOIN all_attribute_names a
  ),
  filled AS (
    SELECT
      ac."ENTITY_ID_COLUMN",
      ac."ATTRIBUTE_NAME_COLUMN",
      t."ATTRIBUTE_VALUE_COLUMN",
      t."CREATED_AT",
      -- Selects the scope column from the source row. NULL for cross-join rows with no matching source data.
      t."SCOPE_COLUMN" AS "SCOPE_COLUMN"
    FROM all_combinations ac
    LEFT JOIN DATABASE.SCHEMA.YOUR_TABLE t
      ON t."ENTITY_ID_COLUMN" = ac."ENTITY_ID_COLUMN"
      AND t."ATTRIBUTE_NAME_COLUMN" = ac."ATTRIBUTE_NAME_COLUMN"
      AND {{# your_query_view.scope_filter.filter }} t."SCOPE_COLUMN" {{/ your_query_view.scope_filter.filter }}
  )
  SELECT * FROM filled
  ORDER BY "ENTITY_ID_COLUMN", "ATTRIBUTE_NAME_COLUMN"

dimensions:
  entity_id:
    sql: '"ENTITY_ID_COLUMN"'
    label: "Entity ID"

  # Right-click this field in the Omni workbook and select "Pivot"
  attribute_name:
    sql: '"ATTRIBUTE_NAME_COLUMN"'
    label: "Attribute Name"

  # The _dim suffix avoids a naming collision with the measure defined below
  attribute_value_dim:
    sql: '"ATTRIBUTE_VALUE_COLUMN"'
    label: "Attribute Value"

  # The _dim suffix avoids a naming collision with the scope_filter defined below.
  # This dimension exists so scope_filter can use suggest_from_field to populate
  # its dropdown dynamically. If you rename this, update suggest_from_field below to match.
  scope_id_dim:
    sql: '"SCOPE_COLUMN"'
    label: "Scope ID"

  # Remove this block (and t."CREATED_AT" from the SQL above) if you don't need a date filter.
  created_at:
    sql: '"CREATED_AT"'
    label: "Created At"

measures:
  count:
    aggregate_type: count

  attribute_value:
    sql: ${attribute_value_dim}
    aggregate_type: max
    label: "Attribute Value (Aggregated)"

filters:
  scope_filter:
    type: string
    default_filter:
      is: your-default-value-here
    suggest_from_field: your_query_view.scope_id_dim

Setting a default filter value is important. Without one, the cross-join CTEs will scan the entire source table on first load.

Replace placeholders in YAML

Replace each placeholder in the YAML using the table below:

Placeholder	Replace with	Example
`your_query_view`	The name of the query view file (without `.query.view`)	`my_pivot_view`
`DATABASE.SCHEMA.YOUR_TABLE`	The fully qualified Snowflake table path	`ANALYTICS.PROD.FACT_EVENTS`
`ENTITY_ID_COLUMN`	The column that identifies each unique entity (the rows in your pivot)	`PRODUCT_ID`
`ATTRIBUTE_NAME_COLUMN`	The column whose distinct values become the pivot column headers	`METRIC_NAME`
`ATTRIBUTE_VALUE_COLUMN`	The column containing the values to show in each pivot cell	`METRIC_VALUE`
`SCOPE_COLUMN`	The column used to filter the data down to a relevant subset	`ACCOUNT_ID`
`CREATED_AT`	Optional. The timestamp column to expose as a date dimension - remove if not needed.	`EVENT_CREATED_AT`
`scope_id_dim`	Rename to match your scope column, keeping the `_dim` suffix	`account_id_dim`
`your_query_view.scope_id_dim` (in `suggest_from_field`)	Update to match your renamed `scope_id_dim` value	`my_pivot_view.account_id_dim`
`your-default-value-here`	The default value to pre-populate the scope filter	`a1b2c3d4-e5f6-7890-abcd-ef1234567890`

Once all placeholders are replaced, your query view is ready to test.

Enable pivoting in the workbook

Omni requires at least one measure on a query to enable pivoting, which is why this view defines attribute_value as a max measure.

This pattern assumes at most one value per entity + attribute (+ scope) combination. If your source has multiple rows for the same combination, the max measure returns only the highest value lexically and drops the others. De-duplicate upstream or change the aggregation if that isn’t what you want.

Once the query view is saved and the model is valid:

Open a workbook and select your query view as the data source.
Add Entity ID as a dimension.
Add Attribute Value (Aggregated) as a measure.
Right-click Attribute Name in the field browser and select Pivot.

Each unique attribute name will become its own column, with NULL shown where no value existed.To change the scope (for example, to view a different parent ID), apply the Scope Filter filter-only field that appears in the field browser under this view’s filters.

Promote to the shared model

Once you’ve confirmed the query view works as expected in the workbook, promote it to make it available across all workbooks.In a published workbook, click Model > View & promote changes, review the changes, and click Promote to shared.

Next steps

Query view parameters — Full reference for query view syntax and options
Templated filters — How filter templating works in Omni SQL
Promoting model changes — How to move changes from a draft to the shared model