CompTIA
Checkpoint
ISC
HP
Dell
EC-Council
EXIN
Fortinet
ITIL®
Juniper
PMI
Microsoft
VMware
Avaya
Google
Salesforce
Amazon
Palo Alto Networks
Certiport
ISC2
All Vendors
  2. Home
  3. Exams
  4. Microsoft
  5. DP-203

Free DP-203 Exam Braindumps (page: 21)

Page 20 of 94
PDF with 397 Questions

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?

  1. \DataSource\SubjectArea\YYYY\WW\FileData_YYYY_MM_DD.parquet
  2. \DataSource\SubjectArea\YYYY-WW\FileData_YYYY_MM_DD.parquet
  3. DataSource\SubjectArea\WW\YYYY\FileData_YYYY_MM_DD.parquet
  4. \YYYY\WW\DataSource\SubjectArea\FileData_YYYY_MM_Dparquet
  5. WW\YYYY\SubjectArea\DataSource\FileData_YYYY_MM_DD.parquet

Answer(s): A

Explanation:

Data will be secured by data source. -> Use DataSource as top folder.
Most queries will include a filter on the current year or week -> Use \YYYY\WW\ as subfolders.

Common Use Cases
A common use case is to filter data stored in a date (and possibly time) folder structure such as /YYYY/MM/DD/ or /YYYY/MM/YYYY-MM-DD/. As new data is generated/sent/copied/moved to the storage account, a new folder is created for each specific time period. This strategy organises data into a maintainable folder structure.


Reference:

https://www.serverlesssql.com/optimisation/azurestoragefilteringusingfilepath/



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?

  1. DBCC INDEXDEFRAG (pool1, table1)
  2. DBCC DBREINDEX (table1)
  3. ALTER INDEX ALL on table1 REORGANIZE
  4. ALTER INDEX ALL on table1 REBUILD

Answer(s): D

Explanation:

Columnstore and columnstore archive compression
Columnstore tables and indexes are always stored with columnstore compression. You can further reduce the size of columnstore data by configuring an additional compression called archival compression. To perform archival compression, SQL Server runs the Microsoft XPRESS compression algorithm on the data. Add or remove archival compression by using the following data compression types:

Use COLUMNSTORE_ARCHIVE data compression to compress columnstore data with archival compression.
Use COLUMNSTORE data compression to decompress archival compression. The resulting data continue to be compressed with columnstore compression.
To add archival compression, use ALTER TABLE (Transact-SQL) or ALTER INDEX (Transact-SQL) with the REBUILD option and DATA COMPRESSION = COLUMNSTORE_ARCHIVE.


Reference:

https://learn.microsoft.com/en-us/sql/relational-databases/data-compression/data-compression



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.

  1. [HistoricalSalesPerson] [nvarchar] (256) NOT NULL
  2. [EffectiveEndDate] [datetime] NOT NULL
  3. [PreviousModifiedDate] [datetime] NOT NULL
  4. [RowID] [bigint] NOT NULL
  5. [EffectiveStartDate] [datetime] NOT NULL

Answer(s): B,E



HOTSPOT (Drag and Drop is not supported)
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.

  1. See Explanation section for answer.

Answer(s): A

Explanation:






Post your Comments and Discuss Microsoft DP-203 exam with other Community members:

DP-203 Exam Discussions & Posts