I have a fact table and an scd-type-2 dimension table. I want to produce sales report by region and year.
I have working solution with a query that joins them for reporting purposes. When I run the query in spark/databricks, it gives me a little warning at the bottom:
Use range join optimization: This query has a join condition that can benefit from range join optimization. To improve performance, consider adding a range join hint. and points to this link.
Question: Is there a more optimal way to query when I'm joining using a
between condition (instead of
create table sales (name string ,sale_date date ,sold_amt long); insert into sales values ('John','2022-02-02',100), ('John','2022-03-03',100), ('John','2023-02-02',200), ('John','2023-03-03',200), ('Rick','2022-02-02',300), ('Rick','2023-02-02',400);
dimension table (scd-type-2)
create table employee_scd2 (name string ,region string ,start_date date, ,end_date date, ,is_current boolean); -- unused, kept for completeness insert into employee_scd2 values ('John','NAM', '2010-01-01', '2022-12-31', false), -- John transferred from NAM to APAC starting 2023 ('John','APAC', '2023-01-01', '9999-01-01', true), ('Rick','NAM', '2020-01-01', '9999-12-31', true);
sales report by region and year
select e.region, year(s.sale_date) as sale_year, SUM(s.sold_amt) as sale_amt from sales s left join employee_scd2 e on e.name = s.name and s.sale_date between e.start_date and e.end_date group by e.region, year(s.sale_date);
I've read following and some more: