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 a PDF which is available to download for free.
Interview Questions and Answer PDF contains questions from beginner to advanced users.
Visit the complete series as well as the final link to download the PDF.
Complete Series of SQL Server Interview Questions and Answers
Samples Questions and Answers
What are Slowly Changing Dimensions (SCD)?
SCD is an 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:
- SCD1: The new record replaces the original record. Only one record exists in database - current data.
- SCD2: A new record is added into the customer dimension table. Two records exist in database - current data and previous history data.
- 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 may be 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 modeling 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 the 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
