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

A serving layer is right when each table is checked before readers see it, and each copy outside the lakehouse is checked against its source. The shop's build checks sales and customer_lookup with assert_true before replacing them, defines revenue once in a metric view, and copies customer_lookup into Postgres for the app. check_serving.py compares the copy with the lakehouse row by row, and catches the day the tables were rebuilt and the copy was not.

- Page: https://tecyfy.com/data-engineering/course/serving-data/build-it
- Course: Data engineering course (https://tecyfy.com/data-engineering/course), Module 8: Serving data, Build it: the shop's serving layer, checked
- Parts of this module: Serving tables and the join that counts twice (https://tecyfy.com/data-engineering/course/serving-data); Metric views, revenue defined once (https://tecyfy.com/data-engineering/course/serving-data/metric-views); Dashboards and Genie on a SQL warehouse (https://tecyfy.com/data-engineering/course/serving-data/dashboards-and-genie); Sharing tables with partners (https://tecyfy.com/data-engineering/course/serving-data/sharing); Lakebase, lookups for an app (https://tecyfy.com/data-engineering/course/serving-data/lakebase); Build it: the shop's serving layer, checked (https://tecyfy.com/data-engineering/course/serving-data/build-it)

At the end of this part you can:

- Build the gold tables a business reads, each checked for grain, keys and totals before it replaces the last version
- Check that a copy served outside the lakehouse, such as a Postgres table for an app, still matches its source
- Put the serving steps in the order a job will run them on Databricks, with the sync after the rebuild

The module so far served the shop's data 4 ways: `sales` with its checks in [part 1](/data-engineering/course/serving-data), revenue defined once in [part 2](/data-engineering/course/serving-data/metric-views), dashboards and Genie in [part 3](/data-engineering/course/serving-data/dashboards-and-genie), a share in [part 4](/data-engineering/course/serving-data/sharing) and an app's lookups in [part 5](/data-engineering/course/serving-data/lakebase). 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:

```sql
-- 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`:

```python
"""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:

```bash
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:

```text
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`:

```python
"""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:

```bash
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:

```text
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.

- Tool: [Snowflake vs Databricks cost](https://tecyfy.com/data-engineering/snowflake-vs-databricks). The same queries on a Snowflake warehouse and a Databricks serverless SQL warehouse, priced side by side in every region both sell in.

## Check yourself

1. After a new order, check_serving.py prints 2 PROBLEM lines for C03, 1 "only in the lakehouse" and 1 "only in Postgres". What happened?

   A. Postgres lost a row
   B. customer_lookup has C03 twice
   C. customer_lookup was rebuilt with the new order, but the Postgres copy was not synced since, so C03's row differs between them
   D. The app wrote to the copy

2. lookup.sql checks that customer_lookup has no row without a customer_id. Why does that matter more for the app's copy than for a dashboard?

   A. A synced table's key cannot be NULL, and rows with a NULL key are left out of the sync, so the app would silently miss them
   B. Postgres cannot store NULL
   C. Dashboards remove NULL rows themselves
   D. It does not matter

### Answers

1. C. Both sides have 3 rows; only C03's values differ, 1 order and 30.00 in the lakehouse, 0 and 0.00 in Postgres. That is a copy left behind by the rebuild. The fix is to sync, and the lasting fix is to run the sync, then the check, as the steps right after the rebuild, so the app never shows yesterday's numbers by accident.
2. A. Databricks' docs say primary key columns are not nullable in a synced table and rows with NULLs in them are excluded. A dashboard would still count such a row; the app would never find it. Checking before the table is replaced turns a silent gap into a stopped run.

## Questions

### How do I keep a Postgres copy of a lakehouse table in step with the source?

Copy it as the step right after the table is rebuilt, and check after each copy that the 2 sides hold the same rows. On Databricks, a synced table in Lakebase does the copy, and for Snapshot and Triggered modes a Database Table Sync task in the same job runs it after the pipeline. A row-by-row comparison of the source and the copy, or at least their counts and totals, catches a sync that did not run.

### What should a data engineer check before replacing a gold table?

That the new rows have the grain the table promises, such as 1 row per order or per customer; that the keys readers and copies depend on are never NULL; and that the totals add up to the table it was built from. Run the checks on the new rows before the replace, so a failure leaves the old table, and every reader, untouched.

## Sources

- [Serve lakehouse data with synced tables](https://docs.databricks.com/aws/en/oltp/projects/sync-tables)
- [assert_true function](https://docs.databricks.com/aws/en/sql/language-manual/functions/assert_true)
- [Create a metric view](https://docs.databricks.com/aws/en/uc-semantics/metric-views/create)