Courseiva

Microsoft Azure Data Fundamentals DP-900 (DP-900) — Questions 76150

820 questions total · 11pages · All types, answers revealed

Page 1

Page 2 of 11

Page 3
76
MCQhard

Refer to the exhibit. A data engineer wants to ensure that all Azure Storage accounts used for analytics use customer-managed keys. They apply this Azure Policy. What is the outcome?

A.The policy audits existing storage accounts for compliance
B.The policy allows all storage accounts whether they use customer-managed keys or not
C.The policy only applies to storage accounts with hierarchical namespace enabled
D.The policy denies creation of StorageV2 accounts that use Microsoft-managed keys
AnswerD

The policy denies StorageV2 account creation when the encryption key source is not Microsoft.Keyvault. For StorageV2 accounts using Microsoft-managed keys, the keySource field is set to Microsoft.Storage, which is not equal to Microsoft.Keyvault, so the condition is satisfied and the deny effect blocks the deployment. The result is that a StorageV2 account must use customer-managed keys with Azure Key Vault to be created.

Why this answer

The Azure Policy in the exhibit uses the 'Deny' effect on storage accounts that do not use customer-managed keys (i.e., that use Microsoft-managed keys). This means any attempt to create or update a storage account without customer-managed encryption will be blocked. Option D correctly identifies that the policy denies creation of StorageV2 accounts (or any storage account type covered by the policy scope) that use Microsoft-managed keys.

Exam trap

The trap here is that candidates confuse the 'Deny' effect with 'Audit' or 'Append', and mistakenly think the policy only monitors or tags accounts instead of actively blocking non-compliant ones.

How to eliminate wrong answers

Option A is wrong because the policy uses the 'Deny' effect, not 'Audit'; an audit effect would only log compliance without blocking creation. Option B is wrong because the policy explicitly denies storage accounts that do not use customer-managed keys, so it does not allow all accounts regardless of key type. Option C is wrong because the policy does not filter by hierarchical namespace; it applies to all storage accounts in the scope, regardless of whether hierarchical namespace is enabled.

77
MCQmedium

A retail chain collects daily sales data from hundreds of stores. The data is stored as CSV files in Azure Data Lake Storage Gen2. The analytics team needs to run complex SQL queries that join sales data with product dimensions and aggregate results across petabytes of data. Queries must return results within seconds. Which Azure service is best suited for this analytical workload?

A.Azure Synapse Analytics
B.Azure SQL Database
C.Azure Analysis Services
D.Azure HDInsight
AnswerA

Azure Synapse Analytics is correct because its massively parallel processing (MPP) architecture splits a single query across many compute nodes, each scanning a different partition of the data simultaneously, so petabyte-scale joins and aggregations can finish in seconds rather than minutes. It can read CSV files directly from Azure Data Lake Storage Gen2 using PolyBase external tables or CREATE EXTERNAL TABLE AS SELECT (CETAS), which means no pre-loading into a traditional relational store is needed. Features like row-level security, workload management, and result-set caching further make it a true cloud-scale analytical SQL engine.

Why this answer

Azure Synapse Analytics (formerly SQL Data Warehouse) is the correct choice because it is a distributed query engine designed for petabyte-scale data warehousing. It uses Massively Parallel Processing (MPP) to distribute data across compute nodes, enabling complex SQL joins and aggregations on data stored in Azure Data Lake Storage Gen2 to return results in seconds via its SQL pool or serverless SQL endpoint.

Exam trap

The trap here is that candidates often confuse Azure Synapse Analytics with Azure SQL Database, assuming both can handle large analytical queries, but Azure SQL Database lacks the MPP architecture and external table support needed for petabyte-scale data lake queries.

Why the other options are wrong

B

Azure SQL Database is an OLTP system optimized for transactional workloads, not for petabyte-scale analytical queries requiring complex aggregations across large datasets. It lacks the distributed query engine and massive parallel processing (MPP) architecture needed for sub-second responses on petabytes of data.

C

Azure Analysis Services is an OLAP engine for semantic models and fast querying of pre-aggregated data, but it does not directly query petabytes of raw CSV data in Data Lake Storage Gen2. It requires data to be processed and loaded into a model, which is not suitable for ad-hoc complex SQL joins on raw data at that scale.

D

Azure HDInsight is a managed Apache Hadoop/Spark service, but it is not optimized for sub-second SQL query performance on petabyte-scale data; it typically requires longer query times and is better suited for batch processing or ETL workloads.

When would these options actually be correct?

B

A question where a retail chain needs to run real-time inventory queries with low latency (e.g., checking stock levels per store) and supports high-concurrency transactions, with data under a few terabytes. The workload is OLTP, not analytical.

C

A question where the requirement is to create a semantic data model for business users to perform interactive analysis and reporting on pre-processed data, with fast query performance on aggregated data, and the data volume is moderate (e.g., terabytes) rather than petabytes.

D

A question requiring large-scale batch processing of unstructured data using custom MapReduce or Spark jobs, where interactive SQL performance is not critical, would make HDInsight the correct answer.

Why candidates pick the wrong answer

B

Candidates may assume that because SQL Database supports SQL queries and can handle large datasets, it is suitable for analytics. They overlook the fundamental architectural difference between OLTP (SQL Database) and MPP/analytical systems (Synapse).

C

Candidates may confuse Analysis Services with a data warehouse solution because it supports fast query responses and is used for analytical workloads, overlooking that it is not designed for direct querying of raw data at petabyte scale.

D

Candidates may confuse HDInsight's big data capabilities with the need for fast SQL analytics, overlooking that Synapse Analytics provides a dedicated SQL engine for interactive queries.

78
Multi-Selecthard

Which THREE of the following are features of Azure Data Lake Storage Gen2?

Select 3 answers
A.Integration with Azure Active Directory (Microsoft Entra ID)
B.Geo-redundant storage (GRS)
C.POSIX-compliant access control lists (ACLs)
D.Atomic rename of directories
E.Fixed-size block storage
AnswersA, C, D

Integration with Azure Active Directory (Microsoft Entra ID) is a hallmark of Azure Data Lake Storage Gen2. It uses Entra ID for OAuth 2.0 authentication, allowing users, service principals, and managed identities to be verified before accessing the storage. Authorization is then layered via Role-Based Access Control (RBAC) at the account/container scope plus POSIX ACLs at the file/directory level, giving a unified identity control plane. This is a native feature of the Data Lake Storage Gen2 account model, not an optional extra.

Why this answer

Azure Data Lake Storage Gen2 (ADLS Gen2) combines Blob Storage with a hierarchical namespace, enabling high-performance analytics. Option A is correct: it integrates with Azure Active Directory (Microsoft Entra ID) for fine-grained access control. Option C is correct: it supports POSIX-compliant access control lists (ACLs) for Unix-like permissions.

Option D is correct: it allows atomic rename of directories, which is efficient for big data workloads. Option B is wrong: Geo-redundant storage (GRS) is a replication option available for storage accounts, but it is not a feature specific to ADLS Gen2. Option E is wrong: fixed-size block storage is a characteristic of Azure Blob Storage, not ADLS Gen2, which uses a hierarchical namespace with variable-size files.

79
MCQmedium

Your company stores IoT sensor data in Azure Blob Storage. Data analysts need to query the data using SQL without moving it. Which Azure service should you use?

A.Azure Stream Analytics
B.Azure Data Lake Storage
C.Azure Synapse Serverless SQL
D.Azure SQL Database
AnswerC

Azure Synapse Serverless SQL is the correct service because it provides a distributed T-SQL query engine that reads files directly from Azure Blob Storage or ADLS Gen2 using OPENROWSET or external tables, without requiring any data movement or loading. It can issue ad-hoc queries over Parquet, CSV, and JSON files, and it charges based on the amount of data processed, making it ideal for exploring stored IoT sensor data with SQL compatibility.

Why this answer

Azure Synapse Serverless SQL is the correct choice because it provides a SQL-based query engine that can directly query data stored in Azure Blob Storage using T-SQL, without requiring data movement or a dedicated data warehouse. It uses a pay-per-query model and supports reading various file formats like Parquet, CSV, and JSON, making it ideal for ad-hoc analytical queries on IoT sensor data.

Exam trap

The trap here is that candidates often confuse Azure Synapse Serverless SQL with Azure SQL Database, mistakenly thinking any 'SQL' service can query external storage, but Azure SQL Database requires data to be loaded into its own tables, while Serverless SQL queries data in place.

How to eliminate wrong answers

Option A is wrong because Azure Stream Analytics is a real-time stream processing service designed for analyzing data in motion (e.g., from IoT Hub or Event Hubs), not for querying static data already stored in Blob Storage using SQL. Option B is wrong because Azure Data Lake Storage is a storage service (built on Blob Storage) that provides a hierarchical namespace and POSIX-like access control; it does not include a built-in SQL query engine. Option D is wrong because Azure SQL Database is a fully managed relational database service that requires data to be imported and stored in its own tables, not for querying data directly in external Blob Storage without movement.

80
MCQmedium

A company uses Azure SQL Database and needs to implement data masking for sensitive columns like email addresses and credit card numbers, so that only authorized users can see the actual data. Which feature should they configure?

A.Row-Level Security
B.Dynamic Data Masking
C.Auditing
D.Transparent Data Encryption (TDE)
AnswerB

Dynamic Data Masking (DDM) in Azure SQL Database is the correct choice because it applies masking functions to designated sensitive columns at query time, so unauthorized users see obfuscated values (for example, xxxx-xxxx-xxxx-1234) while the underlying stored data remains unchanged. DDM does not alter the database physically; instead, it transforms the result set in place, and users with the ALTER ANY MASK permission can still query the unmasked values. This precisely matches the requirement to hide sensitive data from query results while leaving the data intact.

Why this answer

Dynamic Data Masking (DDM) is the correct feature because it limits exposure of sensitive data by obfuscating columns (e.g., email addresses, credit card numbers) in query results to non-privileged users, while authorized users (with EXEMPT or UNMASK permission) see the actual data. This directly meets the requirement of masking sensitive columns without altering the underlying stored data.

Exam trap

The trap here is that candidates often confuse Dynamic Data Masking (which hides data in query results) with Transparent Data Encryption (which protects data at rest), leading them to select TDE when the requirement is about controlling visibility to specific users.

How to eliminate wrong answers

Option A is wrong because Row-Level Security (RLS) controls access to rows based on user identity or context (e.g., a user can only see their own orders), not by masking column values. Option C is wrong because Auditing logs database events (e.g., SELECT, INSERT) for compliance and monitoring, but does not modify query results to hide sensitive data. Option D is wrong because Transparent Data Encryption (TDE) encrypts the database at rest (on disk) and in backups, but does not control visibility of data in query results to authorized vs. unauthorized users.

81
MCQmedium

A development team is designing an application that stores user session data in Azure Cosmos DB. Each session document contains a sessionId (unique), userId, timestamp, and a JSON field 'metadata' that can include various optional properties. The application frequently queries by userId to retrieve all sessions for a particular user. Which property should be chosen as the partition key to optimize query performance and ensure even data distribution?

A.sessionId
B.userId
C.timestamp
D.metadata
AnswerB

userId is the correct partition key because it is the field used in the most common query filter—retrieving a specific user's sessions. All documents for one user share the same logical partition, so an equality filter on userId is routed directly to a single physical partition, producing a fast, low-RU point read. With many distinct users, the workload spreads evenly while keeping each user's related data co-located.

Why this answer

The partition key should be the property most frequently used in queries and that provides high cardinality for even distribution. Since the application frequently queries by userId to retrieve all sessions for a user, choosing userId as the partition key ensures that all session documents for a given user are stored in the same logical partition, making these queries efficient and fast. Additionally, userId typically has a large number of distinct values, which promotes even data distribution across physical partitions.

Exam trap

The trap here is that candidates often choose sessionId because it is unique, not realizing that a high-cardinality key that is not used in queries leads to inefficient cross-partition queries, while a key like userId balances query efficiency with distribution.

How to eliminate wrong answers

Option A is wrong because sessionId is unique per document, which would cause each query by userId to fan out across all partitions, resulting in cross-partition queries that are slower and more expensive. Option C is wrong because timestamp often has low cardinality (many documents share the same timestamp) and can lead to hot partitions, especially if many sessions are created simultaneously, causing uneven data distribution and throttling. Option D is wrong because metadata is a JSON field with optional, unpredictable properties; using it as a partition key can lead to skewed distribution and poor query performance, as the partition key value may be missing or vary inconsistently.

82
Multi-Selecteasy

Which TWO data storage types are classified as structured data in Azure? (Choose two.)

Select 2 answers
A.Azure Cosmos DB
B.Azure Data Lake Storage
C.Azure SQL Managed Instance
D.Azure SQL Database
E.Azure Blob Storage
AnswersC, D

Stores structured relational data with a fixed schema.

Why this answer

Azure SQL Managed Instance is a fully managed SQL Server database engine in Azure, which stores data in a relational schema with predefined tables, columns, and data types. This structured format enforces a rigid schema, making it a classic example of structured data storage in Azure.

Exam trap

The trap here is that candidates often confuse NoSQL databases like Azure Cosmos DB as structured because they support indexing and querying, but structured data specifically requires a fixed relational schema enforced by the database engine, which Cosmos DB does not mandate.

83
MCQmedium

A company uses Azure SQL Database for a reporting application. The database is mostly idle during weekdays but experiences heavy load on weekends when reports are generated. They want to minimize costs by only paying for compute resources when the database is active. Which Azure SQL Database pricing model should they choose?

A.Provisioned DTU
B.Provisioned vCore
C.Serverless
D.Hyperscale
AnswerC

Serverless is the correct choice because Azure SQL Database's serverless compute tier is explicitly designed for intermittent workloads like this reporting application. It automatically pauses the database after a configurable period of inactivity (default 60 minutes) and resumes instantly on the next connection, so compute is billed only on actual usage per second during active periods. Storage remains billed separately at all times, but eliminating idle compute charges makes it far cheaper than provisioned models for a reporting app that is used sporadically.

Why this answer

The Serverless pricing model for Azure SQL Database automatically pauses the database during periods of inactivity (e.g., weekdays) and resumes it when load increases (e.g., weekends), charging only for compute resources consumed during active periods. This aligns perfectly with the described workload pattern of mostly idle weekdays and heavy weekend usage, minimizing costs by eliminating charges for idle compute.

Exam trap

The trap here is that candidates may confuse 'Serverless' with 'Hyperscale' because both are modern Azure SQL offerings, but Hyperscale focuses on storage scalability and performance, not on pausing compute to save costs during idle periods.

Why the other options are wrong

A

Provisioned DTU requires paying for a fixed set of resources regardless of usage, which does not allow cost savings during idle periods on weekdays.

B

Provisioned vCore requires continuous payment for allocated compute resources regardless of usage, so it does not allow pausing during idle periods to minimize costs.

D

Hyperscale is designed for very large databases (up to 100 TB) with high scalability and fast recovery, not for cost savings on intermittent workloads. It still requires continuous compute billing, so it does not minimize costs for a database that is idle most of the week.

When would these options actually be correct?

A

For a predictable workload with consistent usage patterns where you need guaranteed performance and can commit to a fixed resource level, such as a production OLTP system with steady traffic.

B

A question where the database requires predictable performance with reserved capacity, such as a production OLTP workload with consistent traffic, and the organization needs to isolate compute from storage for scaling or licensing flexibility.

D

A question where the database requires massive storage (e.g., over 4 TB), needs rapid scaling for unpredictable high loads, or demands fast recovery from failures. For example: 'A company has a 10 TB database with sudden spikes in traffic and needs near-instant recovery. Which service tier should they choose?'

Why candidates pick the wrong answer

A

Candidates may confuse DTU with a consumption-based model or assume it offers auto-pause capabilities similar to serverless.

B

Candidates may confuse the vCore model's ability to scale compute independently with the ability to pause compute, or they may think vCore is always more cost-effective without considering idle time.

D

Candidates may associate 'Hyperscale' with high performance and assume it can handle weekend loads efficiently, overlooking that it does not provide the auto-pause and auto-resume capability needed to save costs during idle periods.

84
Multi-Selectmedium

Which TWO of the following are characteristics of Azure Blob Storage?

Select 2 answers
A.Enforces a fixed schema for stored data
B.Supports access tiers (Hot, Cool, Archive)
C.Supports storing large binary objects such as videos
D.Provides ACID transactions across multiple records
E.Only supports block blobs
AnswersB, C

Azure Blob Storage supports Hot, Cool, and Archive access tiers to balance performance and cost. Hot tiers serve frequently accessed data with low latency, Cool tiers are for infrequent access with a lower storage cost, and Archive offers the cheapest storage but requires rehydration before reading. This tiering capability is a key cost-optimization feature unique to object storage.

Why this answer

Options B and C are correct. Azure Blob Storage supports access tiers (Hot, Cool, Archive) to optimize cost (B) and can store large binary objects like videos (C). Option A is incorrect because Blob Storage does not enforce a fixed schema; it stores unstructured data.

Option D is incorrect because ACID transactions across multiple records are a feature of relational databases, not Blob Storage. Option E is incorrect because Blob Storage supports block blobs, append blobs, and page blobs.

85
MCQeasy

A company wants to build a real-time analytics dashboard for IoT sensor data. Which combination of Azure services should they use?

A.Azure HDInsight and Azure Databricks
B.Azure Logic Apps and Azure SQL Database
C.Azure Data Factory and Azure Analysis Services
D.Azure Stream Analytics and Power BI
AnswerD

Azure Stream Analytics is a fully managed stream-processing service that runs SQL-like queries against unbounded data from sources like Event Hubs or IoT Hub, supporting temporal windows such as tumbling, hopping, and sliding to compute live aggregates. It has a native output sink to Power BI, which pushes results into a Power BI dataset that dashboard tiles can subscribe to for near-real-time updates—typically within a few seconds. Power BI then renders the streaming data visually, enabling a dashboard to stay current without manual refresh or batch scheduling. This combination is exactly what the company needs for a real-time analytics dashboard because Event Hubs delivers the stream, ASA processes it continuously, and Power BI displays it live.

Why this answer

Azure Stream Analytics is a real-time event processing engine that can ingest IoT sensor data from sources like Azure Event Hubs, apply SQL-based queries to detect patterns or anomalies, and output results directly to Power BI for live dashboard visualization. This combination provides end-to-end streaming analytics with sub-second latency, which is essential for real-time dashboards.

Exam trap

The trap here is that candidates often confuse batch processing services (like Azure Data Factory or HDInsight) with real-time streaming services, or assume that any database (like Azure SQL) can handle high-velocity streaming data, but only Stream Analytics provides the necessary event-time processing and low-latency output for live dashboards.

How to eliminate wrong answers

Option A is wrong because Azure HDInsight and Azure Databricks are designed for batch and big data processing (e.g., Hadoop/Spark jobs), not for low-latency real-time streaming; they introduce significant overhead for continuous sensor data. Option B is wrong because Azure Logic Apps is an orchestration service for workflows and integrations, not a stream processing engine, and Azure SQL Database is optimized for transactional workloads, not real-time analytics on high-velocity streaming data. Option C is wrong because Azure Data Factory is a cloud ETL service for batch data movement and transformation, and Azure Analysis Services is an OLAP engine for historical analytics; neither supports real-time ingestion or live dashboard updates.

86
MCQeasy

A company stores customer names and addresses in a fixed-format file where each record has the same fields in the same order. This type of data is best described as:

A.Structured data
B.Semi-structured data
C.Unstructured data
D.Streaming data
AnswerA

Structured data is organized into a predefined schema, meaning every record contains the same named fields in the same order, such as CustomerName and CustomerAddress. A fixed-format file enforces this rigid layout, often with fixed-width columns or delimiters, so each row maps cleanly to a relational table. This predictability makes it directly queryable with SQL and suitable for Azure SQL Database or Synapse Analytics.

Why this answer

A fixed-format file where each record has the same fields in the same order is a classic example of structured data. Structured data conforms to a rigid schema, such as a table with defined columns and data types, making it easily searchable and processable by relational database systems like Azure SQL Database. The consistent field order and fixed format allow for direct parsing without interpretation.

Exam trap

The trap here is that candidates confuse 'fixed-format' with 'semi-structured' because both can be stored in files, but the key distinction is that fixed-format enforces a rigid schema with identical fields per record, whereas semi-structured allows schema flexibility.

How to eliminate wrong answers

Option B is wrong because semi-structured data (e.g., JSON, XML) does not enforce a fixed schema; fields can vary between records and order may not be guaranteed. Option C is wrong because unstructured data (e.g., text files, images, videos) lacks a predefined data model or organization, unlike the fixed-format file described. Option D is wrong because streaming data refers to data that is continuously generated and processed in real-time (e.g., from IoT devices or event hubs), not to the storage format or schema of the data.

87
MCQeasy

A media company stores high-definition video files for on-demand streaming. The files are accessed very frequently for the first 30 days after upload, then rarely (about once per month) for the next year, and after one year they are rarely accessed but must be retained for compliance (about once per year). Which set of access tier transitions minimizes cost while meeting access requirements?

A.Hot for 30 days, then Cool for 11 months, then Archive
B.Hot for 30 days, then Archive immediately
C.Cool for 30 days, then Cool for 11 months, then Archive
D.Hot for 365 days, then Archive
AnswerA

Hot tier serves the initial frequent access with low latency. Cool tier reduces storage cost during the period of occasional access (once per month) while still allowing retrieval within seconds. Archive tier provides the lowest cost for long-term compliance storage.

Why this answer

It aligns the Azure Blob Storage access tier transitions with the access pattern: Hot tier for the first 30 days of frequent access, Cool tier for the next 11 months of monthly access, and Archive tier after one year for rare compliance access. This minimizes cost by moving data to progressively cheaper storage tiers as access frequency drops, while still meeting the access requirements (Cool supports monthly access, Archive supports yearly access with retrieval time).

Exam trap

The trap here is that candidates assume the Cool tier is always cheaper than Hot for the first 30 days, but Cool's higher read costs and 30-day minimum charge make Hot more cost-effective for frequent access, and Archive's retrieval latency makes it unsuitable for monthly access.

How to eliminate wrong answers

Option B is wrong because moving directly from Hot to Archive after 30 days ignores the monthly access requirement during the next 11 months; Archive tier has a retrieval latency of up to 15 hours and is not suitable for monthly access. Option C is wrong because starting with Cool for the first 30 days incurs higher cost than Hot for that period (Cool has a higher per-GB read cost and a minimum 30-day storage charge, making it more expensive for frequent access). Option D is wrong because keeping data in Hot for 365 days wastes cost for the 11 months of rare access (Cool is cheaper for monthly access) and then moving to Archive after a year is unnecessary for the first year's access pattern.

88
Multi-Selecteasy

Which TWO of the following are characteristics of structured data?

Select 2 answers
A.It has a predefined schema
B.It is stored in Azure Blob Storage as objects
C.It can contain images and videos
D.It is often stored in relational databases
E.It uses tags to describe the data
AnswersA, D

Structured data is defined by a rigid schema that specifies data types, constraints, and relationships before any data is written. This schema enforces consistency and enables efficient querying and indexing, but it demands careful upfront design and makes evolving the data model costly.

Why this answer

Structured data adheres to a predefined schema, meaning its fields, data types, and relationships are defined in advance, typically enforced by a database management system. This schema ensures consistency and enables efficient querying using SQL. Relational databases are the primary storage system for structured data, organizing it into tables with rows and columns that follow the schema.

Exam trap

The trap here is that candidates confuse the storage location (Azure Blob Storage) or metadata mechanisms (tags) with the core definition of structured data, which is solely about having a predefined schema and typically being stored in relational databases.

89
Multi-Selecthard

Which THREE of the following are characteristics of a data lake compared to a data warehouse?

Select 3 answers
A.Data lakes store data in its native or raw format.
B.Data lakes store structured, semi-structured, and unstructured data.
C.Data lakes use schema-on-read rather than schema-on-write.
D.Data lakes guarantee ACID transactions across all data.
E.Data lakes store only structured data.
AnswersA, B, C

Data lakes preserve ingested data exactly as it arrives, without pre-processing, transformation, or format conversion before landing. This native/raw retention lets organizations re-purpose the same underlying file later for diverse workloads, since no information has been discarded for a particular analysis. It is a key difference from data warehouses, which clean and transform data during the load process.

Why this answer

A data lake stores data in its native or raw format, meaning it does not require transformation or schema definition at the time of ingestion. This allows organizations to retain the original fidelity of the data, which is a fundamental distinction from a data warehouse that typically transforms and structures data before loading (ETL). In Azure, Azure Data Lake Storage (ADLS) Gen2 supports storing any file format (e.g., Parquet, CSV, JSON, binary) without preprocessing.

Exam trap

Microsoft often tests the misconception that data lakes are just 'dumping grounds' without any structure, but the trap here is confusing ACID guarantees (which are optional and engine-specific) as a universal characteristic of data lakes, or assuming data lakes only handle unstructured data when they actually support all data types.

90
MCQmedium

A company uses Azure SQL Database for an order processing system. The Orders table has columns: OrderID (PK), CustomerID, OrderDate, TotalAmount. The Customers table has CustomerID (PK), Name, Email. The database administrator wants to ensure that when a customer record is deleted, all orders for that customer are also automatically deleted. Which database constraint should be implemented?

A.ON DELETE SET NULL on Orders.CustomerID
B.ON DELETE CASCADE on Orders.CustomerID
C.ON UPDATE CASCADE on Customers.CustomerID
D.A trigger on Customers table
AnswerB

ON DELETE CASCADE is the correct choice because it declaratively tells the database to automatically delete all rows in the Orders table that reference a customer when that customer's row is deleted from the Customers table. This is a built-in referential action on the foreign key constraint, ensuring that no orphaned order rows remain and that the deletion is atomic and enforced by the database engine. Unlike procedural triggers, it cannot be bypassed accidentally and requires no custom T-SQL logic. This exactly meets the requirement for removing a customer and their associated orders.

Why this answer

ON DELETE CASCADE on the foreign key (Orders.CustomerID) automatically deletes all child rows in the Orders table when the parent row in the Customers table is deleted. This ensures referential integrity without requiring additional code or triggers, and is the standard SQL mechanism for cascading deletes in Azure SQL Database.

Exam trap

The trap here is that candidates often confuse ON DELETE CASCADE with ON UPDATE CASCADE, mistakenly thinking that updating a primary key is the same as deleting a record, or they incorrectly assume that a trigger is always required for cascading operations when a declarative constraint is available.

How to eliminate wrong answers

Option A is wrong because ON DELETE SET NULL would set Orders.CustomerID to NULL when the customer is deleted, which does not delete the orders and would leave orphaned rows with a NULL foreign key. Option C is wrong because ON UPDATE CASCADE handles changes to the primary key value (CustomerID), not deletions; it would update the foreign key in Orders when CustomerID changes, but does not address the delete requirement. Option D is wrong because while a trigger could achieve the same result, it is not a database constraint; it is procedural code that is less declarative, more complex to maintain, and can introduce performance overhead compared to the built-in declarative ON DELETE CASCADE constraint.

91
MCQmedium

A company updates a customer's address in a database. The update must ensure that all existing orders still reference a valid customer ID. The database checks the foreign key constraint and rejects the update if it would violate referential integrity. Which ACID property does this enforcement represent?

A.Atomicity
B.Consistency
C.Isolation
D.Durability
AnswerB

Consistency guarantees that a transaction will not leave the database in a state that violates any declared integrity constraint. Here, changing a customer's address to a value that does not exist in the referenced table would orphan related records, so the database rejects the update to preserve referential integrity. This is the 'C' in ACID: each transaction must take the database from one valid state to another, regardless of how many rows are affected.

Why this answer

Consistency ensures that any database transaction brings the database from one valid state to another, preserving all defined rules, including constraints like foreign keys. In this scenario, the foreign key constraint enforcement prevents an update that would leave orphaned order records, directly upholding the consistency property by rejecting the transaction if it violates referential integrity.

Exam trap

The trap here is that candidates often confuse consistency with atomicity, thinking that rejecting an invalid update is about 'all-or-nothing' behavior, when in fact consistency is specifically about maintaining data integrity rules and constraints.

How to eliminate wrong answers

Option A is wrong because atomicity ensures that a transaction is treated as a single, indivisible unit that either fully completes or fully rolls back, but it does not specifically enforce data rules like foreign key constraints. Option C is wrong because isolation ensures that concurrent transactions do not interfere with each other, preventing dirty reads or lost updates, but it does not enforce referential integrity rules. Option D is wrong because durability guarantees that once a transaction is committed, its changes persist even in the event of a system failure, but it does not validate or enforce constraints during the transaction.

92
MCQmedium

A company stores IoT sensor data in Azure Blob Storage. The data is structured as JSON files organized by date. Data scientists need to query this data using SQL statements without moving it. Which Azure service should they use to enable this?

A.Azure SQL Database
B.Azure Data Lake Storage Gen2
C.Azure Cosmos DB
D.Azure Synapse Serverless SQL
AnswerD

Azure Synapse Serverless SQL is the appropriate choice because its OPENROWSET function can query JSON (or CSV/Parquet) files directly in Azure Blob Storage or Azure Data Lake Storage Gen2 using T-SQL. It does this without requiring you to import the data into a database or provision a dedicated SQL pool, and it uses a pay-per-query model. This lets you run ad hoc analytics on IoT sensor data in place, exactly matching the requirement.

Why this answer

Azure Synapse Serverless SQL enables querying JSON files directly in Azure Blob Storage using T-SQL without moving the data. Option A (Azure SQL Database) is a relational database that requires data to be loaded into tables. Option B (Azure Data Lake Storage Gen2) is a storage layer, not a query service.

Option C (Azure Cosmos DB) is a NoSQL database that can store JSON but requires moving data from Blob Storage.

93
MCQmedium

A company collects temperature readings from IoT sensors every second. Each reading includes a timestamp, sensor ID, and temperature value. The data is used for real-time monitoring and historical trend analysis. Which type of data is this most likely classified as?

A.Structured data
B.Semi-structured data
C.Unstructured data
D.Streaming data
AnswerA

Structured data is organized into a rigid, predefined schema, such as a relational table, where each row represents a sensor reading with fixed columns like timestamp, sensor ID, and temperature. All records follow the identical format, with specific data types (e.g., DATETIME, INT, FLOAT), enabling efficient querying and aggregation. The IoT readings perfectly match this definition, as there is no variability in fields or nested properties. This makes the data directly storable in a SQL database for analysis.

Why this answer

The data consists of timestamp, sensor ID, and temperature value, each with a defined data type and relationship, fitting a tabular schema (rows and columns) typical of relational databases. This structured format enables efficient querying for real-time monitoring and historical trend analysis using SQL-based systems like Azure SQL Database or Azure Synapse Analytics.

Exam trap

The trap here is confusing the data's structure (structured vs. semi-structured) with its velocity (streaming vs. batch), leading candidates to incorrectly select 'Streaming data' because the data arrives in real time, even though the question explicitly asks about classification by type, not ingestion method.

How to eliminate wrong answers

Option B is wrong because semi-structured data (e.g., JSON, XML) has flexible schema with tags or key-value pairs, not the fixed, predefined columns of this IoT data. Option C is wrong because unstructured data (e.g., images, videos, text files) lacks a predefined data model or organization, unlike the clearly defined fields here. Option D is wrong because streaming data refers to the continuous flow of data (e.g., via Azure Stream Analytics or Event Hubs), not the classification of the data's structure; the question asks about data type, not ingestion method.

94
Drag & Dropmedium

Drag and drop the steps to ingest data into Azure Data Explorer (ADX) in the correct order.

Drag steps to the numbered slots on the right, or tap a step then tap a slot.

Steps
Order
1Step 1
2Step 2
3Step 3
4Step 4

Why this order

Ingestion involves creating the target table, defining mapping, executing the ingestion, and verifying.

95
MCQeasy

A company wants to store historical sales data for long-term analysis. The data is accessed infrequently but must be retained for 7 years. Which Azure storage tier minimizes cost while meeting these requirements?

A.Archive storage tier
B.Premium storage tier
C.Cool storage tier
D.Hot storage tier
AnswerC

The Cool storage tier is designed for data that is infrequently accessed (roughly less than once a month) but still needs to be available immediately on request, offering low storage costs with no minimum retention period. For a company storing historical sales data long term, this tier provides the best balance between cost efficiency and the ability to retrieve past records without the latency penalties or 180-day commitment of the Archive tier.

Why this answer

The Cool storage tier is designed for data that is accessed infrequently but must be retained for extended periods, offering lower storage costs than Hot tier while still providing low-latency access when needed. With a 30-day minimum storage duration and a cost structure optimized for infrequent reads, it balances cost and accessibility for 7-year retention of historical sales data.

Exam trap

The trap here is that candidates often choose the Archive tier because it has the lowest storage cost, forgetting that retrieval latency and higher access costs make it unsuitable for data that may need to be accessed even occasionally during the retention period.

How to eliminate wrong answers

Option A is wrong because the Archive storage tier is intended for data that is rarely accessed and can tolerate hours of retrieval latency, which is overkill for data that may need occasional access and incurs higher retrieval costs. Option B is wrong because Premium storage tier is optimized for high-performance, low-latency workloads (e.g., IaaS VMs or databases) and is significantly more expensive, making it unsuitable for long-term, infrequently accessed historical data. Option D is wrong because the Hot storage tier is designed for frequently accessed data with higher storage costs and no minimum retention period, leading to unnecessary expense for data that is accessed infrequently.

96
MCQmedium

A global social media startup stores user profiles as JSON documents in Azure Cosmos DB. Their application frequently reads profiles by user ID and also runs queries to find users based on location or interests. The workload is read-heavy with high throughput requirements. The operations team notices that query performance degrades during peak hours. Which action would most effectively improve query performance?

A.Increase the number of containers
B.Choose a different API (e.g., switch from SQL API to MongoDB API)
C.Increase the provisioned throughput (RU/s)
D.Switch to a different Azure region
AnswerC

Provisioned throughput in Azure Cosmos DB is measured in Request Units per second (RU/s). Each operation, whether a read, write, or query, consumes a specific number of RUs based on item size, indexing, and consistency level. Increasing RU/s directly allocates more computational capacity, allowing the database to handle a higher rate of operations per second and eliminating throttling (HTTP 429 errors). This is the primary knob for scaling throughput in an Azure Cosmos DB account.

Why this answer

Increasing the provisioned throughput (RU/s) directly allocates more processing capacity to the Cosmos DB container, allowing it to handle higher request volumes and reduce throttling during peak hours. Since the workload is read-heavy and query performance degrades under high throughput demands, raising RU/s is the most effective and immediate action to improve performance.

Exam trap

The trap here is that candidates may confuse throughput (RU/s) with other scaling mechanisms like partitioning or API choice, but the core issue in a read-heavy, high-throughput scenario is insufficient provisioned capacity, not data organization or protocol differences.

Why the other options are wrong

A

Increasing the number of containers does not improve query performance; it only helps with data partitioning and management. Query performance in Cosmos DB is primarily governed by provisioned throughput (RU/s), not the number of containers.

B

Switching APIs (e.g., from SQL to MongoDB) does not inherently improve query performance for read-heavy workloads; it changes the data model and query syntax but does not increase throughput or reduce latency under high load.

D

Switching to a different Azure region does not improve query performance for a read-heavy workload with high throughput; it primarily addresses latency or availability issues related to geographic distance, not throughput or query efficiency.

When would these options actually be correct?

A

If the question described a scenario where data is unevenly distributed across partitions causing hot partitions, and the goal is to improve write performance or distribute load more evenly, then increasing the number of containers (or partitions) could be correct.

B

A question where the current API lacks required query capabilities (e.g., geospatial queries) and the alternative API natively supports them, such as switching to MongoDB API for location-based queries in Cosmos DB.

D

A social media app has users concentrated in a specific geographic area, and they experience high latency due to the Cosmos DB account being in a distant region. Moving the account to a region closer to users would reduce network latency and improve read performance.

Why candidates pick the wrong answer

A

Candidates may mistakenly think that more containers mean more parallelism and thus faster queries, similar to scaling out in traditional databases, without understanding that Cosmos DB's performance is tied to RU/s and indexing.

B

Candidates may believe that different APIs offer performance optimizations or are better suited for specific workloads, overlooking that the API choice primarily affects data modeling and query syntax, not throughput.

D

Candidates may think that changing regions can improve performance by moving data closer to users, but the question specifies performance degradation during peak hours due to throughput limits, not geographic latency.

97
MCQmedium

Refer to the exhibit. You deploy this Azure Stream Analytics job. The job runs but no data is written to the Azure SQL Database table. What is the most likely cause?

A.The query syntax is invalid
B.The TumblingWindow has not yet elapsed
C.The Event Hub name is incorrect
D.The SKU does not support SQL output
AnswerB

A tumbling window is a fixed-duration, non-overlapping time interval, and Stream Analytics emits aggregate results only after the window's end time is reached. If the job has been running for less than 5 minutes (the window size), the first window has not yet closed, so no aggregated rows are written to the SQL output. This perfectly matches the observed behavior of a started job with zero output.

Why this answer

The TumblingWindow function in Azure Stream Analytics processes data in fixed, non-overlapping time intervals. Since the job runs but no data is written, the most likely cause is that the first window has not yet elapsed, meaning no aggregation has been triggered to output results. Stream Analytics only emits results when a tumbling window completes, so data will not appear in the SQL Database until the window duration passes.

Exam trap

The trap here is that candidates assume a running job with no errors means data should flow immediately, but they overlook that tumbling windows require the full window duration to elapse before any output is produced.

How to eliminate wrong answers

Option A is wrong because if the query syntax were invalid, the job would fail to start or would generate a compilation error, not run without writing data. Option C is wrong because an incorrect Event Hub name would cause the job to fail at input connection time, not allow it to run silently with no output. Option D is wrong because all Stream Analytics SKUs (including Standard and higher) support Azure SQL Database as an output sink; there is no SKU restriction that would prevent writing to SQL.

98
MCQmedium

A company wants to migrate an on-premises SQL Server database to Azure. The database uses SQL Agent jobs to run nightly ETL processes and relies on Service Broker for asynchronous messaging between applications. They want to minimize changes to the application and database code. Which Azure SQL deployment option should they choose?

A.Azure SQL Managed Instance
B.Azure SQL Database (single database)
C.Azure SQL Database elastic pool
D.SQL Server on Azure Virtual Machines
AnswerA

Azure SQL Managed Instance is a fully managed PaaS deployment that provides near 100% surface-area compatibility with on-premises SQL Server, including instance-scoped features such as SQL Agent jobs, Service Broker, Database Mail, and CLR assemblies. It integrates with a customer's virtual network yet offloads patching, backups, and high availability to Azure, making it the ideal choice for a lift-and-shift migration with minimal application changes. Unlike single databases, it supports cross-database queries and the full T-SQL surface needed by legacy workloads.

Why this answer

Azure SQL Managed Instance is the correct choice because it provides near 100% compatibility with on-premises SQL Server, including support for SQL Agent jobs and Service Broker. This allows the company to migrate the database with minimal code changes, as these features are not available in Azure SQL Database (single or elastic pool). SQL Server on Azure VMs would also support these features but requires more management overhead and is not a fully managed PaaS option.

Exam trap

The trap here is that candidates may assume SQL Server on Azure VMs is the only option for full compatibility, but Azure SQL Managed Instance offers the same compatibility with less operational overhead, making it the optimal PaaS choice for minimizing code changes.

Why the other options are wrong

B

Azure SQL Database (single database) does not support SQL Agent jobs or Service Broker, which are required by the company's existing ETL processes and asynchronous messaging.

C

Azure SQL Database elastic pool does not support SQL Agent jobs or Service Broker, which are required for the nightly ETL processes and asynchronous messaging without code changes.

D

SQL Server on Azure VMs requires you to manage the OS and SQL Server, including patching and backups, and does not provide native support for Service Broker or SQL Agent jobs without additional configuration. The question emphasizes minimizing changes, but this option would require more management overhead and potential code changes.

When would these options actually be correct?

B

An exam scenario where a company wants to migrate a single SQL Server database with no dependency on instance-level features like SQL Agent jobs, Service Broker, or cross-database queries, and is willing to refactor application code to use Azure Elastic Jobs or other alternatives.

C

A company needs to manage multiple databases with varying and unpredictable usage patterns, seeking cost-effective resource sharing and simplified management, and does not require instance-level features like SQL Agent or Service Broker.

D

A company needs full control over the SQL Server environment, including custom configurations, third-party tools, or specific versions of SQL Server that are not supported in PaaS offerings. They are willing to manage the VM and accept higher administrative overhead.

Why candidates pick the wrong answer

B

Candidates may assume Azure SQL Database is the simplest PaaS option and overlook the specific feature requirements (SQL Agent, Service Broker) that are only available in Managed Instance.

C

Candidates may think elastic pools offer similar functionality to Managed Instance due to the 'managed' aspect, or they may focus on cost savings without considering the missing instance-level features.

D

Candidates may think that running SQL Server on a VM is the most compatible option because it provides the same SQL Server engine as on-premises, overlooking the fact that Azure SQL Managed Instance offers higher compatibility with fewer management tasks.

99
MCQhard

A multinational corporation uses Azure Data Factory to orchestrate data pipelines across multiple regions. The company notices that pipeline runs in the West Europe region consistently fail due to throttling errors from the source database. The source database is an Azure SQL Database in the same region. The company needs to reduce throttling while maintaining pipeline throughput. What should the company do?

A.Increase the maximum number of Data Factory activity retries.
B.Stage extracted data in Azure Blob Storage before loading into the destination.
C.Use a self-hosted integration runtime in the same region to execute pipelines.
D.Configure auto-scale on the Azure SQL Database to handle higher load.
AnswerD

Auto-scale on Azure SQL Database (via the serverless compute tier or elastic pool scaling) dynamically adjusts the compute resources (vCores or DTUs) to match the workload, providing additional capacity when the pipeline increases demand. This directly addresses the throttling condition, because throttling occurs when a query or transaction exceeds the resource limits of the current service objective; by scaling up, the database can handle a higher rate of requests without returning error 10928 or 40613. However, auto-scale is not instantaneous — scale-up/scale-down can take minutes — and the database's max size or per-query limits still apply, so pipelines should also be designed to avoid unreasonable bursts. This is the only option that treats the symptom at the resource layer, making it the correct choice for reducing throttling-related failures.

Why this answer

The throttling errors originate from the Azure SQL Database source, which has resource limits (DTU or vCore-based). Configuring auto-scale on the Azure SQL Database dynamically adjusts its performance tier to handle higher concurrency and throughput, directly addressing the root cause of throttling while maintaining pipeline throughput. This aligns with the requirement to reduce throttling without reducing workload.

Exam trap

The trap here is that candidates confuse throttling errors with network or connectivity issues, leading them to choose integration runtime or staging solutions, when the root cause is the source database's resource limits.

How to eliminate wrong answers

Option A is wrong because increasing activity retries only re-executes failed operations, which does not prevent throttling and may exacerbate load on the source database. Option B is wrong because staging data in Blob Storage addresses destination load or transformation patterns, not source-side throttling from Azure SQL Database. Option C is wrong because using a self-hosted integration runtime in the same region improves network latency and connectivity but does not reduce the source database's resource contention or throttling limits.

100
MCQhard

A financial services company is evaluating distributed NoSQL databases for a new application that must remain fully available even during network partitions. The application can tolerate stale reads for some types of queries. Which statement accurately describes the trade-off described by the CAP theorem in this context?

A.During a network partition, the system can maintain both consistency and availability.
B.When a network partition occurs, a distributed system must choose between providing consistency and providing availability.
C.Partition tolerance is an optional property and can be sacrificed to achieve both consistency and availability.
D.Availability guarantees that every read returns the most recent write.
AnswerB

The CAP theorem formalizes the inherent trade-off of distributed systems during a network partition: when nodes cannot communicate, there is no way to atomically update all replicas and still respond to every client request. To preserve consistency (every read reflects the latest write), a system must refuse requests from nodes that cannot confirm the newest state, sacrificing availability. To preserve availability, nodes must answer requests using only local data, which may be stale, thus sacrificing consistency. Thus the theorem forces an explicit choice once a partition occurs.

Why this answer

The CAP theorem states that during a network partition (P), a distributed system must choose between consistency (C) and availability (A). Since the application requires full availability even during partitions, it must sacrifice strong consistency in favor of eventual consistency, which tolerates stale reads. Option B correctly captures this fundamental trade-off.

Exam trap

The trap here is that candidates often confuse 'availability' with 'consistency' or assume that partition tolerance can be sacrificed, when in fact the CAP theorem requires that partition tolerance be a given in any distributed system, and the real choice is between consistency and availability during a partition.

Why the other options are wrong

A

The CAP theorem states that during a network partition, a distributed system must choose between consistency and availability; it cannot maintain both simultaneously, making this statement false.

C

Partition tolerance is not optional in distributed systems; the CAP theorem states that during a network partition, you must choose between consistency and availability, but partition tolerance itself is a requirement for distributed systems, not a trade-off.

D

Availability in the CAP theorem means every request receives a response, but not necessarily the most recent write. Option D incorrectly defines availability as returning the most recent write, which is actually a consistency guarantee.

When would these options actually be correct?

A

This option would be correct in a scenario where the system is not subject to network partitions (e.g., a single-node database or a system that guarantees no partitions), allowing it to provide both consistency and availability.

C

In a scenario where the question explicitly states that the system is not distributed (e.g., a single-node database) or where the application can tolerate network partitions by design (e.g., using a consensus algorithm that sacrifices availability), then sacrificing partition tolerance could be considered to achieve both consistency and availability.

D

In a question asking about the definition of strong consistency in a distributed database, where the requirement is that every read returns the most recent write, this statement would be correct.

Why candidates pick the wrong answer

A

Candidates may mistakenly believe that modern distributed systems can achieve both consistency and availability under all conditions, overlooking the fundamental trade-off imposed by the CAP theorem during partitions.

C

Candidates may misinterpret the CAP theorem as allowing a choice to drop partition tolerance entirely, rather than understanding that partition tolerance is a fundamental requirement for distributed systems, and the trade-off is between consistency and availability during a partition.

D

Candidates often conflate availability with consistency, assuming that an available system always returns the latest data, not realizing that availability only guarantees a response, not its freshness.

101
Multi-Selectmedium

Which TWO features are supported by Azure SQL Database to provide high availability?

Select 2 answers
A.Always On availability groups
B.Point-in-time restore
C.Active geo-replication
D.Auto-failover groups
E.Log shipping
AnswersC, D

Active geo-replication is a built-in Azure SQL Database feature that continuously replicates committed transactions to readable secondary databases in a different Azure region. It supports up to four secondaries per primary, and you can manually initiate failover or configure read workloads to query the secondaries for load balancing. These secondaries are real database endpoints, making the feature a direct high-availability and disaster-recovery mechanism with no need to set up any external infrastructure.

Why this answer

Options C and D are correct. Active geo-replication enables creating readable secondary databases in different regions for failover, while auto-failover groups manage the failover of a group of databases with automatic initiation. Option A is incorrect because Always On availability groups are an on-premises SQL Server feature, not directly used in Azure SQL Database.

Option B is incorrect because point-in-time restore is a backup feature for recovery to a specific time, not for high availability. Option E is incorrect because log shipping is an on-premises disaster recovery technique not natively supported in Azure SQL Database.

102
MCQhard

A company stores large archives of legal documents in Azure Blob Storage. The documents must remain immutable; they cannot be modified or deleted for 7 years due to regulatory requirements. The data is accessed only for compliance audits, which occur less than once a year. The company wants to minimize storage costs while ensuring immutability and data durability. Which combination of features should they configure?

A.Cool access tier with a time-based retention policy
B.Archive access tier with a time-based retention policy
C.Hot access tier with versioning enabled
D.Archive access tier with legal hold
AnswerB

The Archive access tier provides the lowest storage cost in Azure Blob Storage, making it ideal for rarely accessed legal archives. A time-based retention policy, a type of WORM (Write Once, Read Many) immutability, locks blobs for a predefined 7-year period, preventing both deletion and overwriting until the policy expires. While retrieval requires rehydration and incurs additional costs, the combination meets the compliance requirement of a fixed retention period at minimal ongoing storage expense.

Why this answer

The Archive access tier provides the lowest storage cost for data that is rarely accessed, such as legal documents accessed less than once a year. A time-based retention policy enforces immutability for a fixed 7-year period, preventing modifications or deletions. This combination meets regulatory requirements while minimizing storage costs.

Exam trap

The trap here is that candidates may confuse 'legal hold' (which is indefinite and manually managed) with 'time-based retention policy' (which automatically expires after a set duration), leading them to incorrectly choose the Archive tier with legal hold instead of the correct time-based retention policy.

Why the other options are wrong

A

The Cool access tier has higher storage costs than Archive and is not designed for data accessed less than once a year; the question emphasizes minimizing costs, making Archive the appropriate tier.

C

The Hot access tier has higher storage costs than Archive, and versioning does not enforce immutability (versions can be deleted). This combination does not meet the cost minimization goal and fails to provide the required regulatory immutability.

D

Legal hold applies to specific blobs for indefinite periods, not for a fixed 7-year duration, and does not minimize costs as it requires the Archive tier but lacks the automatic time-based policy needed for regulatory compliance.

When would these options actually be correct?

A

A company stores legal documents that must be immutable for 7 years but are accessed quarterly for audits. They need a balance between cost and retrieval time, so Cool tier with a time-based retention policy would be correct.

C

A company needs frequent access to blobs (e.g., daily updates) and requires protection against accidental deletion or overwrites, but not strict immutability. Versioning allows restoring previous versions, and Hot tier provides low-latency access.

D

A company needs to preserve specific blobs indefinitely for litigation or investigation purposes, with no fixed expiration date, and can accept manual management of holds. The question would specify 'indefinite preservation' and 'legal case' rather than a fixed retention period.

Why candidates pick the wrong answer

A

Candidates may confuse 'Cool' with 'Cold' or think it's the lowest-cost tier suitable for long-term retention, overlooking that Archive is cheaper for rarely accessed data.

C

Candidates may think versioning provides immutability, but it only preserves previous versions; it does not prevent deletion of the current version or the entire blob. They may also overlook the cost difference between Hot and Archive tiers.

D

Candidates confuse legal hold with time-based retention, thinking both provide immutability, but overlook that legal hold is for indefinite, case-specific holds and does not enforce a fixed retention period required by regulations.

103
MCQeasy

A company stores customer data in three formats: a relational table with fixed columns for CustomerID, Name, and Email; product reviews stored as JSON documents with varying fields such as rating and comment; and product demonstration videos in MP4 format. Which of the following correctly lists these data types from most structured to least structured?

A.Relational table, MP4 videos, JSON documents
B.JSON documents, relational table, MP4 videos
C.Relational table, JSON documents, MP4 videos
D.MP4 videos, JSON documents, relational table
AnswerC

Relational tables are fully structured because they enforce a strict schema: rows, columns, data types, primary keys, and relationships are defined before data is stored. JSON documents are semi-structured because they use self-describing key-value pairs and nesting, but no fixed schema is required, so different documents can have different fields. MP4 videos are unstructured because they are binary containers for compressed audio and video data, with no field-level metadata that can be directly queried without specialized media-processing tools.

Why this answer

Data structuredness is determined by schema rigidity. A relational table has a fixed schema with predefined columns (CustomerID, Name, Email), making it the most structured. JSON documents have a flexible schema where fields like rating and comment can vary per document, placing them in the semi-structured category.

MP4 videos are unstructured binary data with no inherent schema, making them the least structured.

Exam trap

Microsoft often tests the misconception that JSON is unstructured because it lacks a fixed schema, but JSON is actually semi-structured due to its self-describing key-value pairs, while binary formats like MP4 are truly unstructured.

Why the other options are wrong

A

MP4 videos are unstructured data, not semi-structured like JSON. The order should be relational table (structured), JSON documents (semi-structured), MP4 videos (unstructured).

B

JSON documents are semi-structured (varying fields), while relational tables are structured (fixed schema). The order from most to least structured should be relational table, JSON documents, then MP4 videos, not JSON first.

D

MP4 videos are unstructured data, not more structured than JSON documents (semi-structured) or relational tables (structured). The order from most to least structured should be relational table, JSON documents, MP4 videos.

When would these options actually be correct?

A

If the question asked to list data types from least structured to most structured, then option A (MP4 videos, JSON documents, relational table) would be correct.

B

If the question asked to list data types from least to most structured, then B (JSON, relational table, MP4) would be correct because MP4 is unstructured, JSON is semi-structured, and relational table is structured.

D

If the question asked to list data types from least structured to most structured, then D (MP4 videos, JSON documents, relational table) would be correct.

Why candidates pick the wrong answer

A

Candidates may mistakenly think that JSON is less structured than MP4 because JSON has flexible schema, but MP4 is binary and lacks any inherent structure for querying.

B

Candidates may mistakenly think JSON is more structured than relational tables because JSON has a defined syntax, but they overlook that relational tables enforce a fixed schema, making them more structured.

D

Candidates may mistakenly think that because videos are large and complex, they are more structured, or they confuse 'structured' with 'rich in information'.

104
MCQmedium

A company develops an IoT device registry that stores device metadata as JSON documents. Each device has a unique DeviceID, and the attributes vary per device type (e.g., sensors, actuators). The application requires low-latency reads by DeviceID and needs global distribution to support devices worldwide. Which Azure Cosmos DB API should they choose to natively support JSON documents with flexible schema?

A.Azure Cosmos DB SQL API
B.Azure Cosmos DB Table API
C.Azure Cosmos DB for MongoDB API
D.Azure Cosmos DB Gremlin API
AnswerA

The SQL API is the native JSON document model in Azure Cosmos DB, allowing devices to be stored as schemaless JSON documents with automatic indexing and rich querying via standard SQL syntax. It provides single-digit-millisecond point reads by ID, tunable consistency, and global distribution, making it ideal for an IoT device registry.

Why this answer

The Azure Cosmos DB SQL API (formerly DocumentDB) is the correct choice because it provides native support for storing and querying JSON documents with flexible schema, allowing each device document to have a unique DeviceID and varying attributes per device type. It offers low-latency reads by DeviceID via direct point reads using the partition key, and supports global distribution through multi-region writes and automatic replication, meeting the worldwide deployment requirement.

Exam trap

The trap here is that candidates often choose the MongoDB API because they associate JSON with MongoDB, but the SQL API is the native JSON document API in Cosmos DB and is the correct answer for 'natively support JSON documents with flexible schema' in the context of Azure Cosmos DB.

How to eliminate wrong answers

Option B (Azure Cosmos DB Table API) is wrong because it is designed for key-value and tabular data with a fixed schema, not for flexible JSON documents with varying attributes per device type. Option C (Azure Cosmos DB for MongoDB API) is wrong because while it supports JSON-like documents via BSON, it is a wire-protocol compatibility layer for MongoDB drivers and does not provide the native SQL query capabilities or the same optimized point-read performance for DeviceID as the SQL API; the question specifically asks for an API that natively supports JSON documents with flexible schema, which the SQL API does directly. Option D (Azure Cosmos DB Gremlin API) is wrong because it is built for graph data models and traversals using the Gremlin query language, not for document storage or key-based lookups.

105
MCQhard

Refer to the exhibit. You are analyzing a message from an IoT device captured in Azure Event Hubs. The message contains system properties indicating the device ID and authentication method. You need to route messages from device-01 to a separate storage container for compliance. Which property should you use in a Stream Analytics query to filter messages?

A.partitionId
B.consumerGroup
C.iothub-connection-device-id
D.deviceId
AnswerC

The iothub-connection-device-id system property is automatically appended by Azure IoT Hub to every message that it accepts from a device, and it contains the authenticated device ID. The value is set by the IoT Hub based on the device's authentication identity, so it cannot be spoofed by the device itself and reliably represents the source device. When processing messages in an Azure Function or a Stream Analytics job, you can use this property in a WHERE clause, for example WHERE iothub-connection-device-id = 'device-01', to filter messages from that specific device. This property is the definitive way to reference a device ID in IoT Hub message metadata.

Why this answer

The `iothub-connection-device-id` system property is automatically added by Azure IoT Hub to every device-to-cloud message. In a Stream Analytics query, you can reference this property directly (e.g., `WHERE iothub-connection-device-id = 'device-01'`) to filter messages from a specific device for routing to a separate storage container for compliance.

Exam trap

Microsoft often tests the exact naming of Azure IoT Hub system properties, and the trap here is that candidates assume a simple `deviceId` property exists, but the actual property name includes the `iothub-connection-` prefix, which is specific to IoT Hub's message enrichment.

How to eliminate wrong answers

Option A is wrong because `partitionId` is a logical partition key used for scaling and ordering within Event Hubs, not a device identifier; filtering by partition ID would not isolate messages from a specific device. Option B is wrong because `consumerGroup` is a logical group of consumers reading from an Event Hub or IoT Hub, used for load balancing and checkpointing, not a property on individual messages. Option D is wrong because `deviceId` is not a standard system property in Azure IoT Hub messages; the correct system property name is `iothub-connection-device-id` (with the full prefix), and using `deviceId` would result in a null or undefined value in the query.

106
MCQmedium

Refer to the exhibit. You are reviewing an Azure Resource Manager template for a Blob Storage container named 'sales'. The container has versioning enabled. A developer accidentally overwrites a blob. What is the simplest way to recover the previous version?

A.Access the previous version through the version list and restore it
B.Use blob soft delete to recover the blob
C.Restore from a backup using Azure Backup
D.Perform a point-in-time restore of the container
AnswerA

Azure Blob Storage versioning automatically creates a new version each time a blob is written, overwritten, or deleted. To recover the overwritten blob, open the container, select the blob, go to its Version list, locate the previous version you want, and promote it by copying it over the current version or by using the "Restore version" action. This self-service, metadata-driven operation requires no external backup product and preserves all other versions, including the current one, for auditability.

Why this answer

Azure Blob Storage versioning automatically maintains a history of blob versions. When a blob is overwritten, the previous version is preserved and can be accessed via the version list. The simplest recovery method is to promote the previous version to the current version, which restores the blob to its prior state without needing additional services or configurations.

Exam trap

The trap here is that candidates confuse versioning with soft delete, assuming soft delete can recover overwrites, but soft delete only protects against deletions, not modifications.

How to eliminate wrong answers

Option B is wrong because blob soft delete is a separate feature that protects against accidental deletion, not overwrites; it would not recover a previous version of an overwritten blob. Option C is wrong because Azure Backup is designed for broader disaster recovery scenarios (e.g., entire storage accounts or VMs) and is overkill for recovering a single blob version; it also requires additional cost and configuration. Option D is wrong because point-in-time restore is used to restore a container to a previous state, but it is more complex and resource-intensive than simply accessing the version list, and it requires the container to have immutable storage policies or specific backup configurations.

107
MCQmedium

A retail company uses Azure SQL Database for its sales transaction table, which contains over 500 million rows. Queries that filter by OrderDate are slow because the database scans the entire table. The database administrator decides to implement table partitioning on the OrderDate column. What is the primary benefit of this partitioning strategy?

A.It reduces the total storage required by compressing older partitions.
B.It improves query performance by enabling partition elimination, where only relevant partitions are scanned.
C.It enforces referential integrity between partitions automatically.
D.It eliminates the need for indexes on the partitioned column.
AnswerB

Partition elimination is a query-processing optimization that allows Azure SQL Database to access only the partition(s) relevant to a query's predicate on the partition key, instead of scanning the entire table. When a WHERE clause includes the partitioning column and the values are sargable, the optimizer can read far fewer pages, reducing I/O and improving response time. This is particularly valuable for large fact tables in a data warehouse where reports typically filter by date ranges.

Why this answer

Table partitioning in Azure SQL Database divides a large table into smaller, manageable segments based on a partition key (here, OrderDate). The primary benefit is partition elimination: queries with filters on OrderDate can scan only the relevant partition(s) instead of the entire 500-million-row table, drastically reducing I/O and improving query performance.

Exam trap

The trap here is that candidates may confuse partitioning with indexing or compression, thinking it automatically solves all performance issues or reduces storage, when its core benefit is query performance via partition elimination.

How to eliminate wrong answers

Option A is wrong because partitioning does not inherently compress older partitions; compression is a separate feature (e.g., page or row compression) that can be applied independently. Option C is wrong because referential integrity (foreign keys) is enforced at the table level, not automatically between partitions; partitioning does not manage relationships. Option D is wrong because partitioning does not eliminate the need for indexes; in fact, indexes are often still required on the partition key or other columns for optimal performance, and partitioning works alongside indexes.

108
MCQmedium

A company uses Azure SQL Database for a customer relationship management (CRM) application. The database has a table named Orders that stores order details. The company needs to ensure that the OrderDate column is automatically set to the current date and time when a new row is inserted, without any application-side logic. Which T-SQL construct should be used?

A.CHECK constraint
B.UNIQUE constraint
C.PRIMARY KEY constraint
D.DEFAULT constraint with GETDATE()
AnswerD

DEFAULT with GETDATE() automatically inserts current date/time.

Why this answer

A DEFAULT constraint with GETDATE() automatically populates the OrderDate column with the current date and time on each insert, without requiring application-side logic. Option A is incorrect because a CHECK constraint enforces data integrity by validating conditions but does not provide default values. Option B is incorrect because a UNIQUE constraint ensures all values in a column are unique, not auto-populate dates.

Option C is incorrect because a PRIMARY KEY constraint uniquely identifies each row and does not set default values.

109
MCQmedium

A data analyst uses Power BI to create a report that combines data from Azure Synapse Analytics and an on-premises SQL Server database. The on-premises data must be refreshed every hour. Which component is required to connect to the on-premises data source?

A.Azure VPN Gateway
B.On-premises data gateway
C.Azure ExpressRoute
D.Azure Data Factory
AnswerB

The on-premises data gateway is a software service that runs as a Windows service on a machine inside your local network. It securely stores connection metadata and credentials, then initiates an outbound connection to the Power BI service over Azure Service Bus, eliminating the need for open inbound firewall ports. This gateway enables both scheduled refreshes and DirectQuery queries against on-premises data sources, effectively bridging the secure boundary between Power BI and your local databases or files.

Why this answer

The on-premises data gateway is required to securely connect Power BI to on-premises SQL Server databases for scheduled refreshes. It acts as a bridge, transmitting data from the on-premises source to the Power BI service without opening inbound firewall ports. This component is specifically designed for self-service analytics scenarios where cloud services need to access on-premises data sources.

Exam trap

The trap here is that candidates often confuse Azure Data Factory's self-hosted integration runtime with the Power BI on-premises data gateway, but Power BI requires its own dedicated gateway component for scheduled refreshes, not Data Factory.

How to eliminate wrong answers

Option A is wrong because Azure VPN Gateway establishes site-to-site or point-to-site encrypted tunnels between Azure and on-premises networks, but it is not the component used by Power BI for scheduled data refresh from on-premises SQL Server. Option C is wrong because Azure ExpressRoute provides a dedicated private network connection to Azure, but it is not required for Power BI's on-premises data gateway functionality and is typically used for high-bandwidth, low-latency enterprise scenarios. Option D is wrong because Azure Data Factory is a cloud-based ETL and data integration service that can copy data from on-premises sources using self-hosted integration runtimes, but it is not the component that Power BI directly uses for its scheduled refresh of on-premises data.

110
MCQmedium

A data engineering team at a logistics company handles two distinct data processing workloads. The first workload ingests GPS data from delivery trucks every 10 seconds and updates a dashboard showing real-time vehicle locations. The second workload processes monthly CSV files of completed deliveries to generate reports on delivery times and route efficiency. Which statement correctly identifies these workloads?

A.Both workloads are streaming workloads
B.GPS data processing is a batch workload; monthly report processing is a streaming workload
C.GPS data processing is a streaming workload; monthly report processing is a batch workload
D.Both workloads are batch workloads
AnswerC

Correct. Real-time data ingestion and dashboard updates represent a streaming workload. Scheduled processing of large files is a batch workload.

Why this answer

C is correct because GPS data ingested every 10 seconds is a continuous, near-real-time stream, making it a streaming workload. Monthly CSV file processing is a classic batch workload, as data is collected over a period and processed in a single, scheduled job. This distinction is fundamental in Azure data services: streaming workloads use services like Azure Stream Analytics or Event Hubs, while batch workloads use Azure Synapse Pipelines or Azure Data Factory.

Exam trap

The trap here is that candidates confuse the frequency of data arrival (every 10 seconds) with batch processing, not recognizing that continuous, low-latency ingestion defines a streaming workload, not just the presence of a schedule.

How to eliminate wrong answers

Option A is wrong because both workloads are not streaming; the monthly CSV processing is clearly a batch workload. Option B is wrong because it reverses the definitions: GPS data processing is streaming, not batch, and monthly report processing is batch, not streaming. Option D is wrong because both workloads are not batch; the GPS data ingestion is a streaming workload due to its continuous, low-latency nature.

111
MCQhard

A manufacturing company ingests a continuous stream of sensor data from factory equipment into Azure Event Hubs. Additionally, historical maintenance data in CSV format is stored in Azure Data Lake Storage Gen2. The analytics team needs to join the streaming sensor data with the historical data in near real-time and enable analysts to query the combined dataset using standard T-SQL without moving the data. Which Azure service should they use as the primary analytics platform?

A.A) Azure Stream Analytics
B.B) Azure Synapse Analytics with Synapse Pipelines and serverless SQL pool
C.C) Azure SQL Database
D.D) Azure Databricks
AnswerB

Correct. Synapse Pipelines can orchestrate the ingestion of both streaming (Event Hubs) and batch (Data Lake) data. The data can be stored in the lake, and the serverless SQL pool can query it using T-SQL, providing a unified analytics surface without moving data. This meets all requirements.

Why this answer

Azure Synapse Analytics with Synapse Pipelines and serverless SQL pool is the correct choice because it can ingest streaming data from Event Hubs via pipelines, query historical CSV data in Data Lake Storage Gen2 directly using T-SQL without moving it, and join both datasets in near real-time using the serverless SQL pool's ability to reference external data sources. This meets the requirement for standard T-SQL queries on combined streaming and historical data without data movement.

Exam trap

The trap here is that candidates often choose Azure Stream Analytics because it is the most obvious service for streaming data, but they overlook the requirement for standard T-SQL queries on combined datasets without data movement, which Stream Analytics cannot fulfill as it uses its own query language and cannot directly join with static data in Data Lake Storage Gen2 using T-SQL.

Why the other options are wrong

A

Azure Stream Analytics is designed for real-time stream processing but cannot directly join streaming data with static data in Azure Data Lake Storage Gen2 using T-SQL without moving data. It lacks the serverless SQL pool capability for querying historical data in place.

C

Azure SQL Database cannot query data in Azure Data Lake Storage Gen2 directly without moving it, and it does not natively support joining streaming data from Event Hubs in near real-time without additional services like Stream Analytics.

D

Azure Databricks does not support querying data in place with standard T-SQL; it primarily uses Spark SQL or Python. The requirement for analysts to use T-SQL without moving data is better met by Azure Synapse serverless SQL pool.

When would these options actually be correct?

A

Azure Stream Analytics would be correct if the question required real-time processing of streaming data only, with output to a sink like Power BI or Azure SQL Database, and did not require joining with static historical data or T-SQL querying of combined datasets.

C

A company needs a fully managed relational database for OLTP workloads with high availability and built-in intelligence, such as an e-commerce platform requiring transactional consistency for order processing.

D

Azure Databricks would be correct if the question required advanced machine learning on the combined dataset, or if the analytics team preferred using Spark-based languages (Python, Scala, SQL) and needed collaborative notebooks for data science workflows.

Why candidates pick the wrong answer

A

Candidates may choose Stream Analytics because it is a well-known service for processing streaming data from Event Hubs, and they might overlook the requirement to join with historical data and query via T-SQL without data movement.

C

Candidates may assume that T-SQL support implies a traditional SQL database, overlooking the requirement to query data in place without moving it and to handle streaming data.

D

Candidates may associate Databricks with real-time and batch processing on large datasets, overlooking the specific T-SQL requirement. Its strong data engineering capabilities make it seem suitable for joining streaming and historical data.

112
MCQhard

A financial analytics company has petabytes of transaction data stored as Parquet files in Azure Data Lake Storage Gen2. Data analysts need to run complex SQL queries that join multiple tables and return results within seconds. The company wants to query the data directly without moving it to another store. Which Azure service should they use?

A.Azure SQL Database
B.Azure Synapse Serverless SQL pool
C.Azure HDInsight
D.Azure Databricks
AnswerB

Azure Synapse Serverless SQL pool is purpose-built for on-demand T-SQL queries over data stored in Azure Data Lake Storage (ADLS) or other open formats like Parquet. It automatically scales compute resources to match the size and complexity of the query, eliminating capacity planning and enabling petabyte-scale transactional analytics without provisioning dedicated infrastructure. Because it reads data directly from the lake via a T-SQL endpoint, it provides a familiar relational interface while preserving the cost benefits of a serverless model.

Why this answer

Azure Synapse Serverless SQL pool is correct because it allows querying data directly from Azure Data Lake Storage Gen2 using T-SQL without moving or loading the data. It uses a distributed query engine that can process petabytes of Parquet files and return results in seconds by leveraging pushdown computation and columnar storage formats.

Exam trap

The trap here is that candidates often confuse Azure Synapse Serverless SQL pool with Azure SQL Database, assuming both require data movement, or they overcomplicate the solution by choosing a cluster-based service like HDInsight or Databricks when a serverless query engine is sufficient.

Why the other options are wrong

A

Azure SQL Database requires data to be loaded into its relational store, not querying Parquet files in Data Lake Storage Gen2 directly, and it cannot handle petabytes of data with the required performance for complex SQL queries without significant scaling and cost.

C

Azure HDInsight is designed for big data processing using Hadoop, Spark, or Hive, but it requires provisioning a cluster and is not optimized for low-latency SQL queries on Parquet files in Data Lake Storage Gen2. It also involves moving or processing data in a separate cluster, not querying directly with instant results.

D

Azure Databricks is optimized for big data analytics and machine learning using Apache Spark, but it does not provide a serverless SQL endpoint for directly querying data in Data Lake Storage without moving it. The requirement for complex SQL queries returning results in seconds is better met by Azure Synapse Serverless SQL pool, which offers T-SQL-based querying over data in the lake.

When would these options actually be correct?

A

For a scenario where a company needs a fully managed relational database with built-in high availability, automatic backups, and requires transactional consistency for OLTP workloads, such as an e-commerce application's order processing system.

C

An exam scenario where Azure HDInsight would be correct: A company needs to run batch ETL jobs on petabytes of unstructured data using custom MapReduce or Spark code, and they require full control over the cluster configuration and libraries.

D

Azure Databricks would be correct if the question required running advanced analytics, machine learning, or ETL pipelines using Spark, and the analysts needed to perform iterative data exploration or model training rather than just SQL queries. For example: 'A data science team needs to train a machine learning model on petabytes of transaction data using Python and Spark, with the ability to collaborate in notebooks.'

Why candidates pick the wrong answer

A

Candidates may assume that any SQL querying capability on Azure is provided by Azure SQL Database, overlooking the serverless SQL pool's ability to query data lake files directly without data movement.

C

Candidates may associate HDInsight with big data and Parquet files, overlooking that it is not a serverless SQL query service and does not provide sub-second query performance without data movement.

D

Candidates may associate Azure Databricks with big data and Parquet files, and mistakenly think its SQL capabilities (Spark SQL) are equivalent to a serverless SQL pool. They may overlook that Databricks requires a cluster to be running and is not optimized for low-latency SQL queries.

113
MCQhard

A manufacturer collects sensor data from thousands of IoT devices every second. The data is ingested into Azure Event Hubs and then needs to be stored for historical analysis. The analytics team will run complex aggregations and time-series queries over petabytes of data, expecting fast results even with large scans. Which Azure service should be used as the analytical data store?

A.Azure Data Lake Storage Gen2
B.Azure SQL Database
C.Azure Synapse Analytics dedicated SQL pool
D.Azure Cosmos DB
AnswerC

Azure Synapse Analytics dedicated SQL pool is a purpose-built enterprise data warehouse that uses massively parallel processing (MPP) to distribute tables across decoupled storage and compute nodes. Each node stores compressed columnar (columnstore) data, which minimizes I/O during large analytical scans and aggregations. The query optimizer breaks complex queries into parallel tasks operating across all nodes, so even petabyte-scale IoT telemetry can be joined, filtered, and aggregated efficiently. This architecture is exactly why it is the recommended service for large-scale analytical workloads in Azure.

Why this answer

Azure Synapse Analytics dedicated SQL pool is the correct choice because it is a massively parallel processing (MPP) engine designed for petabyte-scale data warehousing. It can run complex aggregations and time-series queries with fast results by distributing data across 60 distributions and using columnstore indexes for high compression and scan efficiency.

Exam trap

The trap here is that candidates confuse Azure Data Lake Storage Gen2 (a storage layer) with a query engine, assuming it can directly perform fast analytical queries, when in fact it requires a compute service like Synapse or Spark on top.

Why the other options are wrong

A

Azure Data Lake Storage Gen2 is a storage service, not an analytical query engine. It lacks the ability to run complex aggregations and time-series queries with fast results over petabytes of data without additional compute services like Azure Synapse or Databricks.

D

Azure Cosmos DB is a NoSQL database optimized for low-latency, transactional workloads and real-time applications, not for complex aggregations and time-series queries over petabytes of data with fast large-scan performance.

When would these options actually be correct?

A

A question asks for a cost-effective storage layer for raw sensor data that will be processed later by separate analytics tools, with no requirement for built-in query performance or complex aggregations directly on the stored data.

D

An exam scenario where the requirement is for a globally distributed, low-latency, always-on NoSQL database for an IoT application that needs real-time data access and high throughput for point reads and writes, with no need for complex analytical queries.

Why candidates pick the wrong answer

A

Candidates may confuse Data Lake Storage Gen2 with a full analytics solution because it is often used as the storage backend for big data analytics, overlooking that it does not provide native query capabilities for complex time-series analysis.

D

Candidates may think Cosmos DB's scalability and performance suit large IoT datasets, but they overlook that it is designed for operational workloads, not analytical processing like complex aggregations and large scans.

114
Multi-Selectmedium

Which TWO of the following are valid use cases for Azure Queue Storage?

Select 2 answers
A.Building a serverless workflow with Azure Functions
B.Storing JSON documents for querying
C.Storing large binary objects for a website
D.Decoupling front-end and back-end components in a web application
E.Real-time event streaming for analytics
AnswersA, D

Building a serverless workflow with Azure Functions is a valid Queue Storage use case because a queue's messages can trigger Function execution through the Queue trigger binding. This pattern lets you stage work items durably, with visibility timeouts and poison-message handling, while Azure Functions scales automatically to process the queue, enabling a reliable event-driven pipeline.

Why this answer

Azure Queue Storage is a service for storing large numbers of messages that can be accessed from anywhere via authenticated calls. It is commonly used to decouple application components and enable asynchronous processing. Option A is correct because Azure Queue Storage can trigger Azure Functions to build serverless workflows.

Option D is correct because it is designed to decouple front-end and back-end components by passing messages between them. Option B is incorrect because storing JSON documents for querying is better suited for Cosmos DB or Table Storage. Option C is incorrect because storing large binary objects is handled by Azure Blob Storage.

Option E is incorrect because real-time event streaming is typically done with Azure Event Hubs or Azure Stream Analytics.

115
MCQhard

A company runs a critical workload in Azure Synapse Analytics. They need to ensure that if a single node fails, the data in the control node and compute nodes is not lost. Which configuration should they use?

A.Deploy a SQL Server Always On availability group.
B.No additional configuration is needed; Synapse provides built-in fault tolerance.
C.Configure active geo-replication.
D.Use a Windows Server Failover Cluster.
AnswerB

Azure Synapse Analytics provides built-in fault tolerance at the platform level: the control node is paired with a standby node that automatically takes over if a failure occurs, and data in the dedicated SQL pool is stored in Azure Storage with multiple synchronous copies for durability. Compute nodes are stateless and can be rebuilt from metadata and storage, so a single node failure does not cause data loss or long downtime. No additional configuration, such as external clustering or replication, is required to achieve the service's default high-availability guarantees.

Why this answer

Azure Synapse Analytics (formerly SQL DW) is a distributed MPP (Massively Parallel Processing) system that automatically replicates data across multiple internal copies within the control node and compute nodes. This built-in fault tolerance ensures that if a single node fails, data is not lost because Synapse maintains at least three synchronous replicas of all data and metadata. No additional configuration is required for node-level failure protection.

Exam trap

The trap here is that candidates confuse the need for high availability with disaster recovery, assuming that because Synapse is a distributed system, they must manually configure clustering or replication, when in fact Synapse provides built-in fault tolerance at the node level as a core feature of the service.

How to eliminate wrong answers

Option A is wrong because SQL Server Always On availability groups are designed for traditional SQL Server instances, not for Azure Synapse Analytics, which is a PaaS service with its own internal high-availability mechanisms. Option C is wrong because active geo-replication is a disaster recovery feature for Azure SQL Database that replicates data to a different Azure region, not a solution for single-node failure within the same Synapse workspace. Option D is wrong because Windows Server Failover Cluster is an on-premises clustering technology for SQL Server and other applications, and it cannot be applied to Azure Synapse Analytics, which is a fully managed cloud service.

116
MCQeasy

You need to store semi-structured JSON documents from a web application in Azure. The data will be accessed by a key/value lookup. Which Azure data store should you use?

A.Azure Blob Storage
B.Azure Table Storage
C.Azure Cosmos DB
D.Azure SQL Database
AnswerC

Azure Cosmos DB is a globally distributed, multi-model NoSQL database that natively stores JSON documents as its core data format, supporting both document queries through a SQL-like API and fast key-value lookups via point reads. It automatically indexes every property within a JSON document, enabling efficient queries on nested fields, and offers tunable consistency and throughput scaling, making it ideal for semi-structured web data. This native JSON support and flexible querying directly match the requirement to store and access semi-structured JSON documents by key/value.

Why this answer

Azure Cosmos DB is the correct choice because it natively supports semi-structured JSON documents and provides key/value lookup via its partition key mechanism. It offers single-digit millisecond latency for point reads, making it ideal for web application data that needs fast, scalable access by a unique key.

Exam trap

The trap here is that candidates often confuse Azure Table Storage's key/value capabilities with JSON document support, but Table Storage stores flat entities, not nested JSON, and lacks native indexing for document fields.

How to eliminate wrong answers

Option A is wrong because Azure Blob Storage is designed for unstructured binary or text data (like images, videos, or logs), not for semi-structured JSON documents with key/value access patterns; it lacks native querying for individual document fields. Option B is wrong because Azure Table Storage stores structured, schema-less entities (rows of properties) but does not natively support JSON documents; it uses OData for queries, not direct key/value lookup on JSON fields. Option D is wrong because Azure SQL Database is a relational database that requires a fixed schema and uses SQL for queries, making it overkill and less efficient for simple key/value lookups on semi-structured JSON compared to Cosmos DB's native document model.

117
Drag & Dropmedium

Drag and drop the steps to create an Azure Data Lake Storage Gen2 account in the correct order.

Drag steps to the numbered slots on the right, or tap a step then tap a slot.

Steps
Order
1Step 1
2Step 2
3Step 3
4Step 4

Why this order

Creating ADLS Gen2 requires enabling the hierarchical namespace feature on a standard storage account.

118
MCQmedium

A data engineering team needs to transform raw clickstream data stored as Parquet files in Azure Data Lake Storage Gen2. They want to use standard T-SQL queries to perform transformations and aggregations. The team prefers a serverless option to avoid provisioning and managing dedicated compute resources. Which Azure service should they use?

A.Azure Synapse Serverless SQL pool
B.Azure Synapse Dedicated SQL pool
C.Azure Databricks
D.Azure HDInsight
AnswerA

This serverless option enables querying data lake files with T-SQL on-demand, without provisioning compute resources, aligning with the team's requirements.

Why this answer

Azure Synapse Serverless SQL pool is correct because it allows querying Parquet files in Azure Data Lake Storage Gen2 using standard T-SQL without provisioning any dedicated compute resources. It automatically scales compute based on query demand, making it ideal for ad-hoc transformations and aggregations on raw data with a serverless, pay-per-query model.

Exam trap

The trap here is that candidates may confuse 'serverless' with any cloud service that can run SQL, but only Azure Synapse Serverless SQL pool provides T-SQL support without provisioning compute, while Databricks and HDInsight require cluster management and use non-T-SQL query languages.

Why the other options are wrong

B

Azure Synapse Dedicated SQL pool requires provisioning and managing dedicated compute resources, which contradicts the team's preference for a serverless option to avoid such overhead.

C

Azure Databricks does not support standard T-SQL queries; it uses Spark SQL or Python/Scala for transformations, not T-SQL. The question explicitly requires standard T-SQL, making Databricks incorrect.

D

Azure HDInsight requires provisioning and managing a cluster (dedicated compute), which contradicts the team's preference for a serverless option. It also does not support standard T-SQL queries natively; it uses Spark, Hive, or other big data frameworks.

When would these options actually be correct?

B

A question where the team needs consistent, high-performance T-SQL querying with predictable workloads and is willing to provision and manage dedicated compute resources, such as for large-scale data warehousing with complex queries.

C

A data science team needs to perform advanced analytics and machine learning on raw clickstream data using Apache Spark, with collaborative notebooks and automated cluster management. They prefer a serverless option that supports Python, Scala, and SQL.

D

A question where the team needs to run custom MapReduce jobs or use open-source frameworks like Spark, Hive, or HBase on a managed Hadoop cluster, and they are willing to manage dedicated compute resources.

Why candidates pick the wrong answer

B

Candidates may confuse Dedicated SQL pool with serverless options, or assume that all Synapse SQL pools are serverless, not realizing the key difference in resource management.

C

Candidates may associate Azure Databricks with serverless data transformation and overlook the T-SQL requirement, assuming it supports standard SQL queries.

D

Candidates may associate HDInsight with big data processing on Azure and overlook the serverless and T-SQL requirements, thinking any Hadoop-based service can handle Parquet files.

119
MCQmedium

A company has 15 on-premises SQL Server databases, each 20–40 GB, running on a single instance. They rely on cross-database queries using three-part names (e.g., DB1.dbo.table) and SQL Server Agent for maintenance. They want to migrate to Azure with minimal application changes and reduce administrative overhead. Which Azure SQL deployment option should they choose?

A.Azure SQL Database elastic pool
B.Azure SQL Database single database
C.Azure SQL Managed Instance
D.SQL Server on Azure Virtual Machines
AnswerC

Azure SQL Managed Instance is the correct choice because it is a PaaS offering that provides near-100% compatibility with on-premises SQL Server, including instance-level features such as cross-database queries using three-part names, SQL Server Agent, linked servers, and CLR. You get the administrative benefits of Azure (automated backups, patching, and high availability) without sacrificing the instance-scoped functionality these 15 databases likely rely on. This makes it ideal for lift-and-shift migrations where you need reduced overhead but full SQL Server instance behavior.

Why this answer

Azure SQL Managed Instance is correct because it provides near-100% compatibility with on-premises SQL Server, including support for cross-database queries using three-part names and SQL Server Agent. This allows the company to migrate with minimal application changes while offloading administrative overhead like patching and backups, which are handled by Azure.

Exam trap

The trap here is that candidates often confuse Azure SQL Database's elastic pool with Managed Instance, not realizing that elastic pools still lack cross-database query support and SQL Server Agent, which are critical for the described workload.

Why the other options are wrong

A

Azure SQL Database (single or elastic pool) does not support cross-database queries using three-part names, which the company relies on. It also lacks SQL Server Agent, so maintenance jobs would need to be reimplemented.

B

Azure SQL Database single database does not support cross-database queries using three-part names or SQL Server Agent, so it cannot meet the requirements for minimal application changes and maintenance automation.

D

SQL Server on Azure VMs requires you to manage the OS, SQL Server, and backups, increasing administrative overhead. It also does not natively support cross-database queries with three-part names as seamlessly as Azure SQL Managed Instance, and you would need to configure linked servers or other workarounds.

When would these options actually be correct?

A

A company has multiple small databases (e.g., under 2 GB each) with low and variable usage patterns, and they do not require cross-database queries or SQL Agent. They want to minimize cost and get built-in high availability. An elastic pool would be correct.

B

A scenario where an application uses a single database (no cross-database queries) and does not require SQL Server Agent, such as a new cloud-native app with independent databases, and the goal is to minimize cost and management overhead.

D

This option is correct when the question specifies a need for full control over the SQL Server environment, such as requiring custom configurations, third-party agents, or legacy dependencies that are not supported in PaaS offerings, and when the team is willing to handle OS and SQL Server maintenance.

Why candidates pick the wrong answer

A

Candidates may think elastic pool reduces administrative overhead and cost, but overlook the critical requirement for cross-database queries and SQL Agent, which are not supported in Azure SQL Database.

B

Candidates may think Azure SQL Database is the default PaaS choice for reducing administrative overhead, overlooking the specific need for cross-database queries and agent jobs that are only available in Managed Instance.

D

Candidates may think that migrating to VMs is the simplest lift-and-shift approach with minimal application changes, overlooking the administrative overhead and the fact that Azure SQL Managed Instance provides similar compatibility with less management.

120
MCQmedium

Your company has a data pipeline in Azure Data Factory that runs daily. Recently, the pipeline started failing with timeouts. You suspect a downstream database is slow. What should you do to monitor and alert on pipeline run duration?

A.Configure alerts in Azure Monitor based on pipeline run duration
B.Query the pipeline runs in Log Analytics
C.Check Azure Service Health for issues
D.Use Azure Advisor to check performance recommendations
AnswerA

Azure Data Factory pipeline runs emit metrics such as PipelineRun Succeeded, PipelineRun Failed, and PipelineRun Duration to Azure Monitor. You can create a metric alert rule that continuously evaluates this duration and triggers an action—like an email, webhook, or ITSM ticket—when a threshold is exceeded. This is a real-time, proactive monitoring approach because Azure Monitor collects these metrics natively without requiring any additional log configuration.

Why this answer

Azure Monitor can be configured to create alerts based on metrics such as pipeline run duration. When the duration exceeds a threshold, an alert triggers, allowing proactive notification of slow downstream databases. This directly addresses the need to monitor and alert on pipeline performance issues.

Exam trap

The trap here is that candidates may confuse reactive troubleshooting tools (like Log Analytics queries) with proactive monitoring and alerting capabilities, or mistakenly think Azure Service Health or Advisor are designed for pipeline-specific performance alerts.

How to eliminate wrong answers

Option B is wrong because querying pipeline runs in Log Analytics provides historical data for analysis but does not set up proactive alerts; it is a reactive troubleshooting step, not a monitoring and alerting solution. Option C is wrong because Azure Service Health reports on Azure platform outages and service issues, not on the performance or duration of specific data factory pipelines. Option D is wrong because Azure Advisor provides recommendations for optimizing performance and costs, but it does not monitor real-time pipeline run duration or trigger alerts based on thresholds.

121
MCQhard

You are a data architect at a global retail company. The company has an Azure Data Lake Storage Gen2 account that stores petabytes of clickstream data. They need to provide near real-time analytics dashboards for regional managers. The data arrives in batches every 5 minutes. Currently, they use Azure Databricks to transform the data and load it into Azure Synapse Analytics, but the dashboards show data that is 30 minutes old. The business requires dashboards to reflect data within 10 minutes of ingestion. You propose a new solution. Which approach should you recommend?

A.Keep current pipeline but replace Synapse with Azure Analysis Services for faster query performance.
B.Use Azure Data Factory with tumbling window triggers every 5 minutes to load data from Data Lake to Synapse.
C.Ingest data into Azure Event Hubs, use Azure Stream Analytics to process and output to Power BI for real-time dashboards.
D.Increase the number of Databricks clusters and use Auto Loader to speed up transformations.
AnswerC

Event Hubs captures telemetry continuously and Stream Analytics runs persistent SQL-style queries over that stream, producing low-latency results directly in Power BI's streaming dataset. Because processing begins as events arrive and there is no dependency on file batches, dashboards typically reflect updates within seconds rather than minutes. This satisfies the 10-minute freshness SLA and provides the real-time visualization experience the business expects.

Why this answer

It uses Azure Event Hubs for low-latency ingestion and Azure Stream Analytics for real-time processing, enabling near real-time dashboards in Power BI with sub-minute latency. This architecture bypasses the batch-oriented pipeline that causes the current 30-minute delay, meeting the 10-minute requirement.

Exam trap

The trap here is that candidates may assume batch tools like Data Factory or Databricks can be tuned to meet near real-time SLAs, but they fundamentally operate on file-based or micro-batch paradigms that cannot match the sub-minute latency of a true streaming pipeline with Event Hubs and Stream Analytics.

How to eliminate wrong answers

Option A is wrong because replacing Synapse with Azure Analysis Services does not address the root cause of latency—the batch processing in Databricks—and Analysis Services is an OLAP engine that still requires data to be loaded, not a streaming solution. Option B is wrong because Azure Data Factory with tumbling window triggers is a batch-oriented orchestration tool that introduces inherent latency from window scheduling and data movement, failing to achieve sub-10-minute freshness. Option D is wrong because increasing Databricks clusters and using Auto Loader only accelerates the batch transformation step but does not eliminate the fundamental batch processing delay, and Auto Loader still operates on file arrival, not streaming.

122
MCQhard

Your organization stores sensitive financial data in Azure SQL Database. You need to audit all SELECT operations on the 'Transactions' table and alert the security team when a user outside the finance department queries the table. Which feature should you use?

A.Microsoft Defender for SQL
B.Dynamic Data Masking
C.SQL Server Auditing
D.Transparent Data Encryption
AnswerC

SQL Server Auditing is the correct choice because it tracks database events by writing audit entries to a designated destination such as Azure Blob Storage or Azure Log Analytics. You can define database-level audit specifications that include SELECT actions on specific financial tables, capturing the user principal, the exact T-SQL statement, and the timestamp of each access. These audit records can then be integrated with alerting rules to notify administrators about unusual queries against sensitive tables. This provides both a granular access log and a trigger mechanism, fulfilling the requirement in the scenario.

Why this answer

SQL Server Auditing is the correct choice because it tracks database events, including SELECT operations, and writes them to an audit log. You can configure an audit policy to capture all SELECT statements on the 'Transactions' table and then set up an alert (e.g., via Azure Monitor or Logic Apps) that triggers when a user from outside the finance department executes such a query. This directly meets the requirement to both audit and alert on specific user actions.

Exam trap

The trap here is that candidates often confuse auditing (logging who did what) with security features that protect data at rest or in transit, such as TDE or Dynamic Data Masking, which do not provide any logging or alerting capabilities.

How to eliminate wrong answers

Option A is wrong because Microsoft Defender for SQL provides vulnerability assessments, threat detection, and anomaly alerts, but it does not offer granular auditing of specific table-level SELECT operations or user-based alerting. Option B is wrong because Dynamic Data Masking obfuscates sensitive data in query results to unauthorized users, but it does not log or alert on who performed the query. Option D is wrong because Transparent Data Encryption (TDE) encrypts the database at rest and on backup media, but it provides no auditing or alerting capabilities for data access operations.

123
MCQmedium

A smart city application collects sensor data from thousands of devices. Data is ingested as JSON messages containing deviceId, timestamp, and reading value. The application must support fast point reads by deviceId and also run queries to retrieve all readings for a specific deviceId within a time range. The development team prefers a SQL-like query language. Which Azure Cosmos DB API should they choose?

A.SQL (Core) API
B.Table API
C.MongoDB API
D.Cassandra API
AnswerA

The SQL (Core) API is the correct answer for this smart city workload because it is Azure Cosmos DB's native document model, storing sensor JSON telemetry directly and providing an ANSI-SQL-like query syntax. This API supports efficient point reads via id and partition key, and it indexes all fields by default, so you can run fast range filters such as timestamp BETWEEN ... AND ... to retrieve a sensor's data over a specific time window. Given the team is already comfortable with SQL-like language, this is the most natural fit.

Why this answer

The SQL (Core) API is the best choice because it natively supports SQL-like querying, enabling both fast point reads by deviceId (using the partition key) and efficient time-range queries on a specific deviceId. It also provides native JSON support, which aligns with the JSON message format from the sensors, and allows indexing on timestamp for range queries.

Exam trap

Microsoft often tests the misconception that any API with a SQL-like name (like Cassandra's CQL) is equivalent to the SQL (Core) API, but the key differentiator is native JSON support and the specific query language syntax preferred by the team.

Why the other options are wrong

B

The Table API uses OData and RESTful queries, not SQL-like query language, and is optimized for key-value lookups, not efficient time-range queries on a secondary field like timestamp.

C

The MongoDB API uses a MongoDB query language, not SQL. The question explicitly requires a SQL-like query language, making the MongoDB API unsuitable.

D

The Cassandra API uses CQL (Cassandra Query Language), not SQL, and is optimized for high-throughput writes and partition-based queries, not for efficient time-range queries on a specific partition key without additional indexing considerations.

When would these options actually be correct?

B

A question where the application only needs fast point reads by a single partition key (e.g., deviceId) with no requirement for range queries or SQL-like syntax, and the team prefers a simple key-value interface.

C

A question where the application uses an existing MongoDB client library, requires document storage with flexible schema, and needs to support geospatial queries or aggregation pipelines that are native to MongoDB. The team prefers a NoSQL query language over SQL.

D

A scenario where the application requires a distributed, highly available NoSQL database with a wide-column store, needs to handle massive write throughput, and the queries are primarily by partition key (e.g., deviceId) with eventual consistency, and the team prefers a CQL-like query language.

Why candidates pick the wrong answer

B

Candidates may confuse the Table API's simple schema and key-based access with the need for fast point reads, overlooking the requirement for SQL-like queries and time-range filtering.

C

Candidates may associate JSON data with MongoDB, as MongoDB stores documents in BSON (similar to JSON), and mistakenly think its query language is SQL-like or that it supports SQL queries.

D

Candidates may confuse Cassandra's CQL with SQL, or think that because Cassandra supports time-series data and queries by partition key, it would be suitable for this use case, overlooking the specific need for SQL-like query language and efficient time-range queries.

124
MCQeasy

A small business wants to use Azure to store and analyze customer feedback from surveys. The surveys are collected via a web app and stored as JSON files. The business needs to run SQL-based queries on the data and generate reports in Power BI. They have a limited budget and prefer a serverless option to minimize management overhead. Which Azure service should they use?

A.Azure Analysis Services
B.Azure Databricks
C.Azure Synapse Analytics serverless SQL pool
D.Azure SQL Database
AnswerC

Azure Synapse Analytics serverless SQL pool lets you run T-SQL queries directly against JSON and other files in Azure Data Lake Storage with no dedicated compute to provision—you pay only for the amount of data read per query. It can parse JSON automatically using OPENJSON and standard T-SQL, making it a natural fit for storing and exploring customer data in a data lake. Because no cluster or database is pre-provisioned, it is the cheapest, lowest-management option here.

Why this answer

Azure Synapse Analytics serverless SQL pool is the correct choice because it allows you to query JSON files directly from Azure Data Lake Storage or Blob Storage using standard T-SQL, without provisioning any infrastructure. It is serverless (pay-per-query), supports SQL-based queries, and integrates seamlessly with Power BI for reporting, making it ideal for a small business with a limited budget and minimal management overhead.

Exam trap

The trap here is that candidates often confuse 'serverless' with 'fully managed' and choose Azure SQL Database (which is managed but not serverless in the pay-per-query sense) or Azure Databricks (which is serverless but requires Spark expertise and is not SQL-native), missing that Azure Synapse serverless SQL pool is the only option that combines serverless billing, direct JSON querying, and SQL-based reporting for Power BI.

How to eliminate wrong answers

Option A is wrong because Azure Analysis Services is a fully managed analytical engine that requires provisioning and managing a model, and it is not designed for direct querying of raw JSON files; it is used for building tabular or multidimensional models from pre-processed data. Option B is wrong because Azure Databricks is a big data analytics platform based on Apache Spark, which is overkill for simple SQL queries on JSON files and incurs cluster management costs even in serverless mode; it is not optimized for ad-hoc SQL queries on semi-structured data. Option D is wrong because Azure SQL Database is a fully managed relational database that requires provisioning a database instance and schema, and it is not serverless in the sense of pay-per-query; it incurs ongoing costs even when idle and requires importing JSON data into tables before querying.

125
MCQeasy

Refer to the exhibit. This ARM template snippet is used to deploy which Azure resource?

A.Azure Database for MySQL server
B.Azure SQL Managed Instance
C.Azure SQL Database server
D.Azure Synapse Analytics workspace
AnswerC

The ARM template snippet is a match for an Azure SQL Database logical server. The `Microsoft.Sql/servers` resource type uses exactly the properties shown: a server name, `administratorLogin`, `administratorLoginPassword`, and a location. A logical server is a management container for SQL databases and elastic pools, and this template is the standard way to provision that container before adding databases, firewall rules, or other child resources.

Why this answer

The ARM template snippet includes properties such as 'serverName', 'administratorLogin', and 'administratorLoginPassword', which are specific to an Azure SQL Database server deployment. Option A is incorrect because an Azure Database for MySQL server uses different properties like 'mysqlVersion' and 'storageProfile'. Option B is incorrect because Azure SQL Managed Instance requires properties like 'vCores', 'storageSizeInGB', and 'licenseType'.

Option D is incorrect because Azure Synapse Analytics workspace deployments use properties like 'sqlAdministratorLogin' and 'defaultDataLakeStorage' but with different structure.

126
MCQmedium

A SaaS company hosts a multi-tenant application. Each tenant has a separate Azure SQL Database. The databases are small (1-3 GB) and their workloads vary significantly over time, with some tenants active during business hours and others at night. The company wants to maximize resource utilization and minimize costs by pooling compute resources across tenants while maintaining predictable performance per database. Which Azure SQL Database deployment option should they choose?

A.Azure SQL Database Single Database
B.Azure SQL Database Elastic Pool
C.Azure SQL Managed Instance
D.SQL Server on Azure Virtual Machine
AnswerB

An elastic pool allows multiple databases to share a pool of resources. Databases automatically use resources as needed, maximizing utilization and lowering cost while providing predictable performance per database via settings like min and max vCores.

Why this answer

Azure SQL Database Elastic Pool is the correct choice because it allows multiple databases (tenants) to share a fixed pool of compute and storage resources, enabling cost efficiency through resource pooling while providing predictable performance via per-database resource limits (min/max DTU or vCore). This matches the scenario of small databases with variable, non-overlapping workloads across tenants.

Exam trap

The trap here is that candidates may choose Single Database (Option A) thinking it offers the best isolation, but they overlook the cost and resource utilization benefits of Elastic Pool for variable, non-overlapping workloads across many small databases.

Why the other options are wrong

A

Single Database does not allow pooling compute resources across tenants; each database is isolated with its own DTU/vCore allocation, leading to underutilization and higher costs for variable, small workloads.

C

Azure SQL Managed Instance provides near 100% SQL Server compatibility and is designed for lift-and-shift migrations, not for pooling compute resources across multiple small databases with variable workloads. It does not offer the elastic pooling capability to share resources among tenants.

D

SQL Server on Azure VM requires manual management of compute resources and does not provide built-in multi-tenant pooling or elastic scaling across databases. It also incurs higher operational overhead and cost for small, variable workloads compared to an elastic pool.

When would these options actually be correct?

A

A question where each tenant requires guaranteed, isolated performance with no resource sharing, such as a financial application with strict compliance requiring separate performance tiers per database.

C

A company needs to migrate an existing on-premises SQL Server application to Azure with minimal changes, requiring high compatibility with SQL Server features like SQL Agent, CLR, or cross-database queries, and does not need to pool resources across databases.

D

This option is correct when the question specifies a need for full SQL Server instance-level control, custom OS configurations, or legacy application compatibility that cannot be met by PaaS offerings, such as requiring SQL Server Agent jobs, CLR integration, or specific Windows features.

Why candidates pick the wrong answer

A

Candidates may think 'separate database' means Single Database is the natural choice, overlooking Elastic Pool's ability to share resources across multiple databases for cost efficiency.

C

Candidates may think Managed Instance is a good middle ground between single databases and VMs, but they overlook that it lacks the elastic pool feature for resource sharing, which is key for multi-tenant cost optimization.

D

Candidates may think that running SQL Server on a VM offers maximum flexibility and control, and might assume it can be configured to pool resources across tenants, overlooking the management overhead and lack of built-in elastic pooling features.

127
MCQmedium

You need to design a real-time dashboard that displays the number of orders placed in the last hour from an e-commerce application. The application writes orders to Azure Event Hubs. Which Azure service should you use to aggregate the data and serve the dashboard with minimal latency?

A.Azure Databricks Structured Streaming
B.Azure Stream Analytics with Power BI output
C.Azure Analysis Services
D.Azure Data Factory with tumbling window
AnswerB

Azure Stream Analytics is a fully managed streaming analytics service that consumes events from sources like Event Hubs or IoT Hub and applies SQL-like queries for windowed aggregations with sub-second to second latency. It provides a native Power BI output connector that updates dashboards in near real-time without custom code, and the service handles scaling and checkpointing automatically. This makes it the most direct and cost-effective solution for a real-time dashboard with simple aggregate metrics.

Why this answer

Azure Stream Analytics is purpose-built for real-time data processing from sources like Event Hubs, and its native integration with Power BI enables direct output to a dashboard with sub-second latency. This combination provides the minimal-latency aggregation and serving required for a real-time orders dashboard without additional infrastructure.

Exam trap

The trap here is that candidates may confuse real-time processing with batch-oriented services like Azure Data Factory or assume that any big data platform (like Databricks) is automatically the best choice for low-latency dashboards, overlooking the purpose-built streaming-to-visualization pipeline of Stream Analytics and Power BI.

How to eliminate wrong answers

Option A is wrong because Azure Databricks Structured Streaming, while capable of real-time processing, introduces additional overhead for cluster management and is not optimized for direct dashboard serving with minimal latency compared to Stream Analytics. Option C is wrong because Azure Analysis Services is an OLAP engine for historical data analysis and cannot process real-time streaming data from Event Hubs. Option D is wrong because Azure Data Factory with tumbling window is designed for batch processing on a schedule, not for real-time streaming aggregation and low-latency dashboard updates.

128
MCQmedium

The exhibit shows a SQL query run against Azure SQL Database. What is the purpose of the HAVING clause in this query?

A.To filter rows before grouping
B.To sort the result set
C.To join two tables
D.To filter groups based on aggregate conditions
AnswerD

HAVING filters groups after GROUP BY using aggregate functions.

Why this answer

The HAVING clause in SQL is used to filter groups after the GROUP BY clause has been applied, based on aggregate conditions such as SUM, COUNT, or AVG. In this query against Azure SQL Database, HAVING restricts the result to only those groups that satisfy the specified aggregate condition, which cannot be done with a WHERE clause because WHERE filters individual rows before grouping.

Exam trap

The trap here is that candidates often confuse HAVING with WHERE, mistakenly thinking HAVING can filter individual rows before grouping, when in fact WHERE must be used for that purpose.

How to eliminate wrong answers

Option A is wrong because the WHERE clause, not HAVING, is used to filter rows before grouping; HAVING operates after grouping. Option B is wrong because sorting is performed by the ORDER BY clause, not HAVING. Option C is wrong because joining tables is done with JOIN (e.g., INNER JOIN, LEFT JOIN) in the FROM clause, not with HAVING.

129
Drag & Dropmedium

Drag and drop the steps to create an Azure Stream Analytics job in the correct order.

Drag steps to the numbered slots on the right, or tap a step then tap a slot.

Steps
Order
1Step 1
2Step 2
3Step 3
4Step 4

Why this order

Creating a Stream Analytics job involves setting up the job parameters, then configuring inputs, outputs, and the transformation query.

130
MCQeasy

A company stores weather sensor data in Azure Data Lake Storage Gen2. Data scientists need to run large-scale transformations and machine learning experiments on this data using Python and Apache Spark. They want to collaborate using shared Jupyter notebooks. Which Azure service should they use for this analytical workload?

A.Azure Stream Analytics
B.Azure Synapse Analytics (with Apache Spark pools)
C.Azure Analysis Services
D.Azure SQL Database
AnswerB

Azure Synapse Analytics with Apache Spark pools is the correct choice because it provides fully managed, distributed Apache Spark compute tightly integrated with Azure Data Lake Storage Gen2. Data scientists can use Spark notebooks to interactively explore weather sensor data, perform scalable data transformations in PySpark or Scala, and build machine learning pipelines directly from the lake. This serverless-style cluster model is purpose-built for big data analytics and ML workloads on historical files, which is exactly what this scenario requires.

Why this answer

Azure Synapse Analytics with Apache Spark pools provides a fully managed Spark environment that supports Python and allows data scientists to run large-scale transformations and machine learning experiments. It integrates directly with Azure Data Lake Storage Gen2 for reading and writing data, and supports collaborative Jupyter notebooks for shared development. This makes it the correct choice for the described analytical workload.

Exam trap

The trap here is that candidates may confuse Azure Stream Analytics (a real-time processing service) with batch processing, or think Azure SQL Database can handle large-scale Spark workloads, when in fact only Synapse Analytics with Spark pools provides the required Python, Spark, and collaborative notebook capabilities.

Why the other options are wrong

A

Azure Stream Analytics is designed for real-time stream processing, not for large-scale batch transformations and machine learning experiments on stored data using Python and Apache Spark.

C

Azure Analysis Services is an OLAP engine for semantic modeling and business reporting, not for running large-scale transformations or machine learning experiments with Python and Apache Spark. It lacks native Spark integration and Jupyter notebook support.

D

Azure SQL Database is a relational database service for OLTP workloads, not designed for large-scale transformations or machine learning with Apache Spark and Jupyter notebooks.

When would these options actually be correct?

A

A company needs to analyze real-time streaming data from IoT devices and perform simple aggregations or pattern matching without complex ML. The question would specify 'real-time' or 'streaming' data and not mention Spark or Jupyter notebooks.

C

A company needs to create a tabular semantic model for business intelligence reporting from a data warehouse, and users require fast, interactive queries on aggregated data using tools like Power BI. Azure Analysis Services would be the correct choice for this analytical workload.

D

A question asks for a managed relational database service to store structured data from an e-commerce application with high transaction throughput and requires built-in high availability.

Why candidates pick the wrong answer

A

Candidates may confuse stream processing with batch processing or think that 'analytics' implies any analytical workload, overlooking the specific requirements for Spark and Jupyter collaboration.

C

Candidates may confuse 'analytical workload' with any data analysis service, and the name 'Analysis Services' sounds like it could handle analytics, leading them to overlook the specific requirements for Spark, Python, and Jupyter notebooks.

D

Candidates may confuse Azure SQL Database's support for in-database analytics with the need for big data processing, overlooking its lack of native Spark and Jupyter integration.

131
Multi-Selecteasy

Which TWO security features are available in Azure SQL Database to help protect data at rest?

Select 2 answers
A.Firewall rules
B.Dynamic data masking
C.Transparent data encryption (TDE)
D.Azure AD authentication
E.Always Encrypted
AnswersC, E

Transparent data encryption (TDE) encrypts the actual database files (data and log files) at rest by performing real-time I/O encryption and decryption of pages as they are written to and read from disk. The encryption uses a database encryption key that is protected by a certificate or asymmetric key stored either in Azure Key Vault or managed by the service. This operation is transparent to applications, requiring no changes to application code or queries, and explicitly provides the data-at-rest encryption that the question is asking about.

Why this answer

Transparent Data Encryption (TDE) is a feature in Azure SQL Database that encrypts data at rest, including backups and transaction log files, using an AES-256 encryption algorithm. It performs real-time I/O encryption and decryption of the data without requiring changes to the application, ensuring that the physical storage media is protected against unauthorized access. Always Encrypted also protects data at rest by encrypting sensitive columns within the database, but it additionally protects data in transit and during query processing by keeping the encryption keys on the client side.

Exam trap

The trap here is that candidates often confuse dynamic data masking with encryption, or assume that authentication or network controls (like firewall rules) provide data-at-rest protection, when in fact only encryption mechanisms like TDE and Always Encrypted directly secure data stored on disk.

132
MCQmedium

A multinational e-commerce company uses Azure SQL Database for its order processing system. They need to ensure that if an entire Azure region becomes unavailable, the database remains available with minimal data loss and automatic failover. Which feature should they implement?

A.Active geo-replication
B.Automatic tuning
C.Elastic pools
D.Serverless compute
AnswerA

Active geo-replication continuously replicates committed transactions from the primary Azure SQL database to a secondary database in a different Azure region using asynchronous Always On technology. It is the intended disaster-recovery feature because it maintains a readable warm standby that can be promoted during an outage, and when paired with an auto-failover group it can switch customer traffic automatically. This directly addresses the requirement for cross-region availability in a multinational e-commerce deployment.

Why this answer

Active geo-replication (Option A) is correct because it creates readable secondary replicas of an Azure SQL Database in a paired Azure region, enabling automatic failover during a regional outage. This feature provides a recovery point objective (RPO) of as low as 5 seconds and a recovery time objective (RTO) of under 1 hour, ensuring minimal data loss and high availability.

Exam trap

The trap here is that candidates may confuse 'automatic tuning' (a performance feature) with 'automatic failover' (a disaster recovery feature), or assume that serverless compute or elastic pools inherently provide high availability, which they do not.

How to eliminate wrong answers

Option B (Automatic tuning) is wrong because it optimizes query performance through index management and plan regression fixes, not disaster recovery or regional failover. Option C (Elastic pools) is wrong because they are a cost-management model for sharing resources among multiple databases, not a high-availability or geo-replication feature. Option D (Serverless compute) is wrong because it auto-scales compute resources based on workload demand and pauses idle databases, but it does not provide any cross-region replication or automatic failover capability.

133
MCQmedium

A mobile gaming company stores player profiles in Azure Cosmos DB. Each profile document contains many optional fields, and queries frequently filter by the player's locale (a field present in about 30% of documents). Which approach will optimize query performance for these filters?

A.Embed all fields in a single document and rely on automatic indexing
B.Normalize the data by storing locale in a separate container and use cross-container queries
C.Define a fixed schema for all documents to ensure every document has the locale field
D.Create a composite index that includes the locale field
AnswerD

A composite index that includes `locale` as the leading field (and optionally another field like `region` or `level`) directly supports a query that filters on `locale` by enabling an index seek instead of a full container scan. Because the index only contains entries for documents that actually have a `locale` property, the 70% of profiles that omit the field are automatically excluded from the index scan, drastically lowering request units (RU) and improving latency. This is the recommended and simplest optimization within Cosmos DB's schema-agnostic indexing model, and it does not require schema changes or data duplication.

Why this answer

Creating a composite index that includes the locale field allows Azure Cosmos DB to efficiently filter queries by locale without scanning every document. Since locale is present in only 30% of documents, a composite index reduces the query RU cost by directly locating matching documents, leveraging the index's sorted structure for faster lookups.

Exam trap

The trap here is that candidates assume automatic indexing is sufficient for all queries, but they overlook that sparsely populated fields benefit from explicit composite indexing to avoid high RU costs from index scans.

How to eliminate wrong answers

Option A is wrong because embedding all fields in a single document with automatic indexing does not optimize queries for a sparsely present field like locale; automatic indexing still requires a full index scan for the field, leading to higher RU consumption. Option B is wrong because normalizing locale into a separate container and using cross-container queries introduces additional latency and RU cost due to cross-partition queries, and Cosmos DB does not support efficient cross-container joins. Option C is wrong because defining a fixed schema to force the locale field on all documents increases storage and write RU costs unnecessarily, and does not improve query performance without an appropriate index on the field.

134
MCQeasy

A logistics company stores shipment tracking data. The shipment ID, destination, and weight are stored in a fixed-schema database table. The shipment's route history is stored as a JSON document where each document can have different fields depending on the route events recorded. Which classification of data best describes the route history data?

A.Structured data
B.Semi-structured data
C.Unstructured data
D.Analytical data
AnswerB

JSON documents representing shipment route history are semi-structured because they use key-value pairs and nested arrays that may vary from one shipment to another. Unlike a rigid relational schema, fields can be added, omitted, or differently typed as tracking events evolve, and the JSON's self-describing nature carries its structural metadata. This places it between fully structured and completely unstructured data.

Why this answer

The route history data is stored as JSON documents where each document can have different fields depending on the events recorded. This flexibility in schema—where fields vary per document—is the hallmark of semi-structured data. Unlike structured data with a fixed schema, semi-structured data uses tags or markers (like JSON key-value pairs) to organize the data, making it self-describing.

Exam trap

The trap here is that candidates confuse 'structured' with 'organized' and assume JSON is structured because it has keys, but the key differentiator is schema flexibility—structured data enforces a fixed schema, while semi-structured data allows varying fields per record.

Why the other options are wrong

A

Route history data is stored as JSON documents with varying fields, which is semi-structured, not structured. Structured data requires a fixed schema with consistent fields, which does not apply here.

C

Route history JSON documents have a schema (fields like route events) even if fields vary per document, making them semi-structured, not unstructured. Unstructured data lacks any predefined schema or organization (e.g., plain text, images).

D

Analytical data refers to data used for analysis and reporting, not a classification of data structure. The question asks about the structural classification of route history data, which is semi-structured because it is stored as JSON with varying fields.

When would these options actually be correct?

A

If the question described data stored in a fixed-schema table with consistent columns (e.g., shipment ID, destination, weight), then it would be structured data. For example: 'A table with columns for shipment ID, destination, and weight, all with defined data types.'

C

A question describing data with no inherent structure, such as a collection of free-text customer reviews or raw video files, where no schema or metadata is imposed. For example: 'A company stores customer feedback as plain text files with no formatting. Which classification?'

D

A question asks: 'A company stores historical sales data in a data warehouse for trend analysis. Which classification of data best describes this data?' Here, 'Analytical data' would be correct because the data is used for analysis, not because of its structure.

Why candidates pick the wrong answer

A

Candidates may assume all database data is structured, overlooking that JSON documents with variable fields are semi-structured. The mention of 'database table' for other data might cause confusion.

C

Candidates may confuse 'varying fields' with 'no structure,' assuming that because JSON documents can differ, they are unstructured. They overlook that JSON still has key-value pairs and a lightweight schema.

D

Candidates may confuse 'analytical data' with a structural category, thinking that data used for analysis must be a distinct type, or they may misinterpret the question as asking about the data's purpose rather than its format.

135
MCQeasy

A company uses Azure Cosmos DB for a globally distributed application. They need to ensure low-latency reads and writes for users in multiple regions. Which consistency level provides the strongest guarantees without sacrificing availability?

A.Bounded staleness
B.Consistent prefix
C.Strong
D.Eventual
AnswerA

Bounded staleness offers strong consistency with a configurable lag: it guarantees that reads within a specified time or operation count will not miss any committed write, after which they reflect the latest data. This is achieved without requiring synchronous cross-region coordination, preserving write availability and low latency. Because of this, it is the strongest consistency level that still supports globally distributed writes and reads with predictable freshness.

Why this answer

Bounded staleness provides the strongest consistency guarantee that still maintains availability during a partition. It ensures that reads are guaranteed to be within a configurable staleness window (either K versions or a time interval) from the latest write, which is stronger than consistent prefix or eventual consistency, while avoiding the availability trade-offs of strong consistency in a globally distributed Azure Cosmos DB account.

Exam trap

The trap here is that candidates often confuse 'strongest guarantees' with 'strong consistency,' not realizing that strong consistency sacrifices availability during a partition, whereas bounded staleness is the strongest level that still guarantees high availability in a globally distributed setup.

How to eliminate wrong answers

Option B (Consistent prefix) is wrong because it guarantees only that reads never see out-of-order writes, but it does not bound how far behind a read can be, so it is weaker than bounded staleness. Option C (Strong) is wrong because it offers linearizability but sacrifices write availability during a regional outage or partition, as all replicas must acknowledge the write before it is committed. Option D (Eventual) is wrong because it offers no ordering or recency guarantees; reads may return stale data indefinitely, which is the weakest consistency level.

136
MCQhard

A company uses Azure Cosmos DB for a global e-commerce application. The application needs to support multi-region writes and provide strong consistency for inventory updates. Which configuration minimizes write latency while meeting the consistency requirement?

A.Multi-master with bounded staleness consistency
B.Single-region writes with session consistency
C.Single-region writes with strong consistency
D.Multi-master with eventual consistency
AnswerC

Strong consistency is supported only with single-region writes; multi-region writes cannot achieve strong consistency.

Why this answer

Multi-region writes (multi-master) with strong consistency is not supported in Azure Cosmos DB. To achieve strong consistency, you must use single-region writes. Therefore, single-region writes with strong consistency (Option C) meets the requirement while minimizing write latency by avoiding the overhead of multi-region replication.

Option A (multi-master with bounded staleness) provides bounded staleness, not strong consistency. Option B (single-region writes with session consistency) provides session consistency, which is weaker. Option D (multi-master with eventual consistency) provides eventual consistency, which is the weakest level.

137
Multi-Selecteasy

Which TWO of the following Azure services are considered non-relational data stores?

Select 2 answers
A.Azure SQL Database
B.Azure Cosmos DB
C.Azure Table Storage
D.Azure Synapse Analytics
E.Azure Database for PostgreSQL
AnswersB, C

Azure Cosmos DB is a multi-model NoSQL database service that supports document, key-value, graph, and column-family data models, with schema-agnostic ingestion. It provides global distribution and multiple consistency levels, but it deliberately avoids relational constructs like joins and fixed schemas. This makes it one of the two non-relational answers.

Why this answer

Options B and C are correct. Azure Cosmos DB (B) is a globally distributed, multi-model NoSQL database service, making it a non-relational data store. Azure Table Storage (C) is a key-value store that is also non-relational.

Option A is wrong because Azure SQL Database is a relational database. Option D is wrong because Azure Synapse Analytics is a relational analytics system. Option E is wrong because Azure Database for PostgreSQL is a relational database.

138
MCQmedium

A media publishing company stores high-resolution images and video files for their website. These files are large (hundreds of MBs each) and are accessed only a few times per month, but when accessed, they must be delivered within seconds. Additionally, they need to store a small amount of metadata (e.g., upload date, author) for each file. Which Azure service should they use for storing the binary files?

A.Azure Table Storage
B.Azure Blob Storage
C.Azure File Storage
D.Azure Queue Storage
AnswerB

Azure Blob Storage is optimized for large unstructured binary objects and supports custom metadata.

Why this answer

Azure Blob Storage is designed for storing massive amounts of unstructured binary data, such as high-resolution images and video files. It supports objects up to 4.75 TB in size, offers tiered storage (including cool and archive tiers) to optimize cost for infrequently accessed data, and provides low-latency access (typically under 10 seconds) for retrieval when needed. This makes it the ideal choice for the media publishing company's requirements.

Exam trap

The trap here is that candidates confuse Azure Table Storage (for metadata) with the primary storage for binary files, or they assume Azure File Storage (SMB shares) is suitable for web-serving large media files, when in fact Blob Storage is the correct service for unstructured binary data with infrequent access patterns.

Why the other options are wrong

A

Azure Table Storage is a NoSQL key-value store for structured data, not designed for large binary files like images and videos. It cannot efficiently store or stream hundreds of MBs of binary data with low-latency access.

C

Azure File Storage is designed for shared file access using SMB protocol, typically for lift-and-shift scenarios or applications that need network file shares. It is not optimized for storing and serving large binary files like images and videos with low-latency access via HTTP/HTTPS.

D

Azure Queue Storage is designed for message queuing and asynchronous communication between application components, not for storing large binary files like images and videos.

When would these options actually be correct?

A

A question asks for storing structured metadata (e.g., device telemetry, user profiles) with fast key-based lookups and no fixed schema. The files are small (KB range) or stored elsewhere, and the focus is on high-volume, low-cost storage for structured data.

C

A company needs to migrate an on-premises file server to the cloud and requires shared access to files across multiple virtual machines using standard SMB protocols. The files are office documents and application data, not large media files, and low-latency HTTP access is not a primary requirement.

D

A question asking for a service to decouple components of a distributed application, where messages (e.g., processing requests) need to be reliably stored and delivered between producers and consumers, would make Queue Storage the correct answer.

Why candidates pick the wrong answer

A

Candidates may confuse Table Storage with Blob Storage due to both being part of Azure Storage, or think that 'metadata' implies a table-like structure, overlooking that the primary requirement is storing large binary files.

C

Candidates may confuse Azure File Storage with a general-purpose file storage solution, assuming it can handle any file type including large media files, without recognizing that Blob Storage is specifically designed for unstructured data like images and videos with optimized access patterns.

D

Candidates may confuse 'queue' with 'storage' or think that because queues can hold data, they are suitable for storing large files, overlooking the fundamental purpose of queues as a messaging service.

139
MCQhard

A software-as-a-service (SaaS) provider hosts a multi-tenant application with a separate database for each tenant. They anticipate scaling to thousands of tenants and want to minimize cost while allowing tenants to share resources flexibly. Which Azure SQL offering is most suitable?

A.Azure SQL Database elastic pool
B.Azure SQL Database (single database)
C.Azure SQL Managed Instance
D.SQL Server on Azure Virtual Machine
AnswerA

Azure SQL Database elastic pools share a set of eDTUs or vCores across multiple databases, letting a SaaS provider assign per-database minimum and maximum performance limits. This design absorbs unpredictable usage spikes from different tenants without over-provisioning each database, making it the most cost-efficient and operationally simple choice for a large multi-tenant workload.

Why this answer

Azure SQL Database elastic pool is the most suitable choice because it allows multiple single databases (one per tenant) to share a fixed set of resources (DTUs or vCores) within a pool, optimizing cost by averaging resource usage across tenants. This model supports scaling to thousands of tenants while providing resource elasticity and predictable pricing, as tenants with low activity can use unused capacity from busy ones without over-provisioning.

Exam trap

The trap here is that candidates often choose Azure SQL Database single database (Option B) because they assume 'separate database for each tenant' implies isolation, but they overlook the cost and scaling inefficiency of dedicating resources per tenant when resource sharing is explicitly desired.

Why the other options are wrong

B

Single databases do not allow resource sharing across tenants; each database is isolated with fixed resources, leading to higher costs and inefficiency when scaling to thousands of tenants with variable usage.

C

Azure SQL Managed Instance is designed for lift-and-shift migrations of on-premises SQL Server workloads with high compatibility, not for multi-tenant SaaS scenarios where elastic pools provide cost-effective resource sharing across thousands of databases.

D

SQL Server on Azure Virtual Machine requires manual management of scaling, high availability, and resource sharing, and does not provide the built-in multi-tenant resource pooling and cost efficiency of elastic pools for thousands of tenants.

When would these options actually be correct?

B

A question requiring strict tenant isolation, predictable performance, or a fixed pricing model per tenant, such as: 'A financial services company needs separate databases for each client with guaranteed performance and no resource contention.'

C

A company needs to migrate an existing on-premises SQL Server application to Azure with minimal changes, requiring near 100% compatibility with SQL Server features like SQL Agent, CLR, or cross-database queries, and does not need the multi-tenant resource sharing of elastic pools.

D

This option would be correct for a scenario requiring full control over the SQL Server instance, such as needing custom configurations, legacy application compatibility, or specific OS-level dependencies that are not supported by PaaS offerings.

Why candidates pick the wrong answer

B

Candidates may think single databases are simpler to manage and directly map to the 'separate database per tenant' requirement, overlooking the cost and flexibility benefits of elastic pools.

C

Candidates may think Managed Instance offers better isolation and performance for multi-tenant apps, but they overlook that elastic pools are specifically built for managing many databases with shared resources at lower cost.

D

Candidates may think that running SQL Server on a VM offers maximum flexibility for scaling and resource sharing, but they overlook the operational overhead and lack of automated resource pooling compared to Azure SQL Database elastic pool.

140
MCQeasy

A data analyst needs to create interactive dashboards and reports from data stored in Azure Synapse Analytics. Which tool should they use?

A.Microsoft Power BI
B.SQL Server Reporting Services (SSRS)
C.Microsoft Excel
D.Azure Data Studio
AnswerA

Power BI provides interactive dashboards and reports with native Synapse connectivity.

Why this answer

Microsoft Power BI is the correct tool because it is designed specifically for creating interactive dashboards and reports from a wide range of data sources, including Azure Synapse Analytics. Power BI connects directly to Synapse SQL pools or serverless SQL endpoints using DirectQuery or import mode, enabling real-time visualizations and cross-filtering. This aligns with the requirement for interactive analytics, which is Power BI's core strength.

Exam trap

The trap here is confusing a data query/management tool (Azure Data Studio) or a static reporting tool (SSRS) with a dedicated interactive visualization platform, leading candidates to overlook Power BI's native integration with Azure Synapse Analytics.

How to eliminate wrong answers

Option B (SQL Server Reporting Services) is wrong because SSRS is a paginated report server for static, print-ready reports, not for interactive dashboards with live cross-filtering. Option C (Microsoft Excel) is wrong because while Excel can connect to Synapse and create charts, it lacks native interactive dashboard capabilities like slicers and drill-through across multiple visuals without complex add-ins. Option D (Azure Data Studio) is wrong because it is a database management and query tool for writing T-SQL and notebooks, not a reporting or dashboarding platform.

141
MCQhard

A multinational corporation runs a mission-critical relational database on Azure SQL Database. They require automatic failover to a secondary region in case of a regional outage, with no data loss. The secondary region must also be readable for reporting purposes. What should they implement?

A.Active Geo-Replication with manual failover
B.Read Scale-Out with a local secondary replica
C.Azure Site Recovery for the database server
D.Failover group with a readable secondary in a different region
AnswerD

A failover group on Azure SQL Database wraps the underlying geo-replication into an automatic, policy-driven failover mechanism, with a read-write listener and a separate read-only listener for reporting workloads. The readable secondary in a different region gives the corporation both a hot standby and a queryable copy for analytics, while automatic failover handles a regional outage without manual intervention. Failover groups are the intended PaaS database-native pattern for exactly this mission-critical, multi-region scenario.

Why this answer

A failover group with a readable secondary in a different region provides automatic failover (no manual intervention) and the secondary can be used for read-only queries. This meets the requirement of automatic failover, no data loss (synchronous replication when using Premium or Business Critical tiers), and read access for reporting. Option A (Active Geo-Replication) requires manual failover, not automatic.

Option B (Read Scale-Out) creates a local readable secondary, not in a different region, and does not provide failover. Option C (Azure Site Recovery) is designed for VM and physical server replication, not for Azure SQL Database.

142
MCQeasy

A mobile game developer needs to store player session data. Each session has a unique SessionID, a UserID, a start timestamp, an end timestamp, and a collection of game events (each event is a JSON object). The application requires low-latency point reads by SessionID and the ability to query all sessions for a given UserID within a time range. The schema of game events can vary between sessions (e.g., new event types added frequently). The developer wants a fully managed NoSQL database that supports flexible schemas and secondary indexing. Which Azure data store should they choose?

A.Azure Cosmos DB with the NoSQL API
B.Azure Table Storage
C.Azure Blob Storage
D.Azure Cache for Redis
AnswerA

Azure Cosmos DB with the NoSQL API is a multi-model database that natively stores JSON documents and automatically indexes every property, including UserID and timestamp, enabling fast, schema-flexible queries. Its single-digit-millisecond latency and partition-based scaling suit high-volume game telemetry, while its SQL-like query syntax supports rich filters and time-range lookups on arbitrary fields without requiring a predefined schema.

Why this answer

Azure Cosmos DB with the NoSQL API is the correct choice because it provides a fully managed, globally distributed NoSQL database with native support for flexible schemas (schemaless JSON documents), low-latency point reads by partition key (SessionID), and automatic secondary indexing for querying by UserID within a time range. Its ability to handle varying game event schemas without schema migrations makes it ideal for this use case.

Exam trap

The trap here is that candidates often confuse Azure Table Storage with a fully queryable NoSQL database, but it lacks secondary indexing and complex query support, making it unsuitable for time-range queries on non-key fields.

How to eliminate wrong answers

Option B (Azure Table Storage) is wrong because it is a key-value store with limited querying capabilities (only on PartitionKey and RowKey) and does not support secondary indexing or complex queries like time-range filtering on non-key attributes. Option C (Azure Blob Storage) is wrong because it is an object storage service designed for unstructured data (blobs), not for low-latency point reads or indexed queries on individual records; it lacks native querying and indexing for session data. Option D (Azure Cache for Redis) is wrong because it is an in-memory cache, not a durable database; it does not provide persistent storage, secondary indexing, or the ability to query by UserID across sessions over time.

143
MCQmedium

A company has a database that processes millions of small credit card transactions per second for payment authorization. They also need to run complex reports that aggregate transaction data over months to detect fraud patterns. Which type of workload describes the payment authorization process?

A.OLTP (Online Transaction Processing)
B.OLAP (Online Analytical Processing)
C.HTAP (Hybrid Transactional/Analytical Processing)
D.ETL (Extract, Transform, Load)
AnswerA

OLTP is the correct workload for credit card processing because it is optimized for high-volume, low-latency transaction handling, such as authorization requests that must complete in milliseconds. It relies on row-based storage, indexes, and ACID transactions to ensure that millions of concurrent small writes and reads remain consistent and durable, even under heavy load.

Why this answer

The payment authorization process involves high-volume, low-latency transactions that read, insert, and update individual records in real time. This is the classic definition of OLTP (Online Transaction Processing), which is optimized for ACID-compliant, row-based operations on current data. The scenario explicitly states 'millions of small credit card transactions per second,' which aligns with OLTP workloads like order entry or banking.

Exam trap

The trap here is that candidates see 'complex reports' and 'aggregate transaction data' in the same question and assume the entire workload is analytical, but the question explicitly asks only about the payment authorization process, which is purely transactional.

How to eliminate wrong answers

Option B (OLAP) is wrong because OLAP is designed for complex aggregations and historical analysis over large datasets, not for processing individual real-time transactions. Option C (HTAP) is wrong because HTAP combines OLTP and OLAP in a single system, but the question asks specifically about the payment authorization process, which is purely transactional, not analytical. Option D (ETL) is wrong because ETL is a data integration process used to move and transform data between systems, not a workload type for processing live transactions.

144
MCQmedium

A company maintains a large 'Transactions' table in Azure SQL Database. The table has a clustered index on a GUID column (TransactionID). Over time, they observe slow insert performance due to index fragmentation and page splits. They also need fast point lookups by TransactionID. Which approach should they take to improve insert performance while still supporting fast lookups?

A.Change the clustered index to a nonclustered index on TransactionID and make the table a heap
B.Change the clustered key to an integer IDENTITY column and keep a nonclustered index on TransactionID
C.Keep the clustered index on TransactionID but rebuild it daily
D.Remove the clustered index entirely and create a nonclustered index on TransactionID
AnswerB

An integer IDENTITY column provides sequential values that reduce fragmentation and page splits, improving insert performance. The nonclustered index on TransactionID supports efficient point lookups. This is a recommended pattern when the natural key is not ideal for clustering.

Why this answer

Using an integer IDENTITY column as the clustered key eliminates the random insertion order and page splits caused by a GUID clustered index, while the nonclustered index on TransactionID provides fast point lookups. In Azure SQL Database, a clustered index determines the physical order of data; a monotonically increasing integer avoids fragmentation and improves insert throughput.

Exam trap

The trap here is that candidates assume rebuilding the clustered index (Option C) is a sufficient maintenance fix, but the DP-900 exam tests understanding that the root cause is the choice of clustered key data type, not just fragmentation management.

Why the other options are wrong

A

Making the table a heap (no clustered index) eliminates page splits from GUID inserts, but point lookups by TransactionID require a nonclustered index, which still suffers from fragmentation and includes a costly key lookup (RID) to the heap, degrading lookup performance.

C

Rebuilding the clustered index daily does not address the root cause of fragmentation and page splits caused by GUID-based clustered keys; inserts will continue to cause fragmentation between rebuilds, leading to ongoing performance degradation.

D

Removing the clustered index entirely and creating only a nonclustered index on TransactionID would make the table a heap, which eliminates page splits but significantly degrades point lookup performance because lookups would require a key lookup (RID) into the heap, adding extra I/O.

When would these options actually be correct?

A

In a scenario where the table is used primarily for bulk inserts and queries are full table scans or range scans with no need for fast point lookups, and the GUID column is never used for range queries or ordering, then a heap with a nonclustered index on GUID could be acceptable.

C

This option would be correct in a scenario where the table has a clustered index on an integer column that experiences moderate fragmentation over time, and the workload is read-heavy with periodic batch inserts that can tolerate a maintenance window for index rebuilds to restore performance.

D

In a scenario where the table is used primarily for bulk inserts and point lookups are rare or not required, and the main goal is to minimize insert overhead without concern for lookup speed, making the table a heap with a nonclustered index on the lookup column could be acceptable.

Why candidates pick the wrong answer

A

Candidates know that GUIDs cause fragmentation and page splits in clustered indexes, so they think removing the clustered index entirely (heap) will solve insert performance, but they overlook the impact on point lookup performance and the need for a key lookup.

C

Candidates may think that regular index maintenance (rebuilding) is a universal solution for fragmentation, without understanding that the fundamental issue is the GUID clustering key causing excessive page splits, which cannot be fully mitigated by periodic rebuilds.

D

Candidates may think that removing the clustered index eliminates fragmentation and page splits entirely, and that a nonclustered index alone can still support fast lookups, underestimating the performance penalty of key lookups in a heap.

145
Multi-Selecthard

Which THREE of the following are valid considerations when choosing between Azure SQL Database and Azure SQL Managed Instance?

Select 3 answers
A.Azure SQL Managed Instance supports SQL Server Agent for job scheduling.
B.Azure SQL Database supports larger database sizes than Azure SQL Managed Instance.
C.Azure SQL Managed Instance supports cross-database queries within the same instance.
D.Azure SQL Managed Instance does not support virtual network integration.
E.Azure SQL Database supports elastic pools for cost-effective resource sharing among multiple databases.
AnswersA, C, E

Azure SQL Managed Instance includes SQL Server Agent, enabling the scheduling of recurring T-SQL jobs such as index maintenance, backup tasks, and data collection. This feature is inherited from the full SQL Server database engine and is not present in Azure SQL Database single databases, where you would need to use Elastic Jobs or external orchestration instead. Because the service runs the actual SQL Server engine, SQL Agent jobs can be managed using the same stored procedures and SQL commands as on-premises deployments.

Why this answer

Options A, C, and E are correct. Azure SQL Managed Instance supports SQL Server Agent (A) and cross-database queries (C) because it shares more features with on-premises SQL Server. Azure SQL Database supports elastic pools (E) for sharing resources across databases.

Option B is incorrect because Azure SQL Managed Instance supports larger database sizes than Azure SQL Database (up to 16 TB vs. up to 4 TB for single databases in SQL Database). Option D is incorrect because Azure SQL Managed Instance always runs within a virtual network, so it supports VNet integration by default.

146
MCQhard

Refer to the exhibit. An administrator is configuring aggregations in Power BI Premium to improve performance on a large dataset. The aggregation is defined on the Sales table with SUM(Amount) grouped by ProductCategory, Region, and Date at the monthly level. However, some reports that query daily data are still slow. What is the most likely reason?

A.The dataset is too large for aggregations
B.The aggregation is not in DirectQuery mode
C.The aggregation level is monthly, but queries need daily granularity
D.The aggregation has too many dimensions
AnswerC

When an aggregation is built at the monthly grain, any query that filters or groups by date at the daily level cannot be satisfied by that aggregation because the daily detail rows are not preserved. Power BI must then fall back to querying the full detail table, which defeats the purpose of the aggregation and causes poor performance. The aggregation level must match or be a rollup of the query granularity to be used.

Why this answer

The aggregation is defined at the monthly level (grouping by month), but the slow reports are querying daily data. Power BI aggregations work by pre-aggregating data at a specified granularity; when a query requests a lower level of detail (daily), the aggregation cannot satisfy the query, so Power BI falls back to scanning the full detailed dataset, causing slow performance. To improve daily queries, an additional aggregation at the daily level would be needed.

Exam trap

The trap here is that candidates may think aggregations automatically speed up all queries on a table, but they must match the exact granularity of the query; otherwise, the aggregation is ignored and the full dataset is scanned.

How to eliminate wrong answers

Option A is wrong because the dataset being large is the reason aggregations are used, and the issue is not the size itself but the mismatch between the aggregation granularity and the query granularity. Option B is wrong because the mode (DirectQuery vs. Import) is not relevant to the granularity mismatch; aggregations can be defined in either mode, and the problem is the monthly grouping, not the storage mode.

Option D is wrong because having multiple dimensions (ProductCategory, Region, Date) is typical and not a cause of slowness; the issue is the date granularity, not the number of dimensions.

147
MCQmedium

A media company stores video files in Azure Blob Storage. They want to use Azure Content Delivery Network (CDN) to serve these videos globally. However, they need to restrict access to only authorized users. What should you implement?

A.Set the blob container to public access.
B.Use Azure Active Directory (Microsoft Entra ID) authentication for the CDN endpoint.
C.Implement shared access signatures (SAS) and token-based authentication on the CDN.
D.Use a firewall on the storage account to allow only CDN IP addresses.
AnswerC

Implementing shared access signatures (SAS) on the underlying blobs and using token-based authentication on the CDN is the correct method to restrict access to authorized users. A SAS token can be generated with granular permissions, such as read-only and with a specific expiration time, and appended to the video URL or used as a key for CDN token authentication. This ensures that only users with a valid, unexpired token can fetch the content from the origin, while the CDN caches and delivers it efficiently; invalid or expired tokens result in access denial.

Why this answer

To restrict access to authorized users when serving video files from Azure Blob Storage via Azure CDN, you should implement token-based authentication using shared access signatures (SAS). SAS tokens grant time-limited, specific permissions to clients, and Azure CDN can be configured to require these tokens before delivering content. This ensures only authorized users can access the videos.

Option A is incorrect because making the blob container public would allow unrestricted access to anyone. Option B is incorrect because Azure Active Directory authentication is not directly supported by Azure CDN for blob access; token-based authentication with SAS is the standard approach. Option D is incorrect because a storage account firewall restricting to CDN IP addresses only limits the network source, not individual user authorization; any request coming from the CDN IP would still be served without user-level authentication.

148
MCQmedium

A gaming company stores player profiles in Azure Cosmos DB using the NoSQL API. Each profile is a JSON document containing fields like playerId, userName, level, inventory (an array of items), and friends (an array of playerIds). The application frequently needs to query all players that have a specific item in their inventory (e.g., 'sword'). Which Cosmos DB feature should they use to support this query efficiently?

A.Change feed
B.Stored procedures
C.Composite index
D.Indexing policy with a wildcard index
AnswerD

Adding a wildcard index, such as /inventory/[]/? to the indexing policy, instructs Cosmos DB to index every element of the inventory array rather than only the first element, which is the default behavior. With that index in place, ARRAY_CONTAINS queries can use a targeted index seek instead of scanning every document, making the lookup both fast and cost-efficient in terms of request units. This is the correct configuration for a gaming company that frequently queries player profiles based on whether an array contains a specific item, because it directly aligns the index with the predicate.

Why this answer

A wildcard index in the indexing policy allows Azure Cosmos DB to automatically index all properties within a JSON document, including nested array elements like those in the 'inventory' array. This enables efficient queries such as 'SELECT * FROM c WHERE ARRAY_CONTAINS(c.inventory, {name: "sword"})' without requiring a custom composite index for each possible item. Without a wildcard index, the query would require a full scan of all documents, which is inefficient at scale.

Exam trap

The trap here is that candidates often confuse indexing features, thinking a composite index (Option C) is needed for array queries, when in fact composite indexes are for multi-property equality or range filters, not for array membership queries which require a wildcard index to index the array elements themselves.

How to eliminate wrong answers

Option A is wrong because the change feed is a mechanism for capturing document inserts, updates, and deletes in chronological order, not for querying current data based on array contents. Option B is wrong because stored procedures are server-side JavaScript logic for transactional operations, not a query optimization feature for indexing array elements. Option C is wrong because a composite index is designed to optimize queries with multiple filter conditions (e.g., WHERE level = 10 AND userName = 'Alice'), not for queries that filter on array membership like 'inventory contains item X'.

149
MCQmedium

A social networking application stores user profiles as JSON documents in Azure Cosmos DB. Each profile includes fields such as 'userName', 'email', 'followersCount', and optional 'interests'. The application needs to perform fast point reads by 'userName' (under 10 ms) and also run queries to find all users with a 'followersCount' greater than a certain value. The development team prefers to use a query syntax similar to SQL. Which Azure Cosmos DB API should they choose?

A.Azure Cosmos DB for NoSQL (SQL API)
B.Azure Cosmos DB for MongoDB
C.Azure Cosmos DB for Table
D.Azure Cosmos DB for Apache Cassandra
AnswerA

The SQL API is the native JSON document API for Azure Cosmos DB. It stores each user profile as a self-contained JSON document and automatically indexes every property without requiring a predefined schema. Developers can query these documents using a familiar SQL-like syntax (SELECT, WHERE, JOIN, GROUP BY) over any field, including nested properties like followersCount, and perform point reads using the partition key and document ID for optimal latency.

Why this answer

Azure Cosmos DB for NoSQL (SQL API) is the correct choice because it natively supports SQL-like query syntax for querying JSON documents, enabling the required queries such as filtering by 'followersCount'. It also provides fast point reads (under 10 ms) by using the 'userName' field as the partition key, ensuring efficient direct access to individual documents.

Exam trap

The trap here is that candidates may confuse the SQL-like syntax of Cassandra's CQL with the native SQL API, overlooking that Cassandra is a wide-column store not optimized for JSON document queries, while the SQL API is purpose-built for JSON documents and SQL queries.

Why the other options are wrong

C

Azure Cosmos DB for Table is designed for key-value and wide-column data with a tabular schema, not for JSON documents. It does not support SQL-like queries on nested JSON fields or efficient point reads by 'userName' as a custom key.

D

Azure Cosmos DB for Apache Cassandra uses the Cassandra Query Language (CQL), not SQL-like syntax, and is optimized for wide-column stores, not JSON documents. It does not natively support point reads by a single field like 'userName' with the same low-latency guarantees as the SQL API.

When would these options actually be correct?

C

A question where an application stores structured, non-relational data (e.g., sensor readings or user preferences) in a table format, requires fast point reads by a partition key, and uses OData-like queries instead of SQL. The team prefers a simple key-value API with table storage semantics.

D

A question where the application requires high-throughput writes and reads with a schema that is columnar (e.g., time-series data), and the team is familiar with Cassandra's CQL. For example: 'A telemetry system stores device readings with columns like deviceId, timestamp, and value. The team knows CQL and needs a globally distributed, low-latency database.'

Why candidates pick the wrong answer

C

Candidates may confuse Azure Cosmos DB for Table with the Table API in Azure Storage, thinking it supports JSON-like data, or they may assume 'Table' implies a general-purpose NoSQL option suitable for any schema.

D

Candidates may associate Cassandra with high scalability and low latency, and mistakenly think its CQL is similar enough to SQL for the query requirement, overlooking that the data model (JSON documents) and the need for SQL-like syntax are better served by the NoSQL API.

150
MCQeasy

A data analyst needs to visualize sales data from Azure SQL Database in real-time dashboards. Which tool should they use to create interactive reports?

A.Microsoft Power BI
B.Azure Data Studio
C.Azure Synapse Analytics
D.Microsoft Excel
AnswerA

Microsoft Power BI is the correct choice because it is a dedicated enterprise business intelligence platform designed for interactive data visualization. Its native Azure SQL Database connector supports both Import and DirectQuery modes, allowing the analyst to model sales transactions, build DAX measures, and publish shareable, refreshable dashboards that users can filter and drill into in real time.

Why this answer

Microsoft Power BI is the correct tool because it is designed specifically for creating interactive, real-time dashboards and reports from various data sources, including Azure SQL Database. It supports live connections and DirectQuery to enable near-real-time visualization without requiring data movement.

Exam trap

The trap here is confusing database query tools (Azure Data Studio) or data storage/processing services (Azure Synapse Analytics) with dedicated visualization and reporting tools, leading candidates to overlook Power BI's specific role in real-time dashboard creation.

How to eliminate wrong answers

Option B is wrong because Azure Data Studio is a database management and query tool for SQL Server and Azure SQL, not a reporting or dashboarding tool. Option C is wrong because Azure Synapse Analytics is an enterprise analytics service for large-scale data warehousing and big data processing, not a tool for building interactive reports. Option D is wrong because Microsoft Excel is a spreadsheet application that can connect to databases but lacks native real-time dashboard capabilities and is not designed for interactive, live reporting.

Page 1

Page 2 of 11

Page 3

All pages