Microsoft Azure Data FundamentalsDescribe an analytics workload on AzureHard
A data analyst is working with a large dataset in Azure Data Lake Storage Gen2. Before loading it into a data warehouse for reporting, they need to perform several data cleansing and transformation steps, including filtering out invalid records, joining multiple files, and aggregating data. They prefer to use SQL for these operations due to their existing skill set. Which Azure Synapse Analytics component would best facilitate this using a serverless approach?
- AData Explorer pool
- BServerless SQL pool
- CSpark pool
- DDedicated SQL pool
Show answer & explanationAnswer & explanation
Correct answer: B. Serverless SQL pool
The Azure Synapse Analytics serverless SQL pool allows you to query data directly from Azure Data Lake Storage Gen2 using T-SQL, without provisioning any resources, making it ideal for ad-hoc data exploration and transformation using SQL.
Why the other options are wrong
- A. A Data Explorer pool (Kusto) is for time series and log analytics, not for general-purpose SQL querying of data lake files.
- C. A Spark pool uses Apache Spark for big data processing and supports languages like Python, Scala, and SQL (Spark SQL), but the question emphasizes a preference for 'SQL' and a 'serverless approach' for direct querying of data lake files, which points more directly to serverless SQL pool's strength.
- D. A dedicated SQL pool requires provisioning and is designed for pre-loaded, structured data warehousing, not serverless querying of raw data lake files.
Azure Synapse Serverless SQL pool
A distributed data processing engine in Azure Synapse Analytics that enables you to query data in Azure Data Lake Storage using T-SQL without provisioning or managing any resources.
- Pay-per-query model, no upfront cost or server management.
- Ideal for ad-hoc data exploration, logical data warehousing, and data transformation.
- Queries data directly from files in Data Lake Storage (Parquet, CSV, JSON).
Memory trick: Serverless SQL queries your lake, no server worries.