Book Review – Interview Questions and Answers for Database Developers and Administrators

Recently I had privilege of reviewing a book “SQL Server Interview Questions and Answers”. Two authors Pinal Dave and Vinod Kumar are well-respected names in SQL Server community. I decided to write a book review with hope that database professionals can take full advantage of this book and ace that SQL Server interview.

I have worked with database applications for most of my career, and have been a SQL Server database developer for the last fifteen years, with specialization in Microsoft Business Intelligence development since 2006. I always thought SQL database development is my choice of career. However, by 2005, due to numerous family relocation that crossed two countries and 5 states and provinces in 4 years, I started to struggle with my career.  I bumped from one contract to another, and endured (and failed) many grueling job interviews.

In one of such interviews, I was ushered into a conference room with 6 young IT professionals already sitting in the room with papers in front of them on the long conference table. One of them, the leader I assumed, started introduction. Before the lengthy introduction ended, I realized that these 6 young but serious-looking IT processionals weren’t the only interviewers I had to face. There were 4 other interviewers whom I would not be able see face to face, but I could hear them on the speaker phone. Three of them are developers specialized in certain areas and one is the hiring manager.

Needleless to say that I failed that interview miserably. It wasn’t until several failed job interviews that I began to see a pattern in the interview questions. That is they don’t ask tricky questions. They ask database concepts with SQL server, and expect accurate and complete answers with real-life examples.

I immediately started to collect every SQL interview question I could find online, and immersed myself in questions that I could not articulate very well, and expanded on the questions that I was personally interested in.

Looking back several years later, I wish I had a book like “SQL Server Interview Questions and Answers”. It has exactly what most interviewers are looking for in your answers, SQL server database concepts, accurate and complete answers with real-life examples.

Study the Interview Questions and Answers for Job Interviews

While talking to a co-worker who had to start a job interview process again, she mentioned to me that interviewers are really looking for textbook-style answers. She decided to spend a few months to study for a Microsoft SQL Server certificate exam, hoping that by the time she passes the certificate exam, she would be able to ace that job interview. I wish I knew about the book at the time so I could recommend the book to her.

Where can you get a copy?

You can go to this page SQL Server Interview Questions and Answers on beyondrelational.com to get a copy of this book.

Enjoy and good luck!

Cramming for BI Jobs

Here is the link to a short article Cramming for BI Jobs from Information Management (you might need subscription to read it).

According to the article, of the 125 colleges surveyed, only three had an analytics or BI undergraduate program, and 12 had those BI or analytics programs for graduate degrees. As a result, there is a shortage of up to 1.5 million knowledge workers prepared to work on BI and trends (of big data) coming out of colleges.

What are the obstacles? According to the article:

• Obstacles are from both academia and the industry, and
• Lack of cooperation across faculty in business and IT programs and
• Absence of understanding from administration as well as
• Unwillingness of industry leaders to volunteer and share information that can be highly guarded, like large data sets and case studies

Fun as a Contractor #52 – Job description for data analysis and data profiling

Adding to the job description collection for two reasons.

• This is one of the good job descriptions what actually spelled out job duties and required skills.
• One of the rare job descriptions for data analysis, data profiling and data sourcing.

Data System Analyst and Designer – FINANCE

KEY DUTIES:

-Lead with data analysis, data profiling and data sourcing.

-Work with the end-users, data architect to create/validate and enhance source to Client mapping documents.
-Support business analysts and developers with subject expertise in source-to-Client data flows.

-Work closely with the data architect to define, design and implement Center of Excellence of Risk management tools and large data warehouse.

-Work closely with Risk management tool vendors to ensure timely detailed knowledge transfer on the topics of risk modeling, data management of risk models and governance/control of risk management environment.

-Develop thorough understanding of the data definitions, domain values, data relationships, business rules, sources and data integration for each subject area and/or input source file and develop functional and non-functional specifications
with the data architect, translate business requirements into design specifications.
-Assist in testing, creating test scenarios and troubleshooting data issues including source system gap analysis and remediation
-Work with the project manager, data architect and other project teams including test, QA and production to coordinate and conduct impact analysis.
REQUIRED:

• Data analysis background / Data de-duplication / Denormalization of  data structures and flat file linking
• Interface with BAs and end-users to understand structures, quality and processing rules
• Strong knowledge of relational databases (preferably Oracle, AbInitio, Datastage-Infosphere)
• SQL query writing skills
• Solid knowledge of principles of data warehousing projects, data landscape surveying, data analysis, mapping and evaluation
• Strong data analytical, data profiling and data quality troubleshooting skills
• Experience in data integration, reference data and data cross reference techniques
• Solid expertise interacting and working with data architects, solution architects
• Self Starter / Team Player / good problem solver

URGENT REQUIREMENT…..HIRE ASAP

Fun as a Contractor #51 – Are you taking any risk at work?

I recently said yes to a couple of trouble tickets that I could easily say no since I am working on a high priority project. Not only I said yes, I also did something that most developers (contractor or not) would not do, that is, re-designing the entire ETL process and the reporting. I did inform the business users that in order to fix the tiny issue they have reported, I was going to do some fundamental changes (without changing the business logics).

I didn’t realize how much risk I was taking until I got some emails from the business users:

…before the update we could drill down to the account number by clicking on the specific category, where now we have to click on the + sign on each agents name and it puts all categories together; which is much more time consuming…

…the report appears to be missing some March data after you made the changes on Tuesday. I had pulled it earlier in the day on Tuesday and my team had a total of 73 accounts…

…can we get this fixed asap as we are finalizing numbers from March right now. Also you said you could put the report layout back to what it was prior to Tuesday?

I had to deal with over 20 emails to finally make everybody happy again.

I am pretty sure that the new architectural design has improved the maintainability, and performance of the ETL process. There will be less trouble ticket from now on.

But I’ve learned a lesson from this experience. The risk is worth taking, but I should have spent more time with business users to minimize the “discomfort” they would certainly feel after the change.

Should I take risks again?

SQL Contractor #50 – What kind of SQL God (or Goddess) are you?

I recently read an article on SQLCentralServer.com, The Job Posting – Do I really have to be the SQL God? The author was passing on some advices on how to interpret various SQL job postings.

Do you need to be the SQL God/Goddess? You absolutely do, as a SQL consultant/contractor, in order to be a respectable SQL profession and demand a higher rate. But the question is what kind of SQL god/goddess.

The author reviewed a concept that I thought it’s worth mentioning.

For SQL Server, there’s 5 general areas of work: Administration, T-SQL Development, ETL, Reporting, and Architecture.  In most of these cases, you end up blending a few of these areas together, but a solid, 40 hr/week position is going to concentrate on one of these and incorporate a little bit of some of the others.  For example, reporting will need some T-SQL and some ETL.  Architecture requires a basic understanding of the other four.  ETL will probably require a little architecture to build staging structures and some T-SQL to optimize some tasks.

Five general areas of SQL server work, blending a few of these areas together. I will say that I agree with this concept. the longer I am working on the SQL server platform, the more blended it has became. It’s a good thing for me, as a contractor, because it means that I’ve been down many different paths and gained experiences in wide range of areas. It’s also a good thing when it comes to job interview.

However, I always struggle with the question of “what title do I put on my resume”. When I was a full-time employee, I accepted the job title the company gave to me. Although essentially I’ve always been in SQL development, the job titles I received varied so much that people would think they are completely different jobs. Here are some of the job titles I had over the years: Data Analyst, Database Architect, Technical Advisor, Database Analyst, System Developer.   

Now I am on my own, I no longer have a job title (It’s not entirely true. “Contingent worker” is usually attached to my email in the Global Address List.), until it comes to the resume time. What should I put in that blank space underneath my name? It’s been 6 years, and the situation is getting worse. It is panicky time even when I started to think about that blank space. 

Fortunately the companies that hired me as a “Contingent worker” really don’t care about that blank space on my resume.

I’ll take that back. I never left that space blank. Here are some of the words I put in that space:

SQL Developer, Data Analyst, Database Analyst, ETL Developer

Maybe next time, I’ll consider leaving that space blank.

Data Analyst/Technical-Senior

Adding to the job description collections. This is definitely one of the positions that need a long list of skills.

Title: Data Analyst/Technical-Senior

Location: AZ – Phoenix

Type: Contract

Length: 3 Months

Rate: Market

Job Description:

• · Skills equivalent to 6-10 years in comparable position
• · Perform physical and or logical data modeling.
• · MIS Data Services team is looking for a highly-motivated, self-driven individual well versed in database architecture, systems analytics and ETL development methodologies.
• · As a member of the team, you will work directly with other MIS delivery teams to collect their data needs, identify data sources within the firm, coordinate with source systems to obtain data, create data models, document metadata, and guide development team through ETL development to make the data available for use within MIS.
• · Solutions will range from database object design, development, data loads, transformations, aggregations and SQL optimization techniques.
• · Data modeling experience on Data warehouse environment.
• · Working knowledge of ERWIN, Embarcadero or other industry data modeling tools.
• · Good understanding of data modeling in third normal form and star schema.
• · 5+ years demonstrated experience in systems development and requirements analysis. Informatica – PowerCenter (Version 8 and above) experience a plus.
• · Prior experience with ETL development in Teradata and/or Oracle a big plus Familiarity with Unix shell scripting and use of tools such sed, awk, crontab.
• · 5+ years of Advanced SQL experience. Strong verbal and written skills to effectively communicate with business and technical teams. Self starter, with ability to multitask, mange priorities and work on multiple data projects in fast paced environment.

War over SQL procedures only VS. SSIS packages only

A previous co-worker once summarized my design philosophy. “You are very good at modularizing”, he said. It turned out that the comment is not entirely in the sense of approval.

Because of my design philosophy of modularization, most of the ETL processes I designed ended up having a mix of SQL procedures and tasks in several SSIS packages. Most importantly, each procedure/task/data flow/package is designed to perform just one atomic task.

My previous co-workers told me that now they are having discussions about whether to convert the ETL processes to SSIS package only (favored by developers who is experienced in SQL Integration Services), or SQL procedure only (favored by developers who believe that Integration Services creates too much trouble in terms of deployment).

I frankly told them that do not do either. Stay modularized and specialized in each procedure/task/data flow/package . That’s the key. SQL procedure only or SSIS package only is only indirectly relevant.

If we heed my previous boss’ advice of “do not over engineer”, I think stay put is the best route.

Are you over engineering in your work?

My boss at my previous project often reminded developers that “Don’t over engineer for me”.

As a SQL developer, sometimes we are stuck in our certain believes so religiously that we forget that we are working in a team, whose performance is not measured by how many “bells and whistles” we can add to our technical work, but by how efficient we are, and how quick we can respond to the business needs.

I can detect the disgusting tone in his voice when a developer told me that he couldn’t find any ODBC connection to a database in a spreadsheet report a user forwarded to him. I asked him what is he looking for in the report. He said, “Somebody must have pasted thousands of those data into this spreadsheet manually, because I couldn’t find any ODBC connection in this spreadsheet”. He is expecting that this report is generated dynamically. I told him that this was an adhoc report I did a few months ago. I didn’t bother to automate this report in SQL Reporting Services at the time. I told him that I wrote some simple SQL query to get the data and export it to the spreadsheet.

He immediately declared, “I am a strong believer (in SQL Reporting Services). Anything can be automated”. I told him two reasons I didn’t bother.

One, I didn’t feel like it. They only need this kind of report a couple times a year, and the data elements they need and the report parameters are all different.

Two, the user had a long list of employee IDs that need to be used. Allowing users to load their data files into our server can be a real challenge, especially in an environment where several un-trusted network domains exist.

He still insisted that he would give it a try to automate it.

That’s when my boss repeated his reminder of “Don’t over engineer for me”, when the developer declared his pursue to automate the report again.

Rapid SQL 7 – Freezing again?

I don’t know what SQL tools other people are using on Windows for DB2. I’ve used the plain Command Editor from IBM. 

It’s not a fancy Windows application, but it does what it is designed to do. But it’s never a standard SQL tool on Windows. It does have some weird looking on Windows.

Another tool I’ve used is Toad. I’ve got some funny jokes from co-workers occasionally when I open Toad.

It’s not a very well-liked tool either.

Now the team is pushing us to use Rapid SQL.

After about two weeks, developers started to complain about freezing issues, same issues as with Toad.

Fortunately, we were told that it’s just some settings that we need to change.

Here they are.

1. Turn off auto complete: File > Options > ISQL > Code Assist, unselect "Enable Code Complete".

2. To prevent table locks from occurring: need to change to Uncommitted Reads for Isolation Level: File > Options > ISQL> DB2 > Set Isolation Level > Select Uncommitted reads from the drop down.

3. Finally, run this to set the IBM DB2 client to uncommitted reads

urcli.bat

db2cmd -i -w -c db2 UPDATE CLI CFG FOR SECTION COMMON USING  TXNIsolation 1
db2cmd -i -w -c db2 UPDATE CLI CFG FOR SECTION COMMON USING  ReadCommonSectionOnNullConnect 1
db2cmd -i -w -c db2 GET CLI CFG FOR SECTION COMMON

Which year is it, 2008?

I’ve never done SQL database upgrade in a production environment where hundreds and thousands of SQL objects reside on the database engine, Integration Services, Reporting Services, and SQL Agent services.

The current team I am working with is about to take on this upgrade journey from 2005 to 2008. Although we are well into year 2011 now, this reluctant journey is a much welcome step and a necessary one too.