Forum

ALL IT companies SQ...
 
Notifications
Clear all

ALL IT companies SQL ETL and Data Warehouse Interview Questions


Jeevan DBA
Posts: 2
 Jeevan DBA
Topic starter
(@Jeevan DBA)
Joined: 7 months ago

Hi here is the list of interview questions that I have faced so far. If you have something to share please post. Thank You. 

Sonata software:

  1. Tell me about yourself?
  2. Tell me a test case of how to validate the source & target in your project?
  3. Which strategy are you following for the ETL process?
  4. How we can see the table structure?
  5. What exact we can see in a table?
  6. What is surrogate key?
  7. Is it Surrogate key null?
  8. Bet delete, truncate & drop?
  9. How u can say delete performance is poor than truncate?
  10. Through the delete command can we delete all data?
  11. Can truncate table delete all data/
  12. How u can maintain history in DWH?
  13. What was u faced a challenge in the business rule?
  14. Which table I have to delete first either fact or dimension?
  15. Write a query for a salary that is greater than 20k with an emp name?
  16. If I write a query for outer join without any condition so it works properly?
  17. If it is working properly then which join it will take by default?
  18. What is having clause& order by?

 

 

 

 

Capgemini 1st round

 

  1. Tell me about yourself and the project?
  2. Tell me about the ETL process?
  3. How to validate source and Target? Through column mapping
  4. Diff Delete and Truncate?
  5. Tell me Aggregate Functions?
  6. Write a query without using any join?
  7. How to delete duplicate records in a table?
  8. Explain the Testing life cycle?
  9. In STLC, Which phase can u write the test cases?
  10. Diff Re & Regression Testing?
  11. What are the entry criteria and exit criteria?
  12. Explain the Bug life cycle?

 

2nd round

 

  1. what is Datawarehouse?
  2. why we need historical data?
  3. how to develop your business by using a report?
  4. types of joins?
  5. I need to pick the first 10 records from 1 lakh records?

 

  1. what is test data?
  2. how to execute test cases?
  3. how to give links from test case to req?
  4. how to log a defect?
  5. bug life cycle?
  6. if the developer rejects your defect in that situation what u will do?
  7. do u know about the data stage?
  8. how to validate records which are in flat files?
  9. how to raise a defect /

 

 

HP interview questions:- (1st round)

  • Tell me about yourself?
  • In the Tidal tool when it shows time out status what is the exact meaning of time out status...?
  • If u r running 2 jobs simultaneously at a time 1st job is completed then what is the 2nd job status it shows...?
  • If we schedule a job & no one single person is there for monitoring and bugs has occurred then

How should u communicate with your administrative team? how should u handle this situation?

  • How should u change the ownership of directories and permission?
  • What is the command for list out all the files with permission?

 

Capgemini interview questions: - (1st round)

 

  • Tell me about yourself?
  • Had your lunch?
  • Where are you from basically?
  • What are your total years of experience in testing & in ETL testing...?
  • Draw your project architecture?
  • If your source data is a .txt file and it having 100 records and my target also having 100 records then how u can validate both data?
  • How can u check both r same record? /
  • Write count query /
  • What is aggregation testing & regression testing...?
  • Write not null query?
  • What is SCD?
  • Explain type 2 of SCD?
  • How u maintain data in Type 2 of SCD?
  • Where r u staying in Bangalore & with whom...?
  • Difference between a test strategy and a test plan? Have you ever written a test strategy?
  • Explain types of joins?
  • Give me an example of outer joins?
  • Give me sample test cases of your project.
  • What is the use of group by?
  • Give me some test cases on reports.
  • Have you done reports testing anytime?

Questions of SLK

1) Through Informatica possible to generate a report...?

2) If u open the flat file in Unix so what is it looking like...? (row-wise or column)

3)How u can load a flat-file in your target database?

4) write one simple test case in your current project...?

5) draw your project architecture.

6) I want to create one flat file in Unix so how can I create it...?

7) about OLAP & OLTP what comes in your mind first...?

8) Tell me briefly about your previous project...?

 

These are IBM 1st round interview questions

  1. What is your experience? Tell me about your experience?
  2. How u validate that source records are moved to the target?
  3. What all are in mapping documents?
  4. Are u involved in reporting?
  5. How u understand the requirement? If u not understand the requirement, then what you will do?
  6. U have 100 records in a source that u moved to target…target got 100 records around 2’clock. another 100 records moved to target it got 3’clock and next 100 records 4 ‘clock…

Source                                                         target     time

100                                                             100       2’clock

100                                                             100       3’clock

100                                                             100       4’clock

How u find out how many records are there in target?

8.Where u run workflow?

9.Which type of error u ll get in oracle?

Nuance

1.Tell me about yourself?

2.Explain your DW project

3.What are the verifications you do as an ETL tester.

4.Example of joiner transformation

5.Source table has a column Gender with values M and F, but in target, these should be loaded as 1 & 0. How would you validate the correct data is loaded?

6.I have 4 columns in the source table and 3 columns in the destination. The filtered data should be loaded into only three columns of the target. How to verify all the records in the source are loaded to target.

7.How many ETL's have you run so far

8.Given a scenario that has the few transformations to be applied. Asked me to write test cases for that.

9.How many times will you execute an ETL given to you so that you sign off the requirement?

10.Mention any High priority & severity, Low Severity & High priority bugs.

11.What is incremental load & do you have any knowledge on SCDs

  1. Few Manual testing questions like defect life cycle, metrics, etc.,

 

Others

 

Below are some questions which are asked frequently
1. In SQL what are the differences between Where and Having?
2. Differences between Drop, Truncate and delete
3. How do you find duplicates in a table?
4. What is Surrogate Key?
5. What is SCD, what is SCD used in your project?
6. What is Schema used in your project and why (star or snowflake)
7. If data is loaded from Table A to Table B but if some records are missed to be loaded to Table B how do you identify such records?
8. What are different validations done after data loaded into the target table?
9. What is the difference between subquery and inline query (actually it should be inline views)
10. What are the different joins you have used?
11. What is the difference between left outer and right outer join?
12. What is performance tuning? (I had performance tuning mentioned in my resume)
13. How did you perform reprocessing of error files? (Since this was mentioned as my roles in my resume)
14. What are the various ETL tools you have worked on?
16. Can you describe what or how you verified the ETL process using the Informatica tool?
17. What was the validation you did use Informatica tool was it just tool upgrade testing or functional validation
18. Can you give few examples of defects you have raised during ETL testing categorizing them based on severity and priority.
19. Give few examples of how you validated for various constraints like Foreign Key, Unique, Not Null
20. Between two tables fetch department name which has the maximum number of employees
EMPLOYEE Table DEPARTMENT table
EMPNO DEPT DEPTNO DNAME
(I have just given the structure of the table in the above query but one needs to imagine some data in these tables and arrive at a query to find maximum employees)
21. What is coalesce?

 

Accenture

1st Round:

  1. Characteristics Of Data Warehouse
  2. What is volatile?
  3. Project architecture?
  4. Scenarios Tested in your project?
  5. Diff Between having and group by clause
  6. What are subquery and correlated subquery
  7. Decode query (display January if the value is 1, Feb if the value is 2, and so on)?
  8. Display employee name with their respective department names (emp, dept table)
  9. Display the employee details who doesn’t have any department

2nd round:

  1. tell me about yourself
  2. project architecture
  3. What are the scenarios you have tested in the staging layer?
  4. How many tables are there in your project? How many facts and dimension tables?
  5. Negative scenarios for notepads?
  6. Scenarios to test an invisible pen?
  7. Substr and inserting queries
  8. Join Queries

Dell Interview questions

  1. Project architecture
  2. BI report validation
  3. ETL testing process
  4. ETL job role and responsibilities
  5. what is regression testing and when we use it?
  6. what validations done in your project?
  7. Tell me some dimension tables with attributes and fact tables used in your project?
  8. Difference between truncate, delete and drop?
  9. SCD types
  10. how do you run the ETL job while using Informatica?
  11. Informatica architecture
  12. what type of transformations applied in your project?
  13. what are workflow, maplet, and work?
  14. Types of ETL testing?
  15. Defect life cycle and some more Manual topics
  16. Automation of ETL testing
  17. how many layers in ETL and what was that?
  18. Data migration and did u handle any migration projects in your company?
  19. difference between test strategy and test plan?
  20. what is data mining?
  21. Meta Data
  22. Give me some examples of Data cleansing
  23. About joins
  24. Difference between rank and dense rank
  25. what are the disadvantages of surrogated key?
  26. which type of bug tool using in your current project? Describe that.

SQL based Questions

  1. Write a query to delete the duplicate record
  2. write a query to fetch the 30th record
  3. write a query to fetch the highest 2nd salary
  4. write a complex query without using joins
  5. write a query to fetch the department wise 20th max salary
  6. write a query to fetch the deleted records
  7. write a query for Constraint validation.
  8. write a query to select alternative rows from a table.
  9. How do you sync rows in two tables, rows in the first table being inserted/ updated/ deleted, and no modification on the second table?
  10. Get 5 employees from every department getting the highest salary (empid, dept id, salary).

HP Interview Questions (First Two Rounds)

  1. Difference between truncate delete and drop
  2. Flat file Validation
  3. What is master data management?
  4. SCD types
  5. What is coalesce?
  6. Suppose the ETL job is failed. so that free time what u will do...?
  7. project architecture
  8. How do you analyze the table?
  9. What are the layers/stages/staging in DWH?
  10. How do u validate the loaded data is correct or not?
  11. what points are u discussed with your developing team?
  12. which transformation applied in your project?
  13. Defect life cycle, Some of the manual testing concepts
  14. Suppose the ETL job is failed. so that free time what u will do...?
  15. BI validations
  16. What is DWH and which type of approaches u followed in your project?
  17. What are the responsibilities of your team leader?
  18. What is coalesce?
  19. How do you validate for various constraints like Foreign Key, Unique, Not Null?
  20. How many ETL are u run in your project?
  21. How many times will you execute an ETL given to you so that you sign off the requirement?
  22. Are 2 ETL jobs running simultaneously is possible?
  23. What are the Active and passive transformations?
  24. Metadata

 

SQL Based Questions

 

  1. Write a minimum 5 queries related to your present project
  2. A column has some negative values and some positive values. It is required to find the sum of negative numbers and the sum of the positive numbers in two separate columns.
  3. What is equijoin
  4. Explain types of joins
  5. Write 2 Complex queries and tell me what is the result.
  6. Show me some subqueries.
  7. Fetch the 3 rd. highest salary
  8. Fetch the 70th record

Liquid Hub

 

1.Introduce yourself

2.Roles and responsibilities

3.About the current project

4.Left outer and right outer join query and difference

  1. Fetch duplicate
  2. Difference between union and union all
  3. The complex query you have written in your project
  4. What are dimension and fact
  5. Informatica errors
  6. QC tabs
  7. Null and constraint validation query
  8. Reports u validated in Bi

 

JP Morgan

 

1.tell about yourself & the project?

2.what are all the tools u used in your project?

3.DDL, DML, DCL, TCL commands and how they work?

4.how truncate works and diff b/w delete and truncate?

6.Diff b/w Test strategy and test plan?

7.Defect life cycle?

8.Test data creation? Explain how you created it?

9.how, you check the log file in ETL?

10.which tool you use for bug posting and checking?

11.Unix privileges?

12.exit and entry criteria?

13.Which methodology you used in your project?

14.how Agile works?

15.what is the Agile team size and explain?

16.Transformations and explain?

17.validations?

18.complex query you write in your project?

19.what is your work in your project and module?

20.how many ETL’s you run till now?

 

Capgemini

 

  1. Capgemini telephonic
  2. Introduce yourself
  3. Types of joins
  4. What is an inner join?
  5. What are keys in joins
  6. What s foreign key
  7. Diff between PK and FK
  8. What s the database you have used and the ETL tool
  9. Which method u have used in the project?
  10. What is scrum
  11. What is sprint
  12. What is the sprint backlog
  13. Explain your agile method process
  14. Equivalence partitioning
  15. Boundary value analysis

 

Herman

 

Company: Harman

 

Position: team lead

Questions:

1) Without touching the pen, pls tell the test cases u write

2) bug life cycle

3) what is the test technique

4) what are your daily tasks

5) regression and retesting

6) black box and white box testing definitions

7) smoke and sanity

8) defect life cycle

9) Last question, u posted a defect, and after 4 hours, the developer sees it and says it’s not a defect, u too got to know that it's not a defect, what do u do?

 

CGI

 

Interview questions are

  1. In string and substring
  2. Joins
  3. Set operators
  4. Union and union all
  5. SCD types
  6. Incremental load vs initial load
  7. Validations
  8. What type of transformations applied in your project
  9. Metadata
  10. Rank vs dense rank
  11. Data migration and did u handle any migration projects in your company?
  12. Some of the manual testing concepts?
  13. How do you validate the different constraints?
  14. Is ETL jobs running simultaneously? Is possible?
  15. Active and passive transformation
  16. How do you analyze the table?
  17. What do you know about Informatica?

SQL:

  1. A column has some negative and some positive values. Find the sum of negative and positive numbers in two desperate columns?
  2. Department wise 20th max salary
  3. How do you sync rows in two tables? Rows in the first table being inserted/ deleted/ updated, and no modification on the second table.
  4. How do u fetch the deleted records?
  5. One table has 2 columns
  6. Name 2. Gender

Name.      gender

  1. Male
  2. Female
  3. Female
  4. Female

 

Output is

Male.       Female

  1. 4

 

iGATE

Interview with iGATE

 

1.What is the composite primary key?

  1. How to validate phone no column?
  2. How to validate the email id column?
  3. How to validate data if the source is an oracle database and the target is Teradata Database?
  4. What is surrogate key?
  5. What is a data model?
  6. Query to find the distinct records without using a distinct function?
  7. Difference between union and union all?
  8. Difference between intersecting and minus?

 

HCL

 

1) tell me about yourself

2)difference between snowflake and star schema

3)estimation in ETL and give me an example of a recent one u did

4)content in ETL test plan

5)what do u validate in schema validation

6) there is a CSV file in source with 1 mill records and staging has only read-only access, how do we validate the CSV file

7)recent bugs in ETL with examples

8) high severity and high priority examples

9)sanity testing in ETL, what and how u do it

10) Emp table has, empnum , dept Num, sal as columns, write a query to display min 5 employees from each department

 

Wipro

 

Questions asked in Wipro interview

1.about project

2.about Informatica

3.diff bet snow

flake star schema

4 how to check spaces in a particular column

 Records

5 some join SQL

6 queries to find system date in oracle also timestamp

7 latest version of oracle   Informatica and QC

8 data warehousing concepts

9 how to find duplicate records

10 how to find alternate rows

11 how to find negative records in a particular column

12 what is dual in oracle

13 sequence in oracle

14 some testing concepts

 

Ims health questions:

 

1)what difference between OLTP n OLAP

2)what is the fact and dimension table. And which table contains pk and FK

3) SQL queries: display employee name and department name ...employee and department table

4) to find max salary depart wise with each employee drawing max sal in that department

5)in a single table there are odd and even numbers. Write a query to display even numbers in one column and odd numbers in another

6) how to validate records in the target table when a source is a flat-file. Test scenarios to write

7)basic Unix commands

8)if the source is an XML file with 200 records and source DB with  200 records and ETL loads data into DB...how to validate data ..test scenarios...also how to check which records are loaded from XML and which from DB

9) Nvl and nvl2 difference

10) few basic Unix commands

 

Capgemini

 

 

Capgemini questions

 

*If you have 8 records in source and only 2 moved to target. Can you explain probable reasons for this and why?

* Explain the overall process for running an ETL job

* Which technology data warehouse is used

* Delete duplicate records

* Difference between unique and primary key

*what is the error message when data types are different in source and target tables

* Will the job fails in the above scenario

 

 

Capgemini

 

Capgemini interview questions:

1.tell about yourself?

2.your project?

3.what is incremental and initial loads?

4.what is snowflake schema?

5.what is your testing process?

6.what are the extra columns that you will be having in your data warehouse?

7. gives me two tables and he asked me to write to fetch which department is getting more than some salary?

8.what is the transformation you know?

9.give me some examples for rank transformations?

10.difference between union and union all?

11.explain the defect life cycle?

12.how, do you confirm that all data are moved in to target table or not?

13.write some test cases to validate the data.

14.what are dimensional and fact tables?

15.what is expression transformation?

16.some real-time questions about QC!

 

 

 

 

Mphasis

 

Tele round Mphasis went for 34 mint...

1)project and process

2)queries on analytical functions highest,duplicates,delete vs truck,DDL DML,auto-commit  vs rollback

3)WF manager and monitor.

Flat file process

 

Second round: went for about 1.5 hours

 

1)Manual testing questions severity, the priority with examples...Agile methodology...Bug cycle...

2)SQL queries substr, total emp count in each Dept...nested queries...queries using group by...

3)ETL -flow, roles, scenarios on active and passive trans.

4)managerial questions like how u manage new technology when given...How u plan ur tasks...Tester qualities etc

+SQL languages union and union all+ intersect

 

Brillio

 1st round brilliant!

 

1.about a project?

2.bug life cycle?

3. normalized and de

4.partition by

5.two table joins.

6.about transformations?

7. How to fetch 3rd salary.

8.how data flow takes place.

  1. Is staging mandatory?

10.Unix

11.hash partitioning

12.why a data warehouse is required?

13.why not possible to possible to generate reports on source systems?

14.some queries about rank and partition.

 

 

2nd round brillio!

1. tell me about your project?

2.how, will you start your testing?

3.what if you don't have mapping documents?

4.what if the developer not prepared a mapping document?

5.client not ready to give any requirements he will give only some basic requirement some high-level document what will you do?

6.why a data warehouse is required?

7.she asked me about the BI tool...?

8. I think this was the manager round lot of debate happened with respect to the mapping document?

9.how, will you run your workflow?

10.have any knowledge of designer tools...? I said no

11.any idea about the stored procedure? for this no answer?

12.why reports required?

 

3rd round brilliant

1.what are the challenges you faced in your project?

2. Tell me about your project complect

3.which version QC YOUR using?

4.what are the fields in the defect tab?

5.what is your source system and what are the tables you worked on?

6.how, will you give an estimation?

7.deferred bug is assistance or rejection?

8.if the mapping document is clear what will you do?

9.why you are not authorized to prepare test data?

10.have you involved in UI-related bugs?

11.what are the factors that affect estimation?

12.what are all the bugs that you posted?

PWC

 

  1. Brief about yourself.
  2. Explain you are recent project.
  3. Difference between union and union all and asked for output with tables.
  4. 4th highest salary?
  5. Couple of basic questions on alm.. how to map requirements with test cases? Can we update test cases via the test lab?
  6. Query to find duplicate s from a table.
  7. How to check logs in Informatica?

 

SLK Software

 

SLK interview questions.

1.tell me about your project?

2.roles and responsibilities? 

3.what business logic that you’re following in the mapping document.                     

4.what are the components of your mapping document and what it contains.   

5.what data you are loading into DWH.                  

6.explain left, right outer joins.                            

 7.about Union & Union all.

8.about nvl function.   

9.which tools you are using?

 

UST Global

 

 

UST global

  1. How u compare source file with 1 lakh records with target table (No source DB)
  2. If DB doesn't support minus function how do u test target DB
  3. 10 table with 10 records total 100 records move to single table in target. How do you test

4.No mapping doc available how do u write test cases

5.difference between delta and initial load

 

  1. Find the first and last 2 records in a table.
  2. How to find only domain name from the email address.
  3. Data flow diagram of your project.
  4. Find 3rd highest salary.
  5. Difference between Union and join.
  6. What is a composite key and how it differs from others with examples?
  7. Without using distinct keywords find the non-duplicate records.
  8. Self-join with example.
  9. How are you using Informatica in your project?
  10. Different types of transformations you used in your project?
  11. How do you estimate the requirements?
  12. The major risk you faced?
  13. How do you convince a developer this is a bug, not an error?
  14. Difference between SDLC and STLC.
  15. Draw the Agile architecture and life cycle you are following in your project?
  16. Difference between the Fact and the Dimension table?
  17. SCD Types and most used among them?
  18. Difference between Star and Snowflakes schema?
  19. What and how is your testing approach for BI Testing?
  20. How did you do the estimations for BI Testing?
  21. Why we need BI Testing and what are the major challenges you faced?
  22. What is the bug life cycle?
  23. When do you say the bug is different?
  24. What security testing you have done?
  25. How do you convert date to string?
  26. The column values need to be displayed in a row.
  27. NVL () used for and with examples?
  28. Other is manual testing questions.

 

Wipro interview questions, IBM Interview Questions, HCL Interview Questions, and More...

Share: