You have an Azure Synapse Analytics workspace named WS1 that contains an Apache Spark pool named Pool1.You plan to create a database named DB1 in Pool1.You need to ensure that when tables are created in DB1, the tables are available automatically as external tables to the built-in serverless SQL pool.Which format should you use for the tables in DB1?

You have an Azure Synapse Analytics dedicated SQL pool named Pool1. Pool1 contains a partitioned fact table named dbo.Sales and a staging table named stg.Sales that has the matching table and partition definitions.You need to overwrite the content of the first partition in dbo.Sales with the content of the same partition in stg.Sales. The solution must minimize load times.What should you do?

You have files and folders in Azure Data Lake Storage Gen2 for an Azure Synapse workspace as shown in the following exhibit.You create an external table named ExtTable that has LOCATION='/topfolder/'.When you query ExtTable by using an Azure Synapse Analytics serverless SQL pool, which files are returned?

You have an Azure Synapse Analytics dedicated SQL pool that contains a table named Table1. Table1 contains the following:-One billion rows-A clustered columnstore index-A hash-distributed column named Product Key-A column named Sales Date that is of the date data type and cannot be nullThirty million rows will be added to Table1 each month.You need to partition Table1 based on the Sales Date column. The solution must optimize query performance and data loading.How often should you create a partition?

You plan to ingest streaming social media data by using Azure Stream Analytics. The data will be stored in files in Azure Data Lake Storage, and then consumed by using Azure Databricks and PolyBase in Azure Synapse Analytics.You need to recommend a Stream Analytics data output format to ensure that the queries from Databricks and PolyBase against the files encounter the fewest possible errors. The solution must ensure that the files can be queried quickly and that the data type information is retained.What should you recommend?

You have an Azure Databricks workspace that contains a Delta Lake dimension table named Table1.Table1 is a Type 2 slowly changing dimension (SCD) table.You need to apply updates from a source table to Table1.Which Apache Spark SQL operation should you use?

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:✑ Ensure that users can identify the current manager of employees.✑ Support creating an employee reporting hierarchy for your entire company.✑ Provide fast lookup of the managers' attributes such as name and job title.Which column should you add to the table?

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.

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. FactPurchase will contain the following columns.FactPurchase will have 1 million rows of data added daily and will contain three years of data.Transact-SQL queries similar to the following query will be executed daily.SELECTSupplierKey, StockItemKey, COUNT(*)FROM FactPurchaseWHERE DateKey >= 20210101AND DateKey <= 20210131GROUP By SupplierKey, StockItemKeyWhich table distribution will minimize query times?

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. FactPurchase will contain the following columns.FactPurchase will have 1 million rows of data added daily and will contain three years of data. Transact-SQL queries similar to the following query will be executed daily.SELECTSupplierKey, StockItemKey, IsOrderFinalized, COUNT(*)FROM FactPurchaseWHERE DateKey >= 20210101AND DateKey <= 20210131GROUP By SupplierKey, StockItemKey, IsOrderFinalizedWhich table distribution will minimize query times?

You plan to implement an Azure Data Lake Gen 2 storage account.You need to ensure that the data lake will remain available if a data center fails in the primary Azure region. The solution must minimize costs. Which type of replication should you use for the storage account?

You are designing the folder structure for an Azure Data Lake Storage Gen2 container.Users will query data by using a variety of services including Azure Databricks and Azure Synapse Analytics serverless SQLpools. The data will be secured by subject area. Most queries will include data from the current year or current month.Which folder structure should you recommend to support fast queries and simplified folder security?

You need to design an Azure Synapse Analytics dedicated SQL pool that meets the following requirements:-Can return an employee record from a given point in time.-Maintains the latest employee information.-Minimizes query complexity.How should you model the employee data?

You plan to implement an Azure Data Lake Storage Gen2 container that will contain CSV files. The size of the files will vary based on the number of events that occur per hour.File sizes range from 4 KB to 5 GB.You need to ensure that the files stored in the container are optimized for batch processing. What should you do?

You need to implement the surrogate key for the retail store table. The solution must meet the sales transaction dataset requirements. What should you create?

You are implementing a batch dataset in the Parquet format.Data files will be produced be using Azure Data Factory and stored in Azure Data Lake Storage Gen2. The files will be consumed by an Azure Synapse Analytics serverless SQL pool.You need to minimize storage costs for the solution. What should you do?

You are designing the folder structure for an Azure Data Lake Storage Gen2 container.Users will query data by using a variety of services including Azure Databricks and Azure Synapse Analytics serverless SQL pools. The data will be secured bysubject area. Most queries will include data from the current year or current month.Which folder structure should you recommend to support fast queries and simplified folder security?

You have an Azure Databricks workspace that contains a Delta Lake dimension table named Table1.Table1 is a Type 2 slowly changing dimension (SCD) table.You need to apply updates from a source table to Table1.Which Apache Spark SQL operation should you use?

You are designing a partition strategy for a fact table in an Azure Synapse Analytics dedicated SQL pool. The table has the following specifications:-Contain sales data for 20,000 products.-Use hash distribution on a column named ProductID.-Contain 2.4 billion records for the years 2019 and 2020.Which number of partition ranges provides optimal compression and performance for the clustered columnstore index?

You have an Azure Synapse Analytics dedicated SQL pool that contains a table named Table1. Table1 contains the following:-One billion rows-A clustered columnstore index-A hash-distributed column named Product Key-A column named Sales Date that is of the date data type and cannot be nullThirty million rows will be added to Table1 each month.You need to partition Table1 based on the Sales Date column. The solution must optimize query performance and data loading.How often should you create a partition?

You have an enterprise-wide Azure Data Lake Storage Gen2 account. The data lake is accessible only through an Azure virtual network named VNET1.You are building a SQL pool in Azure Synapse that will use data from the data lake.Your company has a sales team. All the members of the sales team are in an Azure Active Directory group named Sales. POSIX controls are used to assign the Sales group access to the files in the data lake.You plan to load data to the SQL pool every hour.You need to ensure that the SQL pool can load the sales data from the data lake.Which three actions should you perform? Each correct answer presents part of the solution.NOTE: Each area selection is worth one point.

You have an Azure Databricks workspace that contains a Delta Lake dimension table named Table1.Table1 is a Type 2 slowly changing dimension (SCD) table.You need to apply updates from a source table to Table1.Which Apache Spark SQL operation should you use?