SnowPro Advanced Architect Practice Test

Understanding how NULLs influence different aggregate functions helps you read these results correctly. The query mixes several counting methods: a row-level count, standard counts by column, and counts of distinct values, including an approximate distinct. First, count(*) returns the total number of rows in the table, regardless of NULLs in any column. So you get all rows, which is 9 here. Second, count(nct.*) uses a row expression. It counts rows where the entire row value is not NULL. If some rows have every column NULL, they don’t contribute to this count. In this case, there are 4 such all-NULL rows, leaving 5 rows that have at least one non-NULL column for this measure. Third, count(col1) tallies non-NULL values in a single column. That column has 6 non-NULL entries. Fourth, count(distinct col1) counts how many distinct non-NULL values appear in that column. There are 5 distinct non-NULL values. Fifth, count(distinct col1, col2) counts distinct combinations of those two columns, ignoring any rows where either component is NULL. The result here is 5 distinct non-NULL pairs. Finally, approx_count_distinct(*) estimates the number of distinct rows (treated as a row value) using a probabilistic algorithm. In this dataset, the estimate is 5, matching the distinct-row intuition from the exact counts. Putting it together, the resulting sequence is 9, 5, 6, 5, 5, 5.