Azure DP-900 common mistakes
Azure DP-900 · Understand the traps and how to avoid them.
Classifying data by where it is stored rather than by its shape - for example calling JSON documents 'unstructured' because they are not in a table, or calling a CSV file 'semi-structured' because it is a file.
Candidates memorise the three labels and then guess from the storage product name (Blob, Cosmos DB, SQL) instead of looking at whether the data has a fixed schema, a self-describing schema, or no schema at all.
What to do instead
Anchor each label to a schema test: structured = fixed, predetermined schema and fits neatly in rows/columns (relational tables, CSV with a fixed header); semi-structured = self-describing tags/keys but no rigid table (JSON, XML, YAML, key-value pairs); unstructured = no schema and not queryable by field (images, audio, video, PDFs, free text). Practise sorting 10 real examples with the schema test, not with the product name.
Recommending the same Azure data store for both transactional and analytical workloads - e.g. proposing a single highly normalized Azure SQL database to serve both the order-entry app and the nightly reporting and aggregation queries.
The workload characteristics (OLTP vs OLAP) are studied as definitions but not applied; candidates see 'SQL' in both contexts and assume one database can just be scaled up to do both well.
What to do instead
Learn the contrast as a decision table: OLTP = many small concurrent read/write transactions, strongly normalized, row-store, current-state data, latency measured in milliseconds; OLAP = few but very large reads with aggregation and grouping, denormalized/star schema, columnar store, historical data, throughput measured in scans. Then map stores to the table: Azure SQL Database/PostgreSQL for OLTP, Azure Synapse dedicated SQL pool or Databricks for OLAP.
Treating Azure SQL Database, Azure SQL Managed Instance and SQL Server on an Azure VM as interchangeable, e.g. claiming Azure SQL Database gives you SQL Server Agent jobs, cross-database queries and OS-level access.
All three run the SQL Server engine, so the marketing names blur together; candidates compare on price and forget that the deployment option determines feature surface, compatibility level and who manages the OS and patching.
What to do instead
Memorise one differentiator per option. Azure SQL Database (single database or elastic pool): highest abstraction, PaaS, no OS access, no SQL Agent (use Elastic Jobs), no cross-database queries. Azure SQL Managed Instance: near-100% SQL Server compatibility - SQL Agent, cross-database queries, linked servers, CLR - inside a VNet, still PaaS. SQL Server on Azure VMs: IaaS, full OS and instance control, you patch and back up; choose it for lift-and-shift or features neither PaaS option offers.
Describing normalization as a performance optimisation that speeds up reads, or being unable to say what is wrong with a table that repeats a customer's address on every order row.
Normalization is learned as a list of numbered forms without the underlying idea of eliminating duplication, so the trade-off between write efficiency and read efficiency is never internalised.
What to do instead
State it as one sentence: normalization removes duplicated data and update anomalies by organising columns into related tables joined by keys, which makes writes consistent but makes reads require joins - which is exactly why analytical stores deliberately denormalize. Then practise identifying the anomaly by asking 'if the customer moves, how many rows must I update?' Also be able to define primary key (unique row identifier), foreign key (reference to another table's primary key) and composite key (two or more columns together forming the key).
Assuming Cosmos DB's default consistency is Strong, or thinking the consistency level is a property you set per container without cost or latency consequences, or that all Cosmos DB APIs expose identical feature sets.
'Strong' sounds like the safe, obviously-correct answer, and the differences between the five levels are memorised as an ordered list rather than as latency/throughput trade-offs; similarly all APIs share one product name so their differences are ignored.
What to do instead
Memorise that the default is Session (read-your-own-writes within a client session) and that only Strong guarantees global linearizability at the cost of higher latency and lower availability. Learn the order strong > bounded staleness > session > consistent prefix > eventual, and be able to describe each in one clause. For APIs, know that the API (NoSQL, MongoDB, Cassandra, Gremlin, Table) is chosen at account creation and determines the query language and driver compatibility, while the account-level consistency choice applies across the account.
Putting frequently read data in the archive access tier, or choosing geo-redundant storage for data that is read constantly from a second region without understanding read access.
Tier names are matched to vague intuitions ('archive = important') and redundancy acronyms are memorised letter-by-letter rather than as trade-offs among durability, availability, cost and read behaviour.
What to do instead
Learn the tier test: how often is it read and how fast must the first byte arrive? Hot = frequent access, lowest access cost; Cool = infrequent, lower storage cost, higher access cost and a minimum retention period; Archive = rarely accessed, cheapest storage, hours to rehydrate and an early-deletion penalty - so never for active reads. For redundancy, learn that LRS keeps three copies in one datacentre, ZRS spreads across availability zones in one region, GRS replicates to a paired secondary region but the secondary is not readable (RA-GRS makes it readable).
Treating a Synapse dedicated SQL pool, a Synapse serverless SQL pool and a Spark pool as equivalent compute options, or assuming serverless SQL pools need data loaded into tables before you can query it.
The word 'SQL pool' appears twice with very different billing and data models, and candidates skim past the 'dedicated vs serverless' qualifier in the option text.
What to do instead
Learn three one-line signatures. Dedicated SQL pool: provisioned compute and storage, you load data into distributed tables, billed for provisioned capacity, best for high-concurrency enterprise warehousing. Serverless SQL pool: no infrastructure, query files directly in the data lake with T-SQL, billed per terabyte of data processed - no tables required. Spark pool: managed Apache Spark for notebooks and big-data transformations. Ask 'does the workload need data loaded into tables (dedicated) or queried in place from files (serverless)?'
Confusing the artefacts inside an Azure Data Factory or Synapse pipeline - calling a dataset a pipeline, or saying a linked service holds the schedule, or believing a trigger copies data.
Four related nouns are taught together and the exam deliberately uses them in wrong-but-plausible combinations; candidates learn the definitions as a set rather than as a hierarchy of containment.
What to do instead
Build the hierarchy once and reuse it: linked service = connection/credentials to a data store or compute; dataset = a named pointer to the specific data within that linked service (the table or file); activity = the single unit of work (copy, lookup, stored procedure); pipeline = a logical grouping of activities with dependencies; trigger = what causes the pipeline to run (schedule, tumbling window, event). Test every option against 'is it a connection, a pointer, a step, a group of steps, or a starter?'.
Using 'data lake' and 'data warehouse' as synonyms, or claiming a data lake is simply a cheaper data warehouse, when asked to choose storage for raw heterogeneous files versus curated aggregated reporting data.
The terms are used loosely in marketing material and both appear under the same analytics umbrella, so the schema-on-read vs schema-on-write distinction that actually separates them never gets applied.
What to do instead
Contrast them on schema timing and content. Data lake (e.g. Azure Data Lake Storage Gen2): stores raw files of any format, schema applied on read, cheap, holds everything including unstructured data, used by data scientists and as the landing zone. Data warehouse (e.g. Synapse dedicated SQL pool): relational, schema applied on write, curated and modelled into star schemas, optimised for fast aggregated SQL reporting. A lakehouse combines both by layering warehouse-style tables (Delta/Parquet) over lake storage.
Choosing Azure Data Factory to process a continuous, unbounded event stream, or choosing Stream Analytics for a nightly scheduled bulk copy, because both are 'data movement' services.
Batch and streaming are understood as concepts but not attached to the specific Azure services, so any option containing 'data' looks plausible for any movement scenario.
What to do instead
Attach one trigger word to each service. Data Factory / Synapse pipelines = batch and scheduled orchestration ('nightly', 'every hour', 'copy from X to Y'). Azure Stream Analytics = continuous query over a live event stream with time windows ('per 5-second window', 'detect in the last minute', IoT/clickstream). Event Hubs = ingest and buffer the stream; Databricks/Spark = large-scale transformation. Decide first whether the input is bounded (batch) or unbounded (stream), then pick the service.