The interview panel consisted of 3 people. The interview was somewhat odd. One person was asking most of the question. And he didn't really want to understand what I was trying to answer. I guess I had to answer them the way he thinks. Based on his questions and reactions to mine, I felt he doesn't implement what he is talking about. But he knows things theoretically. For example, he told me that MERGE statement is a substitution for CDC in data logging in non-enterprise edition of SQL. And the way it was said was a bit odd, when he asked me how I implement data logging when no CDC is available, I suggested to build a logging tables (which I've done in the past) and he asked me with the smirk "Don't you know about merge?". And I said, no, they are 2 different things - one is for logging (CDC) and another is updating (MERGE). He (and the manager) ignored that.
He asked me about how do I denormalize a highly normalized database (he said in 6th normal form - so this company is probably working with such complex databases). I said I would look into relationships between tables, to determine what are attributes and what are facts. But I couldn't elaborate as he just started talking over me. He asked can you have a fact table without numbers? And I said no you have to have numbers in the fact table. Some of them could be not facts, like invoice number. He said, again with smirk - that would be degenerate dimensions. Not sure what he meant? yes, that would be degenerate dimension but it is still a number that is IN the fact table. I don't know if he denormalized any databases before but if he did, the question is too broad and there is no one sentence answer.
Then he asked me a SQL join question. What would be a result of SELECT * from A LEFT OUTER JOIN B on A.ID = B.ID where B.ID is null. A had 1,2,3,4,5,6 and B had 1,2,3,7,9. So I said the result is 4,5,6. One of the people said NO. So I have added results side by side and NULLs to show how I arrived. I was sure I was right. I was pretty nervous at this point. Then he changed the WHERE clause to A.ID is null. I said it will bring nothing. Based on his face I was wrong, so I've asked him - is it a right answer? He told me that I need to check it myself when I come back home from the interview. I am pretty sure it is right, there are no null values for A table.
The way he asked questions I couldn't understand what exactly he wanted to know. He asked me what is the template for initial ETL package I would create. I frankly not sure what he meant. So I said I would have data sources and data flow task and in the data flow task placeholders for transformations for staging area, etc. I said it depends on the task. He was not in agreement and kind of moved on. When talking about data execution plan he has asked me what would slow down the query. My answer was indexes and subselects. Then he asked what is table scan and Index seek. The names by context offer the answer, right? But I was so nervous I wasn't able to answer it and that was in my opinion the only answer that I didn't have. That and "template" for the ETL.
So yes, I have to admit, it was emotionally very hard, intense and somewhat humiliating interview. I wonder if it was a technique to see if I can work under pressure? Obviously I can not as they told me I am in between junior and mid-level on DW development and they need someone with better skills. I am somewhat relieved that I never have to work with them.