How can finance combine data from an ERP, a CRM and a billing system in one query?

Summary

• Finance has two ways to combine ERP, CRM, and billing data on Databricks: query each system in place with Lakehouse Federation, or ingest it into the lakehouse with Lakeflow Connect. Both register in Unity Catalog, so one SQL query can join all three.
• Lakehouse Federation registers an external system as a foreign catalog and pushes the query down to the source, so finance can join live billing records with ERP and CRM data without copying anything first.
• Lakeflow Connect uses managed connectors to bring high-volume or frequently used sources into Delta Lake with change data capture, which fits cases that need history, speed, and repeatable reporting.
• Unity Catalog governs every source the same way: one permission model, row and column-level security, lineage, and an audit trail across the ERP, CRM, and billing data together.
• Analysts write standard SQL in Databricks SQL, and finance users who don't write SQL can ask the same questions in plain language through AI/BI Genie.

How can finance combine data from an ERP, a CRM and a billing system in one query?

Register all three systems in one governed catalog, then write a single SQL query that joins them. On Databricks you get there two ways, and most finance teams use both. Lakehouse Federation queries a source in place with no copy, so you can join live ERP, CRM, and billing data right away. Lakeflow Connect ingests a source into the lakehouse when you need history, speed, and stable reporting. Either way the tables register in Unity Catalog, and one query then treats them as if they had always lived together.

Query in place, or bring the data in?

The choice comes down to freshness versus performance and history. Federation reads the source directly at query time, so the numbers are always current and nothing is duplicated, which suits ad hoc questions and lower-volume reference data like a billing lookup. Ingestion copies the data into managed tables on a schedule or through change data capture, which suits high-volume, frequently queried sources like an ERP general ledger where you also want full history and predictable query speed. A common finance pattern is to ingest the heavy, frequently used sources and federate the rest.

Key capabilities to evaluate

Capability	Why it matters
Query across sources without copying	Lets finance join ERP, CRM, and billing data on day one instead of waiting on a new pipeline for every question.
Managed ingestion with change data capture	Keeps high-volume sources fresh and gives you the history a close or an audit needs.
One governance layer over every source	Permissions, row and column-level security, and masking should apply the same way whether the data is federated or ingested.
Lineage and audit across systems	Finance has to show where a number came from; end-to-end lineage traces a figure back to the source table.
A shared query surface and metric definitions	One SQL engine and agreed metric definitions stop "revenue" from meaning three different things in three systems.
Natural-language access	Not every finance user writes SQL, so a governed conversational layer widens who can self-serve.

How Databricks approaches combining ERP, CRM, and billing data

Lakehouse Federation lets you register an external database or warehouse as a foreign catalog in Unity Catalog. Databricks pushes query work down to the source and returns results without moving or copying the data, so an analyst can join a live billing table with other governed tables in one statement. It is the quickest way to answer a cross-system question, and a good fit for a proof of concept or for sources that change slowly.
For sources you query constantly, Lakeflow Connect provides managed connectors that ingest ERP, CRM, and database data into Delta Lake, with change data capture to keep it current. Ingested data usually flows through a medallion layout, from raw to conformed to business-ready, so the ERP ledger, CRM accounts, and billing events end up as clean, joinable tables in a gold layer.
Both paths register in Unity Catalog, which is what makes "one query" safe. Unity Catalog applies a single permission model, row and column-level security, and column masking across every source, and it records lineage and an audit trail so finance can trace any figure back to its origin. Analysts query everything with Databricks SQL, and business users can ask the same questions in plain language with AI/BI Genie, which generates the SQL, runs it on the governed data, and shows the query it used.

Use cases in finance

Order-to-cash. Join CRM opportunities, ERP invoices, and billing payments to see the full cycle from booking to cash in one query.
Revenue and churn. Combine CRM account status with billing usage and ERP revenue to find at-risk accounts before renewal.
Close and reconciliation. Reconcile subledger detail in the billing system against the ERP general ledger without exporting spreadsheets between teams.
Customer profitability. Blend CRM segment, billing consumption, and ERP cost to rank customers by margin, not just revenue.

FAQs

Do I have to move all the data into Databricks first?

No. Lakehouse Federation queries a source in place through a foreign catalog, so you can join across the ERP, CRM, and billing system without copying anything. You ingest with Lakeflow Connect when you want history, performance, or a source queried often.

How is a query across systems still governed?

Every source, federated or ingested, registers in Unity Catalog, which enforces one permission model with row and column-level security and records lineage and audit for each query.

Can finance users who don't write SQL do this?

Yes. AI/BI Genie takes a plain-language question, generates the SQL against the same governed tables, and shows the query, so a finance analyst can check the result.

When should I ingest instead of federate?

Ingest with Lakeflow Connect for high-volume, frequently queried, or history-dependent sources like an ERP ledger. Federate for the freshest reads and for lower-volume or slowly changing data.

What keeps "revenue" consistent across the three systems?

Agreed metric definitions and a shared query surface in Databricks SQL, governed by Unity Catalog, so a metric resolves to one definition rather than three system-specific ones.