Data Warehousing Architecture, Ingestion Pipelines, and Analytical Stores
Microsoft Fabric, Azure Databricks, ETL and ELT, semantic models, pipelines, warehouses, lakes, and lakehouses
Suggested study time: 95 minutes • Beginner level • Aligned with the DP-900 study guide and official Microsoft Learn documentation
By João Ricardo Dutra••Complete material
1. Two platforms for large-scale analytics
Large-scale analytics combines established business-intelligence warehousing with techniques for high-volume, varied, and continuously arriving data. Transactional records are commonly copied into an analytical schema, while files and streams can land in a data lake and be processed in parallel by engines such as Apache Spark. Combining lake flexibility with SQL warehouse behavior produces the architecture known as a data lakehouse.
is a unified software-as-a-service analytics platform. Data engineering, data warehousing, Real-Time Intelligence, data science, and share browser workspaces and the tenant-wide OneLake storage layer; Microsoft operates the underlying infrastructure. is a managed Azure analytics platform built on Apache Spark. Its code-first notebooks and SQL workloads use Delta Lake, which adds transactions, schema enforcement, and version history to Parquet files.
Figure 1 — Two platforms for large-scale analytics
Topic summary
Fabric offers an integrated SaaS experience over OneLake; offers Spark-centered engineering and analytics over open lakehouse formats.
2. The end-to-end analytical architecture
A modern analytical solution normally starts with sources such as transactional databases, files, applications, devices, and event streams. Ingestion moves the data and processing cleans, filters, joins, or restructures it. ETL transforms before loading the analytical store; ELT loads first and uses the destination compute to transform afterward. Both batch and real-time paths may participate, often with distributed clusters working in parallel.
The processed data is retained in a relational data warehouse, a file-based data lake, or a lakehouse that combines both. Analysts and scientists can query the store directly, but a curated model usually makes reports easier to understand. Reports, dashboards, interactive exploration, and AI-assisted interfaces are the final consumption layer.
ETL and ELT
Pattern
Order
Typical idea
ETL
Extract → Transform → Load
Shape data before the analytical store
ELT
Extract → Load → Transform
Use destination compute after loading
Figure 2 — The end-to-end analytical architecture
Topic summary
Think of the workload as a chain: sources, ingestion and processing, analytical store, analytical model, and business consumption.
3. Semantic models and visual consumption
Earlier BI systems often described multidimensional cubes with values pre-aggregated by dimensions, for example sales by product and region. The current preferred approach in and is a tabular semantic model. It defines tables, relationships, hierarchies, and DAX measures, calculating aggregations when a query runs rather than storing every result in advance.
Direct Lake mode allows a semantic model to read Delta tables from OneLake without importing or pre-aggregating the data. Analysts can then publish trends, comparisons, and key performance indicators as printed reports, charts in documents or presentations, browser dashboards, or interactive self-service experiences.
Topic summary
Semantic models translate stored data into governed business concepts and measures that reports and self-service users can consume.
4. AI-assisted analytics
Natural-language features extend analytics beyond people who write queries. Q&A can turn a plain-language question into a visual response. Copilot in can help generate DAX measures and SQL, summarize reports, explain trends, and assist with other analytical work.
provides Genie for conversational questions over governed Delta Lake data. Genie interprets the request, generates and executes SQL, and returns results. These assistants accelerate exploration, but data quality, permissions, semantic definitions, and human validation remain essential.
Topic summary
AI assistants lower the query barrier; they do not replace governance, reliable data models, or review of the generated result.
5. How maps the architecture
OneLake is the shared, tenant-wide storage foundation for Fabric workloads. Delta Lake is the standard open format for lakehouse tables, so workloads can work over common data instead of maintaining separate storage silos.
A Fabric Lakehouse stores mixed data in Delta Lake and exposes a SQL analytics endpoint. Fabric Warehouse provides a fully managed, SQL Server-compatible relational experience with stronger schema enforcement. Fabric Data Factory builds and schedules movement and low-code transformation. supplies semantic models, reports, and dashboards, including Direct Lake access.
Figure 3 — How maps the architecture
Topic summary
Fabric brings storage, engineering, warehousing, orchestration, real-time analysis, and BI into one SaaS workspace.
6. How maps the architecture
uses Delta Lake as its native open storage format for SQL analytics, data engineering, and machine-learning workloads. Databricks SQL supplies serverless SQL warehouse compute, query history, dashboards, and alerts over Delta tables.
Collaborative Databricks Notebooks support Python, SQL, Scala, and R. Unity Catalog provides discovery, lineage, and fine-grained governance for data and AI assets. Genie adds a conversational layer that can turn natural-language questions into executed SQL.
Topic summary
Databricks centers the architecture on Spark, Delta Lake, notebooks, SQL compute, and unified governance.
7. Pipeline ingestion with Fabric Data Factory
Fabric Data Factory is the usual starting point for pipeline-based ingestion into OneLake. Pipelines orchestrate multi-step workflows and can execute activities sequentially or in parallel. Dataflows Gen2 supplies reusable, visual transformations with Power Query for teams that prefer low-code authoring.
A pipeline connects inputs and outputs through linked services and can copy data, invoke stored procedures, run notebooks, or apply custom logic. Results can be written to a Fabric Lakehouse, Fabric Warehouse, or another supported destination. Some built-in activities do not require a linked service.
Figure 4 — Pipeline ingestion with Fabric Data Factory
Topic summary
Pipelines coordinate movement and execution; Dataflows Gen2 expresses reusable low-code transformations.
8. Shortcuts, mirroring, and real-time events
A OneLake shortcut is a live reference to Gen2, Amazon S3, Google Cloud , or another OneLake location. External files appear inside the Lakehouse without being duplicated, which is valuable when residency, compliance, or transfer cost makes copying undesirable.
Mirroring continuously replicates supported operational databases such as , Snowflake, and into OneLake in near real time. Fabric handles change tracking and writes Delta tables without requiring a custom pipeline.
Fabric Eventstream ingests continuously arriving events from Azure , Apache Kafka, , and custom endpoints. It can route, filter, and transform events before sending them to a Lakehouse, a KQL database optimized for time-series analysis, or another Real-Time Intelligence destination.
Figure 5 — Shortcuts, mirroring, and real-time events
Topic summary
Shortcuts federate without copying, mirroring replicates database changes, and Eventstream handles continuously arriving events.
9. Code-first ingestion and standalone
Fabric Notebooks are an Apache Spark-based escape hatch when a connector is unavailable or transformations need custom code. PySpark, Python, Scala, R, or SQL can read REST APIs, databases, files, and reachable endpoints, then write to Delta tables or a Warehouse. A notebook can run interactively or as a scheduled pipeline activity.
is the standalone Azure integration service for pipelines outside Fabric. It is useful when a destination is or another external platform, and for hybrid connectivity to on-premises sources. Its pipeline and linked-service model is similar to Fabric Data Factory, so core orchestration skills transfer.
Topic summary
Use Fabric Notebooks for custom Spark code and standalone for broader Azure or hybrid integration outside Fabric.
10. Ingestion options in
Lakeflow Spark Declarative Pipelines lets engineers describe the output tables they want while the service manages dependency order, incremental processing, and execution. It is intended for reliable, production-grade pipelines that run continuously or update incrementally.
Databricks Notebooks support ad hoc and exploratory ingestion from REST APIs, JDBC sources, cloud object stores, and streams. They can write Delta tables, run as scheduled jobs, or become transformation steps within a Lakeflow pipeline. also offers specialized ingestion mechanisms beyond this foundational scope.
Figure 6 — Ingestion options in
Topic summary
Lakeflow Declarative Pipelines manages dependable incremental flows; notebooks provide flexible code-first ingestion and transformation.
11. Data warehouses, facts, and dimensions
A data warehouse is a relational store designed for analytical rather than transactional queries. Numeric measurements are placed in central fact tables and related to dimension tables that describe perspectives such as customer, product, store, and time. This structure makes aggregations such as monthly revenue by product and store straightforward.
A star schema links dimensions directly to the fact table. A snowflake schema normalizes parts of a dimension into additional related tables, for example separating a product category hierarchy. Warehouses fit structured data, strong schema control, SQL-based teams, and many concurrent BI queries.
Star and snowflake schemas
Design
Dimension structure
Trade-off
Star
Dimensions connect directly to facts
Simple queries and fewer joins
Snowflake
Dimensions extend into related hierarchy tables
More normalization and more joins
Figure 7 — Data warehouses, facts, and dimensions
Topic summary
Facts hold measurable events, dimensions provide business context, and star or snowflake designs optimize analytical SQL.
12. Data lakes and schema on read
A data lake stores files on a distributed file system and can retain structured, semi-structured, and unstructured content. Cloud-native engines such as Apache Spark process the files in parallel for analysis and reporting.
Many lakes use schema on read: files can land without relational constraints, and a tabular shape is applied when data is queried. This flexibility accelerates collection and experimentation, but organizations still need cataloging, access control, quality practices, and deliberate organization to keep the lake usable.
Topic summary
A lake favors flexible file storage and distributed processing; schema can be applied when data is read rather than when it is written.
13. The lakehouse and Delta Lake
A lakehouse keeps raw and curated data as files while exposing reliable tables and SQL access. Delta Lake adds a transaction log, schema enforcement, consistency, versioning, and support for both batch and streaming updates on top of Parquet.
and both use Delta Lake. In Fabric, Lakehouse tables live in OneLake and receive an automatically created SQL analytics endpoint. Fabric Warehouse also stores its data in OneLake, providing a common storage foundation across the two experiences.
Figure 8 — The lakehouse and Delta Lake
Topic summary
Delta Lake lets a lakehouse combine open file storage with dependable tables, transactional consistency, and SQL analytics.
14. Choosing an analytical store and combining services
Fabric Lakehouse fits mixed or semi-structured data, notebooks, Spark, and machine-learning workflows. Fabric Warehouse fits structured relational data, SQL development, strict schemas, and concurrent BI. Fabric also supplies Data Factory, Real-Time Intelligence, and Mirroring around those stores.
fits code-first Spark engineering, data science, SQL over Delta Lake, multicloud portability, or teams with established Databricks expertise. Its SQL Warehouse is optimized for BI concurrency. Some solutions combine platforms: ELT can land files, a Databricks notebook can process them at scale, and curated results can be loaded into Fabric Warehouse tables.
An analytical service may expose a schema and query interface even when the underlying bytes remain in a data lake. Architecture decisions should therefore consider user skills, source formats, governance, latency, concurrency, transformation style, and ecosystem—not only the storage label.
Analytical store fit
Experience
Strong fit
Fabric Lakehouse
Mixed data, Spark, notebooks, ML
Fabric Warehouse
Structured relational data and concurrent SQL BI
Code-first Spark, Delta Lake, data science, portability
Figure 9 — Choosing an analytical store and combining services
Topic summary
Select the experience that best matches data shape, workload, skills, and governance; Fabric and Databricks can also participate in one solution.
15. Hands-on exercise and assessment reasoning
The guided exercise provisions a environment, creates a Lakehouse, ingests sample data, and analyzes it. It is an orientation to the principal building blocks rather than an advanced analytics tutorial. Allow about 30 minutes and use a Fabric account or trial, then remove unneeded items or capacity after the lab.
For the knowledge check, a solution that combines data-lake and relational-warehouse behavior is a data lakehouse. is the SaaS analytics platform that can create ingestion and processing pipelines. Apache Spark is the open-source distributed engine included for large-scale processing.
Topic summary
For DP-900, connect each scenario to the architecture term, ingestion mechanism, data-store type, and platform capability that uniquely satisfies it.
