Microsoft Certified: Fabric Analytics Engineer AssociateExplore and analyze data (15-20%)Easy
A data engineer is working with a large dataset in a Microsoft Fabric Lakehouse. They need to count the total number of rows in a Delta table named `customer_transactions` using Spark SQL. Which of the following queries will achieve this efficiently?
- ASELECT COUNT(customer_id) FROM customer_transactions;
- BSELECT NUM_ROWS FROM customer_transactions_metadata;
- CSELECT COUNT(*) FROM customer_transactions;
- DSELECT SUM(1) FROM customer_transactions;
Show answer & explanationAnswer & explanation
Correct answer: C. SELECT COUNT(*) FROM customer_transactions;
The COUNT(*) function is the most direct and efficient way to count all rows in a table in Spark SQL, as it counts every row regardless of null values in any column.
Why the other options are wrong
- A. COUNT(customer_id) would only count rows where 'customer_id' is not NULL, which may not be the total row count.
- B. There is no standard `NUM_ROWS` column in a user-accessible metadata table for a Delta table in Spark SQL for direct querying like this.
- D. SUM(1) can work but is generally less idiomatic and potentially less optimized than COUNT(*) for row counts.
Spark SQL COUNT(*)
A Spark SQL aggregate function used to count the total number of rows in a table or result set, including rows with NULL values in columns.
- Counts all rows regardless of column values.
- Most efficient way to get total row count.
- Ignores specific column values, only checks row existence.
Memory trick: Count All Rows, Efficiently and Clearly, with the Star.