DP-203 common mistakes
DP-203 · Understand the traps and how to avoid them.
Choosing a row-store or plain Parquet layout for analytical workloads that filter on a few columns and aggregate over billions of rows, instead of reasoning about columnstore and partitioning up front.
Candidates memorise that Parquet is 'good for analytics' and stop there, so they miss questions about clustered columnstore indexes in dedicated SQL pools, partitioning strategy, and when a heap or row-store is appropriate for small dimension tables.
What to do instead
For each storage question, state the access pattern first (scan-and-aggregate vs point lookup vs high-concurrency small writes), then map it to a physical design: clustered columnstore for large fact tables, partitioned views or table partitioning on a date key, row-store for small lookup tables, and a distribution choice (hash on the join key, round-robin for staging, replicate for small dimensions).
Treating the data lake as a dumping ground with no folder hierarchy, no consistent file format, and no partition pruning, then expecting Synapse or Databricks queries to be fast.
The lake-design questions look simple, so candidates answer at the level of 'store files in ADLS Gen2' and skip the decisions the exam actually tests: bronze/silver/gold layering, partitioning by date, file sizing to avoid the small-file problem, and Delta Lake vs raw Parquet.
What to do instead
Practice describing a lake layout end to end: raw landing zone partitioned by ingestion date, curated zone in Delta format with partitioning and Z-ordering on common filter columns, and a serving zone optimised for the consuming engine. Be ready to justify file compaction and why many tiny files hurt Spark and serverless SQL.
Assuming a single copy of data in one region and one storage tier is sufficient, ignoring redundancy options, geo-replication, and lifecycle management.
Redundancy and tiering feel like infrastructure trivia, so candidates skip them, yet the exam asks about LRS vs ZRS vs GRS vs RA-GRS, when to use cool or archive tiers, and how lifecycle rules move blobs between tiers.
What to do instead
Memorise the trade-off table: LRS (cheapest, single datacentre), ZRS (zone redundancy within a region), GRS/RA-GRS (cross-region, read access in the secondary). Pair that with tiering rules: hot for active processing, cool for 30+ day retention, archive for compliance with rehydration latency and cost in mind.
Writing a pipeline that overwrites its output on every run with no idempotency, no watermark, and no late-arriving-data handling, then failing questions about exactly-once semantics and incremental loads.
Candidates focus on getting data from A to B and treat reprocessing, duplicates, and out-of-order events as edge cases, but the exam deliberately tests watermarking, checkpointing, upserts, and merge patterns.
What to do instead
Design every ingestion answer around idempotency: use MERGE or upsert into a Delta table keyed by business key, track a high-water mark for incremental reads, define a watermark for streaming late data, and enable checkpointing so restarts resume without duplication.
Reaching for a full batch reload when the requirement is change data capture or near-real-time ingestion, or conversely building a streaming pipeline for a nightly report.
Candidates pattern-match on tool names (Event Hubs, Data Factory, Spark Structured Streaming) rather than on latency and volume requirements, so they pick the fashionable tool instead of the fitting one.
What to do instead
Before answering, extract the latency requirement and the change pattern. Sub-minute freshness with deletes and updates points to CDC into Delta with merge; hourly or nightly batch points to Data Factory or Synapse pipelines; high-throughput event ingestion points to Event Hubs feeding Structured Streaming. State the requirement you are satisfying.
Ignoring data skew and shuffle behaviour when choosing partitions, join strategies, and distribution keys in Spark or dedicated SQL pools.
Performance tuning questions reward a mental model of how data moves, and most candidates have only run small datasets where skew never surfaced, so they answer 'increase the cluster size' instead of fixing the data layout.
What to do instead
Diagnose skew explicitly: salting hot keys, repartitioning on a high-cardinality key, broadcast joins for small dimensions, and hash distribution on the join column in Synapse to avoid data movement. Know that adding nodes cannot fix a single overloaded partition.
Granting broad access such as storage account keys, Contributor at subscription scope, or public network access because it 'just works' during development.
Candidates learn the fastest path to a working pipeline and never revisit permissions, so they miss questions on managed identities, least privilege, and network isolation.
What to do instead
Default to managed identity for Data Factory, Synapse, and Databricks accessing ADLS Gen2, scope RBAC to the container or folder rather than the subscription, and pair it with ACLs when hierarchical namespace is enabled. Disable public access and use private endpoints or service endpoints where the scenario demands isolation.
Confusing row-level security, column-level security, dynamic data masking, and object-level permissions, and applying the wrong one to a masking or access requirement.
These four features sound interchangeable in prose but behave very differently: masking hides values in results, RLS filters rows, CLS blocks columns, and object permissions control who can query at all. Candidates memorise definitions without mapping them to scenarios.
What to do instead
Build a one-line decision rule for each. Requirement 'support agents must not see customer email' maps to dynamic data masking or column-level security depending on whether the value must remain usable. Requirement 'regional managers see only their region' maps to row-level security with a predicate function. Requirement 'analysts can read the view but not the base table' maps to object permissions plus ownership chaining.
Skipping monitoring and alerting design, treating logging as an afterthought and never connecting diagnostic settings to a central workspace.
Monitoring questions are short and easy to overlook during study, so candidates cannot describe how to route activity logs, pipeline run metrics, or query store data to Log Analytics and alert on them.
What to do instead
Learn the standard observability path: diagnostic settings on the storage account, Synapse workspace, and Data Factory send logs and metrics to Log Analytics; Azure Monitor alerts fire on those; Data Factory additionally surfaces pipeline and activity runs through its own monitoring hub. Be able to name the specific signal you would alert on for a failed copy activity or a slow query.
Optimising compute cost only, ignoring storage cost, data movement cost, and the cost of leaving clusters or dedicated pools running idle.
Candidates think of 'optimisation' as making queries faster and forget the exam also tests pause/resume, auto-pause, auto-scaling, and right-sizing, which are cost levers rather than performance levers.
What to do instead
For any optimisation question, check four levers: pause or auto-pause idle dedicated SQL pools and Databricks clusters, right-size and auto-scale Spark clusters, compact small files and tier cold data to reduce storage and scan cost, and avoid cross-region egress by co-locating storage and compute. State which lever applies and why.