Select Two (Multi-Select) Questions

Using the COPY statement

The COPY statement is a modern, high-performance bulk load method, but it is not the only valid option; the question requires exactly two answers.

Using PolyBase to load from Azure Blob Storage

PolyBase is a valid method for loading data from Azure Blob Storage, but it is not the only option; the question requires exactly two answers.

Copy activity

The Copy activity moves data from each source (Blob Storage, Azure SQL Database, REST API) into the dedicated SQL pool, satisfying the multi-source ingestion requirement. A subsequent activity then invokes the stored procedure for aggregation.

Stored procedure activity

The stored procedure activity invokes a stored procedure in Azure Synapse Analytics, running the aggregation logic after the copy activities finish loading data from Blob Storage, Azure SQL Database and the REST API into the dedicated SQL pool.

Use Synapse SQL pool to run T-SQL queries.

A dedicated Synapse SQL pool is a provisioned MPP engine that executes T-SQL, including distributed queries, stored procedures and CETAS, directly against data held in its own distributions. This satisfies the requirement for a T-SQL-based processing path within Synapse Analytics.

Use Synapse Spark notebooks to run Scala code.

Synapse Spark pools run Apache Spark, so notebooks can execute Scala, PySpark or Spark SQL against data in the linked Data Lake. This provides the code-first processing route that complements the T-SQL engine, satisfying the requirement for a second valid processing method.

Use access control lists (ACLs) to restrict access to specific directories

ACLs apply POSIX-style permissions at directory and file level, letting you grant or deny access for specific Microsoft Entra ID principals without broad role assignments. This satisfies the requirement to secure sensitive data by restricting access to specific directories, complementing role-based access control at the container scope.

Enable encryption at rest using customer-managed keys in Azure Key Vault

Customer-managed keys in Azure Key Vault satisfy the requirement to control encryption of data at rest, letting your organisation own and rotate the key protecting the storage account rather than relying on Microsoft-managed keys. This meets the stem's sensitive-data constraint by ensuring only holders of the Key Vault key can decrypt the Data Lake content.

Avoid data skew by choosing a good distribution key.

A well-chosen hash distribution key spreads rows evenly across the sixty distributions, preventing one distribution from bearing disproportionate rows. Skew forces serialised processing, so even distribution directly supports the parallel-query design of dedicated SQL pools.

Use replicated tables for small dimension tables (less than 1 GB).

Replicated tables copy the full table to every compute node, eliminating data movement during joins. For dimension tables under 1 GB, this removes shuffle operations and is cheaper than hash distribution, satisfying the small-dimension join pattern.

Use hash distribution on a column with high cardinality for large fact tables.

Hash distribution on a high-cardinality column spreads large fact table rows evenly across the 60 distributions, preventing data skew and maximising parallel query throughput. This satisfies the stem's dedicated SQL pool constraint, where even distribution is essential for large fact tables and hash distribution outperforms round-robin for frequent joins.

Storage account diagnostic settings.

Storage account diagnostic settings stream control-plane and data-plane events, including authenticated read, write and delete operations, to Log Analytics, storage or Event Hubs. This provides the audit trail of data access and changes required by the stem.

Azure Monitor and Microsoft Sentinel.

Azure Monitor collects and queries the diagnostic logs, while Microsoft Sentinel correlates them into detections and incidents. Together they deliver auditing of data access and changes across Data Lake Storage Gen2, satisfying the stem's requirement.

Delta Lake provides time travel capabilities for accessing historical data versions.

Delta Lake's transaction log stores metadata for every write, enabling time travel to query or restore earlier table versions without duplicating the underlying Parquet files. This satisfies the daily batch scenario's need for historical data access and rollback, while retaining Parquet's columnar storage benefits for cost-effective processing.

Delta Lake supports ACID transactions, ensuring data consistency during concurrent writes.

Delta Lake's transaction log delivers ACID guarantees, so concurrent writes during the daily batch cannot leave partially committed or inconsistent output in ADLS Gen2. Parquet files lack this coordination, risking corrupt reads if jobs overlap. This satisfies the stem's data consistency requirement while remaining cost-effective for batch processing.

Enable Azure Storage Service Encryption (SSE) for data at rest

Azure Storage Service Encryption automatically encrypts data at rest using 256-bit AES, covering the stem's at-rest security requirement for Azure Data Lake Storage Gen2. It applies transparently to blobs and files with Microsoft-managed or customer-managed keys, requiring no application changes.

Use Azure RBAC and ACLs to control access to data

Combining Azure RBAC for management-plane and directory-level scope with ACLs for file and folder granularity enforces least-privilege access, satisfying the stem's authorisation requirement. This layered model prevents broad role assignments from exposing sensitive data lake paths to unauthorised principals.

Require HTTPS for all data transfers

Requiring HTTPS enforces TLS encryption for all data in transit, satisfying the stem's in-transit security requirement. Azure Data Lake Storage Gen2 rejects unencrypted HTTP requests when secure transfer is enabled, protecting credentials and payloads from interception on the network.

Enable Microsoft Defender for Storage to get security alerts about unusual access patterns.

Microsoft Defender for Storage continuously analyses data-plane telemetry and raises security alerts for anomalous access, such as unusual locations or suspicious enumeration. This directly satisfies the requirement to detect and alert on access patterns indicating a possible breach in the hierarchical-namespace account.

Ingest the logs into Microsoft Sentinel and create analytics rules for anomalous patterns.

Microsoft Sentinel ingests storage diagnostic logs and applies analytics rules and machine learning to surface anomalous access patterns, generating alerts and incidents. This satisfies the requirement to detect unusual access indicative of a breach, rather than merely retaining logs for later manual review.

Enable diagnostic settings on the storage account to collect read, write, and delete logs.

Diagnostic settings stream read, write and delete logs from the storage account to a destination such as Log Analytics or a storage account. This supplies the access telemetry that anomaly detection depends on, satisfying the requirement to capture the data needed to identify unusual access patterns.

Configure ACLs to grant least privilege to users and groups

POSIX-style ACLs on Data Lake Storage Gen2 apply at directory and file level, enforcing least-privilege access for individual users and groups independently of broad role assignments. This granular permission model directly prevents unauthorised reads by limiting each principal to only the paths they require.

Use private endpoints to restrict access to the storage account

Private endpoints assign a private IP from your virtual network to the storage account, removing exposure to the public internet. Traffic then traverses the Microsoft backbone via Azure Private Link, so unauthorised access from outside the approved network boundary is blocked at the network layer.

SQL Server Integration Services (SSIS) package

SQL Server Integration Services is a valid method because it is a full-featured ETL tool that can connect to on-premises SQL Server and load data directly into Azure Synapse Analytics using connectors like the Synapse destination adapter.

Azure Data Factory with incremental copy

Azure Data Factory with incremental copy is valid because it uses a self-hosted integration runtime to connect to on-premises SQL Server and can perform incremental loads using change tracking or watermark columns, minimizing data transfer.

Use T-SQL scripts in a dedicated SQL pool.

T-SQL is a primary way to transform data in Synapse SQL pools.

Use Mapping Data Flows in Synapse pipelines.

Data Flows provide visual transformations.

Use Spark notebooks in Synapse Spark pools.

Spark notebooks allow code-based transformations.

Pivot

Pivot is a native Mapping Data Flows transformation, reshaping rows into columns through an aggregate-style pivot operation on selected group-by and pivot keys. It satisfies the stem's requirement for transformations available within Mapping Data Flows, unlike pipeline activities or external compute, running on the Spark execution engine.

Derived Column

Derived Column is a native Mapping Data Flows transformation, letting you generate new columns or modify existing ones using expression-builder logic during the data flow execution. It satisfies the stem's requirement for transformations available within Mapping Data Flows, unlike pipeline activities such as Copy or Lookup, which operate outside the data flow canvas.

Aggregate

Aggregate is a native Mapping Data Flow transformation, performing group-by aggregations such as SUM, COUNT, and AVG on streamed data within the data flow graph. It satisfies the requirement by providing in-pipeline aggregation without invoking external compute.

Amazon S3

Amazon S3 is supported via the Amazon S3 connector.

Azure Blob Storage

Azure Blob Storage is a first-class source for Copy activity.

Use materialized views for complex aggregations

Materialized views persist precomputed results of complex aggregations, so repeated queries read stored data instead of rescanning the large fact table. This directly reduces compute and elapsed time for the aggregation-heavy workloads the dedicated SQL pool must optimise.

Create clustered columnstore indexes

Clustered columnstore indexes store data column-wise with high compression and batch-mode processing, giving the dedicated SQL pool its fastest scan performance on large fact tables. This is the default and recommended physical design for analytical queries.

Use hash distribution on columns used in JOINs

Hash distribution spreads rows across distributions by hashing the join key, so matching rows land on the same distribution. This enables collocated joins that avoid costly data movement shuffles across the dedicated SQL pool's compute nodes.

Run the OPTIMIZE command to compact small files and improve read performance.

OPTIMIZE compacts many small files into larger ones, reducing per-file overhead and metadata pressure during reads. This satisfies the small-file performance constraint, since Delta Lake read latency grows with file count; bin compaction via OPTIMIZE restores efficient scan throughput in Synapse.

Partition the table on columns that are used in WHERE clauses to enable partition pruning.

Partitioning on frequently filtered columns lets Synapse's Delta reader skip entire partitions via partition pruning, cutting scanned data and I/O. This directly satisfies the WHERE-clause access pattern constraint, since pruning only works when predicates align with the partition key columns.

Use Z-order on columns that are frequently used in filter predicates.

Z-ordering co-locates related data within Delta files, so filter predicates on those columns skip irrelevant row groups via data skipping. This directly reduces bytes scanned for frequently filtered columns, satisfying the Delta Lake performance optimisation requirement.

Copy

Correct. Copy activity is a core data movement activity in ADF.

Databricks Notebook

Correct. Databricks Notebook activity is a valid compute activity in ADF.

Execute Pipeline

Correct. Execute Pipeline is a valid control flow activity in ADF, though the exact name is 'Execute Pipeline Activity'.

Align distribution keys on tables that are frequently joined together

Aligning distribution keys on tables that are frequently joined together ensures that the join columns are hash-distributed on the same key, enabling collocated joins. This avoids data movement across distributions during query execution, which significantly improves performance.

Minimize data skew by choosing a distribution key with many unique values

Minimizing data skew by choosing a distribution key with many unique values helps ensure that data is evenly distributed across all distributions, preventing performance bottlenecks caused by one distribution handling a disproportionate amount of data.

Consider the size of the table and the frequency of joins

Table size determines whether replication or hash distribution suits a table, while join frequency dictates co-locating join keys to avoid costly data movement across distributions. Both directly shape distribution choice, making them core design considerations for dedicated SQL pool performance.

Use Delta Lake format and optimize the table with Z-ordering on frequently filtered columns.

Delta Lake with Z-ordering co-locates related data in the same files using multi-dimensional clustering on frequently filtered columns, enabling data skipping that reads far fewer files. This directly reduces I/O against Azure Data Lake Storage Gen2, satisfying the stem's I/O-bound constraint.

Increase the number of partitions in the DataFrame to improve parallelism.

Increasing DataFrame partitions spreads reads across more Spark tasks, raising parallelism so more executors read from Azure Data Lake Storage Gen2 concurrently. This directly addresses the I/O-bound bottleneck by improving read throughput, satisfying the stem's requirement for a performance strategy.

Cache the DataFrame in memory after reading to avoid re-reading from disk.

Caching the DataFrame in memory with persist or cache keeps transformed data on the cluster, so subsequent actions avoid re-reading from Azure Data Lake Storage Gen2. This directly reduces the disk I/O that makes the batch job I/O bound, satisfying the stem's stated bottleneck.

tempdb usage

tempdb usage in a dedicated SQL pool indicates spill-to-disk from insufficient memory grants during large sorts, joins and aggregations. Monitoring it reveals queries exceeding granted memory, which degrade performance and signal the need for statistics updates or query tuning.

DWU usage

DWU usage directly reflects how much of the provisioned data warehouse units a dedicated SQL pool consumes, exposing whether compute capacity is saturated. Monitoring it satisfies the stem's requirement to track performance against provisioned resources, letting you scale DWUs or reschedule workloads before queuing degrades query throughput.

Queued queries

Queued queries counts requests waiting for resources before execution begins. Sustained queueing signals insufficient compute capacity, concurrency limits or blocking, making it a direct indicator of dedicated SQL pool performance degradation that administrators must monitor.