Most Asked ETL Testing Interview Questions and Answers
What is Snow Flake schema Design?
In a Snow Flake design the dimension table (de-normalized table) will be further divided into one or more dimensions (normalized tables) to organize the information in a better structural format. To design snow flake we should first design star schema design.
What is Operational Data Store [ODS] ?
It is a collection of integrated databases designed to support operational monitoring. Unlike the OLTP databases, the data in the ODS are integrated, subject oriented and enterprise wide data.
What is Denormalization?
Denormalization means a table with multi duplicate key. The dimension table follows Denormalization method with the technique of surrogate key.
What is Surrogate Key?
A Surrogate Key is a sequence generated key which is assigned to be a primary key in the system (table).
What are the client components of Informatica 7.1.1?
Informatica 7.1.1 Client Components:
1. Informatica Designer
2. Informatica Work Flow Manager
3. Informatica Work Flow Monitor
4. Informatica Repository Manager
5. Informatica Repository Server Administration Console.
What are the server components of Informatica 7.1.1?
Informatica 7.1.1 Server Components:
1. Informatica Server
2. Informatica Repository Server.
What is Metadata?
Data about data is called as Metadata. The Metadata contains the definition of a data.
What is a Repository?
Repository is a centrally stored container which stores the metadata, which is used by the Informatica Power center server and Power Center client tools. The Informatica stores Repository in relational database format.
Informatica 7.1.1 Repository has 247 database objects
Informatica 6.1.1 Repository has 172 database objects
Informatica 5.1.1 Repository has 145 database objects
Informatica 4.1.1 Repository has 111 database objects
What is Data Acquisition Process?
The process of extracting the data from different source (operational databases) systems, integrating the data and transforming the data into a homogenous format and loading into the target warehouse database. Simple called as ETL (Extraction, Transformation and Loading). The Data Acquisition process designs are called in different manners by different ETL vendors.
Informatica —-> Mapping
Data Stage —-> Job
Abinitio —-> Graph
What are the GUI based ETL tools?
The following are the GUI based ETL tools:
3. Data Junction
4. Oracle Warehouse Builder
6. Business Object Data Integrator
7. Cognos Decision Stream.
What are programmatic based ETL tools?
2. SAS BASE
3. SAS ACCESS
4. Tera Data Utilities
b. Fast Load
c. Multi Load
d. Fast Export
e. T (Trickle) Pump
What is a Transformation?
A transformation is a repository object that generates, modifies, or passes data. Transformations in a mapping represent the operations the PowerCenter Server performs on the data. Data passes into and out of transformations through ports that you link in a mapping or mapplet. Transformations can be active or passive. An active transformation can change the number of rows that pass through it. A passive transformation does not change the number of rows that pass through it.
What are the various tools? Name a few?
A few more
– Cognos Decision Stream
– Oracle Warehouse Builder
– Business Objects XI (Extreme Insight)
– SAP Business Warehouse
– SAS Enterprise ETL Server
Along with the above, need to include the below tools –Informatica –Abintio –DataStage
What are features of Informatica Repository Server?
Features of Informatica Repository Server.
1. Informatica client application and Informatica server access the repository database tables through the Repository Server.
2. Informatica client connects to the repository server through the host name/ IP address and its port number.
3. The Repository Server can manager multiple repository on different machines on the network.
4. For each repository database registered with the Repository Server it configures and manages a Repository Agent process.
5. The Repository Agent is a multi-threaded process that performs the action needed to retrieve, insert and updated metadata in the repository database tables.
What is a Work Flow?
A Work Flow is a set of instructions on how to execute tasks such as sessions, emails and shell commands. A WorkFlow is created from Workflow Manager.