Design and implement data storage practice questions

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

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

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

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

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

Why it fails: 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.

Use a private endpoint and Microsoft Entra ID authentication, disable public access.

Why it fails: A private endpoint removes public network exposure and Microsoft Entra ID authentication authorises users, but neither logs data-plane reads nor blocks unauthorised Azure services by identity. This combination suits restricting network reachability, not the auditing and service-level authorisation the scenario demands.

Use managed identity for service access and disable public access.

Why it fails: Managed identity authenticates the service itself but grants no per-user authorisation or audit trail of individual data access, and disabling public access alone does not log reads. Managed identity suits service-to-service authentication where user-level auditing is not required.

Use Microsoft Entra ID authentication and SAS tokens for access, enabling diagnostic logs for auditing.

Why it fails: SAS tokens delegate time-limited access but cannot enforce per-user identity authorisation or prevent unauthorised Azure services reaching the account; diagnostic logs record requests only after access is granted. SAS suits granting scoped external or temporary access, not identity-based control of internal services.

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

Why it fails: 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 it fails: Can be in different subscriptions.

The copy job will skip the CSV file and stop.

Why it fails: It continues with other files.

The copy job will fail with an error.

Why it fails: skipIncompatibleFiles is true, so it skips.

The copy job will attempt to read the CSV file as Parquet and may produce corrupt data.

Why it fails: It skips incompatible files entirely.

Azure Automation

Why it fails: Automation can run scripts but is not the native lifecycle management solution.

Azure Data Factory

Why it fails: Data Factory is for data movement, not lifecycle management.

Azure Policy

Why it fails: Azure Policy is for governance, not data lifecycle.

Enable Event Hubs Capture to automatically write data to Data Lake Storage in Avro format.

Why it fails: Event Hubs Capture writes data in Avro format, not Parquet, so it does not meet the format requirement.

Use Azure Data Factory to copy data from Event Hubs to Data Lake Storage every 5 minutes.

Why it fails: Azure Data Factory copies data in batches (e.g., every 5 minutes), introducing latency that may not satisfy real-time requirements.

Azure Cosmos DB

Why it fails: Azure Cosmos DB is a NoSQL database and does not provide a native SQL interface over Blob Storage. It is designed for transactional workloads, not direct querying of blob data.

Azure SQL Database

Why it fails: Azure SQL Database is a relational database but cannot directly query data stored in Blob Storage without importing it. It is not designed for near real-time queries over external blob data.

Azure Data Lake Storage Gen2

Why it fails: Azure Data Lake Storage Gen2 is a storage service that provides scalable blob storage with hierarchical namespace, but it does not include a SQL query interface. A separate query service like Azure Synapse SQL Pool is required.

Premium tier

Why it fails: Incorrect. Premium tier is for high-performance workloads and is expensive.

Archive tier

Why it fails: Archive tier imposes rehydration latency of hours before data can be read, so raw data is not readily available for future processing. Archive suits long-term retention of rarely accessed compliance data, not an active landing zone for varied-schema ingestion.

Hot tier

Why it fails: Hot tier carries the highest per-gigabyte storage cost of the online tiers, so retaining all raw data there contradicts the cost-minimisation requirement. Hot suits frequently accessed active data, not a raw landing zone kept mainly for future processing.

Azure SQL Database

Why it fails: Incorrect. SQL Database can be expensive and may have scalability limits for such high velocity data.

Azure Blob Storage with Azure Event Hubs

Why it fails: Incorrect. This is a pipeline, not a storage solution; Blob Storage does not natively support real-time queries.

Azure Table Storage

Why it fails: Incorrect. Table Storage is limited in query capabilities and indexing.

Enable automatic tuning

Why it fails: Automatic tuning applies index and plan corrections to the existing OLTP database; it does not create a separate analytical engine, so complex queries still compete for the same compute. It is tempting because it improves query performance automatically, but that benefit applies to tuning the transactional workload, not isolating analytics.

Enable geo-replication

Why it fails: Geo-replication creates readable secondaries in other regions, but its purpose is regional resilience and disaster recovery, not workload isolation. It is tempting because secondaries accept read-only connections, yet routing complex analytics there still consumes replicated resources and adds cross-region latency rather than offloading within the primary region.

Create a failover group

Why it fails: Failover groups replicate databases to a secondary region for disaster recovery, providing no read-only analytical offload. They are tempting because they add a second endpoint, but that endpoint serves failover, not query isolation. The requirement is separating analytical workloads from OLTP transactions.

Always On availability groups

Why it fails: Incorrect. Always On is a high availability feature, not a security feature.

Firewall rules

Why it fails: Incorrect. Firewall rules are network security, but the question asks for security features of the database itself.

The lifecycle management rule only applies to blobs in the Hot tier.

Why it fails: Incorrect. Lifecycle rules apply to both Hot and Cool tiers.

The blob is in a container that is excluded from the lifecycle rule.

Why it fails: Possible, but not the most likely reason given the info.

The blob must be in the Hot tier for the rule to move it to Archive.

Why it fails: Incorrect. The rule can move from Cool to Archive.

Keep public network access enabled but restrict it to specific IP addresses. Use storage account keys for Azure Data Factory and Azure Databricks.

Why it fails: Using storage account keys is not least privilege and exposes keys. IP-based restriction is less secure than managed identity and still allows public access.

Disable public network access. Create private endpoints for the storage account and configure Azure Data Factory and Azure Databricks to use the private endpoints. Use RBAC to assign 'Storage Blob Data Contributor' to the managed identities.

Why it fails: Private endpoints add cost and complexity; they are not required since managed identity and firewall provide sufficient security.

Enable public network access with a firewall rule to allow only the Azure Data Factory and Azure Databricks IP ranges. Keep anonymous access enabled but set the containers to private.

Why it fails: Anonymous access should be fully disabled. Relying on IP ranges is not as secure as managed identity, and firewall rules can be bypassed.

The network connectivity between Synapse and the storage account is intermittent.

Why it fails: Intermittent network issues would likely cause timeouts or different errors, not a 'location does not exist' error.

The CSV file has an incorrect number of columns or contains a header row that mismatches the schema.

Why it fails: Schema mismatch would cause a different error, such as 'column ordinal' or 'data type conversion' error.

The storage account key used in the external data source has expired.

Why it fails: If the key were expired, all loads would fail, not just one file.

Modify the existing storage account to use customer-managed keys by updating the encryption settings in the Azure portal. Then, enable versioning and configure firewall rules to allow access from the VNet.

Why it fails: Existing storage accounts cannot be switched to customer-managed keys if they were not originally provisioned with infrastructure encryption. A new account is required.

Delete the existing storage account and recreate it with the same name, enabling customer-managed keys, versioning, and VNet access. Restore data from backups.

Why it fails: Deleting the account causes full data loss if backups are not available or current. Restoring from backups may not capture recent changes and causes significant downtime.

Create a new storage account with the same name in a different region. Enable infrastructure encryption and customer-managed keys. Use Azure Data Factory to copy data from the old account to the new one. Then, delete the old account and update applications to point to the new account.

Why it fails: Changing the region is unnecessary and adds latency. The account name can be reused after deletion, but this approach causes more downtime than necessary.

Azure SQL Database with PolyBase

Why it fails: PolyBase queries external data but uses T-SQL; not HiveQL.

Azure Databricks with Delta Lake

Why it fails: Delta Lake can read Parquet but Hive queries would require changes; not a direct Hive replacement.

Azure Synapse Analytics with external tables

Why it fails: Synapse uses T-SQL, not HiveQL; requires query modification.

Use Kerberos authentication for the storage account

Why it fails: ADLS Gen2 uses Microsoft Entra ID for authentication, not Kerberos.

Store all files in a single directory for simplicity

Why it fails: Using a hierarchical namespace with folders improves organization and performance.

Enable anonymous public access for ease of use

Why it fails: Public access should be disabled to ensure security.

BULK INSERT with a data source from Azure Blob Storage

Why it fails: BULK INSERT is not directly supported for Azure Blob Storage in Synapse; use PolyBase or COPY.

SQL Server Integration Services (SSIS) package

Why it fails: SSIS is not natively supported for Synapse; requires additional components.

CREATE PARTITION FUNCTION myDateRange (datetime2) AS RANGE LEFT FOR VALUES ('2023-01-01', '2023-02-01', ...)

Why it fails: RANGE LEFT includes the boundary value in the left partition, which can lead to uneven data distribution.

Use Azure SQL Database automatic partitioning feature

Why it fails: Azure SQL Database does not have automatic partitioning; you must create it manually.

Apply a clustered columnstore index with partitioning

Why it fails: Columnstore indexes are for analytics, not the primary partitioning mechanism.