Design and implement data storage practice questions

/data/{date}/{source_system}/ (e.g., /data/2023-01-01/SourceA/)

Why wrong: Date-first may lead to many small partitions across sources.

/data/{source_system}/ with files named {timestamp}.csv/.json/.parquet

Why wrong: No date partitioning, leading to poor query performance.

/data/{source_system}/{year}/{month}/{day}/ (e.g., /data/SourceA/2023/01/01/)

Why wrong: It adds unnecessary folder depth for year/month/day. Azure Synapse Serverless SQL treats each folder level as a separate partition key, so a single date folder (e.g., /data/SourceA/2023-01-01/) is sufficient for efficient pruning on ingestion date. The extra hierarchy in D adds complexity without performance benefit and does not align with the recommended Hive-style partitioning for optimal query performance.

The managed identity of Azure Data Factory must be granted access to the private endpoint.

Why wrong: Not required; the private endpoint is approved by the resource owner.

The private endpoint must be in the same subscription as the Azure Data Factory.

Why wrong: Can be in different subscriptions.

Using INSERT INTO VALUES

Why wrong: INSERT INTO VALUES loads data row by row, which is extremely inefficient in a distributed MPP system and is not recommended for any significant data volume.

Using the COPY statement

Why wrong: 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

Why wrong: 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.

BULK INSERT

Why wrong: BULK INSERT is not supported in dedicated SQL pool.

Azure Data Factory Copy Activity

Why wrong: ADF Copy Activity is an orchestration tool, not a direct loading method.

Azure Import/Export service

Why wrong: Import/Export is for offline data transfer.

Azure Logic Apps

Why wrong: Logic Apps is for business process workflows.

Azure Databricks

Why wrong: Databricks is a compute platform, not an orchestrator.

Azure Functions

Why wrong: Functions is a compute service for event-driven code.

Use Azure SQL Managed Instance as a target

Why wrong: Azure SQL Managed Instance is a platform-as-a-service offering, but using it as a target does not inherently provide continuous replication during migration. To minimize downtime, you need a migration service that supports online migration, such as Azure Database Migration Service. Thus, option A is incorrect.

Use Azure Data Factory with a copy activity

Why wrong: A is wrong because Azure Data Factory is for batch data movement, not continuous replication.

Use Azure Synapse Link for SQL Server

Why wrong: Azure Synapse Link for SQL Server is designed for near-real-time analytics from SQL Server to Synapse, not for migrating databases to Azure SQL Database with minimal downtime. It does not serve as a migration tool. Therefore, option D is incorrect.

Enable double encryption for Azure Storage

Why wrong: Double encryption provides two layers of encryption but does not enable customer-managed keys; it uses Microsoft-managed keys for the second layer.

Enable infrastructure encryption

Why wrong: Infrastructure encryption encrypts data at the hardware level with platform-managed keys, not customer-managed keys.

Use Azure Customer Lockbox for access control

Why wrong: Customer Lockbox controls access during support requests and does not affect data encryption.

Azure Data Lake Storage Gen2

Why wrong: Azure Data Lake Storage Gen2 is designed for storing transformed, structured, or semi-structured data in a hierarchical namespace, not for ingesting raw, unmodified streaming payloads in real time. The scenario requires immediate capture of the original IoT data format without schema enforcement or transformation, which is a capability of Azure Event Hubs or Azure IoT Hub. It is tempting because Data Lake Storage Gen2 excels at large-scale analytics storage and would be the correct choice for the *transformed* data after processing, but it cannot serve as the initial ingestion point for raw streaming events.

Azure Stream Analytics

Why wrong: Azure Stream Analytics is a real-time analytics service that processes and transforms data in motion. It is used for processing data after ingestion, not for the initial raw data capture. Therefore, it is not the service for raw data ingestion.

Azure Data Factory

Why wrong: Azure Data Factory is a data integration service used for orchestrating and managing data pipelines, typically for batch processing and ETL. It is not designed for high-throughput real-time ingestion of streaming data.

Use Azure managed identities to access the storage account

Why wrong: Managed identities provide authentication for Azure services, not user-level authorization or auditing. They do not meet the requirement for controlling which users can access data or for auditing user actions.

Use shared access signatures (SAS) with stored access policies

Why wrong: Shared access signatures (SAS) with stored access policies grant time-limited access but do not provide per-user auditing or fine-grained RBAC. They are typically used for delegated access, not for user authorization and auditing.

Configure the storage account firewall to allow only Azure Databricks IP addresses

Why wrong: Storage account firewall restricts network access, but does not control which users can access data or provide audit logs of user operations. It is not sufficient for the stated requirements.

Share the storage account access key with the partner

Why wrong: Sharing the storage account access key grants full control over the storage account, including read, write, and delete permissions. This is a security risk because the key cannot be scoped to specific permissions or time-limited, making it unsuitable for sensitive data.

Configure a firewall to allow only the partner's IP address

Why wrong: A firewall restricting by IP address only controls network access; it does not authenticate the user or restrict data-plane operations. If the partner's IP changes or they use a shared network, unauthorized access may occur. Additionally, it doesn't provide write-only or time-limited permissions.

Enable anonymous public access to a container

Why wrong: Enabling anonymous public access allows anyone to read or write blobs without authentication, which is extremely insecure for sensitive financial data. It should never be used for partner uploads.

Frequent updates to the key value

Why wrong: Updates to shard key can cause performance overhead and partition splits.

Low cardinality of the key

Why wrong: Low cardinality leads to uneven distribution and hot partitions.

Premium

Why wrong: Optimized for high transaction rates, not cost-effective for infrequent access.

Cold

Why wrong: Cold tier is not a standard access tier in Azure Blob Storage.

Archive

Why wrong: Retrieval latency is hours, not milliseconds.

Assign Azure RBAC roles for each department on the storage account

Why wrong: RBAC roles control access at the storage account/container level, not rows.

Implement Azure Purview data policies for row-level security

Why wrong: Purview does not enforce row-level security.

Use Azure Data Lake Storage Gen2 access control lists (ACLs) at the file level

Why wrong: ACLs work at file/folder level, not row level.

Use heap index on the fact table

Why wrong: Heap indexes are not optimal for large fact tables in Azure Synapse Analytics; clustered columnstore indexes are recommended for columnstore compression and better query performance.

Change the fact table distribution to round-robin

Why wrong: Changing to round-robin distribution would distribute data randomly, increasing data movement during joins with the dimension table, thus degrading performance.

Change the fact table distribution to hash on CustomerID

Why wrong: The fact table is already hash-distributed on CustomerID. 'Changing' to the same distribution is not a valid action and does not improve performance.

The folderPath parameter is not being evaluated correctly

Why wrong: This is incorrect because the folderPath is dynamic and works correctly; the overwrite is due to a static fileName.

The linkedServiceName is not correctly configured

Why wrong: Incorrect because the linked service is properly configured; the issue is with the dataset's fileName property.

The format type is incorrect; it should be 'Parquet' instead of 'ParquetFormat'

Why wrong: Incorrect; the format type 'ParquetFormat' is acceptable and does not affect file naming. The overwrite is due to a static fileName.

Use Azure SQL Database with clustered columnstore index on date and device ID.

Why wrong: Azure SQL Database is a relational store, not a data lake, and cannot handle high-frequency ingest efficiently.

Use Azure Table Storage with PartitionKey set to date and RowKey set to device ID.

Why wrong: Azure Table Storage does not support hierarchical namespace and is not a data lake solution.

Use Azure Cosmos DB with partition key on (date, device ID) and TTL for data retention.

Why wrong: Cosmos DB is not a hierarchical namespace data lake; it's a NoSQL database.

Use Azure SQL Database Change Tracking and push changes to Azure Event Hubs, then use Azure Stream Analytics to write to Synapse.

Why wrong: Change Tracking does not directly push to Event Hubs; additional complexity and latency.

Use Azure Synapse PolyBase to directly query the source SQL database every 5 minutes.

Why wrong: PolyBase is for batch queries, not near-real-time replication.

Schedule a full copy of the entire table every 5 minutes using Azure Data Factory.

Why wrong: Full copy of 50M rows every 5 minutes would be resource-intensive and may not complete within 5 minutes.

Store the logs in Azure SQL Database as a table.

Why wrong: SQL Database is expensive for large volumes of rarely queried data.

Store the logs in Azure Files share.

Why wrong: Azure Files is more expensive and designed for file shares, not log archiving.

Store the logs in Azure Cosmos DB with a JSON container.

Why wrong: Cosmos DB is expensive for infrequent access and large retention.

Use a private endpoint and Azure AD authentication, disable public access.

Why wrong: Private endpoint secures network but doesn't provide fine-grained access control.

Use managed identity for service access and disable public access.

Why wrong: Doesn't restrict unauthorized Azure services unless combined with firewall.

Use Azure AD authentication and SAS tokens for access, enabling diagnostic logs for auditing.

Why wrong: SAS tokens are not fully auditable for individual user actions.

Azure Analysis Services

Why wrong: Analysis Services is an analytical engine, not a data store for copy.

Azure Cognitive Search

Why wrong: Cognitive Search is a search service, not a source.

Azure Purview

Why wrong: Purview is a data governance service.