Kashyap Bhatt03/07/2023, 5:10 PM
condition (instead of
condition)? Or use something more optimal than
Matthew Powers03/07/2023, 5:16 PM
Kashyap Bhatt03/07/2023, 6:10 PM
Quentin Ambard03/07/2023, 9:47 PM
Dominique Brezinski03/07/2023, 10:07 PM
Kashyap Bhatt03/07/2023, 10:11 PM
if you’re on databricks did you try DLT ?@Quentin Ambard, DLT would provide ways to populate facts and dimensions. I am talking about using/querying these tables. I already have many working ways to create/maintain my SCD tables. Besides the point, but DLT is too new to be used in our production system.
Dominique Brezinski03/08/2023, 7:31 PM
select /*+ RANGE_JOIN(e, 365) */ e.name, 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.name, e.region, year(s.sale_date);
Kashyap Bhatt03/08/2023, 8:30 PM
is the hint.
/*+ RANGE_JOIN(e, 365) */
You could also probably run the aggregation over a window function on the range when you want to do something like the sum.Would appreciate an example of doing this using window function if you have the time. Thank you @Dominique Brezinski.