Data Engineering on Microsoft Azure370 questions across 37 pages are complete and free, with no account or card.
370 questions in this bank
Free access · progress saved on this device
You have an Azure Data Lake Storage Gen2 account named storage1. You plan to implement query acceleration for storage1. Which two file types support query acceleration? Each correct answer presents a complete solution. NOTE: Each correct selection is worth one point.
Answer: AD
You have an Azure subscription that contains the resources shown in the following table.
You need to read the files in storage1 by using ad-hoc queries and the OPENROWSET function. The solution must ensure that each rowset contains a single JSON record. To what should you set the FORMAT option of the OPENROWSET function?
Answer: D
It should be D normally. I'll appreciate it if the one who curates the right answer and when the majority doesn't agree with his choice, brings some explanation so we can discuss and understand why he chooses it. It's not the first time I see a total disagreement without any explanation from the ones who select the right answers.
Source: Adapted from community discussion — Zak_Zakaria
HOTSPOT - You have an Azure subscription that contains the Azure Synapse Analytics workspaces shown in the following table.
Each workspace must read and write data to datalake1. Each workspace contains an unused Apache Spark pool. You plan to configure each Spark pool to share catalog objects that reference datalake1. 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.
Answer: See figure below
Provided answers are correct: 1. Yes: Azure Synapse Analytics allows Apache Spark pools in the same workspace to share a managed HMS (Hive Metastore) compatible metastore as their catalog. When customers want to persist the Hive catalog metadata outside of the workspace, and share catalog objects with other computational engines outside of the workspace, such as HDInsight and Azure Databricks, they can connect to an external Hive Metastore. Only Azure SQL Database and Azure Database for MySQL are supported as an external Hive Metastore. 2. Yes: And currently we only support User-Password authentication. 3. No: And currently we only support User-Password authentication. ==> STORAGE BLOB CONTRIBUTOR is an Azure RBAC (Role-Based Access Control) ==> NOT COMPATIBLE (it is supported User-Password authentication ONLY). ref. https://learn.microsoft.com/en-us/azure/synapse-analytics/spark/apache-spark-external-metastore
Source: Adapted from community discussion — auwia
DRAG DROP - You have a data warehouse. You need to implement a slowly changing dimension (SCD) named Product that will include three columns named ProductName, ProductColor, and ProductSize. The solution must meet the following requirements: • Prevent changes to the values stored in ProductName. • Retain only the current and the last values in ProductSize. • Retain all the current and previous values in ProductColor. Which type of SCD should you implement for each column? To answer, drag the appropriate types to the correct columns. Each type may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content. NOTE: Each correct selection is worth one point.
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.
Answer: See figure below
ProductName - type 0, as no changes are done. Color - type 3, as with type 3 we have one column for the current value and one for the previous so only these two are preserved. Size - type 2, as it inserts a new row for every change, so we get all historical values.
Source: Adapted from community discussion — andjurovicela
HOTSPOT - You are incrementally loading data into fact tables in an Azure Synapse Analytics dedicated SQL pool. Each batch of incoming data is staged before being loaded into the fact tables. You need to ensure that the incoming data is staged as quickly as possible. How should you configure the staging tables? To answer, select the appropriate options in the answer area. 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.
Answer: See figure below
Correct! The ROUND_ROBIN distribution distributes the data evenly across all distribution nodes in the SQL pool. This distribution type is suitable for loading data quickly into the staging tables because it minimizes the data movement during the loading process. Use a HEAP table: Instead of creating a clustered index on the staging table, it is recommended to create a HEAP table. A HEAP table does not have a clustered index, which eliminates the need for maintaining the index and improves the data loading performance. It allows for faster insert operations.
Source: Adapted from community discussion — orionduo
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.
Answer: See figure below
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/
Source: Adapted from community discussion — ian_viana
You have an Azure subscription that contains an Azure Synapse Analytics workspace named ws1 and an Azure Cosmos DB database account named Cosmos1. Cosmos1 contains a container named container1 and ws1 contains a serverless SQL pool. You need to ensure that you can query the data in container1 by using the serverless SQL pool. Which three actions should you perform? Each correct answer presents part of the solution. NOTE: Each correct selection is worth one point.
Answer: ACD
HOTSPOT - You have an Azure subscription that contains the resources shown in the following table.
The storage1 account contains a container named container1. The container1 container contains the following files.
In Pool1, you run the following script.
In the Built-in serverless SQL pool, you run the following script.
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.
Answer: See figure below
Both Hadoop(dedicated) and native(serverless) external tables will skip the files with the names that begin with an underline (_) or a period (.).
Source: Adapted from community discussion — pc1337xd
DRAG DROP - You have an Azure subscription that contains an Azure Data Lake Storage Gen2 account named account1 and a user named User1. In account1, you create a container named container1. In container1, you create a folder named folder1. You need to ensure that User1 can list and read all the files in folder1. The solution must use the principle of least privilege. How should you configure the permissions for each folder? To answer, drag the appropriate permissions to the correct folders. Each permission may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content. NOTE: Each correct selection is worth one point.
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.
Answer: See figure below
Execute permission to container1 to navigate to folder1 and Read & Execute permission to folder1 to list and read all files present in there.
Source: Adapted from community discussion — saqib839
You have an Azure Data Factory pipeline named pipeline1. You need to execute pipeline1 at 2 AM every day. The solution must ensure that if the trigger for pipeline1 stops, the next pipeline execution will occur at 2 AM, following a restart of the trigger. Which type of trigger should you create?
Answer: A
Answer is Tumbling : Link : https://learn.microsoft.com/en-us/azure/data-factory/concepts-pipeline-execution-triggers#trigger-type-comparison "Retry capability Supported. Failed pipeline runs have a default retry policy of 0, or a policy that's specified by the user in the trigger definition. Automatically retries when the pipeline runs fail due to concurrency/server/throttling limits (that is, status codes 400: User Error, 429: Too many requests, and 500: Internal Server error)" Retry capability is not supported on Schedule trigger
Source: Adapted from community discussion — yassine70
Keyboard:J Kmove between questionsA–Dselect an answerRshow or hide the answer← →change page
DP-203 practice, question 101. 370 questions available.
Version 1.0.0. How we source questions