DE Mastery GitHub

Apache Spark · SPK-04

Spark SQL and data sources

From your world. dbt {{ ref() }} and marts vs. temp views and SQL on files

← All exercises

Build a revenue mart with SQL on files and a temp view

Rebuild a small dbt-style mart in Spark SQL. The source is the order_items Parquet file. It contains a few duplicate line rows (the deliberate dirt). You must dedup before you aggregate. If you do not, the duplicates inflate revenue.

Implement company_revenue(spark, order_items_path) in solution.py. It must do these steps:

  1. Read the Parquet file at order_items_path (with the DataFrame reader, or directly in SQL with FROM parquet.`<path>` ).
  2. Remove exact duplicate line rows (dedup on the full business key: company_id, order_item_id, order_id, product_code, qty, line_amount).
  3. Register the cleaned data as a temporary view. Query it with spark.sql(...). Use at least one temp view. This is the ref() analogue.
  4. Return a DataFrame with one row for each company:
column value
company_id the group key
revenue SUM(line_amount) over deduped rows
units SUM(qty) over deduped rows

The test compares against a DuckDB oracle (which dedups in the same way). The test ignores row order and column order. It compares floats with a tolerance.

Starter code

solution.py
"""SPK-04 / 01-sql-on-files — starter. See README.md."""


def company_revenue(spark, order_items_path):
    # TODO: read parquet, dedup, register a temp view, aggregate with spark.sql.
    raise NotImplementedError

Hints

Hint 1

Read the parquet file into a DataFrame, dedup the duplicate order-item rows on the full business key, then createOrReplaceTempView('items').

Hint 2

Query the view with spark.sql(...): GROUP BY company_id, SUM(line_amount) AS revenue, SUM(qty) AS units.

Hint 3

You can also read the file directly in SQL: SELECT ... FROM parquet.`<path>`. Either way, dedup BEFORE aggregating or the duplicate line inflates revenue. Return company_id, revenue, units.

Run it locally
uv run de start SPK-04/01-sql-on-files
# edit workspace/SPK-04/01-sql-on-files/
uv run de test