Hotspot Question You are building a database in an Azure Synapse Analytics serverless SQL pool. You have data stored in Parquet files in an Azure Data Lake Storage Gen2 container. Records are structur

Submitted by rachelw· Mar 6, 2026Design and Implement Data Storage – specifically querying semi-structured and nested data in a serverless SQL pool using external tables or OPENROWSET against Parquet/JSON files in Azure Data Lake Storage Gen2.

Question

Hotspot Question You are building a database in an Azure Synapse Analytics serverless SQL pool. You have data stored in Parquet files in an Azure Data Lake Storage Gen2 container. Records are structured as shown in the following sample. The records contain two applicants at most. You need to build a table that includes only the address fields. How should you complete the Transact-SQL statement? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point. Answer:

Exhibits

Explanation

In Azure Synapse Analytics serverless SQL pools, OPENROWSET with the Parquet format is used to read files from ADLS Gen2, and CROSS APPLY OPENJSON is used to flatten nested JSON arrays within Parquet records. To extract only address fields from a structure containing up to two applicants, you would use CROSS APPLY to expand the applicants array and then select the specific address sub-fields (such as street, city, state, zip) using dot notation or JSON path expressions, ensuring each applicant's address appears as its own row.

Topics

#Azure Synapse Analytics#Serverless SQL Pool#OPENROWSET Parquet#OPENJSON / Nested Data Flattening

Community Discussion

No community discussion yet for this question.

Full DP-300 Practice