Uniqueness test generates wrong SQL with count(distinct)

Looker Q&A Looker
1

I have a model, where videos is main table, with playbacks joined to it. Each playback has playback_key (set as primary key in model) and I want to test for its uniqueness.

I based my test on uniqueness example from docs:

test: playback_key_is_unique {
  explore_source: analytics {
    column: key {
     field: playbacks.playback_key
    }
    column: count {
      field: playbacks.count
    }
    sorts: [playbacks.count: desc]
    limit: 1
   }
   assert: playback_key_is_unique {
     expression: ${playbacks.count} = 1 ;; # generated query does count(distinct), why?
   }
}

This generates following SQL:

SELECT
  playbacks.playback_key AS playbacks_playback_key,
  COUNT(DISTINCT playbacks.playback_key ) AS playbacks_count
FROM `analytics.videos` AS videos_new
FULL OUTER JOIN `analytics.playbacks` AS playbacks ON playbacks.video_id = videos_new.id
GROUP BY
  1
ORDER BY
  2 DESC
LIMIT 1

The problem is, this will always pass due to count(distinct) and test is invalid.

I can’t figure out how to fix this, so it generates the usual count() instead of distinct.

2

Hey @pkalinowski !
That’s happening as you are trying to do the check through an explore where this view is a join and not the base view of the explore. Even though you are using a measure type count, it’s going to convert it to count_distinct. And as you mentioned, you won’t be able to actually check the primary key properly.
Because of this, you cannot check if a field coming from a joined view in specific explore is a primary key. And it’s always recommended to check the primary key on a basic explore with the name of the view (afterwards you can delete it).
As possible solution if you want to have the tests is to create a dummy hidden explores which you will use for the tests only like this:

explore: playbacks {hidden: yes}

And build the test over this explore:

test: playback_key_is_unique {
  explore_source: playbacks {
    column: key {
     field: playbacks.playback_key
    }
    column: count {
      field: playbacks.count
    }
    sorts: [playbacks.count: desc]
    limit: 1
   }
   assert: playback_key_is_unique {
     expression: ${playbacks.count} = 1 ;;
   }
}
3

Thank you! Hidden explore did the trick :slightly_smiling_face: