Data Engineering on Microsoft Azure practice questions

1. Question 1 of 25

   HOTSPOT - You have an Azure Synapse Analytics dedicated SQL pool that contains the users shown in the following table.

   

   User1 executes a query on the database, and the query returns the results shown in the following exhibit.

   

   User1 is the only user who has access to the unmasked data. Use the drop-down menus to select the answer choice that completes each statement based on the information presented in the graphic. 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

   Explanation

   Box 1: 0 - The YearlyIncome column is of the money data type. The Default masking function: Full masking according to the data types of the designated fields ✑ Use a zero value for numeric data types (bigint, bit, decimal, int, money, numeric, smallint, smallmoney, tinyint, float, real). Box 2: the values stored in the database Users with administrator privileges are always excluded from masking, and see the original data without any mask. Reference: https://docs.microsoft.com/en-us/azure/azure-sql/database/dynamic-data-masking-overview

   Adapted from community discussion by rjile · 2022-02-26 · 37 community upvotes. Third-party contribution; reviewed by our quality filter, not independently verified.

   Supporting references

   • Microsoft Learn
   • Microsoft Learn
   • Microsoft Learn
   • Microsoft Learn
   • Microsoft Learn

   Answer figure

   

   View community discussion4

   Candidate context

   Question 1 discussion

   These are quality-filtered excerpts from third-party community discussion. They can be useful reasoning signals, but they are not official answers and have not been independently verified by DP-203 or CertExamHQ.

   1. H

      hsetin

      2022-03-01

      113 upvotes

      user 1 is admin, so he will see the value stored in dbms. 1. 0 2. Value in database

   2. R

      rjile

      2022-02-26

      37 upvotes

      • Use a zero value for numeric data types (bigint, bit, decimal, int, money, numeric, smallint, smallmoney, tinyint, float, real). • Use 01-01-1900 for date/time data types (date, datetime2, datetime, datetimeoffset, smalldatetime, time).

   3. F

      fiuel

      2023-07-16

      11 upvotes

      Default masking rule is: For numeric data types use a zero value (bigint, bit, decimal, int, money, numeric, smallint, smallmoney, tinyint, float, real). https://learn.microsoft.com/en-us/sql/relational-databases/security/dynamic-data-masking?view=sql-server-ver16

   4. M

      Mahesh_mm

      2022-06-27

      9 upvotes

      1. 0 (Default values for money data type for masked function will written when queried by user2) 2. Value in database ( As it is queried by user1 who is admin )

2. Question 2 of 25

   You have an Azure Data Lake Storage Gen2 container that contains 100 TB of data. You need to ensure that the data in the container is available for read workloads in a secondary region if an outage occurs in the primary region. The solution must minimize costs. Which type of data redundancy should you use?

   • Ageo-redundant storage (GRS)
   • Bread-access geo-redundant storage (RA-GRS)
   • Czone-redundant storage (ZRS)
   • Dlocally-redundant storage (LRS)

   Show answer

   Correct answer: A

   How others answered

   • A61%
   • B39%

   Only 61% agreed on this answer — treat it as contested rather than settled.

   Explanation

   B is right Geo-redundant storage (with GRS or GZRS) replicates your data to another physical location in the secondary region to protect against regional outages. However, that data is available to be read only if the customer or Microsoft initiates a failover from the primary to secondary region. When you enable read access to the secondary region, your data is available to be read at all times, including in a situation where the primary region becomes unavailable.

   Adapted from community discussion by meetj · 2024-09-20 · 127 community upvotes. Third-party contribution; reviewed by our quality filter, not independently verified.

   Supporting references

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

   View community discussion4

   Candidate context

   Question 2 discussion

   These are quality-filtered excerpts from third-party community discussion. They can be useful reasoning signals, but they are not official answers and have not been independently verified by DP-203 or CertExamHQ.

   1. M

      meetj

      2024-09-20

      127 upvotes

      B is right Geo-redundant storage (with GRS or GZRS) replicates your data to another physical location in the secondary region to protect against regional outages. However, that data is available to be read only if the customer or Microsoft initiates a failover from the primary to secondary region. When you enable read access to the secondary region, your data is available to be read at all times, including in a situation where the primary region becomes unavailable.

   2. S

      Sasha_in_San_Francisco

      2024-09-20

      79 upvotes

      In my opinion, I believe the and answer is A, and this is why. In the question they state "...available for read workloads in a secondary region IF AN OUTAGE OCCURES in the primary...". Well, answer B (RA-GRS) states in Microsoft documentation that RA-GRS is for when "...your data is available to be read AT ALL TIMES, including in a situation where the primary region becomes unavailable." To me, the nature of the question is what is the cheapest solution which allows for failover to read workload, when there is an outage. Answer (A). Common sense would be 'A' too because that is probably the most often real-life use case.

   3. D

      dev2dev

      2022-01-08

      36 upvotes

      A looks correct answer. RA-GRS is always avialable because its auto failover. Since this is not asked in the question but more importantly the question is about reducing cost which GRS.

   4. B

      BK10

      2022-02-13

      32 upvotes

      It should be A because of two reasons: 1. Minimize cost 2. When primary is unavailable. Hence No need for RA_GRS

3. Question 3 of 25

   HOTSPOT - You have a SQL pool in Azure Synapse. You plan to load data from Azure Blob storage to a staging table. Approximately 1 million rows of data will be loaded daily. The table will be truncated before each daily load. You need to create the staging table. The solution must minimize how long it takes to load the data to the staging table. How should you configure the table? 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

   Explanation

   Box 1: Hash - Hash-distributed tables improve query performance on large fact tables. They can have very large numbers of rows and still achieve high performance. Incorrect Answers: Round-robin tables are useful for improving loading speed. Box 2: Clustered columnstore - When creating partitions on clustered columnstore tables, it is important to consider how many rows belong to each partition. For optimal compression and performance of clustered columnstore tables, a minimum of 1 million rows per distribution and partition is needed. Box 3: Date - Table partitions enable you to divide your data into smaller groups of data. In most cases, table partitions are created on a date column. Partition switching can be used to quickly remove or replace a section of a table. Reference: https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-tables-partition https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-tables-distribute

   Adapted from community discussion by laszek · 2021-08-29 · 68 community upvotes. Third-party contribution; reviewed by our quality filter, not independently verified.

   Supporting references

   • Microsoft Learn
   • Microsoft Learn
   • Microsoft Learn
   • Microsoft Learn
   • Microsoft Learn

   Answer figure

   

   View community discussion4

   Candidate context

   Question 3 discussion

   These are quality-filtered excerpts from third-party community discussion. They can be useful reasoning signals, but they are not official answers and have not been independently verified by DP-203 or CertExamHQ.

   1. L

      laszek

      2021-08-29

      68 upvotes

      Round-robin - this is the simplest distribution model, not great for querying but fast to process Heap - no brainer when creating staging tables No partitions - this is a staging table, why add effort to partition, when truncated daily?

   2. S

      Sasha_in_San_Francisco

      2024-09-23

      17 upvotes

      Answer: Round-Robin (1), Heap (2), None (3). Within this doc: https://docs.microsoft.com/en-us/azure/synapse-analytics/sql/develop-tables-overview #1. Search for “Use round-robin for the staging table.” #2. Search for: “A heap table can be especially useful for loading data, such as a staging table,…” Within this doc: https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-tables-partition?context=/azure/synapse-analytics/context/context #3. Partitioning by date is useful when stage destination has data because you can hide the inserting data’s new partition (to keep users from hitting it), complete the load and then unhide the new partition. However, in this question it states, “the table will be truncated before each daily load”, so, it appears it’s a true Staging table and there are no users with access, no existing data, and I see no reason to have a Date partition. To me, such a partition would do nothing but slow the load.

   3. D

      DrTaz

      2022-01-02

      13 upvotes

      The term heap basically refers to a table without a clustered index. Adding a clustered index to a temp table makes absolutely no sense and is a waste of compute resources for a table that would be entirely truncated daily. no clustered index = heap.

   4. G

      gssd4scoder

      2021-10-25

      10 upvotes

      Agree 100%. All in paragraphs under this: https://docs.microsoft.com/en-us/azure/synapse-analytics/sql/develop-tables-overview.

4. Question 4 of 25

   You have an Azure Synapse workspace named MyWorkspace that contains an Apache Spark database named mytestdb. You run the following command in an Azure Synapse Analytics Spark pool in MyWorkspace. CREATE TABLE mytestdb.myParquetTable( EmployeeID int, EmployeeName string, EmployeeStartDate date) USING Parquet - You then use Spark to insert a row into mytestdb.myParquetTable. The row contains the following data.

   

   One minute later, you execute the following query from a serverless SQL pool in MyWorkspace. SELECT EmployeeID - FROM mytestdb.dbo.myParquetTable WHERE EmployeeName = 'Alice'; What will be returned by the query?

   • A24
   • Ban error
   • Ca null value

   Show answer

   Correct answer: A

   How others answered

   • A54%
   • B46%

   Only 54% agreed on this answer — treat it as contested rather than settled.

   Explanation

   I did a test, waited for one minute and tried the query in a serverless sql pool and received 24 as the result, so I don't understand that B has been voted so much because the answer is A) 24 without a doubt

   Adapted from community discussion by gerrie1979 · 2022-10-29 · 63 community upvotes. Third-party contribution; reviewed by our quality filter, not independently verified.

   Supporting references

   • Microsoft Learn
   • Microsoft Learn
   • Microsoft Learn
   • Microsoft Learn
   • Microsoft Learn

   View community discussion4

   Candidate context

   Question 4 discussion

   These are quality-filtered excerpts from third-party community discussion. They can be useful reasoning signals, but they are not official answers and have not been independently verified by DP-203 or CertExamHQ.

   1. G

      gerrie1979

      2022-10-29

      63 upvotes

      I did a test, waited for one minute and tried the query in a serverless sql pool and received 24 as the result, so I don't understand that B has been voted so much because the answer is A) 24 without a doubt

   2. D

      dmitriypo

      2022-10-19

      56 upvotes

      Answer is B, but not because of the lowercase. The case has nothing to do with the error. If you look attentively, you will notice that we create table mytestdb.myParquetTable, but the select statement contains the reference to table mytestdb.dbo.myParquetTable (!!! - dbo). Here is the error message I got: Error: spark_catalog requires a single-part namespace, but got [mytestdb, dbo].

   3. P

      psicktrick

      2023-01-06

      20 upvotes

      But if you look at the docs, that's exactly what has been done https://learn.microsoft.com/en-us/azure/synapse-analytics/metadata/table#expose-a-spark-table-in-sql:~:text=mytestdb.myparquettable%22)%3B-,Now%20you%20can%20read%20the%20data%20from%20your%20serverless%20SQL%20pool%20as%20follows%3A,-SQL

   4. V

      vinicius_cb

      2023-02-15

      17 upvotes

      Actually the error it is because of the lower case. "Table names will be converted to lower case and need to be queried using the lower case". There is nothing wrong with "dbo", since the docs shows this exact same example and contains the "dbo" in the query. Source: https://learn.microsoft.com/en-us/azure/synapse-analytics/metadata/table

5. Question 5 of 25

   DRAG DROP - You have a table named SalesFact in an enterprise data warehouse in Azure Synapse Analytics. SalesFact contains sales data from the past 36 months and has the following characteristics: ✑ Is partitioned by month ✑ Contains one billion rows ✑ Has clustered columnstore index At the beginning of each month, you need to remove data from SalesFact that is older than 36 months as quickly as possible. Which three actions should you perform in sequence in a stored procedure? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order. 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

   Explanation

   Step 1: Create an empty table named SalesFact_work that has the same schema as SalesFact. Step 2: Switch the partition containing the stale data from SalesFact to SalesFact_Work. SQL Data Warehouse supports partition splitting, merging, and switching. To switch partitions between two tables, you must ensure that the partitions align on their respective boundaries and that the table definitions match. Loading data into partitions with partition switching is a convenient way stage new data in a table that is not visible to users the switch in the new data. Step 3: Drop the SalesFact_Work table. Reference: https://docs.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-tables-partition

   Adapted from community discussion by vigilante89 · 2024-09-23 · 13 community upvotes. Third-party contribution; reviewed by our quality filter, not independently verified.

   Supporting references

   • Microsoft Learn
   • Microsoft Learn
   • Microsoft Learn
   • Microsoft Learn
   • Microsoft Learn

   Answer figure

   

   View community discussion4

   Candidate context

   Question 5 discussion

   These are quality-filtered excerpts from third-party community discussion. They can be useful reasoning signals, but they are not official answers and have not been independently verified by DP-203 or CertExamHQ.

   1. V

      vigilante89

      2024-09-23

      13 upvotes

      The answer should be D --> A --> C. Step 1: Create an empty table SalesFact_Work with same schema as SalesFact. Step 2: Switch the partition (to be removed) from SalesFact to SalesFact_Work. The syntax is: ALTER TABLE SWITCH PARTITION to Step 3: Delete the SalesFact_Work table.

   2. T

      the_frix

      2024-09-23

      5 upvotes

      "Partition switching can be used to quickly remove or replace a section of a table. For example, a sales fact table might contain just data for the past 36 months. At the end of every month, the oldest month of sales data is deleted from the table. This data could be deleted by using a delete statement to delete the data for the oldest month. However, deleting a large amount of data row-by-row with a delete statement can take too much time, as well as create the risk of large transactions that take a long time to rollback if something goes wrong. A more optimal approach is to drop the oldest partition of data. Where deleting the individual rows could take hours, deleting an entire partition could take seconds." https://learn.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-tables-partition#benefits-to-loads

   3. A

      alain2

      2021-05-18

      6 upvotes

      Yes, it is. https://www.cathrinewilhelmsen.net/table-partitioning-in-sql-server-partition-switching/

   4. V

      vrodriguesp

      2024-09-23

      4 upvotes

      Given answer is correct: Step 1: Create an empty table SalesFact_Work with same schema as SalesFact (that it will contains reocords older than 3 years) Step 2: Switch the partition from SalesFact to SalesFact_Work. SO we're only doing metadata operations Step 3: Delete the SalesFact_Work table containing stale data and we're not losing any time or blocking target table

6. Question 6 of 25

   You have files and folders in Azure Data Lake Storage Gen2 for an Azure Synapse workspace as shown in the following exhibit.

   

   You create an external table named ExtTable that has LOCATION='/topfolder/'. When you query ExtTable by using an Azure Synapse Analytics serverless SQL pool, which files are returned?

   • AFile2.csv and File3.csv only
   • BFile1.csv and File4.csv only
   • CFile1.csv, File2.csv, File3.csv, and File4.csv
   • DFile1.csv only

   Show answer

   Correct answer: B

   How others answered

   • B72%
   • C25%

   Only 72% agreed on this answer — treat it as contested rather than settled.

   Explanation

   I believe the answer should be B. In case of a serverless pool a wildcard should be added to the location. https://docs.microsoft.com/en-us/azure/synapse-analytics/sql/develop-tables-external-tables?tabs=hadoop#arguments-create-external-table

   Adapted from community discussion by Chillem1900 · 2024-09-20 · 124 community upvotes. Third-party contribution; reviewed by our quality filter, not independently verified.

   Supporting references

   • Microsoft Learn
   • Microsoft Learn
   • Microsoft Learn
   • Microsoft Learn
   • Microsoft Learn

   View community discussion4

   Candidate context

   Question 6 discussion

   These are quality-filtered excerpts from third-party community discussion. They can be useful reasoning signals, but they are not official answers and have not been independently verified by DP-203 or CertExamHQ.

   1. C

      Chillem1900

      2024-09-20

      124 upvotes

      I believe the answer should be B. In case of a serverless pool a wildcard should be added to the location. https://docs.microsoft.com/en-us/azure/synapse-analytics/sql/develop-tables-external-tables?tabs=hadoop#arguments-create-external-table

   2. C

      captainpike

      2021-10-17

      41 upvotes

      I tested and proove you right, the answer is B. Remind the question is referring to serverless SQL and not dedicated SQL pool. "Unlike Hadoop external tables, native external tables don't return subfolders unless you specify /** at the end of path. In this example, if LOCATION='/webdata/', a serverless SQL pool query, will return rows from mydata.txt. It won't return mydata2.txt and mydata3.txt because they're located in a subfolder. Hadoop tables will return all files within any subfolder."

   3. A

      alain2

      2024-09-20

      31 upvotes

      "Serverless SQL pool can recursively traverse folders only if you specify /** at the end of path." https://docs.microsoft.com/en-us/azure/synapse-analytics/sql/query-folders-multiple-csv-files

   4. P

      Preben

      2021-06-07

      22 upvotes

      When you are quoting from Microsoft documentation, do not ADD in words to the sentence. 'Only' is not used.

7. Question 7 of 25

   You are implementing a batch dataset in the Parquet format. Data files will be produced be using Azure Data Factory and stored in Azure Data Lake Storage Gen2. The files will be consumed by an Azure Synapse Analytics serverless SQL pool. You need to minimize storage costs for the solution. What should you do?

   • AUse Snappy compression for the files.
   • BUse OPENROWSET to query the Parquet files.
   • CCreate an external table that contains a subset of columns from the Parquet files.
   • DStore all data as string in the Parquet files.

   Show answer

   Correct answer: A

   How others answered

   • A68%
   • C23%

   Only 68% agreed on this answer — treat it as contested rather than settled.

   Explanation

   A. Use Snappy compression for the files. The most effective way to minimize storage costs for the solution is to use compression. Parquet files can be compressed using a variety of codecs, including Snappy, which provides a good balance between compression ratio and query performance. By compressing the data, the file size is reduced, which results in lower storage costs. Option B, using OPENROWSET to query the Parquet files, is a method to query the data, but it does not address storage costs. Option C, creating an external table that contains a subset of columns from the Parquet files, is a useful optimization to reduce the amount of data scanned and therefore reduce query cost, but it does not address storage costs. Option D, storing all data as strings in the Parquet files, is not a good approach because it would result in larger file sizes and potentially slower query performance due to the need to convert the data back to its original format. Answer is A

   Adapted from community discussion by rocky48 · 2023-04-30 · 14 community upvotes. Third-party contribution; reviewed by our quality filter, not independently verified.

   Supporting references

   • Microsoft Learn
   • Microsoft Learn
   • Microsoft Learn
   • Microsoft Learn
   • Microsoft Learn

   View community discussion4

   Candidate context

   Question 7 discussion

   These are quality-filtered excerpts from third-party community discussion. They can be useful reasoning signals, but they are not official answers and have not been independently verified by DP-203 or CertExamHQ.

   1. M

      m2shines

      2021-12-14

      75 upvotes

      Answer should be A, because this talks about minimizing storage costs, not querying costs

   2. A

      assU2

      2022-01-11

      24 upvotes

      Isn't snappy a default compressionCodec for parquet in azure? https://docs.microsoft.com/en-us/azure/data-factory/format-parquet

   3. A

      Aslam208

      2021-12-11

      23 upvotes

      C is the correct answer, as an external table with a subset of columns with parquet files would be cost-effective.

   4. R

      rocky48

      2023-04-30

      Selected A14 upvotes

      A. Use Snappy compression for the files. The most effective way to minimize storage costs for the solution is to use compression. Parquet files can be compressed using a variety of codecs, including Snappy, which provides a good balance between compression ratio and query performance. By compressing the data, the file size is reduced, which results in lower storage costs. Option B, using OPENROWSET to query the Parquet files, is a method to query the data, but it does not address storage costs. Option C, creating an external table that contains a subset of columns from the Parquet files, is a useful optimization to reduce the amount of data scanned and therefore reduce query cost, but it does not address storage costs. Option D, storing all data as strings in the Parquet files, is not a good approach because it would result in larger file sizes and potentially slower query performance due to the need to convert the data back to its original format. Answer is A

8. Question 8 of 25

   DRAG DROP - You have data stored in thousands of CSV files in Azure Data Lake Storage Gen2. Each file has a header row followed by a properly formatted carriage return (/ r) and line feed (/n). You are implementing a pattern that batch loads the files daily into a dedicated SQL pool in Azure Synapse Analytics by using PolyBase. You need to skip the header row when you import the files into the data warehouse. Before building the loading pattern, you need to prepare the required database objects in Azure Synapse Analytics. Which three actions should you perform in sequence? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order. 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

   Explanation

   Step 1: Create an external data source that uses the abfs location Create External Data Source to reference Azure Data Lake Store Gen 1 or 2 Step 2: Create an external file format and set the First_Row option. Create External File Format. Step 3: Use CREATE EXTERNAL TABLE AS SELECT (CETAS) and configure the reject options to specify reject values or percentages To use PolyBase, you must create external tables to reference your external data. Use reject options. Note: REJECT options don't apply at the time this CREATE EXTERNAL TABLE AS SELECT statement is run. Instead, they're specified here so that the database can use them at a later time when it imports data from the external table. Later, when the CREATE TABLE AS SELECT statement selects data from the external table, the database will use the reject options to determine the number or percentage of rows that can fail to import before it stops the import. Reference: https://docs.microsoft.com/en-us/sql/relational-databases/polybase/polybase-t-sql-objects https://docs.microsoft.com/en-us/sql/t-sql/statements/create-external-table-as-select-transact-sql

   Adapted from community discussion by sunil_smile · 2023-03-18 · 39 community upvotes. Third-party contribution; reviewed by our quality filter, not independently verified.

   Supporting references

   • Microsoft Learn
   • Microsoft Learn
   • Microsoft Learn
   • Microsoft Learn
   • Microsoft Learn

   Answer figure

   

   View community discussion4

   Candidate context

   Question 8 discussion

   These are quality-filtered excerpts from third-party community discussion. They can be useful reasoning signals, but they are not official answers and have not been independently verified by DP-203 or CertExamHQ.

   1. S

      sunil_smile

      2023-03-18

      39 upvotes

      1) create database scoped credentials 2) create external source 3) create file format 4) create external table (it not supports CTAS)

   2. O

      OldSchool

      2023-05-19

      19 upvotes

      Because it's saying "You have data stored in thousands of CSV files in Azure Data Lake Storage Gen2" and "You are implementing a pattern that batch loads the files daily into a dedicated SQL pool in Azure Synapse Analytics by using PolyBase" assumption is that we already have database credentials, so the answer is: 1) create external source 2) create file format 3) create external table

   3. K

      kl8585

      2023-05-11

      9 upvotes

      I have many doubts on this question. Two points on which i focused: 1) The question states "Before building the loading pattern, you need to prepare the required database objects", so it doesn't seem to fit with CETAS statement because is asking about actions to do BEFORE implementing the loading pattern 2) With PolyBase you don't use CETAS statement, you should use CET with LOCATION option to create external table from Data Lake Given these points, i will go with: - Create database scoped credentials - Create external data source - Create file format I will appreciate anyone confirming or confuting this answer. Thank you!

   4. A

      ankeshpatel2112

      2024-09-30

      9 upvotes

      The correct answer are : 1) create database scoped credentials 2) create external source 3) create file format Why ? : Focus on this below statement in question. Before building the loading pattern, you need to prepare the required database objects in Azure Synapse Analytics. Explanation: You just need to specify about prerequisite database object ( Not Loading pattern which is about Creating External Table )

9. Question 9 of 25

   HOTSPOT - You have the following Azure Stream Analytics query.

   

   For each of the following statements, select Yes if the statement is true. Otherwise, select No. 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

   Explanation

   Box 1: No - Note: You can now use a new extension of Azure Stream Analytics SQL to specify the number of partitions of a stream when reshuffling the data. The outcome is a stream that has the same partition scheme. Please see below for an example: WITH step1 AS (SELECT * FROM [input1] PARTITION BY DeviceID INTO 10), step2 AS (SELECT * FROM [input2] PARTITION BY DeviceID INTO 10) SELECT * INTO [output] FROM step1 PARTITION BY DeviceID UNION step2 PARTITION BY DeviceID Note: The new extension of Azure Stream Analytics SQL includes a keyword INTO that allows you to specify the number of partitions for a stream when performing reshuffling using a PARTITION BY statement. Box 2: Yes - When joining two streams of data explicitly repartitioned, these streams must have the same partition key and partition count. Box 3: Yes - Streaming Units (SUs) represents the computing resources that are allocated to execute a Stream Analytics job. The higher the number of SUs, the more CPU and memory resources are allocated for your job. In general, the best practice is to start with 6 SUs for queries that don't use PARTITION BY. Here there are 10 partitions, so 6x10 = 60 SUs is good. Note: Remember, Streaming Unit (SU) count, which is the unit of scale for Azure Stream Analytics, must be adjusted so the number of physical resources available to the job can fit the partitioned flow. In general, six SUs is a good number to assign to each partition. In case there are insufficient resources assigned to the job, the system will only apply the repartition if it benefits the job. Reference: https://azure.microsoft.com/en-in/blog/maximize-throughput-with-repartitioning-in-azure-stream-analytics/ https://docs.microsoft.com/en-us/azure/stream-analytics/stream-analytics-streaming-unit-consumption

   Adapted from community discussion by objecto · 2022-06-12 · 40 community upvotes. Third-party contribution; reviewed by our quality filter, not independently verified.

   Supporting references

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

   Answer figure

   

   View community discussion4

   Candidate context

   Question 9 discussion

   These are quality-filtered excerpts from third-party community discussion. They can be useful reasoning signals, but they are not official answers and have not been independently verified by DP-203 or CertExamHQ.

   1. O

      objecto

      2022-06-12

      40 upvotes

      I feel its all YES. Since it does use a UNION and UNION combines. No matter it repartitions the result is the combination of two sources, a UNION of two sources. Am I missing something here?

   2. A

      auwia

      2023-06-27

      26 upvotes

      False, True, False. https://learn.microsoft.com/en-us/azure/stream-analytics/repartition The first is False, because this: "The following example query joins two streams of repartitioned data." It's extracted from the link above, and it's pointing to our query! Repartitioned and not partitioned. Second is True, it's explicitly written The output scheme should match the stream scheme key and count so that each substream can be flushed independently. Third is False, "In general, six SUs are needed for each partition." In the example we have 10 positions for step 1 and 10 for step 2, it should be 120 and not 60.

   3. M

      Momoanwar

      2023-12-06

      8 upvotes

      Chatgpt say : yes tes no. Based on the provided information and additional documentation on Azure Stream Analytics: 1. **Yes**: The query combines two streams of partitioned data [[❞]](https://azure.microsoft.com/fr-fr/blog/maximize-throughput-with-repartitioning-in-azure-stream-analytics/). 2. **Yes**: The stream scheme key and count should match the output scheme for optimal independent processing of each substream [[❞]](https://azure.microsoft.com/fr-fr/blog/maximize-throughput-with-repartitioning-in-azure-stream-analytics/). 3. **No**: The documentation does not specify that providing 60 streaming units will optimize the query's performance. The appropriate number of streaming units depends on experimentation and resource usage observation [[❞]](https://azure.microsoft.com/fr-fr/blog/maximize-throughput-with-repartitioning-in-azure-stream-analytics/).

   4. R

      rohanb1986

      2022-12-26

      5 upvotes

      Is the first option NO because it mentions partitioned data instead of repartitioned data? Reference : https://learn.microsoft.com/en-us/azure/stream-analytics/repartition The following example query joins two streams of repartitioned data. When joining two streams of repartitioned data, the streams must have the same partition key and count. The outcome is a stream that has the same partition scheme. WITH step1 AS (SELECT * FROM input1 PARTITION BY DeviceID), step2 AS (SELECT * FROM input2 PARTITION BY DeviceID) SELECT * INTO output FROM step1 PARTITION BY DeviceID UNION step2 PARTITION BY DeviceID

10. Question 10 of 25

    HOTSPOT - You have an Azure Synapse Analytics dedicated SQL pool named Pool1 and an Azure Data Lake Storage Gen2 account named Account1. You plan to access the files in Account1 by using an external table. You need to create a data source in Pool1 that you can reference when you create the external table. 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

    Explanation

    Box 1: blob - The following example creates an external data source for Azure Data Lake Gen2 CREATE EXTERNAL DATA SOURCE YellowTaxi WITH ( LOCATION = 'https://azureopendatastorage.blob.core.windows.net/nyctlc/yellow/', TYPE = HADOOP) Box 2: HADOOP - Reference: https://docs.microsoft.com/en-us/azure/synapse-analytics/sql/develop-tables-external-tables

    Adapted from community discussion by Kure87 · 2022-11-23 · 39 community upvotes. Third-party contribution; reviewed by our quality filter, not independently verified.

    Supporting references

    • Microsoft Learn
    • Microsoft Learn
    • Microsoft Learn
    • Microsoft Learn
    • Microsoft Learn

    Answer figure

    

    View community discussion4

    Candidate context

    Question 10 discussion

    These are quality-filtered excerpts from third-party community discussion. They can be useful reasoning signals, but they are not official answers and have not been independently verified by DP-203 or CertExamHQ.

    1. K

       Kure87

       2022-11-23

       39 upvotes

       1. blob. Acoording with this article https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/develop-tables-external-tables?tabs=hadoop we only use DFS (abfss endpoint) when your account has secure transfer enabled. On the question the location starts with "https://account1." not "abfss://"

    2. L

       LetsPassExams

       2022-05-06

       14 upvotes

       From https://docs.microsoft.com/en-us/azure/synapse-analytics/sql/develop-tables-external-tables?tabs=hadoop#example-for-create-external-data-source The following example creates an external data source for Azure Data Lake Gen2 pointing to the publicly available New York data set: SQL Copy CREATE EXTERNAL DATA SOURCE YellowTaxi WITH ( LOCATION = 'https://azureopendatastorage.blob.core.windows.net/nyctlc/yellow/', TYPE = HADOOP)

    3. D

       Deeksha1234

       2022-07-28

       9 upvotes

       blob and Hadoop is correct, since data source is being created without credentials The following example creates an external data source for Azure Data Lake Gen2 pointing to the publicly available New York data set: SQL CREATE EXTERNAL DATA SOURCE YellowTaxi WITH ( LOCATION = 'https://azureopendatastorage.blob.core.windows.net/nyctlc/yellow/', TYPE = HADOOP) ref- https://docs.microsoft.com/en-us/azure/synapse-analytics/sql/develop-tables-external-tables?tabs=hadoop

    4. V

       Vedjha

       2023-01-31

       9 upvotes

       CREATE EXTERNAL DATA SOURCE mydatasource WITH ( LOCATION = 'abfss://[email protected]', CREDENTIAL = AzureStorageCredential, TYPE = HADOOP )

11. Question 11 of 25

    HOTSPOT - You plan to create an Azure Data Lake Storage Gen2 account. You need to recommend a storage solution that meets the following requirements: ✑ Provides the highest degree of data resiliency ✑ Ensures that content remains available for writes if a primary data center fails What should you include in the recommendation? 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

    Explanation

    Reference: https://docs.microsoft.com/en-us/azure/storage/common/storage-disaster-recovery-guidance?toc=/azure/storage/blobs/toc.json https://docs.microsoft.com/en-us/answers/questions/32583/azure-data-lake-gen2-disaster-recoverystorage-acco.html

    Adapted from community discussion by aniagnesighile1 · 2022-11-08 · 81 community upvotes. Third-party contribution; reviewed by our quality filter, not independently verified.

    Supporting references

    • Microsoft Learn
    • Microsoft Learn
    • Microsoft Learn
    • Microsoft Learn
    • Microsoft Learn

    Answer figure

    

    View community discussion4

    Candidate context

    Question 11 discussion

    These are quality-filtered excerpts from third-party community discussion. They can be useful reasoning signals, but they are not official answers and have not been independently verified by DP-203 or CertExamHQ.

    1. A

       aniagnesighile1

       2022-11-08

       81 upvotes

       I am surprised you all missed this requirement 'Ensures that content remains available for writes if a primary data center fails'. RA-GRS and RAGZRS provide read access only after failover. The correct answer is ZRS as t=stated in the link below "Microsoft recommends using ZRS in the primary region for Azure Data Lake Storage Gen2 workloads." https://learn.microsoft.com/en-us/azure/storage/common/storage-redundancy?toc=%2Fazure%2Fstorage%2Fblobs%2Ftoc.json

    2. C

       chinomoreno

       2022-09-05

       26 upvotes

       Failover initiated by Microsoft. Customer-managed account failover is not yet supported in accounts that have a hierarchical namespace (Azure Data Lake Storage Gen2). To learn more, see Blob storage features available in Azure Data Lake Storage Gen2.

    3. S

       SomethingRight100

       2022-11-22

       15 upvotes

       Reading from the following, I do not think RAGZRS is ready only. I think it is read-only for the second region. GZRS writes three copies of your data synchronously across multiple Azure Availability zones, similar to zone-redundant storage (ZRS), providing you continued read and write access even if a datacenter or availability zone is unavailable. In addition, GZRS asynchronously replicates your data to the secondary geo-pair region to protect against regional unavailability. RA-GZRS exposes a read endpoint on this secondary replica allowing you to read data in the event of primary region unavailability.

    4. V

       vctrhugo

       2023-06-24

       12 upvotes

       You can still write data to second region if first one fails. RA only allows you to read data in second region even if the first does not fail.

12. Question 12 of 25

    HOTSPOT - You have an Azure Data Lake Storage Gen2 account that contains a container named container1. You have an Azure Synapse Analytics serverless SQL pool that contains a native external table named dbo.Table1. The source data for dbo.Table1 is stored in container1. The folder structure of container1 is shown in the following exhibit.

    

    The external data source is defined by using the following statement.

    

    For each of the following statements, select Yes if the statement is true. Otherwise, select No. NOTE: Each correct selection is worth one point.

    

    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

    Explanation

    1.Yes, 2.Yes: "Unlike Hadoop external tables, native external tables don't return subfolders unless you specify /** at the end of path" which is the case here. 3. No: "Both Hadoop and native external tables will skip the files with the names that begin with an underline (_) or a period (.)", refers to files, not directories, so the last file with the underscore will be exluded. https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/develop-tables-external-tables?tabs=hadoop#arguments-create-external-table

    Adapted from community discussion by PGiagkoulas · 2023-01-15 · 47 community upvotes. Third-party contribution; reviewed by our quality filter, not independently verified.

    Supporting references

    • Microsoft Learn
    • Microsoft Learn
    • Microsoft Learn
    • Microsoft Learn
    • Microsoft Learn

    Answer figure

    

    View community discussion4

    Candidate context

    Question 12 discussion

    These are quality-filtered excerpts from third-party community discussion. They can be useful reasoning signals, but they are not official answers and have not been independently verified by DP-203 or CertExamHQ.

    1. S

       shoottheduck

       2023-02-17

       97 upvotes

       I have just tested this on Synapse Serverless: ./Folder3 AND _mydata4.csv were ignored. Therefor; Yes, No, No

    2. P

       PGiagkoulas

       2023-01-15

       47 upvotes

       1.Yes, 2.Yes: "Unlike Hadoop external tables, native external tables don't return subfolders unless you specify /** at the end of path" which is the case here. 3. No: "Both Hadoop and native external tables will skip the files with the names that begin with an underline (_) or a period (.)", refers to files, not directories, so the last file with the underscore will be exluded. https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/develop-tables-external-tables?tabs=hadoop#arguments-create-external-table

    3. V

       VikkiC

       2023-06-12

       26 upvotes

       Folder or file that starts with . or _ Reference documentation: https://learn.microsoft.com/en-us/sql/t-sql/statements/create-external-table-transact-sql?view=azure-sqldw-latest&tabs=dedicated#location--folder_or_filepath-1

    4. B

       bakamon

       2023-05-23

       14 upvotes

       Correct Answers ::: 100000% True Statement 1: Yes. The data from the mydata2.csv file will be returned when selecting all the rows in dbo.Table1 because the file is located in the folder structure specified by the external data source. Statement 2: No. The data from the mydata3.csv file will not be returned when selecting all the rows in dbo.Table1 because folders that start with a dot are treated as hidden folders and are not included in wildcard searches. Statement 3: No. The data from the _mydata4.csv file will not be returned when selecting all the rows in dbo.Table1 because files that start with an underscore are treated as hidden files and are not included in wildcard searches.

13. Question 13 of 25

    HOTSPOT - You use Azure Data Factory to prepare data to be queried by Azure Synapse Analytics serverless SQL pools. Files are initially ingested into an Azure Data Lake Storage Gen2 account as 10 small JSON files. Each file contains the same data attributes and data from a subsidiary of your company. You need to move the files to a different folder and transform the data to meet the following requirements: ✑ Provide the fastest possible query times. ✑ Automatically infer the schema from the underlying files. How should you configure the Data Factory copy activity? 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

    Explanation

    Box 1: Preserver hierarchy - Compared to the flat namespace on Blob storage, the hierarchical namespace greatly improves the performance of directory management operations, which improves overall job performance. Box 2: Parquet - Azure Data Factory parquet format is supported for Azure Data Lake Storage Gen2. Parquet supports the schema property. Reference: https://docs.microsoft.com/en-us/azure/storage/blobs/data-lake-storage-introduction https://docs.microsoft.com/en-us/azure/data-factory/format-parquet

    Adapted from community discussion by edba · 2021-12-25 · 12 community upvotes. Third-party contribution; reviewed by our quality filter, not independently verified.

    Supporting references

    • Microsoft Learn
    • Microsoft Learn
    • Microsoft Learn
    • Microsoft Learn
    • Microsoft Learn

    Answer figure

    

    View community discussion4

    Candidate context

    Question 13 discussion

    These are quality-filtered excerpts from third-party community discussion. They can be useful reasoning signals, but they are not official answers and have not been independently verified by DP-203 or CertExamHQ.

    1. A

       alain2

       2024-09-20

       171 upvotes

       1. Merge Files 2. Parquet https://docs.microsoft.com/en-us/azure/storage/blobs/data-lake-storage-performance-tuning-guidance

    2. A

       Ameenymous

       2021-05-25

       25 upvotes

       The smaller the files, the negative the performance so Merge and Parquet seems to be the right answer.

    3. T

       ThiruthuvaRajan

       2021-06-01

       12 upvotes

       It should be 1)Merge Files - Question clearly says "initially ingested as 10 small json files". There is no hint on hierarchy or partition information. so clearly we need to merge these files for better performance 2) Parquet -> Always gives better performance for columnar based data

    4. E

       edba

       2021-12-25

       12 upvotes

       just want to add a bit more reference regarding copyBehavior in ADF plus info mentioned in Best Practice doc, so it shall be MergeFile first. https://docs.microsoft.com/en-us/azure/data-factory/connector-file-system?tabs=data-factory#file-system-as-sink

14. Question 14 of 25

    HOTSPOT - You have a data model that you plan to implement in a data warehouse in Azure Synapse Analytics as shown in the following exhibit.

    

    All the dimension tables will be less than 2 GB after compression, and the fact table will be approximately 6 TB. The dimension tables will be relatively static with very few data inserts and updates. Which type of table should you use for each table? 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

    Explanation

    Box 1: Replicated - Replicated tables are ideal for small star-schema dimension tables, because the fact table is often distributed on a column that is not compatible with the connected dimension tables. If this case applies to your schema, consider changing small dimension tables currently implemented as round-robin to replicated. Box 2: Replicated - Box 3: Replicated - Box 4: Hash-distributed - For Fact tables use hash-distribution with clustered columnstore index. Performance improves when two hash tables are joined on the same distribution column. Reference: https://azure.microsoft.com/en-us/updates/reduce-data-movement-and-make-your-queries-more-efficient-with-the-general-availability-of-replicated-tables/ https://azure.microsoft.com/en-us/blog/replicated-tables-now-generally-available-in-azure-sql-data-warehouse/

    Adapted from community discussion by ian_viana · 2021-09-15 · 240 community upvotes. Third-party contribution; reviewed by our quality filter, not independently verified.

    Supporting references

    • Microsoft Learn
    • Microsoft Learn
    • Microsoft Learn
    • Microsoft Learn
    • Microsoft Learn

    Answer figure

    

    View community discussion4

    Candidate context

    Question 14 discussion

    These are quality-filtered excerpts from third-party community discussion. They can be useful reasoning signals, but they are not official answers and have not been independently verified by DP-203 or CertExamHQ.

    1. I

       ian_viana

       2021-09-15

       240 upvotes

       The answer is correct. The Dims are under 2gb so no point in use hash. Common distribution methods for tables: The table category often determines which option to choose for distributing the table. Table category Recommended distribution option Fact -Use hash-distribution with clustered columnstore index. Performance improves when two hash tables are joined on the same distribution column. Dimension - Use replicated for smaller tables. If tables are too large to store on each Compute node, use hash-distributed. Staging - Use round-robin for the staging table. The load with CTAS is fast. Once the data is in the staging table, use INSERT...SELECT to move the data to production tables. https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-tables-overview#common-distribution-methods-for-tables

    2. O

       ohana

       2021-10-23

       49 upvotes

       Took the exam today, this question came out. Ans: All the Dim tables --> Replicated Fact Tables --> Hash Distributed

    3. B

       Bakachi55

       2024-03-14

       4 upvotes

       Dear Community, I would like to express my heartfelt gratitude for the thoughtful mock questions that have been shared. Your generosity in providing these valuable resources has been immensely helpful. As we engage in discussions and learn together, I am reminded of the strength and camaraderie that exists within our community. To everyone who has contributed, whether by creating questions, participating in discussions, or simply offering encouragement, thank you. Your collective efforts make this community a vibrant and supportive place for learning and growth. Let us continue to share knowledge, support one another, and celebrate our shared passion for learning

    4. V

       vigilante89

       2022-12-16

       3 upvotes

       Dim_*: Replicated Since dimension tables are less likely to get frequent updates and are usually smaller in size, replicating them across all partitions makes logical sense. Also, Tables less than 2gb size should be replicated. Fact_*: Hash Distributed Since Fact tables are huge and have frequent insert/delete/updates going on, hash distribution is the perfect distribution candidate. Also, Tables greater than 2gb size should be hash distributed.

15. Question 15 of 25

    HOTSPOT - The following code segment is used to create an Azure Databricks cluster.

    

    For each of the following statements, select Yes if the statement is true. Otherwise, select No. 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

    Explanation

    Box 1: Yes - A cluster mode of 'High Concurrency' is selected, unlike all the others which are 'Standard'. This results in a worker type of Standard_DS13_v2. Box 2: No - When you run a job on a new cluster, the job is treated as a data engineering (job) workload subject to the job workload pricing. When you run a job on an existing cluster, the job is treated as a data analytics (all-purpose) workload subject to all-purpose workload pricing. Box 3: Yes - Delta Lake on Databricks allows you to configure Delta Lake based on your workload patterns. Reference: https://adatis.co.uk/databricks-cluster-sizing/ https://docs.microsoft.com/en-us/azure/databricks/jobs https://docs.databricks.com/administration-guide/capacity-planning/cmbp.html https://docs.databricks.com/delta/index.html

    Adapted from community discussion by GameLift · 2022-04-15 · 56 community upvotes. Third-party contribution; reviewed by our quality filter, not independently verified.

    Supporting references

    • Microsoft Learn
    • Databricks documentation
    • Databricks documentation
    • Databricks documentation
    • Microsoft Learn

    Answer figure

    

    View community discussion4

    Candidate context

    Question 15 discussion

    These are quality-filtered excerpts from third-party community discussion. They can be useful reasoning signals, but they are not official answers and have not been independently verified by DP-203 or CertExamHQ.

    1. G

       GameLift

       2022-04-15

       56 upvotes

       FROM DP-201, thanks to rmk4ever :: 1. Yes A cluster mode of ‘High Concurrency’ is selected, unlike all the others which are ‘Standard’. This results in a worker type of Standard_DS13_v2. ref: https://adatis.co.uk/databricks-cluster-sizing/ 2. NO recommended: New Job Cluster. When you run a job on a new cluster, the job is treated as a data engineering (job) workload subject to the job workload pricing. When you run a job on an existing cluster, the job is treated as a data analytics (all-purpose) workload subject to all-purpose workload pricing. ref: https://docs.microsoft.com/en-us/azure/databricks/jobs Scheduled batch workload- Launch new cluster via job ref: https://docs.databricks.com/administration-guide/capacity-planning/cmbp.html#plan-capacity-and-control-cost 3.YES Delta Lake on Databricks allows you to configure Delta Lake based on your workload patterns. ref: https://docs.databricks.com/delta/index.html

    2. C

       Canary_2021

       2022-07-02

       39 upvotes

       Answer is Correct. Box 1: Yes "spark.databricks.cluster.profile": "serverless" means that the cluster is a High Concurrency Cluster, which support multi-users. Box 2: No Scheduled jobs should run in standard cluster. High Concurrency clusters are intended for multi-users and won’t benefit a cluster running a single job. Box 3:Yes

    3. F

       fbraza

       2022-02-23

       11 upvotes

       Delta lake is only available from Scala version 2.12 but the json data has a version of scala of 2.11.

    4. [

       [Removed]

       2022-02-25

       8 upvotes

       While it is true that the config jumps to Standard_DS13_v2 when switching from Standard to High Concurrency you could perfectly select Standard_DS13_v2 on your own in a Standard Cluster Mode. The hint is from the "ResourceClass": "Serverless". or same in cluster.profile Since the Cluster Mode info also says: "High Concurrency: [...] Previously known as Serverless."

16. Question 16 of 25

    You are designing a statistical analysis solution that will use custom proprietary Python functions on near real-time data from Azure Event Hubs. You need to recommend which Azure service to use to perform the statistical analysis. The solution must minimize latency. What should you recommend?

    • AAzure Synapse Analytics
    • BAzure Databricks
    • CAzure Stream Analytics
    • DAzure SQL Database

    Show answer

    Correct answer: B

    How others answered

    • B64%
    • C34%

    Only 64% agreed on this answer — treat it as contested rather than settled.

    Explanation

    My answer will be B Stream Analytics supports "extending SQL language with JavaScript and C# user-defined functions (UDFs)". There is no mention of Python support; hence Stream Analytics is not correct. https://docs.microsoft.com/en-us/azure/stream-analytics/stream-analytics-introduction Azure Databricks supports near real-time data from Azure Event Hubs. And includes support for R, SQL, Python, Scala, and Java. So I will go for option B.

    Adapted from community discussion by kolakone · 2021-09-18 · 87 community upvotes. Third-party contribution; reviewed by our quality filter, not independently verified.

    Supporting references

    • Microsoft Learn
    • Microsoft Learn
    • Microsoft Learn
    • Microsoft Learn
    • Microsoft Learn

    View community discussion4

    Candidate context

    Question 16 discussion

    These are quality-filtered excerpts from third-party community discussion. They can be useful reasoning signals, but they are not official answers and have not been independently verified by DP-203 or CertExamHQ.

    1. K

       kolakone

       2021-09-18

       87 upvotes

       My answer will be B Stream Analytics supports "extending SQL language with JavaScript and C# user-defined functions (UDFs)". There is no mention of Python support; hence Stream Analytics is not correct. https://docs.microsoft.com/en-us/azure/stream-analytics/stream-analytics-introduction Azure Databricks supports near real-time data from Azure Event Hubs. And includes support for R, SQL, Python, Scala, and Java. So I will go for option B.

    2. A

       anto69

       2022-02-08

       16 upvotes

       I'm sure it's Stream Analytics cause Event Hubs already supports Python (https://docs.microsoft.com/en-us/azure/event-hubs/event-hubs-python-get-started-send). We don't need the other service to support it. We just need to lower costs. Hence ASA is the correct solution

    3. V

       vctrhugo

       2023-06-12

       Selected B4 upvotes

       Azure Stream Analytics only supports programmability in SQL and JavaScript, while Apache Spark in Azure Databricks supports C#/F#, Java, Python, R, Scala. https://learn.microsoft.com/en-us/azure/architecture/data-guide/technology-choices/stream-processing#general-capabilities

    4. T

       TestingCRM

       2023-05-25

       5 upvotes

       Azure Databricks. See https://learn.microsoft.com/en-us/azure/architecture/data-guide/technology-choices/stream-processing#general-capabilities. Python is not supported in Azure Stream Analytics according to this documentation.

17. Question 17 of 25

    Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution. After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen. You are designing an Azure Stream Analytics solution that will analyze Twitter data. You need to count the tweets in each 10-second window. The solution must ensure that each tweet is counted only once. Solution: You use a hopping window that uses a hop size of 10 seconds and a window size of 10 seconds. Does this meet the goal?

    • AYes
    • BNo

    Show answer

    Correct answer: A

    How others answered

    • A69%
    • B31%

    Only 69% agreed on this answer — treat it as contested rather than settled.

    Explanation

    Unlike tumbling windows, hopping windows model scheduled overlapping windows. A hopping window specification consist of three parameters: the timeunit, the windowsize (how long each window lasts) and the hopsize (by how much each window moves forward relative to the previous one). Additionally, offsetsize may be used as an optional fourth parameter. Note that a tumbling window is simply a hopping window whose ‘hop’ is equal to its ‘size’.

    Adapted from community discussion by medsimus · 2021-10-14 · 19 community upvotes. Third-party contribution; reviewed by our quality filter, not independently verified.

    Supporting references

    • Microsoft Learn
    • Microsoft Learn
    • Microsoft Learn
    • Microsoft Learn
    • Microsoft Learn

    View community discussion4

    Candidate context

    Question 17 discussion

    These are quality-filtered excerpts from third-party community discussion. They can be useful reasoning signals, but they are not official answers and have not been independently verified by DP-203 or CertExamHQ.

    1. [

       [Removed]

       2021-08-24

       129 upvotes

       The answer should be "Yes". Hopping window with hop size equals window size should be the same as Tumbling window.

    2. M

       medsimus

       2021-10-14

       19 upvotes

       Unlike tumbling windows, hopping windows model scheduled overlapping windows. A hopping window specification consist of three parameters: the timeunit, the windowsize (how long each window lasts) and the hopsize (by how much each window moves forward relative to the previous one). Additionally, offsetsize may be used as an optional fourth parameter. Note that a tumbling window is simply a hopping window whose ‘hop’ is equal to its ‘size’.

    3. D

       DataEngineer7331

       2023-04-18

       13 upvotes

       A Tumbling Window would be correct. But as stated in the following, a hopping window can be the same as a tumbling window: "To make a Hopping window the same as a Tumbling window, specify the hop size to be the same as the window size." https://learn.microsoft.com/en-us/azure/stream-analytics/stream-analytics-window-functions#hopping-window

    4. S

       strato

       2022-07-31

       10 upvotes

       a hopping window with equal window size to the hopping is effectively non-overlapping. If the size of the hop was smaller, sure.

18. Question 18 of 25

    HOTSPOT - You have a self-hosted integration runtime in Azure Data Factory. The current status of the integration runtime has the following configurations: ✑ Status: Running ✑ Type: Self-Hosted ✑ Version: 4.4.7292.1 ✑ Running / Registered Node(s): 1/1 ✑ High Availability Enabled: False ✑ Linked Count: 0 ✑ Queue Length: 0 ✑ Average Queue Duration. 0.00s The integration runtime has the following node details: ✑ Name: X-M ✑ Status: Running ✑ Version: 4.4.7292.1 ✑ Available Memory: 7697MB ✑ CPU Utilization: 6% ✑ Network (In/Out): 1.21KBps/0.83KBps ✑ Concurrent Jobs (Running/Limit): 2/14 ✑ Role: Dispatcher/Worker ✑ Credential Status: In Sync Use the drop-down menus to select the answer choice that completes each statement based on the information presented. 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

    Explanation

    Box 1: fail until the node comes back online We see: High Availability Enabled: False Note: Higher availability of the self-hosted integration runtime so that it's no longer the single point of failure in your big data solution or cloud data integration with Data Factory. Box 2: lowered - We see: Concurrent Jobs (Running/Limit): 2/14 CPU Utilization: 6% Note: When the processor and available RAM aren't well utilized, but the execution of concurrent jobs reaches a node's limits, scale up by increasing the number of concurrent jobs that a node can run Reference: https://docs.microsoft.com/en-us/azure/data-factory/create-self-hosted-integration-runtime

    Adapted from community discussion by MirandaL · 2021-06-17 · 25 community upvotes. Third-party contribution; reviewed by our quality filter, not independently verified.

    Supporting references

    • Microsoft Learn
    • Microsoft Learn
    • Microsoft Learn
    • Microsoft Learn
    • Microsoft Learn

    Answer figure

    

    View community discussion4

    Candidate context

    Question 18 discussion

    These are quality-filtered excerpts from third-party community discussion. They can be useful reasoning signals, but they are not official answers and have not been independently verified by DP-203 or CertExamHQ.

    1. M

       MirandaL

       2021-06-17

       25 upvotes

       "We recommend that you increase the concurrent jobs limit only when you see low resource usage with the default values on each node." https://docs.microsoft.com/en-us/azure/data-factory/monitor-integration-runtime

    2. E

       edba

       2021-12-28

       19 upvotes

       It sounds many people were confused regarding 2nd question. after check the link below, I think it means to lower or raise limit concurrent jobs. Apparently 14 is too high as usage is 2 only, so it should be "lowered". ref:https://docs.microsoft.com/en-us/azure/data-factory/create-self-hosted-integration-runtime?tabs=data-factory#scale-considerations

    3. P

       Preben

       2021-06-08

       14 upvotes

       If you eat 1 ice cream a day, but you buy 5 new ones every day -- should you increase the amount of ice cream you buy, or lower it? This is the same. You are paying for 14 concurrent jobs, but you are only using 2. You are only using 6 % of the CPU you have purchased, so you are paying for 94 % that you do not use.

    4. R

       romanzdk

       2022-01-18

       10 upvotes

       0.06/2 = 0.03 0.03 * 14 = 0.42 = maximally 42% of cpu for all jobs isn't this better?

19. Question 19 of 25

    HOTSPOT - You have an Azure Data Factory instance named ADF1 and two Azure Synapse Analytics workspaces named WS1 and WS2. ADF1 contains the following pipelines: ✑ P1: Uses a copy activity to copy data from a nonpartitioned table in a dedicated SQL pool of WS1 to an Azure Data Lake Storage Gen2 account ✑ P2: Uses a copy activity to copy data from text-delimited files in an Azure Data Lake Storage Gen2 account to a nonpartitioned table in a dedicated SQL pool of WS2 You need to configure P1 and P2 to maximize parallelism and performance. Which dataset settings should you configure for the copy activity if each pipeline? 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

    Explanation

    Box 1: Set the Copy method to PolyBase While SQL pool supports many loading methods including non-Polybase options such as BCP and SQL BulkCopy API, the fastest and most scalable way to load data is through PolyBase. PolyBase is a technology that accesses external data stored in Azure Blob storage or Azure Data Lake Store via the T-SQL language. Box 2: Set the Copy method to Bulk insert Polybase not possible for text files. Have to use Bulk insert. Reference: https://docs.microsoft.com/en-us/azure/synapse-analytics/sql/load-data-overview

    Adapted from community discussion by marcin1212 · 2021-12-21 · 98 community upvotes. Third-party contribution; reviewed by our quality filter, not independently verified.

    Supporting references

    • Microsoft Learn
    • Microsoft Learn
    • Microsoft Learn
    • Microsoft Learn
    • Microsoft Learn

    Answer figure

    

    View community discussion4

    Candidate context

    Question 19 discussion

    These are quality-filtered excerpts from third-party community discussion. They can be useful reasoning signals, but they are not official answers and have not been independently verified by DP-203 or CertExamHQ.

    1. M

       marcin1212

       2021-12-21

       98 upvotes

       how to use PolyBase when copy data from Synapse to file ? I don't have idea. Moreover PolyBase option is available only when the target is Synapse it should be P1: Set the partition option to "Dynamic range " P2: PolyBase regarding to P1 https://docs.microsoft.com/en-us/azure/data-factory/connector-azure-sql-data-warehouse?tabs=data-factory#parallel-copy-from-synapse-analytics Scenario: "Full load from large table, without physical partitions.." -> Suggested settings: Partition options: Dynamic range partition.

    2. C

       Canary_2021

       2021-12-22

       46 upvotes

       P1: Copy data from SQL to Data Lake. • Bulk insert and PolyBase are not a choice in Sink tab if target is Data Lake. So they are not correct. • Isolation level can be setup if SQL database is the source. Repeatable Read means that locks are placed on all data that is used in a query. Don't think it maximize parallelism and performance. • Set the Partition option to Dynamic range Can be setup if source is SQL in copy activity. And it maximizes parallelism and performance. So I select this option. P2: Copy data from Data Lake to SQL. It is for sure to select PolyBase.

    3. I

       ItHYMeRIsh

       2021-12-13

       14 upvotes

       I believe both answers are PolyBase. PolyBase supports both export to and import from ADLS as documented here: https://docs.microsoft.com/en-us/sql/relational-databases/polybase/polybase-versioned-feature-summary PolyBase does support delimited text files, which contradicts the question's official answer. "Currently PolyBase can load data from UTF-8 and UTF-16 encoded delimited text files as well as the popular Hadoop file formats RC File, ORC, and Parquet (non-nested format). " https://techcommunity.microsoft.com/t5/datacat/azure-sql-data-warehouse-loading-patterns-and-strategies/ba-p/305456#:~:text=Currently%20PolyBase%20can%20load%20data%20from%20UTF-8%20and,data%20from%20gzip%2C%20zlib%20and%20Snappy%20compressed%20files.

    4. V

       vrodriguesp

       2023-02-10

       12 upvotes

       I tried to create a copy activity in adf and these were results: P1) Synapse to ADLS --> Source Partition option: None/Dynamic range Sink Copy behavior: Add dynamic content/None/Flatten hierarchy/Merge files/Preserve hierarchy P2) ADLS to Synapse --> Source Copy method: NA Sink Copy method: Copy command/PolyBase/Bulk insert/Upsert So I think correct answers should be: P1) Set the partition option to dynamic range p2) set the copy method to PolyBase

20. Question 20 of 25

    DRAG DROP - You are responsible for providing access to an Azure Data Lake Storage Gen2 account. Your user account has contributor access to the storage account, and you have the application ID and access key. You plan to use PolyBase to load data into an enterprise data warehouse in Azure Synapse Analytics. You need to configure PolyBase to connect the data warehouse to storage account. Which three components should you create in sequence? To answer, move the appropriate components from the list of components to the answer area and arrange them in the correct order. 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

    Explanation

    Step 1: an asymmetric key - A master key should be created only once in a database. The Database Master Key is a symmetric key used to protect the private keys of certificates and asymmetric keys in the database. Step 2: a database scoped credential Create a Database Scoped Credential. A Database Scoped Credential is a record that contains the authentication information required to connect an external resource. The master key needs to be created first before creating the database scoped credential. Step 3: an external data source - Create an External Data Source. External data sources are used to establish connectivity for data loading using Polybase. Reference: https://www.sqlservercentral.com/articles/access-external-data-from-azure-synapse-analytics-using-polybase

    Adapted from community discussion by engrbrain · 2022-01-08 · 56 community upvotes. Third-party contribution; reviewed by our quality filter, not independently verified.

    Supporting references

    • Microsoft Learn
    • Microsoft Learn
    • Microsoft Learn
    • Microsoft Learn
    • Microsoft Learn

    Answer figure

    

    View community discussion4

    Candidate context

    Question 20 discussion

    These are quality-filtered excerpts from third-party community discussion. They can be useful reasoning signals, but they are not official answers and have not been independently verified by DP-203 or CertExamHQ.

    1. A

       alexleonvalencia

       2021-12-10

       216 upvotes

       1.- A database scoped credential 2.- an External data sorce 3.- a external file format

    2. E

       engrbrain

       2022-01-08

       56 upvotes

       According to the documentation, the first thing you are to create is CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'S0me!nfo'; I don't think this is means an asymmetric key. It is simply a database encryption key. So I think the answer is 1- Create a Database Encryption Key 2 - Create a Database Scoped Credential 3 - Create an External Data Source

    3. F

       Franz58

       2022-07-26

       21 upvotes

       you need to connect to the DW, not to a specific file. Therefore : 1- Create a Database Encryption Key 2 - Create a Database Scoped Credential 3 - Create an External Data Source

    4. M

       metallicjade

       2022-02-07

       15 upvotes

       1.Create a master key on the database. The master key is required to encrypt the credential secret. 2.Create a database scoped credential for Azure blob storage; IDENTITY can be anything as it's not used. 3.Create an external data source with CREATE EXTERNAL DATA SOURCE. 4.Create an external file format with CREATE EXTERNAL FILE FORMAT. 5.Create an external table pointing to data stored in Azure storage with CREATE EXTERNAL TABLE. Refer https://docs.microsoft.com/en-us/sql/relational-databases/polybase/polybase-configure-azure-blob-storage?view=sql-server-ver15

21. Question 21 of 25

    Select 2

    You are designing a security model for an Azure Synapse Analytics dedicated SQL pool that will support multiple companies. You need to ensure that users from each company can view only the data of their respective company. Which two objects should you include in the solution? Each correct answer presents part of the solution. NOTE: Each correct selection is worth one point.

    • Aa security policy
    • Ba custom role-based access control (RBAC) role
    • Ca predicate function
    • Da column encryption key
    • Easymmetric keys

    Show answer

    Correct answer: A, C

    How others answered

    • AC52%
    • AB43%

    Only 52% agreed on this answer — treat it as contested rather than settled.

    Explanation

    Answer is A & C. Although as many have indicated, the steps are • Create the users or groups you want to isolate access. • Create the inline table-valued function that will filter the results based on the predicate defined. • Create a security policy for the table, assigning the function created above The first step may look like "objects"/option B but option B says "A custom role-based access control (RBAC) role. In reality, you would want to create a domain table with companyId and RoleName and create one Role per companyId. (Or maybe a set of roles per companyId depending on what the requirements are). Then the predicate function would use the meta data driven companyIdRoleName table.

    Adapted from community discussion by MarkJoh · 2023-12-08 · 5 community upvotes. Third-party contribution; reviewed by our quality filter, not independently verified.

    Supporting references

    • Microsoft Learn
    • Microsoft Learn
    • Microsoft Learn
    • Microsoft Learn
    • Microsoft Learn

    View community discussion4

    Candidate context

    Question 21 discussion

    These are quality-filtered excerpts from third-party community discussion. They can be useful reasoning signals, but they are not official answers and have not been independently verified by DP-203 or CertExamHQ.

    1. M

       MarkJoh

       2023-12-08

       Selected A, C5 upvotes

       Answer is A & C. Although as many have indicated, the steps are • Create the users or groups you want to isolate access. • Create the inline table-valued function that will filter the results based on the predicate defined. • Create a security policy for the table, assigning the function created above The first step may look like "objects"/option B but option B says "A custom role-based access control (RBAC) role. In reality, you would want to create a domain table with companyId and RoleName and create one Role per companyId. (Or maybe a set of roles per companyId depending on what the requirements are). Then the predicate function would use the meta data driven companyIdRoleName table.

    2. K

       klayytech

       2023-06-14

       3 upvotes

       Option C, a predicate function, is not wrong. It can be a helpful tool for fine-grained control over data access. However, it is not strictly necessary for the solution described in the question. A security policy and a custom RBAC role can be used to achieve the desired outcome without a predicate function. Here is an example of how you could use a security policy and a custom RBAC role to control access to data in a Synapse Analytics dedicated SQL pool: Create a security policy that defines who can access data, what data they can access, and how they can access it. Create a custom RBAC role that gives users specific permissions to data in the pool. Assign the custom RBAC role to users based on their company name. With this configuration in place, users will only be able to access data that they are authorized to access. The use of a predicate function can be helpful in some scenarios, but it is not always necessary. In the case of the question, a security policy and a custom RBAC role are sufficient to achieve the desired outcome.

    3. J

       juamd

       2022-12-06

       6 upvotes

       According to Microsoft documentation: CREATE SECURITY POLICY SalesFilter ADD FILTER PREDICATE Security.tvf_securitypredicate(SalesRep) ON Sales.Orders WITH (STATE = ON); GO So the answer are A and C

    4. [

       [Removed]

       2023-03-12

       6 upvotes

       That's why I went with AB instead because it wasn't mentioned. Therefore, we should assume that the system does not already have the RBAC already in place.

22. Question 22 of 25

    You have an Azure Data Factory version 2 (V2) resource named Df1. Df1 contains a linked service. You have an Azure Key vault named vault1 that contains an encryption key named key1. You need to encrypt Df1 by using key1. What should you do first?

    • AAdd a private endpoint connection to vault1.
    • BEnable Azure role-based access control on vault1.
    • CRemove the linked service from Df1.
    • DCreate a self-hosted integration runtime.

    Show answer

    Correct answer: C

    How others answered

    • C87%

    Explanation

    I believe this is correct, based on the question: What should you do FIRST? A DF needs to be empty to be encrypted: https://docs.microsoft.com/en-us/azure/data-factory/enable-customer-managed-key#post-factory-creation-in-data-factory-ui So FIRST we need to empty the DF - then we can move on.

    Adapted from community discussion by gnulf69 · 2022-09-11 · 41 community upvotes. Third-party contribution; reviewed by our quality filter, not independently verified.

    Supporting references

    • Microsoft Learn
    • Microsoft Learn
    • Microsoft Learn
    • Microsoft Learn
    • Microsoft Learn

    View community discussion4

    Candidate context

    Question 22 discussion

    These are quality-filtered excerpts from third-party community discussion. They can be useful reasoning signals, but they are not official answers and have not been independently verified by DP-203 or CertExamHQ.

    1. G

       gnulf69

       2022-09-11

       41 upvotes

       I believe this is correct, based on the question: What should you do FIRST? A DF needs to be empty to be encrypted: https://docs.microsoft.com/en-us/azure/data-factory/enable-customer-managed-key#post-factory-creation-in-data-factory-ui So FIRST we need to empty the DF - then we can move on.

    2. A

       auwia

       2024-06-26

       Selected C6 upvotes

       Correct answer: A customer-managed key can only be configured on an empty data Factory. The data factory can't contain any resources such as linked services, pipelines and data flows. https://learn.microsoft.com/en-us/azure/data-factory/enable-customer-managed-key#post-factory-creation-in-data-factory-ui

    3. M

       MFR

       2022-12-28

       3 upvotes

       A customer-managed key can only be configured on an empty data Factory. The data factory can’t contain any resources such as linked services, pipelines and data flows. It is recommended to enable customer-managed key right after factory creation. Note: Azure Data Factory encrypts data at rest, including entity definitions and any data cached while runs are in progress. By default, data is encrypted with a randomly generated Microsoft-managed key that is uniquely assigned to your data factory. Reference: https://docs.microsoft.com/en-us/azure/data-factory/enable-customer-managed-key

    4. J

       juanlu46

       2023-04-25

       Selected C1 upvote

       You don't need to enable "RBAC", access policies is a default and more simple way to assign permissions, so B option is not necesary, but it is a requirement to delete the linked services to configure customer-managed key. So the correct answer is C - Delete linked services first. https://docs.microsoft.com/en-us/azure/key-vault/general/assign-access-policy?tabs=azure-portal https://docs.microsoft.com/en-us/azure/data-factory/enable-customer-managed-key#enable-customer-managed-keys

23. Question 23 of 25

    You have an Azure Synapse Analytics dedicated SQL pool that contains a large fact table. The table contains 50 columns and 5 billion rows and is a heap. Most queries against the table aggregate values from approximately 100 million rows and return only two columns. You discover that the queries against the fact table are very slow. Which type of index should you add to provide the fastest query times?

    • Anonclustered columnstore
    • Bclustered columnstore
    • Cnonclustered
    • Dclustered

    Show answer

    Correct answer: B

    How others answered

    • B65%
    • C16%

    Only 65% agreed on this answer — treat it as contested rather than settled.

    Explanation

    Correct. The "Nonclustered columnstore" isn't a posibility in synapse, it not exists: https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-tables-index#index-types Due to number of rows returned (mora than 60 million) clustered index is not a good option: https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-tables-index#heap-tables Is a Heap table; a table without any index, so clustered columstore index seems to be the correct options. Because non-clustered index is a good option when you have a primary clustered index and usea mutiple columns in where clause, but is a Heap table.

    Adapted from community discussion by juanlu46 · 2022-04-25 · 6 community upvotes. Third-party contribution; reviewed by our quality filter, not independently verified.

    Supporting references

    • Microsoft Learn
    • Microsoft Learn
    • Microsoft Learn
    • Microsoft Learn
    • Microsoft Learn

    View community discussion4

    Candidate context

    Question 23 discussion

    These are quality-filtered excerpts from third-party community discussion. They can be useful reasoning signals, but they are not official answers and have not been independently verified by DP-203 or CertExamHQ.

    1. S

       SujithaVulchi

       2021-09-28

       8 upvotes

       A heap is a table without a clustered index. One or more nonclustered indexes can be created on tables stored as a heap. Data is stored in the heap without specifying an order. Usually data is initially stored in the order in which is the rows are inserted into the table, but the Database Engine can move data around in the heap to store the rows efficiently; so the data order cannot be predicted. To guarantee the order of rows returned from a heap, you must use the ORDER BY clause. To specify a permanent logical order for storing the rows, create a clustered index on the table, so that the table is not a heap. Correct answer: non clustered

    2. J

       juanlu46

       2022-04-25

       Selected B6 upvotes

       Correct. The "Nonclustered columnstore" isn't a posibility in synapse, it not exists: https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-tables-index#index-types Due to number of rows returned (mora than 60 million) clustered index is not a good option: https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-tables-index#heap-tables Is a Heap table; a table without any index, so clustered columstore index seems to be the correct options. Because non-clustered index is a good option when you have a primary clustered index and usea mutiple columns in where clause, but is a Heap table.

    3. D

       dom271219

       2022-09-11

       Selected B6 upvotes

       "return only two columns" => don't be confused. It's 2 col and not 2 rows => then Clustered columnstore

    4. M

       mamahani

       2023-05-03

       5 upvotes

       ignore pls; instead of reading watch out if....i read just if, must have been tired?; so clustered index is NOT good when group by operations; its good if you need to retrieve 1 single row or few rows (but aggregate is not just few rows -> its many many rows aggregating to 1 row, which is not the same); by this i believe its indeed clustered columnstore index so the given answer is correct

24. Question 24 of 25

    You create an Azure Databricks cluster and specify an additional library to install. When you attempt to load the library to a notebook, the library in not found. You need to identify the cause of the issue. What should you review?

    • Anotebook logs
    • Bcluster event logs
    • Cglobal init scripts logs
    • Dworkspace logs

    Show answer

    Correct answer: B

    How others answered

    • B68%
    • C32%

    Only 68% agreed on this answer — treat it as contested rather than settled.

    Explanation

    I should say Cluster Event logs: Azure Databricks provides three kinds of logging of cluster-related activity: Cluster event logs, which capture cluster lifecycle events, like creation, termination, configuration edits, and so on. Apache Spark driver and worker logs, which you can use for debugging. Cluster init-script logs, valuable for debugging init scripts. https://docs.microsoft.com/en-us/azure/databricks/clusters/clusters-manage#event-log

    Adapted from community discussion by Dizzystar · 2022-05-01 · 34 community upvotes. Third-party contribution; reviewed by our quality filter, not independently verified.

    Supporting references

    • Microsoft Learn
    • Databricks documentation
    • Microsoft Learn
    • Microsoft Learn
    • Microsoft Learn

    View community discussion4

    Candidate context

    Question 24 discussion

    These are quality-filtered excerpts from third-party community discussion. They can be useful reasoning signals, but they are not official answers and have not been independently verified by DP-203 or CertExamHQ.

    1. D

       Dizzystar

       2022-05-01

       34 upvotes

       I should say Cluster Event logs: Azure Databricks provides three kinds of logging of cluster-related activity: Cluster event logs, which capture cluster lifecycle events, like creation, termination, configuration edits, and so on. Apache Spark driver and worker logs, which you can use for debugging. Cluster init-script logs, valuable for debugging init scripts. https://docs.microsoft.com/en-us/azure/databricks/clusters/clusters-manage#event-log

    2. E

       edba

       2022-06-29

       6 upvotes

       I think answer is B - Cluster Event logs. Because there are 3 ways to install a new library ( https://docs.microsoft.com/en-us/azure/databricks/libraries/cluster-libraries#--install-a-library-on-a-cluster ), using init script is just one of them.

    3. G

       gerrie1979

       2023-05-12

       Selected B2 upvotes

       https://learn.microsoft.com/en-us/azure/databricks/clusters/init-scripts: Init script start and finish events are captured in cluster event logs. Details are captured in cluster logs. Global init script create, edit, and delete events are also captured in account-level diagnostic logs. Cluster event logs capture two init script events: INIT_SCRIPTS_STARTED and INIT_SCRIPTS_FINISHED, indicating which scripts are scheduled for execution and which have completed successfully. INIT_SCRIPTS_FINISHED also captures execution duration. Global init scripts are indicated in the log event details by the key "global" and cluster-scoped init scripts are indicated by the key "cluster".

    4. V

       vctrhugo

       2023-12-28

       Selected B3 upvotes

       Cluster event logs in Azure Databricks provide detailed information about the cluster's lifecycle events, including the installation and initialization of libraries. By reviewing the cluster event logs, you can examine the events related to library installation and determine if any errors or issues occurred during the process.

25. Question 25 of 25

    You have an Azure data factory named DF1. DF1 contains a single pipeline that is executed by using a schedule trigger. From Diagnostics settings, you configure pipeline runs to be sent to a resource-specific destination table in a Log Analytics workspace. You need to run KQL queries against the table. Which table should you query?

    • AADFPipelineRun
    • BADFTriggerRun
    • CADFActivityRun
    • DAzureDiagnostics

    Show answer

    Correct answer: A

    How others answered

    • A57%
    • B37%

    Only 57% agreed on this answer — treat it as contested rather than settled.

    Explanation

    Remember that you are setting "pipeline runs" logs from diagnostics settings to be sent to a Log Analytics workspace, not the "trigger runs" or "activity runs" logs. Azure Diagnostics stores resource logs for Azure services that use Azure Diagnostics mode. Resource logs describe the internal operation of Azure resources. Azure services that use resource-specific mode store data in a table specific to that service and do not use the AzureDiagnostics table https://learn.microsoft.com/en-us/azure/azure-monitor/reference/tables/azurediagnostics

    Adapted from community discussion by Mausar · 2024-02-24 · 6 community upvotes. Third-party contribution; reviewed by our quality filter, not independently verified.

    Supporting references

    • Microsoft Learn
    • Microsoft Learn
    • Microsoft Learn
    • Microsoft Learn
    • Microsoft Learn

    View community discussion4

    Candidate context

    Question 25 discussion

    These are quality-filtered excerpts from third-party community discussion. They can be useful reasoning signals, but they are not official answers and have not been independently verified by DP-203 or CertExamHQ.

    1. M

       Mausar

       2024-02-24

       Selected A6 upvotes

       Remember that you are setting "pipeline runs" logs from diagnostics settings to be sent to a Log Analytics workspace, not the "trigger runs" or "activity runs" logs. Azure Diagnostics stores resource logs for Azure services that use Azure Diagnostics mode. Resource logs describe the internal operation of Azure resources. Azure services that use resource-specific mode store data in a table specific to that service and do not use the AzureDiagnostics table https://learn.microsoft.com/en-us/azure/azure-monitor/reference/tables/azurediagnostics

    2. M

       mav2000

       2024-02-29

       4 upvotes

       I would say it's A, because in the ADFTriggerRun it would only contain information on when was the pipeline executed and which pipeline, the question asks for "pipeline runs", but yes it's very generic, because by pipeline runs you can also think that it's about when it was executed, but since it doesn't implicitely say so, I'd go with A

    3. F

       f2a9aa5

       2024-07-30

       Selected A3 upvotes

       A. ADFPipelineRun Here’s why: ADFPipelineRun: This table contains detailed information about the execution of your ADF pipelines, including start and end times, status, and other relevant metrics. It’s the go-to table for querying pipeline run details1.

    4. K

       KauK

       2024-12-12

       Selected A1 upvote

       Diagnostic Settings and Log Routing: When configuring diagnostic settings for ADF to send logs to a Log Analytics workspace, you have two modes: Azure-Diagnostics Mode: In this mode, all diagnostic logs are sent to a consolidated table named AzureDiagnostics. This table aggregates various logs, and you can filter the data based on specific services or log types. Resource-Specific Mode: This mode routes logs into distinct tables specific to ADF activities. The relevant tables include: ADFPipelineRun: Contains records of pipeline executions. ADFActivityRun: Details individual activities within pipelines. ADFTriggerRun: Logs trigger executions. https://learn.microsoft.com/en-us/azure/data-factory/monitor-configure-diagnostics

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

DP-203 practice, question 1 of 25