Varchar

My Imagination – How Scientists and Software Testers are related to each other?

Posted on

Hi All,

Hope you guys had  wonderful X-Mas holidays :-).

Image

As we all know all the Inventions are made by Asking more and more questions.

If Newton would have not asked ‘Why Apple fallen down from the Tree’ then we would have not have Gravity Theory.

If Archimedes would have not asked ‘Why waters spilled out of the Tub when he was bathing?’ then we would have not have Archimedes principle.

Likewise if testers asks questions on applications like Why or How? if he/She finds the answer that is not matching with what it supposed to be then IT IS A Discovery, So we are Scientists 🙂

As far as I consider testing the application is asking questions about the application that I am testing – is perfect way of finding software anomalies.

Each functional defect that we stopping by our testing is just saving the production fixes but if we stop any Business Gaps then it saves the whole Business Need. So before kick of the testing make sure we know the Business Needs.

You will get a Functional Defect – If you ask How things are working? then it will be already mentioned in all the functional specifications ! if some functionality is missed out in the developed code can be found.

You will get a Req/ Specification / Design / Defect If you ask why things are working ? then you will need to check the Business Requirements, if you feel something is wrong then you will encounter few anomalies that may be from all the specifications related to that particular work request.

In this post I wanted to explain how Important the Domain knowledge is required for a Testers.

Domains like Banking, Health Care, Manufacturing, Insurance etc., All these domains are closely related to us.

To modernize these functional areas

> Business peoples will write Specs to cater the business needs as Business Requirement Documents.

> Considering the Business Specifications Solution Designers will prepare Functional Specification Documents.

> We testers and developers will refer the above documents and Develop and Test the application.

How you can learn Business easily ?

If you are working in a Banking Domain, you got Loan functionality to be tested in your Client website, then please create a Loan application in real time with your bank or with some other bank’s on-line application ,

If you are working in a Health Care Domain, you got Inventory functionality to be tested then go to nearby Chemist (Medical Shop) pick some medicines from here and there and go for billing and check how the shop keeper handling your goods.

Like above two examples, what ever business that you are testing, please do imagine that YOU ARE ALSO GOING TO USE THIS PRODUCT. Would you accept a Defective product from your manufacturer, ‘No’ right then your testing will be perfect.

I hope all the readers know about Validation and Verification ?

Let me tell you what I think about it,

Validation means, we need to verify all the documents that related to the given functionality are acceptable and valid.

Verification means, by validated specifications, the Code is written and its verified by us.

In Data warehouse world, the Specification documents are needs to be validated, because even a simple mistake will create a huge problem @ the end.

For an Example,

In warehouse we keep amount columns are in Negative (Bank Owe’s to us) as well as in Positive numbers (We Owe to bank).

Business Need – All the transactions of the day to be extracted

For extracts If specification documents asks us to pull the records where Balance > 0 then you will get the customers who are owing money to the bank.

So even a Single Symbol matters a lot !!! Before we start the Verification we need to Validate first!!!

Lets Discover along with Finding defects 🙂

Cheers – Asik

Advertisements

Chapter 13 – Unknown Verification in ETL Testing using an Online Job Application

Posted on Updated on

Hi All,

Hope you are enjoying your weekend. Have a wonderful weekend ahead !!!

Today I am going to explain how we need to take care of Nulls Blanks Spaces 0’s from Source System. These  Nulls Blanks Spaces 0’s are extracted from the upstream applications and given to us to load it into Ware house. But our Data Modelers may or may not want them to put into ware house as it will not give meaning to the business.

Image

Origination of Nulls Blanks Spaces 0s from Source System

I hope everyone used Online applications for your job search. You would have seen a ( * ) symbol in most of the field , so you need to enter some value.

1.In some field , you cant enter Numeric values

2.In some field , you can only enter Numeric Values

3.In some field , dates as per the given format

4.In some fields , you need to give your contact number as per the country standards

5.In some fields , you need to give your correct Email Id.

Like above you will be having so many check points for an online application. Say for an example, 2000 people applied for a Test Engineer job in my Company. And my Online Application has few Free Text field, applicant can enter what ever wanted to. In that few of them gave

1. Spaces in the Applicant Name (by mistake)

2. 0’s in the Experience column (because he is fresher)

3. Combinations of Spaces and 0s in the Phone Number field

4.They didn’t mention date fields.

My source system refers this Online webpage and pulls data and stores into their database. And will extract the data as files.

We will get the file as

D|Asik|8|06031987|9880112345|IND|PERSON@GMAIL.COM  – Valid Record

D||8|06031987|9880112345|IND|PERSON@GMAIL.COM – In Valid Record

We load these records into our  source tables we will not consider these NULLs Blanks 0s and Spaces as the Staging table data types are VarChar (it can have all types of data).

Our Source to Target specification will be taking care of these Unknowns.

a) If the source field_1 is NULL then populate Null in the corresponding target column_1

b) If the source field_2 is NULL then do not load the record

c) If the source field_3 is 0 then populate Null in the corresponding target column_3

d) If the source field_4 is 0 then do not load the record

e) If the source field_5 is BLANK then populate Null in the corresponding target column_5

f) If the source field_6 is BLANK then do not load the record

g) If the source field_7 is Space then populate Null in the corresponding target column_7

h) If the source field_8 is Space  then do not load the record

This verification can be done for each columns separately or collectively to one column like,

If Column_1 from source is NULL, BLANK, SPACE, 0 then populate NULL to the corresponding target column_1

of

If Column_1 from source is NULL, BLANK, SPACE, 0 then Do Not load the record into ware house.

Whatever the circumstances our ETL code should stop Unknown records loading into warehouse.

Most of the S2T extraction criteria designed to stop the records into Warehouse if (mentioned) column values are any of NULL BLANK SPACE. Source system does not have any validations to stop Unknowns When they extract from their upstream. These unknown checks are closely dependent on Data Types of the mentioned columns.When tester designs the SQL, to test these scenarios, he should consider the Data Type of the columns.

1. Integer: Teradata accepts only NULLs and 0s for INTEGER columns. If we insert BLANK or SPACE it will take it as 0s only

2. Decimal: Teradata accepts only NULLs and 0.00s for Decimal columns. If we insert BLANK or SPACE it will take it as 0.0s only

3. CHAR:  All NULL BLANK SPACE 0 can be possible for CHAR columns, so we need to test all the Unknown possibilities for CHAR columns

4. VARCHAR: All NULL BLANK SPACE 0 can be possible for VARCHAR columns, so we need to test all the
Unknown possibilities for VARCHAR columns

When we test VARCHAR column we should be very conscious because:

Source Record from file,
Scenario_01 – (0s with Space in VARCHAR columns)
|D| Asik| 51302138| 0|
These values will be loaded with spaces into staging tables. The tester designed SQL (where Column<>0) then this
record will not be stopped! So it should be (where TRIM(Column)<>0).
If you find any issues like this please raise the concern to Developer and ask them to add TRIM for
VARCHAR columns.

Scenario_02 (multiple 0000s in VRACHAR or CHAR columns)

|D| Asik| 51302138| 00| –> CHAR with 00
|D| Asik| 51302138|000000| –> VARCHAR with 000000
|D| Asik| 51302138|0| –>CHAR or VARCHAR with 0

Source can send 0, 00, 00000 to VARCHAR or CHAR columns, but the meaning is same its 0. If the tester designed SQL (where Column<>0) then only the 3rd record will be stopped whereas other two records will be loaded to in Target tables.

If you find any issues like this then please raise concerns to developer to add TRIM Leading 0 and check  <> 0.

Hope you all will Stop Unknown records into my warehouse.

Cheers Asik

My Next Post is will be a Sample Project !!!!

Chapter 12 – Know about Data Types and ETL Testing

Posted on Updated on

Hi All,

Have you created , Updated , Deleted Face book account to know about DWH concepts ? Today in this post let me explain you what is the necessity and importance of the Data types in ETL Testing.

water

We will start with our known examples :

Can you fill 10 liters of water into a 5 liters container?

“No, the container can have only 5 liters of water, if you fill more than its capacity then it will burst :-(”

Can you use Salt instead of sugar to make Tea?

“No, then every one will stop drinking Tea :-(”

Can we name a Kid using Numbers?

“No, if we keep numbers , then how many duplicate persons exists in this world ? just Imaging if  I was named as 10215 !!!!

Can anyone have their Bank balance as absolute number ?

“No, because every money that you spent is fractional amount ! you cant have $ 5 all the time , it would be $ 5.28 most of the time.

Can you have your mobile number more than 10 digit ?or Can you have your mobile number as alphabets? 

“No, because the mobile number length is pre-defined and the number cant be alphabets”

Like the above example

Our Source files, Source tables , Target tables are constructed with limitations. You cant have or keep the data that you want. You can keep or have the data that what system can accept.

In every programming we have this data types , most of them who reads this post knew about basics of Data types.

INTEGER, CHAR, VARCHAR, DECIMAL, FLOAT etc.,

Most of the time developers are testers encounters problems because of the data typing in Data warehouse world are ,

1. Correct Data type is not chosen in Source tables

2. Correct length of the data is not received from the source system in the source file

3. Source is not giving the values as per the data types mentioned

4. Data got truncated when loading it into Target tables.

5.The amount column precision is not populated correctly as Teradata changes it to make round off value.

6.Regarding Dates, source will send them as var-char but when we load it into target tables we keep as DATE and the format

The Data type and its length will be designed it its  DDL – Data Definition Language . If you want to know about the tables properties then please use the blow query

a) ” SHOW TABLE Database.Table_Name ” – this will give you all about data types, data length. Not Null, Null, Primary Key definitions

b) ” HELP TABLE Database.Table_Name” – this will give you all about the table. 

As a Tester what we need to verify ?

Again as I said,

Check the data is matching with the data type mentioned in the spec.

Check any data truncation happened when source data is loaded into Staging tables

Check the data is the staging tables are as per the Staging tables DDL

Check the target table columns are loaded as per the Target tables DDL.

If it a Varchar columns from source ,then please take care of the space , invalid characters etc., right from source till staging tables, because data stage will not accept special characters

If its a Decimal column then make sure the precision is carried out till the target load

If its a Integer column then make sure you should not get blanks ans spaces from source

If its is a CHAR then check the length of the character that fit into this column

Like above we can add as many as much scenarios for Data Type verification.

Hops this blog helps you to understand what is the importance of the Data Types in ETL testing,

See you in my next post

Cheers – Asik