Building a Lakehouse

Generate and run PostgreSQL to Apache Iceberg medallion pipelines with EKOS Lakehouse: bronze reconciled on every run, tested silver, schema changes as branches, gold marts from descriptions.

EKOS Lakehouse is an open-source extension of EKOS (github.com/alexeyban/ekos-lakehouse, MIT) that turns a PostgreSQL database into Apache Iceberg tables in bronze, silver and gold layers. It generates pipeline code into a Git repository you own and never moves data itself:

The binary is ekos-lakehouse, not ekos, so it never shadows a plain EKOS install. Every EKOS command works from it; the Lakehouse commands are under ekos-lakehouse lakehouse …. Design: EKOS RFCs 0190–0199 (pipeline, console tab, installer) and 0200–0207 (Pentaho).

1. Install

On Linux x86_64 with Docker:

curl -fsSL https://raw.githubusercontent.com/alexeyban/ekos-lakehouse/main/install.sh | sh

One command installs the program, starts the stack, creates a sample project, runs its pipeline once and opens the EKOS web console on its Lakehouse tab. For your own database add -s -- --source host:5432/db --source-user reader; the password is read from EKOS_LH_SOURCE_PASSWORD once and kept in owner-only files, never in configuration.

To do each step by hand from source instead, follow the quickstart in the repository. Every block in it is run verbatim by the project's end-to-end test.

2. Configure

A project is a directory with an ekos.toml. Lakehouse settings live in their own table; credentials never go there, only the name of the variable that holds them:

[extensions.lakehouse]
trino-url           = "http://127.0.0.1:58080"
polaris-health-url  = "http://127.0.0.1:58182/q/health"
s3-url              = "http://127.0.0.1:58333"
dagster-url         = "http://127.0.0.1:53000"
source              = "127.0.0.1:55433"
source-database     = "shop"
source-user         = "ekos"
source-password-env = "EKOS_LH_PG_PASSWORD"   # the variable's name, never the password
source-catalog      = "shop"                  # the Trino catalog that reads the source
bronze-schedule     = "0 * * * *"

[extensions.lakehouse.watermarks]
"public.orders" = "updated_at"   # used only when a BEFORE UPDATE trigger proves it

The source's Trino catalog must set postgresql.array-mapping=AS_ARRAY and unsupported-type-handling=CONVERT_TO_VARCHAR. With the defaults, Trino's PostgreSQL connector silently leaves out columns of types it does not map, and EKOS refuses a table whose columns it cannot all see rather than load it incomplete.

3. Plan, generate, run

ekos-lakehouse init
ekos-lakehouse lakehouse doctor      # every service, every column visible, is the source role read-only
ekos-lakehouse lakehouse plan        # each table's load mode, type mappings and refusals
ekos-lakehouse lakehouse generate    # bronze SQL + dbt project + Dagster project + lakehouse.lock.json → pipeline/

generate writes a Git repository under pipeline/ and commits to it. lakehouse.lock.json is the deployed plan. A file you edit by hand is kept and reported as drift; generate works around it.

Masking and history are opt-in, by a person:

[extensions.lakehouse]
history = ["public.customer"]            # SCD2 snapshot in silver

[extensions.lakehouse.pii]
"public.customer.email" = "hmac"         # key from EKOS_LH_PII_KEY
"public.staff.password" = "null"

Every bronze run is reconciled against the source with Migrate's validation tiers: V1 row counts, V2 per-column aggregates, V3 bucketed checksums. Each run also plants a difference that the comparison must catch. If the planted control does not fire, the run is unverified, not healthy: a check that could not have failed proves nothing.

ekos-lakehouse lakehouse reconcile --table public.orders --json

4. Source schema changes

ekos-lakehouse lakehouse sync     # Dagster runs it before every load

Each change in the source is classified. A new table, a new nullable column and a widened type are committed on the pipeline repository's branch. A rename, a drop, a narrowed type, a new required column, a key change or a changed watermark trigger becomes a branch ekos/change/<table>/<id> with proposals/<id>.md: the change, its evidence, and the silver columns and tests it reaches. Merging the branch is the approval. While a rename, drop or narrowing is open, that table's loads pause; other changes let them continue on the deployed schema.

With git-remote = "origin" the branch is pushed and a pull request opened with gh. sync then starts by fast-forwarding from the remote, so a request merged on GitHub resumes its table on the next run. [extensions.lakehouse.policy] changes the action for a class.

5. Gold marts from descriptions

A mart starts as a committed Markdown file:

---
mart: receivables_by_customer_month
grain: [customer, month]
dimensions:
  customer: entity_credit_account.meta_number
  month: month of transactions.transdate
measures:
  invoiced: sum of ar.amount_bc
  average_invoice: the amount_bc total divided by the number of invoices
filters:
  - transactions.approved is true
---
What each customer was invoiced per month.

ekos-lakehouse lakehouse gold draft marts/receivables.md

Every term must resolve to a silver column or it comes back as a question. Prose terms are drafted by a local model (gold-model, Ollama). A draft is accepted only if it states nothing the description does not (anti-invention) and Trino plans it. The mart is proposed on a branch with grain and reconciliation tests.

6. Monitor

ekos-lakehouse lakehouse status

Every state comes with the facts behind it and, for a failure, a probable cause. alert-webhook-env names a variable holding a webhook URL (Slack, Teams and PagerDuty's generic webhook all accept the payload); lakehouse alert posts each state transition once. otlp-endpoint pushes OpenTelemetry metrics (lakehouse.table.state, .freshness, .rows_written, .run_duration). lakehouse check --lint reports lineage, test and documentation coverage, drift and sqlfluff findings.

7. The console tab and agents

In the EKOS web console every Lakehouse project has a Lakehouse tab, declared by the extension itself (RFC 0198). Its panels show health per table and layer, branches waiting for a merge, how each table loads, tables refused and why, and where the stack runs. Its buttons run doctor, sync, check, plan and generate as jobs. Commands that reach outside the EKOS home need an admin and a typed confirmation, and a viewer sees the buttons greyed out.

ekos-lakehouse mcp serve --workspace <project> serves ekos_pipeline_status, ekos_table_health and ekos_pentaho_translation beside EKOS's own tools. All three are read-only: no tool reruns, merges or approves anything.

What it does not do

• Sources other than PostgreSQL, and CDC.
• Merge anything: every lossy, breaking or business-facing change waits for a person.
• Store a credential in ekos.toml, the ledger or the generated code.