Data Warehouse Testing—The Next Opportunity for QA Leaders

© 2018 by
© 2018 by
Curtis O’Dell, Raj Kanuparthi, Wayne Yaddow
Data Warehouse Testing -
The Next Opportunity For QA Leaders

© 2018 by
Wayne Yaddow
2
• DWH/BI QA consultant, 12 years
• Co-author the book: “Testing the Data Warehouse”
• DHW/BI training: “Testing Process and Tools for Data
Migration, Data Integration, and DWH/BI ETL Projects“
• Author of ETL testing blogs & articles
• Coach/mentor for DWH/BI testers
wyaddow@gmail.com

RajKanuparthi
Professional background and journey
3
Founder & CEO at Narwal Inc. , focused on Test
Automation, DevOps and Big Data areas.
Most recently led Enterprise Quality Assurance,
DevOps, SDLC Tools and Program Delivery at
World’s largest payment processing company at
enterprise scale
Implemented a robust Big Data Testing Automation
framework from the ground up
20 years of well-rounded experience in all areas
Software Development
MBA from Kelley School of Business, Indiana
University and a Bachelor’s degree in Engineering

© 2018 by
Topics for this Session:
• How DWH/BI testing compares with “traditional”
software testing
• How DWH testing fits into the broader DWH/BI
project quality strategy

© 2018 by
PM’s are Calling for DWH/BI Project-wide
Test Automation
Why?
DWH/BI manual testing can be a huge effort
§ Comparing and verifying data
§ Error prone (e.g., programming, debugging SQL)
§ Heterogeneous, complex data
§ Data transformations
§ Project testers need high-level data & SQL skills
§ Manual QA impacts project schedules
§ Testing becomes more limited in scope
Common outcome of largely manual testing? uncertain data quality!
5

© 2018 by
PM’s are Calling to Maximize DWH/BI Project-wide
Test Automation
6
Solutions?
1. Research for tools to implement DWH/BI test automation
2. Accelerate automation of DWH validations, verifications
3. Accelerate automation of BI app/report testing
…where possible, use familiar test tool architectures for
UI, data, and BI apps

© 2018 by 7
“It appears that DWH/BI design experts can tell
us how to build data warehouses, but do not
address well, the subject of validating
correctness during ETL’s and BI reporting.”
C. Bateman, "Where are the Articles on Data Warehouse Testing and
Validation Strategy?," Information-Management Magazine

© 2018 by
DWH/BI Project References
8

© 2018 by
DWH/BI Data Mapping
9

© 2018 by
Common DWH/BI Architecture
10

© 2018 by
UI Front-end vs. DB Back-end Testing
UI Testing Database, DWH, Data Testing
This type of testing is also known as User Interface testing or
Front-end Testing.
This type of testing is also known as back-end testing or
data testing.
This type of testing chiefly deals with all the testable items that
are open to the user for viewership and interaction like Forms,
Presentation, Graphs, Menus, and Reports, etc.. (created through
VB, VB.net, VC++, Delphi - Frontend Tools )
This type of testing chiefly deals with all the testable items
that are generally hidden from the user for viewership.
These include internal process and storage, DBMS like
Oracle, SQL Server, MYSQL.
Validate
• Text boxes
• Dropdown lists
• Calendars and buttons
• Navigation from one page to another
• Display of images
• Look and feel of the overall application
Validate
• DB schemas (source and target)
• Data quality
• Columns / fields completeness
• Keys and indexes
• Stored procedures, packages, views
• Event triggers
• Database server functions
• Data transformations & business rules
• BI reports
Testers must be thoroughly knowledgeable about business
requirements; also, usage of the development tools and the usage
of automation framework and tools.
Testers need strong expertise in database management
functions and SQL concepts.
11

© 2018 by
Verifications of Primary DWH/BI Components
12
Test Type
DWH/BI Components
Schema ETL Workflow Database Front-end UI
Functional * * * *
Usability * *
Performance * * * *
Stress * * *
Recovery * *
Security * * *
Integration * * * *
Regression * * * *
End-to-End * * * *

© 2018 by
DWH/BI Testing Maturity Model
Initial Managed Defined Measured Optimized
• DWH/BI testing
process is not
clearly defined
• No integrated
DWH/BI test
planning &
strategy based
on BI Vision
• DWH/BI testing
not documented
• Initialized BI Test
Policy & Test
Strategy
• Test planning
• Test monitoring
& control
• Implementing BI Test
Policy & Test Strategy
• Testing of DWH/BI end
to end
• Testing MDM
• Nonfunctional testing
(ex., performance,
security)
• Test
measurement
& test matrix
• BI data quality
assessment
• Comparing QA
against
industry
standards
• BI defect
prevention
• Test process
improvement
• DWH/BI
automation focus
• Security,
performance,
UAT testing, full
regression tests
Low to High Test Process Maturity

© 2018 by
DWH/BI Test Lead Skills / Experiences
• Firm knowledge of DWH, BI and database concepts
• Advanced expertise with…
– SQL queries
– Stored procedures
– DB and SQL editors
– Test automation
• Expert data profiling methods & tool skills
• Exceptional skills with MS Excel / Access for data analysis

© 2018 by
DWH/BI Test Lead Skills / Experiences (cont.)
§ Solid understanding of data models, data mapping documents, ETL design
and ETL coding.
§ Experience with multiple DB systems: Oracle, SQL Server, Sybase, DB2
§ Skills testing “change data capture” (CDC), “slowly changing dimensions”
(SCD)
§ Ability to troubleshoot ETL tool workflows, stored procedures
§ Ability to perform adequate testing with huge volumes of data. Skill to select
data samples
§ An understanding of testing database code deployments

“75% of businesses are wasting 14% of revenue
due to poor data quality.” – Experian
“Data volume is expected to expand by 800%
in the next 5 years” - Gartner
Companies need to start making data driven decisions today
and accelerate this journey
17

Problem Statement
• Data and reports were not accurate. Users abandoned the data products.
• Data modification was done in production with live data
• Data Quality rules, Data Governance, Data definitions were not in place
• Lack of understanding of tools for Data Testing, which is different from UI
18

Challenges - Process
• Testing in Big Data or DW is not well understood
• Testing happens in UAT or Pre-Prod or Production
• No widespread, coordinated testing in place
• The process, governance and framework are not in place
• Understanding of different layers and levels of testing
• End to End Reconciliation of data is complex
19

Challenges – People, Culture
• Most Traditional QA teams don’t understand it. QA is not a priority for Dev Teams
• Development, QA and other teams are often not collaborating
• Differing stakeholder interests and opinions on the QA value, process and tools
• Not taking a longer-term or big picture view on Cost, Speed to delivery, Quality
• Data testing requires good SQL skills
• Training for Big Data Testing is not widely available
20

Challenges – Tools, Technology
• Automation is mandatory in Data Testing
• Lack of understanding about what type of tools can be used
• The volume, Infrastructure, Performance, Speed
• Test Environments
• Different types of Testing, Data Validation and Profiling
21

Learnings/ Recommendations
• Conduct an Assessment. Leverage Partners.
• Institute Testing Methodology and Governance in place
• Right Culture/mindset - QA to be part of the team
• Start automation while you’re starting the Big Data/DWH Journey
• Leverage toolset that can be easily integrated, easy to learn and implement
22

Data Testing Approach
23
Sources
Extraction Logic
Raw Audited Certified
ETL ETL
DatamartsHarmonized
ETL
Missing data
Delimiter Issue
Date format
DQ Business Rules Null Translation
Referential Errors
Harmonization Logic
Numeric precision
Aggregation
Logic
KPI logic
Calculated Measures
Hierarchies
User Access
User Security
Layout
Drill down/thru
Filters/Prompts
Formats
Exports
Reports
QA QA IDQ
Verify one to one match
between Source and Raw
after applying cleansing
rules
Verify that DQ Business
Rules are properly
implemented, and
appropriate records are
being sent to error table.
This will be done by
the DQ/DQ team
Business Views
Create custom
reports to test
business views
ETL ETL
QA
QA
Test aggregation
logic
QA
Verify one to one
match and additional
aggregation logic
QA
Verify that the
correct data loads
for user
Visualization Visualization

Success/ Benefits
• Over 90% improvement in Cost, Effort and Time-to-Market
• Savings of thousands of hours of manual effort on monthly basis, with reusable automated
regression for data validation
• Improved user adoption and productivity as a result of improved data quality
• Tricentis Tosca and Tosca BI enabled a mindset shift and helped mature data testing
• End to End Automated Testing of all data layers – Ingestion, Transformation, Data Marts,
Reporting and UI (Driven by QA Team)
24

Thankyou
Raj Kanuparthi
Raj@narwalinc.com
25
Curtis O’Dell
c.odell@tricentis.com
Wayne Yaddow
wyaddow@gmail.com

Data Warehouse Testing—The Next Opportunity for QA Leaders

Recommended

Recommended

More Related Content

What's hot

What's hot (20)

Similar to Data Warehouse Testing—The Next Opportunity for QA Leaders

Similar to Data Warehouse Testing—The Next Opportunity for QA Leaders (20)

Recently uploaded

Recently uploaded (20)

Data Warehouse Testing—The Next Opportunity for QA Leaders