Data Warehouse Testing

Data quality costs (companies) an estimated $14.2 million annually”

– Gartner

Bad data caused by defects in the ETL process can cause data problems in reporting that can result in poor strategic decision-making. According to analyst firm Gartner, bad data costs companies, on average, $14 million annually with some companies losing as much as $100 million.

3 Most Common Methods used for Data Warehouse/ETL Testing

The #1 method to compare data movement from data sources to a target data warehouse is Sampling, also known as“Stare and Compare”. It is an attempt to verify data by extracting it from source and target stores and dumping the data into 2 Excel spreadsheets and then viewing or“eyeballing” the 2 sets of data for anomalies. Less than 1% of data is usually verified and reporting is manual.

The #2 method is a MINUS Query that uses the MINUS operator in SQL to subtract one result set from another result set to evaluate the difference. If there is no difference, there is no remaining result set. If there is a difference, the resulting rows will be displayed. It is inefficient and produces no audit trail or reporting.

The #3 method companies employ are homegrown tools, utilities or frameworks to perform ETL Testing. While this may be tailored to and work for a company’s specific needs, they are very expensive to build (think lots of consulting dollars) and maintain.

75% of businesses are wasting 14% of revenue due to poor data quality”

– Experian Data Quality

**Query*Surge*** — The Data Warehouse /ETL Testing Solution

QuerySurge is the smart data testing solution that leverages artificial intelligence to speed up and simplify the validation & testing of Data Warehouses and the ETL testing process. QuerySurge ensures that the data extracted from data sources remains intact in the target data warehouse by analyzing and pinpointing any differences quickly.

Point-to-Point Testing. The QuerySurge ETL testing process mimics the ETL development process by testing data from point-to-point along the data warehouse lifecycle and can provide 100% coverage of your data mappings.

ETL Developer - Data Tester — Click to Enlarge

Test across 200+ data stores. QuerySurge supports connections to data warehouses and databases, big data and NoSQL data stores, files and APIs, collaboration software, CRMs and ERPs, and accounting, marketing and ecommerce software. See the full list here»

19.2% of big data app developers say quality of data is the biggest problem they consistently face.”

– Evans Data Corporation

Key Features. Here are some of the key features of QuerySurge:

Artificial Intelligence – Leverage AI-powered technology to automatically and quickly create data validation tests, including transformational tests, based on data mappings with our generative AI solution
Projects — Multi-project support, assign users and agents, user activity log reports
Smart Query Wizards — Create tests visually, without writing SQL
Create Custom Tests — Modularize functions with snippets, set thresholds, stage data, check data types & duplicate rows, full text search, asset tagging
Scheduling — Run test immediately, at a predetermined date & time or after any event from a build/release, CI/CD, DevOps or test management solution
DevOps for Data — API Integration (both RESTful & CLI) with build/release, continuous integration/ETL , operations/DevOps monitoring, test management/issue tracking and more
Data Analytics & Data Intelligence — Data Analytics dashboard, Data Intelligence reports, auto-emailed results, Ready-for-Analytics back-end data access
Test Management Integration — Out-of-the-box integration with Azure DevOps, IBM RQM, Micro Focus (formerly HP) ALM, Atlassian Jira and any other solution with API access
BI Testing — Testing data embedded in Microsoft Power BI, Tableau, SAP BusinessObjects, MicroStrategy, IBM Cognos or Oracle OBIEE
Available On-Premises and In-the-Cloud — Install on a bare metal server, virtual machine, any private cloud or in the Microsoft Azure Cloud as a pay-as-you-go service
Security — AES 256-bit encryption, support for LDAP/LDAPS, TLS, Kerberos support, HTTPS/SSL, auto-timeout, security hardening and more