Azure DP-900 Practice Test

Exam DP-900: Microsoft Azure Data Fundamentals

Azure DP-900 overview

  • 197 practice questions
  • 32 flashcards
  • 4 study guide topics
  • 39 key terms

What you'll study

  • Describe core data concepts28%
  • Identify considerations for relational data on Azure24%
  • Describe considerations for working with non-relational data on Azure18%
  • Describe an analytics workload on Azure30%

The Microsoft Certified: Azure Data Fundamentals credential is an entry-level certification - Microsoft lists its level as Beginner - that demonstrates foundational knowledge of core data concepts and the Microsoft Azure data services built around them. It sits in the Databases subject area and is associated with the Data Engineer role, but the intended audience is broader: candidates just beginning to work with data in the cloud who want a recognized starting point. It can help you prepare for role-based certifications such as Azure Database Administrator Associate or Azure Data Engineer Associate, although Microsoft states it is not a prerequisite for any of them.

Exam DP-900 is a proctored assessment scheduled through Pearson VUE, or through Certiport if you are a student or educator. You have 45 minutes to complete it, it may include interactive components alongside standard question types, and a score of 700 or greater is required to pass. The skills measured as of July 21, 2026 span four areas: core data concepts, relational data on Azure, non-relational data on Azure, and analytics workloads on Azure.

Preparation is self-directed: Microsoft recommends self-paced or instructor-led learning material plus hands-on experience. A free practice assessment is available through AI Skills Navigator, and an exam sandbox lets you preview the real question types before test day.

Common mistakes

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.

Exam requirements can change. Confirm current details with the exam provider.

Azure DP-900 practice

197 questions. Each one lists the choices, the correct answer, and an explanation. With JavaScript, this list opens one question at a time. The checker at the top of this page saves progress in this browser when it is available.

  1. Question 1Describe core data concepts

    Which term describes data that conforms to a fixed schema and is stored in tables with rows and columns?

    1. Streaming data
    2. Structured data Correct answer.
    3. Unstructured data
    4. Semi-structured data

    Explanation

    Structured data has a predefined schema and is organized into tables of rows and columns, which makes it easy to query with SQL. Semi-structured data has some organization but not a fixed table schema, unstructured data has no predefined model, and streaming data refers to how data is delivered rather than its structure.

  2. Question 2Describe core data concepts

    In a relational database, what is a primary key?

    1. A column that references a row in another table
    2. A column that may contain duplicate values
    3. A column used only to sort query results
    4. A column or set of columns that uniquely identifies each row Correct answer.

    Explanation

    A primary key is a column or combination of columns whose values uniquely identify every row in a table, and it cannot contain nulls. A foreign key references another table, and a sort column does not enforce uniqueness.

  3. Question 3Describe core data concepts

    Which workload is characterized by many small, short transactions that insert, update, or delete individual records?

    1. Batch reporting workload
    2. Data warehousing workload
    3. Analytical workload
    4. Transactional workload Correct answer.

    Explanation

    Transactional (OLTP) workloads consist of many short, concurrent read and write operations on individual records. Analytical workloads scan large volumes for reporting, and batch reporting runs on a schedule rather than as per-record transactions.

  4. Question 4Describe core data concepts

    Which type of data store is designed to hold documents, key-value pairs, or graphs without requiring a fixed relational schema?

    1. Data warehouse
    2. Relational database
    3. Star schema
    4. NoSQL database Correct answer.

    Explanation

    NoSQL databases include document, key-value, column-family, and graph stores, which do not require a fixed relational schema. Relational databases and data warehouses use schemas and tables, and a star schema is a modeling pattern rather than a store type.

  5. Question 5Describe core data concepts

    Which role is primarily responsible for building and maintaining pipelines that move and transform data?

    1. Data scientist
    2. Database administrator
    3. Data engineer Correct answer.
    4. Data analyst

    Explanation

    Data engineers build and maintain the pipelines and infrastructure that ingest, transform, and move data between systems. Analysts focus on reporting, scientists on modeling and experimentation, and database administrators on database availability and performance.

  6. Question 6Describe core data concepts

    Which file format stores data as comma-separated values in plain text?

    1. CSV Correct answer.
    2. JSON
    3. Parquet
    4. Avro

    Explanation

    CSV (comma-separated values) is a plain-text format in which fields are separated by commas and rows by line breaks. JSON is a text format with key-value structure, while Parquet and Avro are binary formats.

  7. Question 7Describe core data concepts

    A data warehouse is optimized for which type of workload?

    1. Real-time device telemetry ingestion
    2. High-volume transaction processing
    3. Managing session state for web applications
    4. Complex analytical queries over large historical datasets Correct answer.

    Explanation

    A data warehouse is optimized for analytical (OLAP) workloads, which involve complex queries that aggregate and scan large historical datasets. Transactional systems are optimized for many small writes, and telemetry ingestion is a streaming workload.

  8. Question 8Describe core data concepts

    Which characteristic best describes semi-structured data?

    1. It contains tags or markers that separate semantic elements but does not follow a rigid table schema. Correct answer.
    2. It must be stored in a relational table with a fixed schema.
    3. It is always stored as binary large objects.
    4. It cannot contain nested elements.

    Explanation

    Semi-structured data uses tags or markers, such as keys in JSON or elements in XML, to separate semantic elements, but it does not require a rigid table schema. Structured data fits tables, and unstructured data lacks a predefined model.

  9. Question 9Describe core data concepts

    What is the main purpose of normalizing a relational database?

    1. To increase query speed by duplicating data
    2. To convert structured data into unstructured data
    3. To eliminate the need for primary keys
    4. To reduce data duplication and update anomalies Correct answer.

    Explanation

    Normalization organizes tables to minimize redundancy and dependency, which reduces duplicate data and avoids update, insert, and delete anomalies. Denormalization intentionally duplicates data to improve read performance.

  10. Question 10Describe core data concepts

    In an ETL process, what happens during the Transform step?

    1. Data is loaded into the destination warehouse.
    2. Data is extracted from source systems.
    3. Data is deleted from the source system.
    4. Data is cleansed, validated, and converted into a target format. Correct answer.

    Explanation

    The Transform step cleanses, validates, standardizes, and converts extracted data into the structure required by the target. Extract retrieves data from sources, and Load writes the result to the destination.

  11. Question 11Describe core data concepts

    Which statement describes a data lake?

    1. It enforces strict schema-on-write for all data.
    2. It stores large volumes of raw data in its native format until it is needed. Correct answer.
    3. It stores only highly structured data in relational tables.
    4. It is limited to data smaller than 1 GB.

    Explanation

    A data lake stores raw data in its native format, including structured, semi-structured, and unstructured data, until it is needed, often using schema-on-read. Data warehouses typically enforce schema-on-write and store structured data.

  12. Question 12Describe core data concepts

    Which processing approach handles data continuously as it arrives, with very low latency?

    1. Batch processing
    2. Stream processing Correct answer.
    3. ETL scheduled nightly
    4. OLTP

    Explanation

    Stream processing analyzes data continuously as it arrives, which supports near-real-time scenarios. Batch processing handles data in scheduled chunks, and nightly ETL is a batch pattern.

  13. Question 13Describe core data concepts

    A foreign key in a relational table is used to do what?

    1. Encrypt sensitive columns
    2. Uniquely identify each row in the same table
    3. Sort rows alphabetically
    4. Establish a link to a primary key in another table Correct answer.

    Explanation

    A foreign key is a column or set of columns whose values must match a primary key or unique key in another table, creating a referential link between the tables. A primary key enforces uniqueness within its own table.

  14. Question 14Describe core data concepts

    Which property of a transaction ensures that it is completed entirely or not at all?

    1. Isolation
    2. Atomicity Correct answer.
    3. Consistency
    4. Durability

    Explanation

    Atomicity guarantees that all operations in a transaction succeed or the entire transaction is rolled back. Durability persists committed changes, consistency enforces rules, and isolation prevents interference between concurrent transactions.

  15. Question 15Describe core data concepts

    In a relational model, what does cardinality describe?

    1. The number of rows in a table or the relationship between entities Correct answer.
    2. The physical size of a database file
    3. The number of columns in a table
    4. The data type of a column

    Explanation

    Cardinality refers to the number of rows in a relation or to the numeric relationship between entity instances, such as one-to-many. It does not describe column counts, data types, or file size.

  16. Question 16Describe core data concepts

    Which of the following is an example of unstructured data?

    1. A table of customer orders
    2. A JSON document with defined fields
    3. A collection of audio recordings Correct answer.
    4. A CSV file of sales figures

    Explanation

    Audio recordings have no predefined data model or schema, so they are unstructured. Tables, CSV files, and JSON documents all have some defined structure or organization.

  17. Question 17Describe core data concepts

    Which task is most typical for a data analyst?

    1. Writing low-level device drivers
    2. Creating reports and visualizations from existing data Correct answer.
    3. Managing network firewalls
    4. Designing storage engines for databases

    Explanation

    Data analysts explore and interpret existing data to produce reports, dashboards, and visualizations for decision-makers. They generally do not design storage engines or manage network infrastructure.

  18. Question 18Describe core data concepts

    What is a database view?

    1. A backup of the transaction log
    2. A physical copy of a table stored on disk
    3. A type of index used for full-text search
    4. A virtual table defined by a stored query Correct answer.

    Explanation

    A view is a virtual table whose contents are defined by a stored query, and it does not store data separately unless it is a materialized view. It is not an index, a log backup, or a physical copy.

  19. Question 19Describe core data concepts

    Which characteristic distinguishes OLAP systems from OLTP systems?

    1. OLAP systems are normalized to third normal form.
    2. OLAP systems use denormalized schemas and read-heavy queries. Correct answer.
    3. OLAP systems typically process many short write transactions.
    4. OLAP systems store only current-day data.

    Explanation

    OLAP systems typically use denormalized star or snowflake schemas and are read-heavy, scanning large volumes for aggregation. OLTP systems handle many short write transactions and are usually highly normalized.

  20. Question 20Describe core data concepts

    What is a data lakehouse?

    1. A data warehouse that cannot store unstructured data
    2. A relational database used only for OLTP
    3. A platform that combines data lake storage with data warehouse management features Correct answer.
    4. A file system that does not support transactions

    Explanation

    A data lakehouse combines low-cost data lake storage with data warehouse features such as schema enforcement, transactions, and performance optimizations. It is not limited to OLTP or structured-only data.

  21. Question 21Describe core data concepts

    Why is Parquet often preferred over CSV for analytical workloads?

    1. It stores data as plain text for easy editing.
    2. It can store only a single column per file.
    3. It requires no schema.
    4. It is a columnar format that supports compression and efficient column reads. Correct answer.

    Explanation

    Parquet is a columnar binary format that compresses well and lets a query read only the columns it needs, which improves analytical scan performance. CSV is row-based plain text and generally requires reading entire rows.

  22. Question 22Describe core data concepts

    What does sharding a database involve?

    1. Partitioning data across multiple servers based on a key Correct answer.
    2. Converting all tables to unstructured files
    3. Creating a duplicate copy of the entire database for backup
    4. Removing all indexes to speed up writes

    Explanation

    Sharding horizontally partitions data across multiple servers, often by a shard key, so that each server holds a subset of the data. Replication creates copies, which is a different technique.

  23. Question 23Describe core data concepts

    What does data lineage describe?

    1. The encryption algorithm used for a column
    2. The physical location of backup tapes
    3. The number of users who can query a table
    4. The origin and movement of data through a system over time Correct answer.

    Explanation

    Data lineage tracks where data originated and how it moves and transforms through systems over time, which supports auditing and impact analysis. It does not describe backups, encryption algorithms, or access counts.

  24. Question 24Describe core data concepts

    How does a snowflake schema differ from a star schema?

    1. Its dimension tables are normalized into multiple related tables. Correct answer.
    2. It contains no fact table.
    3. It stores only unstructured data.
    4. It cannot be used in a data warehouse.

    Explanation

    In a snowflake schema, dimension tables are normalized into multiple related tables, whereas a star schema keeps dimensions denormalized in single tables. Both contain fact and dimension tables and are used in warehouses.

  25. Question 25Describe core data concepts

    Which statement best describes the difference between a database and a data warehouse?

    1. A data warehouse cannot store historical data.
    2. A database cannot store structured data.
    3. A database is optimized for day-to-day transactions, while a data warehouse is optimized for analytical reporting. Correct answer.
    4. A data warehouse is used only for OLTP.

    Explanation

    An operational database is optimized for day-to-day transactional processing, while a data warehouse is designed for analytical reporting over consolidated historical data. Warehouses commonly store historical data and are not used for OLTP.

  26. Question 26Describe core data concepts

    Which term describes data that is organized into a predefined schema of tables, rows, and columns?

    1. Metadata
    2. Semi-structured data
    3. Structured data Correct answer.
    4. Unstructured data

    Explanation

    Structured data conforms to a predefined schema, so every row has the same columns and each column has a defined type. This makes it easy to query with SQL and to enforce constraints. Semi-structured and unstructured data do not follow a fixed table schema, and metadata describes data rather than being the data itself.

  27. Question 27Describe core data concepts

    Which data type is designed to store a calendar date with no time component?

    1. Datetime
    2. Boolean
    3. Date Correct answer.
    4. Time

    Explanation

    A date type stores a year, month, and day only, so it is the right choice when no clock time is needed. Datetime types add a time component, a time type stores only a clock value, and Boolean stores true or false.

  28. Question 28Describe core data concepts

    How is a data mart best described?

    1. A copy of every source system in the organization
    2. A cache that speeds up transactional queries
    3. A subset of a data warehouse focused on one business area Correct answer.
    4. A staging area that holds raw files before transformation

    Explanation

    A data mart is a smaller, focused slice of analytical data serving a single department or subject area. It can be sourced from a warehouse or built independently. A full copy of all sources is closer to a warehouse, raw files belong in a landing zone, and a cache is a performance tier.

  29. Question 29Describe core data concepts

    Which role is primarily responsible for building and maintaining the pipelines that move and transform data?

    1. Data engineer Correct answer.
    2. Data scientist
    3. Business analyst
    4. Database administrator

    Explanation

    Data engineers design, build, and operate the pipelines and storage that move and transform data between systems. Data scientists build models, database administrators manage database availability and security, and business analysts gather requirements and interpret results.

  30. Question 30Describe core data concepts

    Which SQL statement is used to retrieve rows from a table without changing the data?

    1. UPDATE
    2. SELECT Correct answer.
    3. INSERT
    4. DELETE

    Explanation

    SELECT is the data manipulation statement that queries and returns rows. INSERT adds rows, UPDATE modifies existing rows, and DELETE removes rows, so all three change stored data.

  31. Question 31Describe core data concepts

    What is the main purpose of creating an index on a relational table?

    1. To define relationships between tables
    2. To speed up retrieval of rows that match a search condition Correct answer.
    3. To compress all table data on disk
    4. To enforce uniqueness across every column of a table

    Explanation

    An index is an auxiliary structure keyed on one or more columns that points to the matching rows, so lookups and joins can avoid scanning the whole table. Uniqueness is enforced only when the index is declared unique and only on its own columns. Compression is a storage option, and relationships are defined by foreign keys.

  32. Question 32Describe core data concepts

    A data cleansing process finds several records that describe the same customer. Which task addresses this problem?

    1. Deduplication Correct answer.
    2. Validation
    3. Normalization
    4. Aggregation

    Explanation

    Deduplication detects repeated records and either removes them or merges them into a single trusted record. Normalization restructures tables to reduce redundancy, validation checks values against rules, and aggregation summarizes detail into totals.

  33. Question 33Describe core data concepts

    Which format represents data as a collection of name-value pairs and supports nested objects and arrays?

    1. JSON Correct answer.
    2. Fixed-width text
    3. CSV
    4. Parquet

    Explanation

    JSON encodes data as name-value pairs and allows objects and arrays to be nested inside one another, which makes it common for semi-structured data exchange. CSV and fixed-width text use positional columns, and Parquet is a binary columnar format for analytics.

  34. Question 34Describe core data concepts

    A table has a primary key formed by combining the OrderID and ProductID columns. What is this key called?

    1. Surrogate key
    2. Foreign key
    3. Alternate key
    4. Composite key Correct answer.

    Explanation

    A composite key uses two or more columns together to uniquely identify a row. A foreign key references a key in another table, a surrogate key is system-generated, and an alternate key is a candidate key that was not chosen as the primary key.

  35. Question 35Describe core data concepts

    Which ACID property guarantees that once a transaction commits, its changes survive a power failure?

    1. Durability Correct answer.
    2. Consistency
    3. Isolation
    4. Atomicity

    Explanation

    Durability means committed changes are written to durable storage and remain available after a crash or restart. Atomicity makes a transaction all-or-nothing, isolation controls visibility between concurrent transactions, and consistency keeps the database within its rules.

  36. Question 36Describe core data concepts

    In an ELT process, how does the load step differ from a traditional ETL process?

    1. Data is loaded only after a person approves each batch
    2. Data is loaded in raw form and transformed inside the target system Correct answer.
    3. Data is never transformed at any stage
    4. Data is transformed before it is loaded into the target

    Explanation

    ELT loads raw data into the target platform first and then uses that platform's compute to transform it, which suits scalable cloud warehouses. ETL transforms data before loading it. Manual approval is a workflow choice rather than the defining difference.

  37. Question 37Describe core data concepts

    Many rows in a customer table have an empty Phone column. Which data quality dimension is most directly affected?

    1. Uniqueness
    2. Accuracy
    3. Completeness Correct answer.
    4. Timeliness

    Explanation

    Completeness measures whether all required values are present, so missing phone numbers reduce completeness. Timeliness concerns availability when needed, uniqueness concerns duplicate records, and accuracy concerns whether values reflect reality.

  38. Question 38Describe core data concepts

    Which data model is the highest-level model and shows only business entities and the relationships between them?

    1. Physical data model
    2. Conceptual data model Correct answer.
    3. Dimensional model
    4. Logical data model

    Explanation

    A conceptual model captures business entities and their relationships for communication with stakeholders and omits technical detail. A logical model adds attributes and keys, a physical model adds tables, indexes, and platform details, and a dimensional model is an analytical modeling style.

  39. Question 39Describe core data concepts

    Which type of NoSQL store is best suited for data whose value comes from the relationships among items, such as a social network?

    1. Column-family store
    2. Document store
    3. Key-value store
    4. Graph database Correct answer.

    Explanation

    A graph database stores nodes and edges and can traverse relationships efficiently, which suits connected data. Key-value and document stores optimize simple lookups and document retrieval, and column-family stores handle wide sparse rows.

  40. Question 40Describe core data concepts

    In a dimensional model, what does a fact table primarily contain?

    1. Descriptive attributes of business entities
    2. Numeric measures and keys that reference dimension tables Correct answer.
    3. A copy of every source table
    4. Metadata about the warehouse

    Explanation

    A fact table stores measurable events such as sales amounts together with foreign keys to the dimension tables that describe them. Descriptive attributes belong in dimension tables, and warehouse metadata belongs in a catalog.

  41. Question 41Describe core data concepts

    What is the primary purpose of a data catalog?

    1. To enforce row-level security policies inside a database
    2. To store the physical files that make up a data lake
    3. To provide a searchable inventory of data assets Correct answer.
    4. To schedule and monitor nightly ETL jobs across systems

    Explanation

    A data catalog records metadata such as descriptions, owners, locations, and classifications so users can find and trust data assets. File storage, access control, and job scheduling are handled by other components.

  42. Question 42Describe core data concepts

    Data governance is a set of policies and processes that primarily does what?

    1. Defines who can act on which data and how quality is maintained Correct answer.
    2. Automatically encrypts every database in the organization
    3. Converts unstructured data into relational tables
    4. Replaces the need for a data warehouse

    Explanation

    Data governance assigns decision rights and accountability, sets policies for access and quality, and defines how data is managed across the organization. Encryption is one possible control, and governance does not replace storage platforms or change data formats.

  43. Question 43Describe core data concepts

    Which characteristic is typical of batch processing?

    1. It requires a graph database
    2. Large volumes are processed together on a schedule Correct answer.
    3. Records are handled one at a time as they arrive
    4. Latency is measured in milliseconds

    Explanation

    Batch processing collects a large set of records and processes them together at planned intervals, such as a nightly job. Handling records individually as they arrive with millisecond latency describes stream processing, and the data store choice is independent.

  44. Question 44Describe core data concepts

    In a commonly used data lake organization, what is the purpose of the raw landing zone?

    1. To archive deleted records for legal hold
    2. To serve dashboards directly to business users
    3. To hold source data exactly as received before transformation Correct answer.
    4. To store only aggregated reporting tables

    Explanation

    The raw or landing zone keeps source data in its original form so it can be reprocessed later if transformation logic changes. Aggregated reporting tables and dashboards are served from curated or serving layers, and legal archives are a separate retention concern.

  45. Question 45Describe core data concepts

    Which ACID property prevents one transaction from seeing uncommitted changes made by another?

    1. Atomicity
    2. Consistency
    3. Durability
    4. Isolation Correct answer.

    Explanation

    Isolation controls how concurrent transactions interact and prevents them from reading each other's uncommitted work. Durability protects committed changes, atomicity makes a transaction all-or-nothing, and consistency preserves database rules.

  46. Question 46Describe core data concepts

    A customer dimension must keep a full history of address changes. Which technique should be used?

    1. Store the address only in the fact table
    2. Overwrite the old value with the new value and keep no history
    3. Add a new row with validity dates and keep the old row Correct answer.
    4. Truncate the dimension and reload it every night

    Explanation

    Keeping history calls for a type 2 slowly changing dimension, which inserts a new version of the row with effective dates and flags the previous row as expired. Overwriting values is a type 1 change that loses history, and reloading or moving attributes to the fact table does not preserve versions.

  47. Question 47Describe core data concepts

    A system-generated integer key with no business meaning is added to a dimension table. What is this key called?

    1. Composite key
    2. Foreign key
    3. Surrogate key Correct answer.
    4. Natural key

    Explanation

    A surrogate key is generated by the system and carries no business meaning, which keeps history stable when business identifiers change. A natural key comes from business data, a composite key combines columns, and a foreign key references another table.

  48. Question 48Describe core data concepts

    What does granularity mean for a fact table?

    1. The number of dimension tables joined to the fact table
    2. The level of detail represented by each row Correct answer.
    3. The number of partitions used for storage
    4. The physical size of each row on disk

    Explanation

    Granularity, or grain, defines what one fact row represents, such as one line item versus one daily total. It determines which dimensions can be used and how detailed the analysis can be. Row size, partitioning, and dimension count are separate design concerns.

  49. Question 49Describe core data concepts

    Master data management is primarily concerned with which goal?

    1. Maximizing the compression ratio of archived files
    2. Recording the lineage of every transformation step
    3. Converting transactional workloads into batch workloads
    4. Providing a single consistent view of core entities Correct answer.

    Explanation

    Master data management creates and maintains trusted, shared definitions of core entities such as customers, products, and suppliers across systems. Compression, lineage tracking, and workload conversion are separate technical or governance activities.

  50. Question 50Describe core data concepts

    A data pipeline has tasks that must run in a specific order, with retries when a step fails. Which capability provides this?

    1. Sharding
    2. Pipeline orchestration Correct answer.
    3. Data masking
    4. Data virtualization

    Explanation

    Orchestration coordinates tasks, enforces dependencies and ordering, and handles retries and monitoring across a pipeline. Virtualization queries data without copying it, masking hides sensitive values, and sharding distributes data across partitions.

  51. Question 51Describe core data concepts

    Which statement best describes structured data?

    1. Data stored only as binary large objects such as images and audio.
    2. Data that cannot be indexed because it has no defined format.
    3. Data whose schema is discovered at read time from self-describing documents.
    4. Data that conforms to a predefined schema of rows and columns. Correct answer.

    Explanation

    Structured data follows a predefined schema and is typically organized into tables with rows and columns. This makes it easy to query with languages such as SQL. Semi-structured and unstructured data do not fit neatly into fixed relational tables.

  52. Question 52Describe core data concepts

    A retail system records individual sales as they happen, with many short insert and update transactions. Which workload type is this?

    1. Stream processing that aggregates events over sliding windows.
    2. Analytical processing that scans large historical datasets.
    3. Transactional processing that supports day-to-day operations. Correct answer.
    4. Batch processing that runs on a nightly schedule.

    Explanation

    Transactional processing, often called OLTP, handles frequent short read and write operations that support everyday business activity. Analytical processing instead focuses on large-scale queries over historical data. Batch and stream processing describe how data is ingested or computed, not the transactional nature of the workload.

  53. Question 53Describe core data concepts

    A data warehouse uses a star schema, but one dimension is normalized into multiple related tables to reduce redundancy. What is this schema called?

    1. Fact constellation
    2. Denormalized star
    3. Flat schema
    4. Snowflake schema Correct answer.

    Explanation

    A snowflake schema extends a star schema by normalizing dimension tables into multiple related tables. This reduces redundancy but can require more joins. A star schema keeps each dimension as a single denormalized table.

  54. Question 54Describe core data concepts

    A team stores analytical data in Parquet instead of CSV. Which benefit does Parquet primarily provide for analytical queries?

    1. It enforces referential integrity between tables.
    2. It supports only row-by-row transactional updates.
    3. It stores data as human-readable plain text.
    4. It reads only the columns referenced by a query. Correct answer.

    Explanation

    Parquet is a columnar file format, so a query can read only the columns it needs instead of scanning entire rows. This reduces input/output and improves analytical query performance. Parquet does not enforce relational constraints and is not a plain-text format.

  55. Question 55Describe core data concepts

    A monitoring system must ingest thousands of sensor events per second and produce alerts within seconds. Which processing approach fits this requirement?

    1. Stream processing that handles events as they arrive. Correct answer.
    2. Archival processing that compresses data after it is no longer needed.
    3. Manual processing performed by an operator each day.
    4. Batch processing that processes accumulated files on a schedule.

    Explanation

    Stream processing consumes events continuously as they arrive and can produce near-real-time results such as alerts. Batch processing introduces delay because it operates on accumulated data at scheduled intervals. Manual and archival processing do not meet low-latency event handling requirements.

  56. Question 56Describe core data concepts

    An analyst uses historical sales data to recommend which products to stock next month by evaluating possible outcomes of each ordering decision. Which type of analytics is this?

    1. Diagnostic analytics that explains why something happened.
    2. Prescriptive analytics that recommends an action. Correct answer.
    3. Descriptive analytics that summarizes what happened.
    4. Reporting that displays current inventory levels.

    Explanation

    Prescriptive analytics goes beyond prediction by recommending actions and evaluating possible outcomes. Descriptive analytics summarizes past events, while diagnostic analytics investigates causes. Reporting presents current or historical facts without recommending a decision.

  57. Question 57Identify considerations for relational data on Azure

    In a relational database, what does a column in a table represent?

    1. An attribute of the entity stored in the table Correct answer.
    2. A single instance of the entity
    3. A physical file that stores the table
    4. A relationship to another table

    Explanation

    Tables model an entity type, each row is one instance of that entity, and each column describes an attribute of it. Every column has a defined data type that constrains the values it can hold.

  58. Question 58Identify considerations for relational data on Azure

    Which description matches a primary key in a relational table?

    1. A column or columns that uniquely identify each row Correct answer.
    2. A column that links a row to a related row in another table
    3. A constraint that restricts values to a defined list
    4. A column that is allowed to contain duplicate values

    Explanation

    A primary key enforces uniqueness for the column or combination of columns that identify a row, and it cannot contain nulls. Other tables reference it through foreign keys.

  59. Question 59Identify considerations for relational data on Azure

    Which category of SQL statements is used to create, alter, and drop tables and other schema objects?

    1. DQL
    2. DDL Correct answer.
    3. DML
    4. DCL

    Explanation

    Data Definition Language (DDL) statements such as CREATE, ALTER, and DROP change the structure of database objects. DML works with the rows inside those objects.

  60. Question 60Identify considerations for relational data on Azure

    Which Azure service offers the closest compatibility with on-premises SQL Server, including features such as SQL Server Agent and Service Broker?

    1. Azure SQL Managed Instance Correct answer.
    2. Azure SQL Database single database
    3. Azure Table Storage
    4. Azure Database for PostgreSQL

    Explanation

    Azure SQL Managed Instance runs the SQL Server engine in a near-100% compatible surface, including SQL Server Agent, Service Broker, and cross-database queries. Azure SQL Database intentionally omits several instance-level features.

  61. Question 61Identify considerations for relational data on Azure

    A table contains a column that stores the value of the primary key from a related table. What is this column called?

    1. A candidate key
    2. A foreign key Correct answer.
    3. A surrogate key
    4. A super key

    Explanation

    A foreign key holds values that must match a primary or unique key in another table, which is how referential integrity between tables is enforced.

  62. Question 62Identify considerations for relational data on Azure

    In the DTU purchasing model for Azure SQL Database, what does a DTU measure?

    1. Network bandwidth only
    2. Backup retention period only
    3. Storage capacity only
    4. Compute, memory, and I/O combined Correct answer.

    Explanation

    A Database Transaction Unit bundles CPU, memory, and read/write I/O into a single blended measure so you do not size each resource individually. Storage is selected separately from the DTU level.

  63. Question 63Identify considerations for relational data on Azure

    Which Azure SQL Database option lets several databases share a common pool of compute and storage resources?

    1. A single database
    2. A managed instance
    3. A virtual machine
    4. An elastic pool Correct answer.

    Explanation

    An elastic pool places multiple databases on shared resources so that peaks in one database can be absorbed by capacity that another database is not using at that moment. This suits databases whose usage patterns differ.

  64. Question 64Identify considerations for relational data on Azure

    A company needs nearly complete SQL Server compatibility on Azure, including SQL Server Agent jobs, linked servers, and cross-database queries. Which service should they choose?

    1. Azure SQL Database single database
    2. Azure SQL Managed Instance Correct answer.
    3. Azure Synapse Analytics dedicated SQL pool
    4. Azure Database for MySQL

    Explanation

    Azure SQL Managed Instance provides instance-level SQL Server capabilities such as SQL Server Agent, linked servers, and cross-database queries. Azure SQL Database does not surface those instance-scoped features.

  65. Question 65Identify considerations for relational data on Azure

    How does the vCore purchasing model differ from the DTU model for Azure SQL Database?

    1. It removes the need to select a service tier
    2. It measures throughput only in database transaction units
    3. It lets you choose compute, memory, and storage independently Correct answer.
    4. It applies only to Azure Database for MySQL

    Explanation

    The vCore model exposes virtual cores, memory, and storage as separate choices so you can size them to your workload. It also translates directly to on-premises core licensing, which enables benefits such as Azure Hybrid Benefit.

  66. Question 66Identify considerations for relational data on Azure

    Which Azure SQL Database compute tier can automatically scale compute and pause a database when it is idle?

    1. Hyperscale
    2. Provisioned
    3. Serverless Correct answer.
    4. Basic

    Explanation

    The serverless compute tier scales vCores automatically based on activity and can auto-pause a database during inactivity, at which point only storage is billed. It is intended for intermittent, unpredictable workloads.

  67. Question 67Identify considerations for relational data on Azure

    Which Azure SQL Database service tier is intended for very large databases of up to 100 TB with fast restore times?

    1. Hyperscale Correct answer.
    2. General Purpose
    3. Basic
    4. Business Critical

    Explanation

    Hyperscale uses a distributed architecture with a scale-out storage layer, supporting databases up to roughly 100 TB and making backup and restore operations fast regardless of database size.

  68. Question 68Identify considerations for relational data on Azure

    A team needs PostgreSQL with community extensions such as PostGIS and pg_stat_statements. Which Azure service supports this requirement?

    1. Azure SQL Database
    2. Azure Database for MySQL flexible server
    3. Azure Synapse Analytics
    4. Azure Database for PostgreSQL flexible server Correct answer.

    Explanation

    Azure Database for PostgreSQL flexible server allows a supported set of PostgreSQL extensions to be enabled on the server. The engine is PostgreSQL, so PostgreSQL-specific extensions are available.

  69. Question 69Identify considerations for relational data on Azure

    Which capability restricts which client IP addresses may connect to an Azure SQL logical server?

    1. Server-level and database-level firewall rules Correct answer.
    2. Always Encrypted
    3. Transparent data encryption
    4. Dynamic data masking

    Explanation

    Azure SQL firewall rules evaluate the originating IP address of a connection and reject requests that do not match an allowed range. Rules can be defined at the server level and, where supported, at the database level.

  70. Question 70Identify considerations for relational data on Azure

    What does Transparent Data Encryption (TDE) provide in Azure SQL?

    1. It masks column values from non-privileged users
    2. It encrypts chosen columns in the client application
    3. It encrypts data at rest and backups Correct answer.
    4. It encrypts traffic between client and server

    Explanation

    TDE encrypts the database, its log, and its backups at rest without requiring changes to the application. It is enabled by default for new Azure SQL databases.

  71. Question 71Identify considerations for relational data on Azure

    Which Azure SQL capability restores a database to an earlier moment within the configured retention period?

    1. Long-term retention
    2. Point-in-time restore Correct answer.
    3. Geo-restore
    4. Copy-only backup

    Explanation

    Point-in-time restore uses the automated backup chain to rebuild a database as it existed at a chosen timestamp inside the retention window. This is used to recover from accidental data changes.

  72. Question 72Identify considerations for relational data on Azure

    When you create an Azure SQL Database, it is hosted on a logical server. What does this server provide?

    1. A central administrative and security boundary for its databases Correct answer.
    2. A network gateway for on-premises connections only
    3. A container for tables within a single database
    4. A physical virtual machine dedicated to one database

    Explanation

    The logical server is an administrative grouping that supplies shared settings such as logins, firewall rules, auditing, and failover group membership for the databases it contains. It is not a physical machine you manage.

  73. Question 73Identify considerations for relational data on Azure

    Many departmental databases have low average usage but occasionally spike sharply. Which Azure SQL Database configuration fits this pattern most cost-effectively?

    1. A single database with steady, high provisioned capacity
    2. A workload requiring SQL Server Agent and linked servers
    3. A database sized above 100 TB
    4. An elastic pool shared by the departmental databases Correct answer.

    Explanation

    Elastic pools let databases with offsetting peaks draw from a shared capacity pool, so the combined cost is lower than provisioning each database for its own worst case. This matches workloads whose peaks rarely coincide.

  74. Question 74Identify considerations for relational data on Azure

    A company wants to move an on-premises MySQL application to Azure with minimal changes and no operating system patching. Which service should it use?

    1. Azure Database for MySQL Correct answer.
    2. Azure Blob Storage
    3. SQL Server on Azure virtual machines
    4. Azure SQL Managed Instance

    Explanation

    Azure Database for MySQL is a fully managed platform-as-a-service engine, so Microsoft handles patching and availability while the application keeps using MySQL. Choosing a SQL Server-based service would require rewriting the application.

  75. Question 75Identify considerations for relational data on Azure

    Which SQL statement adds new rows to an existing table?

    1. CREATE TABLE
    2. INSERT Correct answer.
    3. UPDATE
    4. ALTER TABLE

    Explanation

    INSERT is the Data Manipulation Language statement that adds one or more new rows to a table. UPDATE changes existing rows, and DDL statements change the table structure.

  76. Question 76Identify considerations for relational data on Azure

    Which Azure SQL feature automatically fails over a set of databases to a secondary region and provides a read-write listener endpoint?

    1. Active geo-replication
    2. Zone-redundant configuration
    3. Long-term retention
    4. An auto-failover group Correct answer.

    Explanation

    An auto-failover group groups one or more databases for automatic failover to a paired secondary region and exposes listener endpoints that redirect clients without connection string changes.

  77. Question 77Identify considerations for relational data on Azure

    Which statement accurately describes the Business Critical service tier of Azure SQL Database?

    1. It has the lowest cost of all service tiers
    2. It is available only in the DTU purchasing model
    3. It uses local SSD storage and built-in readable replicas Correct answer.
    4. It uses remote premium storage with a single replica

    Explanation

    Business Critical uses local solid-state storage for low latency and includes multiple built-in secondary replicas, which support read scale-out and fast failover. It also supports in-memory OLTP features.

  78. Question 78Identify considerations for relational data on Azure

    How does network connectivity for Azure SQL Managed Instance differ from Azure SQL Database?

    1. SQL Database cannot use private endpoints
    2. Managed instance is deployed into a virtual network subnet Correct answer.
    3. SQL Database requires a VPN gateway in every case
    4. Managed instance requires a public IP address

    Explanation

    Azure SQL Managed Instance is deployed inside a dedicated subnet of a virtual network and is reached through a private IP address by default. Azure SQL Database runs on shared infrastructure and uses a public endpoint plus optional private endpoints or service endpoints.

  79. Question 79Identify considerations for relational data on Azure

    A database has unpredictable, intermittent usage with long idle periods, and the team wants the lowest compute cost while keeping data online. Which configuration fits best?

    1. Hyperscale with multiple read replicas
    2. Serverless compute tier with auto-pause enabled Correct answer.
    3. An elastic pool with a fixed minimum eDTU
    4. Provisioned General Purpose with 8 vCores

    Explanation

    Serverless bills compute only while the database is running and, with auto-pause, stops compute entirely after an idle period so only storage is charged. This matches spiky workloads that are idle much of the time.

  80. Question 80Identify considerations for relational data on Azure

    A reporting workload joins many normalized tables and performs poorly. Which design change addresses this while accepting some data redundancy?

    1. Convert every primary key into a foreign key
    2. Denormalize by combining tables into wider tables Correct answer.
    3. Remove all indexes from the reporting tables
    4. Normalize the tables further to fifth normal form

    Explanation

    Denormalization deliberately reintroduces redundancy, for example by combining related tables or storing precomputed values, which reduces the number of joins a read-heavy query must perform. The trade-off is extra storage and more careful update logic.

  81. Question 81Identify considerations for relational data on Azure

    In a relational database table, what is the purpose of a primary key?

    1. It links the table to another table by referencing that table's key column
    2. It encrypts the values stored in the table
    3. It sorts the rows physically on disk in a fixed order
    4. It uniquely identifies each row in the table Correct answer.

    Explanation

    A primary key is a column or set of columns whose values are unique and non-null for every row, so each row can be identified unambiguously. It is enforced by a constraint that rejects duplicate or null key values.

  82. Question 82Identify considerations for relational data on Azure

    What does a foreign key enforce between two tables?

    1. That every column in the child table has a default value defined
    2. That values in the child table exist in the parent table Correct answer.
    3. That the parent table contains no duplicate rows of any kind
    4. That both tables are stored in the same filegroup

    Explanation

    A foreign key is a column in the child table whose values must match values in the parent table's key, which enforces referential integrity. Inserts or updates that would leave an orphaned value are rejected unless the constraint allows it.

  83. Question 83Identify considerations for relational data on Azure

    Why would you create an index on a column in a relational table?

    1. To store an editable copy of the table that is kept separate from the original
    2. To speed up queries that filter or sort on that column Correct answer.
    3. To prevent the column from accepting NULL values in any row
    4. To automatically generate a surrogate key value for each new row when rows are inserted

    Explanation

    An index is a supporting structure that lets the engine locate rows by key value instead of scanning the whole table, which reduces the work for filtering and ordering. The trade-off is extra storage and slower writes, because the index must be maintained.

  84. Question 84Identify considerations for relational data on Azure

    What is a view in a relational database?

    1. A physical structure that stores rows in sorted order
    2. A constraint that limits the values a column can contain
    3. A stored copy of a table that is refreshed on a schedule
    4. A saved query that is used like a table Correct answer.

    Explanation

    A view is a named, stored query definition; when you query the view, the engine runs the underlying query and returns the result set. It does not normally store data itself and can be used to simplify access or restrict which columns users see.

  85. Question 85Identify considerations for relational data on Azure

    A company must control the operating system, the registry, and the SQL Server patch level for its database workload on Azure. Which deployment option should it choose?

    1. Azure Database for PostgreSQL
    2. Azure SQL Database single database
    3. SQL Server on Azure VMs Correct answer.
    4. Azure SQL Managed Instance

    Explanation

    SQL Server on Azure virtual machines is an IaaS option, so the customer manages the operating system, patching, and instance configuration. Fully managed PaaS options take that control away in exchange for reduced administration.

  86. Question 86Identify considerations for relational data on Azure

    In a relational table, what does a single row represent?

    1. A relationship between two or more tables
    2. A collection of all records of the same type
    3. The definition of the table's column names and data types
    4. One instance of the entity Correct answer.

    Explanation

    A relational table models an entity type, with columns as attributes and rows as individual instances of that entity. Each row is one complete record described by the column values.

  87. Question 87Identify considerations for relational data on Azure

    Which Azure service provides a fully managed MySQL database in which Microsoft handles patching, backups, and high availability?

    1. Azure Database for MySQL Correct answer.
    2. Azure Synapse Analytics
    3. SQL Server on Azure virtual machines
    4. Azure SQL Database

    Explanation

    Azure Database for MySQL is a PaaS offering that runs the MySQL engine for you and takes care of patching, automated backups, and availability options. Because it is managed, customers do not administer the host operating system.

  88. Question 88Identify considerations for relational data on Azure

    What does auditing provide for an Azure SQL database?

    1. It automatically adds a read-only replica in a second region
    2. It blocks connections from IP addresses that are not in an allow list
    3. It records database events to a log for later review Correct answer.
    4. It encrypts data at rest by using a database-level key

    Explanation

    Auditing captures events such as logins, permission changes, and data access, and writes them to a destination such as a storage account, Log Analytics, or Event Hub. The records support compliance reporting and forensic investigation after an incident.

  89. Question 89Identify considerations for relational data on Azure

    Which benefit does Microsoft Entra authentication provide when connecting to Azure SQL Database?

    1. It automatically encrypts every column that contains personal data
    2. It removes the need for any network firewall rules on the logical server
    3. Centralized identity with multifactor authentication Correct answer.
    4. It gives each database its own dedicated public IP address for connections

    Explanation

    Entra authentication lets users and applications sign in with centrally managed identities instead of SQL logins, and it supports features such as multifactor authentication and conditional access. Access is still controlled by database users and permissions mapped to those identities.

  90. Question 90Identify considerations for relational data on Azure

    What does dynamic data masking do in Azure SQL Database?

    1. It encrypts the table columns so that even administrators see only ciphertext
    2. It hides sensitive values from users who lack unmask permission Correct answer.
    3. It prevents rows from being returned by queries that do not filter on the masked column
    4. It permanently replaces sensitive values in storage with masked characters

    Explanation

    Dynamic data masking rewrites the values returned by queries so that users without the UNMASK permission see a masked form, while privileged users see the real data. The stored values are not changed, so masking is not an encryption or access-control boundary by itself.

  91. Question 91Identify considerations for relational data on Azure

    Azure SQL Database performs automated backups of a database. Which statement about the backup schedule is correct?

    1. Transaction log backups are taken every 5 to 10 minutes Correct answer.
    2. Backups are taken only when an administrator schedules a maintenance window
    3. Differential backups are taken once per month for each database
    4. A full backup is taken every 5 to 10 minutes and retained for 7 days

    Explanation

    Automated backups combine weekly full backups, differential backups at roughly 12- to 24-hour intervals, and transaction log backups every 5 to 10 minutes. Together they let the service restore a database to a point in time within the retention period.

  92. Question 92Identify considerations for relational data on Azure

    Which Azure SQL Database capability keeps copies of automated backups for up to 10 years?

    1. Elastic pools
    2. Point-in-time restore
    3. Long-term retention Correct answer.
    4. Active geo-replication

    Explanation

    Long-term retention lets you store full backups in a separate retention policy with a configurable period of up to 10 years. It supplements the default point-in-time retention window used for routine restores.

  93. Question 93Identify considerations for relational data on Azure

    A team needs to connect to Azure SQL Database from a virtual network over a private IP address with no exposure to the public internet. What should they configure?

    1. A failover group with an automatic failover policy
    2. A private endpoint Correct answer.
    3. A database-scoped credential stored in the master database
    4. A server-level firewall rule for the virtual network's address range

    Explanation

    A private endpoint places a private IP address for the logical server inside a subnet of the virtual network, so traffic never traverses the public internet. Firewall rules, by contrast, still apply to the public endpoint.

  94. Question 94Identify considerations for relational data on Azure

    Which Azure service helps assess and move on-premises databases to Azure with minimal downtime?

    1. Azure SQL Database elastic query
    2. Azure Load Balancer
    3. Azure Synapse Analytics
    4. Azure Database Migration Service Correct answer.

    Explanation

    Azure Database Migration Service orchestrates assessment and migration of database workloads to Azure targets, with options for online migration that keep the source available during the move. Cutover occurs after the target has been synchronized.

  95. Question 95Identify considerations for relational data on Azure

    Which property of a transaction guarantees that either all of its changes are committed or none are?

    1. Isolation
    2. Atomicity Correct answer.
    3. Consistency
    4. Durability

    Explanation

    Atomicity treats the work in a transaction as a single unit, so a failure rolls back every change made within it. The other ACID properties cover data validity, concurrency behavior, and persistence after commit.

  96. Question 96Identify considerations for relational data on Azure

    Which Azure SQL feature restricts the rows a user can read based on their identity at query time?

    1. Transparent data encryption
    2. Always Encrypted with secure enclaves
    3. Row-level security Correct answer.
    4. Dynamic data masking

    Explanation

    Row-level security applies a predicate that filters rows automatically according to the characteristics of the connected user, so the filter cannot be bypassed by changing a WHERE clause. It is implemented with an inline table-valued function bound to a security policy.

  97. Question 97Identify considerations for relational data on Azure

    How does Always Encrypted protect a column in Azure SQL Database?

    1. The client driver encrypts and decrypts, so the engine never sees plaintext Correct answer.
    2. The data is encrypted with a key that database administrators can export from the master database
    3. The column is masked at query time for all users except the database owner
    4. The database engine decrypts the column for every user who has SELECT permission

    Explanation

    Always Encrypted keeps the column encryption keys in the client application, so encryption and decryption happen in the driver. The database engine stores and processes only ciphertext, which means administrators and the service itself cannot read the protected values.

  98. Question 98Identify considerations for relational data on Azure

    What is the default backup storage redundancy for a new Azure SQL Database?

    1. No redundancy, because backups are kept only on local SSD
    2. Locally redundant storage
    3. Geo-redundant storage Correct answer.
    4. Zone-redundant storage

    Explanation

    By default, automated backups are replicated to a paired Azure region so they remain available if the primary region is lost. The storage redundancy for backups can be changed at creation time when a different trade-off between cost and resilience is required.

  99. Question 99Identify considerations for relational data on Azure

    A database must be recovered into a different Azure region after a regional outage by using the geo-replicated backups. Which capability should be used?

    1. Restore of a deleted database
    2. Point-in-time restore to the same logical server
    3. An automatic failover group policy
    4. Geo-restore Correct answer.

    Explanation

    Geo-restore creates a new database from the most recent geo-replicated backup, and it can target a server in a different region than the original. It is the recovery path when the source region is unavailable and no replicated secondary exists.

  100. Question 100Identify considerations for relational data on Azure

    A database is slow because the server runs out of CPU during peak hours. Which action addresses this cause?

    1. Increase the number of vCores Correct answer.
    2. Split the largest table into a separate schema to reduce CPU load
    3. Move the database to a smaller service tier to reduce contention
    4. Add a read-only replica and send all write traffic to it

    Explanation

    When the bottleneck is compute on the server, scaling up by adding vCores gives the workload more processing capacity. Scaling out with readable replicas helps read-heavy workloads but does not add capacity for writes.

  101. Question 101Identify considerations for relational data on Azure

    A table already has a primary key on an ID column. The business requires that no two rows have the same email address. What should you add?

    1. A foreign key on the email column
    2. A default constraint on the email column
    3. A unique constraint on the email column Correct answer.
    4. A check constraint comparing the email to the ID column

    Explanation

    A unique constraint rejects duplicate non-null values in a column while allowing the table to keep a different primary key. It enforces the business rule without changing how rows are identified.

  102. Question 102Identify considerations for relational data on Azure

    Which Azure SQL Database purchasing option offers a discount in exchange for a one-year or three-year commitment?

    1. Reserved capacity Correct answer.
    2. Pay-as-you-go vCore pricing
    3. The DTU purchasing model
    4. The serverless compute tier with auto-pause enabled

    Explanation

    Reserved capacity lets you prepay for compute for a one-year or three-year term in exchange for a lower rate than pay-as-you-go pricing. It is a billing commitment and does not change the technical capabilities of the database.

  103. Question 103Identify considerations for relational data on Azure

    A team deploys Azure Database for PostgreSQL flexible server and needs automatic failover to a standby server in a different availability zone. Which configuration should they enable?

    1. Connection pooling with PgBouncer
    2. A read replica in the same region
    3. A geo-restore of the server to a paired region
    4. Zone-redundant high availability Correct answer.

    Explanation

    Zone-redundant high availability provisions a standby in a different availability zone and fails over to it automatically when the primary becomes unavailable. Read replicas and geo-restore provide different capabilities and do not offer automatic same-region failover.

  104. Question 104Describe considerations for working with non-relational data on Azure

    A company needs to store large volumes of unstructured data such as images, video files, and log archives in Azure. Which Azure Storage service is designed for this workload?

    1. Azure Files
    2. Azure Table Storage
    3. Azure Queue Storage
    4. Azure Blob Storage Correct answer.

    Explanation

    Blob storage is optimized for storing massive amounts of unstructured data such as text and binary content, including images, video, backups, and logs. The other services are aimed at messaging, key-attribute tables, and mounted file shares respectively.

  105. Question 105Describe considerations for working with non-relational data on Azure

    Azure Cosmos DB for NoSQL stores each item in which format?

    1. XML documents
    2. JSON documents Correct answer.
    3. Column-family rows
    4. Relational tables

    Explanation

    The NoSQL API of Azure Cosmos DB stores schema-free items as JSON documents, and queries use a SQL-like syntax over those documents. The other formats belong to relational engines, wide-column stores, or legacy XML-based systems.

  106. Question 106Describe considerations for working with non-relational data on Azure

    In Azure Table Storage, which combination uniquely identifies an entity?

    1. Timestamp and row key
    2. Partition key only
    3. Partition key and row key Correct answer.
    4. Row key only

    Explanation

    An entity is uniquely identified within a table by the pair formed from its partition key and row key. Neither key is unique on its own, and the timestamp is system-managed metadata rather than part of the identity.

  107. Question 107Describe considerations for working with non-relational data on Azure

    Users must mount a shared network drive from Windows and Linux clients by using SMB without running a file server. Which Azure service should be used?

    1. Azure Table Storage
    2. Azure Queue Storage
    3. Azure Files Correct answer.
    4. Azure Blob Storage

    Explanation

    Azure Files provides fully managed file shares that clients mount over SMB (and NFS for Linux workloads). Blob, queue, and table storage are accessed through REST APIs and client libraries rather than as mounted network drives.

  108. Question 108Describe considerations for working with non-relational data on Azure

    Which blob access tier has the lowest storage cost but the longest delay before the data can be read?

    1. Archive Correct answer.
    2. Cool
    3. Cold
    4. Hot

    Explanation

    The archive tier is the cheapest for storing data but requires rehydration, which can take hours, before the blob can be read. Hot, cool, and cold tiers trade higher storage cost for progressively faster access.

  109. Question 109Describe considerations for working with non-relational data on Azure

    Which Azure Cosmos DB API is intended for storing and traversing highly connected graph data?

    1. Table API
    2. NoSQL API
    3. MongoDB API
    4. Gremlin API Correct answer.

    Explanation

    The Gremlin API models data as vertices and edges and supports graph traversal queries. The NoSQL and MongoDB APIs target document data, and the Table API targets key-attribute entities.

  110. Question 110Describe considerations for working with non-relational data on Azure

    Which capability distinguishes Azure Data Lake Storage Gen2 from ordinary Blob Storage?

    1. Blob versioning
    2. Lifecycle management
    3. Hierarchical namespace Correct answer.
    4. Geo-redundant replication

    Explanation

    Data Lake Storage Gen2 is Blob Storage with a hierarchical namespace enabled, which organizes objects into directories and files. Replication, versioning, and lifecycle policies are features of Blob Storage generally and do not distinguish the two.

  111. Question 111Describe considerations for working with non-relational data on Azure

    What is the primary role of Azure Queue Storage in a distributed application?

    1. Hosting static websites
    2. Message-based decoupling Correct answer.
    3. Mounting SMB shares
    4. Storing structured records

    Explanation

    Queue Storage holds messages so that producers and consumers can run independently and asynchronously, which decouples application tiers. It does not store records, serve web content, or provide file shares.

  112. Question 112Describe considerations for working with non-relational data on Azure

    Which Azure Storage feature automatically moves blobs to a cooler tier or deletes them based on their age or last access time?

    1. Lifecycle management Correct answer.
    2. Object replication
    3. Blob versioning
    4. Blob soft delete

    Explanation

    Lifecycle management policies contain rules that transition blobs between tiers or delete them after conditions such as days since modification are met. Soft delete, versioning, and object replication address recovery and copying rather than tier transitions.

  113. Question 113Describe considerations for working with non-relational data on Azure

    Which storage redundancy option keeps data readable in a secondary Azure region while the primary region is unavailable?

    1. Zone-redundant storage (ZRS)
    2. Read-access geo-redundant storage (RA-GRS) Correct answer.
    3. Geo-redundant storage (GRS)
    4. Locally redundant storage (LRS)

    Explanation

    RA-GRS replicates data to a secondary region and exposes a secondary endpoint that remains readable during a primary-region outage. GRS also replicates to a secondary region but does not allow reads from it until a failover occurs.

  114. Question 114Describe considerations for working with non-relational data on Azure

    Which blob type should be used to store a virtual hard disk file that supports random read and write operations?

    1. Block blob
    2. Page blob Correct answer.
    3. Append blob
    4. Blob snapshot

    Explanation

    Page blobs store data in 512-byte pages and are optimized for random read and write access, which is why Azure virtual machine disks are backed by them. Block and append blobs are optimized for streaming and sequential writes, and a snapshot is a point-in-time copy rather than a blob type.

  115. Question 115Describe considerations for working with non-relational data on Azure

    A Cosmos DB container will store millions of orders that are read and written evenly. Which property is the best partition key?

    1. Order status
    2. Customer country
    3. Order month
    4. Order ID Correct answer.

    Explanation

    A good partition key has high cardinality and many distinct values so that requests and storage spread evenly across partitions. Status, month, and country have few distinct values and would concentrate traffic into a small number of large partitions.

  116. Question 116Describe considerations for working with non-relational data on Azure

    Approximately how many request units are consumed by a point read of a 1 KB item in Azure Cosmos DB for NoSQL?

    1. Approximately 10
    2. Approximately 5
    3. Approximately 2
    4. Approximately 1 Correct answer.

    Explanation

    Request unit cost scales with the work performed, and reading a single 1 KB item by its ID and partition key is the cheapest operation at roughly 1 RU. Larger items, queries, and writes consume proportionally more.

  117. Question 117Describe considerations for working with non-relational data on Azure

    Which Azure Cosmos DB capability provides an ordered record of item changes that a downstream service can process incrementally?

    1. Conflict feed
    2. Change feed Correct answer.
    3. Stored procedures
    4. Time to live

    Explanation

    The change feed presents a persistent, ordered log of inserts and updates within a container's partition keys, which downstream consumers read incrementally. It is commonly used to trigger processing, synchronize caches, or feed analytics pipelines.

  118. Question 118Describe considerations for working with non-relational data on Azure

    Which Azure Cosmos DB setting automatically removes items after a specified period?

    1. Change feed
    2. Time to live Correct answer.
    3. Conflict resolution policy
    4. Indexing policy

    Explanation

    Time to live (TTL) deletes items once a configured number of seconds has elapsed, either at the container level or per item. It is useful for session data, event logs, and other data with a natural expiry.

  119. Question 119Describe considerations for working with non-relational data on Azure

    A team needs a low-cost, schema-free key-attribute store for configuration data. The data is accessed only from one region and no latency SLA is required. Which Azure service best fits?

    1. Azure SQL Database
    2. Azure Table Storage Correct answer.
    3. Azure Cosmos DB for NoSQL
    4. Azure Database for PostgreSQL

    Explanation

    Azure Table Storage provides a schemaless key-attribute store at low cost for data that does not need global distribution or single-digit-millisecond guarantees. The relational options impose a fixed schema, and Cosmos DB is designed for global distribution and guaranteed low latency at a higher price.

  120. Question 120Describe considerations for working with non-relational data on Azure

    Which mechanism lets you grant a client temporary, permission-scoped access to a single blob without disclosing the storage account key?

    1. Account key
    2. Shared access signature Correct answer.
    3. Connection string
    4. Managed identity

    Explanation

    A shared access signature is a signed URL that encodes the permitted operations, the resource, and an expiry time, so the caller never needs the account key. Account keys and connection strings grant full control of the account, and managed identity applies to Azure-hosted workloads rather than external clients.

  121. Question 121Describe considerations for working with non-relational data on Azure

    Which Azure Storage feature retains deleted blobs for a configurable period so they can be restored after accidental removal?

    1. Blob versioning
    2. Object replication
    3. Immutability policy
    4. Blob soft delete Correct answer.

    Explanation

    Blob soft delete keeps deleted blobs (and optionally snapshots and versions) recoverable for a retention period you configure. Versioning preserves previous content of overwritten blobs, replication copies data to another account, and immutability prevents modification or deletion entirely.

  122. Question 122Describe considerations for working with non-relational data on Azure

    A worker retrieves a message from Azure Queue Storage but fails before deleting it. What happens when the visibility timeout expires?

    1. It is copied to Blob storage for long-term retention
    2. It becomes visible to consumers again Correct answer.
    3. It is permanently deleted from the queue immediately
    4. It is moved to the poison queue after the first failure

    Explanation

    While a message is being processed it is invisible to other consumers for the duration of the visibility timeout. If the worker does not delete it in time, the message reappears and can be picked up again, which may eventually push it to a poison queue after repeated failures.

  123. Question 123Describe considerations for working with non-relational data on Azure

    Which storage account type provides SSD-backed storage optimized for block blobs and data lake workloads with consistently low latency?

    1. Premium page blobs
    2. Standard general-purpose v2
    3. Premium file shares
    4. Premium block blob storage Correct answer.

    Explanation

    Premium block blob accounts use solid-state drives and are intended for block blobs and Data Lake Storage Gen2 workloads that need low latency and high transaction rates. Premium file shares target SMB file workloads, and premium page blobs back managed disks.

  124. Question 124Describe considerations for working with non-relational data on Azure

    Which Azure Cosmos DB consistency level guarantees that reads lag behind writes by no more than a configured number of versions or a configured time interval?

    1. Eventual
    2. Strong
    3. Bounded staleness Correct answer.
    4. Session

    Explanation

    Bounded staleness lets you define a staleness window expressed as either K versions or T seconds, whichever is reached first, while preserving ordering guarantees. Strong consistency has no lag at all, session scopes consistency to a single client session, and eventual offers no ordering or lag bound.

  125. Question 125Describe considerations for working with non-relational data on Azure

    A Cosmos DB container uses a partition key with only five distinct values, and writes are being throttled because one partition is hot. What is the recommended fix?

    1. Enable multi-region writes so that each region handles part of the load
    2. Append a suffix to each key to add partitions Correct answer.
    3. Create one separate container for each of the five partition key values
    4. Increase the provisioned throughput of the container significantly

    Explanation

    Low-cardinality partition keys concentrate logical partitions onto a small number of physical partitions, which caps throughput per partition. Adding a suffix creates a synthetic key with far more distinct values so the data and traffic spread across many physical partitions.

  126. Question 126Describe considerations for working with non-relational data on Azure

    Which storage redundancy option keeps three copies of data across three availability zones within a single Azure region?

    1. Read-access geo-redundant storage (RA-GRS)
    2. Zone-redundant storage (ZRS) Correct answer.
    3. Locally redundant storage (LRS)
    4. Geo-redundant storage (GRS)

    Explanation

    ZRS replicates data synchronously across three availability zones in one region, protecting against datacenter-level failure while keeping reads local. LRS keeps copies within one datacenter, and GRS options replicate to a separate secondary region.

  127. Question 127Describe considerations for working with non-relational data on Azure

    Why does reading a Cosmos DB item by its ID and partition key cost far fewer request units than querying for the same item?

    1. It is served entirely from the client cache
    2. It skips the partition key lookup step
    3. It reads a single item directly by id and key Correct answer.
    4. It uses a weaker consistency level by default

    Explanation

    A point read resolves one item using the ID and partition key, so no query engine work, index scan, or result materialization is required. Queries consume additional request units for parsing, filtering, and returning a result set even when only one item matches.

  128. Question 128Describe considerations for working with non-relational data on Azure

    What efficiency does a hierarchical namespace give Data Lake Storage Gen2 compared with a flat blob namespace?

    1. Data is compressed automatically to reduce storage costs
    2. Blobs are cached in the compute node's local memory
    3. Renames become single atomic metadata operations Correct answer.
    4. Reads are served from a read-only secondary region

    Explanation

    With a hierarchical namespace, directories are real objects, so renaming or moving a directory is one atomic metadata operation rather than a copy-and-delete of every blob whose name begins with a prefix. This greatly improves the performance of analytics jobs that reorganize large datasets.

  129. Question 129Describe considerations for working with non-relational data on Azure

    A company archives scanned invoices to Azure Blob Storage. Each invoice is accessed roughly once a year, and when requested it must be retrievable within a few hours. Which access tier provides the lowest storage cost for this data?

    1. Cool
    2. Archive Correct answer.
    3. Cold
    4. Hot

    Explanation

    The archive tier has the lowest storage price and is intended for data that is rarely accessed and can tolerate hours of rehydration latency before it becomes readable. Hot, cool, and cold tiers all cost more to store because they are designed for faster access.

  130. Question 130Describe considerations for working with non-relational data on Azure

    Which Azure Storage service exposes data as a fully managed share that multiple virtual machines can mount at the same time over the SMB protocol?

    1. Azure Table Storage
    2. Azure Blob Storage
    3. Azure Queue Storage
    4. Azure Files Correct answer.

    Explanation

    Azure Files provides managed file shares that are accessible over SMB (and NFS), so several clients can mount the same share concurrently. Blob, queue, and table storage are reached through REST APIs and client libraries rather than a mounted file share.

  131. Question 131Describe considerations for working with non-relational data on Azure

    A service continuously writes log records to a blob and never edits or deletes records that were already written. Which blob type is designed for this pattern?

    1. Append blob Correct answer.
    2. Page blob
    3. Blob snapshot
    4. Block blob

    Explanation

    An append blob is optimized for append-only workloads such as logging, because new data is added to the end of the blob without rewriting existing content. Block blobs are built for large uploads of discrete blocks, and page blobs are built for random read and write operations.

  132. Question 132Describe considerations for working with non-relational data on Azure

    An application must insert 90 entities into a single Azure Table Storage table atomically. What condition must the batch satisfy?

    1. All entities share one PartitionKey and total 100 or fewer. Correct answer.
    2. Every entity must use the same RowKey value.
    3. The storage account must be configured with read-access geo-redundant replication.
    4. Each entity must be written to a separate table.

    Explanation

    Table Storage entity group transactions are atomic only within a single partition, and a batch may contain at most 100 operations. Entities in the batch therefore must share the same PartitionKey and target the same table, while RowKey values must be unique.

  133. Question 133Describe considerations for working with non-relational data on Azure

    Writes to an Azure Cosmos DB for NoSQL container consume a high number of request units because every property is indexed, yet queries filter on only two properties. What should you configure to reduce the write cost?

    1. Enable the analytical store on the container.
    2. An indexing policy that excludes the unused paths. Correct answer.
    3. Raise the default consistency level to strong.
    4. Create a separate container for each query type.

    Explanation

    By default every property is indexed, so each write pays for index maintenance across all paths. Excluding paths that are never filtered or sorted on reduces the request unit charge for writes as well as the index storage footprint.

  134. Question 134Describe considerations for working with non-relational data on Azure

    Which statement describes a requirement of a transactional batch in Azure Cosmos DB for NoSQL?

    1. The batch may contain read operations only.
    2. Each operation in the batch must use a different partition key value.
    3. The batch may contain no more than ten operations.
    4. All operations must target the same logical partition key. Correct answer.

    Explanation

    A transactional batch executes atomically only when every operation addresses the same logical partition key, because the transaction is scoped to a single partition. It can mix creates, updates, deletes, and reads and supports up to 100 operations.

  135. Question 135Describe considerations for working with non-relational data on Azure

    An Azure Cosmos DB account is configured for writes in multiple regions. The default conflict resolution policy is in use. Which value determines the winning item when two regions write the same item concurrently?

    1. The request unit charge recorded for the last write
    2. The item identifier sorted in descending order
    3. The region that accepted the write first
    4. The item's _ts timestamp value Correct answer.

    Explanation

    The default conflict resolution policy is last-writer-wins, which compares the _ts system timestamp property and keeps the item with the highest value. A custom policy can instead point to a user-defined property for the comparison.

  136. Question 136Describe considerations for working with non-relational data on Azure

    A Cosmos DB container uses autoscale throughput with a maximum of 4,000 RU/s. Between which values does the provisioned throughput move as load changes?

    1. 40 RU/s and 4,000 RU/s
    2. 400 RU/s and 4,000 RU/s Correct answer.
    3. 4,000 RU/s and 40,000 RU/s
    4. 1,000 RU/s and 4,000 RU/s

    Explanation

    Autoscale scales between 10 percent of the configured maximum and that maximum, so a 4,000 RU/s maximum yields a range of 400 to 4,000 RU/s. Billing is based on the highest throughput reached during each hour.

  137. Question 137Describe considerations for working with non-relational data on Azure

    Two processes occasionally overwrite the same block blob at the same time. Which mechanism lets one process hold an exclusive, time-limited lock on the blob so competing writers must wait or fail?

    1. A shared access signature
    2. Blob soft delete
    3. A blob lease Correct answer.
    4. A blob snapshot

    Explanation

    A lease places an exclusive, renewable lock on a blob for a defined duration, and other clients are blocked from writing until the lease is released or expires. This makes it the standard way to implement concurrency control over a single blob.

  138. Question 138Describe considerations for working with non-relational data on Azure

    An Azure Cosmos DB for NoSQL container is partitioned on /tenantId, and one very large tenant generates most of the traffic, causing throttling on that logical partition. Which option addresses the hot partition directly?

    1. Hierarchical partition keys. Correct answer.
    2. Autoscale with a higher maximum RU/s.
    3. A stronger default consistency level.
    4. Time to live enabled on the container.

    Explanation

    Hierarchical partition keys let a container partition on multiple levels, for example tenantId followed by a second attribute, so a large tenant's data can be spread across many physical partitions. Raising throughput or changing consistency does not change how data maps to a single logical partition.

  139. Question 139Describe considerations for working with non-relational data on Azure

    A team needs to run large aggregation queries over Cosmos DB operational data without consuming the request units that serve the transactional workload and without building an ETL pipeline. Which capability should they enable?

    1. A stored procedure per query
    2. The change feed with a downstream function
    3. The integrated cache on the account
    4. The analytical store Correct answer.

    Explanation

    The analytical store keeps a separate column-oriented copy of the data that analytical engines such as Azure Synapse can query, so aggregation queries do not consume transactional request units. It is populated automatically, removing the need for a separate ETL process.

  140. Question 140Describe an analytics workload on Azure

    Which Azure service provides a provisioned, massively parallel processing (MPP) relational data warehouse?

    1. Synapse dedicated SQL pool Correct answer.
    2. Azure Cosmos DB for NoSQL
    3. Azure Table Storage
    4. Azure SQL Database single database

    Explanation

    A dedicated SQL pool in Azure Synapse Analytics stores relational data in a warehouse and distributes it across compute nodes so queries run in parallel. It is designed for large-scale analytical (OLAP) workloads.

  141. Question 141Describe an analytics workload on Azure

    Which Azure service is used to orchestrate and schedule data movement and transformation pipelines?

    1. Azure Data Factory Correct answer.
    2. Azure Stream Analytics
    3. Azure Data Explorer
    4. Azure Event Hubs

    Explanation

    Azure Data Factory defines pipelines of activities that copy and transform data across many sources and sinks, and triggers let those pipelines run on a schedule or in response to events.

  142. Question 142Describe an analytics workload on Azure

    Which Azure service processes streaming data by using a SQL-like query language over time windows?

    1. Azure Data Factory pipelines
    2. Azure Files storage share
    3. Azure Databricks
    4. Azure Stream Analytics Correct answer.

    Explanation

    Azure Stream Analytics runs continuous queries written in a SQL-like language and applies windowing functions such as tumbling, hopping, and sliding windows to aggregate streaming data.

  143. Question 143Describe an analytics workload on Azure

    What is the main purpose of Power BI?

    1. Storing relational table data in the cloud
    2. Building reports and dashboards Correct answer.
    3. Orchestrating data pipelines on a schedule
    4. Running Apache Spark jobs at scale

    Explanation

    Power BI is the Microsoft business intelligence layer: it connects to data sources, creates semantic models, and presents interactive reports and dashboards.

  144. Question 144Describe an analytics workload on Azure

    Which feature of Azure Data Lake Storage Gen2 enables efficient directory-level operations for analytics?

    1. Blob soft delete
    2. Hierarchical namespace Correct answer.
    3. Immutable blob policies
    4. Blob snapshots

    Explanation

    The hierarchical namespace organizes objects into a true directory tree, which lets analytics engines such as Spark and Synapse perform efficient folder renames and partition pruning.

  145. Question 145Describe an analytics workload on Azure

    Which statement describes Azure Databricks?

    1. A data governance catalog service
    2. A serverless query engine for logs
    3. A relational data warehouse using MPP
    4. An Apache Spark-based analytics platform Correct answer.

    Explanation

    Azure Databricks is a first-party Azure service built on Apache Spark, offering managed clusters and collaborative notebooks along with Delta Lake for data engineering and machine learning.

  146. Question 146Describe an analytics workload on Azure

    Which characteristic is typical of an analytics (OLAP) workload?

    1. Highly normalized tables optimized for writes
    2. Row-level locking for write concurrency
    3. Large scans that aggregate many rows Correct answer.
    4. Many small single-row transactions

    Explanation

    Analytics workloads read large volumes of data and aggregate it to answer broad business questions, so they favor columnar storage and scan-and-aggregate queries rather than high-volume single-row writes.

  147. Question 147Describe an analytics workload on Azure

    What is the primary purpose of Microsoft Purview?

    1. Hosting relational databases
    2. Data governance and cataloging Correct answer.
    3. Processing real-time event streams
    4. Building paginated reports

    Explanation

    Microsoft Purview scans data sources to build a searchable catalog, tracks lineage across pipelines, and supports classification and policy management for governed data estates.

  148. Question 148Describe an analytics workload on Azure

    In Azure Synapse Analytics, which pool type lets you query files in a data lake with a pay-per-query model and no provisioned resources?

    1. Data Explorer pool
    2. Apache Spark pool
    3. Provisioned dedicated SQL pool
    4. Serverless SQL pool Correct answer.

    Explanation

    The serverless SQL pool is billed per terabyte of data processed and queries files such as Parquet and CSV directly in the lake through OPENROWSET, so no capacity must be provisioned.

  149. Question 149Describe an analytics workload on Azure

    Which statement describes the extract, load, and transform (ELT) pattern used in cloud analytics?

    1. Transform data on a staging server before loading it
    2. Transform data only after it is visualized in reports
    3. Load raw data first and transform it in the target Correct answer.
    4. Extract data and load it without any transformation

    Explanation

    ELT loads raw data into a scalable target such as a warehouse or data lake and then transforms it there using the target's compute, which suits cloud platforms with elastic storage and processing.

  150. Question 150Describe an analytics workload on Azure

    Which Synapse workspace component runs Apache Spark notebooks for big data processing?

    1. Pipelines
    2. Link connections
    3. Spark pools Correct answer.
    4. SQL pools

    Explanation

    Spark pools in Azure Synapse provide managed Apache Spark clusters that execute notebooks and Spark jobs, separate from the relational SQL engines in the same workspace.

  151. Question 151Describe an analytics workload on Azure

    You need Azure Data Factory to connect to an on-premises SQL Server. Which component must you deploy?

    1. A mapping data flow with a staging linked service
    2. Azure integration runtime with a public endpoint
    3. Self-hosted integration runtime Correct answer.
    4. Managed virtual network integration runtime

    Explanation

    A self-hosted integration runtime is installed on a machine inside the on-premises network and provides the outbound connection that Azure Data Factory uses to reach private data sources.

  152. Question 152Describe an analytics workload on Azure

    In Power BI, how does a dashboard differ from a report?

    1. It is a paginated document designed for printing at scale
    2. It is a scheduled refresh that runs in a workspace
    3. It is one canvas of tiles pinned from reports Correct answer.
    4. It is a semantic model that stores import-mode tables

    Explanation

    A dashboard is a single canvas of visual tiles that are pinned from one or more reports, giving a monitoring view, whereas a report itself can contain many pages built on a single semantic model.

  153. Question 153Describe an analytics workload on Azure

    Which offering lets you embed Power BI reports inside a custom application?

    1. Power BI Embedded Correct answer.
    2. Power BI Desktop
    3. Power BI Report Server
    4. Power BI Premium per user

    Explanation

    Power BI Embedded provides REST APIs and a JavaScript client so developers can surface reports and dashboards inside their own applications under an app-owns-data or user-owns-data model.

  154. Question 154Describe an analytics workload on Azure

    In an IoT solution, which pairing correctly separates streaming ingestion from stream processing?

    1. Data Factory ingests and a Synapse SQL pool processes
    2. Blob Storage ingests and Power BI processes
    3. Data Explorer ingests and Purview processes
    4. Event Hubs ingests and Stream Analytics processes Correct answer.

    Explanation

    Event Hubs is a durable, high-throughput event ingestion service, and Azure Stream Analytics consumes those events and runs continuous queries over them, so ingestion and processing stay as distinct services.

  155. Question 155Describe an analytics workload on Azure

    Which schema design is typical of a relational data warehouse?

    1. A star schema with fact and dimension tables Correct answer.
    2. A document schema with nested arrays
    3. A graph schema with nodes and edges
    4. A fully normalized schema eliminating all redundancy

    Explanation

    A star schema places measurable events in a central fact table surrounded by descriptive dimension tables, which simplifies analytical queries and reduces the number of joins required.

  156. Question 156Describe an analytics workload on Azure

    Which Synapse capability provides near real-time analytical queries over operational data in Azure Cosmos DB without ETL?

    1. Azure Synapse Spark pools
    2. Azure Synapse Pipelines
    3. Azure Synapse Link Correct answer.
    4. Azure Synapse dedicated SQL pool

    Explanation

    Azure Synapse Link creates an analytical store over operational data and exposes it to Synapse, so analysts can query it without copying the data through a pipeline.

  157. Question 157Describe an analytics workload on Azure

    Which T-SQL function reads file content from a data lake path inside a serverless SQL pool query?

    1. OPENQUERY
    2. OPENDATASOURCE
    3. OPENJSON
    4. OPENROWSET Correct answer.

    Explanation

    OPENROWSET with a BULK data source reads files such as Parquet or CSV directly from storage and exposes the columns so they can be used like a table in the query.

  158. Question 158Describe an analytics workload on Azure

    What is the purpose of Azure Analysis Services?

    1. To host enterprise semantic models for BI clients Correct answer.
    2. To move data between on-premises data sources
    3. To ingest streaming events from devices
    4. To store raw files in a data lake

    Explanation

    Azure Analysis Services hosts tabular semantic models in the cloud so BI clients such as Power BI and Excel can query them using fast, pre-modeled aggregations and relationships.

  159. Question 159Describe an analytics workload on Azure

    Which workload is best suited to Azure Stream Analytics rather than a batch pipeline?

    1. Alerting when sensor values exceed a threshold in seconds Correct answer.
    2. Monthly aggregation of archived sales history over the year
    3. Nightly loading of large CSV files into a warehouse
    4. Weekly refresh of a reporting semantic model

    Explanation

    Stream Analytics is built for low-latency continuous processing, so it fits alerting and monitoring scenarios where results must be produced within seconds of the event arriving.

  160. Question 160Describe an analytics workload on Azure

    In a dedicated SQL pool, which table distribution should you use for a large fact table to minimize data movement during joins on a common key?

    1. Replicated distribution on every compute node
    2. Hash distribution on the join column Correct answer.
    3. Partitioned distribution by date key
    4. Round-robin distribution across all nodes

    Explanation

    Hash distribution places rows with the same key value on the same distribution, so large fact tables hashed on their join columns can be joined without shuffling rows between nodes.

  161. Question 161Describe an analytics workload on Azure

    A team has existing Apache Spark notebooks that use Delta Lake and Auto Loader and must run them on Azure with minimal changes. Which service should they use?

    1. Azure Synapse serverless SQL pool
    2. Azure Data Factory mapping data flows
    3. Azure Databricks Correct answer.
    4. Azure Stream Analytics

    Explanation

    Azure Databricks runs the same Apache Spark runtime and supports Delta Lake and Auto Loader natively, so existing notebooks move over with little or no rework.

  162. Question 162Describe an analytics workload on Azure

    Which Synapse pool is designed for near real-time analysis of high-volume log and time-series data using KQL?

    1. Provisioned dedicated SQL pool
    2. Apache Spark pool
    3. Data Explorer pool Correct answer.
    4. Serverless SQL pool

    Explanation

    The Data Explorer pool in Azure Synapse uses the Kusto query language and is optimized for append-only log and telemetry data with fast time-based filtering and aggregation.

  163. Question 163Describe an analytics workload on Azure

    A company must ingest on-premises data, store it in a data lake, transform it with Spark, and visualize the results. Which sequence of services maps to these four stages?

    1. Stream Analytics, Cosmos DB, Data Explorer, and Power BI
    2. Data Factory, ADLS Gen2, Databricks, and Power BI Correct answer.
    3. Event Hubs, Blob Storage, Power BI, and Analysis Services
    4. Data Factory, Azure SQL Database, Databricks, and Purview

    Explanation

    Data Factory handles ingestion, ADLS Gen2 stores the raw and curated files, Databricks performs the Spark transformation, and Power BI visualizes the modeled results.

  164. Question 164Describe an analytics workload on Azure

    A Power BI report must show near real-time data from a large Azure SQL database without importing a full copy. Which storage mode should you use?

    1. DirectQuery Correct answer.
    2. Composite with aggregation
    3. Import
    4. Dual

    Explanation

    DirectQuery sends queries to the source when the report renders, so visuals reflect current data without loading a full copy into the model, at the cost of depending on source performance.

  165. Question 165Describe an analytics workload on Azure

    Which capability is provided by Azure Data Lake Storage Gen2 but not by a standard blob storage account?

    1. A hierarchical namespace that supports directories Correct answer.
    2. Blob-level tiering to the archive tier
    3. Shared access signatures for delegated access
    4. Geo-redundant replication across regions

    Explanation

    Enabling the hierarchical namespace on a storage account turns it into Data Lake Storage Gen2, giving directory-level operations and POSIX-style access control that flat blob namespaces do not offer. Tiering, replication, and SAS tokens are blob features available in both cases.

  166. Question 166Describe an analytics workload on Azure

    Which kind of data does a fact table primarily contain in a dimensional model?

    1. Descriptive attributes used for filtering
    2. Business keys for each entity
    3. Quantitative measures linked to dimension keys Correct answer.
    4. Parent-child hierarchies

    Explanation

    A fact table records measurable events such as sales amounts or quantities together with foreign keys that reference dimension tables. Descriptive attributes and hierarchies belong in the dimensions.

  167. Question 167Describe an analytics workload on Azure

    What is the primary role of Azure Data Factory?

    1. Orchestrating data movement and transformation Correct answer.
    2. Brokering messages between applications
    3. Serving interactive reports to users
    4. Storing curated relational tables

    Explanation

    Azure Data Factory is a cloud data integration service whose pipelines and activities copy data between stores and invoke transformation services on a schedule or event. Storage, reporting, and messaging are handled by other Azure services.

  168. Question 168Describe an analytics workload on Azure

    What is the main purpose of a workspace in the Power BI service?

    1. Sharing reports, datasets, and dashboards with a team Correct answer.
    2. Scheduling refreshes from on-premises sources
    3. Storing raw files for a data lake
    4. Defining the visual layout of one report page

    Explanation

    A workspace is a collaborative container in the Power BI service that groups related reports, datasets, dashboards, and dataflows and controls who can access them. Layout is defined inside a report, not by the workspace.

  169. Question 169Describe an analytics workload on Azure

    Why is the Parquet format commonly used for analytical data in a data lake?

    1. It enforces referential integrity between files
    2. It builds a B-tree index on every column
    3. It stores rows as plain text for universal readability
    4. It stores data by column with compression Correct answer.

    Explanation

    Parquet is a columnar format, so a query that reads a few columns touches only those columns and benefits from compression, reducing I/O and cost. Text formats such as CSV require reading entire rows.

  170. Question 170Describe an analytics workload on Azure

    What does Microsoft OneLake provide in Microsoft Fabric?

    1. A relational engine that replaces Azure SQL Database
    2. A dashboard service for executive reporting
    3. A broker for real-time event streams
    4. One logical data lake shared across Fabric workloads Correct answer.

    Explanation

    OneLake is a single tenant-wide logical data lake built on Azure Data Lake Storage, so lakehouses, warehouses, and other Fabric items can share the same underlying storage without copying data.

  171. Question 171Describe an analytics workload on Azure

    Which Azure Synapse component is a provisioned relational data warehouse that uses a massively parallel processing architecture?

    1. Dedicated SQL pool Correct answer.
    2. Serverless SQL pool
    3. Apache Spark pool
    4. Data Explorer pool

    Explanation

    A dedicated SQL pool provisions fixed compute nodes and distributes tables across them, which is the classic MPP relational warehouse model. Serverless SQL pools are pay-per-query and do not provision capacity.

  172. Question 172Describe an analytics workload on Azure

    A pipeline must start as soon as a new file is written to a storage account. Which trigger type should you use?

    1. Schedule trigger
    2. Manual trigger
    3. Event trigger Correct answer.
    4. Tumbling window trigger

    Explanation

    An event trigger subscribes to storage events such as blob creation and starts the pipeline in response. Schedule and tumbling window triggers run on time-based recurrence instead of reacting to a file.

  173. Question 173Describe an analytics workload on Azure

    What does a mapping data flow in Azure Data Factory provide?

    1. A gateway that connects to on-premises sources
    2. A stored procedure that runs inside a dedicated SQL pool
    3. Visual transformation logic that runs on Spark Correct answer.
    4. A JSON file that describes the pipeline schedule

    Explanation

    Mapping data flows let you design transformations graphically, and Data Factory executes them on managed Spark clusters. They are not stored procedures, configuration files, or connectivity components.

  174. Question 174Describe an analytics workload on Azure

    For which scenario is round-robin distribution best suited in a dedicated SQL pool?

    1. A large fact table joined frequently on a date key
    2. A small dimension table used in every query
    3. A table updated with frequent single-row inserts
    4. A staging table with no common join column Correct answer.

    Explanation

    Round-robin spreads rows evenly across distributions without needing a join key, which suits staging or loading tables that have no predictable join pattern. Tables joined often should use hash distribution, and small dimensions suit replication.

  175. Question 175Describe an analytics workload on Azure

    Why is a clustered columnstore index recommended for large fact tables in a dedicated SQL pool?

    1. It compresses by column and speeds up large scans Correct answer.
    2. It enforces uniqueness on the distribution key
    3. It replicates the table to every compute node
    4. It automatically partitions data by date

    Explanation

    Columnstore storage compresses each column separately and reads only the columns a query needs, which greatly improves analytical scans and aggregations over large tables. Uniqueness, replication, and partitioning are separate design decisions.

  176. Question 176Describe an analytics workload on Azure

    In Azure Stream Analytics, which window produces fixed-size, contiguous, non-overlapping time segments?

    1. Hopping window
    2. Sliding window
    3. Session window
    4. Tumbling window Correct answer.

    Explanation

    A tumbling window covers a repeating period with no gaps and no overlap, producing one result per period. Hopping windows can overlap, and session windows group events separated by gaps of inactivity.

  177. Question 177Describe an analytics workload on Azure

    A Power BI dataset must refresh from an on-premises SQL Server. Which component is required?

    1. A virtual network gateway
    2. A self-hosted integration runtime
    3. An on-premises data gateway Correct answer.
    4. An ExpressRoute circuit

    Explanation

    The on-premises data gateway brokers secure connections between the Power BI service and on-premises data sources so scheduled refreshes can run. A self-hosted integration runtime serves Azure Data Factory and Synapse pipelines instead.

  178. Question 178Describe an analytics workload on Azure

    What benefit does a Power BI dataflow provide?

    1. It removes the need for a Power BI license
    2. It converts reports into paginated documents
    3. It makes prepared transformations reusable across datasets Correct answer.
    4. It stores report pages for offline viewing

    Explanation

    A dataflow performs reusable data preparation in the Power BI service, so multiple datasets can consume the same cleansed entities instead of repeating transformation logic. Licensing and report delivery are unchanged.

  179. Question 179Describe an analytics workload on Azure

    What does Azure Synapse Link for Azure SQL Database enable?

    1. It migrates a SQL database to a dedicated SQL pool
    2. It exposes SQL data as a REST endpoint for apps
    3. It copies data nightly by using a scheduled pipeline
    4. It replicates operational data to Synapse with low latency Correct answer.

    Explanation

    Synapse Link continuously replicates changes from operational stores into Synapse analytics with low latency, avoiding hand-built ETL. It is not a migration tool and does not expose data through REST endpoints.

  180. Question 180Describe an analytics workload on Azure

    In a serverless SQL pool, which object lets you query data lake files as a table without copying the data?

    1. A materialized view
    2. A user-defined function
    3. An external table Correct answer.
    4. A stored procedure

    Explanation

    An external table stores metadata that points at files in the lake, so queries read the files in place using a familiar table-like syntax. Materialized views and stored procedures in a serverless pool do not provide this file-based abstraction.

  181. Question 181Describe an analytics workload on Azure

    A solution must ingest millions of log events per second from many producers, retain them for several days, and let multiple consumers read independent partitions. Which service should you use?

    1. Azure Queue Storage
    2. Azure Event Hubs Correct answer.
    3. Azure Service Bus queue
    4. Azure Blob Storage

    Explanation

    Event Hubs is a partitioned, high-throughput event ingestion service that retains events for a configurable period so multiple consumer groups can read independently. Queue services deliver messages to a single consumer rather than supporting replay across consumer groups.

  182. Question 182Describe an analytics workload on Azure

    In Microsoft Fabric, how does a lakehouse differ from a warehouse?

    1. A warehouse stores data only as Parquet files
    2. A lakehouse stores files; a warehouse uses a relational engine Correct answer.
    3. A lakehouse can be queried only with KQL
    4. A warehouse cannot use T-SQL

    Explanation

    A Fabric lakehouse keeps Delta-formatted files in OneLake and exposes them through a SQL analytics endpoint, while a warehouse stores data in a relational engine with full T-SQL and multi-table transaction support.

  183. Question 183Describe an analytics workload on Azure

    A dimension must preserve the full history of attribute changes, with each version valid for a specific date range. Which design should you use?

    1. Overwrite the dimension row with the new values
    2. Store changes in a separate fact table
    3. Add a new column for each changed attribute
    4. Add a new row with effective and expiry dates Correct answer.

    Explanation

    A type 2 slowly changing dimension inserts a new row for each change and records validity dates plus a current-row flag, so historical facts still join to the version that was active at the time. Overwriting rows loses history.

  184. Question 184Describe an analytics workload on Azure

    In a dedicated SQL pool, a small dimension table of about 1 GB is joined repeatedly to a very large fact table. Which distribution minimizes data movement?

    1. Hash distribution on the date column
    2. Replicated distribution Correct answer.
    3. Hash distribution on a surrogate key
    4. Round-robin distribution

    Explanation

    Replicating a small table places a full copy on every compute node, so joins to the distributed fact table need no shuffle of the dimension. Hash and round-robin distributions would require moving rows between nodes during the join.

  185. Question 185Describe an analytics workload on Azure

    A Stream Analytics query must report totals over the last 10 minutes and recalculate the result every 2 minutes. Which windowing approach fits?

    1. Session window with a 10-minute timeout and no overlap
    2. Hopping window with a 10-minute size and 2-minute hop Correct answer.
    3. Snapshot window of 2 minutes
    4. Tumbling window of 10 minutes

    Explanation

    A hopping window with a 10-minute size and a 2-minute hop produces overlapping 10-minute totals every 2 minutes. A tumbling window would only emit every 10 minutes with no overlap.

  186. Question 186Describe an analytics workload on Azure

    You must reprocess a long series of historical time slices in order and rerun only the slices that failed while tracking state per slice. Which trigger type should you use?

    1. Tumbling window trigger Correct answer.
    2. Manual trigger
    3. Event trigger
    4. Schedule trigger

    Explanation

    A tumbling window trigger runs for fixed, non-overlapping historical periods, tracks state per window, and supports retries and backfill of failed windows only. Event and schedule triggers do not maintain per-window state or history.

  187. Question 187Describe an analytics workload on Azure

    A Delta Lake table on Azure Databricks must be queried as it existed before an incorrect write. Which Delta Lake feature should you use?

    1. Time travel Correct answer.
    2. Z-ordering
    3. Auto compaction
    4. Schema evolution

    Explanation

    Delta Lake keeps a transaction log of table versions, and time travel lets you query an earlier version or timestamp to read pre-change data. Z-ordering, compaction, and schema evolution affect layout and structure, not historical reads.

  188. Question 188Describe an analytics workload on Azure

    What is a primary benefit of enabling a hierarchical namespace on an Azure Data Lake Storage account?

    1. Data is replicated to a secondary region synchronously at no extra cost.
    2. Containers become directly accessible through an SMB file share.
    3. Files are automatically converted to Parquet format when they are written.
    4. Rename and delete operations on a directory complete in near-constant time. Correct answer.

    Explanation

    A hierarchical namespace turns the flat object store into a true directory tree, so an operation on a folder is a single atomic metadata operation rather than work proportional to the number of objects it contains. This makes analytics workloads that reorganize or prune large directory trees far more efficient.

  189. Question 189Describe an analytics workload on Azure

    Which Azure service runs continuous SQL-like queries over streaming data arriving from services such as Event Hubs and IoT Hub?

    1. Azure Synapse dedicated SQL pool
    2. Azure Stream Analytics Correct answer.
    3. Azure Blob Storage
    4. Azure Data Factory

    Explanation

    Azure Stream Analytics is a managed, real-time stream processing engine that evaluates a SQL-based query continuously against unbounded event streams and writes results to outputs such as Power BI, storage, or a database. Data Factory is for batch orchestration, a dedicated SQL pool stores relational data at rest, and Blob Storage only persists data.

  190. Question 190Describe an analytics workload on Azure

    In Power BI, which item combines visuals pinned from one or more reports onto a single canvas?

    1. A dashboard Correct answer.
    2. A report
    3. A workspace
    4. A dataflow

    Explanation

    A dashboard is a single-page canvas to which tiles pinned from reports are added, and it can mix tiles from several reports and datasets. Reports themselves are multi-page views built on a single dataset, whereas a dataflow performs data preparation and a workspace is a container for items.

  191. Question 191Describe an analytics workload on Azure

    A very large fact table in a dedicated SQL pool is frequently joined to dimension tables on a high-cardinality CustomerKey column. Which distribution should the fact table use?

    1. Hash distribution on the date column
    2. Replicated distribution
    3. Hash distribution on CustomerKey Correct answer.
    4. Round-robin distribution

    Explanation

    Hash distribution places rows with the same key value on the same distribution, so joining on that column keeps matching rows co-located and avoids shuffling data across nodes at query time. High cardinality on the joining column also helps keep the distributions evenly sized.

  192. Question 192Describe an analytics workload on Azure

    In Azure Event Hubs, what does the partition count of an event hub primarily determine?

    1. The maximum degree of parallel consumption and the ingress throughput scale. Correct answer.
    2. The encryption level applied to events while they are stored.
    3. The maximum number of consumer groups that can be created.
    4. The length of time that events remain available for replay.

    Explanation

    Partitions are the unit of parallelism and throughput in Event Hubs: each partition is an independent ordered sequence, and consumers can read partitions in parallel. More partitions therefore raise the number of concurrent readers and the throughput ceiling, while ordering is guaranteed only within a single partition.

  193. Question 193Describe an analytics workload on Azure

    You must copy data from an on-premises SQL Server to Azure Data Lake Storage without opening inbound firewall ports. Which Azure Data Factory component should you deploy?

    1. A self-hosted integration runtime Correct answer.
    2. An Azure-SSIS integration runtime
    3. A managed private endpoint in a Synapse workspace
    4. An Azure integration runtime

    Explanation

    A self-hosted integration runtime runs on a machine inside the on-premises network and initiates outbound connections to Azure Data Factory, so no inbound ports must be opened. It is the standard way to reach data sources that are not publicly reachable.

  194. Question 194Describe an analytics workload on Azure

    A Power BI report must always reflect the current rows of a large Azure SQL database, and the data must not be copied into the semantic model. Which storage mode should you use?

    1. Dual mode
    2. Scheduled refresh of an imported model
    3. DirectQuery mode Correct answer.
    4. Import mode

    Explanation

    DirectQuery sends queries to the source each time a visual is rendered, so the report shows current data and nothing is copied into the model. Import and scheduled refresh copy data into the model, and Dual mode behaves as Import unless it is used in a relationship that requires DirectQuery.

  195. Question 195Describe an analytics workload on Azure

    A Delta Lake table is often filtered on two columns, and queries are slow because many small files must be scanned. Which operation improves data skipping by co-locating related rows in the same files?

    1. OPTIMIZE with Z-ORDER on the two columns Correct answer.
    2. Time travel to an earlier version of the table
    3. VACUUM with a retention period of zero hours
    4. Converting the table to an external Parquet table

    Explanation

    OPTIMIZE compacts small files into larger ones, and Z-ORDER additionally sorts data within those files so that rows sharing similar values on the specified columns land together. Statistics recorded per file then let queries skip files that cannot contain matching rows.

  196. Question 196Describe an analytics workload on Azure

    Which approach is the recommended high-throughput way to load large Parquet files from Data Lake Storage Gen2 into a dedicated SQL pool?

    1. The COPY statement Correct answer.
    2. The CREATE EXTERNAL TABLE statement used on its own
    3. An SSIS package executed inside the SQL pool nodes
    4. Repeated INSERT ... VALUES statements

    Explanation

    The COPY statement is the recommended load mechanism for a dedicated SQL pool because it reads files directly from storage with high throughput and requires only the storage location and credentials. It avoids the extra external-table and credential objects that PolyBase-style loading needs.

  197. Question 197Describe an analytics workload on Azure

    A solution must store months of high-volume telemetry and support fast interactive queries written in KQL for near-real-time analysis. Which Azure service should you use?

    1. Azure Synapse dedicated SQL pool
    2. Azure Analysis Services
    3. Azure Table Storage
    4. Azure Data Explorer Correct answer.

    Explanation

    Azure Data Explorer is a fast, fully managed analytics service optimized for large volumes of time-series and log data, and it uses Kusto Query Language. Its indexing and columnar storage deliver low-latency interactive queries over telemetry.

Azure DP-900 flashcards

32 cards. Read the term and the definition here. The deck at the top of this page is optional.

Structured data
Data organized into a predefined schema of tables, rows, and columns, such as records in a relational database. It is straightforward to query with SQL and enforce with constraints.
Semi-structured data
Data that uses tags or keys to organize itself without a rigid table schema, such as JSON or XML documents. It is more flexible than structured data yet still self-describing.
Unstructured data
Data with no predefined model or schema, such as images, audio, video, and free text. Specialized tools or AI services are usually needed to extract meaning from it.
OLTP workload
A transactional workload characterized by many small, frequent read and write operations, where fast response time and data integrity matter more than large-scale aggregation.
OLAP workload
An analytical workload that reads large volumes of historical data and aggregates it for reporting and decision-making, typically served from a data warehouse.
ACID properties
Atomicity, Consistency, Isolation, and Durability: guarantees that a transaction completes fully or not at all and leaves the database in a valid state.
Primary key vs foreign key
A primary key uniquely identifies each row in a table; a foreign key is a column that references another table's primary key to enforce a relationship between them.
Batch vs stream processing
Batch processing handles accumulated data in large scheduled chunks, while stream processing handles continuous events in near real time as they arrive.
Azure SQL Database
A platform-as-a-service relational database from Microsoft that provides built-in high availability, automated backups, and elastic scaling without managing an operating system or SQL Server installation.
Azure SQL Managed Instance
A platform-as-a-service offering that runs a near-complete SQL Server engine inside a virtual network, supporting instance-scoped features such as SQL Agent and cross-database queries.
SQL Server on Azure VMs
An infrastructure-as-a-service option that runs full SQL Server inside a virtual machine, giving complete control over the operating system, engine version, and configuration.
Elastic pool
A shared set of compute and storage resources in Azure SQL Database used by several databases together, which suits databases with unpredictable or intermittent usage peaks.
DTU purchasing model
A billing model for Azure SQL Database that bundles compute, memory, and read/write rates into a single Database Transaction Unit measure with a fixed amount of included storage.
vCore purchasing model
A billing model for Azure SQL Database, Managed Instance, and SQL Server on VMs that charges for virtual cores, memory, and storage separately and qualifies for Azure Hybrid Benefit.
Point-in-time restore
An Azure SQL Database capability that restores a database to any moment within a retention window, typically up to 35 days, using automated backups.
Always Encrypted
A SQL Server and Azure SQL feature that encrypts sensitive columns on the client side so the database engine never sees plaintext data, protecting against privileged database access.
Azure Blob Storage
An object storage service for massive amounts of unstructured data such as documents, images, and backups, organized into containers and separated by access tiers.
Blob access tiers
Hot, Cool, Cold, and Archive tiers in Azure Blob Storage trade storage cost against access cost and latency; Archive is cheapest to store but slowest to read.
Data Lake Storage Gen2
Azure Blob Storage extended with a hierarchical namespace, which enables directory-level access control and lets analytics engines read it as a file system at scale.
Azure Table Storage
A NoSQL key-value store for schemaless entities identified by a partition key and row key, offering inexpensive storage for very large volumes of simple structured records.
Azure Cosmos DB
A globally distributed multi-model NoSQL database service that offers five tunable consistency levels and single-digit millisecond latency across replicated Azure regions.
Cosmos DB request unit
The normalized throughput currency of Azure Cosmos DB: it measures the cost of reads, writes, and queries against provisioned or autoscale RU/s capacity.
Cosmos DB consistency levels
Five settings from strongest to weakest are strong, bounded staleness, session, consistent prefix, and eventual, trading data freshness guarantees against latency and availability.
Azure Files
A managed cloud file share service that exposes SMB and NFS shares mountable by many clients, commonly used to replace or extend on-premises file servers.
Azure Synapse Analytics
A unified analytics platform combining dedicated SQL pools, a serverless SQL pool, Apache Spark pools, pipelines, and data integration inside a single workspace.
Dedicated SQL pool
A provisioned Synapse component that stores data in relational tables distributed across compute nodes with massively parallel processing for high-performance data warehousing.
Serverless SQL pool
A Synapse component that queries files in a data lake using T-SQL without provisioning storage, billing only for the data processed by each query.
Azure Data Factory
A cloud data integration service that runs pipelines of activities orchestrating data movement and transformation, using linked services, datasets, and integration runtimes.
Azure Databricks
A managed Apache Spark analytics platform with collaborative notebooks, used for large-scale data engineering, streaming, and machine learning workloads on Delta Lake.
Azure Stream Analytics
A real-time stream processing engine that runs SQL-like queries over continuous data from Event Hubs or IoT Hub and writes results to storage, databases, or Power BI.
Star schema
A data warehouse design with a central fact table of measurements surrounded by denormalized dimension tables that describe it, keeping analytical joins to a single hop.
Power BI semantic model
A layer of tables, relationships, measures, and business logic, often authored in Power BI, that reporting visuals query to return consistent results.

Azure DP-900 study guide

Describe Core Data Concepts

Know the vocabulary first

Core data concepts questions are almost always vocabulary and classification questions. Learn three axes:

  1. Structure: Structured data fits a fixed schema (rows and columns, e.g. a customer table). Semi-structured data has tags/keys but flexible schema (JSON, XML, YAML, Avro). Unstructured data has no schema (images, audio, video, PDFs, free text).
  2. Workload type: OLTP (transactional) handles many small read/write operations, is normalized, and optimizes for write speed and concurrency. OLAP (analytical) handles few, large, read-heavy queries over historical data, is often denormalized, and optimizes for aggregation.
  3. Processing style: Batch processes chunks on a schedule; streaming processes events continuously as they arrive.

Non-relational families

Be able to match a scenario to a database type: key-value (fast lookups by key), document (JSON-like documents, flexible schema), graph (entities and relationships, traversals), and column-family (wide rows, sparse data, very large scale).

Roles and file formats

Know who does what: a database administrator manages availability, security, and performance; a data engineer builds pipelines and integrates data; a data analyst builds reports and visualizations; a data scientist builds models and experiments. Know that CSV is text and row-oriented, JSON/XML are semi-structured text, and Parquet/ORC/Avro are binary and compact (Parquet is columnar and compresses well, so it suits analytics).

Traps and a rule of thumb

Traps: calling JSON "unstructured" (it is semi-structured); confusing ETL (transform before load) with ELT (load raw, transform later); assuming OLTP and OLAP use the same schema design.

Rule of thumb: ask what shape is the data and what will be done with it. Example: a folder of JSON order documents queried by analytics is semi-structured data in an analytical workload - load it into a columnar store, do not model it as an OLTP table.

Identify Considerations for Relational Data on Azure

Relational fundamentals

The relational model stores data in tables of rows and columns, with a schema enforcing types and constraints. Know: primary key (unique row identifier), foreign key (enforces a relationship and referential integrity), unique, check, not null, and default constraints; views (saved queries); and indexes (clustered = physical row order; nonclustered = separate lookup structure that speeds reads but slows writes). Normalization removes duplication; denormalization trades duplication for fewer joins in reporting.

Choosing an Azure service

  • Azure SQL Database - PaaS, fully managed, ideal for single databases or many databases sharing resources (elastic pools); serverless tier auto-pauses. No SQL Server Agent, no cross-database queries (without workarounds), no instance-level features.
  • Azure SQL Managed Instance - PaaS with near-full SQL Server compatibility: VNet injection, SQL Agent, cross-database queries, instance-scoped features. Use it for "lift and shift with minimal changes".
  • SQL Server on Azure Virtual Machines - IaaS; full OS and engine control, you patch and manage. Use when you need OS-level access or an unsupported version/feature.
  • Open-source engines - Azure Database for MySQL, PostgreSQL (Flexible Server), and MariaDB as managed options.

Migration and traps

Use Azure Database Migration Service (with assessment tooling such as Data Migration Assistant) to move on-premises databases to Azure.

Traps: recommending SQL Database for a workload that requires SQL Server Agent jobs; assuming PaaS gives OS access; forgetting that managed services still require you to design indexes and tune queries.

Worked example: a legacy app needs cross-database joins, SQL Server Agent scheduling, and near-zero code changes, but the team does not want to patch the OS. Answer: Azure SQL Managed Instance - not SQL Database (missing features) and not a VM (needs patching).

Describe Considerations for Working with Non-Relational Data on Azure

Azure Cosmos DB

Cosmos DB is a globally distributed, multi-model NoSQL service. Know its APIs: NoSQL (document), MongoDB, Cassandra (column-family), Gremlin (graph), and Table. Data is partitioned by a partition key you choose at creation; a good key spreads traffic evenly and is used in most queries. Throughput is expressed in request units (RU/s).

Memorize the five consistency levels from strongest to weakest: strong, bounded staleness, session, consistent prefix, eventual. Session is the default and satisfies read-your-own-writes within a session; eventual offers lowest latency.

Azure Storage data services

  • Blob storage for unstructured objects: block blobs (files, media, backups), page blobs (random reads/writes, used for VHDs), append blobs (logs). Access tiers: hot, cool, cold, archive; archive is cheapest for storage but requires rehydration (hours) before reading.
  • Azure Files for managed SMB/NFS file shares.
  • Queue storage for asynchronous messages.
  • Table storage for key-value/NoSQL entities at low cost.

Redundancy: LRS (single region), ZRS (zones in one region), GRS/RA-GRS (replicated to a secondary region; RA adds read access).

Traps and a rule of thumb

Traps: assuming Cosmos DB Table API and Azure Table storage are the same product (Cosmos adds global distribution, SLAs, and lower latency); picking archive tier for data read daily; choosing eventual consistency when the app must read its own writes.

Rule of thumb: unstructured files go to Blob; relationship-heavy graphs go to Cosmos DB Gremlin; if the app must read what it just wrote, use session consistency.

Describe an Analytics Workload on Azure

The shape of an analytics solution

Analytics workloads ingest data, store it at scale, transform it, and serve it to reports or models. Typical Azure components:

  • Azure Data Lake Storage Gen2 - hierarchical namespace over Blob storage; the landing zone for raw files (often organized in a medallion pattern: bronze raw, silver cleansed, gold curated).
  • Azure Data Factory - orchestration and data movement (pipelines, activities, linked services, integration runtimes).
  • Azure Synapse Analytics - an integrated workspace with pipelines, a dedicated SQL pool (provisioned MPP data warehouse), a serverless SQL pool (query files in the lake on demand, pay per query), and Spark pools.
  • Azure Databricks - managed Apache Spark for engineering and machine learning.
  • HDInsight - managed open-source big-data clusters (Spark, Hadoop, Kafka, HBase) when you need specific frameworks.
  • Azure Stream Analytics / Event Hubs - real-time ingestion and stream processing.
  • Power BI / Azure Analysis Services - semantic models and visualization.

Modeling and traps

Warehouses usually use a star schema: fact tables hold numeric measures and foreign keys; dimension tables hold descriptive attributes. Compare ETL vs ELT (ELT is common in cloud warehouses because storage and compute scale independently) and batch vs streaming (streaming for continuous events, batch for scheduled loads).

Traps: calling a data lake a data warehouse (a lake stores raw files; a warehouse stores modeled, query-ready tables); assuming serverless SQL pools hold data (they query external files); forgetting that data movement in Data Factory requires an integration runtime.

Worked example: the business needs dashboards over years of sales history from many sources, refreshed nightly. Pattern: Data Factory loads raw files into ADLS Gen2, Spark transforms them into star-schema tables in a dedicated SQL pool, and Power BI reports on top.

Azure DP-900 cheat sheet

Quick reference

Describe core data concepts

  • Structured data fits a fixed schema of tables, rows, and columns; semi-structured data (JSON, XML) is self-describing through tags or keys; unstructured data (images, audio, free text) has no predefined schema.

  • OLTP workloads run many small, concurrent read/write transactions and require ACID guarantees; OLAP workloads run large aggregate reads over historical data and favor scan throughput.

  • ACID = Atomicity, Consistency, Isolation, Durability. Many NoSQL stores instead offer BASE (Basically Available, Soft state, Eventual consistency) as a scalability trade-off.

  • Normalization splits data into related tables to remove redundancy and update anomalies; denormalization deliberately duplicates data to reduce joins and speed reads.

  • A primary key uniquely identifies each row in a table; a foreign key references a primary key in another table; a composite key combines two or more columns.

  • Batch processing handles bounded data sets on a schedule; stream processing handles unbounded event flows continuously with low latency.

  • Common file formats: CSV is delimited text; JSON is nested and self-describing; Parquet and ORC are columnar and compressed for analytics; Avro is row-based with an embedded schema.

  • Data is commonly characterized by volume, velocity, variety, veracity, and value.

  • Role split: a DBA manages availability, security, and backup; a data engineer builds pipelines and integration; a data analyst builds reports; a data scientist builds models.

Quick reference

Identify considerations for relational data on Azure

  • Azure SQL Database is PaaS (single database or elastic pool) and hides the OS and engine configuration; Azure SQL Managed Instance is PaaS with a near-complete SQL Server surface area inside a virtual network; SQL Server on Azure VMs is IaaS with full OS and engine control.

  • Elastic pools share eDTUs or vCores across databases whose usage peaks at different times; a fixed single-database size suits steady, predictable load.

  • The DTU model bundles compute, memory, and I/O into preset tiers; the vCore model selects compute and storage separately and qualifies for Azure Hybrid Benefit.

  • Serverless compute auto-scales and can auto-pause, billing per second of compute used, which fits intermittent or unpredictable workloads.

  • Automated backups enable point-in-time restore (default retention 7 days, configurable up to 35) plus long-term retention; active geo-replication and auto-failover groups provide regional disaster recovery.

  • Built-in high availability uses a primary replica plus secondary replicas, so no extra HA configuration or licensing is required in the PaaS tiers.

  • Security controls: Transparent Data Encryption encrypts data at rest; Always Encrypted keeps keys client-side; dynamic data masking hides values in results; row-level security filters rows; Microsoft Entra ID provides authentication and managed identities.

  • Access is gated by server-level and database-level firewall rules, virtual network service endpoints, and private endpoints.

  • Azure Database for PostgreSQL, MySQL, and MariaDB are PaaS alternatives for open-source engines; Azure Database Migration Service moves on-premises databases to these targets.

Quick reference

Describe considerations for working with non-relational data on Azure

  • NoSQL stores typically scale horizontally by partitioning, are schema-flexible, and often trade strict ACID guarantees for availability and low latency.

  • Four common NoSQL shapes: key-value (Table Storage, Azure Cache for Redis), document (Cosmos DB for NoSQL or MongoDB), graph (Gremlin), column-family (Cassandra).

  • Blob Storage tiers trade storage cost against access cost: Hot, Cool (30-day minimum), Cold (90-day minimum), Archive (offline, requires rehydration). Blob types are block, append, and page.

  • Azure Data Lake Storage Gen2 is Blob Storage with a hierarchical namespace, enabling directory- and file-level POSIX ACLs and efficient access by analytics engines.

  • Table Storage entities require a PartitionKey and RowKey; entities sharing a partition are stored together and queried efficiently, so a high-cardinality key spreads load.

  • Cosmos DB APIs include NoSQL (SQL syntax), MongoDB, Cassandra, Gremlin (graph), and Table; throughput is provisioned as request units per second (RU/s) or autoscale max RU/s.

  • Cosmos DB partition key choice determines scalability and hot-partition risk; the key should have high cardinality and appear in most query filters.

  • Cosmos DB consistency, strongest to weakest: strong, bounded staleness, session, consistent prefix, eventual. Weaker levels give lower latency and higher availability.

  • Azure Files provides managed SMB and NFS shares; Azure Cache for Redis provides in-memory caching rather than durable primary storage.

Quick reference

Describe an analytics workload on Azure

  • A Synapse Analytics workspace can contain dedicated SQL pools (provisioned MPP data warehouse), a serverless SQL pool (T-SQL over lake files, billed per data processed), Spark pools, Data Explorer pools, and pipelines.

  • Azure Data Factory orchestrates ETL/ELT: pipelines contain activities, activities read and write datasets built on linked services, and integration runtimes (Azure, self-hosted, SSIS) supply the compute for movement.

  • Azure Databricks is a managed Apache Spark platform with collaborative notebooks and Delta Lake; Synapse Spark pools provide Spark inside the Synapse workspace instead.

  • Streaming path: Event Hubs or IoT Hub ingests events, Stream Analytics or Spark Structured Streaming applies SQL-like queries and windowing, and results sink to storage, a database, or Power BI.

  • Medallion lakehouse layering: bronze holds raw ingested data, silver holds cleansed and conformed data, gold holds curated aggregates consumed by reporting.

  • Dimensional modeling: fact tables hold measures plus foreign keys, dimension tables hold descriptive attributes; a star schema needs one join hop while a snowflake normalizes dimensions.

  • Power BI is the visualization layer: it connects to semantic models, defines measures in DAX, and refreshes on a schedule; it is not the storage engine.

  • Microsoft Fabric is a SaaS analytics platform that unifies OneLake storage, Data Factory, Synapse data engineering and data warehouse, real-time analytics, and Power BI under one capacity.

  • Azure HDInsight provides managed open-source clusters (Hadoop, Spark, Hive, Kafka) when specific framework versions or control are required.

Azure DP-900 key terms

Structured data

Data that fits neatly into a fixed schema of rows and columns, where every record has the same fields and each field holds a defined type of value, such as a customer table.

Related: Semi-structured data, Relational database
Semi-structured data

Data that carries tags or markers describing its own shape, such as JSON or XML documents, so individual records can differ in fields while still being machine-readable without a strict table schema.

Related: Unstructured data, Document database
Unstructured data

Data with no predefined model or schema, such as images, audio, video, and free-form text, usually kept as files and processed by tools rather than queried column by column.

Related: Azure Blob Storage, Data lake
Relational database

A collection of data organized into tables that relate to one another through shared key values, queried with SQL and managed by a database engine that enforces the schema.

Related: Table, Primary key
Table

A named two-dimensional structure in a relational database built from rows (records) and columns (fields), where each column declares an attribute name and a data type.

Related: Relational database, Primary key
Primary key

A column or combination of columns whose values uniquely identify each row in a table; duplicates and empty values are rejected, which keeps every record individually addressable.

Related: Foreign key, Index
Foreign key

A column in one table that stores a primary key value from another table, creating a link between the two tables and preventing rows that point at records which do not exist.

Related: Primary key, Relational database
Index

A supporting structure that keeps sorted references to table rows so the engine can locate matching records quickly instead of scanning the entire table, at the cost of storage and slower writes.

Related: Primary key, Table
Normalization

The practice of splitting related data into separate linked tables so each fact is stored once, which reduces duplicated values and the errors that appear when copies are updated inconsistently.

Related: Relational database, Foreign key
OLTP (transactional workload)

A workload made of many small, short read and write operations that insert, update, or delete individual records, typically supporting day-to-day applications such as order entry or banking.

Related: OLAP (analytical workload), Relational database
OLAP (analytical workload)

A workload that reads large volumes of historical data and aggregates it to answer business questions, using fewer but much heavier queries than an application's day-to-day processing.

Related: OLTP (transactional workload), Data warehouse
Batch processing

Handling data in large groups that are collected over a period and processed together by a scheduled job, rather than reacting to each event the moment it arrives.

Related: Stream processing, Azure Data Factory
Stream processing

Handling data continuously as individual events arrive, computing results inside a short time window so dashboards or alerts reflect what is happening right now.

Related: Batch processing, Azure Stream Analytics
Azure SQL Database

A fully managed relational database service that runs the SQL Server engine in Azure, with backup, patching, and high availability handled for you and no operating system to administer.

Related: Elastic pool, Azure SQL Managed Instance
Azure SQL Managed Instance

A managed service that runs nearly the complete SQL Server engine inside a private Azure virtual network, giving close compatibility with on-premises features such as cross-database queries and SQL Agent.

Related: Azure SQL Database, SQL Server on Azure Virtual Machines
SQL Server on Azure Virtual Machines

An infrastructure-as-a-service option where you install and run your own SQL Server instance on an Azure virtual machine, keeping full control of the operating system, disks, and configuration.

Related: Azure SQL Managed Instance, Azure SQL Database
Azure Database for PostgreSQL

A managed relational service for the open-source PostgreSQL engine, delivered in server tiers with automated backups, scaling, and patching so you focus on schema and queries rather than servers.

Related: Azure SQL Database, Relational database
Elastic pool

A shared set of compute and storage resources that several Azure SQL databases draw from, letting busy databases borrow capacity from idle ones and lowering total cost.

Related: Azure SQL Database, DTU-based purchasing model
DTU-based purchasing model

A billing option for Azure SQL Database that blends compute, memory, and read/write rates into a single unit, with the size chosen from fixed performance tiers.

Related: vCore-based purchasing model, Elastic pool
vCore-based purchasing model

A billing option where you choose virtual cores, memory, and storage independently, and may apply the Azure Hybrid Benefit to reuse existing SQL Server licenses.

Related: DTU-based purchasing model, Azure SQL Database
Azure Cosmos DB

A globally distributed, multi-model database service that stores items in containers, replicates data across regions, and exposes several APIs including SQL, MongoDB, Cassandra, Gremlin, and Table.

Related: Partition key, Request unit (RU)
Key-value store

A data store that maps a unique key to an opaque value, giving very fast lookups when the key is known but little ability to search inside the stored value.

Related: Document database, Azure Table Storage
Document database

A store that keeps each record as a self-describing document such as JSON, where documents may have different fields and are read as whole units instead of rows spread across tables.

Related: Semi-structured data, Azure Cosmos DB
Graph database

A store built from nodes representing entities and edges representing relationships, optimized for traversing connections such as social networks, recommendation paths, or fraud rings.

Related: Azure Cosmos DB, Key-value store
Column-family store

A store that groups columns into families so wide, sparse records can be read by column group and spread across many nodes, the model used by the Cassandra API in Cosmos DB.

Related: Azure Cosmos DB, Document database
Partition key

A field whose value Cosmos DB uses to group related items into logical partitions, which are spread across physical partitions; choosing a high-cardinality value avoids hot spots.

Related: Azure Cosmos DB, Request unit (RU)
Request unit (RU)

The normalized cost of a Cosmos DB operation, combining CPU, memory, and I/O work; throughput is provisioned or auto-scaled as a number of request units per second.

Related: Azure Cosmos DB, Partition key
Consistency level

A Cosmos DB setting that trades read freshness against latency and availability, ranging from strong through bounded staleness, session, and consistent prefix to eventual consistency.

Related: Azure Cosmos DB, Request unit (RU)
Azure Blob Storage

Azure's object storage service for very large amounts of unstructured data such as files, images, and backups, held in containers inside a storage account and offered in hot, cool, cold, and archive tiers.

Related: Unstructured data, Azure Data Lake Storage Gen2
Azure Data Lake Storage Gen2

A storage account configuration that adds a hierarchical namespace and fine-grained folder permissions to blob storage, making it a practical landing zone for large-scale analytics.

Related: Data lake, Azure Blob Storage
Azure Table Storage

A schemaless store in Azure Storage that holds large numbers of simple entities, each addressed by a partition key and a row key, with no fixed set of columns required.

Related: Key-value store, Azure Cosmos DB
Azure Synapse Analytics

An analytics platform that brings ingestion pipelines, a SQL data warehouse, Spark pools, and a shared workspace together, so teams can build end-to-end analytical solutions inside one service.

Related: Dedicated SQL pool, Data warehouse
Dedicated SQL pool

A Synapse component that provides provisioned, massively parallel processing storage and compute for relational warehouse tables, sized and billed separately from the surrounding workspace.

Related: Data warehouse, Azure Synapse Analytics
Data warehouse

A central store of cleaned, modeled historical data gathered from many source systems and shaped for fast aggregate queries that feed reporting and business intelligence.

Related: OLAP (analytical workload), Data lake
Data lake

A storage repository that holds raw files of any format at very large scale, preserving the original data until it is transformed or queried for analysis.

Related: Azure Data Lake Storage Gen2, Data warehouse
Azure Data Factory

A cloud data integration service where you define pipelines of activities that copy and transform data between stores, run on a schedule, or triggered by an event.

Related: Batch processing, Azure Synapse Analytics
Azure Databricks

A managed Apache Spark platform in Azure with collaborative notebooks, used to process very large data sets using code in Python, SQL, Scala, or R.

Related: Data lake, Azure Synapse Analytics
Azure Stream Analytics

A real-time analytics service that runs SQL-like queries over flowing data from sources such as IoT Hub or Event Hubs and writes the results to outputs for alerting or reporting.

Related: Stream processing, Power BI
Power BI

Microsoft's business intelligence service for building interactive reports and dashboards on top of a data model, authored on the desktop and published for sharing in the cloud.

Related: Data warehouse, Azure Synapse Analytics

Azure DP-900 study plan

8 weeks · About 6 hours per week

Week 1

Describe core data concepts

  • Define structured, semi-structured, and unstructured data and give two concrete examples of each from your own experience

  • Distinguish OLTP from OLAP workloads by listing three contrasting characteristics of each

  • Classify at least six common file formats (CSV, JSON, XML, Parquet, Avro, ORC, plain text) as structured or semi-structured and note which are row-based vs column-based

  • Describe the day-to-day responsibilities of a data engineer, database administrator, data analyst, and data scientist, and state which role owns each of ten sample tasks

Your checkpoint

A one-page handwritten or typed summary sheet of Domain 1 terminology, plus a closed-book self-quiz of 25 questions scored at 80% or higher

Week 2

Describe core data concepts & Describe an analytics workload on Azure

  • Explain batch processing versus stream processing and identify which one fits each of five described business scenarios

  • Define core analytics concepts: data ingestion, data storage, data processing, data visualization, and the difference between a data lake and a data warehouse

  • Describe the purpose of Azure Data Factory, Azure Synapse Analytics, Azure Databricks, Azure Stream Analytics, and Power BI in one sentence each

  • Trace a described end-to-end analytics pipeline and label where each Azure service fits

Your checkpoint

A hand-drawn or text-based architecture diagram of one batch pipeline and one streaming pipeline, with every service named and its role annotated

Week 3

Describe an analytics workload on Azure

  • Compare Azure Synapse Analytics, Azure Databricks, and Azure Data Factory by listing the primary job each performs and one case where each is the better choice

  • Differentiate the Synapse dedicated SQL pool, serverless SQL pool, and Spark pool in terms of when you would use each

  • Describe how Azure Data Lake Storage Gen2 organizes data using containers, directories, and files, and explain the hierarchical namespace benefit

  • Explain the difference between Azure Event Hubs and Azure Stream Analytics in a streaming workload

Your checkpoint

A four-row comparison table covering ingestion, storage, processing, and visualization services, with at least two selection rules written beneath it

Week 4

Describe an analytics workload on Azure & Describe core data concepts

  • Describe how Power BI connects to a data source, models data, and produces a report, including the difference between a dataset and a report

  • Explain data visualization choices by matching chart types to described scenarios (trend over time, part-to-whole, comparison across categories)

  • Re-state the five analytics stages from memory and map each to at least one Azure service

  • Identify the correct Azure analytics service for ten described scenarios under timed conditions

Your checkpoint

A timed 30-question practice set covering Domains 1 and 4, scored at 80% or higher, with every missed question rewritten as a corrected statement

Week 5

Identify considerations for relational data on Azure

  • Define the relational model: table, row, column, primary key, foreign key, and candidate key, using a small example schema you design yourself

  • Write and correctly run SELECT, WHERE, ORDER BY, INSERT, UPDATE, and DELETE statements against a sample relational database

  • Normalize a described unnormalized table to third normal form and explain each step

  • Compare Azure SQL Database, Azure SQL Managed Instance, and SQL Server on Azure virtual machines by deployment model, control level, and typical use case

  • Explain how Azure Database for MySQL and Azure Database for PostgreSQL differ from Azure SQL Database in supported engines and migration fit

Your checkpoint

A working T-SQL script file containing at least eight statements (including a join and an aggregate) that executes without errors, plus a results note

Week 6

Identify considerations for relational data on Azure & Describe considerations for working with non-relational data on Azure

  • Explain the role of indexes, views, stored procedures, and transactions in a relational database and state one benefit of each

  • Describe Azure SQL security basics: authentication versus authorization, firewall rules, and role-based access

  • Define the main non-relational data models: key-value, document, graph, and column-family, with one realistic use case each

  • Compare Azure Blob Storage, Azure Table Storage, Azure Cosmos DB, and Azure Files by data model and primary scenario

Your checkpoint

A two-column contrast sheet (relational vs non-relational) listing at least ten decision criteria, plus a 15-question self-quiz scored at 80% or higher

Week 7

Describe considerations for working with non-relational data on Azure & Identify considerations for relational data on Azure

  • Describe Azure Cosmos DB capabilities: multiple APIs, global distribution, and tunable consistency levels, and match each consistency level to a described scenario

  • Distinguish Blob Storage access tiers (hot, cool, cold, archive) and select the right tier for four described workloads

  • Classify Blob Storage content types as block blobs, append blobs, or page blobs for described scenarios

  • Explain provisioned throughput versus serverless in Cosmos DB and state when each is appropriate

  • Re-verify the ten weak points you recorded in Weeks 5 and 6 by answering them cold without notes

Your checkpoint

A 20-question mixed practice set covering Domains 2 and 3 scored at 85% or higher, plus a fixed list of remaining weak areas

Week 8

Describe core data concepts & Identify considerations for relational data on Azure & Describe considerations for working with non-relational data on Azure & Describe an analytics workload on Azure

  • Complete two full-length timed practice exams under exam conditions and record the score per domain

  • Rewrite every missed item as a corrected factual statement and re-quiz yourself on those statements

  • Recite the four domains and their weightings from memory and confirm your study time matched the weightings

  • Review the two lowest-scoring domains for 60 minutes each and re-test only those domains

Your checkpoint

A score log showing two full practice exam results with per-domain breakdown, both at or above the passing threshold, and a final one-page cue sheet of facts you most often missed

Milestones

Week 2: You can explain, without notes, the difference between batch and streaming processing and name the Azure service used for each; Domain 1 self-quiz at 80% or higher

Week 4: You can name and correctly place all five analytics stages in a pipeline and select the right Azure service for ten described scenarios; combined Domain 1 and 4 practice score at 80% or higher

Week 6: You have written and successfully executed a multi-statement T-SQL script and can contrast relational versus non-relational storage across ten criteria

Week 8: Two full timed practice exams completed with per-domain scores at or above the passing threshold and no domain below 70%

Azure DP-900 exam day

Your checklist

Government-issued photo ID with signature

Use a valid passport or driver's licence. The first and last name must match your exam registration exactly; a mismatch is the most common reason candidates are turned away. If your ID name differs from your registration, update the registration before exam day.

Appointment confirmation and registration/order number

Have your confirmation email or registration ID accessible on your phone before check-in (phone gets put away during the exam itself). Confirm the exact date, time and time zone of your appointment.

A second form of ID if your provider or region requires it

Some test centres ask for a secondary ID or a signature-bearing card. Requirements vary by location and delivery method - confirm with the provider before exam day.

Completed system test (online-proctored delivery)

Run the provider's system/pre-check tool on the same computer, camera, microphone and network you will use on exam day. Do this at least 24-48 hours ahead so you have time to fix issues or reschedule.

Stable wired internet connection and a working webcam/microphone

Wireless can be acceptable but a wired connection reduces dropout risk. Close bandwidth-hungry apps (updates, streaming, VPNs unless required) before check-in.

A quiet, private, well-lit room with a clear desk (online delivery)

Remove phones, notes, books, second monitors, smart watches and any other devices from the desk and surrounding area. The proctor will ask you to show the room with your camera.

Confirmed test centre location, travel time and arrival instructions (test-centre delivery)

Do a route check the day before and allow buffer time for traffic and security screening. Specific arrival and locker rules are centre-dependent - confirm with the provider.

Know the exam shape before you sit down

DP-900 is a fundamentals-level exam with roughly 40-50 multiple-choice/multiple-response questions in about 45 minutes, scored on a 1-1000 scale with 700 to pass. No labs or hands-on coding.

Skim the official skills outline one last time

The four measured areas are core data concepts (roughly 25-30%), relational data on Azure (20-25%), non-relational data on Azure (15-20%), and analytics workloads on Azure (25-30%). Use it to decide what to review, not to cram new topics.

Restroom and water before check-in

Fundamentals exams are short and scheduled breaks are not part of the standard delivery. Take care of physical needs before the clock starts.

Know where your score report will appear

You typically see a provisional pass/fail at the end of the test, with the official score and certification status appearing in your Microsoft Learn / certification profile within a short period afterwards.

Plan the day

72 hours before

Re-confirm the appointment (date, time, time zone, delivery method). Run or re-run the system test if you are testing online. Re-read the skills outline and spend your remaining study time only on the two domains you score weakest on in practice questions - for most candidates these are analytics workloads and non-relational data.

48 hours before

Do one timed practice set under exam conditions (no notes, single screen) to check your pacing against roughly one minute per question. Stop studying new material after this point; only review what you already know.

24 hours before

Light review only: service-to-scenario mapping (Blob vs Files vs Tables vs Queues, Azure SQL vs SQL Managed Instance vs Synapse, Cosmos DB APIs, Data Factory vs Synapse Pipelines vs Databricks vs Stream Analytics vs Power BI). Prepare your ID and confirmation email, tidy the test room, charge devices, and go to bed at a normal hour.

Morning of

Eat and hydrate normally. Avoid last-minute cramming of unfamiliar topics - it raises anxiety without adding reliable points. Reboot the computer (online delivery) and close all other applications, or leave early enough for the test centre with a buffer.

30 minutes before

Begin check-in / arrive at the centre. For online delivery, complete the ID photo, room scan and proctor handshake early; the check-in process can take longer than expected and time spent waiting is not deducted from your exam clock, but a late start can be.

During the test

Read the full question stem before looking at options - many DP-900 items hinge on one word such as 'unstructured', 'petabyte-scale', 'relational' or 'real-time'. Answer every question (there is no penalty for guessing), flag anything slow for review, and use remaining time to revisit flagged items rather than to second-guess confident answers.

After the test

Note your provisional result, then confirm the official score in your certification profile. If you did not pass, check the provider's retake waiting period before rebooking, and use your domain-level score breakdown to target the next attempt.

Rules to check

  • Bring a valid government-issued photo ID with signature; the name must match your registration exactly. No ID, no exam - and the fee is generally not refunded in that case.

  • The exam content is confidential under the candidate agreement. Do not copy, screenshot, share or discuss specific questions or answers afterwards.

  • No notes, books, phones, smart watches, second screens or other aids are permitted during the exam. Online proctors will ask you to clear and show your workspace; test centres provide a locker for personal items.

  • Do not leave the camera view or your seat during an online exam except as directed by the proctor. Repeated movement or looking away can be flagged as a policy violation.

  • Scoring is scaled, not a raw percentage: you need 700 out of 1000. A handful of wrong answers does not doom the attempt, so keep moving and answer everything.

  • Scheduled breaks are not part of the standard fundamentals exam delivery. If you need an accommodation, request it through the provider well before exam day - do not assume it will be granted on the spot.

  • Rescheduling, cancellation windows, fees, retake waiting periods and accommodation rules vary by provider and region. Confirm the exact terms with the provider rather than relying on second-hand information.

  • If technical problems interrupt an online exam, stop and contact the proctor immediately through the on-screen chat; do not attempt to fix things by closing the browser or restarting on your own.

Azure DP-900 FAQ

How many Azure DP-900 practice questions are available?

The practice set has 197 Azure DP-900 questions. Each one includes the choices, the correct answer, and an explanation, and the set is free to read with no signup.

How many Azure DP-900 flashcards are available?

The flashcard set has 32 Azure DP-900 cards. Each card shows the term and its definition.

Which topics does the Azure DP-900 study material cover?

The Azure DP-900 study material has 4 sections, including Describe Core Data Concepts, Identify Considerations for Relational Data on Azure, and Describe Considerations for Working with Non-Relational Data on Azure.

What format facts does the catalog list for the Azure DP-900 exam?

The catalog lists a duration of 45 minutes and these item types: proctored assessment and interactive components for the Azure DP-900 exam.