Preparing for and Taking Microsoft Exam 70-767 (Implementing a Data Warehouse using SQL)

Microsoft Exam 70-767: Implementing a Data WarehouseAfter taking a break from exams and certifications for a few years, I recently decided to resume studying. On May 29th, I shared my Updated Microsoft Certification Goals. A week later, on June 5th, I achieved my first goal! I passed Microsoft Exam 70-767: Implementing a Data Warehouse using SQL. By passing this exam, I became a Microsoft Certified Solutions Expert (MCSE): Data Management and Analytics. Woohoo :)

In this blog post, I share how I prepared for the exam and what my experience was like on the day of the exam. While I only shared my updated goals last week, I spent weeks before that focused on studying. More importantly, I have had real experience working hands-on in projects for several years. Without this experience, I don’t think I would have been able to pass this exam – at least not on the first attempt.

(If you are simply looking for information about exam questions and answers, please leave. Sharing or using that kind of information is a violation of the Microsoft Exam Policy and Non-Disclosure Agreement.)

Preparing for Microsoft Exam 70-767: Implementing a Data Warehouse using SQL

Even though I have worked as a SQL Server, SSIS and Data Warehouse architect and developer for many years, getting hands-on experience in multiple projects, I had to spend several weeks studying for this exam. I have had very little experience with hardware, designing storage solutions and working with DQS and MDS, for example, so a lot of my time was spent on those topics.

Because I like to be organized and structured (that’s why I work with data!) I decided to study in a very specific way:

  1. Complete four free online edX courses
  2. Go through every single skill measured and ensure I knew how to explain the concept or complete the task
  3. Take the official practice test until my average score was over 95%

While studying, I continuously referenced the SQL Server Docs and took detailed study notes.

Online Training for Exam 70-767

Microsoft recommends three online courses in their exam preparation options. In addition to the three recommended courses, I found a fourth course that was relevant for me. In total, I completed these four edX courses:

I skipped most of the demos and labs for the Data Warehouse and ETL courses because I felt I already had a good understanding of the topics. For the DQS and MDS courses, I worked my way through all the labs. During all courses, I watched the videos in 1.5x or 2.0x speed :) That drastically cut down the overall time spent on the courses!

In general, I was very satisfied with these courses. For me, it was a good strategy to start with these structured courses with videos and labs. It was a nice way of refreshing my memory on topics I already knew fairly well, and gave me a solid overview of topics I didn’t know as well. The labs gave me hands-on experience I had not previously had in projects.

Total time spent on online training, labs, and corresponding research: 35-40 hours

Skills Measured in Exam 70-767

After completing each course, I went through the corresponding list of skills measured to ensure I hadn’t missed anything. For each skill, checked that I could explain the topic (for example Sliding Windows) or complete the task (for example Implement a Clustered Columnstore Index). If there were multiple ways of doing something, I practiced all options: using the GUI, stepping through wizards and/or writing code.

As a starting point, you can look at my final checklist below. This is based on the skills measured, and expanded to include my own notes and reminders. Since the skills measured may change, make sure you always reference the latest list on the Microsoft website and add your own points.

↓ Click to open Cathrine's skills measured checklist

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

Dimensions

  • Conformed and Junk Dimensions
  • Slowly Changing Dimensions (SCD1, SCD2, SCD3)
  • Attributes and Hierarchies
  • Star and Snowflake Schema
  • Auditing and Lineage Requirements
  • Primary Keys, Surrogate Keys and Business Keys

Facts

  • Dimension Table Relationships
  • Primary Keys, Foreign Keys and Composite Keys
  • Many-to-Many Relationships and Bridge Tables
  • Additive, Semi-Additive and Non-Additive Measures

Indexes

  • Rowstore Indexes: Clustered vs. Non-Clustered
  • Columnstore Indexes: Clustered vs. Non-Clustered
  • Filtered Indexes
  • Unique, Covered, Composite Indexes

Storage and Hardware

  • Hardware, RAID (0, 1, 2, 3, 4, 5, 6, 10), Windows Storage Spaces, SAN, NAS
  • Disk Size, Estimating Space, Compression (Row, Page, Columnstore)
  • Filegroups, Data Files, Log Files, TempDB

Partitioned Tables and Views

  • Partition Schema and Partition Function
  • Partition Switching
  • Merge and Split
  • Sliding Windows
  • Partition Elimination
  • Partitioned Views

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

SSIS Control Flow

  • Containers (Sequence, For Loop, Foreach Loop)
  • Tasks (types, functionality, usage)
  • Precedence Constraints
  • Variables
  • Parameters
  • Checkpoints
  • Data Profiling
  • Parallelism
  • Transactions
  • Logging
  • Security

SSIS Data Flow

  • Slowly Changing Dimension
  • Fuzzy Grouping and Fuzzy Lookup
  • Audit
  • Blocking vs. Non-Blocking
  • Term Lookup
  • T-SQL vs. SSIS Lookup
  • Bulk Loading vs. Standard Loading
  • Deduplication

SSIS Debugging

  • Performance
  • Connectivity
  • Execution
  • Debugger
  • Logging for Package Execution
  • Error Handling for Data Types
  • Breakpoints
  • Data Viewers
  • Profile Data
  • Batch Clean-Up

Incremental Loading

  • Incremental Load Fact Table Pattern
  • Change Tracking (T-SQL syntax, SSIS pattern)
  • Change Data Capture (T-SQL syntax, SSIS pattern and components)
  • MERGE statement (T-SQL syntax)

SSIS Deployment

  • SSIS Catalog (views, stored procedures)
  • Deployment options (project deployment, package deployment, where to store packages)
  • SQL Server Agent Jobs
  • Dtutil (commands and options)
  • Dtexec (commands and options)

Build data quality solutions (15–20%)

Data Quality Services (DQS)

  • Install DQS
  • Create DQS Knowledge Base
  • DQS Knowledge Base Use Cases
  • Knowledge Discovery
  • Domain Management

Maintain Data Quality using DQS

  • Add matching knowledge to knowledge base
  • Prepare data for deduplication
  • Create matching policy
  • Clean data using DQS knowledge base
  • Clean data using SSIS DQS task

Master Data Services (MDS) Models

  • Install MDS
  • Implement MDS
  • Models
  • Entities
  • Hierarchies
  • Collections
  • Attributes
  • Security Roles
  • Import and export data
  • Business rules

Manage Data using MDS

  • Use MDS tools
  • Use MDS Configuration Manager
  • Create MDM database and web app
  • Use MDS add-in for Excel
  • Deploy sample model using MDSModelDeploy.exe
  • Stage and load data
  • Create subscription views
  • Manage entity syncing

Total time spent on additional studying based on skills measured: 8-10 hours

Practice Test for Exam 70-767

Finally, I invested in an official practice test from MeasureUp. This was the only thing I paid for while studying for this exam, and absolutely worth the money.

The practice test contained 150 questions covering all skills measured and all question types found in the actual exam. The question types include case studies, repeated answer choices, single choice, multiple choice, dropdown lists, option buttons, drag and drop snippets, as well as questions simulating the GUI or with attached screenshots. Getting familiar with the question types and learning how to interpret questions was one of the most valuable things for me.

You can see examples of exam question types in these videos from Microsoft Learning:

 

There are two modes in the practice test: Study Mode and Certification Mode. In Study Mode, you can choose the number of questions, the question types, which exam objectives you want questions from, and whether or not to automatically display answers. For each question, you will get detailed explanations of the correct and incorrect answers. These explanations also link to external articles and documentation going into more detail. The Study Mode settings allow you to choose specific areas to focus on. The Certification Mode simulates an actual exam. You get the same number of questions, the same amount of time, and you cannot view any answers until the test is completed.

Using the Practice Test to study for Exam 70-767

Since I had already gone through the courses and skills measured, I thought I was well prepared for the practice test. Boy, was I wrong :) On my first couple of attempts, my average was only between 20-40%! There were two main things that surprised me. The first was the wording of the questions. In many cases, you get much more information than you need to answer the question. In other cases, you don’t specifically get the information you need, but you have to interpret the question and find the most appropriate solution. The other was the level of detail in many of the questions. I knew the concepts and workflow, but not the specific syntax or options.

After miserably failing a few practice tests, I decided to roll up my sleeves and get to work. I started a Study Mode test and went through every single question in detail. I looked at the correct and incorrect answers, read the referenced articles and documentation, took even more detailed study notes, and experimented in my lab environment. Yes, some of it was simply memorizing options and syntax. No, I don’t think that’s the best way of measuring skills. The things you memorize are the things you can look up in less than a minute. The real value came from digging into the topics and really understanding why one answer was correct and another incorrect.

On the day before my exam, I completed three Certification Mode tests with an average score of over 95%. But beware. After going through each question multiple times, you may fall into the trap of just memorizing the correct answer. Make sure you actually know why it’s the correct answer and why the other answers are incorrect :)

Total time spent on practice tests: 15-20 hours

Taking Microsoft Exam 70-767: Implementing a Data Warehouse using SQL

The first step of actually taking the exam is to accept and sign the Microsoft Exam Policy and Non-Disclosure Agreement. Because of that, I obviously can’t say much about the exam itself :) Instead, I want to share a few of my experiences taking the exam.

Question text and question types

Investing in a practice test, getting familiar with the question types, and learning how to interpret questions was one of the most important ways to prepare. (The most important thing is to know the content, obviously!) The questions often have a lot of text, irrelevant information for that specific question, or information that can confuse or distract you. I consider myself a good reader and fluent in English, but I still spent a lot of time just reading and re-reading the questions. The practice test helped me learn how to pick out the relevant and important information and focus on the question itself.

Exam time

I thought I had 150 minutes for the exam. Turns out I only had 120 minutes to answer all the questions. The remaining 30 minutes are for reading instructions, signing the NDA, and commenting on questions after you finish your exam (if you choose to do so). I realized afterward that this is clearly stated in the Exam FAQ, but I missed it. Oops! So for your convenience, remember that you have 150 minutes in seat time, but only 120 minutes exam time. But don’t worry, during the exam, you have a huge timer on the screen constantly counting down and showing you the remaining time :)

(Yes, that last sentence was slightly sarcastic. That timer can feel like the Big Timer of Doom!)

Planning

Even though I’m a good reader and I felt prepared for the overload of text, I nearly ran out of time. Yikes! There is a lot of information to process. You might get stuck on a few questions. You probably want to review some of the questions at the end. When you start your exam, look at how many total questions you have to answer. Make sure you have enough time for all questions. According to Microsoft, exams have 40-60 questions. That means you have 2-3 minutes per question. Let that sink in. Two to three minutes. To read, understand, interpret, compare, consider and answer. That is not a lot of time! I had around ten minutes left when I had answered all the questions, and three minutes left when I had reviewed the questions I had marked. My advice is that if you feel stuck on a question, mark it for review and return to it later. It’s better to get that one question wrong than not having time to answer the last five questions.

Help! I don’t know the answer?

Even after all your hours of preparation, you might come across a question that takes you by surprise. It could be something you have never had to look at before, or it could be a question on something that is not covered in the skills measured. (Don’t forget that Microsoft explicitly states: “Please note that the questions may test on, but will not be limited to, the topics described in the bulleted text.”) I got a few of these questions. My best advice is to use the elimination method to narrow down the options to the most likely answer, and if that doesn’t help… take your best guess :) Pick the answer that sounds most likely or logical, and move on to the next question. Remember that you don’t need 100% to pass the exam, so don’t worry too much about it.

Don’t overthink!

Finally, don’t overthink it. There are no “it depends” answers in the Microsoft certification exam world. Even though I wish I could add “yes, but, if…” to many of my answers, you can’t. You need to go in with a mindset that there is either a right answer, or a best answer. Read the text and stay as objective as possible. Use the facts in the question to make your decision, don’t fall into the “it depends” trap. If you’re unsure, pick the best answer. Again, don’t get stuck on any questions. Go back and review them at the end. You may even answer some other questions in the meantime that may trigger your memory.

Summary

In total, I spent around 60-70 hours preparing for Microsoft Exam 70-767 (Implementing a Data Warehouse using SQL):

  • Courses and Labs (35-40 hours)
  • Skills Measured (8-10 hours)
  • Practice Test (15-20 hours)

That does not include the time spent getting hands-on experience working on real projects. I also chose not to take any in-person classes or buy any books this time. If you do that instead, or in addition, it will of course affect the total time spent. Finally, you may have to spend half or double the time I did, depending on your background and experience. Regardless of your background, this is a difficult exam that requires a lot of dedication and effort.

My key takeaway and final piece of advice is: work through the entire list of skills measured. It doesn’t matter how you choose to do it, but do it. Not only will you be prepared for the exam, but you will be confident that you have the skills needed to implement a Data Warehouse on SQL Server with confidence.

Good luck! :)

Who is Cathrine Wilhelmsen?

Cathrine is a Microsoft Data Platform MVP, BimlHero, Microsoft Certified Solutions Expert, author, speaker, blogger and chronic volunteer who loves teaching and sharing knowledge. She works as a consultant, architect and developer, focusing on Data Warehouse and Business Intelligence projects. She loves sci-fi, chocolate, coffee, craft beers, ciders, cat gifs and smilies :)

5 thoughts on “Preparing for and Taking Microsoft Exam 70-767 (Implementing a Data Warehouse using SQL)”

I always enjoy reading your blog. This post in particular has encouraged me to take another look at the database focused exams. Thanks for sharing your objective view; not a cheat sheet ;)

Thank you so much for this thorough blog! I know it will help others who are pursuing their certification.

Congratulations again for getting your MCSE!

Thanks for the blog and great job! I really liked the tips and advise and this will help to achieve my goals!

I am considering this exam. Work paid for me to take the course and it came with a free voucher so it doesn’t hurt to try right? Anyway this is a very detailed post and will be invaluable to me as I prepare for my attempt.
Thanks!

This is very helpful. Thank you for sharing this.

Share Your Thoughts?

This site uses Akismet to reduce spam. Learn how your comment data is processed.