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?
Answer(s): B
Table partitions enable you to divide your data into smaller groups of data. In most cases, table partitions are created on a date column.Benefits to queriesPartitioning can also be used to improve query performance. A query that applies a filter to partitioned data can limit the scan to only the qualifying partitions. This method of filtering can avoid a full table scan and only scan a smaller subset of data. With the introduction of clustered columnstore indexes, the predicate elimination performance benefits are less beneficial, but in some cases there can be a benefit to queries.For example, if the sales fact table is partitioned into 36 months using the sales date field, then queries that filter on the sale date can skip searching in partitions that don't match the filter.Note: Benefits to loadsThe primary benefit of partitioning in dedicated SQL pool is to improve the efficiency and performance of loading data by use of partition deletion, switching and merging. In most cases data is partitioned on a date column that is closely tied to the order in which the data is loaded into the SQL pool. One of the greatest benefits of using partitions to maintain data is the avoidance of transaction logging. While simply inserting, updating, or deleting data can be the most straightforward approach, with a little thought and effort, using partitioning during your load process can substantially improve performance.
https://learn.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-tables-partition
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 timesWhich folder structure should you recommend?
Answer(s): A
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 CasesA 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.
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?
Answer(s): D
Columnstore and columnstore archive compressionColumnstore 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.
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.
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 daysYou 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.
Post your Comments and Discuss Microsoft DP-203 exam prep with other Community members:
Our website is free, but we have to fight against AI bots and content theft. We're sorry for the inconvenience caused by these security measures. You can access the rest of the DP-203 content, but please register or login to continue.