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

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

Why wrong: VPN is not required for TLS encryption.

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

Why wrong: 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 wrong: Azure ExpressRoute provides a private connection but does not replace TLS.

Use a managed identity and assign it to the directory.

Why wrong: Managed identity is an identity used for authentication, not authorization. To grant permissions to a managed identity, you assign roles or set ACLs with the managed identity as the security principal.

Create a stored access policy on the directory.

Why wrong: Stored access policies are used with shared access signatures (SAS) to delegate access to containers or directories, not for granting permissions to service principals.

Set ACLs on the directory with default ACLs for the service principal.

Why wrong: Default ACLs apply only to new child items created within the directory, not to the directory itself or to existing objects, so they cannot grant the service principal read and write access to the target directory directly. This option is tempting because default ACLs are designed to propagate permissions to future files and subdirectories, making them correct when the requirement is to control access for newly created content rather than the existing directory.

Row-level security (RLS)

Why wrong: RLS restricts rows, not columns.

Azure Purview data policies

Why wrong: Azure Purview is a data governance service, not for column-level security.

Dynamic data masking (DDM)

Why wrong: Dynamic data masking obfuscates data but does not restrict access.

Managed identity authentication

Why wrong: Used for services, not interactive user queries.

SQL authentication

Why wrong: Less secure and not recommended for serverless.

Azure Key Vault authentication

Why wrong: Key Vault stores secrets, not authentication.

Generate a shared access signature (SAS) token for the storage account and include it in the external table definition.

Why wrong: Incorrect. A shared access signature (SAS) token is a shared secret that grants access to anyone who possesses it, not tied to individual user identities. It does not enforce per-user authorization based on Microsoft Entra ID roles.

Configure an IP firewall rule on the storage account to allow only the SQL pool's outbound IP addresses.

Why wrong: Incorrect. An IP firewall rule controls network-level access to the storage account, but it does not enforce user-level authorization. It would allow any user from the allowed IP range, not just those with specific Microsoft Entra ID roles.

Create a managed identity for the SQL pool and grant it access to the storage account.

Why wrong: Incorrect. A managed identity for the SQL pool would grant access to the service itself, not to individual users. This bypasses per-user authorization and does not restrict access to users with specific Microsoft Entra ID roles.

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

Why wrong: 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 wrong: Incorrect: The procedure must be run in the user database where the tables reside.

Run `UPDATE STATISTICS` on each table individually.

Why wrong: Incorrect: This is inefficient for many tables.

Always choose the largest VM sizes to minimize cluster count.

Why wrong: Incorrect: Large VMs can be underutilized and cost more; right-sizing is better.

Use all-purpose clusters for all workloads to avoid startup delays.

Why wrong: Incorrect: All-purpose clusters are more expensive and designed for interactive use; job clusters are cheaper.

Use the serverless pricing tier to avoid paying for idle resources.

Why wrong: Incorrect: Azure Databricks does not have a serverless tier; you pay for allocated VMs.

Use a stored access policy to grant permissions to the directory.

Why wrong: A stored access policy is used to set permissions for a shared access signature (SAS), not to directly grant permissions to a directory. It does not provide granular directory-level permissions like ACLs.

Generate a shared access signature (SAS) with permissions scoped to the specific directory.

Why wrong: A SAS token is scoped to a storage account or container, not to subdirectories within a hierarchical namespace, so it cannot provide the required granular access.

Assign the Storage Blob Data Contributor role to the users at the storage account level.

Why wrong: The Storage Blob Data Contributor role is an RBAC role that, when assigned at the storage account level, grants permissions to the entire storage account or container, which is too broad for the requirement of restricting to a specific directory path.

Azure Policy

Why wrong: Azure Policy ensures compliance but does not directly protect data.

Azure Active Directory (Microsoft Entra ID) integration

Why wrong: Microsoft Entra ID integration is for authentication, not a security feature itself.

Azure Monitor alerts

Why wrong: Azure Monitor alerts notify about issues but do not protect data.

Azure Log Analytics

Why wrong: Azure Log Analytics is a tool within Azure Monitor for querying logs but is not a standalone service for monitoring data pipeline runs or setting up alerts. This is incorrect.

Azure Sentinel

Why wrong: Azure Sentinel is a security information and event management (SIEM) solution, not used for monitoring data pipeline runs. This is incorrect.

Azure Automation

Why wrong: Azure Automation is for automating processes and configurations, not for monitoring pipeline runs. This is incorrect.

It disables encryption using the customer-managed key.

Why wrong: It enables additional encryption, not disables it.

It enables encryption of data in transit between nodes.

Why wrong: Infrastructure encryption is for at-rest data, not in-transit.

It enables transparent data encryption for SQL pools.

Why wrong: Infrastructure encryption is a storage-level feature, not specific to SQL pools.

Enable Microsoft Defender for Cloud on the server.

Why wrong: Microsoft Defender for Cloud provides threat detection but not granular control over client tools.

Configure Azure Private Link for the dedicated SQL pool.

Why wrong: Azure Private Link only provides network-level isolation, not auditing or blocking.

Assign Azure RBAC roles to users for the SQL pool.

Why wrong: Azure RBAC controls access to resources but does not enforce client tool restrictions.

Round-robin distribution

Why wrong: Round-robin distributes rows evenly but randomly. Since the merge operation joins on the business key, rows with the same business key are likely on different distributions, causing significant data movement.

Hash distribution on the surrogate key

Why wrong: Hash distribution on the surrogate key (e.g., an identity column) places rows based on that key. The merge join uses the business key, so rows with the same business key are not co-located, leading to data movement.

Replicated distribution

Why wrong: Replicated distribution copies the entire table to each node. For 10 million rows, this is too large to replicate efficiently and does not reduce data movement during the merge because the source data still needs to be moved.

Sets the default blob service version of all storage accounts to '2020-10-02'

Why wrong: Incorrect. The policy denies non-compliant resources, it does not set the version.

Audits storage accounts to check if their default blob service version is set to a value other than '2020-10-02'

Why wrong: Incorrect. The policy uses the 'deny' effect, not 'audit'.

Denies the creation of storage accounts that have the default blob service version set to '2020-10-02'

Why wrong: Incorrect. The policy denies if the version is not set to '2020-10-02', not if it is set to that value.

Use Azure Policy to automatically rotate keys and update Databricks cluster configurations.

Why wrong: Azure Policy cannot automatically update Databricks configurations; key rotation would still cause disruption.

Use Azure Key Vault to store the storage account keys and configure a lifecycle policy to rotate them every 90 days. Update Databricks secrets accordingly.

Why wrong: Key rotation would require updating secrets and could cause job failures if not carefully coordinated.

Generate a SAS token with a 90-day expiry and update the Databricks secret before expiry.

Why wrong: SAS tokens still require manual rotation and could interrupt jobs if not updated in time.

Transparent Data Encryption (TDE) on the storage account.

Why wrong: TDE is for SQL databases, not Azure Storage.

Client-side encryption with keys stored in Azure Key Vault.

Why wrong: Client-side encryption is not default; it must be implemented by the client.

Azure Disk Encryption on the storage nodes.

Why wrong: Azure Disk Encryption is for virtual machine disks, not Azure Storage.

Create a private endpoint for the Stream Analytics job to access the storage account.

Why wrong: Creating a private endpoint is not required because the Stream Analytics job can use its managed identity to access the storage account through the firewall by being added as a trusted service. A private endpoint adds unnecessary complexity.

Disable the firewall on the storage account to allow all networks.

Why wrong: Disabling the firewall removes all network security and is not necessary. The correct approach is to add the Stream Analytics job's managed identity as a trusted service in the storage account firewall, which allows access while keeping the firewall enabled.

Generate a SAS token for the storage account and configure the job to use it.

Why wrong: SAS tokens are not needed when using managed identity.

Use Microsoft Entra ID Conditional Access policies to require multi-factor authentication for publishing.

Why wrong: Conditional Access controls authentication, not authorization to publish.

Create an Azure Policy to deny publishing if the user is not in an approved group.

Why wrong: Azure Policy does not integrate with Synapse publishing directly.

Configure branch permissions in the Git repository to restrict who can merge to the collaboration branch.

Why wrong: Git permissions control source control operations, not publishing to Synapse.

Azure Sentinel

Why wrong: Sentinel is for security incident detection.

Azure Dashboard

Why wrong: Dashboards display data but do not monitor.

Azure Service Health

Why wrong: Service Health monitors Azure platform health.

Run OPTIMIZE command in a scheduled job.

Why wrong: Running OPTIMIZE in a scheduled job is a manual approach and does not automatically compact files; auto-optimize does this automatically during writes.

Set retention duration for vacuum to 7 days.

Why wrong: Setting retention duration for vacuum controls how long old files are kept before deletion; it does not compact small files.

Enable Z-order indexing on the Delta table.

Why wrong: Enabling Z-order indexing improves data skipping and query performance, but it does not compact small files.