Click here to Skip to main content
Click here to Skip to main content

SQL SERVER - Data Warehousing Interview Questions and Answers Complete List Download

, 29 Jul 2007
Rate this:
Please Sign up or sign in to vote.
I have tried to accommodate all of them in last post in the series. I hope this series is helpful to all candidates who are seeking job as well interviewers. I have combined all the questions and answers in the one PDF which is available to download and refer at convenience.

Introduction

SQL Server Data Warehousing interview questions and answers were original published in three series. This complete series is combined in one article which is listed here in this article. This article is PDF which is available to download for free.

Interview Questions and Answer PDF contains questions from beginner to advance users.

Visit complete series as well as final link to download the PDF.

Points of Interest

Complete Series of SQL Server Interview Questions and Answers
SQL SERVER - Data Warehousing Interview Questions and Answers - Introduction
SQL SERVER - Data Warehousing Interview Questions and Answers - Part 1
SQL SERVER - Data Warehousing Interview Questions and Answers - Part 2
SQL SERVER - Data Warehousing Interview Questions and Answers - Part 3
SQL SERVER - Data Warehousing Interview Questions and Answers Complete List Download

Samples Questions and Answers

What are slowly changing dimensions (SCD)?
SCD is abbreviation of Slowly changing dimensions. SCD applies to cases where the attribute for a record varies over time.
There are three different types of SCD.
1) SCD1 : The new record replaces the original record. Only one record exist in database - current data.
2) SCD2 : A new record is added into the customer dimension table. Two records exist in database - current data and previous history data.
3) SCD3 : The original data is modified to include new data. One record exist in database - new information are attached with old information in same row.

Differences between star and snowflake schema?
Star schema - A single fact table with N number of Dimension, all dimensions will be linked directly with a fact table. This schema is de-normalized and results in simple join and less complex query as well as faster results.
Snow schema - Any dimensions with extended dimensions are know as snowflake schema, dimensions maybe interlinked or may have one to many relationship with other tables. This schema is normalized and results in complex join and very complex query as well as slower results.

What is surrogate key?
Surrogate key is a substitution for the natural primary key. It is just a unique identifier or number for each row that can be used for the primary key to the table. The only requirement for a surrogate primary key is that it is unique for each row in the table. It is useful because the natural primary key can change and this makes updates more difficult.Surrogated keys are always integer or numeric.

What is ER Diagram?
Entity Relationship Diagrams are a major data modelling tool and will help organize the data in your project into entities and define the relationships between the entities. This process has proved to enable the analyst to produce a good database structure so that the data can be stored and retrieved in a most efficient manner.
An entity-relationship (ER) diagram is a specialized graphic that illustrates the interrelationships between entities in a database. A type of diagram used in data modeling for relational data bases. These diagrams show the structure of each table and the links between tables.

What are the Different methods of loading Dimension tables?
There are two different ways to load data in dimension tables.
Conventional (Slow) :

All the constraints and keys are validated against the data before, it is loaded, this way data integrity is maintained.
Direct (Fast) :

All the constraints and keys are disabled before the data is loaded. Once data is loaded, it is validated against all the constraints and keys. If data is found invalid or dirty it is not included in index and all future processes are skipped on this data.

SQL SERVER - Data Warehousing Interview Questions and Answers Complete List Download

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

pinaldave
Founder http://blog.SQLAuthority.com
India India
Pinal Dave is a Microsoft Technology Evangelist (Database and BI). He has written over 2200 articles on the subject on his blog at http://blog.sqlauthority.com. Along with 8+ years of hands on experience he holds a Masters of Science degree and a number of certifications, including MCTS, MCDBA and MCAD (.NET). He is co-author of two SQL Server books - SQL Server Programming, SQL Wait Stats and SQL Server Interview Questions and Answers. Prior to joining Microsoft he was awarded Microsoft MVP award for three continuous years for his contribution in community.
Follow on   Twitter

Comments and Discussions

 
Generalproject guide Pinmembermresh12-Aug-07 17:30 
AnswerRe: project guide PinmemberTomruster1-Sep-08 0:00 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web04 | 2.8.140709.1 | Last Updated 29 Jul 2007
Article Copyright 2007 by pinaldave
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid