DP-203 practice questions 81–90

1. Question 81 · 370 in bank

   You plan to create a dimension table in Azure Synapse Analytics that will be less than 1 GB. You need to create the table to meet the following requirements: ✑ Provide the fastest query time. ✑ Minimize data movement during queries. Which type of table should you use?

   • Areplicated
   • Bhash distributed
   • Cheap
   • Dround-robin

   Show answer

   Answer: A

   Explanation

   Since the dim table is under 1 GB size which is quite small, it should be replicated across all the partitions so that data movement is less and efficiency is more.

   Source: Adapted from community discussion — vigilante89

   Next question

2. Question 82 · 370 in bank

   You are designing a dimension table in an Azure Synapse Analytics dedicated SQL pool. You need to create a surrogate key for the table. The solution must provide the fastest query performance. What should you use for the surrogate key?

   • Aa GUID column
   • Ba sequence object
   • Can IDENTITY column

   Show answer

   Answer: C

   Explanation

   C. an IDENTITY column When designing a dimension table in a data warehouse, it's important to consider the types of queries that will be run against it. IDENTITY columns are generally the best option for surrogate keys in dimension tables because they provide the fastest query performance. IDENTITY columns are auto-incremented and indexed by default, which makes them ideal for use as primary keys. They also require less storage space than GUID columns and are less likely to cause fragmentation in indexes.

   Source: Adapted from community discussion — nicky87654

   Next question

3. Question 83 · 370 in bank

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

   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

   Source: Adapted from community discussion — PGiagkoulas

   References

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

   

   Next question

4. Question 84 · 370 in bank

   You have an Azure Synapse Analytics dedicated SQL pool. You need to create a fact table named Table1 that will store sales data from the last three years. The solution must be optimized for the following query operations: • Show order counts by week. • Calculate sales totals by region. • Calculate sales totals by product. • Find all the orders from a given month. Which data should you use to partition Table1?

   • Aproduct
   • Bmonth
   • Cweek
   • Dregion

   Show answer

   Answer: B

   Explanation

   • Show order counts by week. • Calculate sales totals by region. • Calculate sales totals by product. For these, Group By is required while querying, hence cannot be a parition. But fourth one, requires you to use WHERE clause, so month is ideal for a partition

   Source: Adapted from community discussion — AlviraTony

   Next question

5. Question 85 · 370 in bank

   HOTSPOT - You need to output files from Azure Data Factory. Which file format should you use for each type of output? 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: Parquet - Parquet stores data in columns, while Avro stores data in a row-based format. By their very nature, column-oriented data stores are optimized for read-heavy analytical workloads, while row-based databases are best for write-heavy transactional workloads. Box 2: Avro - An Avro schema is created using JSON format. AVRO supports timestamps. Note: Azure Data Factory supports the following file formats (not GZip or TXT). Avro format - ✑ Binary format ✑ Delimited text format ✑ Excel format ✑ JSON format ✑ ORC format ✑ Parquet format ✑ XML format Reference: https://www.datanami.com/2018/05/16/big-data-file-formats-demystified

   Source: Adapted from community discussion — rocky48

   References

   • Microsoft Learn

   

   Next question

6. Question 86 · 370 in bank

   You are designing the folder structure for an Azure Data Lake Storage Gen2 account. You identify the following usage patterns: • Users will query data by using Azure Synapse Analytics serverless SQL pools and Azure Synapse Analytics serverless Apache Spark pools. • Most queries will include a filter on the current year or week. • Data will be secured by data source. You need to recommend a folder structure that meets the following requirements: • Supports the usage patterns • Simplifies folder security • Minimizes query times Which folder structure should you recommend?

   • A\DataSource\SubjectArea\YYYY\WW\FileData_YYYY_MM_DD.parquet
   • B\DataSource\SubjectArea\YYYY-WW\FileData_YYYY_MM_DD.parquet
   • CDataSource\SubjectArea\WW\YYYY\FileData_YYYY_MM_DD.parquet
   • D\YYYY\WW\DataSource\SubjectArea\FileData_YYYY_MM_DD.parquet
   • EWW\YYYY\SubjectArea\DataSource\FileData_YYYY_MM_DD.parquet

   Show answer

   Answer: A

   Explanation

   A. \DataSource\SubjectArea\YYYY\WW\FileData_YYYY_MM_DD.parquet The recommended folder structure that best meets the requirements is option A. It separates data by data source, year and week. It allows for easy filtering of data by year or week, which aligns with the usage pattern where most queries include a filter on the current year or week.

   Source: Adapted from community discussion — nicky87654

   Next question

7. Question 87 · 370 in bank

   You have an Azure Synapse Analytics dedicated SQL pool named Pool1. Pool1 contains a table named table1. You load 5 TB of data into table1. You need to ensure that columnstore compression is maximized for table1. Which statement should you execute?

   • ADBCC INDEXDEFRAG (pool1, table1)
   • BDBCC DBREINDEX (table1)
   • CALTER INDEX ALL on table1 REORGANIZE
   • DALTER INDEX ALL on table1 REBUILD

   Show answer

   Answer: D

   Explanation

   D. ALTER INDEX ALL on table1 REBUILD This statement will rebuild all indexes on table1, which can help to maximize columnstore compression. The other options are not appropriate for this task. DBCC INDEXDEFRAG (pool1, table1) is for defragmenting the indexes and DBCC DBREINDEX (table1) is for recreating the indexes. ALTER INDEX ALL on table1 REORGANIZE is for reorganizing the indexes.

   Source: Adapted from community discussion — MrWood47

   References

   • Microsoft Learn
   • Microsoft Learn
   • Microsoft Learn

   Next question

8. Question 88 · 370 in bank

   Select 2

   You have an Azure Synapse Analytics dedicated SQL pool named pool1. You plan to implement a star schema in pool and create a new table named DimCustomer by using the following code.

   

   You need to ensure that DimCustomer has the necessary columns to support a Type 2 slowly changing dimension (SCD). Which two columns should you add? Each correct answer presents part of the solution. NOTE: Each correct selection is worth one point.

   • A[HistoricalSalesPerson] [nvarchar] (256) NOT NULL
   • B[EffectiveEndDate] [datetime] NOT NULL
   • C[PreviousModifiedDate] [datetime] NOT NULL
   • D[RowID] [bigint] NOT NULL
   • E[EffectiveStartDate] [datetime] NOT NULL

   Show answer

   Answer: BE

   Explanation

   that's the business key, not the surrogate key. If a new record is generated, there would be a duplicate key. SK is necessary to identify the record

   Source: Adapted from community discussion — laurasscastro

   References

   • Microsoft Learn

   Next question

9. Question 89 · 370 in bank

   HOTSPOT - You have an Azure subscription that contains an Azure Synapse Analytics dedicated SQL pool. You plan to deploy a solution that will analyze sales data and include the following: • A table named Country that will contain 195 rows • A table named Sales that will contain 100 million rows • A query to identify total sales by country and customer from the past 30 days You need to create the tables. The solution must maximize query performance. How should you complete the script? 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.

   Show the answer figure

   Answer: See figure below

   Explanation

   It is hash because it is a fact table (you can tell because there is the "total" column being created which is numerical). Rule of thumb, never hash on a date field, so in this case you would hash on 'CustomerID'. You want the hash to have as many unique values as possible.

   Source: Adapted from community discussion — ajhak

   

   Next question

10. Question 90 · 370 in bank

    You have an Azure subscription that contains an Azure Data Lake Storage Gen2 account named account1 and an Azure Synapse Analytics workspace named workspace1. You need to create an external table in a serverless SQL pool in workspace1. The external table will reference CSV files stored in account1. The solution must maximize performance. How should you configure the external table?

    • AUse a native external table and authenticate by using a shared access signature (SAS).
    • BUse a native external table and authenticate by using a storage account key.
    • CUse an Apache Hadoop external table and authenticate by using a shared access signature (SAS).
    • DUse an Apache Hadoop external table and authenticate by using a service principal in Microsoft Azure Active Directory (Azure AD), part of Microsoft Entra.

    Show answer

    Answer: A

    Explanation

    Correct! Serverless SQL Pools cannot use Hadoop, Only Native. Access Key Auth is never best practice therefore leaving only A as a viable answer. https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/develop-tables-external-tables?tabs=hadoop

    Source: Adapted from community discussion — FRANCIS_A_M

    References

    • Microsoft Learn
    • Microsoft Learn

    Next question

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

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