Azure Synapse Analytics Flashcards

How many distributions does an Azure Synapse Analytics dedicated SQL pool use to spread data across compute nodes?

Answer: 60

Azure Synapse Analytics dedicated SQL pools always use exactly 60 distributions to enable massively parallel processing (MPP) across compute nodes.

Which table distribution type in an Azure Synapse dedicated SQL pool is best for large fact tables that are frequently joined on a specific column?

Answer: HASH

HASH distribution places rows sharing the same hash value on the same distribution, reducing data movement during joins when the join column matches the hash key.

Which index type in Azure Synapse dedicated SQL pools provides the best compression and performance for large-scale analytical queries?

Answer: Clustered columnstore index

Clustered columnstore indexes store data column-by-column enabling high compression ratios and efficient columnar scans that dramatically improve analytical query performance.

What is the primary purpose of an Azure Synapse workspace?

Answer: Providing a unified collaboration environment integrating SQL, Spark, pipelines, and analytics

A Synapse workspace is a unified analytics platform that integrates dedicated SQL pools, serverless SQL, Apache Spark, Data Explorer, and pipeline authoring in a single environment.

What is the main advantage of using REPLICATE table distribution in Azure Synapse dedicated SQL pools?

Answer: Copies the full table to each compute node, eliminating data movement during joins

REPLICATE caches a full copy of the table on each compute node so that joins with large distributed tables require no data movement across nodes.

In Azure Synapse Analytics dedicated SQL pools, what does the service objective (e.g., DW1000c) control?

Answer: The number of Data Warehouse Units (DWUs) and compute power allocated

The service objective specifies the DWU (Data Warehouse Unit) allocation, which determines the compute power, number of compute nodes, and query concurrency available.

Which feature in Azure Synapse Analytics allows SQL queries to access data stored in Azure Data Lake Storage Gen2 without loading it into a dedicated SQL pool?

Answer: External tables with PolyBase

PolyBase external tables enable Synapse SQL to query data directly in Azure Data Lake Storage Gen2 without physically importing the data into the dedicated SQL pool.

What is the primary purpose of Azure Synapse Link for Azure Cosmos DB?

Answer: Enabling near real-time analytics on operational Cosmos DB data without ETL pipelines

Azure Synapse Link creates a tight integration between Cosmos DB's analytical store and Synapse Analytics, enabling zero-ETL near real-time analytics without impacting transactional performance.

Which file format is NOT natively supported by Azure Synapse Analytics serverless SQL pools for querying data in Azure Data Lake Storage?

Answer: Avro

Serverless SQL pools natively support Parquet, CSV, JSON, and Delta Lake formats; Avro is not natively supported and requires additional workarounds.

What is the primary use case for Apache Spark pools in Azure Synapse Analytics?

Answer: Running distributed data processing, transformation, and machine learning workloads

Apache Spark pools provide a managed distributed computing environment supporting Python, Scala, R, and .NET for large-scale data processing and machine learning.

What does the Delta Lake format primarily provide when used in Azure Synapse Analytics?

Answer: ACID transactions, schema enforcement, and time travel on data lake storage

Delta Lake adds ACID transaction guarantees, schema enforcement, and time travel (data versioning) capabilities on top of Parquet files in Azure Data Lake Storage.

What connector allows Apache Spark pools in Azure Synapse Analytics to efficiently read from and write to dedicated SQL pools?

Answer: Synapse Spark connector (azure-synapse-spark-connector)

The Synapse Spark connector uses a high-performance bulk load mechanism to efficiently move data between Spark pools and dedicated SQL pools within the same workspace.

Which approach best optimizes query performance in Azure Synapse Analytics serverless SQL pools?

Answer: Using Parquet format with folder partitioning and creating table statistics

Parquet enables predicate and projection pushdown, folder-based partitioning eliminates unnecessary file reads via partition pruning, and statistics help the query optimizer choose efficient plans.

Which Azure Synapse dedicated SQL pool feature stores query results so that subsequent identical queries return instantly without re-execution?

Answer: Result-set caching

Result-set caching stores the complete output of a query and serves it from cache for identical subsequent queries, dramatically reducing latency for repeated analytical queries.

What is the purpose of workload management in Azure Synapse Analytics dedicated SQL pools?

Answer: Allocating and isolating compute resources for different user groups to enforce query SLAs

Workload management lets administrators define workload groups with specific resource minimums and caps, ensuring critical workloads receive guaranteed compute resources and concurrency slots.

In Azure Synapse Analytics dedicated SQL pools, which T-SQL object must be created first to define the location and authentication for an external data source like Azure Data Lake Storage Gen2?

Answer: EXTERNAL DATA SOURCE

CREATE EXTERNAL DATA SOURCE defines the URI location and credential reference for the external storage, which is then referenced by EXTERNAL TABLE definitions using PolyBase.

What feature of Azure Synapse Analytics dedicated SQL pools allows organizations to stop compute charges during periods of inactivity without losing data?

Answer: Pause and resume

Pausing a dedicated SQL pool stops the compute layer and associated DWU charges while all data stored in Azure Storage is preserved and instantly available upon resume.

What is Azure Synapse Studio?

Answer: A web-based unified IDE for SQL development, Spark notebooks, pipeline authoring, and monitoring

Synapse Studio is a web-based development environment embedded in the Azure portal that consolidates SQL scripting, Spark notebook authoring, pipeline design, data exploration, and monitoring.

Which Azure Synapse Analytics component enables federated queries that join data from Azure Data Lake Storage, Cosmos DB, and other sources without physically moving the data?

Answer: Serverless SQL pool with OPENROWSET or external tables

Serverless SQL pool uses OPENROWSET or external table syntax to query multiple external data sources in a single query, federating results across sources without data movement.

What happens to data stored in an Azure Synapse Analytics dedicated SQL pool when the pool is paused?

Answer: Data remains intact in Azure Storage and is fully preserved

Dedicated SQL pools separate compute and storage; pausing only stops the compute nodes while data in Azure Storage remains untouched and is immediately available when the pool is resumed.