Catalyst and the physical plan

6 h
requires SPK-04

From your world. Redshift EXPLAIN and the query planner

Let Catalyst filter before the join

A report needs the line items of USD orders only. The naive idea is to join everything and filter at the end. Catalyst can do better. If you write the filter as a native predicate, Catalyst pushes it below the join and into the orders scan. Then far fewer rows enter the join.

Task

Implement usd_line_items(orders, order_items). Join the orders with currency == 'USD' to their line items on (company_id, order_id). Return these columns:

order_id, order_item_id, line_amount.

Constraints

Join on both company_id and order_id (the business key). In this multi-company dataset, order_id alone collides across companies.
The currency filter must be a native Column/SQL predicate on orders, not a Python UDF.

How it is graded

Data (DuckDB oracle): equals the SQL join + WHERE currency = 'USD'.
Plan: the FileScan on the orders side must carry PushedFilters that reference currency. This proves that the filter reached the scan and that the join sees fewer rows.
The test does not check row order or column order.

Starter code

solution.py

"""SPK-05 / 02-filter-before-join — starter. See README.md."""


def usd_line_items(orders, order_items):
    # TODO: join orders to order_items on (company_id, order_id), keep USD orders,
    # return order_id, order_item_id, line_amount. Keep the filter native so
    # Catalyst can push it below the join.
    raise NotImplementedError

Hints

Hint 1

Join orders to order_items on (company_id, order_id), and keep only USD orders. Write the currency filter against the orders DataFrame as a native predicate.

Hint 2

Return columns order_id, order_item_id, line_amount for USD orders only. Keep the filter a Column/SQL predicate so Catalyst can push it below the join and into the orders scan.

Hint 3

The test reads the plan: the orders-side scan must carry PushedFilters mentioning currency (the filter reached the scan, so fewer rows enter the join).

Run it locally

uv run de start SPK-05/02-filter-before-join
# edit workspace/SPK-05/02-filter-before-join/
uv run de test