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:
- Complete online training: four free online edX courses
- Go through every single skill measured and ensure I knew how to explain the concept or complete the task
- 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:
- Delivering a Relational Data Warehouse (course DAT216x)
- Implementing ETL with SQL Server Integration Services (SSIS) (course DAT217x)
- Data Cleansing with Data Quality Services (DQS) (course DAT218x)
- Creating a Master Data Solution with SQL Server Master Data Services (MDS) (course DAT226x, not listed on the Microsoft exam website)
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.
Design, implement, and maintain a data warehouse (35–40%)
- 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
- Dimension Table Relationships
- Primary Keys, Foreign Keys and Composite Keys
- Many-to-Many Relationships and Bridge Tables
- Additive, Semi-Additive and Non-Additive Measures
- 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
- Data Profiling
SSIS Data Flow
- Slowly Changing Dimension
- Fuzzy Grouping and Fuzzy Lookup
- Blocking vs. Non-Blocking
- Term Lookup
- T-SQL vs. SSIS Lookup
- Bulk Loading vs. Standard Loading
- Logging for Package Execution
- Error Handling for Data Types
- Data Viewers
- Profile Data
- Batch Clean-Up
- 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 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
- 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.
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!)
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.
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.
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! :)