Hard Difficulty Questions

Convert the parquet files to CSV format to reduce metadata overhead

Why wrong: CSV is less efficient than parquet for analytical queries.

Enable soft delete on the storage account to reduce read latency

Why wrong: Soft delete does not affect read performance.

Apply Z-order clustering on the parquet files using Azure Databricks

Why wrong: Z-order improves within-file skipping, but the issue is partition pruning.

Integration with Power BI for real-time dashboards

Why wrong: Both integrate with Power BI.

Maximum allowed latency for late-arriving data

Why wrong: Both handle late data with windowing.

All files in a single folder

Why wrong: No partitioning at all, causing full scans.

/yyyy-mm-dd/ (e.g., /2023-12-25/)

Why wrong: Single-level partitioning does not allow efficient pruning for yearly or monthly queries.

Files named by date (e.g., data_20231225.parquet)

Why wrong: Partition pruning requires folder hierarchy, not file names.

Use Always Encrypted in SQL Server and customer-managed keys in Blob Storage.

Why wrong: Always Encrypted encrypts columns but not the entire data flow.

Set up a VPN between on-premises and Azure, and use Azure Disk Encryption.

Why wrong: VPN encrypts the connection but not data at rest; Disk Encryption is for VMs.

Use HTTPS for the copy activity and enable Azure Storage Service Encryption.

Why wrong: HTTPS is not sufficient; TLS is required for in-transit encryption.

A VIEW that selects only the allowed columns

Why wrong: A VIEW is an alternative method but not required for column-level security.

A DENY statement on specific columns to users or roles

Why wrong: DENY is not required; absence of GRANT is enough to block access.

A row-level security policy must be in place

Why wrong: RLS is for rows, not columns.

The table contains many NULL values in indexed columns.

Why wrong: Both index types handle NULL values.

The table will be partitioned frequently.

Why wrong: Partitioning is independent of index type.

Assign the Storage Blob Data Reader role at the storage account level and use row-level security

Why wrong: RBAC at account level applies to all containers; row-level security is not for storage.

Generate a shared access signature (SAS) token for each user

Why wrong: SAS tokens are less manageable and require token distribution.

Configure a storage firewall to allow only the Data Reader role's IP addresses

Why wrong: Firewall controls network access, not user-level permissions.

Azure Policy to enforce tagging

Why wrong: Azure Policy is for governance and compliance, not a security layer.

Dynamic data masking for all databases

Why wrong: Dynamic data masking is a feature, not a defense-in-depth layer; it's part of access control.

The encryptedCredential property in the linked service

Why wrong: encryptedCredential stores encrypted credentials, not TLS settings.

The connectVia property in the linked service

Why wrong: connectVia specifies the integration runtime, not encryption.

The integrationRuntime property in the dataset

Why wrong: IntegrationRuntime specifies the runtime, not encryption settings.

Move the data to Azure Blob Storage cool tier.

Why wrong: Cool tier has higher read costs, increasing transaction costs.

Increase the Parquet file size to maximize block size.

Why wrong: While larger files reduce the number of transactions, it may not be practical to change file sizes arbitrarily.

Convert the container to Azure Files.

Why wrong: Azure Files is more expensive for read-heavy analytics and does not reduce transaction costs.

Move data to the cool tier to reduce storage costs.

Why wrong: Cool tier increases read costs and may not be cost-effective for analytics workloads.

Enable soft delete to protect against accidental deletion.

Why wrong: Soft delete does not improve performance or reduce costs.

Move frequently accessed data to Azure SQL Database.

Why wrong: Azure SQL Database is for transactional workloads, not for large-scale analytical queries.

Enable soft delete on the storage account to optimize read performance.

Why wrong: Soft delete is a data protection feature, not a performance optimization.

Migrate the data to Azure NetApp Files for lower latency.

Why wrong: NetApp Files is a file share service, not optimized for analytical queries on data lakes.

Configure the reference input with a static blob path

Why wrong: Static path does not trigger auto-refresh; you need a pattern to detect new blobs.

Use Azure Event Grid to trigger job restart on blob update

Why wrong: Restarting the job is not required; auto-refresh avoids restart.

Store the reference data in Azure SQL Database instead of Blob Storage

Why wrong: While SQL Database is an option, the question specifies Blob Storage; auto-refresh works with Blob Storage.

Ensure data is evenly distributed across distributions.

Why wrong: Data distribution is a design aspect, not a monitoring strategy.

Configure automatic index rebuild for columnstore indexes.

Why wrong: Index maintenance is an optimization action, not a monitoring strategy factor.

To assign RBAC roles to users for the storage account.

Why wrong: RBAC role assignment is done via 'Microsoft.Authorization/roleAssignments/write'.

To enable delegation of access to a specific blob.

Why wrong: Blob-level delegation requires 'Microsoft.Storage/storageAccounts/blobServices/containers/blobs' actions.

To allow users to set permissions on storage account containers.

Why wrong: Setting container permissions is done via different actions like 'container/update'.

Increase the batch interval to reduce load.

Why wrong: May reduce throughput, not reliability.

Increase the cluster size to handle spikes.

Why wrong: Does not address checkpoint corruption.

The integration runtime status is "Offline"

Why wrong: The status is Online.

Auto-update is disabled, preventing the IR from updating

Why wrong: Auto-update is enabled.

Self-contained interactive authoring is disabled, causing connectivity issues

Why wrong: Interactive authoring is enabled.

Create a stored procedure that checks the user's role and returns the appropriate columns.

Why wrong: Stored procedures cannot be used as predicate functions; they must be inline table-valued functions.

Grant the 'Manager' role SELECT permission on the security policy.

Why wrong: Permissions on the security policy are not needed; the policy automatically applies based on the predicate function.

Use GRANT SELECT ON OBJECT::[dbo].[Employee](Salary) TO [Manager] to grant access to the salary column.

Why wrong: Column-level permissions are not supported in dedicated SQL pools; use column-level security via a security policy.

Use storage account access keys (Shared Key) for authentication.

Why wrong: Shared Key does not provide per-user auditing and is less secure.

Generate shared access signatures (SAS) with a long expiry time.

Why wrong: Long-lived SAS tokens increase security risk.

Configure a firewall to allow access from all Azure services.

Why wrong: Allowing all Azure services may grant unintended access.

Use Azure IoT Hub for POS streaming and Azure Blob Storage for offline store uploads, then process with Stream Analytics and Data Factory

Why wrong: Two separate ingestion services increase management overhead and cost; IoT Hub is designed for device-to-cloud, not necessarily POS systems.

Use Azure Data Lake Storage for all data, then use Azure Databricks structured streaming for real-time and batch

Why wrong: ADLS is storage, not an ingestion service; Databricks structured streaming can read from ADLS but not directly ingest streaming data without a queue.

Use Azure Stream Analytics directly on POS data and store offline uploads in Blob Storage, then batch process with U-SQL

Why wrong: Stream Analytics cannot directly ingest from POS systems without a messaging layer; offline store uploads still need ingestion.