Why am I getting the error: “Joins fan out the data without a primary key”?

Errors
1

How to fix the error

If you define your table’s primary key, that should resolve the error! A table’s primary key is a column that contains a unique identifier per row — usually, it’s an ID column.

There are 2 ways to define your primary key:

  1. Defining your primary key through the UI
    Omni makes it easy to define a primary key by right-clicking the field in the field picker and hitting Modeling > Primary Key:

  1. Defining your primary key through the model IDE
    Navigate to the table in your model (you can do this in either your shared or workbook, depending on if you’d like this to be defined for your entire org or just this workbook). Add the primary_key: true parameter to the appropriate dimension:

    id:
  primary_key: true

Advanced: If your table has a compound primary key (comprised of a few different fields), you can add the custom_compound_primary_key_sql parameter to your view file. For example:

custom_compound_primary_key_sql: [ id, date ]

Why you’re getting this error

You may be trying to calculate an aggregation (sum, count, average, etc.) on a table that’s being “fanned out” by a join. This happens when you’re joining two tables, and one row in the first table gets joined onto multiple rows in the other table. For example, if table A is an orders table, and table B is an order_items table, then joining the tables on order_id will fan out the orders table since many items in order_items match to the same order in orders.

Omni uses a method called symmetric aggregates to prevent from inadvertently miscalculating aggregations (sums, counts, averages, etc.) when a table is being fanned out. Symmetric aggregates can get pretty complex, but here’s the main idea: before Omni calculates an aggregation on a fanned out table, it’ll look at that table’s primary key — its unique ID per row — to ensure it’s not double-counting values in the aggregation.

If you haven’t defined a primary key on your fanned out table, Omni won’t know how to calculate aggregations correctly, so it’ll throw this error to protect you from an incorrect result.