Scenario practice questions

Using INSERT INTO VALUES

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

Using BCP utility

Why it fails: BCP is a command-line utility that performs bulk data imports and exports, and it is fully supported in Azure Synapse SQL Pool.

Using SQL Server Integration Services (SSIS)

Why it fails: SSIS is a robust ETL tool that can connect to Synapse SQL Pool and load data efficiently, making it a valid choice.

Azure Cosmos DB

Why it fails: Cosmos DB is a globally distributed NoSQL document store with multiple consistency models, not a relational engine with SQL schemas and joins, so it fails the transactional relational requirement. It is tempting because it is fully managed and serverless, and would be correct for low-latency, multi-region NoSQL workloads.

Azure Synapse Analytics

Why it fails: Synapse Analytics is an analytics workspace for MPP data warehousing and big-data pipelines, not a transactional OLTP engine, so it cannot serve the stem's transactional workload. It is tempting because it is fully managed and serverless-capable, and would be the right pick for large-scale analytical querying over a data warehouse.

Azure Data Lake Storage Gen2

Why it fails: Data Lake Storage Gen2 is hierarchical blob storage for files and analytics engines, offering no relational schema, SQL query engine or transactions, so it cannot host transactional workloads. It is tempting because it is fully managed and serverless, and would be the right choice for storing raw files consumed by Synapse or Databricks.

Execute Pipeline activity

Why it fails: Execute Pipeline activity invokes a separate pipeline, so it neither ingests from the three sources nor runs the aggregation stored procedure within this pipeline. It is correct for orchestrating reusable child pipelines, such as parameterised ingestion patterns shared across workloads.

Azure Function activity

Why it fails: This activity runs custom code in an Azure Function. While it could potentially be used to call a stored procedure or load data, it is not the standard way and adds unnecessary complexity. The Copy activity is preferred for loading data, and Stored procedure activity is preferred for executing stored procedures.

Data Flow activity

Why it fails: A Data Flow activity performs Spark-based transformations, not ingestion from Blob Storage, Azure SQL Database and REST endpoints, nor stored procedure execution. Data Flows suit complex in-pipeline transformations and mappings when the data is already staged.

Azure Data Factory

Why it fails: Data Factory orchestrates batch pipelines and scheduled copies; its streaming capability is limited to ingestion into specific sinks and does not provide the continuous event processing this scenario needs. It is tempting because it already handles batch ingestion, but the streaming path in Synapse is served by Azure Stream Analytics or Event Hubs.

Azure Blob Storage

Why it fails: Blob Storage stores objects; it has no native streaming ingestion pipeline into Synapse SQL pools or Spark tables, so events cannot be consumed continuously. It is tempting as the landing zone for batch files, and would suit archival or staged batch loads, but streaming requires an engine that reads and writes events in motion.

Azure Analysis Services

Why it fails: Analysis Services hosts semantic tabular models for BI querying; it consumes processed data and cannot ingest streaming events into Synapse. It is tempting because it sits in the analytics stack and serves reporting, but it is a query and modelling layer, not an ingestion service, so it fails the streaming requirement entirely.

Azure Cosmos DB

Why it fails: Azure Cosmos DB offers tunable consistency and low-latency writes, but its SQL API lacks the mature multi-table join and complex relational query capability the scenario demands. It is tempting because it handles frequent updates with strong consistency, yet it suits document or key-value workloads rather than relational joins.

Azure Synapse Analytics

Why it fails: Azure Synapse Analytics is an analytics warehouse optimised for large-scale read queries and batch loading, not for frequent row-level updates with strong consistency on transactional data. It is tempting because it handles complex joins across tables, but it targets analytical workloads rather than high-volume transactional writes.

Azure Table Storage

Why it fails: Azure Table Storage is a key-value NoSQL store without secondary indexes, joins or strong consistency guarantees across partitions, so it cannot satisfy complex multi-table queries. It is tempting because it stores frequently updated entities cheaply, but it targets simple key-based lookups rather than relational reporting.

Ingest data via Azure Functions and write to Data Lake Storage; use Power BI to query Data Lake

Why it fails: Azure Functions is event-driven compute, not a streaming ingestion service, so it lacks the throughput, ordering and checkpointing needed for continuous IoT streams. It is tempting because Functions is serverless and low-overhead, and would be the right choice for sporadic, event-triggered processing rather than sustained telemetry ingestion.

Use Azure Databricks with Structured Streaming to write to Data Lake Storage and use Power BI DirectQuery

Why it fails: Databricks Structured Streaming requires provisioning and tuning clusters, adding operational overhead the scenario explicitly asks to minimise. It is tempting because Databricks handles both streaming ingestion and historical processing with checkpointing, and would be the right choice when complex transformations or custom Spark logic are required.

Ingest data to Azure Event Hubs, then use Event Hubs Capture to store in Data Lake Storage; use Power BI with Event Hubs

Why it fails: Event Hubs Capture writes Avro files in a fixed time/size window, so it cannot serve real-time dashboards, and Power BI cannot query Event Hubs directly. It is tempting because Capture is fully managed and lands data in Data Lake Storage with minimal overhead, which suits archival ingestion rather than combined real-time and historical analytics.

Power BI Dataset

Why it fails: Power BI datasets are not supported as sources for Copy activity.

Azure DevOps

Why it fails: Azure DevOps (boards, repos) is not a data store.

Azure Analysis Services

Why it fails: Azure Analysis Services is a semantic modelling and tabular query engine, not a data store that Azure Data Factory can read from or write to as a Copy activity source or sink; supported sources include Azure SQL Database, Blob Storage, and SAP HANA via the appropriate connectors. It is tempting because Analysis Services holds business data and appears in the same Azure data platform, but it exposes tabular models over XMLA rather than a copyable dataset endpoint.

Azure Cosmos DB with single-region writes and strong consistency

Why it fails: Strong consistency forces synchronous quorum replication across replicas, adding write latency and capping throughput, which conflicts with millions of events per second. It is tempting because Cosmos DB is a low-latency globally distributed store, and it would be correct where the workload needs multi-region writes with bounded staleness rather than maximum single-region ingest rate.

Azure Data Lake Storage Gen2 with hierarchical namespace

Why it fails: Data Lake Storage Gen2 is object storage built on Blob, so per-event writes incur blob-level overhead rather than the sub-millisecond point writes a database provides. It is tempting because hierarchical namespace suits analytics over massive datasets, and it would be correct for batch or analytical workloads, not sustained high-rate event ingestion.

Azure Blob Storage with hot tier and append blobs

Why it fails: Append blobs serialise writes to a single blob, so concurrent producers contend and throughput per blob is limited, raising latency at millions of events per second. It is tempting because append blobs suit log-style ingestion, and they would be correct for lower-rate sequential logging rather than high-concurrency event streams.

The Azure Blob Storage account is throttling requests.

Why it fails: The error is raised on the Source side against SQL Server, so Blob Storage throttling cannot produce SqlOperationFailed; that would surface as sink-side errors. Storage throttling is the likely cause when copying into Blob Storage at excessive request rates.

The authentication method to SQL Server is incorrect.

Why it fails: Incorrect credentials fail every copy activity consistently, not intermittently during business hours, and would raise a login or authentication error rather than SqlOperationFailed. Wrong authentication is the cause when all runs fail identically regardless of server load.

The self-hosted integration runtime is not connected to the network.

Why it fails: A disconnected integration runtime would fail every copy run, not intermittently, and would report a connectivity or gateway error rather than SqlOperationFailed from the source. It is tempting because the self-hosted runtime is the on-premises connectivity component, and reinstalling or restarting it is the correct fix when the node is offline or deregistered.

Azure NetApp Files.

Why it fails: Azure NetApp Files exposes NFS and SMB shares with POSIX or NTFS permissions, but it is not a data lake store and does not surface assets to Microsoft Purview for lineage. It is tempting because it delivers high-performance file access, which is the correct choice for lift-and-shift enterprise file workloads needing low latency, not lake-scale analytics.

Azure Files.

Why it fails: Azure Files provides SMB and NFS shares with share-level and NTFS permissions, but it is not a hierarchical data lake namespace and its Purview lineage integration is limited. It is tempting because it offers managed file shares with identity-based access, which is the correct choice for replacing on-premises file servers, not for analytics lake storage.

Azure Blob Storage.

Why it fails: Azure Blob Storage offers no native file-level POSIX ACLs or hierarchical namespace permissions; those come from Azure Data Lake Storage Gen2. Blob Storage is tempting because it is the underlying object store and integrates with Microsoft Purview, but it cannot satisfy the fine-grained file-level access control requirement.

Azure Table Storage

Why it fails: Azure Table Storage stores entities as key-attribute pairs, so nested JSON objects must be flattened and there is no native support for the low-latency analytical query patterns real-time clickstream analytics demands. It is tempting because it offers cheap, high-throughput storage for semi-structured data, and would suit simple key-based lookups rather than nested analytics.

Azure SQL Database

Why it fails: Azure SQL Database is a relational engine requiring a fixed schema, so nested JSON must be shredded or stored as opaque text, and it cannot sustain the write throughput of millions of clickstream users. It is tempting because it offers low-latency queries and JSON functions, and would be correct for structured transactional workloads.

Azure Blob Storage

Why it fails: Azure Blob Storage provides cheap, scalable object storage but no native indexing or query engine, so low-latency reads over nested JSON for real-time analytics require an additional compute layer. It is tempting because it handles high write throughput and semi-structured data well, and would be the correct choice for storing raw files for later batch processing.

Create nonclustered indexes on all columns of the target table

Why it fails: Nonclustered indexes on every column slow bulk inserts because each row must update every index structure, and dedicated SQL pools favour clustered columnstore for large loads. They suit selective point lookups on small tables, not high-volume ingestion into a columnstore fact table.

Set the row group size to 100,000 rows for optimal compression

Why it fails: A 100,000-row row group is far below the 1,048,576-row maximum that maximises columnstore compression and query performance; small row groups waste space and add overhead. This setting would suit tables where the full million rows cannot be accumulated before a load commits.

Enable change tracking on the target table

Why it fails: Change tracking records row-level changes for downstream consumption, adding overhead during bulk loads and offering no load-performance gain. It would be correct when synchronising changed data to external systems. Dedicated SQL pool load tuning uses PolyBase, CTAS, and distribution choices instead.

Azure Table Storage.

Why it fails: Azure Table Storage is a key-value NoSQL store with no native time-series ingestion pipeline or sub-second analytical query engine; it suits cheap schema-less lookups, not 10,000 events/second telemetry analytics. Azure Data Explorer handles that throughput and latency.

Azure SQL Database with in-memory OLTP.

Why it fails: In-memory OLTP accelerates transactional processing within a relational engine, but Azure SQL Database lacks the partitioned ingestion pipeline and global distribution needed for 10,000 events per second. It is the right choice for low-latency OLTP applications, not telemetry analytics.

Azure Cosmos DB with analytical store.

Why it fails: The analytical store is columnar and optimised for large-scale analytical queries over synced operational data, introducing latency unsuited to sub-second reads on live telemetry. It is correct for near-real-time reporting over Cosmos DB data, not high-rate ingestion with immediate query.

Add a filter in the copy activity to exclude rows with NULL Amount

Why it fails: Filtering out NULL rows silently drops source records, which the requirement forbids. It is tempting because it removes the failing rows quickly, and would be correct if the business rule genuinely excluded those records from the sink rather than requiring them to be preserved.

Modify the sink table to have a default value for the Amount column

Why it fails: A default value only applies when a column is omitted from the INSERT, not when an explicit NULL is supplied, so the copy still fails. It tempts because defaults are the standard fix for missing values, and would be correct if the source rows lacked the Amount field entirely rather than containing NULL.

Change the sink table column to allow NULLs

Why it fails: Making the column nullable accepts the NULLs but stores them as NULL, so the 'Amount' values are not preserved as data — the stem demands no data loss. It tempts because relaxing a NOT NULL constraint is the quickest way to stop the copy failing, and would be correct if NULLs were genuinely valid business values.

Storage account firewall and virtual network rules

Why it fails: Firewall and virtual network rules restrict network access to the storage account, but they do not log access attempts. They are preventive controls, not detective controls. Enabling them would not provide the detailed audit logs required for compliance. They are part of a defense-in-depth strategy but do not satisfy the logging requirement.

Azure Defender for Storage

Why it fails: Azure Defender for Storage provides threat detection and security alerts for anomalous access patterns, but it does not provide a complete audit log of all access attempts. It is a security monitoring tool, not an auditing solution. While it can complement auditing, it does not fulfill the requirement to log every access attempt for compliance.

Azure Storage analytics logging

Why it fails: Storage analytics logging is a legacy feature that provides logging for Blob, Queue, and Table storage, but it is not designed for hierarchical namespace operations in Data Lake Storage Gen2. It does not capture fine-grained access details for directories and files, and it has been largely superseded by Azure Monitor diagnostic settings.

Bounded staleness

Why it fails: Bounded staleness offers consistency within a specified lag, which may be acceptable for some financial scenarios but does not guarantee strong consistency. It can introduce stale reads, which might lead to incorrect financial decisions. The requirement explicitly states strong consistency, so this level does not meet the need.

Eventual

Why it fails: Eventual consistency provides no ordering guarantees and may return stale data. It is the weakest level and is unsuitable for financial transactions where accuracy is critical. While it offers the lowest latency, it fails to meet the strong consistency requirement, making it a poor choice here.

Session

Why it fails: Session consistency guarantees monotonic reads and writes within a session but does not provide global strong consistency. Different sessions may see different data, which is unacceptable for financial transactions that require a single, up-to-date view. This level is insufficient for the stated requirement.

Keep ROUND_ROBIN distribution and add a nonclustered index on ProductKey in FactSales.

Why it fails: ROUND_ROBIN distribution evenly spreads rows without regard to join keys, so joins on ProductKey will always require shuffling data across nodes. Adding a nonclustered index on ProductKey may help point lookups but does not change the distribution, and in a columnstore-heavy analytical workload it adds overhead. Data movement remains the dominant cost.

Change the distribution of FactSales to HASH(DateKey) and create a partition on ProductKey.

Why it fails: Hash distributing on DateKey colocates rows by date, but the join is on ProductKey, so rows with the same ProductKey would still be spread across distributions, causing data movement. Partitioning on ProductKey is also ineffective because partitioning is designed for large date ranges or predictable slices, not for high-cardinality join keys. This approach does not solve the join data movement problem.

Recreate FactSales as a replicated table and create a hash distribution on DimProduct using ProductKey.

Why it fails: Replicating a 4 TB fact table would duplicate the entire table to every compute node, consuming excessive storage and slowing down loads. Replication is intended for small dimension tables, not large fact tables. Additionally, changing the distribution of the dimension table alone does not align with the join key on the fact table, so data movement would remain high for FactSales joins.

Sort-merge join

Why it fails: Sort-merge join requires both datasets to be sorted and then merged, which involves shuffling and sorting both sides of the join. For a 500-million-row fact table, this would cause massive data movement and high latency. While sort-merge can be efficient for large-to-large joins, it is not ideal when one side is small enough to broadcast, as it unnecessarily processes the large dataset through sorting and shuffling.

Cross join

Why it fails: Cross join produces a Cartesian product of both tables, which would generate an enormous number of rows (500 million times 2 million) and is not suitable for a join with a join condition. It would cause extreme resource consumption and is semantically incorrect for joining a fact table with a dimension based on a key relationship.

Hash join

Why it fails: Hash join builds a hash table on one side and probes it with the other, but in mapping data flows, the default join type may still require partitioning both sides unless broadcast is specified. Since the dimension is small, broadcasting is more efficient. A generic hash join without broadcast would still shuffle the fact table, increasing data movement and reducing performance compared to broadcasting the smaller dimension.

Configure the job to output to Azure Cosmos DB, then use a Synapse pipeline to copy data into the dedicated SQL pool.

Why it fails: Routing through Cosmos DB adds an unnecessary database and an additional copy activity, which increases latency, cost, and complexity. Cosmos DB is optimized for document workloads, not as a staging area for analytical loads. The dedicated SQL pool can be written to directly by Stream Analytics, so this multi-hop design is needlessly indirect.

Use Azure Functions as the output to insert rows into the dedicated SQL pool.

Why it fails: Azure Functions can write to a dedicated SQL pool, but it adds a compute layer that must scale and manage connections, and it typically results in row-by-row inserts that are inefficient for high-volume telemetry. This increases latency and cost compared to a native output adapter. It also introduces potential throttling and cold-start delays in a streaming context.

Write the Stream Analytics output to Azure Blob Storage, then use PolyBase in the dedicated SQL pool to load the data.

Why it fails: This approach introduces an intermediate storage layer and requires a separate PolyBase load step, increasing latency and operational complexity. While PolyBase is efficient for bulk loads, it is not designed for continuous near-real-time ingestion from a streaming job. The scenario explicitly asks to avoid intermediate storage, so this staging pattern is not appropriate.

nullIf

Why it fails: The nullIf function returns null if two expressions are equal, otherwise returns the first expression. It is used to create nulls, not to replace them. For example, nullIf(column, '') would return null if the column is an empty string. This is the opposite of what is needed. Therefore, it does not help in replacing nulls with a default value.

iif

Why it fails: The iif function evaluates a condition and returns one of two values. While you can use iif(isNull(column), default, column) to replace nulls, it requires an additional isNull check and is more complex. The coalesce function is designed specifically for this purpose and is simpler. Thus, iif is not the best choice for replacing nulls directly.

isNull

Why it fails: The isNull function checks whether a value is null and returns a boolean. It does not replace null values; it only identifies them. You could use it in a conditional expression like iif(isNull(column), default, column), but that is more verbose. The question asks for a function to replace null values, so isNull alone is not sufficient. It is used for testing, not substitution.