Build a serving layer: checked gold tables, a metric view and an app copy in Postgres

The module so far served the shop's data 4 ways: sales with its checks in part 1, revenue defined once in part 2, dashboards and Genie in part 3, a share in part 4 and an app's lookups in part 5. This part builds the serving layer of the shop's final project in 1 run and adds a check for the copy the app reads. It needs part 1's setup_serving.py, run_sql.py and sales.sql, part 5's copy_to_postgres.py and app.py, and part 5's Postgres container with its PG settings.

Build it: the shop's serving layer, checked

customer_lookup gets the same treatment sales got in part 1: its rows are built as a view, checked, and only then replace the table. Replace part 5's lookup.sql with this:

-- 1 row per customer, for the app, from the customers' current versions and their sold orders.
CREATE OR REPLACE TEMPORARY VIEW lookup_next AS
SELECT c.customer_id,
       c.country,
       count(s.order_id) AS orders,
       coalesce(sum(s.amount), 0) AS spend,
       max(s.day) AS last_order_day
FROM customer_versions c
LEFT JOIN sales s ON s.customer_id = c.customer_id
WHERE c.valid_to = TIMESTAMP'9999-12-31 00:00:00'
GROUP BY c.customer_id, c.country;

-- A synced table needs a key that is never NULL and never repeats, and the spend must add up to sales.
SELECT assert_true(count(*) = count(DISTINCT customer_id), 'customer_lookup has a customer more than once') AS one_row_per_customer,
       assert_true(count(customer_id) = count(*), 'customer_lookup has a row with no customer_id') AS every_row_has_a_key,
       assert_true(sum(spend) = (SELECT coalesce(sum(amount), 0) FROM sales), 'customer_lookup does not add up to sales') AS spend_adds_up
FROM lookup_next;

CREATE OR REPLACE TABLE customer_lookup USING delta
COMMENT 'For the app: 1 row per customer, their orders, spend and last order day'
AS SELECT * FROM lookup_next;

SELECT * FROM customer_lookup ORDER BY customer_id;

The key check matters for the copy: a synced table's key cannot be NULL, and Lakebase leaves out rows whose key is NULL, so the app would never find them. Here that stops the run instead.

A check for the copy

Checks inside the lakehouse cannot see the copy in Postgres. Save this as check_serving.py:

"""Check that the app's Postgres copy holds exactly what customer_lookup holds in the lakehouse."""
import sys
import psycopg
from delta_session import delta_spark

spark = delta_spark("check")
lakehouse = {tuple(row) for row in spark.table("customer_lookup").collect()}
with psycopg.connect() as conn:
    copy = set(conn.execute("SELECT customer_id, country, orders, spend, last_order_day FROM shop.customer_lookup_synced").fetchall())

problems = [f"only in the lakehouse: {row}" for row in sorted(lakehouse - copy)]
problems += [f"only in Postgres: {row}" for row in sorted(copy - lakehouse)]
print(f"lakehouse {len(lakehouse)} rows, Postgres {len(copy)} rows")
for problem in problems:
    print("PROBLEM:", problem)
sys.exit(1 if problems else 0)

It compares every row, which a table of 1 row per customer allows. For millions of customers, compare the row counts and the sums of orders and spend instead, which needs 1 small query on each side.

Run it

Build both tables, copy, check, and look a customer up:

python setup_serving.py
python run_sql.py sales.sql lookup.sql
python copy_to_postgres.py
python check_serving.py
python app.py C03

The checks of both tables print NULL, customer_lookup has C01, C02 and C03 as in part 5, and the copy matches:

lakehouse 3 rows, Postgres 3 rows

app.py C03 prints ('C03', 'DE', 0, Decimal('0.00'), None): C03 has bought nothing yet. Now C03 places a first order. Save this as new_order.py:

"""A new order arrives in silver: C03's first, placed on 2026-10-04."""
from delta_session import delta_spark

spark = delta_spark("new_order")
spark.sql("""
INSERT INTO orders VALUES
  (1009, 'C03', 'placed', 30.00, TIMESTAMP'2026-10-04 13:00:00', TIMESTAMP'2026-10-04 13:00:00')
""")
print("orders", spark.table("orders").count(), "rows")

Run it, rebuild both tables, and check, without copying:

python new_order.py
python run_sql.py sales.sql lookup.sql
python check_serving.py

sales now has 4 rows, the new one 1009 2026-10-04 C03 DE 30.00, and every check passes, but the copy is behind, and check_serving.py exits with 1:

lakehouse 3 rows, Postgres 3 rows
PROBLEM: only in the lakehouse: ('C03', 'DE', 1, Decimal('30.00'), datetime.date(2026, 10, 4))
PROBLEM: only in Postgres: ('C03', 'DE', 0, Decimal('0.00'), None)

The counts agree, which is why the check compares values: a count alone would have passed. Run python copy_to_postgres.py and the check again, and it prints lakehouse 3 rows, Postgres 3 rows and exits with 0. python app.py C03 now prints ('C03', 'DE', 1, Decimal('30.00'), datetime.date(2026, 10, 4)). Every output on this page was the same through Spark Connect, apart from the timings.

On Databricks

The same layer on Databricks is 4 pieces, in the order a job runs them:

1. sales.sql and lookup.sql in a SQL task, after module 7's pipeline, with <catalog>.shop. before each table. A failed check stops the job before either table is replaced.
2. Part 2's metric view, created once with CREATE OR REPLACE VIEW <catalog>.shop.sales_metrics WITH METRICS LANGUAGE YAML AS $$ ... $$. It reads sales when queried, so it needs no step of its own; dashboards, Genie and the share read the same table.
3. A synced table of customer_lookup in Lakebase, Triggered, with a Database Table Sync pipeline task after the SQL task in place of copy_to_postgres.py.
4. check_serving.py as the last task, with the PG settings and an OAuth token from part 5, so a sync that did not run fails the job where someone sees it.

Module 10 puts these tasks in a job and schedules it.

ToolSnowflake vs Databricks costThe same queries on a Snowflake warehouse and a Databricks serverless SQL warehouse, priced side by side in every region both sell in.