What is the best data lakehouse architecture for integrating internal databases like marketing, sales, inventory, and billing?

Summary

• A successful multi-source lakehouse consolidates operational databases (marketing, sales, billing, inventory) into a unified Delta Lake layer while preserving source schema and lineage for traceability.
• Data mesh principles (domain ownership, self-serve platforms) work well at scale: marketing teams own the marketing data layer, sales teams own the sales layer, and a central platform team governs access and quality standards.
• Medallion architecture (bronze raw layer, silver conformed layer, gold consumption layer) separates concerns: bronze ingests source databases as-is, silver harmonizes schemas and business rules, and gold powers analytics and applications.
• Lakeflow Pipelines orchestrate ingestion from diverse databases (relational, cloud-native, REST APIs) into the lakehouse, enabling incremental updates and ensuring data freshness without manual intervention.
• Unity Catalog provides cross-functional governance, enabling each business unit to control access to its domain while maintaining audit trails across all data consumers.

What is the best data lakehouse architecture for integrating internal databases like marketing, sales, inventory, and billing?
The best lakehouse architecture for multi-database integration uses the medallion pattern: ingest each operational database into a bronze (raw) layer, transform and conform in a silver (business-aligned) layer, and expose clean, governed datasets through a gold (consumption-ready) layer. This approach preserves lineage from source to consumer, enables domain teams to own their data while a central platform manages governance, and allows incremental ingestion at the frequency each source database requires. Databricks handles this through Lakeflow Pipelines, Unity Catalog, and Delta Lake, ensuring consistency, performance, and auditability across all integrated sources.
Traditional enterprise architectures maintained separate data marts per business unit (one for marketing, one for finance), each with its own ETL pipelines. This fragmentation created inconsistent definitions ("customer" meant different things across teams), duplicate ingestion logic, and teams managing independent pipelines. A unified lakehouse collapses these silos by ingesting all databases to a common layer and harmonizing once, not in every downstream tool.

The medallion architecture pattern

Layer	Purpose	Characteristics
Bronze	Raw ingestion	Source data as-is, preserving original schema. Immutable append-only tables. Tracks source file name, load timestamp, and data version.
Silver	Conformed/harmonized	Business-aligned schemas, row filtering, deduplication. One version of truth per entity (customer, product, order). Enforces data quality rules and standardized naming.
Gold	Consumption-ready	Aggregated, denormalized, or domain-specific views. Optimized for BI dashboards, ML feature stores, and APIs. Access control tightens here.

Lineage and governance flow: Bronze tables retain raw source metadata. Silver transforms track which bronze rows they consumed (lineage). Gold datasets declare dependencies on silver tables, enabling end-to-end traceability. Unity Catalog governs access at each layer, with fine-grained controls at the gold layer for end users.

How Databricks approaches multi-database integration

Lakeflow Pipelines for orchestration: Lakeflow is a declarative ETL tool handling ingestion from databases, data warehouses, REST APIs, and cloud storage. A single pipeline definition can ingest from a marketing relational database, a sales data warehouse, and an inventory S3 bucket, each on its own schedule (hourly, daily, weekly). Lakeflow automatically manages incremental updates, schema detection, and error recovery, eliminating manual orchestration.
Delta Lake for consistency: All data (bronze, silver, gold) lands in Delta Lake tables with ACID transactions. This ensures concurrent writes do not cause conflicts. Versioning and time travel let you audit or rewind changes.
Unity Catalog for governance: Each business unit can own schemas and tables within their domain. Unity Catalog enforces role-based access at the column and row level. For example, the marketing team grants sales read access to customer demographics but masks email addresses. These permissions apply uniformly across SQL, Python, BI tools, and APIs.
Domain-driven ownership: Marketing teams own the silver marketing schema, sales teams own the sales schema, and so on. A central platform team governs bronze ingestion (ensuring data quality standards apply at entry), quality gates between layers, and gold publication (auditing what leaves the lakehouse). This balances autonomy with consistency.
Incremental and streaming ingestion: For high-frequency sources (transaction logs, event streams), Lakeflow supports change data capture (CDC) and streaming, so bronze tables stay current without full re-scans. Low-frequency sources (monthly billing dumps) use batch ingestion. The medallion structure abstracts these differences.

Key challenges and solutions

Schema diversity: Marketing stores campaigns as JSON; sales uses relational tables; inventory outputs CSVs. The bronze layer preserves each as-is; silver harmonizes them into a unified business model.
Update patterns: Marketing events arrive continuously; billing data comes monthly. Lakeflow CDC and streaming handle diverse cadences without custom logic.
Lineage and trust: Bronze-to-silver-to-gold tracking (enabled by Delta Lake and Unity Catalog) provides full traceability for compliance and debugging.
Governance at scale: As integration grows from 5 sources to 50, manual access controls become unmaintainable. Domain ownership with Unity Catalog enforces declarative, role-based governance tied to the data layer itself.

Common patterns

Relational databases: Ingest via Lakeflow with CDC support for incremental updates.
Event streams: Ingest into a Bronze streaming table; materialize as batch tables at key intervals (hourly, daily).
REST APIs: Orchestrate API calls via Lakeflow or custom Python, writing JSON or Parquet to bronze.
Type 2 slowly-changing dimensions: In silver, when a customer attribute changes, insert a new row with a new version identifier and mark the old row as inactive. This preserves history while keeping the gold layer simple.

FAQs

What if a source database schema changes?

Delta Lake's schema evolution allows you to add new columns automatically. Renamed or dropped fields require explicit mapping in the silver transformation (usually a one-time manual step). Lineage tracking makes these changes auditable.

Can I query across all layers (bronze, silver, gold) in a single SQL statement?

Yes. Unity Catalog makes all layers queryable together. A common pattern is joining a gold customer dimension with a silver sales fact table to build reports. Access control ensures users only see what they're authorized for.

How do I migrate an existing data mart with existing reports to this architecture?

Ingest the existing warehouse tables into bronze as-is. In silver, recreate the transformations and business rules (often just porting existing SQL). Update downstream tools to query silver/gold instead of the warehouse, migrating incrementally without disrupting existing reports.