AdministratorLot of great updates here, but unless APPROXIMATEDISTINCTCOUNT in import mode is a precursor update to full HyperLogLog (HLL) support in Vertipaq, I think you missed the point of having approximate distinct count in import mode at all. If you're going to go to the trouble of importing a key column for counts, why wouldn't you use actual DISTINCTCOUNT? The benefit of having approximate distinct count (via HLL) support in import mode is that you could significantly reduce the number of rows required and enable dynamic, re-aggregable approximate distinct counts for columns that otherwise cannot fit in-memory by merging the HLL sketches at query time. Maybe it's a pipe-dream, but simply enabling APPROXIMATEDISTINCTCOUNT for use in import-mode does not help much on data at a very large scale.
Approximate Distinct Count in Import Mode via HyperLogLog (HLL) | Microsoft Fabric Community
Hi tate-bowman, your comment made me curious, so I tested it (F64 trial, Direct Lake on OneLake, 100M rows, 36.7M distinct customers).
You're partly right. With the column already in memory, APPROXIMATEDISTINCTCOUNT is still much faster on warm queries: 8.6 s -> 0.3 s for a single total, 28 s -> 0.7 s grouped by day, and about 10x fewer CU across the same 60 queries. On a cold start the advantage drops to 1.3-2x, because loading the column takes about 20 s either way.
On memory you're right. Each ADC query used at least ~1 GB, and with 150k small groups (~550 distinct customers each) it hit the 10 GB per-query limit, while DISTINCTCOUNT handled the same query in 3.3 GB. So it doesn't help with data that doesn't fit in memory - pre-aggregated, mergeable HLL sketches as in your idea would still be the way to go there.
One more thing for the product team: when Direct Lake on SQL falls back to DirectQuery, the measure fails with "isn't supported for this data source", even though the SQL analytics endpoint itself supports APPROX_COUNT_DISTINCT.
Full write-up (in Polish): https://lnkd.in/p/eadzuM-2
