Redshift APPROXIMATE COUNT(DISTINCT) in Snowflake, Databricks and BigQuery
Redshift writes the approximate distinct count as a keyword in front of COUNT. The targets use a function instead: APPROX_COUNT_DISTINCT in all three.
Redshift syntax: APPROXIMATE COUNT(DISTINCT expression) · Redshift docs
Equivalents
| Target | Support | Syntax | Notes | Source |
|---|---|---|---|---|
| Snowflake | Rewrite | APPROX_COUNT_DISTINCT(expr) | HyperLogLog estimate. | Snowflake docs |
| Databricks | Rewrite | approx_count_distinct(expr [, relativeSD]) | relativeSD sets the allowed relative error. | Databricks docs |
| BigQuery | Rewrite | APPROX_COUNT_DISTINCT(expression) | HyperLogLog++ estimate. | BigQuery docs |
Behavior differences to check
- The estimates differ slightly between engines, so do not compare approximate counts across systems for exact equality during validation; compare with an exact COUNT(DISTINCT) on a sample instead.
Examples
Approximate daily active users
SELECT event_date, APPROXIMATE COUNT(DISTINCT user_id) AS dau FROM events GROUP BY event_date;SELECT event_date, APPROX_COUNT_DISTINCT(user_id) AS dau FROM events GROUP BY event_date;SELECT event_date, approx_count_distinct(user_id) AS dau FROM events GROUP BY event_date;SELECT event_date, APPROX_COUNT_DISTINCT(user_id) AS dau FROM events GROUP BY event_date;