Nice explanation. I do have a doubts what's the need for broadcast if the filter condition is already being applied to dimensions table and if it is required than tables which are greater than broadcast threshold in those case will this technique not work or just join type will be different.
from what I understand, filter condition, or predicate pushdown as Databricks calls it, works only when querying single table. When you join two tables, you need to 'broadcast' the filter to the other table being joined.
Nicely explained !....but how does the filter is transferred to order table..since where condition is on year and month , query on fact table would still have to figure out what is full_date (of dimension table)column values on for 2021 Feb...could be multiple full_dates for month,year .?
