Advanced Practice Exam (60 Questions)
This advanced practice exam contains:
- Higher-difficulty questions
- More scenario-based questions
- Multi-answer questions
- Matching questions
- Fill-in-the-blank questions
- SQL and architecture concepts
- Azure service selection scenarios
Section 1 — Core Data Concepts
Question 1 (Scenario-Based)
A company stores customer survey responses in JSON format. Each survey can contain different fields depending on the survey type.
How should this data be classified?
A. Structured
B. Semi-structured
C. Unstructured
D. Transactional
✅ Answer: B — Semi-structured
Explanations
A. Incorrect
Structured data requires a rigid schema.
B. Correct
JSON is semi-structured because it contains flexible tagged fields.
C. Incorrect
Unstructured data has little or no organization.
D. Incorrect
Transactional refers to workload type, not structure.
Question 2 (Multi-Answer)
Which characteristics are associated with transactional workloads? (Choose TWO)
A. High concurrency
B. Historical aggregations
C. Fast insert/update operations
D. Large-scale reporting queries
✅ Answers: A and C
Explanations
A. Correct
Transactional systems support many simultaneous users.
B. Incorrect
Historical aggregations are analytical.
C. Correct
OLTP systems perform fast write operations.
D. Incorrect
Large reporting queries belong to analytics workloads.
Question 3 (Scenario-Based)
A database contains duplicated customer addresses across multiple tables. The database architect wants to reduce redundancy and improve consistency.
Which process should be used?
A. Partitioning
B. Normalization
C. Encryption
D. Replication
✅ Answer: B — Normalization
Explanations
A. Incorrect
Partitioning improves scalability.
B. Correct
Normalization reduces duplication.
C. Incorrect
Encryption secures data.
D. Incorrect
Replication copies data.
Question 4 (Single Answer)
Which SQL statement removes an existing table and all its data?
A. DELETE
B. REMOVE
C. DROP
D. ERASE
✅ Answer: C — DROP
Explanations
A. Incorrect
DELETE removes rows only.
B. Incorrect
REMOVE is not standard SQL.
C. Correct
DROP deletes the table structure and data.
D. Incorrect
ERASE is not standard SQL.
Question 5 (Matching)
Match the role to the responsibility.
| Role | Responsibility |
|---|---|
| 1. DBA | A. Creates dashboards |
| 2. Data Analyst | B. Maintains database performance |
| 3. Data Engineer | C. Builds data pipelines |
✅ Answers
- 1 → B
- 2 → A
- 3 → C
Question 6 (Scenario-Based)
A retail company needs a database for processing thousands of purchases per minute with guaranteed consistency.
Which workload type is MOST appropriate?
A. Analytical
B. Streaming
C. Transactional
D. Archival
✅ Answer: C — Transactional
Explanations
A. Incorrect
Analytical systems focus on reporting.
B. Incorrect
Streaming processes event flows.
C. Correct
Transactional systems support operational consistency and speed.
D. Incorrect
Archival systems store inactive data.
Question 7 (Fill in the Blank)
The SQL statement used to add new rows to a table is __________.
✅ Answer: INSERT
Question 8 (Multi-Answer)
Which file formats are commonly used in analytics workloads? (Choose TWO)
A. Parquet
B. ORC
C. BMP
D. EXE
✅ Answers: A and B
Explanations
A. Correct
Parquet is optimized for analytics.
B. Correct
ORC is another columnar analytics format.
C. Incorrect
BMP is an image format.
D. Incorrect
EXE is executable software.
Question 9 (Scenario-Based)
An organization wants to analyze 10 years of sales history for trends and forecasting.
Which workload type is BEST suited?
A. OLTP
B. Analytical
C. Streaming
D. Operational
✅ Answer: B — Analytical
Question 10 (Single Answer)
Which database object contains reusable SQL logic?
A. View
B. Index
C. Stored Procedure
D. Key
✅ Answer: C — Stored Procedure
Section 2 — Relational Data on Azure
Question 11 (Scenario-Based)
A company is migrating an on-premises SQL Server application that relies heavily on SQL Server Agent, cross-database queries, and instance-level features.
Which Azure service is MOST appropriate?
A. Azure SQL Database
B. Azure SQL Managed Instance
C. Azure Cosmos DB
D. Azure Blob Storage
✅ Answer: B — Azure SQL Managed Instance
Explanations
A. Incorrect
Azure SQL Database has fewer instance-level features.
B. Correct
Managed Instance offers near full SQL Server compatibility.
C. Incorrect
Cosmos DB is NoSQL.
D. Incorrect
Blob Storage stores files.
Question 12 (Single Answer)
Which Azure SQL offering provides the HIGHEST level of infrastructure control?
A. Azure SQL Database
B. Azure SQL Managed Instance
C. SQL Server on Azure Virtual Machines
D. Azure Synapse Analytics
✅ Answer: C — SQL Server on Azure Virtual Machines
Question 13 (Multi-Answer)
Which are advantages of Platform as a Service (PaaS) databases? (Choose TWO)
A. Automatic patching
B. Reduced administrative overhead
C. Full operating system control
D. Manual backups only
✅ Answers: A and B
Question 14 (Scenario-Based)
A company wants automatic scaling, backups, and minimal management overhead for a new cloud-native application.
Which solution is BEST?
A. SQL Server on Azure VMs
B. Azure SQL Database
C. Windows Server Failover Cluster
D. Self-hosted SQL Server
✅ Answer: B — Azure SQL Database
Question 15 (Single Answer)
What is the purpose of a foreign key?
A. Encrypt data
B. Create indexes
C. Enforce relationships between tables
D. Remove duplicates
✅ Answer: C — Enforce relationships between tables
Question 16 (Scenario-Based)
A company needs a managed PostgreSQL service in Azure.
Which service should be used?
A. Azure SQL Database
B. Azure Database for PostgreSQL
C. Azure Blob Storage
D. Azure Cosmos DB
✅ Answer: B — Azure Database for PostgreSQL
Question 17 (Single Answer)
Which normalization form removes transitive dependencies?
A. 1NF
B. 2NF
C. 3NF
D. 4NF
✅ Answer: C — 3NF
Question 18 (Multi-Answer)
Which SQL statements are Data Manipulation Language (DML)? (Choose TWO)
A. SELECT
B. INSERT
C. CREATE
D. DROP
✅ Answers: A and B
Question 19 (Scenario-Based)
A query needs to return ALL customers, including those without orders.
Which JOIN should be used?
A. INNER JOIN
B. CROSS JOIN
C. LEFT JOIN
D. SELF JOIN
✅ Answer: C — LEFT JOIN
Question 20 (Single Answer)
Which object improves query performance but does NOT store actual business data?
A. Table
B. View
C. Index
D. Row
✅ Answer: C — Index
Section 3 — Non-Relational Data
Question 21 (Scenario-Based)
A media company needs to store petabytes of video content at low cost.
Which Azure service is MOST appropriate?
A. Azure SQL Database
B. Azure Blob Storage
C. Azure Table Storage
D. Azure Cache for Redis
✅ Answer: B — Azure Blob Storage
Question 22 (Single Answer)
Which Azure Blob Storage tier is optimized for infrequently accessed data?
A. Premium
B. Hot
C. Cool
D. Archive
✅ Answer: C — Cool
Question 23 (Scenario-Based)
An organization needs cloud-hosted SMB file shares accessible by both cloud and on-premises servers.
Which service should be used?
A. Azure Cosmos DB
B. Azure Files
C. Azure Table Storage
D. Azure SQL Database
✅ Answer: B — Azure Files
Question 24 (Multi-Answer)
Which APIs are supported by Azure Cosmos DB? (Choose TWO)
A. MongoDB
B. Cassandra
C. Oracle
D. SMB
✅ Answers: A and B
Question 25 (Scenario-Based)
A gaming company needs globally distributed low-latency data access for player profiles.
Which Azure service is BEST?
A. Azure Cosmos DB
B. Azure Files
C. Azure SQL Database
D. Azure Blob Storage
✅ Answer: A — Azure Cosmos DB
Question 26 (Single Answer)
What is a major benefit of Azure Cosmos DB partitioning?
A. Reduces security
B. Enables scalability
C. Removes replication
D. Prevents indexing
✅ Answer: B — Enables scalability
Question 27 (Fill in the Blank)
Azure Cosmos DB provides multi-region __________ to improve availability and performance.
✅ Answer: replication
Question 28 (Scenario-Based)
A company needs a NoSQL key-value store for massive telemetry ingestion.
Which service is MOST appropriate?
A. Azure Table Storage
B. Azure SQL Database
C. Azure Files
D. Azure DNS
✅ Answer: A — Azure Table Storage
Question 29 (Single Answer)
Which storage service stores data as objects inside containers?
A. Azure Files
B. Azure Blob Storage
C. Azure SQL Database
D. Azure Cosmos DB
✅ Answer: B — Azure Blob Storage
Question 30 (Multi-Answer)
Which are characteristics of non-relational databases? (Choose TWO)
A. Flexible schemas
B. Strict relational constraints
C. Horizontal scalability
D. Mandatory JOIN operations
✅ Answers: A and C
Section 4 — Analytics Workloads
Question 31 (Scenario-Based)
A company collects IoT sensor readings every second and needs near real-time dashboards.
Which processing approach is MOST appropriate?
A. Batch processing
B. Streaming processing
C. Archival processing
D. Offline reporting
✅ Answer: B — Streaming processing
Question 32 (Single Answer)
Which Azure service is designed for high-throughput event ingestion?
A. Azure Event Hubs
B. Azure Backup
C. Azure Files
D. Azure DNS
✅ Answer: A — Azure Event Hubs
Question 33 (Scenario-Based)
An organization needs Apache Spark-based analytics with collaborative notebooks.
Which service is BEST?
A. Azure Databricks
B. Azure Files
C. Azure DNS
D. Azure Firewall
✅ Answer: A — Azure Databricks
Question 34 (Single Answer)
Which architecture commonly includes fact tables and dimension tables?
A. OLTP schema
B. Star schema
C. Graph schema
D. XML schema
✅ Answer: B — Star schema
Question 35 (Multi-Answer)
Which are characteristics of a data warehouse? (Choose TWO)
A. Optimized for analytics
B. Stores historical data
C. Primarily supports OLTP transactions
D. Limited aggregations
✅ Answers: A and B
Question 36 (Scenario-Based)
A company wants a unified analytics platform combining engineering, warehousing, data science, and BI.
Which Microsoft service BEST fits?
A. Microsoft Fabric
B. Azure Files
C. Azure Firewall
D. Azure DNS
✅ Answer: A — Microsoft Fabric
Question 37 (Single Answer)
Which service allows SQL-like queries against streaming data?
A. Azure Stream Analytics
B. Azure Files
C. Azure Backup
D. Azure Monitor
✅ Answer: A — Azure Stream Analytics
Question 38 (Scenario-Based)
An organization processes payroll data once nightly.
Which processing type is MOST appropriate?
A. Streaming
B. Batch
C. Event-driven only
D. Real-time analytics
✅ Answer: B — Batch
Question 39 (Single Answer)
Which process extracts, transforms, and loads data into analytical systems?
A. ETL
B. DNS
C. RAID
D. OLTP
✅ Answer: A — ETL
Question 40 (Multi-Answer)
Which services are commonly associated with real-time analytics? (Choose TWO)
A. Azure Event Hubs
B. Azure Stream Analytics
C. Azure Files
D. Azure Backup
✅ Answers: A and B
Section 5 — Power BI
Question 41 (Scenario-Based)
An executive wants a single-page overview showing KPIs and summary visuals.
Which Power BI object should be used?
A. Dataset
B. Dashboard
C. Dataflow
D. Semantic model
✅ Answer: B — Dashboard
Question 42 (Single Answer)
Which Power BI component is primarily used for data transformation?
A. DAX
B. Power Query
C. Azure Functions
D. Power Automate
✅ Answer: B — Power Query
Question 43 (Scenario-Based)
A report must show revenue trends over 24 months.
Which visualization is BEST?
A. Pie chart
B. Gauge chart
C. Line chart
D. Scatter chart
✅ Answer: C — Line chart
Question 44 (Single Answer)
Which visualization is BEST for displaying proportions?
A. Scatter chart
B. Pie chart
C. Card
D. Gauge chart
✅ Answer: B — Pie chart
Question 45 (Scenario-Based)
A company wants users to filter reports interactively by region and year.
Which feature should be used?
A. Indexes
B. Slicers
C. Measures
D. Triggers
✅ Answer: B — Slicers
Question 46 (Single Answer)
Which Power BI language creates measures and calculated columns?
A. SQL
B. Python
C. DAX
D. XML
✅ Answer: C — DAX
Question 47 (Scenario-Based)
A business analyst wants to identify the relationship between advertising spend and revenue.
Which visualization is BEST?
A. Pie chart
B. Scatter chart
C. Gauge chart
D. Card
✅ Answer: B — Scatter chart
Question 48 (Single Answer)
Which Power BI visualization is BEST for detailed row-level data?
A. Table
B. Gauge
C. Pie chart
D. Card
✅ Answer: A — Table
Question 49 (Multi-Answer)
Which are benefits of Power BI dashboards? (Choose TWO)
A. Real-time monitoring
B. Single-page summaries
C. Operating system administration
D. SQL indexing
✅ Answers: A and B
Question 50 (Scenario-Based)
A company needs a geographic visualization of sales by country.
Which visualization is BEST?
A. Matrix
B. Map
C. Gauge
D. Card
✅ Answer: B — Map
Section 6 — Comprehensive Scenarios
Question 51 (Scenario-Based)
A healthcare organization requires:
- Globally distributed NoSQL storage
- Automatic replication
- Low latency worldwide
- Flexible schema support
Which solution BEST fits?
A. Azure SQL Database
B. Azure Cosmos DB
C. Azure Files
D. Azure Synapse Analytics
✅ Answer: B — Azure Cosmos DB
Question 52 (Scenario-Based)
A manufacturing company collects sensor telemetry every second from thousands of devices.
Which Azure service should ingest the streaming events?
A. Azure Event Hubs
B. Azure Files
C. Azure SQL Managed Instance
D. Azure Backup
✅ Answer: A — Azure Event Hubs
Question 53 (Scenario-Based)
A company wants full control of SQL Server patching, OS configuration, and backups.
Which deployment option should be used?
A. Azure SQL Database
B. Azure SQL Managed Instance
C. SQL Server on Azure Virtual Machines
D. Azure Cosmos DB
✅ Answer: C — SQL Server on Azure Virtual Machines
Question 54 (Single Answer)
Which Azure service is MOST optimized for unstructured object storage?
A. Azure Blob Storage
B. Azure SQL Database
C. Azure Files
D. Azure Synapse Analytics
✅ Answer: A — Azure Blob Storage
Question 55 (Scenario-Based)
An analytics team needs to store historical sales data optimized for aggregation queries.
Which solution is BEST?
A. Transactional database
B. Data warehouse
C. Azure Files
D. DNS server
✅ Answer: B — Data warehouse
Question 56 (Single Answer)
Which SQL statement changes existing records?
A. CREATE
B. UPDATE
C. INSERT
D. ALTER
✅ Answer: B — UPDATE
Question 57 (Multi-Answer)
Which are benefits of normalization? (Choose TWO)
A. Reduced redundancy
B. Improved consistency
C. Increased duplicate storage
D. Reduced relationships
✅ Answers: A and B
Question 58 (Scenario-Based)
A report needs to compare revenue across product categories.
Which visualization is BEST?
A. Line chart
B. Scatter chart
C. Bar chart
D. Gauge chart
✅ Answer: C — Bar chart
Question 59 (Fill in the Blank)
The SQL JOIN that returns only matching rows from both tables is called an __________ JOIN.
✅ Answer: INNER
Question 60 (Scenario-Based)
A company needs:
- Large-scale analytics
- Integrated Power BI reporting
- Data engineering
- Real-time analytics
- Unified SaaS experience
Which platform BEST meets these requirements?
A. Microsoft Fabric
B. Azure Files
C. Azure DNS
D. Windows Server Failover Clustering
✅ Answer: A — Microsoft Fabric
Advanced Exam Study Tips
Know the differences between:
- OLTP vs OLAP
- Batch vs streaming
- Structured vs semi-structured vs unstructured
- Relational vs NoSQL
Memorize Azure service associations:
| Service | Purpose |
|---|---|
| Azure Blob Storage | Unstructured object storage |
| Azure Files | SMB file shares |
| Azure Table Storage | Key-value NoSQL |
| Azure Cosmos DB | Globally distributed NoSQL |
| Azure Event Hubs | Streaming ingestion |
| Azure Stream Analytics | Real-time analytics |
| Azure Databricks | Spark analytics |
| Microsoft Fabric | Unified analytics platform |
Power BI visualization shortcuts:
| Visualization | Best Use |
|---|---|
| Line chart | Trends |
| Bar chart | Comparisons |
| Pie chart | Proportions |
| Scatter chart | Relationships |
| Card | Single KPI |
| Map | Geographic analysis |
| Gauge | Progress toward target |
Go to the DP-900 Exam Prep Hub main page.
The Data Community 
One thought on “DP-900: Azure Data Fundamentals – Advanced Practice Exam – 60 questions”