Engines on the lake

6 h
requires LKH-07

From your world. Athena on S3; comparing it to Trino, DuckDB and Spark on the same tables

Same query on Spark, DuckDB (and optionally Trino)

You have one Iceberg table, local.lkh08.sales. It is partitioned by region. Its columns are (id INT, region STRING, cat STRING, amt DOUBLE). You run the same logical query on two engines and prove that they agree. Then you explain the plan.

The query (fixed):

SELECT cat, SUM(amt) AS total
FROM <sales>
WHERE region = 'r1' AND amt > 50
GROUP BY cat

Part 1 — two engines, graded (no Docker)

In solution.py, implement:

run_spark(spark, table) → run the query against the Iceberg table and return the result (a Spark DataFrame with the columns cat, total).
run_duckdb(con, parquet_glob) → run the same query with DuckDB over parquet_glob (read_parquet(parquet_glob, hive_partitioning=true)). Return the columns cat, total.

The test writes the same rows to the Iceberg table and to a Hive-partitioned Parquet copy. Then it asserts that your two results agree (independent engines ⇒ a trustworthy oracle).

Part 2 — explain the plan, graded

Fill plan_facts.yaml with facts that you read from the actual physical plan of Spark (run_spark(...).explain("formatted") or df.explain(True)). The table is partitioned by region. For this query on this table, answer these questions:

data_filter_pushed: is the amt > 50 predicate pushed into the scan (predicate pushdown)?
partition_filter_pushed: is the region = 'r1' predicate used to prune partitions (it appears in the pushed filters of the scan)?

Set each key to true or false to match what the plan actually shows. The test extracts the truth from the real plan (it parses the filters=... of the Iceberg BatchScan). It fails you if your claim disagrees. So look at the plan and do not guess.

Part 3 — Trino, optional (needs the `core` lab)

Suppose the core lab is running. Then a third test runs the same query on Trino and checks that it agrees with Spark. This part skips cleanly when the lab is down. It is a bonus layer. The grade does not require it. You may start the lab with uv run de lab up core. Never run de lab down, because other learners or agents may use the lab.

Starter code

plan_facts.yaml

# TODO: set each to true/false based on what Spark's physical plan actually shows.
data_filter_pushed: null
partition_filter_pushed: null

solution.py

"""LKH-08 / 01-three-engines — starter. See README.md.

Run the SAME query on Spark (Iceberg) and DuckDB (Parquet) and return results with
columns cat, total. Then fill plan_facts.yaml from the real Spark plan.
"""


def run_spark(spark, table):
    # TODO: SELECT cat, SUM(amt) AS total FROM <table>
    #       WHERE region='r1' AND amt > 50 GROUP BY cat
    raise NotImplementedError


def run_duckdb(con, parquet_glob):
    # TODO: the same query over read_parquet(parquet_glob, hive_partitioning=true)
    raise NotImplementedError

Hints

Hint 1

run_spark: spark.sql("SELECT cat, SUM(amt) AS total FROM <table> WHERE region='r1' AND amt > 50 GROUP BY cat"). run_duckdb: the SAME logical query over read_parquet(path, hive_partitioning=true). Both must return columns cat, total.

Hint 2

Inspect the real plan with explain("formatted"): the Iceberg BatchScan line lists filters=...; both region = 'r1' (partition pruning) and amt > 50 (predicate pushdown) appear there when pushdown works.

Hint 3

Fill plan_facts.yaml with what the plan ACTUALLY shows: data_filter_pushed and partition_filter_pushed are both true for this query/table. The test extracts the truth from the plan and compares.

Run it locally

uv run de start LKH-08/01-three-engines
# edit workspace/LKH-08/01-three-engines/
uv run de test