Skip to content

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

Microsoft Exam 70-767: Implementing a Data Warehouse

After taking a break from exams and certifications for a few years, I decided to resume studying. On May 29th, 2018, I shared my Updated Microsoft Certification Goals. A week later, on June 5th, 2018, I achieved my first goal! I passed Microsoft Exam 70-767: Implementing a Data Warehouse using SQL. 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 online training: 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:

1. Microsoft Certification Exam: Active Screen Tutorial

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):

  • Online Training (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! :)

About the Author

Cathrine Wilhelmsen is a Microsoft Data Platform MVP, BimlHero Certified Expert, Microsoft Certified Solutions Expert, international speaker, author, blogger, and chronic volunteer who loves teaching and sharing knowledge. She works as a Senior Business Intelligence Consultant at Inmeta, focusing on Azure Data and the Microsoft Data Platform. She loves sci-fi, chocolate, coffee, craft beers, ciders, cat gifs and smilies :)

Comments

Hi! This is Cathrine. Thank you so much for visiting my blog. I'd love to hear your thoughts, but please keep in mind that I'm not technical support for any products mentioned in this post :) Off-topic questions, comments and discussions may be moderated. Be kind to each other. Thanks!

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.

I passed 767 today. Not easy, but not exceptionally hard. I didn’t expect to see <redacted> related questions, as it was not mentioned in the exam areas…

The Skills Measured clearly states: “Please note that the questions may test on, but will not be limited to, the topics described in the bulleted text.”

(I edited your comment as the exam policies say you are not to share anything from the exam.)

Thank you for sharing this post. It will for sure be a benefit for my preparation.
In regard to the exam, will there be anything about Azure?

Microsoft writes: “Please note that the questions may test on, but will not be limited to, the topics described in the bulleted text.” So yes, there could be questions about Azure :)

Thanks for sharing your post. I will check out the EdX courses. Thanks!

It is amazing that you were able to do all of this training in a week. Four EdX Courses! Amazing!~

Hello there. Thanks for the much needed advice on what to study for this exams. Just writing for some clarification. You said you completed four free online edX courses. Could you mention these courses by name please? Thanks again.

Hi Yauri, the courses are all listed with names, codes, and links under “Online Training for Exam 70-767” :)

Hello Cathrine, I would like to enquire about taking the Microsoft Exam 70-767: Implementing a Data Warehouse using SQL. I have no working experience in Data Warehousing but would eventually like to work in the field Data Analytics. I do however have knowledgeable experience in Database Management. Would taking this exam be worth it considering my lack of working experience in Data Warehousing? Thank you.

Hi
I do not know if it is violating the rules, but could you tell us how many questions there are for this exam?
Thank you (-:
Jesper

According to Microsoft, most exams contain between 40-60 questions :)

I past the exam Today with a good margin but was surprised how difficult it was.
A few of the questions i simpely had no Idea of the answer and let them unaswer.
Thank You , Cathrine for you blog, It helped a Lot and like you my next goal is know 70-768

Hi,
could you please give me the link of The practice test?

I already have, it’s right there in the blog post. Just scroll to “Practice Test for Exam 70-767”.

Thank you so much

Thanks for this post – it will help so much in my own prep to take this test.

Hi,
Not sure if you can answer this, but I just completed my MCSA with 70-463, how similar is this exam to it. I thought I would take to complete the MCSE. Thanks for sharing all of the information you posted above, I will definitely use it to help prepare.

This page has been super helpful in so far as approaching exam 70-767. Thank you for taking the time to share and care! I have a question with regards to hardware, designing storage solutions. Similar to you, I have little to no experience in this area and feeling totally nervous about it. I am curious how you went about preparinging for this section. How deep did you go to learn this section? What material did you use? Of course I am well aware of the MS syllabus, however it is super high level and I am a little anal by nature Any info will help.

what does the Access Duration of the Practice test mean?
Does it mean the Access of the Download or the access of the Use ?

Hi, Catherine. Just curious… What server environments/ software did you use to conduct that labs? How much did that cost? I’ve created an Azure free account (which lasted 30 days and used $150 of credits). This worked out okay for the DW course (course DAT216x), but I still have 3 other courses to take and I’m nervous about presenting cost to management for Azure. I’m like to get hands-on practice and I’d prefer testig with Azure, compared to requesting our Network Engineers to create a new VM just for my practice. So, What did you do to conduct the labs?

I’m fortunate to have a Visual Studio Enterprise subscription where I get $150 worth of credits in Azure each month. I didn’t track my usage or costs except ensuring that I didn’t run out of credits, so I can’t give you any estimates. (It would also depend entirely on your usage and that could be very different from mine!) All I can do is recommend that you stop or delete services as soon as you are done using them, to reduce costs as much as possible.

If you do need to request a subscription or present cost to management, make sure to look into and mention the Azure Cost Management tools like cost analysis, budgets, and alerts. This way, you can ensure management that they won’t get an unexpected bill of thousands of dollars :)

Thanks for your response! What you have suggested with Azure (training, cost management, and expense reimbursement) is what I am currently doing. I have requested management to provide a subscription at the estimated monthly expense of $150. I have been using Microsoft Learn website to educate myself about Azure to better-present my case to management (https://docs.microsoft.com/en-us/learn/paths/azure-fundamentals/). Managers have been thinking for a couple week now about how to implement my request for an Azure Subscription on their account. I have heard they are working on it, so that is promising. Hopefully I’ll get the environment soon! Thanks again for your response. -Brian

Hi Cathrine, I wanted to say a BIG thank you for sharing this comprehensive blog post. I must say It was very useful for me to do this exam. I did it today following all your amazing guidance. And I got pass too :) This is the best blog post I have ever come across. Thank you. I wish you all the very best for your future exams . Cheers, Thilini

Hi there, how many questions were in your exam?

Hi! This is Cathrine (again). Just a reminder. I'd love to hear your thoughts, but please keep in mind that I'm not technical support for any products mentioned in this post :) Off-topic questions, comments and discussions may be moderated. Be kind to each other. Thanks!

Share Your Thoughts?