DP-203 practice questions 51–60

1. Question 51 · 370 in bank

   You are designing a fact table named FactPurchase in an Azure Synapse Analytics dedicated SQL pool. The table contains purchases from suppliers for a retail store. FactPurchase will contain the following columns.

   

   FactPurchase will have 1 million rows of data added daily and will contain three years of data. Transact-SQL queries similar to the following query will be executed daily. SELECT - SupplierKey, StockItemKey, COUNT(*) FROM FactPurchase - WHERE DateKey >= 20210101 - AND DateKey <= 20210131 - GROUP By SupplierKey, StockItemKey Which table distribution will minimize query times?

   • Areplicated
   • Bhash-distributed on PurchaseKey
   • Cround-robin
   • Dhash-distributed on DateKey

   Show answer

   Answer: B

   Explanation

   From the documentation the answer is clear enough. B is the right answer. When choosing a distribution column, select a distribution column that: "Is not a date column. All data for the same date lands in the same distribution. If several users are all filtering on the same date, then only 1 of the 60 distributions do all the processing work."

   Source: Adapted from community discussion — AugustineUba

   References

   • Microsoft Learn
   • Microsoft Learn
   • Microsoft Learn

   Next question

2. Question 52 · 370 in bank

   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

   Answer: B

   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

   Source: Adapted from community discussion — Chillem1900

   References

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

   Next question

3. Question 53 · 370 in bank

   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

   Answer: A

   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

   Source: Adapted from community discussion — rocky48

   References

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

   Next question

4. Question 54 · 370 in bank

   DRAG DROP - You need to build a solution to ensure that users can query specific files in an Azure Data Lake Storage Gen2 account from an Azure Synapse Analytics serverless SQL pool. 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: More than one order of answer choices is correct. You will receive credit for any of the correct orders you select. Select and Place:

   

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

   Show the answer figure

   Answer: See figure below

   Explanation

   Step 1: Create an external data source You can create external tables in Synapse SQL pools via the following steps: 1. CREATE EXTERNAL DATA SOURCE to reference an external Azure storage and specify the credential that should be used to access the storage. 2. CREATE EXTERNAL FILE FORMAT to describe format of CSV or Parquet files. 3. CREATE EXTERNAL TABLE on top of the files placed on the data source with the same file format. Step 2: Create an external file format object Creating an external file format is a prerequisite for creating an external table. Step 3: Create an external table Reference: https://docs.microsoft.com/en-us/azure/synapse-analytics/sql/develop-tables-external-tables

   Source: Adapted from community discussion — vctrhugo

   References

   • Microsoft Learn

   

   Next question

5. Question 55 · 370 in bank

   Select 2

   You are designing a data mart for the human resources (HR) department at your company. The data mart will contain employee information and employee transactions. From a source system, you have a flat extract that has the following fields: ✑ EmployeeID FirstName -

   

   ✑ LastName ✑ Recipient ✑ GrossAmount ✑ TransactionID ✑ GovernmentID ✑ NetAmountPaid ✑ TransactionDate You need to design a star schema data model in an Azure Synapse Analytics dedicated SQL pool for the data mart. Which two tables should you create? Each correct answer presents part of the solution. NOTE: Each correct selection is worth one point.

   • Aa dimension table for Transaction
   • Ba dimension table for EmployeeTransaction
   • Ca dimension table for Employee
   • Da fact table for Employee
   • Ea fact table for Transaction

   Show answer

   Answer: CE

   Explanation

   Correct. A dimension is like a dictionary of information, therefore will hold the customer data such as name, address, date of birth, whatever. The fact table contains the facts. Usually numbers, usually the data that we are getting from a system from which we will create metrics later on. Therefore for transactions.

   Source: Adapted from community discussion — ellala

   Next question

6. Question 56 · 370 in bank

   You are designing a dimension table for a data warehouse. The table will track the value of the dimension attributes over time and preserve the history of the data by adding new rows as the data changes. Which type of slowly changing dimension (SCD) should you use?

   • AType 0
   • BType 1
   • CType 2
   • DType 3

   Show answer

   Answer: C

   Next question

7. Question 57 · 370 in bank

   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: See figure below

   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

   Source: Adapted from community discussion — sunil_smile

   References

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

   

   Next question

8. Question 58 · 370 in bank

   HOTSPOT - You are building an Azure Synapse Analytics dedicated SQL pool that will contain a fact table for transactions from the first half of the year 2020. You need to ensure that the table meets the following requirements: ✑ Minimizes the processing time to delete data that is older than 10 years ✑ Minimizes the I/O for queries that use year-to-date values How should you complete the Transact-SQL statement? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point. Hot Area:

   

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

   Show the answer figure

   Answer: See figure below

   Explanation

   Box 1: PARTITION - RANGE RIGHT FOR VALUES is used with PARTITION. Part 2: [TransactionDateID] Partition on the date column. Example: Creating a RANGE RIGHT partition function on a datetime column The following partition function partitions a table or index into 12 partitions, one for each month of a year's worth of values in a datetime column. CREATE PARTITION FUNCTION [myDateRangePF1] (datetime) AS RANGE RIGHT FOR VALUES ('20030201', '20030301', '20030401', '20030501', '20030601', '20030701', '20030801', '20030901', '20031001', '20031101', '20031201'); Reference: https://docs.microsoft.com/en-us/sql/t-sql/statements/create-partition-function-transact-sql

   Source: Adapted from community discussion — Aditya0891

   

   Next question

9. Question 59 · 370 in bank

   You are performing exploratory analysis of the bus fare data in an Azure Data Lake Storage Gen2 account by using an Azure Synapse Analytics serverless SQL pool. You execute the Transact-SQL query shown in the following exhibit.

   

   What do the query results include?

   • AOnly CSV files in the tripdata_2020 subfolder.
   • BAll files that have file names that beginning with "tripdata_2020".
   • CAll CSV files that have file names that contain "tripdata_2020".
   • DOnly CSV that have file names that beginning with "tripdata_2020".

   Show answer

   Answer: D

   Explanation

   Sorry but I don't understand. File or Directory that start with "tripdata_2020" can selected. /tripdata_2020/a.csv /tripdata_2020_a_b.csv /tripdata_2020/2020/1/1/myfile.csv So question is very not clear. D question is partially correct

   Source: Adapted from community discussion — panda_azzurro

   Next question

10. Question 60 · 370 in bank

    DRAG DROP - You use PySpark in Azure Databricks to parse the following JSON input.

    

    You need to output the data in the following tabular format.

    

    How should you complete the PySpark code? To answer, drag the appropriate values to the correct targets. Each value may be used once, more than once, or not at all. You may need to drag the spit bar between panes or scroll to view content. NOTE: Each correct selection is worth one point. Select and Place:

    

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

    Show the answer figure

    Answer: See figure below

    Explanation

    Box 1: select - Box 2: explode - Bop 3: alias - pyspark.sql.Column.alias returns this column aliased with a new name or names (in the case of expressions that return more than one column, such as explode). Reference: https://spark.apache.org/docs/latest/api/python/reference/api/pyspark.sql.Column.alias.html https://docs.microsoft.com/en-us/azure/databricks/sql/language-manual/functions/explode

    Source: Adapted from community discussion — kb8bo

    

    Next question

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

DP-203 practice, question 51. 370 questions available.