Refer to the Exhibit Practice Questions

The Parquet files are not using the .parquet extension.

Why it fails: Serverless SQL identifies Parquet by content, not filename extension, so files lacking .parquet are still read and would not raise a schema-mismatch error. It is tempting because extension-based format detection is common elsewhere, but the real cause is inconsistent column definitions between files.

The derivedModel option is set to false, which disables schema inference.

Why it fails: No derivedModel setting exists in Synapse serverless OPENROWSET or external tables; schema inference is governed by WITH clauses and file consistency. It is tempting because schema inference genuinely can be toggled in some engines, but the mismatch here stems from differing Parquet schemas across files.

The recursive option is causing the table to include files from subfolders that have different schemas.

Why it fails: The recursive option only controls whether subfolders are traversed; it does not reconcile differing column schemas, and the mismatch occurs among files in the specified folder. It is tempting because recursion can pull in unexpected files, but that yields extra rows, not schema errors.

Denies storage accounts that do not have any IP rules defined

Why it fails: The policy's condition targets accounts lacking IP rules, but the exhibit enforces private endpoint or firewall configuration, so this misreads the rule's effect. It is tempting because IP rules are a genuine network restriction, and denying accounts without them would be correct if the policy aimed to mandate IP allowlists rather than block public access.

Denies storage accounts that have firewall rules configured

Why it fails: Firewall rules are the mechanism the policy permits, not the condition it denies; the exhibit denies accounts where public network access remains enabled. It is tempting because firewall rules and private endpoints both restrict traffic, and a policy denying accounts with no firewall would be correct if the requirement were firewall-based rather than public-access-based.

Denies storage accounts that have public network access disabled

Why it fails: The policy denies accounts where public network access is enabled, so denying accounts that have it disabled inverts the condition. It is tempting because disabling public access is the desired secure state, and a policy denying accounts without that setting would be correct if the intent were to enforce private-only access.

The location should be fixed instead of using resourceGroup().location

Why it fails: Using resourceGroup().location is a deployment portability convention, not a security control, so it cannot constitute the violated security best practice. Hardcoding a location is preferable only when resources must be pinned to a specific region for latency, residency, or paired-region requirements.

The SKU should be Standard_GRS for disaster recovery

Why it fails: SKU redundancy governs durability and availability of stored data, not security posture, so Standard_GRS addresses disaster recovery rather than the violated control. Choosing geo-redundant SKUs is correct when the requirement is regional failover or durability against datacentre loss, not when auditing an ARM template for security misconfiguration.

The account does not enable hierarchical namespace (HNS)

Why it fails: Hierarchical namespace is a functional capability enabling Data Lake Storage Gen2 directory semantics and POSIX ACLs; its absence is a design or feature gap, not the security best-practice violation the template exhibits. Enabling HNS is correct when workloads need directory-level operations or fine-grained ACL inheritance.

The input alias is incorrect.

Why it fails: An incorrect alias would raise a 'not found' or unresolved-name error, not a type mismatch. The alias resolves correctly; the input it points to is declared as Stream instead of Reference. It tempts because alias typos are a common query fault, but the message explicitly concerns input type.

The JOIN syntax is incorrect for reference data.

Why it fails: The JOIN clause itself is valid; the error arises because the input referenced in the join was configured as Stream rather than Reference in the job's inputs. It tempts because join errors often stem from syntax, but here the input type metadata, not the query text, is at fault.

The output type is set to ReferenceData; it should be a different type.

Why it fails: Output type is irrelevant to this error, which concerns how an input is declared. The message names the input, not the output, so changing the output type leaves the misconfigured stream input unchanged. It tempts because ReferenceData is a valid output type, but it is never the cause here.

The source table is being updated between pipeline runs, but the copy activity is not configured to upsert.

Why it fails: If the source table is updated but the copy activity is not configured for upsert, it would still copy all 1 million rows (append or overwrite), not 0 rows.

The pipeline is using a full load each time, overwriting the sink, so the second run sees the same data and skips because the sink already has it.

Why it fails: A full load with overwriting would still copy all 1 million rows to the sink; it does not skip rows because the sink already has the data. 0 rows would not occur in this scenario.

The sink is a file system that fails to write due to permission issues.

Why it fails: Permission issues would typically cause the copy activity to fail with an error, not silently copy 0 rows.

It disables encryption using the customer-managed key.

Why it fails: Infrastructure encryption adds a second encryption layer using platform-managed keys; it does not disable customer-managed key encryption, which remains configured separately via the key vault properties. The option tempts because both settings concern encryption keys, but they operate on different layers and are not mutually exclusive.

It enables encryption of data in transit between nodes.

Why it fails: Infrastructure encryption protects data at rest with a second layer of platform-managed keys; data in transit between nodes is handled by transport-level encryption, not this ARM setting. The confusion arises because encryption settings commonly cover both states, yet this property targets stored data only.

It enables transparent data encryption for SQL pools.

Why it fails: Infrastructure encryption applies a second at-rest encryption layer to the workspace's Azure resources; transparent data encryption is configured on the SQL pools themselves, not through this workspace property. The option tempts because TDE is a familiar encryption control, but it operates at a different scope.

It configures the workspace to use a user-assigned managed identity

Why it fails: managedVirtualNetwork governs the workspace's network isolation boundary, not identity. A user-assigned managed identity is configured through the workspace's identity property. That identity setting is the correct choice when the workspace must authenticate to resources such as storage accounts using a pre-created identity.

It disables public network access to the workspace

Why it fails: managedVirtualNetwork controls outbound traffic isolation from the workspace, not inbound public access. Disabling public network access is achieved through a separate workspace property. That setting is the correct choice when the workspace must be reachable only via private endpoints.

It creates the workspace in a private endpoint configuration

Why it fails: Setting managedVirtualNetwork to default provisions an Azure Synapse-managed virtual network with managed private endpoints inside it; it does not itself create a private endpoint configuration for the workspace. Private endpoints are the right choice when clients must reach the workspace over a private IP.

storageAccountType

Why it fails: storageAccountType sets the pool's backing storage redundancy (LRS, GRS, ZRS), not its compute performance level. It is tempting because storage configuration genuinely matters for dedicated SQL pools, but the DW200c requirement is met by changing the serviceLevelObjective parameter, which controls the DWU allocation.

maxSizeBytes

Why it fails: Changing maxSizeBytes alters only the storage capacity ceiling, not the compute performance level, so DW200c cannot be reached. It is tempting because it governs dedicated SQL pool storage limits, and would be the correct parameter when you need to expand capacity beyond the default 1 TB for a dedicated SQL pool.

collation

Why it fails: Collation fixes sort order, comparison rules and code-page behaviour for character data at creation time; it has no bearing on compute capacity. The performance level is governed by the sku object's name or capacity value, which must specify DW200c.

It configures automated backups of the Synapse workspace

Why it fails: purviewConfiguration associates the workspace with Microsoft Purview for data governance and lineage; backup scheduling is not its function. Synapse workspace backups are handled through Azure-native recovery options, which would be the correct mechanism when disaster recovery is the requirement.

It enables monitoring of data movement by Azure Monitor

Why it fails: purviewConfiguration links the Synapse workspace to a Microsoft Purview account for lineage and cataloguing; it does not enable Azure Monitor data-movement monitoring, which uses diagnostic settings and Log Analytics. Purview would be correct when governance and lineage tracking are required.

It connects the workspace to a data catalog for pipeline sources

Why it fails: purviewConfiguration registers the workspace with a Microsoft Purview account so lineage and metadata flow into the governance portal; it does not connect pipeline sources. Pipeline source connections are configured through linked services, which would be correct when external data stores must be accessed.

Increase the number of executors to 4.

Why it fails: Four executors cannot parallelise a 200 GB shuffle across enough partitions, leaving each task handling too much data and spilling. Raising executor count is the right lever for small datasets needing modest concurrency, but here the bottleneck is per-task shuffle volume, not executor scarcity.

Change executor size to 'Large' to increase memory per executor.

Why it fails: Enlarging executor memory gives each task more heap but does not reduce the volume of data moved across the network during shuffle, which is the actual constraint. Larger executors suit memory-intensive caching or broadcast joins, not a shuffle bound by partition count and data movement.

Decrease spark.sql.shuffle.partitions to 200 to reduce overhead.

Why it fails: Lowering shuffle partitions to 200 concentrates 200 GB into fewer, larger partitions, increasing per-task data and spill rather than relieving the shuffle. Reducing partitions helps small datasets where many tiny tasks create scheduling overhead, the opposite of this workload's profile.

Change the output to a different sink

Why it fails: Switching the output sink cannot resolve input deserialization errors, since failures occur while reading and parsing the source stream before any output write. Sink changes suit output throttling or write failures, not malformed inbound events.

Increase the number of Streaming Units (SUs)

Why it fails: Adding Streaming Units scales compute throughput for CPU-bound or partitioned workloads, but deserialization errors originate from malformed input records, which extra SUs cannot parse. SU increases suit genuine backlog from insufficient parallel processing, not format failures.

Set the watermark delay threshold higher

Why it fails: Raising the watermark threshold conceals the symptom without addressing the deserialization errors corrupting the input stream; those malformed events still fail to parse. Threshold tuning suits late-arriving-but-valid data in event-ordering scenarios, not schema or format mismatches.

Add a firewall rule to limit access to the storage account

Why it fails: A firewall rule restricts network origin but leaves the storage account key embedded in the linked service definition, so anyone reading the JSON obtains full account access. Firewall rules suit restricting public exposure to known IP ranges; here the requirement is removing the static secret entirely by switching the linked service to the user-assigned managed identity.

Remove the managed identity and use a service principal

Why it fails: Removing the managed identity discards the credential-free authentication already provisioned for the factory, and a service principal reintroduces a secret that must be stored and rotated. Service principals suit cross-tenant access or scenarios where the resource cannot receive a managed identity; here, switching the linked service to the user-assigned managed identity removes the account key entirely.

Keep the account key but store it in Azure Key Vault

Why it fails: Storing the account key in Key Vault still leaves a shared secret that must be rotated and can be exfiltrated, so the user-assigned managed identity remains unused. Key Vault suits centralising secrets for services lacking identity support; here Microsoft Entra ID authentication removes the credential entirely.

Add a filter activity after the copy to remove duplicates.

Why it fails: A filter activity after the copy removes rows already written, so every run still reads the full source table and duplicates are only discarded post-transfer; a watermark column compared against the last load is what restricts extraction. It is tempting because filtering is a genuine data-quality step, and would suit deduplicating a static dataset.

Enable staging with a staging table.

Why it fails: Staging tables buffer data during a copy but track no high-water mark, so every run re-reads the full orders table rather than only new rows. They suit bulk loading into a warehouse before transformation, not identifying deltas; a watermark column compared against the last load is what enables incremental extraction.

Change the copy behavior to 'MergeFiles'.

Why it fails: MergeFiles only controls how the copy activity writes multiple output files into one destination file; it does not track which rows were previously loaded, so the full source is copied each run. It is tempting because it sounds like merging data, and would be correct when consolidating many small files into a single target.

The source dataset is not filtering by date

Why it fails: Date filtering restricts which rows enter the flow; it does not change the aggregation grain, so the sink still receives one total per run. Filtering is tempting because partitioning by date is standard in pipelines, but the symptom is a missing group-by column in the aggregate transformation, not excess input rows.

The data flow is missing a filter transformation

Why it fails: A filter transformation removes rows but leaves the aggregate's group-by unchanged, so output remains a single total. Filtering is tempting because it is a common data flow step, yet the defect is the aggregate transformation's missing product column, not unwanted records entering the stream.

The sink dataset is not configured to append

Why it fails: Append mode only adds rows; it cannot create the per-product grouping the sink lacks. The aggregate transformation's group-by must include product, otherwise every row collapses into one total. Append is tempting because it preserves history across runs, which suits fact-table loading, not fixing aggregation granularity.

It sets the timeout for the integration runtime to connect to the data sources.

Why it fails: The property controls how long an idle data flow cluster stays alive before shutting down, not connection timeouts to sources. It is tempting because TTL sounds like a timeout, and a connection timeout setting would be correct when sources are unreachable within a defined window.

It specifies the maximum duration a data flow activity can run before timing out.

Why it fails: Activity timeout is a separate setting on the data flow activity; TTL instead keeps the cluster warm between runs. It is tempting because both involve durations, and an activity timeout would be correct when a single data flow must be stopped after a fixed period.

It determines the maximum number of concurrent data flows that can run on the cluster.

Why it fails: Concurrency is governed by cluster size and compute configuration, not TTL, which only governs idle cluster retention. It is tempting because both settings affect data flow execution, and a concurrency limit would be correct when parallel activity execution must be capped.