Exam 70-767: Implementing a SQL Data Warehouse

The Exam 70-767 Implementing a SQL Data Warehouse certification by Microsoft is really rewarding in terms of entry-level tech work, learnable, and a clear path to a good career. This certification validates your proficiency in building a data warehouse with the implementation of ELTs (Extract, Transform and Load), interaction with Azure technologies like Big Data, Azure DW, maintaining the data with DQS (Data Quality Services) as well as implementing a data warehouse design. The certification is primarily introduced for BI developers, ETL administrators, and developers. It helps candidates acquire an array of knowledge in working and designing with SQL SSIS (Server Integration Services), performance, table design, and others.

Recommended Knowledge

This examination has certain objectives and it prefers candidates to have brains in topics like designing, implementing, and maintenance of a data warehouse, building data quality solutions and transforming, extracting, and loading data.

Target Audience

The Microsoft 70-767: Implementing a SQL Data Warehouse certification aims at targeting those prospective candidates who are willing to build their career in Microsoft SQL Server domain. This exam is intended for those who are ETL (Extract, transform, and load) developer or an administrator, or a business intelligence developer. Those developers who have the willingness to perform the data cleansing function additionally with ETL and data warehouse implementation. Hence, it is intended for them who create business intelligence (BI) solutions.

Exam Overview

• Exam Code: 70-767
• Number of Questions: 40-60 Multiple Choice and Multi-Response Questions
• Time Limit: 120 minutes
• Passing score: 700 (on a scale of 1-1000)
• Language: English
• Exam Type: SQL Server 2016/2017/2019
• Price: USD $165

Schedule Your Exam

Follow the steps below to schedule your Microsoft certification exam –

• Go to the Microsoft portal, to register yourself
• Click on “Schedule Exam” to schedule an exam date at your convenience
• Fill all your details asked in the next page that appears
• Select the exam delivery option on the Pearson VUE page
• Pay your exam fees. You have successfully registered for the MS 70-767 Exam

Exam Cancellation Policy

Microsoft offers candidates to cancel or reschedule their exams within a minimum of 24 hours prior to the exam date. However, to prevent any cancellation fee, you must cancel or reschedule your exam, at least 6 business days prior to the date scheduled for your exam. Also, if you fail to appear in the exam, then you will not receive any refund of the exam fee.

Exam Result

Right after successfully completing your Exam 70-767 Implementing a SQL Data Warehouse, you will be notified of your pass or fail status within a few minutes of completing your exam. Also, printed reports providing your exam score and feedback on your performance will also be provided to you. Your score will be forwarded to Microsoft within five business days.
As far as Beta exam results are concerned, your result will be visible on your Microsoft transcript. However, you will see your result only if you’ve achieved a passing score. Also, this result will be published on Pearson VUE’s site within 14 business days, after the live exam is published.
Note – If you are able to clear the beta exam, you will earn Microsoft credit for that exam and will also receive a resulting certification. Moreover, you are not required to retake the exam in its live version after clearing its beta version.

Exam Retake Policy

If anyhow you are not able to achieve a passing score in the Microsoft 70-767 exam. However, you will have to wait for a minimum of 24 hours before you become eligible for a retake. Failure in the second attempt will result in a waiting time of 14 days before rescheduling your third attempt. The waiting period for the fourth and the fifth attempts will also be 14 days. All candidates are allowed a maximum of five attempts per year.

For More Details See – Microsoft 70-767: Implementing a SQL Data Warehouse FAQ

Course Outline

The Microsoft 70-767 Exam covers the following domains –

Design, implement, and maintain a data warehouse (35-40%)

Designing and implementing dimension tables

• Design shared and conformed dimensions
• determine support requirements for slowly changing dimensions (Microsoft Documentation: Slowly Changing Dimension Transformation)
• determine attributes
• design hierarchies (Microsoft Documentation: Create Hierarchies)
• determine star or snowflake schema requirements (Microsoft Documentation: Dimensions – Introduction)
• determine the granularity of the relationship by using fact tables (Microsoft Documentation: Dimension Relationships)
• determine auditing or lineage requirements
• determine keys and key relationships for a data warehouse
• implement dimensions
• implement data lineage of a dimension table

Designing and implementing fact tables

• Identify measures
• identify dimension table relationships (Microsoft Documentation: Defining a Fact Relationship)
• create composite keys (Microsoft Documentation: Keys)
• design a data warehouse that supports many-to-many relationships (Microsoft Documentation: Define a Many-to-Many Relationship and Many-to-Many Relationship Properties)
• implement semi-additive measures (Microsoft Documentation: Define Semiadditive Behavior)
• implement non-additive measures

Designing and implementing indexes for a data warehouse workload

• Design an indexing solution (Microsoft Documentation: SQL Server Index Architecture and Design Guide)
• select appropriate indexes
• implement clustered, non-clustered, filtered, and columnstore indexes (Microsoft Documentation: Columnstore indexes: Overview)

Designing storage for a data warehouse

• Design an appropriate storage solution, including hardware, disk, and file layout (Microsoft Documentation: Storage and SQL Server capacity planning and configuration)

Designing and implementing partitioned tables and views

• Design a partition structure to support a data warehouse (Microsoft Documentation: Partitioning tables in Synapse SQL pool)
• implement sliding windows (Microsoft Documentation: Sliding Window)
• implement partition elimination
• design a partition structure that supports the quick loading and scale-out of data

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

Designing 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 (Microsoft Documentation: Control Flow, Precedence Constraints)
• create variables and parameters (Microsoft Documentation: Create Resource Manager parameter file)
• create checkpoints, sequence and loop containers, and variables in SSIS (Microsoft Documentation: For Loop Container, Debugging Control Flow, Integration Services Containers, Restart Packages by Using Checkpoints)
• implement data profiling, parallelism, transactions, logging, and security (Microsoft Documentation: Query Processing Architecture Guide, Data Profiling Task)

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

• Implement slowly changing dimension, fuzzy grouping, fuzzy lookup, audit, blocking, nonblocking, and term lookup transformations (Microsoft Documentation: Fuzzy Lookup Transformation, Fuzzy Grouping Transformation, Lookup and Fuzzy Lookup Components with Error Output)
• map columns (Microsoft Documentation: Map Columns to Composite Domains, Functions.Map(Column[]) Method)
• determine the appropriate transform object for a given task (Microsoft Documentation: Transformation Custom Properties)
• determine appropriate scenarios for Transact-SQL joins vs. SSIS lookup (Microsoft Documentation: Joins (SQL Server), Lookup Transformation)
• design table loading by using bulk loading or standard loading (Microsoft Documentation: Best practices for loading data using Synapse SQL pool)
• remove extra rows or bad rows by using de-duplication

Implement an ETL solution that supports incremental data extraction

• Design fact table patterns (Microsoft Documentation: Design tables in Synapse SQL pool)
• enable Change Data Capture (Microsoft Documentation: Enable and Disable Change Data Capture (SQL Server))
• create a SQL MERGE statement (Microsoft Documentation: MERGE (Transact-SQL))

Implement an ETL solution that supports incremental data loading

• Design a control flow to load change data (Microsoft Documentation: Change Data Capture (SSIS), Control Flow)
• load data by using Transact-SQL Change Data Capture functions (Microsoft Documentation: About Change Data Capture (SQL Server), Change Data Capture Functions (Transact-SQL))
• 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 (Microsoft Documentation: Integration Services (SSIS) Logging)
• implement error handling for data types (Microsoft Documentation: Error Handling in Data)
• implement breakpoints (Microsoft Documentation: Use breakpoints in the Visual Studio debugger)
• add data viewers (Microsoft Documentation: Data Viewer, Debugging Data Flow)
• profile data with different tools (Microsoft Documentation: Data Profiling Task
• perform batch clean-up

Deploy and configure SSIS packages and projects

• Create an SSIS catalog (Microsoft Documentation: SSIS Catalog)
• deploy packages by using the deployment utility, SQL Server, and file systems (Microsoft Documentation: Deploy Packages with SSIS, Deploy Integration Services (SSIS) Projects and Packages)
• run and customize packages by using DTUTIL (Microsoft Documentation: dtutil Utility)

Build data quality solutions (15-20%)

Create a knowledge base

• Create a Data Quality Services (DQS) knowledgebase (Microsoft Documentation: DQS Knowledge Bases and Domains)
• determine appropriate use cases for a DQS knowledge base (Microsoft Documentation: DQS Knowledge Bases and Domains)
• perform knowledge discovery (Microsoft Documentation: Perform Knowledge Discovery)
• perform domain management

Maintain data quality by using DQS

• Add matching knowledge to a knowledge base (Microsoft Documentation: Adding Knowledge to a Knowledge Base)
• prepare a DQS for data deduplication (Microsoft Documentation: Data Quality Services Concepts)
• create a matching policy (Microsoft Documentation: Create a Matching Policy)
• clean data by using DQS knowledge (Microsoft Documentation: Cleanse Data Using DQS (Internal) Knowledge)
• clean data by using the SSIS DQS task (Microsoft Documentation: DQS Cleansing Transformation)
• install DQS (Microsoft Documentation: Install Data Quality Services)

Implement a Master Data Services (MDS) model

• Install MDS (Microsoft Documentation: Master Data Services Installation and Configuration)
• implement MDS
• create models, entities, hierarchies, collections, and attributes (Microsoft Documentation: Models (Master Data Services))
• define security roles
• import and export data (Microsoft Documentation: Import and Export Data with the SQL Server Import and Export Wizard)
• create and edit a subscription
• implement entities, attributes, hierarchies, and business rules (Microsoft Documentation: Business Rule Examples (Master Data Services), Master Data Services Overview (MDS))

Manage data by using MDS

• Use MDS tools (Microsoft Documentation: Master Data Services Overview (MDS))
• use the Master Data Services Configuration Manager (Microsoft Documentation: Master Data Services Installation and Configuration)
• create a Master Data Manager database and web application (Microsoft Documentation: Create a master data manager web application (Master Data Services))
• deploy a sample model using MDSModelDeploy.exe (Microsoft Documentation: Deploy a Model Deployment Package by Using MDSModelDeploy)
• use the Master Data Services web application
• use the Master Data Services Add-in for Excel (Microsoft Documentation: Master Data Services Add-in for Microsoft Excel)
• create a Master Data Management hub (Microsoft Documentation: Master Data Services)
• stage and load data
• create subscription views (Microsoft Documentation: Create a Subscription View to Export Data (Master Data Services))

Preparation Guide for Microsoft Exam 70-767: Implementing a SQL Data Warehouse

Instructor-led Training

This five days instructor-led training course equips the participants with the skills and studies to provision a Microsoft SQL Server database. Through this 14 modules course, a learner can focus on SQL Server provision both on-premise and in Azure and goes on to cover installing from new and migrating from an existing install. Through this course a learner gains how to implement a logical design for a data warehouse, describing the core elements of a data warehousing solution and explaining the important hardware considerations required to build a data warehouse. The participants must focus on hands-on work creating BI solutions.

Course 20767-C: Implementing a SQL Data Warehouse

Microsoft Books

Microsoft provides reference books that can be helpful while studying for the exam. These books contain various useful resources that can be used while studying. Visit Microsoft Press books, to find relevant books to get a deeper insight into the exam objectives, and hence crack the test, and earn your certification. Some highly recommended books include:

Exam Ref 70-767 Implementing a SQL Data Warehouse

Direct from Microsoft, this Exam Ref is an official study guide for the new Microsoft 70-767 Implementing a SQL Data Warehouse certification exam. Professional and advanced level preparation is provided by this book to broaden the horizon of the candidate`s skills and maximize their chances of scoring high in the examination.

The book is specially written to lay emphasis on specific areas of expertise modern IT professionals that need to successfully build modern data warehouses supporting advanced business intelligence solutions.

Microsoft Community

Discussion is a great way for people to connect over shared experiences. This is a great way to ensure that the members stay engaged. A key part of your communication plan could be an online forum. Rather than searching through emails, you can simply ask a question within the forum and see everyone’s responses in a clear, chronological order. 75% of the employer rate teamwork as “very important.” Collaboration can help bring out the creative sides of your team members, as you can work together and bring new ideas to the table. It gives you a platform to express your views and creativity and hence train you. Therefore, joining online forums will ultimately help you to achieve the training experience necessary to crack the Microsoft 70-767 Certification Exam.

One big advantage offered by online study forums is that you get to know your stand in the competition. Furthermore, you also get to know how your competitors are preparing, and this will boost up your enthusiasm, and keep your motivation throughout the journey.

Practice Tests

You must take practice tests to build your own exam-taking endurance. The more you practice, the easier you’ll find taking the actual exam. Solving practice tests will help you learn the quirks of the actual exam. As, these tests are designed to identify what you know and what you don’t. This will help you become more comfortable with the process, and also helps you prepare your own study strategies. The results of practice tests can confirm that you’re as knowledgeable as you think, or that you need to step up your studying game.

One way to ensure maximum comfort and minimal anxiety are to practice. The more often you sit for a practice test, the more natural it will feel the day of the actual test. This will also assist in tracking your progress, to know which study strategies are helpful and which should be abandoned. Solving Practice tests can provide you with the confidence you need to be stress-free.

Enrich and Upgrade your profile to pass Microsoft 70-767: Implementing a SQL Data Warehouse exam with hundreds of practice Exams. Start Preparing Now!