DP-300 Question #139: Real Exam Question with Answer & Explanation

Question

Hotspot Question You are performing exploratory analysis of bus fare data in an Azure Data Lake Storage Gen2 account by using an Azure Synapse Analytics serverless SQL pool. You execute the Transact-SQL query shown in the following exhibit. Use the drop-down menus to select the answer choice that completes each statement based on the information presented in the graphic. Answer:

Options

• __typehotspot
• variantdropdown

Explanation

This hotspot question tests understanding of how Azure Synapse Analytics serverless SQL pool reads Parquet or CSV files from ADLS Gen2 using OPENROWSET, and how query results and row counts behave based on the file path pattern and schema inference.

Approach. In Azure Synapse serverless SQL pools, OPENROWSET with BULK can use wildcards (e.g., .csv or .parquet) to read multiple files from ADLS Gen2, and the query returns all rows across all matched files. The SELECT COUNT() returns the total number of rows across all files matching the path pattern. If the BULK path points to a folder or uses a wildcard, all matching files are scanned. The schema is inferred or explicitly defined via WITH clause, and if no WITH clause is used, all columns are returned as generic types. The key concepts are: (1) wildcard paths aggregate rows from multiple files, (2) COUNT() reflects total rows from all matched files, and (3) OPENROWSET without a WITH clause returns rows but requires explicit column references or SELECT * which returns a single 'doc' column for JSON or typed columns for CSV/Parquet depending on format specification.

Concept tested. Azure Synapse Analytics serverless SQL pool OPENROWSET function behavior when querying ADLS Gen2 files - specifically how wildcard file paths aggregate data across multiple files, how row counts are computed, and how the result set structure is determined by the presence or absence of a WITH clause and the file format specified.

Reference. https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/develop-openrowset

No community discussion yet for this question.