Design a Federated Query and Lakehouse Access Layer

An analyst has one question: for users who signed up this quarter, how does their clickstream engagement correlate with the revenue they generated, split by whether their account is currently in good standing? The answer lives in three different systems. Clickstream events sit in the lakehouse as Iceberg tables on S3. Curated revenue lives in the cloud warehouse. And "currently in good standing" is a live flag in an operational Postgres replica. Today, answering that means filing three tickets, waiting for three teams to build three copy jobs, and joining the results a week later, by which point "currently" is a week stale.

Rafael built the near-real-time OLAP serving layer two chapters back: one carefully denormalized, governed store that answers a fixed set of dashboard questions in milliseconds. This is the opposite problem. Analysts don't want one curated store; they want to roam across every store the company has, joining data that was never centralized, asking questions nobody pre-planned. And they don't want to wait a week for a pipeline each time.

The tempting pitch is "federate everything": stand up a query engine that connects to all three sources and let a single SQL statement join across them, no copying, no ETL. It is genuinely magical when it works. It is also a trap, because the moment a query can't push its work down into the sources, that same engine quietly turns into a slow, source-hammering ETL job that runs at query time, with none of the governance. This chapter is about building the access layer and knowing exactly where that line is.

Step 1 - Understand the Problem and Establish Design Scope

"Query across everything" can mean a serving layer, an ETL replacement, or an analyst sandbox. The clarifying questions pin down which, and against what.

Candidate: Who's querying, and is this interactive serving or exploration?

Interviewer: Analysts and data scientists, running ad-hoc exploratory SQL. Not a high-QPS dashboard, that's the serving layer you already built. Here the queries are unpredictable and varied, and interactive latency, seconds to a couple of minutes, is the goal, not milliseconds.

Candidate: What are the sources, and can I copy data between them?

Interviewer: Three to start: the lakehouse (Iceberg on S3, the big fact tables), the cloud warehouse (curated marts), and a Postgres read replica (live operational dimensions like account status). We'd rather not copy everything, the whole point is to stop building a pipeline for every question, but materializing hot paths is on the table.

Candidate: How fresh does the data need to be?

Interviewer: It varies by source, and that's a feature. Querying the Postgres replica should reflect near-current account status. The lakehouse is as fresh as its last load. Federation should give each source's current truth without us pre-staging it.

Candidate: How much can an analyst query hurt the sources?

Interviewer: This is critical. A careless analyst query must never degrade the production Postgres or the warehouse. Source protection is a hard requirement, not a nice-to-have.

Candidate: Where does access control and governance live?

Interviewer: Each source has its own permissions today. Ideally there's a single place to reason about who can see what, but I know federation spreads that across systems, so name the cost.

Candidate: Scale?

Interviewer: Hundreds of analysts, a few thousand queries a day, wildly varying: some peek at metadata, some try to scan a ten-billion-row fact table.

With that, the shape is clear: an ad-hoc, cross-source, exploration engine that must minimize data movement, protect the sources, and let hot paths be pre-materialized.

Functional requirements

• Run a single SQL query that joins across the lakehouse, the warehouse, and the Postgres replica, without pre-copying the data.
• Push filters, projections, and aggregations down into each source so only the needed data crosses the wire.
• Protect every source system from a runaway analyst query.
• Let a hot cross-source join be pre-materialized into the lakehouse for speed.
• Provide a single access point for querying, with a coherent story for governance across sources.