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.
Solved! Go to Solution.
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 ;;
}
}
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 ;;
}
}
Thank you! Hidden explore did the trick 🙂