Catalyst and the physical plan

6 h
requires SPK-04

From your world. Redshift EXPLAIN and the query planner

Make the scan read less — pushdown and pruning

The orders table is a 7-column Parquet file. A downstream report needs only the order_id and currency of USD orders. Your job is to write the transformation so that Catalyst pushes the filter into the scan and prunes the unused columns. You learned to read these two gains from the physical plan.

Task

Implement usd_orders(df). It gets the raw orders DataFrame and returns a DataFrame that:

keeps only rows where currency == 'USD', and
has exactly the columns order_id and currency (in any order).

Constraints

The filter must be a native column predicate (a Column expression or SQL string). Never use a Python UDF. A UDF would make the predicate opaque and PushedFilters would be empty.
Write the select/filter as DataFrame operations. Then Catalyst can rewrite them into the scan.

How it is graded

Data (DuckDB oracle): the rows must equal SELECT order_id, currency FROM orders WHERE currency = 'USD'.
Plan — pushdown: the PushedFilters of the physical plan must reference currency.
Plan — pruning: the ReadSchema of the scan must list only order_id and currency, not all 7 columns.
The test does not check row order or column order.

Starter code

solution.py

"""SPK-05 / 01-pushdown-and-prune — starter. See README.md."""


def usd_orders(df):
    # TODO: keep USD rows and return only order_id and currency, as DataFrame ops
    # so Catalyst can push the filter into the scan and prune the columns.
    raise NotImplementedError

Hints

Hint 1

Read the Parquet file, then filter on a native column (currency == 'USD') and select only the two columns you need. Both must happen on the DataFrame so Catalyst can rewrite them into the scan.

Hint 2

Do NOT wrap the filter column in a Python UDF or a non-pushable function — that empties PushedFilters. Use F.col('currency') == 'USD' (or a SQL string filter).

Hint 3

Return the filtered+pruned DataFrame with exactly columns order_id and currency. The test reads the physical plan: PushedFilters must mention currency and ReadSchema must list only those two columns.

Run it locally

uv run de start SPK-05/01-pushdown-and-prune
# edit workspace/SPK-05/01-pushdown-and-prune/
uv run de test