Data Vault: Relationship between LINK and SAT on Historical analysis using SQL

Question

In Data Vault Model, we have below tables:

Details on how LINKTradeinVehicle and SAT_Order are inserted below:

Problem statement:

We need to know historically which data between LINK and SAT tables on the basis of LINK's orderkey and vehicleKEY relationship on period of time (creationDate) i.e., historical analysis as well.

And the latest record status can be fetched without issues on basis of max on creation date for a given orderkey and vehiclekey combination.

Note:

• CreationDate between tables are always varies depending on data we get, i.e., on creationdate 02.01.2023 we got the two vehicles for same order and we have inserted the same at both places i.e., LINK and SAT. On third day 03.01.2023 we have some changes in order attributes but the vehicle linked to that order is not changed and hence we do not add/ update anything in LINK table and only we insert it in SAT and on fourth day, the order has only one vehicle instead of two on previous instance and some change in attribute of order as well, hence there are insert at both ends.

• OrderHash is a unique value generated for each row by considering all columns which comes from source (json)

• We cannot query through the creationdate as clause statements

Below are the output we are looking for in a separate queries i.e., Historical analysis:

We tried with maintaining orderHash from SAT to LINK but it is an overhead as it will create more records in LINK table for every row from source system and did not yield the expected result.

Below are the output we are looking for in a separate queries i.e., Historical analysis:

CREATE TABLE LINK_Tradein (
  ordervehiclekey varchar(255),
  orderkey varchar(255),
  vehiclekey varchar(255),
  creationdate date
);
CREATE TABLE SAT_Order (
  orderkey varchar(255),
  orderhash varchar(255),
  orderamt int,
  agentemployee varchar(255),
  creationdate date
);
INSERT INTO LINK_Tradein
  VALUES ('111', 'ABC', '1', '01.01.2023');
INSERT INTO LINK_Tradein
  VALUES ('111', 'ABC', '1', '02.01.2023');
INSERT INTO LINK_Tradein
  VALUES ('222', 'ABC', '1', '02.01.2023');
INSERT INTO LINK_Tradein
  VALUES ('111', 'ABC', '1', '04.01.2023');
INSERT INTO SAT_Order
  VALUES ('ABC', 'ZZZ', 3000, 'Smith', '01.01.2023');
INSERT INTO SAT_Order
  VALUES ('ABC', 'YYY', 5000, 'Smith', '02.01.2023');
INSERT INTO SAT_Order
  VALUES ('ABC', 'AAA', 5000, 'Doe', '03.01.2023');
INSERT INTO SAT_Order
  VALUES ('ABC', 'XXX', 2000, 'Doe', '04.01.2023');

Answer 1

Based on what you provided, I am assuming that your Data Vault model consists of several tables: LINK, SAT_Order, and SAT_TradeinVehicle. The problem statement is to perform historical analysis by linking the data between the LINK and SAT tables based on the orderKey relationship and the creationDate.

CREATE TABLE LINK_Tradein (
  ordervehiclekey varchar(255),
  orderkey varchar(255),
  vehiclekey varchar(255),
  creationdate date
);
CREATE TABLE SAT_Order (
  orderkey varchar(255),
  orderhash varchar(255),
  orderamt int,
  agentemployee varchar(255),
  creationdate date
);
INSERT INTO LINK_Tradein
  VALUES ('111', 'ABC', '1', '01.01.2023');
INSERT INTO LINK_Tradein
  VALUES ('111', 'ABC', '1', '02.01.2023');
INSERT INTO LINK_Tradein
  VALUES ('222', 'ABC', '1', '02.01.2023');
INSERT INTO LINK_Tradein
  VALUES ('111', 'ABC', '1', '04.01.2023');
INSERT INTO SAT_Order
  VALUES ('ABC', 'ZZZ', 3000, 'Smith', '01.01.2023');
INSERT INTO SAT_Order
  VALUES ('ABC', 'YYY', 5000, 'Smith', '02.01.2023');
INSERT INTO SAT_Order
  VALUES ('ABC', 'AAA', 5000, 'Doe', '03.01.2023');
INSERT INTO SAT_Order
  VALUES ('ABC', 'XXX', 2000, 'Doe', '04.01.2023');

8 rows affected

SELECT
  L.ordervehiclekey,
  L.orderkey,
  L.vehiclekey,
  S.orderhash,
  S.orderamt,
  S.agentemployee
FROM
  LINK_Tradein L
JOIN
  SAT_Order S ON L.orderkey = S.orderkey 
WHERE
  L.creationdate <= S.creationdate
  AND NOT EXISTS (
    SELECT 1
    FROM LINK_Tradein L2
    WHERE L2.orderkey = L.orderkey
      AND L2.vehiclekey = L.vehiclekey
      AND L2.creationdate <= S.creationdate
      AND L2.creationdate > L.creationdate
  )

--AND   S.agentemployee like 'Doe'
GROUP BY
  L.ordervehiclekey,
  L.orderkey,
  L.vehiclekey,
  S.orderhash,
  S.orderamt,
  S.agentemployee

ordervehiclekey	orderkey	vehiclekey	orderhash	orderamt	agentemployee
111	ABC	1	AAA	5000	Doe
111	ABC	1	XXX	2000	Doe
111	ABC	1	YYY	5000	Smith
111	ABC	1	ZZZ	3000	Smith
222	ABC	1	AAA	5000	Doe
222	ABC	1	YYY	5000	Smith

fiddle