767

Implementing a SQL Data Warehouse

Exam number: 70-767

Exam title: Implementing a SQL Data Warehouse

Publish date:

GUID:

Language(s) this exam will be available in:

Audience (IT professionals, Developers, Information workers, etc.):

This document shows changes to objectives and functional groupings. These

changes are effective as of March 2017.

Audience Profile

This exam is intended for extract, transform, and load (ETL) and data warehouse developers who create business

intelligence (BI) solutions. Their responsibilities include data cleansing as well as ETL and data warehouse

implementation.

Skills measured

warehouse; implement dimensions; implement data lineage of a dimension table

Design and implement fact tables

Identify measures; identify dimension table relationships; create composite keys; design a

data warehouse that supports many-to-many relationships; implement semi-additive

measures; implement non-additive measures

Design and implement indexes for a data warehouse workload

Design an indexing solution; select appropriate indexes; implement clustered, non-clustered,

filtered, and columnstore indexes

Design storage for a data warehouse

Design an appropriate storage solution, including hardware, disk, and file layout

the data warehouse; grow, shrink, and pause the data warehouse

Extract, transform, and load data (40-45%)

Design and implement an extract, transform, and load (ETL) control flow by using a SQL Server

Integration Services (SSIS) package

Design and implement ETL control flow elements, including containers, tasks, and

precedence constraints; create variables and parameters; create checkpoints, sequence and

loop containers, and variables in SSIS; implement data profiling, parallelism, transactions,

logging, and security

Design and implement an ETL data flow by using an SSIS package

Design fact table patterns; enable Change Data Capture; create a SQL MERGE statement

Implement an ETL solution that supports incremental data loading

Design a control flow to load change data; load data by using Transact-SQL Change Data

Capture functions; load data by using Change Data Capture in SSIS

Debug SSIS packages

Fix performance, connectivity, execution, and failed logic issues by using the debugger;

enable logging for package execution; implement error handling for data types; implement

breakpoints; add data viewers; profile data with different tools; perform batch clean-up

Deploy and configure SSIS packages and projects

Blob service by using Transact-SQL; import data from Hadoop or blobs as regular SQL Server

tables; export data to Hadoop or the Azure Blob service

Extract, transform, and load data from SQL Data Warehouse by using Polybase

Integrate Azure SQL Data Warehouse with on-premises data warehouses; implement bi-

directional data synchronization between Azure and on-premises systems; load data into SQL

Data Warehouse from Polybase; design an incremental load strategy by using Polybase and

the Azure Blob service

Design and implement an Azure SQL Data Warehouse

Create a new Azure SQL Data Warehouse database by using the Azure portal; create an Azure

SQL Data Warehouse database by using Transact-SQL; select the appropriate method to load

Create a Data Quality Services (DQS) knowledge base; determine appropriate use cases for a

DQS knowledge base; perform knowledge discovery; perform domain management

Maintain data quality by using DQS

Add matching knowledge to a knowledge base; prepare a DQS for data deduplication; create

install DQS

Implement a Master Data Services (MDS) model

Install MDS; implement MDS; create models, entities, hierarchies, collections, and attributes;

define security roles; import and export data; create and edit a subscription; implement

entities, attributes, hierarchies, and business rules

Manage data by using MDS

Use MDS tools; use the Master Data Services Configuration Manager; create a Master Data

Manager database and web application; deploy a sample model using MDSModelDeploy.exe;