DataBull Search
4 real examples

Best practice Revenue, Retention

Checking the primary key before you trust a count

What is this count actually counting?

The rule

Trust: Inferred

State the grain of every table as one sentence (one row per what) and test that its key is unique before you count or join. A count over a table without a unique key is a row count of an unknown grain: it can mean users, user-days or merged lines depending on the query.

Why it matters

Trust: Inferred

A primary key states the grain of a view: one row per what. Without one, or with one that is not unique, counts are row counts of an unknown grain and joins cannot be de-duplicated.

Symmetric aggregates and distinct counts both depend on a unique key. With an ambiguous key, a count can mean rows, users or user-days depending on the query.

user_tenure_metrics, one user
idtenuremessages_sent_by_tenure
4212
4235
42712

Trust: Suggested Three rows share primary key 42, so any join on id returns all three. Values are invented.

How to check your own model

Trust: Suggested
  1. For each table, write the grain as a sentence: one row per something.
  2. Declare a primary key that matches that sentence (primary_key in LookML, a unique test in dbt). For compound grains, build a compound key.
  3. Test uniqueness: compare a row count with a distinct count of the key.
  4. Name measures for what they count, such as user_day_count, not just count.

A safer shape

Test the key before you trust it

Trust: Suggested

If the row count and the distinct key count differ, the key is not the grain. Compare both with the distinct pairs of id and tenure: when those match the rows, the real grain is one row per user per tenure day.

Show the suggested SQL: key uniqueness check
Suggested, not from source
-- user_tenure_metrics is the derived table defined in the sourceSELECT  (SELECT COUNT(*) FROM user_tenure_metrics) AS row_count,  (SELECT COUNT(DISTINCT id) FROM user_tenure_metrics) AS distinct_ids,  (SELECT COUNT(*)     FROM (SELECT DISTINCT id, tenure FROM user_tenure_metrics) AS k) AS distinct_id_tenure_pairs

The same fix in LookML

Trust: Suggested

In LookML, declare the compound grain: a hidden compound key makes it explicit and keeps id available for joins that pin one tenure day.

Show the suggested LookML: customer_loyalty_metrics.view.lookml
Suggested, not from source
- dimension: user_tenure_key  primary_key: true  hidden: true  sql: ${TABLE}.id || '-' || ${TABLE}.tenure- dimension: id  type: int  sql: ${TABLE}.id

Where it shows up in real projects 4

user_tenure_metrics: A key that repeats three times

Trust: Source-defined

id is the primary key, but the derived table returns one row per user per tenure: 1, 3 and 7 days.

Source: customer_loyalty_metrics.view.lookml L36-39
dating_platform/customer_loyalty_metrics.view.lookmlL36-39
    - id    - tenure  fields:  - dimension: id    primary_key: true    hidden: true    sql: ${TABLE}.id  - dimension: tenure    type: int    sql: ${TABLE}.tenure

dating-platform, customer_loyalty_metrics.view.lookml, lines 36-39

geo_pid_facts: A stored count next to a row count

Trust: Source-defined

With no key, the measure count counts geo and pid rows while the stored count column holds the traffic. Two counts, two grains, one name.

Source: geo_pid_facts.view.lookml L3-16
advertising_marketing_small/geo_pid_facts.view.lookmlL3-16
- view: geo_pid_facts  fields:  - dimension: count    type: int    sql: ${TABLE}.count  - dimension: geo    sql: ${TABLE}.geo  - dimension: ip_count    type: int    sql: ${TABLE}.ip_count  - dimension: pid    type: int    sql: ${TABLE}.pid  - measure: count    type: count    detail: detail*  sets:    detail: null

advertising-marketing-small, geo_pid_facts.view.lookml, lines 3-16

transaction_items: Item lines with no key

Trust: Source-defined

No primary key, and the derived table groups by all six columns, so two identical item lines in one order merge into one.

Source: purchase_items.view.lookml L1-14
dating_platform/purchase_items.view.lookmlL1-14
- view: transaction_items  derived_table:    sql: |      SELECT        ti_orderid,        ti_category,        ti_sku,        ti_name,        ti_price,        ti_quantity      FROM ${TABLE}.events_atomic      WHERE event = 'transaction_item'      GROUP BY 1,2,3,4,5,5,6      ORDER BY ti_orderid    sql_trigger_value: SELECT MAX(collector_tstamp) FROM ${TABLE}.events_atomic    distkey: ti_orderid    sortkeys: ti_orderid

dating-platform, purchase_items.view.lookml, lines 1-14

sessions: A natural key that is not declared

Trust: Inferred

session_id joins cookie and session index, so it is unique per session within one app. It is not marked as the primary key, and the table spans several apps, so a true key also needs app_id.

Source: user_sessions.view.lookml L105-106
dating_platform/user_sessions.view.lookmlL105-106
  - dimension: session_index    type: int    sql: ${TABLE}.domain_sessionidx  - dimension: session_id    sql: ${TABLE}.domain_userid || '-' || ${TABLE}.domain_sessionidx  - dimension: session_index_tier    type: tier    tiers:

dating-platform, user_sessions.view.lookml, lines 105-106

Other places it occurs

Warning campaign clicks and conversions: The count measure counts IPs, not clicks advertising-marketing-small Caution Warning experiments and buckets: No primary key on assignments dating-platform Note Warning other_browser_os_impressions: No primary key advertising-marketing-small Note Warning visitors: No primary key and a missing drill set dating-platform Note