CertifyDeckDP-900 · CHEAT SHEET
Microsoft Certified: Azure Data Fundamentals
Key concepts grouped by official exam weight.
Download as PDF (free, no sign-up)1. Describe core data concepts
25–30%Describe ways to represent data
- Structured data adheres to a fixed schema, so every instance of an entity has the same fields. It is most commonly represented in tables: rows are the instances and columns are the attributes, and each column stores a specific datatype. It is often stored in relational databases, where tables are linked through keys.
- Semi-structured data has some structure but allows variation between instances: for example, one customer may have several email addresses and another none. JSON is a very common format, with a hierarchical schema where an attribute can be an object or a collection. JSON is only one possible representation, and document databases store JSON documents.
- Unstructured data has no specific structure: documents, images, audio, video and binary files. It is stored as raw binary that applications must interpret, and data professionals call such files BLOBs. Azure Blob Storage is optimized for storing massive amounts of this kind of data. Vector data (embeddings) lets AI answer questions over it.
Identify options for data storage
- The file format depends on the type of data, the applications that use it and whether it must be human-readable or optimized. Delimited text (CSV, TSV, fixed-width) suits structured data; JSON suits structured and semi-structured data; XML is readable but more verbose. Parquet (columnar) and Avro (row-based) compress and process better, and Delta Lake adds transactions on top of Parquet.
- A database is a dedicated system for managing data records, not just files. Relational databases keep structured data in tables with primary keys, normalized and queried with SQL. Nonrelational databases, often called NoSQL, do not apply a relational schema: key-value, document, column family and graph. No single store fits everything, so models are often combined.
- Each need has its datastore: Azure Blob Storage holds unstructured data (images, video, backups) and, with a hierarchical namespace, forms the data lake; Azure Files offers SMB/NFS file shares; Azure Table storage keeps schemaless key/attribute data; Azure Cosmos DB is the low-latency global NoSQL database; and the Azure SQL family covers relational data.
Describe common data workloads
- A transactional (OLTP) workload records business events as small, fast units of work, mostly writes with moderate reads, and supports line of business applications. It applies CRUD operations with ACID semantics: atomicity, consistency, isolation and durability. Its data is typically highly normalized with an enforced schema.
- An analytical workload uses read-only or read-mostly systems holding large volumes of historical data. Data is loaded with ETL or ELT into a data lake, lakehouse or data warehouse, and can be aggregated into an OLAP or semantic model for reports and dashboards. Microsoft Fabric and Azure Databricks are modern analytics platforms.
Identify roles and responsibilities for data workloads
- The database administrator designs, implements and maintains on-premises and cloud-based database systems. They ensure availability, performance and optimization, define backup and recovery plans, and manage security by granting or denying access. With PaaS services such as Azure SQL Database they no longer manage the engine, operating system or hardware.
- The data engineer designs and implements ingestion pipelines, cleansing and transformation activities, and data stores for analytical workloads. They manage and monitor pipelines, maintain data privacy between on-premises and cloud, and work with tools such as Azure Data Factory, Microsoft Fabric, Azure Databricks, Azure Stream Analytics and Microsoft Purview.
- A data analyst explores data to identify trends and relationships, designs analytical models and turns raw data into insights through reports and visualizations, based on business requirements. Analysts typically use Power BI. The role differs from the database administrator (permissions, backups) and the data engineer (pipelines, cleansing), and one person can hold several roles.
2. Identify considerations for relational data on Azure
20–25%Describe relational concepts
- In a relational database data is tabular: entities are tables, each row is an instance and each column a property, and every row of a table has the same columns. The primary key uniquely identifies each row and a foreign key references the primary key of another table, which maintains relationships and referential integrity. It suits OLTP workloads well.
- Normalization organizes data into related tables following rules (normal forms) that eliminate redundancy and inconsistent dependencies. A piece of data, such as a customer address, is stored in one place and changed once. Third normal form is usually enough; the cost is that more tables are needed, and too many small tables can affect performance.
- SQL lets you create tables (CREATE TABLE), insert (INSERT), modify (UPDATE) and delete rows (DELETE) and query data (SELECT), with WHERE to filter and JOIN to combine tables. DDL statements (CREATE, ALTER, DROP) define data structures and DML statements (INSERT, UPDATE, DELETE, SELECT) work with rows. Each statement is an atomic unit: it completes or fails entirely.
- A view is a virtual table defined by a query. An index is a sorted copy of a column that speeds up queries but must be maintained on every insert, update or delete. A stored procedure groups Transact-SQL statements with parameters, and a trigger runs automatically on INSERT, UPDATE or DELETE. Primary and foreign keys are constraints that guarantee integrity.
Describe relational Azure data services
- Azure SQL is a family of products built on the SQL Server engine. Azure SQL Database is PaaS for new cloud applications, with single databases or elastic pools. Azure SQL Managed Instance is PaaS with near-100% SQL Server compatibility, ideal for migrating instances with minimal changes. SQL Server on Azure Virtual Machines is IaaS: it gives full control of the operating system and engine, but the customer manages them.
- Azure Database for MySQL and Azure Database for PostgreSQL are PaaS services based on the community editions of those engines, so existing applications migrate with few changes. Azure applies patches, creates automatic backups and offers high availability, although some server-level features, such as certain extensions, are not available. You can stop the server to save costs.
3. Describe considerations for working with non-relational data on Azure
15–20%Describe the capabilities of Azure storage
- Azure Blob Storage is Azure's object storage for large volumes of unstructured data: images, video, backups and analytics. Blobs live in containers inside a storage account and can be block, append or page blobs. Access tiers (hot, cool, cold and archive) balance cost and latency, and lifecycle management automates tier changes.
- Azure Files provides fully managed file shares accessible over SMB and NFS, and also through a REST API. They can be mounted at the same time from cloud and on-premises clients, which allows replacing file servers or lifting applications without code changes. Azure File Sync uses Windows Server as a local cache of an Azure file share, and redundancy is defined at the storage account level.
- Azure Table storage is a NoSQL key/attribute store with a schemaless design for structured, non-relational data, such as user data or device information. A table is a collection of entities, and each entity is a set of properties with a partition key and a row key. It does not support complex joins or foreign keys; Azure Cosmos DB for Table adds global distribution and secondary indexes.
Describe the capabilities and features of Azure Cosmos DB
- Azure Cosmos DB is a fully managed NoSQL database with flexible schema, automatic indexing, elastic scaling, low latency and global distribution. It fits IoT, product catalogs, gaming, web and mobile apps and personalization, and its change feed enables event-driven architectures. It is a poor fit for analytics (OLAP), where Microsoft Fabric is used, and for highly relational apps, where Azure SQL is used.
- When you create an Azure Cosmos DB account you choose the API, which determines the data format, query language and client libraries. NoSQL is the native API (JSON documents, SQL-like syntax) and is recommended for new applications; MongoDB (BSON, MQL), Cassandra (column families, CQL) and Table (key-value) make it easy to migrate existing applications, and Gremlin serves graphs of vertices and edges.
4. Describe an analytics workload
25–30%Describe common elements of large-scale analytics
- Ingestion and processing load data from transactional databases, files and real-time streams into a data lake or data warehouse, in batch or streaming mode. In ETL data is transformed before loading; in ELT it is copied and then transformed in the target. In Microsoft Fabric you ingest with Data Factory, shortcuts, mirroring, Eventstream or notebooks, and in Azure Databricks with Lakeflow or notebooks.
- A data warehouse is a relational database with a schema optimized for analytics (fact and dimension tables, star or snowflake schema). A data lake is a file store with schema-on-read that holds structured, semi-structured and unstructured data. A lakehouse combines both using Delta Lake. On Azure these are Fabric Lakehouse, Fabric Warehouse and Azure Databricks.
- Microsoft Fabric is a unified SaaS analytics platform whose workloads (Data Factory, Data Engineering, Data Warehouse, Data Science, Real-Time Intelligence and Power BI) share OneLake, a single data lake per tenant. Azure Databricks is an Apache Spark-based platform that uses Delta Lake as its native format and includes notebooks, Unity Catalog for governance and Databricks SQL.
Describe considerations for real-time data analytics
- Batch processing collects and stores records and processes them together in a single operation, with latency of hours and suited to complex analytics. Stream processing handles each event as it arrives, with latency of seconds or milliseconds, and suits simple aggregates and time-critical operations. Many solutions combine both, for example with the lambda architecture.
- Microsoft offers several real-time options. Microsoft Fabric Real-Time Intelligence includes Eventstreams, Eventhouse (KQL queries), Real-Time Dashboards and Activator; Azure Stream Analytics is a PaaS service that runs perpetual queries; and Spark Structured Streaming runs on Fabric and Azure Databricks. Events are ingested with Azure Event Hubs or Azure IoT Hub.
Describe data visualization in Microsoft Power BI
- Power BI is a suite of tools and a core workload of Microsoft Fabric. In Power BI Desktop, a Windows application, you import data, model it and build interactive reports; the Power BI service publishes, shares and schedules refreshes; the phone app consumes content. Report Builder creates paginated, printable reports.
- A Power BI semantic model is built from related tables: fact tables hold the numeric measures and dimension tables the entities used to group them, in a star or snowflake schema. Relationships propagate filters, hierarchies enable drill-down, and imported data is stored in memory with the VertiPaq engine.
- Each visualization answers a need: tables and cards for values and key figures, column or bar charts to compare categories, lines for trends, pie charts for proportions of a total, scatter plots for correlation between two measures and maps for locations. In Power BI the visuals in a report are linked and filter one another.