primary_key

Usage

view: view_name {
  dimension: field_name {
    primary_key: yes 
  }
}

Hierarchy View File view Field Type primary_key

Possible Field Types Dimension Accepts A Boolean (yes or no)

Definition

The primary_key parameter specifies that a dimension is the primary key for its view. The default value is false. For example:

dimension: id {
  primary_key: yes
  sql: ${TABLE}.id ;;
}

The primary_key parameter has the following qualities:

• Helps Looker to avoid unnecessary GROUP BY clauses.
• Is necessary to make the foreign_key parameter work properly.
• Is required for the proper functioning of symmetric aggregates. When you join views with measures (aggregates), a primary_key must be defined on each side of the join to properly calculate the aggregations.

The primary_key parameter has the following limitations:

• You cannot add this parameter to more than one field in each view.
• You cannot add this parameter to a dimension_group that specifies more than one timeframe.

Creating a compound primary key

If you have a primary key that is comprised of multiple columns, you can create a dimension that concatenates those columns, then declare that as your primary key. For example:

dimension: primary_key {
  primary_key: yes
  sql: CONCAT(${TABLE}.promotion_id, ${TABLE}.sku) ;;
}

CONCAT works in some databases, such as MySQL, but other databases might have a different equivalent. Redshift, for example, uses || as its concatenation operator.

Using a persistent derived table (PDT) to create a primary key

In cases where there is no reasonable way to identify a unique row as a primary key, you can create a persistent derived table (PDT) to generate a unique ID. For example, the following derived table uses the table's row number to serve as a primary key:

view: transaction {
  # sql_table_name: warehouse.transaction
  derived_table: {
    sql: SELECT ROW_NUMBER as transaction_pk, * FROM warehouse.transaction ;;
    sql_trigger_value: SELECT CURRENT_DATE ;;
    indexes: [customer_id, product_id, salesperson_id, transaction_date]
  }

  dimension: transaction_pk {
    type: number
    primary_key: yes
    hidden: yes
    sql: ${TABLE}.transaction_pk ;;
    ...
  }
}

Defining a new primary key in an extended view

If you use the extends parameter to extend a view, the primary key from the base view will be extended to the new view.

To change the primary key to a new dimension, add primary_key: no to the original dimension definition in the new view, and add primary_key: yes to the new dimension definition in the new view.

view: base {
  dimension: old_primary_key {
    primary_key: yes
    sql: ${TABLE}.id ;;
  }
}

view: new {
  extends: base

  dimension: old_primary_key {
    primary_key: no
  }

  dimension: new_primary_key {
    primary_key: yes
    sql: ${TABLE}.new_id ;;
  }
}