Consider 5 tables with more than 1 billion records each.
A query needs to join them. And I know that less than 10% of their records will be require. Say they all have a Date dimension, and only data from current month is needed.
What should be faster:
1) Use a simple SELECT: join all tables, then filter (WHERE) each table's dimension for current month.
2) create 5 temp tables, filtering each source table for current month records, here we can also take the opportunity to select only required columns, then join these temp tables.
3) Maintain secondary tables, having only current month/year worth of data. These tables are maintained by same ETL that feeds main ones.