DP-900 Identify considerations for relational data on Azure Practice Test 13 — 15 Questions

Question 1

A data warehouse team in Azure Synapse Analytics notices query performance degradation on a large fact table. The table is partitioned by date and has a clustered columnstore index. Which action is most likely to improve performance?

Choose an answer to begin — your selection is scored in the full session.

Accepted Answer

Reorganize the clustered columnstore index. Reorganizing the clustered columnstore index (option C) is the most likely action to improve performance because, over time, columnstore indexes can become fragmented due to data modifications (inserts, updates, deletes). Reorganizing the index physically recompresses the data into optimal rowgroups, removing deleted rows and merging small rowgroups, which directly improves query scan efficiency and reduces I/O.

Answer

Update statistics on the fact table

Answer

Drop and recreate the partition boundaries

Answer

Change the distribution to ROUND_ROBIN

Question 2

A company uses Azure SQL Database and wants to ensure that a specific query always uses a particular index. What should they do?

Accepted Answer

Use a query hint to force the index. Query hints, specifically the INDEX hint, allow you to force the query optimizer to use a particular index for a specific query. In Azure SQL Database, this is done by adding `OPTION (TABLE HINT (table_name, INDEX (index_name)))` to the query, overriding the optimizer's default index selection.

Answer

Update statistics on the table

Answer

Rebuild the index

Answer

Enable Query Store

Question 3

An organization stores sensitive customer data in Azure SQL Database. They need to encrypt the data at rest and ensure that only authorized applications can decrypt it. Which combination of features should they implement?

Accepted Answer

Transparent Data Encryption (TDE) and Always Encrypted. TDE encrypts the database at rest, and Always Encrypted ensures that only authorized applications with the column encryption key can decrypt sensitive columns. Option A is wrong because row-level security controls access but does not encrypt data. Option B is wrong because auditing does not control decryption. Option D is wrong because dynamic data masking obfuscates data but does not encrypt.

Answer

Row-Level Security (RLS) and Transparent Data Encryption (TDE)

Answer

Azure SQL Database Auditing and Transparent Data Encryption (TDE)

Answer

Dynamic Data Masking and Always Encrypted

Question 4

You are designing a solution to store relational data that requires support for graph relationships and JSON queries. Which Azure service should you choose?

Accepted Answer

Azure SQL Database. Azure SQL Database is the correct choice because it natively supports graph relationships through graph tables (NODE and EDGE tables) and JSON queries via built-in JSON functions like JSON_VALUE, JSON_QUERY, and OPENJSON. This makes it ideal for storing relational data that also needs to handle graph traversals and semi-structured JSON data without requiring a separate service.

Answer

Azure Cosmos DB

Answer

Azure Table Storage

Answer

Azure Database for PostgreSQL

Question 5

A company has an Azure SQL Database and needs to run a weekly data aggregation job that takes several hours. They want to minimize cost and avoid impacting production workload. Which approach should they use?

Accepted Answer

Create a read-only replica and run the aggregation on the replica. Creating a read-only replica allows the weekly aggregation job to run against a separate copy of the database without affecting the production workload. Since the replica is read-only, it incurs additional compute costs only during the aggregation window, and you can scale it down or stop it when not in use, minimizing overall cost.

Answer

Migrate the database to the Hyperscale service tier

Answer

Use Azure Elastic Jobs to run the aggregation during off-peak hours

Answer

Increase the DTU or vCore size of the database to handle the load

Question 6

You are designing a multi-tenant SaaS application using Azure SQL Database. Each tenant has its own database. You need to perform maintenance across all databases efficiently. Which feature should you use?

Accepted Answer

Elastic Jobs. (Elastic Jobs) is correct because Elastic Jobs allows executing T-SQL scripts across multiple databases in Azure SQL Database. Option A (Elastic pools) is for resource pooling and sharing, not for running scripts across databases. Option B (Failover groups) is for high availability and disaster recovery. Option C (SQL Server Agent) is not available in Azure SQL Database; it is available in on-premises SQL Server or SQL Server on Azure VMs.

Answer

Elastic pools

Answer

Failover groups

Answer

SQL Server Agent

Question 7

You need to migrate an on-premises SQL Server database to Azure SQL Managed Instance with minimal downtime. Which tool should you use?

Accepted Answer

Azure Database Migration Service (DMS). The correct tool for migrating an on-premises SQL Server database to Azure SQL Managed Instance with minimal downtime is Azure Database Migration Service (DMS). DMS supports online migrations with minimal downtime. Option A (Azure Data Factory) is primarily for data integration and orchestration, not optimized for online database migrations. Option B (SSIS) is an ETL tool, not designed for minimal downtime migration. Option C (BACPAC export/import) is an offline method that involves exporting the database to a .bacpac file and then importing, which requires downtime. Therefore, option D is correct.

Answer

Azure Data Factory

Answer

SQL Server Integration Services (SSIS)

Answer

BACPAC export and import

Question 8

Which TWO Azure services can be used to host a relational database that is compatible with SQL Server?

Accepted Answer

Azure SQL Database. Options A and D are correct. Azure SQL Database and Azure SQL Managed Instance both provide SQL Server compatibility. Option B (Azure Database for PostgreSQL) is wrong because it is PostgreSQL, not SQL Server. Option C (Azure Cosmos DB) is wrong because it is NoSQL. Option E (Azure Database for MySQL) is wrong because it is MySQL.

Answer

Azure Database for PostgreSQL

Answer

Azure Cosmos DB

Answer

Azure Database for MySQL

Question 9

Which THREE are benefits of using Azure SQL Database serverless compute tier?

Accepted Answer

Billing per second for compute usage. Azure SQL Database serverless compute tier offers billing per second for compute usage (B), auto-scaling compute based on workload (C), and auto-pause during periods of inactivity (D). These features provide cost savings and automatic scaling for intermittent workloads. However, serverless does not guarantee a 99.99% SLA (A) and is not ideal for high-throughput, latency-sensitive applications (E) due to potential cold starts.

Answer

Guaranteed high availability with 99.99% SLA

Answer

Ideal for high-throughput, latency-sensitive applications

Question 10

Which TWO are valid deployment options for Azure SQL?

Accepted Answer

Azure SQL Managed Instance. Options A and B are correct. Azure SQL Database and Azure SQL Managed Instance are the two main deployment options for Azure SQL. Options C, D, and E are incorrect: Azure Cosmos DB is a NoSQL database, Azure Database for MariaDB is a separate managed database service, and Azure Synapse Analytics Dedicated SQL Pool is a data warehousing service.

Answer

Azure Cosmos DB

Answer

Azure Database for MariaDB

Answer

Azure Synapse Analytics Dedicated SQL Pool

Question 11

Refer to the exhibit. You are reviewing an ARM template for an Azure SQL Database deployment. What is the maximum size (in GB) of the database?

Accepted Answer

250 GB. The ARM template specifies the 'maxSizeBytes' property as 268,435,456,000 bytes. Converting this to gigabytes (divide by 1024^3) yields exactly 250 GB. This is the maximum size of the database as defined in the template.

Answer

256 GB

Answer

500 GB

Answer

268 GB

Question 12

Refer to the exhibit. You create an external table in Azure SQL Database. Which data source is being used?

Accepted Answer

Azure Blob Storage. The exhibit shows an external table referencing a data source with the LOCATION set to 'https://mystorage.blob.core.windows.net/...', which is the endpoint for Azure Blob Storage. In Azure SQL Database, external tables are created over external data sources that point to Azure Blob Storage or Azure Data Lake Storage, but the URL format 'blob.core.windows.net' specifically indicates Azure Blob Storage. The CREATE EXTERNAL TABLE statement uses this data source to read data stored as files (e.g., CSV, Parquet) in the blob container.

Answer

Azure Files

Answer

Azure SQL Database

Answer

Azure Data Lake Storage Gen2

Question 13

Refer to the exhibit. This ARM template snippet is used to deploy which Azure resource?

Accepted Answer

Azure SQL Database server. The ARM template snippet includes properties such as 'serverName', 'administratorLogin', and 'administratorLoginPassword', which are specific to an Azure SQL Database server deployment. Option A is incorrect because an Azure Database for MySQL server uses different properties like 'mysqlVersion' and 'storageProfile'. Option B is incorrect because Azure SQL Managed Instance requires properties like 'vCores', 'storageSizeInGB', and 'licenseType'. Option D is incorrect because Azure Synapse Analytics workspace deployments use properties like 'sqlAdministratorLogin' and 'defaultDataLakeStorage' but with different structure.

Answer

Azure Database for MySQL server

Answer

Azure SQL Managed Instance

Answer

Azure Synapse Analytics workspace

Question 14

A company stores customer orders in an Azure SQL Database. They need to ensure that the database can automatically scale to handle peak loads without manual intervention. Which Azure feature should they use?

Accepted Answer

Enable the serverless compute tier. The serverless compute tier for Azure SQL Database automatically pauses and resumes the database based on compute usage, scaling compute resources on demand without manual intervention. This makes it ideal for handling unpredictable peak loads while minimizing costs during idle periods.

Answer

Purchase reserved capacity

Answer

Add a read replica

Answer

Configure an elastic pool

Question 15

A retail company uses Azure SQL Database to store inventory data. They notice excessive blocking and deadlocks during peak hours. Which design change would best reduce these issues?

Accepted Answer

Add appropriate indexes to reduce lock duration. Adding appropriate indexes reduces the number of rows scanned during queries, which shortens lock duration and lowers the chance of blocking and deadlocks. In Azure SQL Database, indexes help queries become more efficient by using seeks instead of scans, minimizing the time locks are held on resources.

Answer

Implement read replicas for reporting queries

Answer

Use the READ UNCOMMITTED isolation level

Answer

Scale up to a higher service objective