Cartesian Data

Another term, if data modelers are not aware and if data model doesn’t take care via proper Data Cardinality check and balance, data analysts are hit by Chasm and Fan Traps.

We know what Data Modelling is. We know what Data Cardinality is. Now, it’s time to understand Cartesian data which give nightmares to Finance department when they manually know the total is USD100k but when they run query, they see USD200 k 😊.

‘Cartesian Data is about writing a query on tables with ‘Many to Many (M:M)’ relationship.’

If there are two tables with ‘Many to Many’ relationship, it’s time for data modeler to introduce an intermediary table which can have unique records and then can have ‘One to Many’ relationship with previously used two tables.

If the Sales table is joined with Target tables directly based on Employee ID then surely there will Cartesian data as both Sales and Target tables can have more than 1 employee ID. But hang-on, even having intermediary table, if query is written wrongly then still data analyst can hit with Cartesian data know as Chasm Trap.

Referring to the same example from the topic ‘The Chasm and Fan Trap’, consider there is an employee name i.e., John in Employee table. He has 2 Targets to achieve e.g., 1) For Product 1 = Target USD 1M, 2) For Product 2 = Target USD 2M. He has done 10 Sales Transactions in-total, let’s say USD 10k each transaction mean in-total as of now he has achieved USD 100k. Now, extracting USD100k information via a query, if we join all these 3 tables in one query, this is what will happen.

• Query will join Employee table with Target table and will process 2 rows in in-memory i.e.,
  • Row1: John with Target of USD 1M for Product 1
  • Row2: John with Target of USD 2M for Product 2
• Now, Query has 2 rows in in-memory to join with Sales table. Now, it will join these 2 Johns with Sales table using John as key, this is what will happen.
  • John [Product 1] will join with 10 rows in Sales and will generate 10 Rows.
  • John [Product 2] will join with 10 rows in Sales and again will generate 10 Rows.
  • So, in total there will be 20 rows generated which is wrong as now total target achieved will show as USD 200k instead USD100 which is wrong.
• The issue is Cartesian Result.
• Now let’s investigate how to resolve this issue. There are two approaches to handle this scenario 1) write two queries separately 2) write 2 queries with-in 1 query and use UNION ALL. Let’s choice 2nd Approach and understand with an example.
• 1st Query will join Employee table with Target table and will process 2 rows in in-memory i.e.,
  • Row1: John with Target of USD 1M for Product 1
  • Row2: John with Target of USD 2M for Product 2
• 2nd Query will join Employee table with Sales table and will process 10 rows in in-memory i.e.,
  • Row1: John with Sales of USD 10k for Product 1
  • Row2: John with Sales of USD 10k for Product 2
  • .
  • .
  • Row10: John with Sales of USD 10k for Product 2
• Now, join both queries using UNION ALL and you will see 12 rows.
• Correct result.

I won't go into detail but when you join two tables having duplicates at both sides then data will multiply itself e.g., Dept. (department table) has 2 rows of employee ID i.e., that employee might have worked in multiple departments during his engagement with the company. Let's say, the second table is Sal (salary table) and assumes that the employee is with this company for 1 year so there will be 12 rows i.e., 1 row for each month right. Now, when you join Dept. and Sal tables using the employee ID field, the output will be 24 rows and the total salary will be double as compared to what it should be. So, by right Dept table should never join with Sal table using Employee ID. Both should first join Employee Table as it always has a unique number of employee rows.