I am trying to implement date interval check while joining 2 tables in DBT using Bigquery.
Table 1 (Customer) has 3 columns. Customer ID , Product ID & Order Date. Table 2 (Products) has 4 columns. Product ID , Start Date , End Date & Price. Same product can have different price based on Start Date & end date.
I am trying to join these 2 table based on Product ID and fetch the price where Order Date is falling between Start Date & End Date.
Table Data & Expected Output
Customer |Customer ID|Product ID|Order Date| |-----------|----------|----------| |1|A|2023-07-05| |2|A|2022-04-04|
Products |Product ID|Start Date|End Date|Price| |----------|----------|--------|-----| |A|2020-01-01|2021-12-31|100| |A|2022-01-01|2022-05-05|120| |A|2022-05-06|9999-12-31|130|
Output |Customer ID|Product ID|Order Date|Price| |-----------|----------|----------|-----| |1|A|2023-07-05|130| |2|A|2022-04-04|120|
To implement this , I have joined the 2 tables in DBT based on Product ID and then applied filter condition to select the record where Order Date is falling between Start Date & End Date. It is working fine and providing desired output.
Another approach is to divide the product table into active & in-active records and first join with active and then further join with inactive records with date interval check to find any match.
Is there any other better approach ? Some out of the box solution which DBT provides ? Customer table will have 1M records & Product will have around .8M records.