Sign in
Home/Microsoft/Administering Microsoft Azure SQL Solutions (DP-300)/Free questions

Administering Microsoft Azure SQL Solutions (DP-300) — Free Practice Questions

10 free sample questions from a bank of 443, with the correct answers and explanations. No signup required — start practising right now.

1You have 20 Azure SQL databases provisioned by using the vCore purchasing model. You plan to create an Azure SQL Database elastic pool and add the 20 databases. Which three metrics should you use to size the elastic pool to meet the demands of your workload? Each correct answer presents part of the solution. NOTE: Each correct selection is worth one point.
  • total size of all the databases
  • geo-replication support
  • number of concurrently peaking databases * peak CPU utilization per database
  • maximum number of concurrent sessions for all the databases
  • total number of databases * average CPU utilization per database
Answer: A, C, E

The short version

ACE — Size elastic pools by storage plus CPU shape. Total database size plus concurrent peak CPU plus average CPU together set eDTU or vCore headroom. Geo replication and session counts do not drive pool compute sizing.

Key concepts in this question

  • Elastic pool sharing: many DBs share one performance envelope with independent peaks smoothing together.
  • Total size: data plus log footprint that must fit the pool storage limit for vCore model.
  • Concurrent peak versus average: peaks size burst headroom, averages size sustained compute across all DBs.
  • Non-sizing signals: replication topology and session caps affect design but not core pool compute math.

Why ACE is correct

Pool compute must absorb simultaneous peaks without starving steady load while storage must hold all files. Total size plus number of peaking DBs times peak CPU plus total DBs times average CPU give those bounds. Microsoft pool sizing guidance uses exactly these three inputs for vCore pools.

Why the others are wrong

  • B. Geo-replication support decides topology and cost, not the shared compute envelope.
  • D. Max concurrent sessions limits connections but does not predict CPU or storage draw.

DP-300 exam tip

Elastic pool math is always size plus average plus concurrent peak, never sessions or replication.

2You are designing a streaming data solution that will ingest variable volumes of data. You need to ensure that you can change the partition count after creation. Which service should you use to ingest the data?
  • Azure Event Hubs Standard
  • Azure Stream Analytics
  • Azure Data Factory
  • Azure Event Hubs Dedicated
Answer: D

The short version

D — Event Hubs Dedicated allows partition changes. Only the Dedicated tier exposes dynamic partition scaling after namespace creation for variable streams. Standard fixes partition count at creation and cannot grow the same way.

Key concepts in this question

  • Partition scaling need: variable ingest volumes require more parallel readers without rebuilding the hub.
  • Dedicated capacity units: single-tenant clusters that permit partition count updates post creation.
  • Standard tier limit: partition count set at creation with only throughput unit scaling afterward.
  • Non-ingest services: Stream Analytics processes and Data Factory moves, neither stores partitions.

Why D is correct

The requirement is explicitly to change partition count after creation under variable load. Dedicated supports that operation through capacity unit management, while Standard locks the count. Choosing Dedicated therefore satisfies the only mutable-partition ingest path.

Why the others are wrong

  • A. Standard fixes partitions at creation and cannot meet the post-creation change need.
  • B. Stream Analytics is a processor that consumes hubs but provides no partition store itself.
  • C. Data Factory orchestrates batch movement and does not offer streaming partition scaling.

DP-300 exam tip

Change partitions after creation always means Dedicated, Standard means fixed at creation.

3You plan to build a structured streaming solution in Azure Databricks. The solution will count new events in five-minute intervals and report only events that arrive during the interval. The output will be sent to a Delta Lake table. Which output mode should you use?
  • complete
  • append
  • update
Answer: B

The short version

B — Append mode reports only arrivals within each five-minute interval. Windowed aggregation over five-minute event-time buckets creates one result row per window as fresh events arrive. Append writes only newly arrived rows to Delta Lake and never rewrites earlier output, matching the interval-only contract. Complete rewrites the whole table each trigger and update upserts changed rows, both violating the requirement.

Key concepts in this question

  • Windows: Tumbling five-minute event-time groups create one aggregate row per interval for reporting.
  • Append: Adds rows since last trigger with watermarking, ideal for insert-only sinks.
  • Complete and update: Complete outputs the full table each batch, update emits changed rows, both causing rewrites.
  • Delta sink: Accepts streaming appends as new files with exactly-once insert semantics.

Why B is correct

Counting new events per interval with only in-interval arrivals is the textbook append case. With watermarking, on-time events materialize once and late ones drop, yielding a clean insert stream. Complete would resend all history each trigger, exploding writes and breaking the rule. The settled mode thus aligns semantics with the stated contract.

Why the others are wrong

  • A. complete rewrites every window each trigger, reporting old intervals again against the requirement.
  • C. update modifies prior rows, suited to mutable dashboards rather than insert-only interval feeds.

DP-300 exam tip

New interval rows only equals append, full table equals complete, changed rows equals update.

4You have an Azure Synapse Analytics Apache Spark pool named Pool1. You plan to load JSON files from an Azure Data Lake Storage Gen2 container into the tables in Pool1. The structure and data types vary by file. You need to load the files into the tables. The solution must maintain the source data types. What should you do?
  • Load the data by using PySpark.
  • Load the data by using the OPENROWSET Transact-SQL command in an Azure Synapse Analytics serverless SQL pool.
  • Use a Get Metadata activity in Azure Data Factory.
  • Use a Conditional Split transformation in an Azure Synapse data flow.
Answer: A

The short version

A — Load JSON with PySpark to keep types. Spark infers per-file schema and preserves nested plus variant types into Pool1 tables. Serverless SQL and factory activities would coerce or flatten the varying structures.

Key concepts in this question

  • Varying JSON schema: files differ in fields and types, needing flexible schema inference.
  • PySpark reader: schema-of-json plus dynamic typing keeps source types through DataFrames.
  • OPENROWSET limits: serverless SQL needs explicit schema and flattens semi-structured quirks.
  • Factory metadata and data flow splits: discover or route data but do not preserve rich JSON typing into Spark.

Why A is correct

Pool1 is a Spark pool where notebooks can read each JSON file with schema inference and write typed Delta or Spark tables. PySpark handles missing fields, nested arrays and type evolution without manual DDL per file. That maintains source data types across heterogeneous inputs as required.

Why the others are wrong

  • B. OPENROWSET in serverless SQL targets SQL types and struggles with evolving nested JSON.
  • C. Get Metadata only lists file facts and does not load or type data.
  • D. Conditional Split routes rows in a data flow but does not infer varying JSON types.

DP-300 exam tip

Varying JSON into Spark with types kept always means PySpark, not serverless SQL.

5You are designing a date dimension table in an Azure Synapse Analytics dedicated SQL pool. The date dimension table will be used by all the fact tables. Which distribution type should you recommend to minimize data movement?
  • HASH
  • REPLICATE
  • ROUND_ROBIN
Answer: B

The short version

B — REPLICATE the shared date dimension. A tiny date table copied to every compute node removes shuffles for all fact joins. Hash and round robin would scatter the dimension and force data movement.

Key concepts in this question

  • Dedicated SQL pool distributions: hash, replicate and round robin control row placement across nodes.
  • Small shared dimension: date is narrow and used by every fact, ideal for full copies.
  • Data movement cost: joining a scattered dimension requires shuffle before match, hurting large fact queries.
  • Storage versus compute trade: replication costs little extra storage for dates while saving repeated shuffle compute each query.
  • Design guidance: Microsoft recommends replicate for small dimensions referenced universally across facts.

Why B is correct

Every fact joins to date, so collocating a full copy with each fact slice lets joins complete locally without shuffle. Microsoft guidance recommends replicate for small dimensions referenced universally. That minimizes movement exactly as the stem demands while keeping storage overhead minimal for a narrow date table.

Why the others are wrong

  • A. Hash spreads dates by key and still requires movement to align with differently hashed facts.
  • C. Round robin scatters randomly and guarantees shuffle for almost every fact to date join.

DP-300 exam tip

Small date dimension used by all facts always means REPLICATE to kill shuffles.

6You have a SQL pool in Azure Synapse that contains a table named dbo.Customers. The table contains a column name Email. You need to prevent nonadministrative users from seeing the full email addresses in the Email column. The users must see values in a format of [email protected] instead. What should you do?
  • From the Azure portal, set a mask on the Email column.
  • From the Azure portal, set a sensitivity classification of Confidential for the Email column.
  • From Microsoft SQL Server Management Studio, set an email mask on the Email column.
  • From Microsoft SQL Server Management Studio, grant the SELECT permission to the users for all the columns in the dbo.Customers table except Email.
Answer: A

The short version

A — Portal mask shows email as masked pattern to nonadmin users. Synapse and Azure SQL support Dynamic Data Masking that transforms results without changing stored values. The email mask function renders the required masked shape while privileged logins with UNMASK still see full addresses. Configuring it from the portal creates the same rule as scripts and directly implements least-privilege redaction.

Key concepts in this question

  • Masking: Column policy with email, default, partial, or random function applied transparently at query time.
  • Email shape: Exposes first letter plus masked remainder in the requested aXXX pattern.
  • Portal path: Data masking blades create the Transact-SQL rule without manual scripting.
  • Classification: Labels data for audit but does not redact result sets, so it cannot meet the goal.

Why A is correct

Setting the portal mask means unprivileged selects automatically receive transformed values without app changes, while admins retain full visibility. Stored emails stay intact and support flows continue. That avoids coarse denial of the column and precisely matches the display-format demand, which is why the settled choice is correct.

Why the others are wrong

  • B. classification tags Confidential for governance but leaves values unmasked in results.
  • C. SSMS path misstates the validated portal route and adds no masking advantage.
  • D. denying SELECT on Email breaks queries or forces workarounds instead of masking.

DP-300 exam tip

Redacted display equals masking, labels alone equal classification.

7You have an Azure Databricks workspace named workspace1 in the Standard pricing tier. Workspace1 contains an all-purpose cluster named cluster1. You need to reduce the time it takes for cluster1 to start and scale up. The solution must minimize costs. What should you do first?
  • Upgrade workspace1 to the Premium pricing tier.
  • Configure a global init script for workspace1.
  • Create a pool in workspace1.
  • Create a cluster policy in workspace1.
Answer: C

The short version

C — Creating a pool first speeds start and scale at lowest cost. Pools keep idle ready virtual machines that new or scaling clusters claim instead of waiting for cloud provisioning and runtime setup. Attaching the all-purpose cluster to a pool cuts cold start and autoscale latency. It avoids Premium uplift or policy changes that do not address provisioning delay, and reuses idle capacity to minimize cost.

Key concepts in this question

  • Pools: Managed cache of instances matched by VM type, runtime, and zone for rapid claim.
  • All-purpose gain: Interactive notebook clusters suffer most from cold start, so pooling shows clear wins.
  • Init and policy: Init scripts add setup time and policies constrain settings, neither warms VMs.
  • Tier: Premium adds governance, not faster allocation, so it cannot minimize cost here.

Why C is correct

Creating the pool first is prerequisite because no cluster benefits until warm capacity with matching tags exists. Once available, the cluster can use it, cutting provisioning and scale-out delay through shared idle instances. That sequencing answers what-should-you-do-first while minimizing spend, following the cited 4x-faster pool guidance.

Why the others are wrong

  • A. Premium adds security and controls but does not warm VMs, leaving delay and raising cost.
  • B. global init script runs extra setup on each node, typically lengthening start.
  • D. cluster policy governs allowed settings without pre-provisioning compute.

DP-300 exam tip

Slow start plus minimize cost equals pools first, never tier upgrade.

8You have an Azure Synapse Analytics workspace named WS1 that contains an Apache Spark pool named Pool1. You plan to create a database named DB1 in Pool1. You need to ensure that when tables are created in DB1, the tables are available automatically as external tables to the built-in serverless SQL pool. Which format should you use for the tables in DB1?
  • JSON
  • CSV
  • Parquet
  • ORC
Answer: C

The short version

C — Use Parquet for Spark to serverless sharing. Spark databases backed by Parquet in ADLS expose SYNC metadata that serverless SQL can read as external tables automatically. JSON, CSV and ORC lack that synced catalog path.

Key concepts in this question

  • Spark to serverless interop: shared storage plus shared metastore lets both engines see the same tables.
  • Parquet columnar base: supported synced format that preserves types and partition pruning.
  • CSV JSON ORC gap: either text ambiguity or missing automatic external table surfacing in this flow.
  • DB1 requirement: tables created in Pool1 must appear without manual DDL in serverless.

Why C is correct

Synapse Spark can create Parquet-backed databases whose metadata syncs to the workspace catalog for serverless SQL. New Parquet tables then appear as external tables automatically. Other formats either lose type fidelity or need manual external definitions, missing the automatic goal.

Why the others are wrong

  • A. JSON stores semi-structured text without the synced Parquet table contract.
  • B. CSV lacks types and partition metadata needed for auto external surfacing.
  • D. ORC is readable but is not the documented auto-sync format for this Spark to serverless path.

DP-300 exam tip

Spark DB auto-visible to serverless always means Parquet, never CSV or JSON.

9You are designing an anomaly detection solution for streaming data from an Azure IoT hub. The solution must meet the following requirements: ✑ Send the output to an Azure Synapse. ✑ Identify spikes and dips in time series data. ✑ Minimize development and configuration effort. Which should you include in the solution?
  • Azure SQL Database
  • Azure Databricks
  • Azure Stream Analytics
Answer: C

The short version

C — Azure Stream Analytics gives low-code anomaly output to Synapse. Built-in ML functions spot spikes and dips in IoT streams with SQL-like syntax and native Synapse output. Databricks and SQL DB need custom models and plumbing.

Key concepts in this question

  • Streaming anomaly need: detect temporal spikes and dips over IoT Hub telemetry in near real time.
  • Stream Analytics ML: anomaly detection functions embedded in the streaming query language for spikes.
  • Synapse sink: first-class output binding that lands scored rows for serving without extra jobs.
  • Config effort: low-code query plus output binding beats notebook model build and upkeep.
  • Alternative paths: Databricks needs training code, SQL DB alone has no streaming engine.

Why C is correct

Stream Analytics can ingest IoT Hub, apply spike-and-dip detection inline, and write to Synapse with minimal setup. That covers all three mandates in one service without training or hosting models. Databricks would require custom code and SQL DB alone has no streaming engine to meet latency.

Why the others are wrong

  • A. SQL Database stores results but cannot ingest streams or score anomalies natively.
  • B. Databricks can model anomalies but needs far more development and tuning effort.

DP-300 exam tip

Streaming spikes to Synapse with least effort always means Stream Analytics ML.

10You are creating a new notebook in Azure Databricks that will support R as the primary language but will also support Scala and SQL. Which switch should you use to switch between languages?
  • \\[ ]
  • %
  • \\[ ]
  • @
Answer: B

The short version

B — Percent magic switches notebook languages. A cell starting with percent plus r, scala or sql overrides the notebook default so R primary can still run other code. Other symbols have no language-switch meaning.

Key concepts in this question

  • Databricks polyglot notebooks: one default language plus per-cell overrides for mixed logic.
  • Percent magic form: percent followed by language name selects the interpreter for that cell.
  • Supported magics: percent python plus percent r plus percent scala plus percent sql cover all cell switches.
  • Distractor symbols: brackets and at signs serve indexing or decorators, not language choice.
  • R primary context: default stays R while single cells can still run Scala or SQL.

Why B is correct

Azure Databricks documents percent plus python, r, scala and sql as the magic prefixes for cell language override. Using percent at the cell start routes that cell to the named runtime without changing notebook default. Only B names that mechanism for R plus Scala plus SQL mixing.

Why the others are wrong

  • A. Bracket syntax addresses collections, never notebook language selection.
  • C. Alternate bracket form likewise has no magic language role.
  • D. At sign denotes decorators or matrix ops, not cell language switching.

DP-300 exam tip

Switch languages per cell means percent magic, so memorize percent r scala sql.

Want the full bank of 443 questions for Administering Microsoft Azure SQL Solutions (DP-300)? See all practice exams.