Hello world, I hope everyone is doing great. I’ve not been able to contribute that much this year on the SSIS Tutorial. In fact, this is my first post on the topic this year and l’m actually not giving tutorial but providing some likely technical questions and the answers to them. These question will pop up in a job interview relating to ETL or Data warehouse. I decide top post this on hear at this time because someone will be needing the answers to some of these questions at any time soon. So here goes the questions and the answers l’ve provided for them.
1. What are the steps to logically designing a database?
answer:
- Identify the entities
- Define the dependencies of the entities on each other
- Define the attributes of the entities
- Normalize the database
2. What are the steps to physically creating a database?
answer:
- Define columns and their data types
- Create relationships between tables
- Add constraints to fulfill business logic
- Forward Engineer
3. What are the processes of designing a Data Warehouse?
answer:
- Identify the Business Processes
- Identify the Dimensions and their attributes
- Identify the units of measurement of the Processes
- Identify the level of granularity
4. What is Granularity?
answer:
The granularity of data refers to the fineness and depth of the data within the Data warehouse.
The lower the granularity the more dimensions the fact tables have which allow for more in-depth analysis.
The higher the granularity the less dimensions the fact tables have which allows for a more general perspective with analysis.
5. What are Business Keys?
answer:
Business keys are the primary keys of an OLTP which uniquely identify that dimension in the OLTP. Business key concept is used in a Data warehouse where mostly surrogate keys serves as the primary key for dimensions.
6. What are Slowly Changing Dimensions?
answer:
Slowly changing dimensions have attributes which may change slowly over time. It is implemented in several ways.
7. What are the types of Slowly Changing Dimensions?
answer:
There are three types of slowly changing dimensions:
- Type 1
- Changes from the OLTP are implemented by overwriting the data within that same dimension row.
- Type 2
- Changes from the OLTP are implemented by inserting a new row with the altered data. In order to differentiate between these records we include a Flag or Start and End Date.
- Type 3
The same as type 2 however we filter with a maximum number of stored records within the dimension.
8. What are Rapidly Changing Dimensions?
answer:
Rapidly changing dimensions change quickly over time. This can cause your dimension to increase in size exponentially if it is a Type 2 or Type 3 dimension. In order to better accommodate RCD you should put the rapidly changing attribute into the Fact table.
9. What are Conformed Dimensions?
answer:
Conformed dimensions are dimensions which are shared between multiple fact tables.
10. What are Parent-Child Dimensions?
answer:
Parent-child dimensions have hierarchy or a recursive relationship with itself.
Ex. An employee dimension which has a ManagerID attribute which points to the EmployeeID business key.
I will be providing more interview questions and answers for ETL related job as often as possible. Remember, passing the interview and getting your foot into the door is the most important part to having a job.
