Integrating Oracle Database@Azure with Microsoft Fabric
Article Overview
The transactional heart of most enterprises beats in Oracle, but the analytics ambition lives in Microsoft Fabric. This article is about wiring the two together into a single, modern analytics platform — without the fragile ETL that used to sit between them. It walks the whole path: getting Oracle operational data into OneLake with zero-ETL mirroring, organising it with the medallion (Bronze/Silver/Gold) architecture, understanding why Delta Lake and the OneCopy principle change the economics, building Direct Lake semantic models that let Power BI read the lakehouse without importing, and governing all of it through Purview and Entra ID. The focus is the analytics platform — how the data is shaped, stored, modelled, and served — with Oracle kept firmly as the trusted source of record underneath.
1.Why Oracle and Fabric Belong Together
For years, the analytics story for Oracle-heavy enterprises had an awkward middle. The operational data lived in Oracle, the reporting and data-science ambition lived in a Microsoft analytics stack, and between them sat a tangle of extract jobs, staging databases, and nightly batch windows that everyone maintained and nobody loved. The analytics were always a little stale, the pipeline was always a little fragile, and every new report meant another wire to solder.
Two shifts changed that. Oracle Database@Azure put the Oracle database inside the Azure datacenter, and Microsoft Fabric unified the entire analytics estate — data engineering, warehousing, data science, real-time, and Power BI — onto one lake. Suddenly the operational data and the analytics platform are neighbours, and the middle tangle can be replaced by something far simpler.
The goal is not to move Oracle into Fabric. It is to let Fabric analyse Oracle's data continuously, while Oracle stays exactly where it is — the transactional source of record.
This post assumes you have read the broader AI discussion elsewhere on this blog; here the lens is narrower and more concrete: how to build a working analytics platform on top of Oracle operational data using Fabric's building blocks. That means understanding a handful of Fabric concepts, getting the data in cleanly, shaping it well, and serving it fast.
2.Fabric in Five Concepts
You do not need to know all of Fabric to build this platform — you need five ideas. Everything else follows from them.
OneLake
A single, tenant-wide data lake built on Azure Data Lake Storage Gen2. Every workspace gets a path in it, and every Fabric engine reads the same files. Think of it as one place for all the organisation's data, accessible to every tool.
Lakehouse
A workspace item that stores tables in OneLake and gives you, for free, a SQL analytics endpoint (read-only T-SQL) and a default Power BI semantic model. It is where your Oracle-sourced tables live and get shaped.
Delta Lake
The open table format Fabric standardises on — Parquet files plus a transaction log, giving ACID transactions, schema enforcement, and time travel. One Delta table is readable by every engine without conversion.
Direct Lake
A Power BI storage mode that reads Delta files in OneLake directly from storage — no import, no refresh cycle. It combines the speed of in-memory with the freshness of live data.
Shortcuts
Virtual pointers that make data stored elsewhere appear inside OneLake without copying it. They let Fabric operate on external data as if it were local — the mechanism behind the OneCopy principle.
The one idea that makes the rest click
All structured data in Fabric lives in the same open Delta format in OneLake, so a table a data engineer writes in a notebook, a table a warehouse queries in T-SQL, and a table Power BI reads for a report are the same physical files. No copying between systems, because there are no separate systems. Hold onto that — it is why the platform below is so much simpler than the pipeline it replaces.
3.Landing Oracle Data in OneLake
The platform starts with getting Oracle's operational data into OneLake — continuously, and without a hand-built pipeline. On Oracle Database@Azure the preferred route is mirroring: change data from the Oracle database is replicated into OneLake automatically, so a live copy of the operational tables sits in the lake, ready to shape.
3.1 The two mirroring routes
| Route | What it is | When to use |
|---|---|---|
| Oracle Database mirroring in OneLake | Native, zero-ETL continuous synchronisation of Oracle data into OneLake | The direct default — simplest path for Oracle Database@Azure sources |
| Open Mirroring + GoldenGate 23ai | Fabric's Open Mirroring (open Delta format) fed by Oracle GoldenGate change capture | Low-latency, high-throughput needs, or pulling several sources into one lake |
Both deliver the same essential outcome — a continuously current, analytics-ready reflection of Oracle in OneLake — and neither requires you to build or operate an ETL pipeline. The mirrored tables land as Delta tables, which means every downstream step below treats them exactly like any other Fabric data.
3.2 When mirroring is not the right entry
Occasionally you want something other than a full continuous mirror — a periodic bulk extract of a few tables, or a query-time pull. For those, a Fabric Data Factory pipeline with a Copy activity can read from Oracle into the Bronze layer on a schedule. Mirroring is the modern default; a pipeline copy is the fallback when you need selective or scheduled movement rather than continuous change capture.
Let the data land raw, then shape it in Fabric
Resist the temptation to transform on the way in. Land the Oracle data in OneLake as close to its source shape as possible, then do the cleaning and modelling inside Fabric where it is versioned, repeatable, and visible. Transforming during ingestion buries logic in a pipeline nobody can see; transforming in the lakehouse keeps it in notebooks and SQL you can review, test, and re-run.
4.The Medallion Architecture
Once the Oracle data is in OneLake, you need a way to organise the journey from raw operational rows to trustworthy business metrics. The medallion architecture is the pattern the whole Fabric ecosystem is built around, and it maps naturally to the lakehouse: three layers, each refining the last.
The discipline of the three layers is what keeps a growing analytics platform sane. Raw stays raw and auditable; cleaning happens in one known place; and the business logic lives in a layer built for consumption. When a number looks wrong in a report, you can trace it back through Gold to Silver to Bronze to the exact Oracle rows — a lineage that a pile of ad-hoc extracts could never give you.
5.Bronze — Land It Raw
The Bronze layer is the faithful landing zone. Its only job is to hold the Oracle data exactly as it arrived, with no cleaning and no business logic — because the moment you transform on landing, you lose the ability to reprocess when the logic changes. With mirroring, the mirrored Delta tables effectively are your Bronze layer; with a pipeline copy, you write the raw extract here untouched.
# Bronze: read the mirrored Oracle tables and land them as-is,
# adding only lineage metadata — never transforming the data itself.
from pyspark.sql import functions as F
# The mirrored Oracle table is already a Delta table in OneLake.
src = spark.read.format("delta").load(
"abfss://Sales@onelake.dfs.fabric.microsoft.com/Mirrored.Lakehouse/Tables/orders")
bronze = (src
.withColumn("_ingested_at", F.current_timestamp())
.withColumn("_source", F.lit("oracle:sales.orders")))
# Write to the Bronze lakehouse, preserving everything
(bronze.write.format("delta").mode("append")
.option("mergeSchema", "true")
.saveAsTable("bronze.orders"))Why Bronze never transforms
Bronze exists so that when someone discovers the Silver cleaning logic had a bug six months ago, you can reprocess from raw rather than re-extracting from Oracle. It is your replay buffer and your audit trail. Add ingestion timestamps and source tags if you like, but never clean, dedupe, or reshape here — those belong one layer up, where they can be changed and re-run against the untouched Bronze.
6.Silver — Clean and Conform
Silver is where the raw Oracle data becomes trustworthy. Here you deduplicate, enforce types, handle nulls, standardise codes, and join related tables into clean, conformed entities. The output is one dependable version of each business object — a clean customer, a clean order — that everything downstream can rely on.
# Silver: clean and conform the Bronze orders into a trustworthy entity.
from pyspark.sql import functions as F
from pyspark.sql.window import Window
bronze = spark.read.format("delta").table("bronze.orders")
# Keep only the latest row per order (mirroring may carry change history)
latest = Window.partitionBy("order_id").orderBy(F.col("_ingested_at").desc())
silver = (bronze
.withColumn("_rn", F.row_number().over(latest))
.filter(F.col("_rn") == 1).drop("_rn")
# enforce types and standardise
.withColumn("order_date", F.to_date("order_date"))
.withColumn("amount", F.col("amount").cast("decimal(18,2)"))
.withColumn("status", F.upper(F.trim("status")))
# drop rows that fail a basic quality rule
.filter(F.col("amount") >= 0)
.filter(F.col("customer_id").isNotNull()))
(silver.write.format("delta").mode("overwrite")
.option("overwriteSchema", "true")
.saveAsTable("silver.orders"))Put your data-quality rules here, and make them visible
Silver is the right home for quality checks — row counts, null thresholds, referential checks against other Silver tables. Do them as explicit, logged steps between Bronze and Silver rather than as invisible assumptions. When a downstream report is questioned, being able to point at the quality rules that ran — and the rows they rejected — is the difference between trust and a long meeting.
7.Gold — Business-Ready
Gold is the layer reports and dashboards actually consume. Here the clean Silver entities are modelled for analysis — typically a star schema of fact and dimension tables — and aggregated into the metrics the business asks for. Gold is shaped for the question, not for the source system, which is what makes queries against it fast and the semantic model on top of it clean.
-- Gold: a fact table shaped for analytics, built from Silver.
CREATE TABLE gold.fact_sales AS
SELECT
o.order_id,
o.customer_id,
d.date_key,
o.product_id,
o.amount,
o.quantity,
o.amount / NULLIF(o.quantity, 0) AS unit_price
FROM silver.orders o
JOIN gold.dim_date d ON d.full_date = o.order_date
WHERE o.status = 'COMPLETED';
-- A pre-aggregated metric the dashboard reads directly
CREATE TABLE gold.agg_daily_revenue AS
SELECT date_key,
product_id,
SUM(amount) AS revenue,
COUNT(*) AS order_count
FROM gold.fact_sales
GROUP BY date_key, product_id;Bronze answers "what did Oracle say?", Silver answers "what is true?", and Gold answers "what does the business want to know?" Keep those three questions separate and the platform stays clean as it grows.
🔧 Model Gold as a star schema
Facts (the measurable events — sales, transactions) surrounded by dimensions (the descriptive context — customer, product, date) is the shape Power BI and Direct Lake are optimised for. Resisting the urge to report straight off normalised Silver tables, and instead building proper facts and dimensions in Gold, is what makes the semantic model performant and the reports intuitive.
8.Direct Lake and Power BI
Here is where the platform pays off for the people who never see a notebook. Historically, Power BI had two imperfect choices: import the data (fast queries, but stale until the next refresh) or use DirectQuery (live, but slower and heavier on the source). Direct Lake is the third option that removes the compromise: Power BI reads the Delta files in OneLake directly from storage, with no import step and no refresh cycle.
8.1 Why Direct Lake changes the economics
Because the Gold Delta tables already sit in OneLake, a Direct Lake semantic model points straight at them. Reports get near-import speed because the data is columnar Delta read into memory on demand, and they get near-real-time freshness because there is no copy to refresh — the model reads whatever is currently in the Gold tables. Microsoft's own guidance calls Direct Lake an ideal fit for the Gold layer of a medallion lakehouse, and that is exactly how to use it here.
| Power BI mode | Query speed | Freshness | Load on source |
|---|---|---|---|
| Import | Fast (in-memory) | Stale until refresh | Heavy at refresh time |
| DirectQuery | Slower (queries live) | Live | Continuous on source |
| Direct Lake | Near-import fast | Near real-time | None — reads OneLake Delta |
8.2 The workflow is almost nothing
The elegant part is how little there is to do. Every lakehouse comes with a SQL analytics endpoint and a default semantic model at creation — no separate setup. You build a Direct Lake semantic model over the Gold tables, define relationships and measures, and connect Power BI. Because data preparation already happened upstream in Silver and Gold, the model stays thin and the logic stays reusable rather than being buried in report-level calculations.
-- Measures defined once in the Gold semantic model, reused everywhere
Total Revenue = SUM ( fact_sales[amount] )
Revenue YoY % =
VAR _cur = [Total Revenue]
VAR _prior =
CALCULATE ( [Total Revenue],
SAMEPERIODLASTYEAR ( dim_date[full_date] ) )
RETURN
DIVIDE ( _cur - _prior, _prior )
Avg Order Value =
DIVIDE ( [Total Revenue], DISTINCTCOUNT ( fact_sales[order_id] ) )Keep the semantic model thin
Because Silver and Gold already did the heavy lifting, the semantic model should mostly define relationships and a clean set of measures — not re-implement transformations in DAX. Pushing preparation upstream (into the lakehouse) and keeping the model thin means the logic is reusable across every report and the model stays fast. A semantic model stuffed with calculated columns and cleanup DAX is a sign the work was done in the wrong layer.
9.Shortcuts and the OneCopy Principle
One more Fabric idea makes the platform genuinely lean: you can often avoid copying data at all. A OneLake shortcut is a virtual pointer that makes data stored elsewhere — another OneLake location, ADLS Gen2, S3, or GCS — appear inside your lakehouse without physically moving it. Fabric engines then operate on it as if it were local.
This is the practical expression of the OneCopy principle: data stored in OneLake exists as one physical set of files, readable by every authorised engine at once. Instead of the old world — three copies, three sync jobs, three access-control systems, three places to apply quality rules — there is one copy that the data engineer, the analytics engineer, and the data scientist all read.
Shortcuts for data you are not ready to move
If some of your analytics data already sits in ADLS Gen2 or another lake, a shortcut lets Fabric use it in place alongside the Oracle-sourced Gold tables — no migration required. That makes this platform additive: you can bring the Oracle data into a modern lakehouse without first having to consolidate every other data source you already own.
10.Governance Across the Platform
A platform that spans an Oracle database and the whole Fabric estate could be a governance nightmare — but Fabric's design keeps identity and cataloguing at the platform level rather than per tool, and Oracle Database@Azure already lives inside Azure's governance plane. The two line up.
| Concern | How it is handled |
|---|---|
| Identity | Microsoft Entra ID flows through every workload — the Oracle database, the lakehouse, the warehouse, and the Power BI model are all reached with one directory |
| Lineage and cataloguing | Microsoft Purview tracks lineage and catalogues items across the platform, so a metric in a report can be traced to its Gold, Silver, and Bronze origins |
| Sensitivity labels | Purview sensitivity labels flow across item types — a lakehouse table and a semantic model are labelled and governed the same way |
| Row-level security | Applied on the Gold semantic model so users see only the rows they are entitled to, regardless of which report they open |
| Data at the source | Oracle keeps its own controls — TDE, Database Vault, auditing — as the authoritative layer beneath the analytics |
| Residency | The Oracle source and its OneLake mirror stay in-region, so analytics happen on in-region data |
Govern at the boundaries, once
The lean way to govern this platform is to lean on the platform-level controls rather than bolting compliance onto each tool. Identity through Entra ID, lineage and labels through Purview, row-level security on the Gold model, and the Oracle source keeping its existing controls. Set those once at the right boundaries and every report, query, and notebook inherits them — instead of each needing its own compliance wiring.
11.An End-to-End Blueprint
Assembling the whole thing, here is the sequence a team follows to stand up this platform.
- Mirror Oracle into OneLake. Set up Oracle Database mirroring (or Open Mirroring with GoldenGate) so operational tables land continuously as Delta — your Bronze layer.
- Stand up the lakehouse and workspace. Create the Fabric workspace and lakehouse, decide single-lakehouse-with-schemas versus a lakehouse per layer, and plan capacity.
- Build Bronze. Land the mirrored data raw, adding only lineage metadata. Never transform here.
- Build Silver. Clean, deduplicate, type, conform, and quality-check into trustworthy entities, with the quality rules explicit and logged.
- Build Gold. Model a star schema of facts and dimensions and pre-aggregate the metrics reports need.
- Create a Direct Lake semantic model. Point it at the Gold tables, define relationships and measures, and keep it thin.
- Connect Power BI. Build reports on the semantic model — near-import speed, near-real-time freshness, no refresh jobs.
- Wire governance. Entra ID identity, Purview lineage and labels, row-level security on the Gold model, from the start rather than retrofitted.
- Orchestrate and monitor. Schedule the Bronze→Silver→Gold refresh with Fabric pipelines, and monitor mirroring health and pipeline runs.
The one-sentence version
Mirror Oracle into OneLake, refine it through Bronze→Silver→Gold, serve Gold to Power BI with Direct Lake, and govern the whole chain once through Entra ID and Purview — with Oracle staying the transactional source of record throughout.
12.Pitfalls to Avoid
| Pitfall | Why it hurts, and the fix |
|---|---|
| Transforming data during ingestion | Buries logic in a pipeline and destroys replay — land raw in Bronze, transform in Silver |
| Skipping the Silver layer | Reports built straight on raw data inherit every source quirk — always clean and conform first |
| Reporting off normalised tables | Slow queries and confusing models — model Gold as a star schema of facts and dimensions |
| Fat semantic models | Cleanup DAX and calculated columns mean work done in the wrong layer — push preparation upstream, keep the model thin |
| Using Import mode out of habit | Stale data and refresh jobs you do not need — use Direct Lake on the Gold layer |
| Copying data that a shortcut could reference | Needless duplication and sync — use shortcuts and the OneCopy principle |
| Retrofitting governance | Painful and gap-prone — wire Entra ID, Purview, and row-level security in from day one |
| Treating the mirror as writable | It is a read-optimised reflection — keep writes and corrections in Oracle, the source of record |
13.Frequently Asked Questions
Do I move my Oracle data into Fabric permanently?
No. You mirror it continuously into OneLake, where it is analysed, but Oracle remains the transactional system of record. The lakehouse holds a refined, analytics-ready reflection — not the authoritative copy, and not the place writes happen.
What is the difference between mirroring and a pipeline copy?
Mirroring is continuous, zero-ETL change capture — the default for keeping OneLake current with Oracle. A Data Factory pipeline copy is scheduled, selective movement, useful when you want a periodic extract of specific tables rather than a live mirror.
Why bother with three medallion layers?
Separation of concerns. Bronze preserves raw data for replay and audit, Silver produces one clean version of each entity, and Gold shapes data for business questions. The layers give you lineage and let you fix cleaning logic without re-extracting from Oracle.
What makes Direct Lake better than Import or DirectQuery?
It reads the Gold Delta files in OneLake directly — so you get near-import query speed with near-real-time freshness and no refresh job, and no continuous query load on a source. For the Gold layer of a medallion lakehouse it is the recommended mode.
What is the OneCopy principle, in practice?
One physical set of Delta files in OneLake, read by every authorised Fabric engine at once. Instead of separate copies for engineering, warehousing, BI, and data science — each with its own sync and access control — there is one governed copy they all share.
Can I combine Oracle data with data from other systems?
Yes. Bring other sources into OneLake with their own mirrors or shortcuts, and model them alongside the Oracle-sourced tables in Silver and Gold. Shortcuts let you reference data in ADLS, S3, or GCS in place, without copying it.
How is the whole platform secured?
Through platform-level controls: Entra ID identity across every workload, Purview for lineage and sensitivity labels, row-level security on the Gold semantic model, and Oracle keeping its own TDE, Database Vault, and auditing at the source. Governance is set at the boundaries once, not per tool.
Where does AI fit into this?
Naturally on top — once the Oracle data is a governed lakehouse in OneLake, the Azure AI stack (Foundry, Copilot Studio, the Fabric Data Agent) can reason over it. That is a larger topic covered separately; this platform is the clean data foundation those AI workloads need.
14.Key Takeaways
The short version
• Mirror, do not migrate. Zero-ETL mirroring lands Oracle operational data in OneLake continuously, with no pipeline to build — and Oracle stays the source of record.
• Refine through the medallion. Bronze holds raw, Silver cleans and conforms, Gold models for the business — three questions, kept separate, with full lineage back to Oracle.
• Everything is Delta in OneLake. One open format, one physical copy, every engine reading it — the OneCopy principle is what makes the platform lean.
• Serve Gold with Direct Lake. Power BI reads the lakehouse directly — near-import speed, near-real-time freshness, no refresh jobs.
• Keep the semantic model thin by pushing preparation upstream into Silver and Gold, so logic is reusable and reports stay fast.
• Govern once, at the boundaries. Entra ID and Purview span the whole platform; Oracle keeps its own controls at the source.
The old integration between Oracle and a Microsoft analytics stack was a standing tax — pipelines to maintain, copies to reconcile, freshness to apologise for. The combination of Oracle Database@Azure and Microsoft Fabric replaces that tax with something close to its opposite: the operational data flows into a unified lake by itself, gets refined in visible and repeatable layers, and is served to reports at speed without a single refresh job — all governed as one estate.
The discipline that makes it work is not complicated. Keep Oracle authoritative. Land raw, refine in layers, and model for the question. Let one open copy serve every engine. Push logic upstream and keep the serving layer thin. Govern at the boundaries. Do those things, and Oracle operational data stops being the thing your analytics platform works around and becomes the trusted foundation it is built on.
Comments