Azure DP-203考试题

Correct

Answer:

Box 1: Type 2 -

A Type 2 SCD supports versioning of dimension members. Often the source system

doesn't store versions, so the data warehouse load process detects and manages

changes in a dimension table. In this case, the dimension table must use a surrogate

key to provide a unique reference to a version of the dimension member. It also

includes columns that define the date range validity of the version (for example,

Box 2: a business key -

A business key or natural key is an index which identifies uniqueness of a row based on

business keys are customer code in a customer table, composite of sales order header

number and sales order item line number within a sales order details table.

Reference:

https://docs.microsoft.com/en-us/learn/modules/populate-slowly-changing-

dimensions-azure-synapse-analytics-pipelines/3-choose-between-dimension-types

Question #2Topic 1

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.

SELECT -

SupplierKey, StockItemKey, COUNT(*)

FROM FactPurchase -

WHERE DateKey >= 20210101 -

AND DateKey <= 20210131 -

GROUP By SupplierKey, StockItemKey

Which table distribution will minimize query times?

Hash-distributed tables improve query performance on large fact tables, and are the

distribution. If several users are all filtering on the same date, then only 1 of the 60

distributions do all the processing work.

Reference:

https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-

data-warehouse-tables-distribute

Question #3Topic 1

You have a table in an Azure Synapse Analytics dedicated SQL pool. The table was

created by using the following Transact-SQL statement.

You need to alter the table to meet the following requirements:

• C. [ManagerEmployeeKey] [int] NULL

• D. [ManagerName] [varchar](200) NULL

Correct Answer: A

Use the same definition as the EmployeeID column.

Reference:

tabular

Question #4Topic 1

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,

EmployeeStartDate date)

USING Parquet -

SELECT EmployeeID -

FROM mytestdb.dbo.myParquetTable

WHERE name = 'Alice';

What will be returned by the query?

• A. 24

• B. an error

• C. a null value

Correct Answer: A

Once a database has been created by a Spark job, you can create tables in it with Spark

that use Parquet as the storage format. Table names will be converted to lower case

asynchronously, there will be a delay until they appear.

Reference:

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