Data Warehouse Interview Questions!!!

I just interviewed with a company for a Quality Assurance Position in Data Warehouse. The company was looking for a candidate with knowledge of ETL Processes, DataStage and Databases, in general. Here are the questions from that interview and their answers.

Q1. In 5 minutes, can you tell me about yourself and how are you fit for this position? How famaliar are you with ETL Processes and DataStage?
A: Told them about myself and my famaliarity with ETL Processes, DataStage, Informatica, Databases and programming. Also told them about my interests and my work ethics.

Q2. OK, I see that you have done PERL scripting. So how comfortable are you with PERL?
A: Told them I have done PERL scripting and that I am not extremely comfortable with PERL.

Q3. In have a table called Applicant. I make a copy of this table called Applicant_Old. I use Applicant_Old in DataStage for testing and everything works fine. Now I want to replace Applicant_Old with Applicant in DataStage. Before doing this I would like to verify that Applicant and Applicant_Old are exact copy, record for record. How will you verify this?
A: Told them I would use union to combine these two tables and do a SELECT DISTINCT query on the combined table. If the number of records matches, then the copies are exact.

Q4. While verifying, you found out that there is a mismatch in the tables. So how will you identify the mismatched record and from which table it came from?
A: I said, I would do the following, SELECT * FROM applicant WHERE "" NOT IN (SELECT "" FROM applicant_old). The record this query returns is the extra record.
Q5. So you have worked with OLAP. How famaliar are you with OLAP?
A: Yes, I have worked with OLAP. I know theoritical concepts about OLAP.
Q6. In OLAP, what is the difference between a Star Schema and a Snow Flake Schema?
A: I told them the standard defination of both the schemas.
Q7. Some basic SQL questions. What is the difference between Inner and Outer Joins?
A: Again, I told them the standard defination.

Q8. When will you use Inner Join and when will you use Outer Join?
A: Explained them the standard examples.

Q9. In your own words, can you tell me what is referential integrity?
A: Referential Integrity sees to it that the references between two tables is maintained during the ETL Process.

Q10. I want to use one of the columns as a Foreign Key. What are the constraints, that I have to put on this column?
A: The column should be linked to a Primary Key in another table, can or cannot be unique and can or cannot be NULL.

Q11. How famaliar are you with basic UNIX commands?
A: I told them I am very familiar. I would rate myself 8.5 out of 10.

Q12. What is the UNIX command for RENAME?
A: This is a trick question. There is no RENAME command in UNIX. You use mv to rename files.

Q13. What UNIX command do you use to find a string in files in UNIX?
A: grep

Q14. What do you use to make this search case sensitive?
A: Use the flag -i with grep.

Q15. How do you search for a string in VI Editor?
A: Use /

Some of the other questions to expect are:

Q1. What kind of Datawarehouse was it in your last project?
A: Relational as well as Flat Files.

Q2. How big was your database?
A: You mention the size. Usually in TB.

Q3. Roughly, how many table were there?
A: Usually 100s.

Q4. Record wise, how many records were there in your biggest table?
A: Usually millions.

Well, those were my I answers. I am not sure how correct my answers were, but I really got a good feedback from the interviewer.

Nithin Kamath
www.nithinkamath.net