Explore topic-wise InterviewSolutions in .

A lookup transformation allows USERS to access DATA from relational tables that are not defined in mapping documents. It enables users to UPDATE slowly changing dimension tables to determine whether the records ALREADY EXIST in the target or not.

A fact-less fact table doesn’t CONSIST of any MEASURES. It’s an INTERSECTION of dimensions.

It has two types.

• To capture an event
• To describe CONDITIONS

The logical structures where the database stores the schema object LOGICALLY within a database tablespace are defined as schema OBJECTS. These objects can be table, views, INDEXES, database LINKS and FUNCTION packages.

In ETL, cubes are DATA processing units which CONTAIN dimensions and fact TABLES from the data warehouse and provide multi-dimensional analysis. OLAP or Online Analytics Processing STORES large data in a multi-dimensional form for reporting purpose. The OLAP cubes consist FACTS called as measures categorized by dimensions.

In ETL, the Database Normalization is a process required to organize the tables and attributes of a relational database to MINIMIZE DATA redundancy. The process involves decomposing a table into less REDUNDANT tables without losing any information.

ETL testing’s cosmetic bug is related to the GUI of an APPLICATION. This bug can be related to font SIZE, font style, ALIGNMENT, colors, navigation, spelling mistakes, etc.

Here is a LIST of differences between ETL and DATABASE testing.

• ETL testing is focused on data extraction, TRANSFORM, and loading for BI reporting, whereas data validation and integration is the PRIMARY aim of database testing.
• Database testing applies to TRANSACTIONAL systems where the business flow takes places. Compared to this, ETL testing takes place in an order where historical data is a store.
• ETL testing includes multidimensional modeling, but database testing takes place in ER method modeling,

• CALCULATION BUGS
• Source bugs
• ECP RELATED bugs
• User-interface bugs
• Load CONDITIONAL bugs

An ETL mapping sheet contains all the required INFORMATION from the SOURCE file and stores them in rows and columns. It dramatically helps DEVELOPERS in writing SQL queries to SPEED quickly operate the testing process; The mapping sheet is created by the database designer.

Data loading process in ETL loads the prepared data from staging tables to main tables.

ETL has three types of data loading:

• Initial LOAD: It populates data tables from the source table and loads them in the data warehouse table.
• Full Refresh: It erases the data from one or more tables COMPLETELY and reloads the FRESH data.
• Incremental Load: It applies the ongoing CHANGES as REQUIRED periodically.

Data extracted from the source system NEEDS to be cleaned, mapped and TRANSFORMED before it loads into the target server.

Three steps need to be followed for data transformation:

• Selection: To select data and pack it in target
• Matching: Data matching with the target system.
• Data Transforming: CHANGE data as per target table STRUCTURES.

There are many test CASES available for ETL TESTING. Here are few best examples for consideration,

• Correctness Issues: Use to test INACCURATE DATA, misspelled data and null data.
• Data Check: Aspects regarding the data such as Number Check, Data Check, Null Check are tested in this case.
• Mapping Doc Validation: VERIFIES the ETL information present in the Mapping Doc.

In ETL testing, the data source view defines the relational schema which will be used in the DATABASES of analysis services. Cubes and dimensions are created from data source VIEWS, rather than DIRECTLY from data source OBJECTS.

The BUS Schema handles the Dimension IDENTIFICATION ACROSS business processes. Bus Schema in ETL comes with a conformed dimension along with a standardized DEFINITION of information.

The ETL TRANSACTIONS are NEEDED to be divided to achieve better performance. This process is known as ETL PARTITIONING. It makes sure that the server can DIRECTLY access the sources through multiple CONNECTIONS.

An ETL testing includes:

• Ensure that the ETL application must REPORT in all the invalid data and replace them with a default value.
• Confirm whether the data is transforming correctly according to the business requirements.
• Ensure that data loads at EXPECTED TIME frame to IMPROVE performance and scalability.
• Assay the projected data should be LOADED into the data warehouse without any loss and truncation.