Azure Data Fundamentals Notes: stores and workloads

Domain 1: Ways to represent data

Start from the shape of the data, not the logo on the service card.

Options for data storage

Common file formats appear across lakes and pipelines: CSV for simple tabular exchange, JSON for nested documents, Parquet and ORC for columnar analytics, Avro for row-oriented streaming and schemas. A database adds querying, consistency rules, and concurrent access on top of storage.

Data shape is different from file encoding

A storage service does not determine whether the contents are structured. A Blob container can hold a consistently typed sales table in Parquet, nested product documents in JSON, and photographs. All three are objects from the storage service’s perspective, but their contents require different interpretation. Likewise, a binary format does not automatically mean unstructured data: Parquet and Avro encode records using schemas.

Imagine an export with order ID, date, and amount. CSV is easy to inspect as text, but consumers must agree on delimiters, date formats, and missing values. Parquet groups values by column and can let analytical readers avoid unrelated columns. JSON is useful when a product has a variable list of attributes. Choose the representation from the data and reading pattern, not from the filename alone. Microsoft’s data lake storage guidance explains the storage and file-format distinction.

Extra Exact SKU sizing and reserved capacity math sit outside the fundamentals scope. Know which family fits the need first.

Domain 2: Relational concepts

Relational data lives in tables related by keys. Normalization reduces redundant copies of the same fact by splitting into related tables. Common SQL statements include SELECT, INSERT, UPDATE, and DELETE. JOIN is a clause used within queries to combine rows; it is not a standalone statement. CREATE, ALTER, and DROP define or change database objects. Common objects include tables, views, indexes, stored procedures, and constraints such as primary and foreign keys.

Suitable indexes can speed selected lookups and filters, but take storage and add work when indexed data changes. Views present a query as a virtual table. Stored procedures package reusable logic on the server. Constraints enforce rules the application alone might miss.

SQL worked example: customers, orders, and totals

Use this small fictional dataset to reason through a query before memorizing SQL vocabulary. Customers contains one row per customer, identified by CustomerID. Orders contains one row per order, identified by OrderID, and references Customers through CustomerID. The relationship is one customer to many orders. The sample is printed here for inspection; this page does not connect to a database or execute SQL.

Read, filter, join, and aggregate

The question is “How much has each paying customer spent?” First join orders to customers through the matching key. Then keep paid orders, group them by customer, and add the amounts. Sorting the totals makes the highest amount appear first. This SELECT reads data without changing the source rows.

SELECT c.CustomerID, c.CustomerName,
       SUM(o.Amount) AS PaidTotal
FROM Customers AS c
INNER JOIN Orders AS o
  ON o.CustomerID = c.CustomerID
WHERE o.Status = 'Paid'
GROUP BY c.CustomerID, c.CustomerName
ORDER BY PaidTotal DESC;

Expected result: Ada has 100.00 and Ben has 30.00, in that order. Ben’s pending 25.00 order is excluded. Cleo does not appear because an INNER JOIN requires a matching order. Grouping by the ID as well as the name avoids combining different customers who happen to share a name.

WHERE filters input rows before aggregation. GROUP BY defines the groups, SUM produces a total for each group, and ORDER BY controls display order. HAVING would filter the groups after aggregation: adding HAVING SUM(o.Amount) > 50 before ORDER BY leaves only Ada. JOIN belongs to the FROM portion of this query. See the Microsoft SELECT reference for clause definitions.

Normalization protects current facts; snapshots preserve history

Storing a customer’s current contact details once avoids updating many order rows when those details change. A foreign key links each order to the customer record. However, the address used to deliver an old order is a historical fact. Updating the customer’s current address must not silently rewrite where that parcel was shipped. A deliberate shipping-address snapshot on the order can therefore be appropriate. Reducing accidental duplication and preserving historical evidence are compatible goals.

Distinguish an entity’s current attributes from the facts captured by an event. In the example, Amount belongs to the order: calculating an old sale using today’s product price could change historical revenue. Before suggesting normalization, identify what each value means and when it is supposed to change. A database design is more than a rule to remove every repeated value.

Transactions and ACID in a checkout

Suppose a checkout creates an order and reserves inventory in the same database transaction. Atomicity means both operations succeed or both are rolled back. Consistency means declared rules remain satisfied, such as a valid customer reference. Isolation controls how concurrent operations interact; the chosen isolation level affects what other transactions can observe. Durability means a committed change persists despite a subsequent failure.

A transaction boundary must be designed explicitly. Two separate web requests do not become one atomic database transaction simply because they happen at checkout. An external payment processor also needs coordination beyond a local transaction. For DP-900, recognize why operational systems need dependable updates and why a folder of files alone does not supply relational constraints.

Open-source Azure database services

Azure Database for MySQL Flexible Server and Azure Database for PostgreSQL Flexible Server provide managed open-source relational engines. Choose them when the application already targets that engine and you want Azure to handle patching, backups, and high availability options. Choose based on the application’s engine, supported features, and access patterns; a relational service and a document service are not interchangeable just because both are managed.

Retired service: Azure Database for MariaDB retired on September 19, 2025. Older preparation material may still list it. The MariaDB database engine itself is separate from this retired Azure service. Evaluate migration compatibility rather than assuming MariaDB and MySQL are identical. See the Microsoft lifecycle announcement.

Azure Cosmos DB

Cosmos DB is a globally distributed, multi-model database service. Use cases include low-latency reads and writes at global scale, flexible schemas, and multiple consistency options. You select an API that matches how the application talks to data.

• NoSQL API — document-oriented JSON with SQL-like queries over documents.
• MongoDB API — MongoDB wire protocol compatibility for existing Mongo clients.
• Cassandra API — Cassandra Query Language style for wide-column workloads.
• Gremlin API — graph traversals for relationships as first-class data.
• Table API — table/key-attribute model compatible with Azure Table patterns.

Extra Throughput RU/s tuning and multi-region write conflict detail belong to role-based exams. DP-900 expects use-case and API recognition.

Choose an analytics path from the business question

Data lake, warehouse, and lakehouse

A data lake retains files in multiple formats so that different consumers can explore and process them. A warehouse organizes curated analytical tables for consistent reporting. A lakehouse adds table management and analytical capabilities over lake storage. These patterns can coexist: keeping source files helps with reprocessing, while curated tables give report authors stable definitions.

Consider a shop that receives order exports, supplier spreadsheets, and product photographs. The lake can retain all three. A warehouse might contain validated sales facts joined to product and date dimensions, rather than placing every photograph in a sales table. A lakehouse can provide managed analytical tables over files. Simply copying miscellaneous files into a container does not automatically create a usable warehouse or a governed lakehouse.

ETL versus ELT: where transformation happens

In extract, transform, load (ETL), a processing step cleans or reshapes source data before loading the destination. In extract, load, transform (ELT), data is loaded first and transformed using the destination’s processing capabilities. For example, standardizing currency codes before loading a reporting table is ETL; loading source rows to a warehouse staging area and then transforming them there is ELT. Neither term by itself specifies whether a business report refreshes every hour or every day. See Microsoft’s ETL and ELT guide.

Freshness and correctness are separate requirements

A delivery team might need an alert within seconds when a vehicle’s temperature exceeds a threshold. Finance may need a reconciled total after the day closes. The first requirement points toward streaming; the second can use a batch job that processes a bounded daily dataset. A streaming pipeline still needs rules for repeated events, missing readings, and events that arrive late. “Real time” describes a latency objective, not a guarantee that every received value is accurate.

Keep ingestion and computation separate in your explanation. Event Hubs accepts and buffers event streams for consumers; a processor such as Azure Stream Analytics calculates a time-window average or detects a condition. A destination stores the output, and a report presents it. Receiving events alone does not calculate a five-minute average. A fresh chart also depends on how its data reaches the model and how the report updates.

A small Power BI model you can explain

Take one Sales row per sold item. Its product key, date key, quantity, and sale amount describe a business event. Product and Date tables supply the names and calendar attributes used to group those events. A measure such as total sales is evaluated for the current filters, so choosing a month changes the result without changing the underlying order records. The row-level detail is the model’s grain; mixing monthly targets with individual order rows without a deliberate relationship can duplicate totals. See Microsoft’s star schema guidance.

A Power BI report can have several pages for exploration. A dashboard is a single canvas in the Power BI service that summarizes selected highlights and can combine tiles from different reports. These terms are not interchangeable with the semantic model behind them. See Microsoft’s dashboard introduction.

For the shop, use a line chart for daily revenue, a bar chart to compare product categories, and a table when someone needs exact order values. A map is useful only if location contributes to the question. Label currency and time periods, avoid combining different units on an unexplained axis, and show the refresh date when old data could mislead a decision.

Batch versus streaming and real-time services

Batch data has a start and an end for a job: nightly sales files, weekly extracts. Streaming data is continuous: clickstreams, IoT telemetry, fraud signals. Real-time analytics services on Azure include options such as Azure Stream Analytics, real-time capabilities in Microsoft Fabric, and event ingestion paths such as Azure Event Hubs that feed streaming processors. Match the service to whether you need continuous processing or scheduled batch jobs.

Worked scenarios

Explain each scenario out loud before you read the choice. Name one wrong alternative and why it fails the constraint.

1. Checkout cart consistency. A retail app must insert an order and its line items together or not at all. Prefer a transactional relational store such as Azure SQL Database over Blob storage for the operational records.
2. Product images for a catalog. Millions of image files with occasional reads fit Azure Blob storage. Azure Files would be the wrong fit unless clients need SMB share semantics.
3. Legacy SQL Server with linked servers and agent jobs. Azure SQL Managed Instance or SQL Server on Azure VMs is more likely than a single Azure SQL Database, depending on how much OS control you still need.
4. Existing MongoDB application moving to Azure. Evaluate Azure Cosmos DB for MongoDB when MongoDB-compatible access is required. Check supported commands and versions before assuming an application can move unchanged. Rewriting to Azure SQL requires a different data model.
5. Clickstream fraud signals within seconds. Streaming analytics, not a nightly batch warehouse load alone.
6. Executive trend report by region. A Power BI line or map visual on a modeled dataset, not raw Event Hubs consumption by every executive.

Practice DP-900 reasoning without an Azure subscription

You can complete these exercises on paper or in a local text editor. They need no account, paid resource, or personal dataset. Explain each answer in a sentence before opening the suggested answer; recognizing a service name is less useful than identifying the constraint that makes it fit.

Turn practice misses into a useful revision plan

After a practice session, write down the question ID, the decision you missed, and the clue you overlooked. Revisit the matching section above, then explain why the nearest alternative fails. For example, “Files because the application requires SMB” is more actionable than “remember storage.” Retry missed items after a break and also answer unfamiliar examples; recognizing yesterday’s option wording can inflate your apparent progress.

Finish by checking every objective in the official DP-900 study guide. The local bank uses an editorial allocation within the published ranges, not an exact model of a live exam. Its raw percentage cannot be converted into Microsoft’s scaled passing score. Use the explanations and domain report to find gaps rather than treating a single result as a booking recommendation.

Frequently asked questions