An enterprise is designing a data analytics and integration platform on Azure to process a… — Microsoft Azure Solutions Architect (AZ-305)

An enterprise is designing a data analytics and integration platform on Azure to process and analyze financial transaction records. The platform must meet the following requirements:

- Historical Data: Store $150\text{ TB}$ of structured historical data in Parquet format. The storage solution must remain resilient against a regional datacenter outage.
- Daily Ingestion: Process $2\text{ TB}$ of new transaction records appended nightly. The batch integration pipeline runs for $3$ hours and requires support for complex transactional upsert operations.
- Ad-hoc Analytics: Allow data analysts to run occasional, unpredictable ad-hoc queries throughout the day. Each query typically scans up to $10\text{ TB}$ of data.
- Interactive Reporting: Serve a critical executive dashboard accessed concurrently by up to $100$ users between 09:00 and 11:00 daily. Dashboard queries require a guaranteed response latency of under $5$ seconds against a pre-aggregated $50\text{ GB}$ dataset.
- Cost Optimization: Minimize operational and compute costs, avoiding charges for idle resources when no queries are active.

Which architectural solution should you recommend?

Configure Azure Data Lake Storage Gen2 (ADLS Gen2) with Geo-Redundant Storage (GRS). Use Azure Synapse Spark pools to execute the nightly ETL pipeline and write upserts in Delta format. For ad-hoc analytics, use an Azure Synapse Serverless SQL pool. Load the pre-aggregated dashboard dataset into an Azure SQL Database Serverless tier.Answer
B
Configure ADLS Gen2 with GRS. Provision an Azure Synapse Dedicated SQL Pool to execute the nightly ETL pipeline, store the pre-aggregated dashboard dataset, and run the ad-hoc queries directly.
C
Configure ADLS Gen2 with Locally Redundant Storage (LRS) to minimize storage costs. Use Azure Synapse Spark pools for nightly ETL, an Azure Synapse Serverless SQL pool for ad-hoc queries, and load the pre-aggregated dashboard dataset into an Azure SQL Database Serverless tier.
D
Configure ADLS Gen2 with GRS, accessing files via Shared Access Signature (SAS) tokens with a $2$ -year expiration window hardcoded in the application. Use Azure Synapse Spark pools for nightly ETL, and use an Azure Synapse Serverless SQL pool for both the ad-hoc queries and the executive dashboard.

Answer

Configure Azure Data Lake Storage Gen2 (ADLS Gen2) with Geo-Redundant Storage (GRS). Use Azure Synapse Spark pools to execute the nightly ETL pipeline and write upserts in Delta format. For ad-hoc analytics, use an Azure Synapse Serverless SQL pool. Load the pre-aggregated dashboard dataset into an Azure SQL Database Serverless tier.

The correct solution uses Azure Data Lake Storage Gen2 with GRS to satisfy the regional resilience requirement. Azure Synapse Spark pools run only when active during the nightly

3

-hour ETL window to perform transactional updates (upserts) in Delta format. For ad-hoc queries, a Serverless SQL pool charges only per TB scanned, which is ideal for occasional use. For the high-concurrency dashboard, Azure SQL Database Serverless provides sub-second query latency and automatically pauses during off-peak hours, ensuring cost optimization.

Step-by-Step Solution

Select the storage redundancy model based on resilience requirements.

Azure Data Lake Storage Gen2 (ADLS Gen2) configured with Geo-Redundant Storage (GRS).

GRS replicates data asynchronously to a secondary region, protecting against regional datacenter outages, whereas LRS only replicates within a single datacenter.

Select the query engine for occasional, large ad-hoc queries.

Azure Synapse Serverless SQL pool.

Since ad-hoc queries run occasionally and scan up to

10\text{ TB}

each, a Serverless SQL pool is highly cost-effective because it charges only per data scanned ($5 per TB) and incurs zero costs when idle.

Select the database engine to serve high-concurrency dashboard queries.

Azure SQL Database Serverless.

Azure SQL Database handles high-concurrency workloads for small datasets (

50\text{ GB}

) efficiently. The Serverless tier scales compute dynamically during peak hours (09:00 to 11:00) and autopauses during inactive hours to minimize costs.

Select the processing engine for nightly transactional batch ETL.

Azure Synapse Spark pools using Delta format.

Spark pools handle large scale ETL (

2\text{ TB}

daily) efficiently, support Delta Lake for ACID transaction support and upserts, and automatically pause when the

3

-hour pipeline completes.

Key Concept

Designing a modern data warehouse architecture that separates storage, batch processing, interactive serving, and ad-hoc query capabilities using serverless and auto-scaling components to optimize cost and performance.

Question

Answer

Step-by-Step Solution

Key Concept

Common Mistakes

Topics

More questions from this topic