APPROX_COUNT_DISTINCT

Category: Aggregate Functions

Platform Support:

✅ Snowflake (HLL or APPROX_COUNT_DISTINCT) | ✅ BigQuery (APPROX_COUNT_DISTINCT) | ✅ Databricks (APPROX_COUNT_DISTINCT)

Description

Returns an approximate count of distinct values using the HyperLogLog algorithm. This function is significantly faster than exact COUNT(DISTINCT) for large datasets, with typical accuracy of ~98% (2% error rate). Essential for analyzing large-scale data where exact precision isn't critical.

Syntax by Platform

Snowflake:

APPROX_COUNT_DISTINCT(column)
-- OR
HLL(column)  -- Snowflake-specific, same functionality

BigQuery:

APPROX_COUNT_DISTINCT(column)

Databricks:

APPROX_COUNT_DISTINCT(column)

Platform-Specific Notes

Snowflake:

• Original function name is HLL (HyperLogLog)
• APPROX_COUNT_DISTINCT added for SQL standard compatibility
• Both functions work identically
• Extremely efficient on large datasets

BigQuery:

• Standard function across Google Cloud
• Can be combined with HLL_COUNT functions for more control
• Supports APPROX_TOP_COUNT for approximate top-k values

Databricks:

• Standard Spark SQL function
• Can specify precision with second parameter (default: 0.05 = 5% error)
• Compatible with Delta Lake optimizations

Example 1: Count Unique Visitors

All Platforms:

SELECT 
    DATE_TRUNC('DAY', event_timestamp) as date,
    APPROX_COUNT_DISTINCT(user_id) as unique_visitors,
    COUNT(*) as total_page_views,
    COUNT(*) / APPROX_COUNT_DISTINCT(user_id) as avg_views_per_user
FROM page_views
GROUP BY DATE_TRUNC('DAY', event_timestamp)
ORDER BY date;

Sample Data (page_views table):

Result:

Example 2: Compare Exact vs Approximate

All Platforms:

SELECT 
    category,
    COUNT(DISTINCT customer_id) as exact_count,
    APPROX_COUNT_DISTINCT(customer_id) as approx_count,
    ABS(COUNT(DISTINCT customer_id) - APPROX_COUNT_DISTINCT(customer_id)) as difference,
    (ABS(COUNT(DISTINCT customer_id) - APPROX_COUNT_DISTINCT(customer_id)) * 100.0 
     / COUNT(DISTINCT customer_id)) as error_rate_pct
FROM purchases
GROUP BY category;

Sample Data (purchases table with 1M+ rows):

Note: Approximate counts are typically within 2% of exact values

Example 3: Multi-Dimensional Analysis

All Platforms:

SELECT 
    country,
    device_type,
    APPROX_COUNT_DISTINCT(user_id) as unique_users,
    APPROX_COUNT_DISTINCT(session_id) as unique_sessions,
    COUNT(*) as total_events,
    COUNT(*) / APPROX_COUNT_DISTINCT(session_id) as events_per_session
FROM user_events
WHERE event_date >= CURRENT_DATE - INTERVAL '7 days'
GROUP BY country, device_type
ORDER BY unique_users DESC
LIMIT 10;

Sample Data (user_events table):

Result:

Example 4: Platform-Specific Features

Snowflake (Using HLL):

-- Snowflake supports both function names
SELECT 
    product_category,
    HLL(customer_id) as hll_count,  -- Original Snowflake function
    APPROX_COUNT_DISTINCT(customer_id) as approx_count  -- Standard SQL
FROM sales
GROUP BY product_category;

Databricks (With Custom Precision):

-- Databricks allows specifying error rate
SELECT 
    region,
    APPROX_COUNT_DISTINCT(user_id) as default_precision,  -- ~5% error
    APPROX_COUNT_DISTINCT(user_id, 0.01) as high_precision  -- ~1% error (slower)
FROM user_activity
GROUP BY region;

BigQuery (With HLL Sketches):

-- BigQuery HLL functions for advanced use cases
SELECT 
    date,
    APPROX_COUNT_DISTINCT(user_id) as unique_users,
    HLL_COUNT.MERGE(HLL_COUNT.INIT(user_id)) as hll_merge_count
FROM daily_users
GROUP BY date;

Performance Comparison

Performance gains increase with data size

Accuracy Characteristics

• Typical Error Rate: ±2% (98% accurate)
• Algorithm: HyperLogLog (HLL)
• Memory Usage: Fixed, very small (~12KB per distinct value calculation)
• Consistency: Results are deterministic for same data

When to Use APPROX_COUNT_DISTINCT

Use when:

• ✅ Analyzing large datasets (millions+ rows)
• ✅ Exact precision not required (analytics, dashboards)
• ✅ Query performance is critical
• ✅ Counting distinct users, sessions, devices, IPs
• ✅ Real-time analytics requiring fast results

Use COUNT(DISTINCT) when:

• ❌ Small datasets (< 100k rows) where performance difference is minimal
• ❌ Financial calculations requiring exact accuracy
• ❌ Compliance/audit scenarios
• ❌ Small cardinality (< 1000 distinct values)

Common Use Cases

1. Web Analytics: Unique visitors, sessions per day
2. E-commerce: Unique customers, product views
3. Marketing: Campaign reach, unique email opens
4. IoT: Unique devices, sensor readings
5. Gaming: Daily active users (DAU), monthly active users (MAU)
6. Social Media: Unique post impressions, engagement

Best Practices

1. Use for large datasets: 100k+ rows where performance matters
2. Document approximation: Let stakeholders know counts are approximate
3. Benchmark accuracy: Test on your data to understand error rates
4. Combine with sampling: Further improve performance on massive datasets
5. Use for trending: Perfect for dashboards showing trends over time
6. Avoid mixing: Don't mix exact and approximate counts in comparisons

Related Functions

• COUNT(DISTINCT) - Exact distinct count (slower)
• APPROX_PERCENTILE - Approximate percentile calculations
• APPROX_TOP_COUNT (BigQuery) - Approximate top-k frequent values
• HLL_COUNT functions (BigQuery) - Advanced HyperLogLog operations