Microsoft Certified: Azure Data Fundamentals — Practice Set 1

Question 1

Your organization needs to store customer orders where each record has a fixed set of columns including order ID, customer name, product, and quantity. Which type of data structure best describes this scenario?

Accepted Answer

Structured data is organized in a fixed schema with defined columns and data types, such as rows in a relational database table. Customer order records with fixed columns (order ID, customer name, product, quantity) are a classic example of structured data. Semi-structured data (like JSON or XML) has flexible schemas; unstructured data (like images or documents) has no predefined schema.

Question 2

You are reviewing a dataset that stores product reviews as JSON documents where each review can have a different set of fields. Some reviews include a rating, others include images, and some have neither. Which type of data does this best represent?

Accepted Answer

Semi-structured data has some organizational properties (like JSON key-value pairs or XML tags) but does not conform to a strict tabular schema. Each document can have different fields. JSON documents where each record may contain different fields are the canonical example of semi-structured data.

Question 3

Your organization processes point-of-sale transactions that must be recorded immediately and guarantee consistency when a customer purchases an item. Which type of data workload best describes this requirement?

Accepted Answer

Transactional workloads (OLTP — Online Transaction Processing) involve recording individual business events such as sales, orders, and payments in real time. They require ACID properties (Atomicity, Consistency, Isolation, Durability) to ensure data integrity. Point-of-sale transactions are a textbook example of OLTP workloads.

Question 4

Your team is discussing data roles in your organization. Which two responsibilities are typically associated with a data engineer? Choose 2.

Accepted Answer

Data engineers are responsible for building and maintaining data pipelines (ingestion, transformation, orchestration) and provisioning/managing data infrastructure (databases, data lakes, warehouses). Data analysts create reports and dashboards (B), data scientists train ML models (D), and writing ad hoc SQL for business questions (E) is typically a data analyst responsibility.

Question 5

You need to store a large amount of historical sales data for reporting and trend analysis. The workload requires reading large volumes of data but does not require frequent individual record updates. Which type of data workload is most appropriate?

Accepted Answer

OLAP (Online Analytical Processing) systems are optimized for reading and analyzing large volumes of historical data for reporting and trend analysis. They use analytical data stores such as data warehouses. OLTP systems are optimized for frequent individual record updates and transactional consistency, which is not needed here.

Question 6

Which file format stores data in a row-and-column tabular structure with comma-separated values and is commonly used for data exchange?

Accepted Answer

CSV (Comma-Separated Values) is a plain-text tabular format where fields are separated by commas. It is one of the most common formats for data exchange due to its simplicity and human readability. Parquet and ORC are columnar binary formats optimized for analytics; Avro is a row-based binary format with schema evolution support.

Question 7

Your organization stores employee photos, contract PDF documents, and video recordings in Azure. A data analyst asks you to classify these files. Which category do these files belong to?

Accepted Answer

Unstructured data does not have a predefined schema or organization. Images (JPG, PNG), PDF documents, audio files, and video recordings are all examples of unstructured data. They cannot be easily stored in rows and columns without additional processing. Azure Blob Storage is the typical service for storing unstructured data at scale.

Question 8

You are designing a relational database for a retail application. You need to eliminate data redundancy and ensure that each piece of data is stored only once. Which database design technique achieves this?

Accepted Answer

Normalization is the process of organizing a relational database to reduce data redundancy and improve data integrity. It involves dividing tables into smaller related tables and defining relationships between them. Denormalization intentionally introduces redundancy to improve read performance. Partitioning divides data into segments; indexing speeds up data retrieval.

Question 9

Your organization wants a fully managed platform-as-a-service (PaaS) relational database on Azure that provides built-in high availability, automatic backups, and automatic patching without managing the underlying infrastructure. Which Azure service should you use?

Accepted Answer

Azure SQL Database is a fully managed PaaS relational database service that provides built-in high availability, automatic backups, automatic patching, and elastic scaling without requiring infrastructure management. SQL Server on Azure Virtual Machines is IaaS (you manage the OS and SQL Server). Azure SQL Managed Instance is a managed instance but targets near-100% SQL Server compatibility. Azure Table Storage is a NoSQL key-value store.

Question 10

Your company is evaluating Azure database services for open-source workloads. Which two Azure services provide managed relational databases for open-source database systems? Choose 2.

Accepted Answer

Azure Database for PostgreSQL and Azure Database for MySQL are fully managed PaaS services for open-source relational databases. Azure SQL Database is a managed service for Microsoft SQL Server (not open-source). Azure Cosmos DB is a NoSQL multi-model database. Azure Synapse Analytics is an analytics service combining data warehousing and big data analytics.

Question 11

You need to retrieve all columns from a table named Orders where the order total is greater than 500. Which SQL statement correctly accomplishes this?

Accepted Answer

The standard SQL syntax to retrieve all columns from a table with a filter condition is: SELECT * FROM TableName WHERE condition. The asterisk (*) represents all columns. GET, FETCH, and SELECT ALL are not valid standard SQL DML statements for this purpose.

Question 12

Your organization has a complex SQL Server on-premises workload that uses SQL Server Agent jobs, linked servers, and cross-database queries. You need to migrate to Azure with minimal code changes. Which Azure SQL service provides the highest level of SQL Server compatibility for this migration?

Accepted Answer

Azure SQL Managed Instance provides near-100% compatibility with the SQL Server database engine, including features like SQL Server Agent, linked servers, cross-database queries, and Service Broker. Azure SQL Database (single database) does not support all SQL Server instance-scoped features. Azure Database for PostgreSQL is for a different database engine. Azure Cosmos DB is a NoSQL service.

Question 13

You are designing a relational database schema. A database administrator mentions that a view would be useful. Which of the following best describes a view in a relational database?

Accepted Answer

A view is a virtual table in a relational database defined by a SQL query. It does not store data physically; instead, it retrieves data from one or more base tables at query time. Views simplify complex queries, provide a layer of security (limiting column/row access), and present data in a customized format. An index speeds up queries but is not a view; a materialized view does store data physically.

Question 14

Your organization needs to store large binary files such as images, videos, and documents in Azure. These files will be accessed via URLs by a web application. Which Azure storage service is best suited for this requirement?

Accepted Answer

Azure Blob Storage is designed for storing large amounts of unstructured data such as images, videos, audio files, documents, and backups. Files are accessible via HTTP/HTTPS URLs. Azure Table Storage is for structured NoSQL key-value data. Azure Queue Storage is for message queuing. Azure SQL Database is a relational database service.

Question 15

You are building an application that needs to store user session data as key-value pairs with fast read and write operations. The data is schema-flexible and does not require complex relationships. Which Azure service is most appropriate?

Accepted Answer

Azure Table Storage is a NoSQL key-value store that provides fast, schema-flexible storage for structured non-relational data such as user session data, device information, and metadata. It uses a partition key and row key model for efficient lookups. Azure SQL Database requires a fixed schema. Azure Blob Storage is for unstructured binary data. Azure Data Lake Storage is optimized for big data analytics workloads.

Question 16

Your team is evaluating Azure Cosmos DB for a new application. Which two characteristics are true of Azure Cosmos DB? Choose 2.

Accepted Answer

Azure Cosmos DB is a globally distributed, multi-model NoSQL database that supports multiple APIs including Core (NoSQL), MongoDB, Cassandra, Gremlin (graph), and Table. It provides turnkey global distribution with multi-region reads and writes and low-latency guarantees. It is not relational, does not require a fixed schema, and is not limited to SQL.

Question 17

You are a data architect for a global e-commerce platform. You need to store product catalog data where each product can have a different set of attributes (electronics have voltage and wattage; clothing has size and color). The solution must support single-digit millisecond reads globally. Which Azure service should you recommend?

Accepted Answer

Azure Cosmos DB for NoSQL is ideal for product catalog scenarios requiring schema flexibility (different attributes per product type) and global low-latency reads (single-digit millisecond response times). It supports schema-free JSON documents and provides global distribution with read replicas in any Azure region. Azure SQL Database is relational with fixed schemas. Azure Table Storage lacks the global distribution and query richness. Azure Synapse Analytics is for analytical workloads, not low-latency operational reads.

Question 18

Your organization needs to provide shared file storage that can be mounted as a network drive on both Windows and Linux virtual machines. Which Azure storage service supports this requirement?

Accepted Answer

Azure File Storage provides fully managed cloud file shares that can be mounted as network drives using the SMB (Server Message Block) and NFS (Network File System) protocols on Windows, Linux, and macOS. This is the Azure equivalent of an on-premises file server. Azure Blob Storage is object storage; Azure Table Storage is key-value NoSQL; Azure Data Lake Storage Gen2 is optimized for analytics.

Question 19

Your organization wants to create a centralized analytics solution that combines structured data from Azure SQL Database and semi-structured data from Azure Data Lake Storage. The solution must support both SQL-based queries and big data processing. Which Azure service is most appropriate?

Accepted Answer

Azure Synapse Analytics is an integrated analytics service that combines data warehousing (Synapse SQL), big data processing (Apache Spark pools), and data integration (Synapse Pipelines). It can query data in Azure Data Lake Storage directly and integrates with Azure SQL Database. Azure Stream Analytics is for real-time streaming data. Azure Cosmos DB is a NoSQL operational store.

Question 20

Your team receives a continuous stream of IoT sensor readings and needs to analyze and detect anomalies in near-real time. Which Azure service is designed specifically for processing and analyzing streaming data?

Accepted Answer

Azure Stream Analytics is a real-time analytics service designed to process and analyze streaming data from sources like Azure Event Hubs, Azure IoT Hub, and Azure Blob Storage. It uses a SQL-like query language to filter, aggregate, and detect patterns in real-time data streams. Azure Data Factory is for batch ETL/ELT pipelines. Azure Synapse is primarily for batch analytics. Power BI is for visualization.

Question 21

Your organization is building a large-scale analytics solution. Which two Azure services are commonly used as analytical data stores in a modern data lakehouse or data warehouse architecture? Choose 2.

Accepted Answer

Azure Synapse Analytics is a comprehensive analytics platform combining data warehousing and big data capabilities. Microsoft Fabric is an all-in-one analytics platform that includes data engineering, data warehousing, real-time analytics, and Power BI capabilities in a unified SaaS offering. Azure Queue Storage is for message queuing. Azure Cache for Redis is an in-memory cache. Azure Active Directory is an identity service.

Question 22

You are a data engineer designing a data ingestion pipeline for a large-scale analytics solution. Data from multiple source systems needs to be extracted, transformed, and loaded into Azure Synapse Analytics on a scheduled basis. Which Azure service is most appropriate for orchestrating this ETL pipeline?

Accepted Answer

Azure Data Factory is a cloud-based data integration service for creating data-driven workflows (pipelines) to orchestrate and automate data movement and data transformation at scale. It supports ETL and ELT patterns and can connect to hundreds of data sources. It is the primary orchestration tool for batch data pipelines that load data into Azure Synapse Analytics. Azure Stream Analytics handles real-time streaming, not batch ETL.

Question 23

Your organization uses Microsoft Fabric to build a unified analytics solution. A business analyst wants to create interactive reports and dashboards that connect directly to a Fabric semantic model and refresh automatically. Which component of Microsoft Fabric should the analyst use?

Accepted Answer

Power BI is the visualization and reporting component of Microsoft Fabric. Business analysts use Power BI to create interactive reports, dashboards, and semantic models (formerly datasets) that connect to Fabric data sources. Power BI is deeply integrated into Fabric and provides self-service BI capabilities. Spark notebooks are for data engineering. Data Factory pipelines are for orchestration. Real-Time Intelligence is for streaming analytics.

Question 24

You are evaluating the difference between batch and streaming data processing. Your organization receives website clickstream events continuously throughout the day, and the business needs insights within 30 seconds of events occurring. Which processing approach should you use?

Accepted Answer

Stream processing is required when insights are needed within seconds of events occurring. Azure Stream Analytics processes continuous streams of real-time data with sub-second to second latency. Batch processing (hourly, nightly scheduled jobs) is suitable when latency of minutes to hours is acceptable. The requirement of 30-second insights from continuous clickstream events clearly requires stream processing.

Question 25

Your organization's business users want to explore sales data using interactive charts without writing code. They need to create bar charts, line graphs, and maps from data stored in Microsoft Fabric. Which capability of Power BI allows users to create these visualizations without writing DAX or SQL?

Accepted Answer

Power BI provides a drag-and-drop report canvas where business users can create interactive visualizations (bar charts, line graphs, maps, pie charts, tables, etc.) by dragging fields onto the canvas without writing code. Built-in visualization types cover most common business reporting needs. Power Query Dataflows are for data preparation, not visualization. Deployment pipelines are for ALM (Application Lifecycle Management) of Power BI content.

DP-900 — Azure Data Fundamentals