Top 50 Etl Testing Interview Questions You Must Prepare 26.May.2022

When addressing a table some dimension key must reflect the need for a record to get extracted. Mostly it will be from time dimension (e.g. date >= 1st of current month) or a traction flag (e.g. Order Invoiced Stat). Foolproof would be adding an archive flag to record which gets reset when record changes.

Non additive facts are facts that cannot be summed up for any dimensions present in fact table. These columns cannot be added for producing any results.

PowerCenter - ability to organize repositories into a data mart domain and share metadata across repositories.
PowerMart - only local repository can be created.

joiner is used to join two or more tables to retrieve data from tables(just like joins in sql).

Look up is used to check and compare source table and target table .(just like correlated sub-query in sql).

These are some differences b/w manual and ETL development.
ETL

  • The process of extracting data from multiple sources.(ex. flatfiles, XML, COBOL, SAP etc) is more simpler with the help of tools.
  • High and clear visibility of logic.
  • Contains Meta data and changes can be done easily.
  • Error handling, log summary and load progress makes life easier for developer and maintainer.
  • Can handle Historic data very well.


Manual

  • Loading the data other than flat files and oracle table need more effort.
  • complex and not so user friendly visibility of logic.
  • No Meta data concept and changes needs more effort.
  • need maximum effort from maintenance point of view.
  • as data grows the processing time degrades.

Specify the Full path of the Shell script the "Post session properties of session/workflow".

  • Data cleaning is also known as data scrubbing.
  • Data cleaning is a process which ensures the set of data is correct and accurate. Data accuracy and consistency, data integration is checked during data cleaning. Data cleaning can be applied for a set of records or multiple sets of data which need to be merged.

Parameter file defines the value for parameter and variable used in a workflow, worklet or session.

Deleting data from data warehouse is known as data purging. Usually junk data like rows with null values or spaces are cleaned up.

Data purging is the process of cleaning this kind of junk values.

Full Load: completely erasing the contents of one or more tables and reloading with fresh data.

Incremental Load: applying ongoing changes to one or more tables based on a predefined schedule.

Yes, you can use advanced external trformation, You can use c++ language on unix and c++, vb vc++ on windows server.

  • A mapping represents dataflow from sources to targets.
  • A mapplet creates or configures a set of trformations.
  • A workflow is a set of instructions that tell the Informatica server how to execute the tasks.
  • A worklet is an object that represents a set of tasks.
  • A session is a set of instructions that describe how and when to move data from sources to targets.

Here are some popular versions of Informatica.

  • Informatica Powercenter 4.1,
  • Informatica Powercenter 5.1,
  • Powercenter Informatica 6.1.2,
  • Informatica Powercenter 7.1.2,
  • Informatica Powercenter 8.1,
  • Informatica Powercenter 8.5,
  • Informatica Powercenter 8.@

  • A data warehouse can be considered as a storage area where relevant data is stored irrespective of the source.
  • Data warehousing merges data from multiple sources into an easy and complete form.

By using Aggregator trformation with first and last functions we can get first and last record.

  • A fact may be measure, metric or a dollar value. Measure and metric are non additive facts.
  • Dollar value is additive fact. If we want to find out the amount for a particular place for a particular period of time, we can add the dollar amounts and come up with the total amount.
  • A non additive fact, for eg; measure height(s) for 'citizens by geographical location' , when we rollup 'city' data to 'state' level data we should not add heights of the citizens rather we may want to use it to derive 'count'.

  • No, In Informatica 5.2 and
  • Yes, in Informatica 6.1 and later.

Data cubes are commonly used for easy interpretation of data. It is used to represent data along with dimensions as some measures of business needs. Each dimension of the cube represents some attribute of the database. E.g profit per day, month or year.

  • Active trformation can change the number of rows that pass through it. (Decrease or increase rows)
  • Passive trformation cannot change the number of rows that pass through it.

SCD are dimensions whose data changes very slowly.
eg: city or an employee.

  • This dimension will change very slowly.
  • The row of this data in the dimension can be either replaced completely without any track of old record OR a new row can be inserted, OR the change can be tracked.

Snapshots are read-only copies of a master table located on a remote node which is periodically refreshed to reflect changes made to the master table. Snapshots are mirror or replicas of tables.

Views are built using the columns from one or more tables. The Single Table View can be updated but the view with multi table cannot be updated.

A View can be updated/deleted/inserted if it has only one base table if the view is based on columns from one or more tables then insert, update and delete is not possible.

Materialized view
A pre-computed table comprising aggregated or joined data from fact and possibly dimension tables. Also known as a summary or aggregate table.

A data warehouse can be thought of as a three-tier system in which a middle system provides usable data in a secure way to end users. On either side of this middle system are the end users and the back-end data stores.

A trformer built set of similar cubes is known as cube grouping. They are generally used in creating smaller cubes that are based on the data in the level of dimension.

  1.  PowerMart Designer
  2.  Server
  3.  Server Manager
  4.  Repository
  5.  Repository Manager

  • ODS - Operational Data Store.
  • ODS Comes between staging area & Data Warehouse. The data is ODS will be at the low level of granularity.
  • Once data was populated in ODS aggregated data will be loaded into EDW through ODS.

The only difference b/w informatica 7 & 8 is... 8 is a SOA (Service Oriented Architecture) whereas 7 is not. SOA in informatica is handled through different grid designed in server.

The best procedure to take a help of debugger where we monitor each and every process of mappings and how data is loading based on conditions breaks.

An active data warehouse represents a single state of the business. It considers the analytic perspectives of customers and suppliers. It helps to deliver the updated data through reports.

Additive: A measure can participate arithmetic calculations using all or any dimensions.

Ex: Sales profit

Semi additive: A measure can participate arithmetic calculations using some dimensions.

Ex: Sales amount

Non Additive:A measure can't participate arithmetic calculations using dimensions.

Ex: temperature

  • In real time data-warehousing, the warehouse is updated every time the system performs a traction.
  • It reflects the real time business data.
  • This me that when the query is fired in the warehouse, the state of the business at that time will be returned.

You cannot lookup from a source qualifier directly. However, you can override the SQL in the source qualifier to join with the lookup table to perform the lookup.

Data staging is actually a collection of processes used to prepare source system data for loading a data warehouse. Staging includes the following steps:

  • Source data extraction, Data trformation (restructuring),
  • Data trformation (data cleing, value trformations),
  • Surrogate key assignments.

Popular Tools:

  • IBM Web Sphere Information Integration (Accentual DataStage)
  • Ab Initio
  • Informatica
  • Talend

ETL tool is meant for extraction data from the legacy systems and load into specified database with some process of cleing data.

ex: Informatica, data stage ....etc

OLAP is meant for Reporting purpose in OLAP data available in Multidirectional model. so that you can write simple query to extract data from the data base.

ex: Business objects, Cognos....etc

While the selection of a database and a hardware platform is a must, the selection of an ETL tool is highly recommended, but it's not a must. When you evaluate ETL tools, it pays to look for the following characteristics:

  • Functional capability: This includes both the 'trformation' piece and the 'cleing' piece. In general, the typical ETL tools are either geared towards having strong trformation capabilities or having strong cleing capabilities, but they are seldom very strong in both. As a result, if you know your data is going to be dirty coming in, make sure your ETL tool has strong cleing capabilities. If you know there are going to be a lot of different data trformations, it then makes sense to pick a tool that is strong in trformation.
  • Ability to read directly from your data source: For each organization, there is a different set of data sources. Make sure the ETL tool you select can connect directly to your source data.
  • Metadata support: The ETL tool plays a key role in your metadata because it maps the source data to the destination, which is an important piece of the metadata. In fact, some organizations have come to rely on the documentation of their ETL tool as their metadata source. As a result, it is very important to select an ETL tool that works with your overall metadata strategy.

Informatica Metadata is data about data which stores in Informatica repositories.

Data mining can be used in a variety of fields/industries like marketing of products and services, AI, government intelligence.

The US FBI uses data mining for screening security and intelligence for identifying illegal and incriminating e-information distributed over internet.

ETL Tool:

It is used to Extract(E) data from multiple source systems(like RDBMS, Flat files, Mainframes, SAP, XML etc) trform(T) then based on Business requirements and Load(L) in target locations.(like tables, files etc).

Need of ETL Tool:

An ETL tool is typically required when data scattered across different systems.(like RDBMS, Flat files, Mainframes, SAP, XML etc).

If return port only one then we can go for unconnected. More than one return port is not possible with Unconnected. If more than one return port then go for Connected.

The ANALYZE statement allows you to validate and compute statistics for an index, table, or cluster. These statistics are used by the cost-based optimizer when it calculates the most efficient plan for retrieval. In addition to its role in statement optimization, ANALYZE also helps in validating object structures and in managing space in your system. You can choose the following operations: COMPUTER, ESTIMATE, and DELETE. Early version of Oracle7 produced unpredictable results when the ESTIMATE operation was used. It is best to compute your statistics.

  EX:
select OWNER,
sum(decode(nvl(NUM_ROWS,9999), 9999,0,1)) analyzed,
sum(decode(nvl(NUM_ROWS,9999), 9999,1,0)) not_analyzed,
count(TABLE_NAME) total
from dba_tables
where OWNER not in ('SYS', 'SYSTEM')
group by OWNER

Key areas of activity in which favorable results are necessary for a company to obtain its goal.
There are four basic types of CSFs which are:

  • Industry CSFs
  • Strategy CSFs
  • Environmental CSFs
  • Temporal CSFs

Connected lookup:

Connected lookup will receive input from the pipeline and sends output to the pipeline and can return any number of values it does not contain return port.

Unconnected lookup:

Unconnected lookup can return only one column it contain return port.

ETL stands for extraction, trformation and loading.

ETL provide developers with an interface for designing source-to-target mappings, trformation and job control parameter.

Extraction :

Take data from an external source and move it to the warehouse pre-processor database.

Trformation:

Trform data task allows point-to-point generating, modifying and trforming data.

Loading:

Load data task adds records to a database table in a warehouse.

  • Informatica allows end users and partners to extend the metadata stored in the repository by associating information with individual objects in the repository. For example, when you create a mapping, you can store your contact information with the mapping. You associate information with repository metadata using metadata extensions.
  • Informatica Client applications can contain the following types of metadata extensions:
  • Vendor-defined. Third-party application vendors create vendor-defined metadata extensions. You can view and change the values of vendor-defined metadata extensions, but you cannot create, delete, or redefine them.
  • User-defined. You create user-defined metadata extensions using PowerCenter/PowerMart. You can create, edit, delete, and view user-defined metadata extensions. You can also change the values of user-defined extensions.

A BUS schema is to identify the common dimensions across business processes, like identifying conforming dimensions. It has conformed dimension and standardized definition of facts.

ETL is extraction, trforming, loading process, you will extract data from the source and apply the business role on it then you will load it in the target the steps are :

  • define the source(create the odbc and the connection to the source DB)
  • define the target (create the odbc and the connection to the target DB)
  • create the mapping ( you will apply the business role here by adding trformations , and define how the data flow will go from the source to the target )
  • create the session (its a set of instruction that run the mapping )
  • create the work flow (instruction that run the session)

A virtual data warehouse provides a collective view of the completed data. It can be considered as a logical data model of the containing metadata.

Conformed fact in a warehouse allows itself to have same name in separate tables. They can be compared and combined mathematically. Conformed dimensions can be used across multiple data marts. They have a static structure. Any dimension table that is used by multiple fact tables can be conformed dimensions.

Granularity, is the level of detail in which the fact table is describing, for example if we are making time analysis so the granularity maybe day based - month based or year based.