Microsoft Certified: Fabric Analytics Engineer AssociateExplore and analyze data (15-20%)Easy
A data analyst is querying a large Spark Delta table `customer_feedback` in Microsoft Fabric. The table has columns `feedback_id` (string), `customer_id` (int), `feedback_text` (string), and `submission_date` (date). The analyst needs to count the total number of feedback entries. Which Spark SQL query should they use?
- ASELECT COUNT(feedback_id) FROM customer_feedback
- BSELECT COUNT(*) FROM customer_feedback
- CSELECT SUM(1) FROM customer_feedback
- DSELECT COUNT(1) FROM customer_feedback
Show answer & explanationAnswer & explanation
Correct answer: B. SELECT COUNT(*) FROM customer_feedback
`COUNT(*)` is the most common and efficient way to count all rows in a table, including those with NULL values in any column. It is universally supported and clear in its intent to count records.
Why the other options are wrong
- A. `COUNT(feedback_id)` counts non-NULL values in the `feedback_id` column. While `feedback_id` is likely non-nullable, `COUNT(*)` is the more general and often slightly more performant approach for counting all rows.
- C. `SUM(1)` would sum the constant `1` for each row, effectively counting rows. However, `COUNT(*)` is the dedicated aggregate function for this purpose and is generally preferred for clarity and potential optimization by the query optimizer.
- D. `COUNT(1)` also counts all rows, similar to `COUNT(*)`, as the constant `1` is never NULL. It's functionally equivalent to `COUNT(*)` in most SQL engines, but `COUNT(*)` is more idiomatic.
Spark SQL COUNT(*)
The `COUNT(*)` aggregate function in Spark SQL returns the total number of rows in a table or a specified group, including rows that contain NULL values in any column.
- Counts all rows, regardless of NULL values.
- Often the most efficient way to get a total row count.
- Equivalent to `COUNT(1)` in most SQL dialects.
- Does not require specifying a column name.
Memory trick: Count star for all; count column for non-nulls only!