DP-300 practice questions 1–10

1. Question 1 · 354 in bank

   Select 3

   You have 20 Azure SQL databases provisioned by using the vCore purchasing model. You plan to create an Azure SQL Database elastic pool and add the 20 databases. Which three metrics should you use to size the elastic pool to meet the demands of your workload? Each correct answer presents part of the solution. NOTE: Each correct selection is worth one point.

   • Atotal size of all the databases
   • Bgeo-replication support
   • Cnumber of concurrently peaking databases * peak CPU utilization per database
   • Dmaximum number of concurrent sessions for all the databases
   • Etotal number of databases * average CPU utilization per database

   Show answer

   Answer: ACE

   Explanation

   D is not correct. The database sessions are not mentioned anywhere in the documentation for elastic pool cost-effectiveness calculations. Each database counts as one. The number of sessions within the databases doesn't matter.

   Source: Adapted from community discussion — pozdrotechno

   References

   • azure.microsoft.com documentation
   • Microsoft Learn
   • Microsoft Learn

   Next question

2. Question 2 · 354 in bank

   You are designing a streaming data solution that will ingest variable volumes of data. You need to ensure that you can change the partition count after creation. Which service should you use to ingest the data?

   • AAzure Event Hubs Standard
   • BAzure Stream Analytics
   • CAzure Data Factory
   • DAzure Event Hubs Dedicated

   Show answer

   Answer: D

   Explanation

   The number of partitions is specified at creation and must be between 1 and 32. The partition count isn't changeable in all tiers except the dedicated tier, so you should consider long-term scale when setting partition count. https://docs.microsoft.com/en-us/azure/event-hubs/event-hubs-faq

   Source: Adapted from community discussion — Oralinux

   References

   • Microsoft Learn
   • Microsoft Learn

   Next question

3. Question 3 · 354 in bank

   HOTSPOT - You are building a database in an Azure Synapse Analytics serverless SQL pool. You have data stored in Parquet files in an Azure Data Lake Storage Gen2 container. Records are structured as shown in the following sample.

   

   The records contain two applicants at most. You need to build a table that includes only the address fields. How should you complete the Transact-SQL statement? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point. Hot Area:

   

   This is an interactive task on the real exam — a drag-and-drop, hotspot or ordering question. There is nothing to select here, so the answer is shown below for reference.

   Show the answer figure

   Answer: See figure below

   Explanation

   Box 1: CREATE EXTERNAL TABLE - An external table points to data located in Hadoop, Azure Storage blob, or Azure Data Lake Storage. External tables are used to read data from files or write data to files in Azure Storage. With Synapse SQL, you can use external tables to read external data using dedicated SQL pool or serverless SQL pool. Syntax: CREATE EXTERNAL TABLE { database_name.schema_name.table_name | schema_name.table_name | table_name } ( <column_definition> [ ,...n ] ) WITH ( LOCATION = 'folder_or_filepath', DATA_SOURCE = external_data_source_name, FILE_FORMAT = external_file_format_name Box 2. OPENROWSET - When using serverless SQL pool, CETAS is used to create an external table and export query results to Azure Storage Blob or Azure Data Lake Storage Gen2. Example: AS - SELECT decennialTime, stateName, SUM(population) AS population FROM - OPENROWSET(BULK 'https://azureopendatastorage.blob.core.windows.net/censusdatacontainer/release/us_population_county/year=*/*.parquet', FORMAT='PARQUET') AS [r] GROUP BY decennialTime, stateName GO - Reference: https://docs.microsoft.com/en-us/azure/synapse-analytics/sql/develop-tables-external-tables

   

   Next question

4. Question 4 · 354 in bank

   You have an Azure Synapse Analytics Apache Spark pool named Pool1. You plan to load JSON files from an Azure Data Lake Storage Gen2 container into the tables in Pool1. The structure and data types vary by file. You need to load the files into the tables. The solution must maintain the source data types. What should you do?

   • ALoad the data by using PySpark.
   • BLoad the data by using the OPENROWSET Transact-SQL command in an Azure Synapse Analytics serverless SQL pool.
   • CUse a Get Metadata activity in Azure Data Factory.
   • DUse a Conditional Split transformation in an Azure Synapse data flow.

   Show answer

   Answer: A

   Explanation

   Synapse notebooks support four Apache Spark languages: PySpark (Python) Spark (Scala) Spark SQL - .NET Spark (C#) Note: Bring data to a notebook. You can load data from Azure Blob Storage, Azure Data Lake Store Gen 2, and SQL pool as shown in the code samples below. Read a CSV from Azure Data Lake Store Gen2 as a Spark DataFrame. from pyspark.sql import SparkSession from pyspark.sql.types import * account_name = "Your account name" container_name = "Your container name" relative_path = "Your path" adls_path = 'abfss://%s@%s.dfs.core.windows.net/%s' % (container_name, account_name, relative_path) df1 = spark.read.option('header', 'true') \ .option('delimiter', ',') \ .csv(adls_path + '/Testfile.csv') Reference: https://docs.microsoft.com/en-us/azure/synapse-analytics/spark/apache-spark-development-using-notebooks

   Source: Adapted from community discussion — Backy

   References

   • Microsoft Learn

   Next question

5. Question 5 · 354 in bank

   You are designing a date dimension table in an Azure Synapse Analytics dedicated SQL pool. The date dimension table will be used by all the fact tables. Which distribution type should you recommend to minimize data movement?

   • AHASH
   • BREPLICATE
   • CROUND_ROBIN

   Show answer

   Answer: B

   Explanation

   A replicated table has a full copy of the table available on every Compute node. Queries run fast on replicated tables since joins on replicated tables don't require data movement. Replication requires extra storage, though, and isn't practical for large tables. Incorrect Answers: C: A round-robin distributed table distributes table rows evenly across all distributions. The assignment of rows to distributions is random. Unlike hash-distributed tables, rows with equal values are not guaranteed to be assigned to the same distribution. As a result, the system sometimes needs to invoke a data movement operation to better organize your data before it can resolve a query. Reference: https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-tables-distribute

   Source: Adapted from community discussion — TheMCT

   References

   • Microsoft Learn

   Next question

6. Question 6 · 354 in bank

   HOTSPOT - From a website analytics system, you receive data extracts about user interactions such as downloads, link clicks, form submissions, and video plays. The data contains the following columns:

   

   You need to design a star schema to support analytical queries of the data. The star schema will contain four tables including a date dimension. To which table should you add each column? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point. Hot Area:

   

   This is an interactive task on the real exam — a drag-and-drop, hotspot or ordering question. There is nothing to select here, so the answer is shown below for reference.

   Show the answer figure

   Answer: See figure below

   Explanation

   Box 1: DimEvent - Box 2: DimChannel - Dimension tables describe business entities ג€" the things you model. Entities can include products, people, places, and concepts including time itself. The most consistent table you'll find in a star schema is a date dimension table. A dimension table contains a key column (or columns) that acts as a unique identifier, and descriptive columns. Box 3: FactEvents - Fact tables store observations or events, and can be sales orders, stock balances, exchange rates, temperatures, etc. Reference: https://docs.microsoft.com/en-us/power-bi/guidance/star-schema

   References

   • Microsoft Learn

   

   Next question

7. Question 7 · 354 in bank

   DRAG DROP - You plan to create a table in an Azure Synapse Analytics dedicated SQL pool. Data in the table will be retained for five years. Once a year, data that is older than five years will be deleted. You need to ensure that the data is distributed evenly across partitions. The solutions must minimize the amount of time required to delete old data. How should you complete the Transact-SQL statement? To answer, drag the appropriate values to the correct targets. Each value may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content. NOTE: Each correct selection is worth one point. Select and Place:

   

   This is an interactive task on the real exam — a drag-and-drop, hotspot or ordering question. There is nothing to select here, so the answer is shown below for reference.

   Show the answer figure

   Answer: See figure below

   Explanation

   Box 1: HASH - Box 2: OrderDateKey - In most cases, table partitions are created on a date column. A way to eliminate rollbacks is to use Metadata Only operations like partition switching for data management. For example, rather than execute a DELETE statement to delete all rows in a table where the order_date was in October of 2001, you could partition your data early. Then you can switch out the partition with data for an empty partition from another table. Reference: https://docs.microsoft.com/en-us/sql/t-sql/statements/create-table-azure-sql-data-warehouse https://docs.microsoft.com/en-us/azure/synapse-analytics/sql/best-practices-dedicated-sql-pool

   Source: Adapted from community discussion — U_C

   References

   • Microsoft Learn

   

   Next question

8. Question 8 · 354 in bank

   You have an Azure Synapse Analytics workspace named WS1 that contains an Apache Spark pool named Pool1. You plan to create a database named DB1 in Pool1. You need to ensure that when tables are created in DB1, the tables are available automatically as external tables to the built-in serverless SQL pool. Which format should you use for the tables in DB1?

   • AJSON
   • BCSV
   • CParquet
   • DORC

   Show answer

   Answer: C

   Explanation

   For each Spark external table based on Parquet or CSV and located in Azure Storage, an external table is created in a serverless SQL pool database. As such, you can shut down your Spark pools and still query Spark external tables from serverless SQL pool.

   Source: Adapted from community discussion — CaptainJameson

   Next question

9. Question 9 · 354 in bank

   You are designing an anomaly detection solution for streaming data from an Azure IoT hub. The solution must meet the following requirements: ✑ Send the output to an Azure Synapse. ✑ Identify spikes and dips in time series data. ✑ Minimize development and configuration effort. Which should you include in the solution?

   • AAzure SQL Database
   • BAzure Databricks
   • CAzure Stream Analytics

   Show answer

   Answer: C

   Explanation

   Anomalies can be identified by routing data via IoT Hub to a built-in ML model in Azure Stream Analytics Reference: https://docs.microsoft.com/en-us/learn/modules/data-anomaly-detection-using-azure-iot-hub/ https://docs.microsoft.com/en-us/azure/stream-analytics/azure-synapse-analytics-output

   Source: Adapted from community discussion — gt002

   Next question

10. Question 10 · 354 in bank

    You are creating a new notebook in Azure Databricks that will support R as the primary language but will also support Scala and SQL. Which switch should you use to switch between languages?

    • A\\[<language>]
    • B%<language>
    • C\\[<language>]
    • D@<language>

    Show answer

    Answer: B

    Explanation

    You can override the default language by specifying the language magic command %<language> at the beginning of a cell. The supported magic commands are: %python, %r, %scala, and %sql. Reference: https://docs.microsoft.com/en-us/azure/databricks/notebooks/notebooks-use

    Next question

Keyboard:J Kmove between questionsA–Dselect an answerRshow or hide the answer← →change page

DP-300 practice, question 1. 354 questions available.