Secure, monitor, and optimize data storage and data processing practice questions

Column-level security masks.

Why it fails: Column-level security masks are used to mask sensitive data at the column level, not for ABAC with tags.

Dynamic views with user context functions.

Why it fails: Dynamic views with user context functions enable ABAC by filtering data based on the user's attributes, such as group membership or identity, which can be mapped to table tags.

Row-level security filters.

Why it fails: Row-level security filters restrict rows based on conditions, but they do not directly provide ABAC using table tags in Unity Catalog.

Set up a site-to-site VPN between the on-premises network and Azure.

Why it fails: VPN is not required for TLS encryption.

Configure the on-premises SQL Server to use SSL certificates.

Why it fails: Configuring the on-premises SQL Server to use SSL certificates is necessary but not sufficient; Azure Data Factory must also use HTTPS.

Use Azure ExpressRoute with private peering.

Why it fails: Azure ExpressRoute provides a private connection but does not replace TLS.

The 'keySource' should be 'Microsoft.Storage' for customer-managed key.

Why it fails: Setting 'keySource' to 'Microsoft.Storage' selects platform-managed keys, so the auditor's customer-managed key requirement cannot be met. The tempting error is assuming the provider namespace denotes the key owner; in fact 'Microsoft.Keyvault' is required, pointing at an Azure Key Vault holding the customer's key.

The storage account requires double encryption to use customer-managed key.

Why it fails: Double encryption is an independent, optional setting and is not a prerequisite for customer-managed keys; accounts use customer-managed keys without it. Infrastructure encryption is the correct choice when regulation demands two layers of encryption at rest with separate algorithms or keys.

The 'infrastructureEncryption' setting is enabled, which overrides customer-managed key.

Why it fails: Infrastructure encryption does not override or disable customer-managed keys; the two settings are independent and combine, giving double encryption with customer-managed keys. Enabling infrastructureEncryption is correct when both platform-level and customer-managed encryption layers are required for compliance.

sys.dm_pdw_wait_stats

Why it fails: sys.dm_pdw_wait_stats aggregates wait types and durations, not per-query memory grants, so it cannot isolate queries under memory pressure. It is the right DMV for diagnosing contention such as CXPACKET or IO waits, which is why it appears plausible when queries are merely slow.

sys.dm_pdw_query_stats_xe

Why it fails: sys.dm_pdw_query_stats_xe exposes extended-event query execution statistics such as duration and CPU, but no memory grant or spill columns, so memory pressure cannot be identified. It is correct when you need historical query-level performance counters rather than resource-pressure diagnostics.

sys.dm_pdw_nodes_os_performance_counters

Why it fails: sys.dm_pdw_nodes_os_performance_counters reports node-level operating system counters such as CPU and memory utilisation, aggregated per node rather than per query. It suits capacity monitoring of the appliance, which is why it looks relevant, but it cannot attribute memory pressure to individual queries.

Generate a shared access signature (SAS) token for the Synapse Analytics workspace.

Why it fails: SAS tokens are not used for Synapse Analytics authentication from Stream Analytics.

Create a user-assigned managed identity and assign it to the Stream Analytics job.

Why it fails: A user-assigned managed identity cannot be assigned to a Stream Analytics job because the service only supports system-assigned managed identities for authenticating to Azure Synapse Analytics. This option is tempting because user-assigned identities are commonly used across many Azure services to grant granular, reusable permissions, and would be correct for scenarios where multiple resources need to share the same identity, such as when several jobs must authenticate to the same Synapse workspace without individual identity management.

Configure the output to use SQL Server authentication with a username and password.

Why it fails: This is not managed identity authentication.

Create an alert rule in Azure Data Factory based on the 'Failed pipeline runs' metric.

Why it fails: Azure Data Factory does not support metric alerts directly; you use Azure Monitor.

Configure diagnostic settings to send logs to Log Analytics and create a log alert.

Why it fails: This works but is less efficient than a metric alert.

Use a Logic App to periodically check the pipeline run status and send notifications.

Why it fails: This is manual and not efficient.

Use hash distribution on a column that is not used in joins.

Why it fails: This can cause unnecessary data shuffling.

Use round-robin distribution for large fact tables.

Why it fails: Round-robin spreads rows evenly but provides no co-location, so joins and aggregations on fact tables force shuffle operations across distributions. It suits staging or temporary tables where no consistent join key exists, not large fact tables queried against dimensions.

Increase the resource class for the loading user.

Why it fails: Resource class governs memory and concurrency slots for a loading session; it does not alter table distribution or co-location, so cross-node shuffles during queries remain unchanged. Raising it helps when ingestion concurrency or memory per load is the bottleneck.

SQL connection strings.

Why it fails: SQL connection strings carry credentials for database engines such as Azure SQL Database; ADLS Gen2 has no SQL endpoint and accepts no such strings for authentication or authorisation. It is tempting because connection strings are a familiar credential mechanism, but they apply to relational data stores, not the ADLS Gen2 REST and Blob APIs.

Managed identities.

Why it fails: Managed identities are a valid authentication method for ADLS Gen2, but this question asks for three methods and this option is not among the accepted set for the scenario as examined. It is tempting because managed identities genuinely authenticate Azure resources to storage via Microsoft Entra ID, which is their real purpose.

Integration runtime CPU utilization.

Why it fails: Integration runtime CPU utilisation reflects the compute executing the Copy activity, not the Synapse sink's ingestion capacity, so it misses sink-side bottlenecks. It suits diagnosing self-hosted IR saturation or parallel-copy throughput limits, not dedicated SQL pool write performance.

Pipeline run duration.

Why it fails: Pipeline run duration is an outcome, not a diagnostic metric; it confirms slowness but cannot localise whether source reads, staging or Synapse writes cause it. It suits SLA tracking and trend reporting, not identifying the specific bottleneck stage.

The filter predicate is set to 'true', which only captures a subset of events.

Why it fails: A predicate of 'true' matches every event, so it cannot be the cause of missing read events. Predicates are the correct mechanism for narrowing or broadening which events a policy captures, so this option misstates how the filter behaves.

The action group 'ALL_ACTIONS' does not include read events.

Why it fails: ALL_ACTIONS is a superset that includes read operations, so it cannot explain missing read events. Action groups define which operation categories a policy governs, and ALL_ACTIONS would be the correct choice when every action must be audited.

The policy excludes the 'Read' action by default.

Why it fails: No default exclusion of Read exists in Purview data policies; actions are governed only by what the policy explicitly specifies. Read is included when listed, so this option invents behaviour that would otherwise be the correct configuration for auditing reads.

Set the pipeline's retry property to 3 and retry interval to 60 seconds.

Why it fails: Incorrect because pipelines in Azure Data Factory do not have a retry property; retry is configured at the activity level.

Set the activity's retry property to 3 and retry secs to 60.

Why it fails: Correct because this sets the activity's retry count to 3 and the fixed retry interval to 60 seconds, which is the available retry mechanism in Azure Data Factory.

Set the trigger's retry policy to 3 with exponential backoff.

Why it fails: Incorrect because triggers do not have a retry policy; retry must be set on the activity itself.

sys.dm_pdw_resource_waits

Why it fails: Shows resource wait statistics, not memory per query.

sys.dm_pdw_nodes

Why it fails: Shows node-level information, not query memory.

sys.dm_pdw_waits

Why it fails: Shows wait types, not query memory consumption.

Create a nonclustered index on the date column.

Why it fails: Helps but less effective than partitioning for large scans.

Create a clustered columnstore index on the table.

Why it fails: A clustered columnstore index already exists by default on dedicated SQL pool tables, so creating one changes nothing. Columnstore suits large fact tables and is the right structure for analytical scans; the full scan persists because the date filter cannot be pushed down without partitioning or suitable indexing on that predicate.

Change the distribution to round-robin.

Why it fails: Round-robin distribution spreads rows evenly but provides no co-location for joins, so it cannot eliminate the full scan on a date filter. It is chosen when no natural distribution key exists or to avoid skew; here a date-based partition or hash distribution would prune the scanned data.

Azure Purview

Why it fails: Data governance, not monitoring.

Azure Log Analytics

Why it fails: Part of Azure Monitor, but Azure Monitor is the overarching service.

Azure Sentinel

Why it fails: Security information and event management.

It reduces the amount of data scanned in the source.

Why it fails: Staging does not affect source scanning.

It enables data validation before loading.

Why it fails: Staging does not inherently validate data.

It transforms data into columnstore format before loading.

Why it fails: Transformation is separate from staging.

Scale up the SQL pool to a higher DWU.

Why it fails: Scaling up increases resources but is not a specific optimization technique.

Use heap indexes for fact tables.

Why it fails: Heap indexes suit small staging tables, not large fact tables where clustered columnstore delivers the compression and segment elimination that cut I/O. Choosing heaps leaves fact tables without columnstore's scan efficiency, so full-table reads dominate. Heaps would be correct for transient staging loads before a CTAS into a columnstore table.

Use round-robin distribution for all large fact tables.

Why it fails: Round-robin spreads rows evenly but forces data movement during joins, since matching keys land on different distributions. Hash distribution on the join column colocates rows, eliminating shuffles. Round-robin is correct for staging tables with no joins or when no suitable distribution key exists.

CPU percentage

Why it fails: CPU percentage measures compute consumption within the dedicated SQL pool, but it cannot distinguish whether a bottleneck originates from skewed data distribution, waiting tasks, or tempdb contention. It is tempting because CPU percentage is a standard Azure Monitor metric for identifying compute saturation in general-purpose workloads, and would be the correct choice when diagnosing an under-provisioned or overworked compute tier.

Storage used

Why it fails: Storage used reports space consumed by the dedicated SQL pool, which does not identify query performance bottlenecks. It is the correct metric for capacity planning and detecting approaching storage limits, not for diagnosing slow queries.

DWU used

Why it fails: DWU used reports provisioned Data Warehouse Units against the service level objective, a capacity and cost signal rather than a bottleneck indicator. It is tempting because scaling DWU is a genuine tuning lever, and it would be the right metric when validating whether a workload is saturating its purchased compute allocation.

Set the pipeline's 'Concurrency' to 3 and 'Retry' to 1.

Why it fails: Concurrency controls how many pipeline runs execute simultaneously, and pipeline-level retry applies to the whole run, not individual activities; neither setting produces a 5-minute delay between three activity retries. It tempts because both settings mention retry counts, but they govern run scheduling, not transient-failure recovery.

Leave the default settings as they are because Azure Data Factory automatically retries failed activities 3 times.

Why it fails: Azure Data Factory applies no automatic activity retries by default; the retry count and interval must be configured explicitly on each activity. Leaving defaults means transient network errors fail the run immediately. It tempts because some Azure services do retry automatically, but Data Factory does not.

Configure a 'Retry' policy on the pipeline itself, setting maximum retries to 3 and retry interval to 5 minutes.

Why it fails: Retry policy configured at pipeline level does not exist as an activity retry mechanism; retry count and interval are set on individual activities (or via a Retry policy on the activity), and pipeline-level settings govern run concurrency and timeout. It tempts because the pipeline is where failures surface.

Configure a firewall rule to allow only specific IP addresses.

Why it fails: Incorrect: Firewall controls network access, not data protection within the database.

Enable Azure Storage encryption for the underlying storage.

Why it fails: Incorrect: Dedicated SQL pool data is stored in SQL Server files, not directly in Azure Storage; TDE covers that.

Use Dynamic Data Masking to obfuscate sensitive data from all users.

Why it fails: Incorrect: DDM masks data but does not prevent users with higher privileges from seeing unmasked data.

Enable automatic statistics update and wait for the system to update them.

Why it fails: Incorrect: While automatic update is enabled by default, it may not update all statistics in a timely manner.

Execute `sp_updatestats` in the master database.

Why it fails: Incorrect: The procedure must be run in the user database where the tables reside.

Run `UPDATE STATISTICS` on each table individually.

Why it fails: Incorrect: This is inefficient for many tables.