Test Yourself!

These ten questions cover the chapter, and each catches people who already run lakehouses: a delete that deletes nothing, nine rows the metadata counts as eleven, a column that keeps its old name, a compaction that grows the bucket, an erasure that leaves the customer behind, a VACUUM that refuses, two engines that divide differently, a uniqueness test that ignores duplicates, an outlier that can never reach three sigma, and a forbidden file whose name the reader can still see. All ran against BookNest's lakehouse on this host: MinIO 30,943 with mc, Spark 4.1.3 129 with Iceberg 1.12.0 129 and the REST catalog, deltalake 1.6.6, DuckDB 1.5.5 61,228 , Trino 483 403,499 and dbt-duckdb 1.11.0. Write down what each prints and why, then check Appendix I for the real output, the reason, the fix and the section.

check.sh in demos/ch08/quiz/ creates a scratch namespace quiz through the REST catalog, runs the five files below in order (q7.sql once in Trino and once in DuckDB, on The Capstone's platform tables), then runs the fixes Appendix I quotes and removes everything it created.

quiz_s3.sh: questions 1 and 10 on object storageShell

# quiz_s3.sh: questions 1 and 10 (MinIO and mc; alias bi is Section 8.14.2's bi-reader).
# 1. Write a key twice in a versioned bucket, then delete it. What is left? (Section 8.2.2)
mc mb lake/quiz >/dev/null && mc version enable lake/quiz >/dev/null
for v in v1 v2; do echo $v | mc pipe lake/quiz/a.txt >/dev/null; done
mc rm lake/quiz/a.txt >/dev/null
echo "1: ls=[$(mc ls lake/quiz)] versions=$(mc ls --versions lake/quiz | wc -l)"
# 10. bi-reader may not GET customers' files. May it list them? (Section 8.14.2)
d=bi/warehouse/booknest/customers/data/
echo "10: ls=$(mc ls -r $d | wc -l) files, cat=$(mc cat "$d$(mc ls -r $d | head -1 |
  awk '{print $NF}')" 2>&1 | grep -o -m1 'Insufficient permissions')"
mc rb --force --dangerous lake/quiz >/dev/null 2>&1 || mc rb --force lake/quiz >/dev/null

quiz_spark.py: questions 2-5 on Iceberg tablesPython

# quiz_spark.py: questions 2-5 (Spark 4.1.3, Iceberg 1.12.0, REST catalog; namespace quiz).
from lake import spark
from lakeduck import connect
q = lambda sql: [tuple(row) for row in spark.sql(sql).collect()]
q("""CREATE TABLE quiz.customers (id INT, name STRING) USING iceberg
     TBLPROPERTIES ('write.delete.mode' = 'merge-on-read')""")
q("INSERT INTO quiz.customers SELECT id, concat('customer-', id) FROM range(1, 11)")
first = q("SELECT snapshot_id FROM quiz.customers.snapshots")[0][0]
# 2. Erase customer 7 of 10. What do count(*) and the files table say? (Sections 8.4.3, 8.4.8)
q("DELETE FROM quiz.customers WHERE id = 7")
print(2, q("SELECT count(*) FROM quiz.customers"),
      q("SELECT content, record_count FROM quiz.customers.files"))
# 3. Rename a column, then read the first snapshot. Which column name? (Section 8.6.5)
q("ALTER TABLE quiz.customers RENAME COLUMN name TO full_name")
print(3, spark.sql(f"SELECT * FROM quiz.customers VERSION AS OF {first}").columns)
# 4. Ten one-row inserts, then compaction. Files now, and files kept? (Section 8.6.6)
q("CREATE TABLE quiz.events (id INT) USING iceberg")
for i in range(10):
    q(f"INSERT INTO quiz.events VALUES ({i})")
q("CALL system.rewrite_data_files('quiz.events')")
print(4, q("SELECT count(*) FROM quiz.events.files"),
      q("SELECT count(DISTINCT file_path) FROM quiz.events.all_data_files"))
# 5. Expire every old snapshot. Is customer 7 still in the bucket? (Section 8.13.7)
q("CALL system.expire_snapshots(table => 'quiz.customers', older_than => now(), "
  "retain_last => 1)")
files = [r[0] for r in q("SELECT file_path FROM quiz.customers.files WHERE content = 0")]
print(5, connect().sql(f"SELECT * FROM read_parquet({files}) WHERE id = 7").fetchall())

quiz_misc.py: questions 6 and 9 in delta-rs and DuckDBPython

# quiz_misc.py: questions 6 and 9 (deltalake 1.6.6, DuckDB 1.5.5; run in an empty directory).
import duckdb, pyarrow as pa
from deltalake import DeltaTable, write_deltalake
# 6. Overwrite a Delta table, then VACUUM with zero retention. What happens? (Section 8.7.5)
for v in (1, 2):
    write_deltalake("orders_delta", pa.table({"version": [v]}), mode="overwrite")
try:
    print(6, DeltaTable("orders_delta").vacuum(retention_hours=0, dry_run=False))
except Exception as e:
    print(6, type(e).__name__, e)
# 9. Nine days of ~1,000 orders, then 1,000,000. Its z-score, plain and robust? (8.12.1)
print(9, duckdb.sql("""
  WITH d AS (SELECT CASE WHEN i = 9 THEN 1000000 ELSE 1000 + i END AS n FROM range(10) t(i)),
  s AS (SELECT avg(n) mu, stddev_samp(n) sd, median(n) med, mad(n) mad FROM d)
  SELECT round((1000000 - mu) / sd, 3), round((1000000 - med) / (1.4826 * mad))
  FROM s""").fetchall())

q7.sql: question 7 in two enginesSQL

-- q7.sql: question 7, run in Trino 483 and in DuckDB 1.5.5 on the same Iceberg table.
-- 7. Average quantity per order line, two ways. What does each engine print? (8.9.7)
SELECT sum(qty) / count(*) AS ratio, avg(qty) AS average FROM platform.order_items;

quiz_dbt.sh: question 8 in dbtShell

# quiz_dbt.sh: question 8 (dbt-duckdb 1.11.0 in a scratch project; profile in profiles.yml).
# 8. A coupon column holds SPRING10 and two NULLs. Which tests pass? (Section 8.11.6)
mkdir -p models && cat > models/coupons.sql <<'SQL'
select * from (values ('SPRING10'), (null), (null)) as t(coupon)
SQL
cat > models/schema.yml <<'YML'
models:
  - name: coupons
    columns:
      - {name: coupon, data_tests: [unique, not_null]}
YML
dbt build --no-use-colors --profiles-dir . | grep -oE "(PASS|FAIL [0-9]+) [a-z_]{3,}" |
  sed "s/^/8: /"

MENU