Predicate Pushdown — Solution & ExplanationProblems / Predicate Pushdown / EditorialApproachThis is a three-table join with predicates on two dimension tables:

Push predicates down — filter stores to US only and products to Electronics only.

Join the filtered dimensions with the fact table (sales).

Aggregate by store and product.

Why This MattersWithout predicate pushdown, the engine joins all 400 sales × 10 stores × 25 products before filtering. With pushdown, it joins 400 sales × ~3 US stores × ~5 Electronics products — dramatically fewer comparisons.Query optimizers attempt this automatically, but:

Complex queries may prevent automatic pushdown

Explicit CTEs document the intent for future maintainers

Some engines (especially distributed ones) need hints to push predicates through outer joins

Complexity

O(n) scan of sales with two small hash-join lookups, versus O(n × m × k) without pushdown.

Hints

Use CTEs to filter stores (country='US') and products (department='Electronics') before joining.

In PySpark, call .filter() on stores and products before .join().

The join with sales should happen AFTER the dimension tables are already filtered.

Solve Predicate Pushdown yourself →