Explore topic-wise InterviewSolutions in .

There is always the possibility of duplicate or mislabeled data when combining multiple data sources. Incorrect data leads to unreliable outcomes and algorithms, even when they appear to be correct. THEREFORE, consolidation of multiple data representations as WELL as elimination of duplicate data become essential in order to ensure accurate and consistent data. Here comes the importance of the data cleaning process.  

Data cleaning can also be referred to as data scrubbing or data CLEANSING. This refers to the process of removing incomplete, duplicate, corrupt, or incorrect data from a dataset. As the need to integrate multiple data sources becomes more apparent, for example in data warehouses or federated database systems, the significance of data cleaning increases greatly. Because the specific steps in a data cleaning process will VARY depending on the dataset, developing a TEMPLATE for your process will ensure that you do it correctly and consistently.

As the name suggests, ETL pipelines are the mechanisms to perform ETL processes. This involves a series of processes or activities required for transferring data from one or more sources into the data warehouse for analysis, reporting and data synchronization. It is IMPORTANT to move, consolidate, and ALTER source data from multiple systems to match the parameters and capabilities of the destination database in order to provide valuable INSIGHTS. 

Among its benefits are: 

• They reduce errors, bottlenecks, and latency, ensuring the smooth flow of information between systems.
• With ETL pipelines, businesses are able to achieve competitive advantage.
• The ETL pipeline can CENTRALIZE and standardize data, allowing analysts and decision-makers to easily access and use it.
• It facilitates data migrations from legacy systems to NEW repositories.

Business Intelligence (BI) involves acquiring, cleaning, analyzing, integrating, and sharing data as a means of identifying actionable insights and enhancing business growth. An EFFECTIVE BI test verifies staging data, ETL process, BI reports, and ensures the implementation is reliable. In simple words, BI is a technique used to gather RAW business data and transform it into useful insight for a business. By performing BI TESTING, insights from the BI process are verified for ACCURACY and credibility. 

Data validation is involved in both ETL testing and database testing, however, the two are different. The ETL testing PROCEDURE normally involves analyzing data stored in a warehouse SYSTEM. On the other hand, the database testing procedure is commonly used to analyze data stored in transactional systems. The following are the distinct differences between ETL testing and Database testing. 

Several analysis services databases rely on relational schemas, and the Data source view is responsible for defining such schemas (logical model of the schema). Additionally, it can be easily used to create cubes and dimensions, thus enabling users to set their dimensions in an intuitive way. A multidimensional model is incomplete without a DSV. In this way, you are given complete control over the data structures in your project and are able to work independently from the underlying data sources (e.g., changing column names or concatenating columns without directly changing the original data source). Every model must have a DSV, no matter when or how it's created.

Using the Data Source View Wizard to create a DSV

You must run the Data Source View Wizard from Solution EXPLORER within SQL Server Data TOOLS to create the DSV.

• In solution explorer, Right Click Data source view folder -> Click NEW Data Source View.
• Choose one of the available data source objects, or add a new one.
• Click ADVANCED on the same page to specifically select schemas, apply a filter, or exclude information about table relationships.
• Filter Available Objects (If we use a string as a selection criterion, it is possible to prune the list of the available objects).
• A Name Matching page appears if there are no table relationships defined for the relational data source, and you can choose the appropriate method for matching names by CLICKING on it.

• ETL tools: The DATA is extracted, transformed, and loaded into the data warehouse or data mart using ETL tools. Several transformations are necessary before data is loaded into the target table in ORDER to implement business logic.  Example: Data stage, Informatica, etc. 
• OLAP (Online Analytical Processing) tools: OLAP tools are designed to create reports from data warehouses and data marts for business ANALYSIS. It loads data from the target tables into the OLAP repository and performs the required MODIFICATIONS to create a report. Example: Business OBJECTS, Cognos etc.

When data needs to be deleted from the data warehouse, it can be a very tedious task to delete data in bulk. The term data purging refers to methods of permanently erasing and removing data from a data warehouse. Data purging, often contrasted with deletion, involves MANY different techniques and strategies. When you delete data, you are removing it on a temporary BASIS, but when you purge data, you are permanently removing the data and freeing up memory or storage space. In GENERAL, the data that is deleted is usually JUNK data such as null values or extra spaces in the row. Using this approach, users can delete MULTIPLE files at once and maintain both efficiency and speed. 

Both data mining and data warehousing are powerful data analysis and storage techniques.  

• Data warehousing: To generate meaningful business insights, it involves COMPILING and organizing data from various sources into a common database. In a data warehouse, data are cleaned, integrated and consolidated to support management decision-making processes. Object-oriented, integrated, time-varying, and NONVOLATILE data can be stored within a Data warehouse.

• Data mining: Also referred to as KDD (Knowledge Discover in Database), it involves searching for and identifying hidden, relevant, and potentially valuable patterns in LARGE data sets. An important goal of data mining is to discover previously unknown relationships among the data. Through data mining, insights can be extracted that can be used for things such as marketing, fraud detection, and scientific discoveries. 

Difference between Data Warehouse and Data Mining -

An enterprise data WAREHOUSE can be divided into subsets, also called data marts, which are focused on a particular business unit or department. Data marts allow selected groups of users to easily access specific data without having to SEARCH through an entire data warehouse. Some companies, for example, may have a data mart aligned with purchasing, SALES, or inventories as shown below: 

In contrast to data warehouses, each data mart has a UNIQUE set of end users, and building a data mart takes less time and costs less, so it is more suitable for SMALL businesses. There is no duplicate (or unused) data in a data mart, and the data is updated on a regular basis.

Typically, ETL tool-based data warehouses use STAGING areas, data integration layers, and access layers to ACCOMPLISH their work. In general, the architecture has THREE layers as shown below: 

• Staging Layer: In a staging layer, or source layer, data is stored that is extracted from MULTIPLE data sources.
• Data Integration Layer: The integration layer plays the role of transforming data from the staging layer to the database layer.
• Access Layer: Also called a dimension layer, it allows USERS to retrieve data for analytical reporting and information retrieval.

In spite of the importance of ETL TESTING, companies may face some challenges when trying to implement it in their applications. The volume of data INVOLVED or the heterogeneous nature of the data makes ETL testing challenging.   Some of these challenges are listed below:    

• Changing CUSTOMER requirements result in re-running test cases.
• Changing customer requirements may necessitate a tester creating/modifying new mapping documents and SQL scripts, resulting in a long and tedious process.
• Uncertainty about BUSINESS requirements or employees who are not aware of them.
• During migration, data loss may occur, making it difficult for source-to-destination reconciliation to take place.
• An incomplete or corrupt data source.
• Reconciliation between data sources and targets may be impacted by incorporating real-time data.
• There may be memory issues in the system due to the large volume of historical data.
• Testing with inappropriate tools or in an UNSTABLE environment.

SINCE ETL testing is so important, ETL testers are in great demand. ETL testers validate data sources, extract data, apply transformation logic, and LOAD data into target tables. The following are key responsibilities of an ETL TESTER: 

• In-depth KNOWLEDGE of ETL tools and processes.
• Performs thorough testing of the ETL software.
• Check the data warehouse test component.
• Perform the backend data-driven test.
• Design and execute test cases, test PLANS, test harnesses, etc.
• Identifies problems and suggests the best solutions.
• Review and approve the requirements and design specifications.
• Writing SQL queries for testing scenarios.
• Various types of tests should be carried out, including primary keys, defaults, and checks of other ETL-related functionality.
• Conducts regular quality checks.

Before you begin the testing process, you need to define the right ETL Testing technique. It is important to ensure that the ETL test is performed USING the right technique and that all stakeholders agree to it. Testing team members should be familiar with this technique and the STEPS involved in testing. Below are some types of testing techniques that can be used: 

• Production Validation Testing: Also known as "production reconciliation" or "table balancing," it involves validating data in production systems and comparing it against the source data.
• Source to Target Count Testing: This ensures that the number of records loaded into the target is consistent with what is expected.
• Source to Target Data Testing: This entails ensuring no data is lost and truncated when loading data into the warehouse, and that the data values are accurate after transformation.
• Metadata Testing: The process of determining whether the source and target systems have the same schema, data types, lengths, indexes, constraints, etc.
• Performance Testing: Verifying that data loads into the data warehouse within predetermined timelines to ensure speed and scalability.
• Data Transformation Testing: This ensures that data transformations are completed according to various business rules and requirements.
• Data Quality Testing: This testing involves checking numbers, dates, nulls, precision, etc. Testing includes both Syntax Tests to report invalid CHARACTERS, INCORRECT upper/lower case order, etc., and Reference Tests to check if the data is properly formatted.
• Data Integration Testing: In this test, testers ensure the data from various sources have been properly incorporated into the target system, as well as verifying the threshold values.
• Report Testing: The test EXAMINES the data in a summary report, verifying the layout and functionality, and making calculations for subsequent analysis.

The use of ETL testing tools increases IT productivity and facilitates the process of extracting insights from big DATA. With the tool, you no LONGER have to use labor-intensive, costly traditional programming METHODS to extract and process data.  

Technology EVOLVED over time, so did solutions. Nowadays, various WAYS can be used for ETL testing depending on the source data and the environment. There are several ETL vendors that focus on ETL exclusively, such as Informatica. Software vendors like IBM, Oracle, and Microsoft provide other tools as well.  Open source ETL tools have also recently emerged that are free to use. The following are some ETL software tools to consider:   

Enterprise Software ETL 

• Informatica PowerCenter
• IBM InfoSphere DataStage
• Oracle Data Integrator (ODI)
• Microsoft SQL Server Integration Services (SSIS)
• SAP Data Services
• SAS Data Manager, etc.

Open Source ETL 

• Talend Open Studio
• Pentaho Data Integration (PDI)
• Hadoop, etc.

ETL testing is made easier when a testing strategy is well defined. The ETL testing process goes through different phases, as illustrated below: 

• Analyze Business Requirements: To perform ETL Testing effectively, it is crucial to understand and capture the business requirements through the use of data models, business FLOW diagrams, reports, etc.
• Identifying and Validating Data Source: To proceed, it is necessary to identify the source data and perform preliminary checks such as schema checks, table counts, and table validations. The purpose of this is to make sure the ETL process matches the business model specification.
• Design Test Cases and Preparing Test Data: Step three includes designing ETL mapping scenarios, developing SQL scripts, and defining transformation RULES. Lastly, verifying the documents against business needs to make sure they cater to those needs. As soon as all the test cases have been checked and approved, the pre-execution check is performed. All three steps of our ETL processes - namely extracting, transforming, and loading - are covered by test cases.
• Test Execution with Bug Reporting and Closure: This process continues until the exit criteria (business requirements) have been met. In the previous step, if any defects were found, they were sent to the developer for fixing, after which retesting was performed. MOREOVER, regression testing is performed in order to prevent the introduction of new bugs during the FIX of an earlier bug.
• Summary Report and Result Analysis: At this step, a test report is prepared, which lists the test cases and their status (passed or failed). As a result of this report, stakeholders or decision-makers will be able to properly maintain the DELIVERY threshold by understanding the bug and the result of the testing process.
• Test Closure: Once everything is completed, the reports are closed.

Following are some of the notable benefits that are highlighted while ENDORSING ETL TESTING: 

• Ensure data is transformed efficiently and QUICKLY from one system to another.
• Data quality ISSUES during ETL processes, such as duplicate data or data LOSS, can also be identified and prevented by ETL testing.
• Assures that the ETL process itself is running smoothly and is not hampered.
• Ensures that all data implemented is in line with client requirements and provides accurate output.
• Ensures that bulk data is moved to the new destination completely and securely.