What is Online and Offline Extraction?

Online Extraction:

Here the data is extracted directly from the Source for processing in the staging area, that’s why it’s called online extraction. During Extraction we connect directly to the source system and then access the source tables. There is no need of any external staging area

Offline Extractions:

Here the data is not extracted directly from the source, but instead it’s taken from another external area which keeps the copy of source. The external area can be Flat files, or some dump files in a specific format. So when we need to process the data we can fetch the records from the external source instead of the actual source.

Audit Columns in Datawarehouse

In data warehouse and data marts we maintain timestamp columns like “Last_Modified_Date or Created_Date” to track the exact date and time when the records were updated or inserted.These columns are called Audit Columns. But audit columns won’t track the deletion of columns. Last modified date stores the timestamp for record when it was modified for the last time and Created date will store the timestamp for the date on which the record was created, mostly we give it as SYSDATE

After we load the data for a particular day we can easily identify these newly loaded records by using audit columns. Whenever a record is inserted or deleted it makes a fresh timestamp entry through the Audit columns. So make sure that you include the Audit columns.

Snapshot sources in Datawarehouse

There are some source systems or an application that lacks the interface needed to extract data for ETL Processing .In that case we cannot directly extract from the source.What we can  do is to take copy of the source data and dump into flat file. This file will give a snapshot of the source system and hence is called Snapshot Sources.

Snapshot Source provides the snapshot of the Data at the time of data extraction. We can track the change of data by comparing successive snapshots. This approach is also known as snapshot differential.

Pipeline Partitioning in Informatica

Pipeline Partitioning a mapping in Informatica actually means to Partition the pipeline inside the mapping. Hope you all are aware of the various Partitions available in Oracle, if not please check about Partitions in ORACLE.

A pipeline will contain the source, transformations and targets. Mapping will be subdivided into many pipelines, these are called stage pipeline .After enabling partitioning in stage pipeline when we run the Integration service it runs pretty faster and we can expect more performance. The Integration service will run the partition threads concurrently.

Now it’s about how to enable partition and where to enable partition

• Set the partition points in the mapping
• Set the number of partitions
• Set the partition types

Partition points marks the boundaries that divide the pipeline into stages. By default Integration Service will keep partition at various transformations. Partition points mark the points in the pipeline where the Integration Service can redistribute data across partitions.

When we give Partition points it actually creates various stage pipelines inside the mapping. Now we need to decide how many partitions we need to have inside one stage pipeline. When we add more partitions it increases the performance of Informatica. You can define up to 64 partitions at any partition point in a pipeline.Also note that if we define two Partitions at any partition point, then the remaining partition points will also have two partitions.

 

By default Integration service will create a default Partition type for each partition point. Also we have the option of explicitly giving the Partitioning Type. The type of partition decides how Integration service will distribute the data across components. Below are the various Partitions available:

• Database partitioning
• Hash user keys
• Hash auto-keys
• Key range
• Pass-through
• Round-robin

Regression Testing Phase in ETL Testing

The steps are,

• Ensure that current functionality stays intact whenever new code is released

Performance Testing Phase in ETL Testing

The steps are,

• Verify that data loads and queries are executed within anticipated time frames
• Verify that maximum anticipated volume of data is loaded within an acceptable time frame
• Verify load times with various amounts of data to predict scalability

User Acceptance Testing(UAT) Phase in ETL Testing

The steps are,

• Verify that the business rules have been met
• Confirm that the system is acceptable to the client

Report Testing Phase in ETL Testing

The steps are,

• Verify report data with the data source
• Create SQL queries to verify source/target data
• Verify field-level data

Integration Testing Phase in ETL Testing

The steps are,

• Verify the sequence and outcome of ETL batch jobs
• Verify that ETL processes function with upstream and downstream processes
• Verify the initial load of records on data warehouse
• Verify any incremental loading of records at a later date for newly inserted or updated data
• Test the rejected records that fail ETL rules
• Test error log generation