You need to design a data storage structure for the product sales transactions. The solution must meet the sales transaction dataset requirements.
What should you include in the solution? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.

Correct Answer:

Comment: *

Name: *

Email: *

Verification: *

You have an enterprise data warehouse in Azure Synapse Analytics that contains a table named FactOnlineSales. The table contains data from the start of 2009 to the end of 2012.
You need to improve the performance of queries against FactOnlineSales by using table partitions. The solution must meet the following requirements:
Create four partitions based on the order date.
Ensure that each partition contains all the orders places during a given calendar year.
How should you complete the T-SQL command? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.

Correct Answer:

Explanation
Text Description automatically generated

Range Left or Right, both are creating similar partition but there is difference in comparison For example: in this scenario, when you use LEFT and 20100101,20110101,20120101 Partition will be, datecol<=20100101, datecol>20100101 and datecol<=20110101, datecol>20110101 and datecol<=20120101, datecol>20120101 But if you use range RIGHT and 20100101,20110101,20120101 Partition will be, datecol<20100101, datecol>=20100101 and datecol<20110101, datecol>=20110101 and datecol<20120101, datecol>=20120101 In this example, Range RIGHT will be suitable for calendar comparison Jan 1st to Dec 31st Reference:
https://docs.microsoft.com/en-us/sql/t-sql/statements/create-partition-function-transact-sql?view=sql-server-ver1

Comment: *

Name: *

Email: *

Verification: *

You need to implement a Type 3 slowly changing dimension (SCD) for product category data in an Azure Synapse Analytics dedicated SQL pool.
You have a table that was created by using the following Transact-SQL statement.

Which two columns should you add to the table? Each correct answer presents part of the solution.
NOTE: Each correct selection is worth one point.

• A.[EffectiveScarcDate] [datetime] NOT NULL,
• B.[CurrentProduccCacegory] [nvarchar] (100) NOT NULL,
• C.[EffectiveEndDace] [dacecime] NULL,
• D.[ProductCategory] [nvarchar] (100) NOT NULL,
• E.[OriginalProduccCacegory] [nvarchar] (100) NOT NULL,

Correct Answer: B,E

A Type 3 SCD supports storing two versions of a dimension member as separate columns. The table includes a column for the current value of a member plus either the original or previous value of the member. So Type
3 uses additional columns to track one key instance of history, rather than storing additional rows to track each change like in a Type 2 SCD.
This type of tracking may be used for one or two columns in a dimension table. It is not common to use it for many members of the same table. It is often used in combination with Type 1 or Type 2 members.

Reference:
https://k21academy.com/microsoft-azure/azure-data-engineer-dp203-q-a-day-2-live-session-review/

Comment: *

Name: *

Email: *

Verification: *

You have an Azure Synapse Analytics serverless SQL pool, an Azure Synapse Analytics dedicated SQL pool, an Apache Spark pool, and an Azure Data Lake Storage Gen2 account.
You need to create a table in a lake database. The table must be available to both the serverless SQL pool and the Spark pool.
Where should you create the table, and Which file format should you use for data in the table? TO answer, select the appropriate options in the answer are a.
NOTE: Each correct selection is worth one point.

Correct Answer:

Comment: *

Name: *

Email: *

Verification: *

You have an Azure subscription that contains an Azure Synapse Analytics dedicated SQL pool.
You need to identify whether a single distribution of a parallel query takes longer than other distributions.

• A.sys.dm_pdw_dns_workers
• B.sys.dm_pdw_sql_requests
• C.sys.dm_pdw_Mec_sessions
• D.sys.dm_pdw_request_steps

Comment: *

Name: *

Email: *

Verification: *