Describe an analytics workload on Azure practice questions

Azure Stream Analytics

Why wrong: Incorrect. Azure Stream Analytics is designed for real-time stream processing only. It can output to sinks like Data Lake, but cannot directly query historical batch data or combine both in a single query for dashboards.

Azure Data Factory

Why wrong: Azure Data Factory is a cloud data integration and orchestration service, not a query engine. While it can ingest GPS event streams from Event Hubs into Azure Data Lake Storage and orchestrate transformation pipelines, it has no T-SQL or SQL query endpoint to interactively analyze the combined hot/cold data. Therefore, it cannot directly serve or power the near real-time dashboard backed by SQL queries, which is the core requirement in this scenario.

Azure Databricks

Why wrong: Incorrect. Azure Databricks is a powerful analytics platform based on Apache Spark, but its primary query language is Spark SQL (or Python/Scala), not T-SQL. It also requires more manual setup for streaming and batch integration compared to Synapse.

Azure Analysis Services

Why wrong: Incorrect: Azure Analysis Services provides a semantic layer but does not support direct T-SQL queries on raw data and is not serverless.

Azure Databricks

Why wrong: Incorrect: Azure Databricks uses Spark SQL, not T-SQL, and while it can be serverless, it does not natively support T-SQL syntax.

Azure SQL Database (creating a secondary replica)

Why wrong: Incorrect: Creating a secondary replica of Azure SQL Database does not provide serverless compute and shares the underlying provisioning; it also does not independently scale compute.

Azure Data Factory

Why wrong: Data Factory is for orchestration and data movement, not for interactive SQL queries.

Azure Synapse Dedicated SQL pool

Why wrong: Dedicated SQL pool requires provisioning compute resources and is more expensive for infrequent ad-hoc queries.

Azure Databricks

Why wrong: Databricks uses Spark SQL and requires a cluster (though auto-scaling), but the cost model is per compute, not per data scanned; also it is not purely SQL.

Select Restore, then navigate to database, then choose Point-in-time, then click OK.

Why wrong: Selecting Restore before navigating to the database is invalid because the Restore button is context-sensitive and only appears on the database's own overview page, not on a global menu. Without first opening the specific database, you have no resource context to initiate a restore, so the action cannot be performed. Even if you later navigate to the database, the order is broken because the restore wizard would not have been launched from that database's blade, and the point-in-time configuration would not be properly tied to the correct logical server.

Navigate to database, choose Point-in-time, then select Restore, then click OK.

Why wrong: Choosing Point-in-time before selecting Restore is logically impossible because the Point-in-time option is a choice presented within the Restore dialog, not a standalone command on the database blade. The restore workflow requires you to first invoke Restore, which then presents source types (e.g., Point-in-time, Long-term backup, Deleted database) and only after that selection do you get the time and configuration fields. Reversing this order means you would have nowhere to enter the restore time or choose a target, so the restore cannot be configured or executed.

Navigate to database, select Restore, click OK, then choose Point-in-time.

Why wrong: Clicking OK before configuring the point-in-time settings is dangerous because the restore wizard would attempt to use default values, which are not appropriate for a point-in-time restore. The dialog remains incomplete, so the restore would likely fail validation or, at best, restore to an unintended default time, not the specific point you intended. You must first select the Point-in-time source, specify the timestamp and destination, and only then click OK to confirm and start the restore operation.

Ordered clustered columnstore index

Why wrong: An ordered clustered columnstore index improves compression and ordered scans but does not reduce data movement across distributions.

Clustered columnstore index

Why wrong: A regular clustered columnstore index provides columnar storage benefits but has no effect on cross-distribution data shuffling.

Round-robin distributed table

Why wrong: Round-robin distribution spreads rows evenly without considering join keys, causing maximum data movement during joins.

Bronze layer

Why wrong: Bronze layer stores raw ingested data, not optimized for analytics.

Silver layer

Why wrong: Silver layer is for cleaned and validated data, but not yet aggregated for reporting.

Lakehouse layer

Why wrong: Lakehouse is an architecture pattern, not a layer within medallion.

Batch input (e.g., Azure Blob Storage)

Why wrong: Azure Blob Storage stores unstructured batch files, not continuous event streams, so it cannot serve as a real-time input that pushes data to Stream Analytics. While Stream Analytics can reference Blob Storage for static reference data or read batch files, doing so requires a manual trigger or scheduler and introduces latency that violates the real-time requirement. A batch input is therefore neither a component of a real-time architecture nor a substitute for a streaming source.

Machine learning model

Why wrong: Machine learning models are optional enhancements that can be embedded in Stream Analytics as user-defined functions to score streaming data, but they are not a prerequisite for real-time processing. A basic real-time solution can simply filter, aggregate, or route data using a standard SQL-like query without any ML inference. Since the question asks for the three components that are required, including ML would misunderstand the scope of a minimal streaming pipeline.

Change partitioning to daily partitions.

Why wrong: More partitions can increase overhead and may not improve performance.

Convert the table to a clustered rowstore index.

Why wrong: Rowstore is not optimized for analytical workloads; columnstore is better.

Increase the data warehouse performance level (DWU).

Why wrong: Increasing DWU adds resources but does not fix index fragmentation.

Output to Azure Data Explorer and then use a Power BI dashboard.

Why wrong: Incorrect. Outputting to Azure Data Explorer and then to Power BI does not provide exactly-once semantics directly from Stream Analytics, and adds complexity without guaranteed exactly-once delivery.

Output to Azure Event Hubs and then use Azure Functions to write to Power BI.

Why wrong: Incorrect. While Event Hubs and Azure Functions can be used to build an exactly-once pipeline, it is not a direct output configuration of Stream Analytics for Power BI, and it introduces additional components and complexity.

Configure the Power BI output with 'Exactly once' delivery mode.

Why wrong: Incorrect. Azure Stream Analytics does not support exactly-once delivery mode for Power BI output; the only available mode is at-least-once.

Azure SQL Database

Why wrong: Azure SQL Database is an as-a-service relational database engine optimized for online transaction processing (OLTP), not for interactive analytical queries over large-scale data warehouses or data lakes. Its provisioned compute and storage limits make it expensive and inefficient for scanning billions of rows, and it lacks native integration with file formats like Parquet for ad-hoc serverless analysis. Therefore, while you can run some queries, it is not designed as an interactive analytics service.

Azure Data Factory

Why wrong: Azure Data Factory is a cloud-based data integration and orchestration service used to create pipelines that ingest, transform, and move data between more than 100 supported sources and destinations. It does not host a query engine or interactive workspace; instead, it triggers activities that can invoke analytics services like Synapse or Databricks. Since it does not execute analytical queries itself, it cannot itself be used to perform interactive analytics.

Azure Data Lake Storage Gen2

Why wrong: Azure Data Lake Storage Gen2 is a highly scalable storage service that combines a hierarchical namespace with low-cost capacity for structured and unstructured big data. Although it is commonly used as the data repository for analytics solutions, it does not provide a compute or query engine, nor does it expose an interactive notebook or SQL endpoint. It is an inert data store, so it cannot perform interactive analytics without an external processing service.

The total number of tornado events across all states.

Why wrong: The query uses summarize count() by State, so the aggregation produces one row per state, not a single grand total. There is no summarize without a grouping key, no sum() wrapping the per-state counts, and no rollup or union to collapse the rows into a national figure. Therefore the output is a per-state distribution, not the total number of tornado events across all states.

The top 5 years with the most tornadoes.

Why wrong: The aggregate key in the summarize operator is State, a geographic attribute, not a time-based dimension. Although the source data likely includes a timestamp column, the query never extracts the year with functions like datetime_part() or format_datetime(), nor does it group by any time bucket. Because the ranking is based on state rather than time, the result cannot be the top five tornado years.

The number of events by type.

Why wrong: The query applies a preliminary filter that restricts the data to only tornado events, typically with something like where EventType == 'Tornado', before any aggregation. Since the grouping key is State, not EventType, the summarize step never creates counts per distinct event category. The output therefore only covers a single event type and cannot provide a breakdown of event counts by type.

Azure Data Factory with mapping data flows.

Why wrong: Azure Data Factory (ADF) is an integration and ETL service, not an interactive query engine. Mapping data flows let you design visual transformations, but they must run inside pipelines and are meant for scheduled, repeatable data movement. While the underlying Spark compute is serverless, you still have to create linked services, datasets, and pipeline triggers rather than issuing ad-hoc transformations on demand.

Azure HDInsight with Apache Spark clusters.

Why wrong: HDInsight gives you full control over Apache Spark clusters, but that means you must provision and manage the cluster lifecycle, including choosing VM sizes, configuring autoscaling, and handling upgrades. The cluster keeps running and accruing cost even when no queries are active, so it is not a serverless on-demand option. This operational overhead makes it less appropriate than a fully managed serverless service for occasional, ad-hoc transformations on data lake storage.

Azure Synapse Analytics with serverless SQL pool.

Why wrong: Azure Synapse serverless SQL pool is genuinely serverless for querying files in the data lake, but it only supports T-SQL-based relational transformations. You can create external tables, views, and run SQL queries, but more complex, code-heavy transformations using Python, Scala, or advanced custom logic are not available. That SQL-only constraint limits its usefulness for open-ended raw data transformations compared with a platform that combines serverless compute with a broader transformation toolkit.

Use the Copy Activity with automatic partitioning.

Why wrong: Incorrect. Automatic partitioning improves parallelism for source data like partitioned tables, but it is not a specific WAN optimization technique.

Enable parallel copy within the Copy Activity.

Why wrong: Incorrect. Parallel copy increases throughput by copying data in chunks concurrently, but it does not inherently address latency or bandwidth constraints of a WAN link.

Use staging with Azure Blob Storage and PolyBase.

Why wrong: Incorrect. Staging with PolyBase is effective for bulk loading into Azure Synapse Analytics, but the destination is Azure Data Lake Storage Gen2, which does not use PolyBase. The staging concept still applies, but PolyBase is not relevant here.

Azure Data Factory

Why wrong: Azure Data Factory is a data integration and orchestration service that moves and transforms data on a schedule, typically in batch mode using pipelines and control flow. It does not offer low-latency event ingestion or sub-second stream processing, so it cannot serve as the engine for a real-time dashboard.

Azure Data Lake Storage Gen2

Why wrong: Azure Data Lake Storage Gen2 provides hierarchical, petabyte-scale object storage for analytical data, but it is purely a storage service with no compute capabilities to process or query live streams. Real-time architectures may land streaming data into it, but the lake itself does not generate or refresh dashboard visuals without an additional processing service.

Azure HDInsight with Spark

Why wrong: Azure HDInsight with Spark can run Spark Structured Streaming, but its micro-batch model introduces seconds of latency and requires cluster management, making it less suitable for true real-time dashboards. While it can process streaming data, it is optimized for batch analytics and lacks the built-in, latency-sensitive dashboard integration found in a dedicated stream processing service.

Azure Databricks with Apache Spark

Why wrong: Azure Databricks with Apache Spark is a unified analytics platform that can indeed perform complex data transformations, but it is not the service type the question targets because it normally requires provisioning and managing Spark clusters. While Azure offers serverless options for Databricks, the default model involves cluster lifecycle management, unlike the two correct serverless services. Therefore, it is not one of the two correct answers in this serverless transformation scenario.

Azure SQL Database

Why wrong: Azure SQL Database is a fully managed relational database service used primarily for transactional and query workloads, not for serverless data transformation. It requires a provisioned logical server and compute tier, and although you can run T-SQL transforms, it is not a serverless transformation service for data lakes or pipelines. For that reason, it is not one of the correct choices.

Azure HDInsight with Hive

Why wrong: Azure HDInsight with Hive is an open-source analytics service that can transform data, but it requires you to provision and configure a cluster and handle its scaling and patching. Hive queries run against managed clusters, which introduces infrastructure overhead and does not align with the serverless compute model of the correct answers. Thus, it is not a correct answer.

Read Scale-out replicas

Why wrong: Provides read-only replicas for query offloading, not scaling compute capacity.

Elastic Database Pools

Why wrong: Used in Azure SQL Database for pooling resources, not for Synapse Analytics.

Manual scale-up during peak hours

Why wrong: Requires manual intervention, not automatic scaling.

Power BI

Why wrong: Power BI is a business analytics service for visualizing and reporting on data, not an ingestion service. It can connect to data sources and import data into its own in-memory model, but that is for analysis, not for moving data into a data lake or warehouse. It lacks the orchestration, scheduling, and large-scale data movement capabilities of dedicated ingestion tools.

Azure SQL Database

Why wrong: Azure SQL Database is a managed relational database service that can act as a source or destination for data, but it is not an ingestion tool. Ingesting data into Azure Data Lake Storage from Azure SQL Database would require a service like Azure Data Factory, which can connect to the database and copy data. Merely having a database does not perform ingestion.

Azure DevOps

Why wrong: Azure DevOps is a suite of tools for software development lifecycle management, including CI/CD pipelines, repos, and boards. It is not designed for data ingestion into Azure storage; its pipelines might execute scripts that use tools like AzCopy, but DevOps itself does not move data. Using it for ingestion would be an indirect and unconventional approach, not a native ingestion method.