One semantic layer over scattered Postgres, MySQL and app databases

The situation

Most companies past their first few years have data spread across systems that grew independently. A typical picture: the product runs on Postgres, billing runs on MySQL, support tickets live in a separate Postgres owned by another team, and sales data sits in a CRM. Each is fine on its own. Then someone asks a question that crosses them:

"Which customers with an open support ticket had a payment fail this week?"

A person can answer that with three exports and a spreadsheet. An agent needs one place to ask, one definition of "customer", and an answer it can trust. That is the job of a semantic layer over scattered sources. The first design decision is whether the layer reaches into each database live, or reads copies.

Option A: query them where they live

A federated query engine such as Trino connects to each database through a connector and lets you join across them in one SQL statement:

SELECT c.customer_id, c.email, t.ticket_id, p.failure_reason
FROM app_pg.public.customers      c
JOIN billing_mysql.billing.payments p ON p.customer_ref   = c.billing_ref
JOIN support_pg.public.tickets      t ON t.customer_email = c.email
WHERE p.status = 'failed'
  AND p.attempted_at >= current_date - INTERVAL '7' DAY
  AND t.status = 'open';

What it does well: no pipelines to build or run, answers reflect the source at query time, and you can start in days. It is also the fastest way to find out which questions people actually ask.

Where it hurts:

• Load on production databases. Filters and simple aggregations are pushed down to the source, but joins across sources happen in the query engine, which means pulling rows out of each database. Point connectors at read replicas, never at the primary.
• No consistency across sources. The three reads happen at slightly different moments. For most questions that does not matter; for a reconciliation ("do billing and orders agree?") it does.
• No history. You see each table as it is now. "How many tickets were open on the 1st?" needs history the source may not keep.
• Every source becomes a dependency. If the billing database is down for maintenance, every question touching billing fails.

Option B: copy the changes into one lakehouse

Change data capture (Debezium) reads each database's log and streams every insert, update and delete into Kafka. A sink, either the Iceberg connector for Kafka Connect or a Flink job, writes them into Apache Iceberg tables. The semantic layer then queries the lakehouse.

What it does well: almost no load on the source beyond reading its log, full history if you keep it, fast large joins, snapshot IDs that make answers reproducible, and questions that keep working when a source is down.

What it costs: pipelines to operate, seconds to minutes of delay, schema changes to manage, and deletes to carry through correctly. Iceberg's row-level deletes handle that, but tables that receive frequent updates need regular compaction to stay fast.

Decide table by table

The mistake is deciding per system ("billing is federated"). The right unit is the table, because one database usually holds both a huge, busy transactions table and a handful of small lookup tables.

The hard part: one customer, four IDs

In our example, the product database knows a customer by customer_id, billing by customer_ref, support by email address, and the CRM by an account number. Joining on email works until someone changes their email, or two people share a company address. An agent left to work this out will join on whatever looks plausible, including names.

We build an explicit key-mapping entity: one row per link between a system's ID and a single customer key, with how the link was made and how confident it is.

customer_key | system  | system_id        | matched_on  | confidence
-------------+---------+------------------+-------------+-----------
C-000184     | product | 48211            | source_link | exact
C-000184     | billing | BILL-7731        | gstin       | exact
C-000184     | support | ops@acme.example | email       | exact
C-000184     | crm     | ACC-20931        | domain+name | probable

Exact matches come from shared identifiers (a stored foreign reference, a tax registration number such as GSTIN, a verified email). Probable matches are kept, but labelled, and the semantic layer can exclude them for questions that need certainty. The "customer" entity in the layer always joins through this mapping, so neither people nor agents ever join systems on names or free-text fields.

Consistency, honestly stated

Replication does not make independent systems perfectly consistent either. Each source is captured separately, so at any moment billing might be two seconds behind and support thirty. What replication gives you is the ability to know how far behind each one is. We record the "as of" time for every table a query touched and report the oldest one with the answer. For reconciliation questions, the layer can wait until all the sources involved have caught up past a cut-off time before it answers.

A typical outcome

How we roll it out

1. Start federated, on read replicas, for two to four weeks. It is the fastest way to learn which questions people and agents actually ask.
2. Log every query's cost and source load. The expensive, frequent ones tell you which tables to replicate first.
3. Replicate those tables through change capture into Iceberg, and switch the semantic layer's definitions to read the copies. Questions and metric names stay the same, so nothing downstream changes.
4. Build the key-mapping entity early. It is useful under either option, and it takes longer than anything else.