You are designing a star schema for a dataset that contains records of online orders. Each record includes an order date, an order due date, and an order ship date.
You need to ensure that the design provides the fastest query times of the records when querying for arbitrary date ranges and aggregating by fiscal calendar attributes.
Which two actions should you perform? Each correct answer presents part of the solution.
NOTE: Each correct selection is worth one point.

• A.Create a date dimension table that has a DateTime key.
• B.Create a date dimension table that has an integer key in the format of yyyymmdd.
• C.Use built-in SQL functions to extract date attributes.
• D.In the fact table, use integer columns for the date fields.
• E.Use DateTime columns for the date fields.

Comment: *

Name: *

Email: *

Verification: *

You have two fact tables named Flight and Weather. Queries targeting the tables will be based on the join between the following columns.

You need to recommend a solution that maximum query performance.
What should you include in the recommendation?

• A.In each table, create a column as a composite of the other two columns in the table.
• B.In each table, create an IDENTITY column.
• C.In the tables, use a hash distribution of ArriveAirPortID and AirportID.
• D.In the tables, use a hash distribution of ArriveDateTime and ReportDateTime.

Comment: *

Name: *

Email: *

Verification: *

You have an Azure Synapse Analytics workspace named WS1.
You have an Azure Data Lake Storage Gen2 container that contains JSON-formatted files in the following format.

You need to use the serverless SQL pool in WS1 to read the files.
How should you complete the Transact-SQL statement? 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 split bar between panes or scroll to view content.
NOTE: Each correct selection is worth one point.

Correct Answer:

Explanation
Graphical user interface, text, application, email Description automatically generated

Box 1: openrowset
The easiest way to see to the content of your CSV file is to provide file URL to OPENROWSET function, specify csv FORMAT.
Example:
SELECT *
FROM OPENROWSET(
BULK 'csv/population/population.csv',
DATA_SOURCE = 'SqlOnDemandDemo',
FORMAT = 'CSV', PARSER_VERSION = '2.0',
FIELDTERMINATOR =',',
ROWTERMINATOR = '\n'
Box 2: openjson
You can access your JSON files from the Azure File Storage share by using the mapped drive, as shown in the following example:
SELECT book.* FROM
OPENROWSET(BULK N't:\books\books.json', SINGLE_CLOB) AS json
CROSS APPLY OPENJSON(BulkColumn)
WITH( id nvarchar(100), name nvarchar(100), price float,
pages_i int, author nvarchar(100)) AS book
Reference:
https://docs.microsoft.com/en-us/azure/synapse-analytics/sql/query-single-csv-file
https://docs.microsoft.com/en-us/sql/relational-databases/json/import-json-documents-into-sql-server

Comment: *

Name: *

Email: *

Verification: *

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 use?

• A.Type 0
• B.Type 1
• C.Type 2
• D.Type 3

Comment: *

Name: *

Email: *

Verification: *

You have an Azure Data Lake Storage Gen2 container that contains 100 TB of data.
You need to ensure that the data in the container is available for read workloads in a secondary region if an outage occurs in the primary region. The solution must minimize costs.
Which type of data redundancy should you use?

• A.zone-redundant storage (ZRS)
• B.locally-redundant storage (LRS)
• C.geo-redundant storage (GRS)
• D.read-access geo-redundant storage (RA-GRS)

Comment: *

Name: *

Email: *

Verification: *