Microsoft 70-767 Actual Free Exam Questions & Community Discussion

You deploy a Microsoft Azure SQL Data Warehouse instance. The instance must be available eight hours each day.
You need to pause Azure resources when they are not in use to reduce costs.
What will be the impact of pausing resources? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.

You manage the data warehouse for a large retail company. The company has many store locations. Each location runs their own independent sales systems. They report daily sales and receipt information to a central system every night. You plan to load data from all locations into a fact table named fact.Sales by using SQL Server Integration Services (SSIS) packages. You create the following Transact-SQL statement:

You need to select the columns for the primary key and clustered column key. The keys must meet the following requirements:
* Prevent duplicate rows from being entered by a single system.
* Allow for point lookups of a single sale.
* Minimize storage requirements for nonclustered indexes.
* Store rows in the order that they are inserted into the table.
How should you configure the keys? To answer, select the appropriate column type in the answer area.

You are developing a Microsoft SQL Server Integration Services (SSIS) package to incrementally load new and changed records from a data source.
The SSIS package must load new records into Table1 and updated records into Table1_Updates. After loading records, the package must call a Transact-SQL statement to process updated rows according to existing business logic.
You need to complete the design of the SSIS package.
Which tasks should you use? To answer, drag the appropriate SSIS objects to the correct targets. Each SSIS object 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.

You are designing a warehouse named DW1.
A table named Table1 is partitioned by using the following partitioning scheme and function.

Reports are generated from the data in Table1.
You need to ensure that queries to DW1 return results as quickly as possible.
Which column should appear in the WHERE statement clause of the query?

Note: This question is part of a series of questions that use the same or similar answer choices. An answer choice may be correct for more than one question in the series. Each question is independent of the other questions in this series. Information and details provided in a question apply only to that question.
You are developing a Microsoft SQL Server Integration Services (SSIS) package.
You need to use XPath to extract information from documents.
Which SSIS Toolbox item should you use?

Note: This question is part of a series of questions that use the same or similar answer choices. An answer choice may be correct for more than one question in the series. Each question is independent of the other questions in this series. Information and details provided in a question apply only to that question.
You are a database administrator for an e-commerce company that runs an online store. The company has three databases as described in the following table.

You plan to load at least one million rows of data each night from DB1 into the OnlineOrder table. You must load data into the correct partitions using a parallel process.
You create 24 Data Flow tasks. You must place the tasks into a component to allow parallel load. After all of the load processes compete, the process must proceed to the next task.
You need to load the data for the OnlineOrder table.
What should you use?

You have a data warehouse that contains a fact table named Table1 and a Product table named Dim1. Dim1 is configured as shown in the following table.

You are adding a second OLTP system to the data warehouse as a new fact table named Table2. The Product table of the OLTP system is configured as shown in the following table

You need to modify Dim1 to ensure that the table can be used for both fact tables.
Which two actions should you perform? Each correct answer presents part of the solution.
NOTE: Each correct selection is worth one point.

Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution.
After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.
Each night you receive a comma separated values (CSV) file that contains different types of rows. Each row type has a different structure. Each row in the CSV file is unique. The first column in every row is named Type. This column identifies the data type.
For each data type, you need to load data from the CSV file to a target table. A separate table must contain the number of rows loaded for each data type.
Solution: You create a SQL Server Integration Services (SSIS) package as shown in the exhibit. (Click the Exhibit tab.)

Does the solution meet the goal?

Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution.
After you answer a question in this sections, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.
You have the following line-of-business solutions:
* ERP system
* Online WebStore
* Partner extranet
One or more Microsoft SQL Server instances support each solution. Each solution has its own product catalog.
You have an additional server that hosts SQL Server Integration Services (SSIS) and a data warehouse. You populate the data warehouse with data from each of the line-of-business solutions. The data warehouse does not store primary key values from the individual source tables.
The database for each solution has a table named Products that stored product information. The Products table in each database uses a separate and unique key for product records. Each table shares a column named ReferenceNr between the databases. This column is used to create queries that involve more than once solution.
You need to load data from the individual solutions into the data warehouse nightly. The following requirements must be met:
* If a change is made to the ReferenceNr column in any of the sources, set the value of IsDisabled to True and create a new row in the Products table.
* If a row is deleted in any of the sources, set the value of IsDisabled to True in the data warehouse.
Solution: Perform the following actions:
* Enable the Change Tracking for the Product table in the source databases.
* Query the CHANGETABLE function from the sources for the updated rows.
* Set the IsDisabled column to True for the listed rows that have the old ReferenceNr value.
* Create a new row in the data warehouse Products table with the new ReferenceNr value.
Does the solution meet the goal?

You have a database that contains a table named Email. Change Data Capture (CDC) is enabled for the table.
You have a Microsoft SQL Server Integration Services (SSIS) package that contains the Data Flow task shown in the Data Flow exhibit. (Click the Exhibit button.)

You have an existing CDC source as shown in the CDC Source exhibit (Click the Exhibit button)


and a CDC Splitter transform as shown in the CDC Splitter exhibit. (Click the Exhibit button.)


You need to perform an incremental import of customer email addresses. Before importing email addresses, you must move all previous email addresses to another table for later use.
For each of the following statements, select Yes if the statement is true. Otherwise, select No.
NOTE: Each correct selection is worth one point.

You have a database that includes a table named dbo.Sales. The table contains two billion rows. You created the table by running the following Transact-SQL statement:

You run the following queries against the dbo.Sales. All the queries perform poorly.

The ETL process that populates the table uses bulk insert to load 10 million rows each day. The process currently takes six hours to load the records.
The value of the Refund column is equal to 1 for only 0.01 percent of the rows in the table. For all other rows, the value of the Refund column is equal to 0.
You need to maximize the performance of queries and the ETL process.
Which index type should you use for each query? To answer, select the appropriate index types in the answer area.
NOTE: Each correct selection is worth one point.

A company plans to load data from a CSV file that is stored in a Microsoft Azure Blob storage container.
You need to load the data.
How should you complete the Transact-SQL statement? To answer, drag the appropriate Transact-SQL segments to the correct locations. Each Transact-SQL segment 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.

Note: This question is part of a series of questions that use the same or similar answer choices. An answer choice may be correct for more than one question in the series. Each question is independent of the other questions in this series. Information and details provided in a question apply only to that question.
You are loading data from an OLTP database to a data warehouse.
The OLTP database includes a table for sales data and a table for refund data.
The data warehouse contains a single table for all the sales and refund data.
Which component should you use to load the data to the data warehouse?

You have a Microsoft SQL Server Integration Services (SSIS) package that includes the control flow shown in the following diagram.

You need to choose the enumerator for the Foreach Loop container.
Which enumerator should you use?