Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
In this tutorial, you build a small order-management solution with Fabric HorizonDB. You use the three Federated Analytics capabilities: database mirroring for analytics, federated queries over mirrored data and a separate warehouse, and data loading through a Copy job. You also build a Power BI report, analyze mirrored data in a notebook, and connect a local PostgreSQL application.
Important
Fabric HorizonDB is in public preview. Capabilities, limits, and behavior might change before general availability. For more information, see Microsoft Fabric preview features.
Scenario
Contoso needs an operational database for customer orders. Application queries must use the PostgreSQL endpoint, while reporting must use the mirrored analytical copy so that analytical queries don't compete with operational work.
You create these resources:
| Resource | Tutorial name | Purpose |
|---|---|---|
| Fabric workspace | contoso-horizondb-tutorial |
Contains the tutorial items. |
| HorizonDB database | contoso-orders-db |
Stores operational customers and orders. |
| PostgreSQL schema | horizon_sample |
Contains the tutorial tables. |
| SQL analytics endpoint | Created with the Fabric HorizonDB integration | Provides read-only access to mirrored data. |
| PostgreSQL foreign schema | fabric_mirror |
Exposes the mirrored Contoso tables as read-only foreign tables. |
| Warehouse | contoso-signals-warehouse |
Stores the synthetic dbo.customer_signals source used by federation and the Copy job. |
| Customer-owned foreign server | customer_signals_dw |
Connects PostgreSQL to the separate warehouse. |
| PostgreSQL foreign schema | fabric_sales |
Exposes warehouse signals without storing their rows locally. |
| Copy job | load-contoso-customer-signals |
Loads customer signals into PostgreSQL staging. |
| Power BI semantic model | contoso-orders-model |
Models customers and orders for reporting. |
| Power BI report | contoso-order-operations |
Visualizes order totals and status. |
| Fabric notebook | contoso-orders-analysis |
Analyzes mirrored data through the SQL analytics endpoint. |
| Python application | contoso-orders-app |
Queries federated and loaded signals through PostgreSQL as the database creator. |
Use these names throughout the tutorial, or record your replacement names so that you can use them consistently.
Architecture
The tutorial uses separate operational and analytical paths:
- The Fabric SQL editor uses the Fabric HorizonDB PostgreSQL endpoint for operational work.
- Fabric HorizonDB mirroring asynchronously publishes eligible tables to OneLake.
- The SQL analytics endpoint, semantic model, Power BI report, and notebook use the mirrored analytical copy.
- The PostgreSQL endpoint uses
pg_fabric_fdwto query supported Fabric data through read-only foreign tables. - A Copy job loads a bounded Fabric result into PostgreSQL staging before an idempotent publish step.
- The application connects to PostgreSQL as the creator and queries views over federated or locally loaded signals.
The PostgreSQL endpoint and SQL analytics endpoint have different engines, permissions, freshness, and supported operations. Don't use the SQL analytics endpoint for operational writes.
The mirroring process is asynchronous. A healthy database doesn't guarantee that mirrored data is current, and a healthy SQL analytics endpoint doesn't guarantee that the operational database is ready.
Sample data model
The horizon_sample schema contains two tables:
customersstores a customer name and segment.ordersstores an order reference, customer relationship, date, status, amount, and creation timestamp.
Each table has a primary key so that it can participate in relational queries and mirroring eligibility checks. The tutorial load is rerunnable and uses stable business keys to avoid duplicate sample rows.
What you learn
In this tutorial, you:
- Create a Fabric workspace.
- Create and validate a HorizonDB database.
- Create PostgreSQL tables and load sample data.
- Use the SQL editor and object explorer.
- Review basic monitoring, security, and integration state.
- Validate mirroring and query the SQL analytics endpoint.
- Query the mirrored tables from Fabric HorizonDB through federation.
- Load a bounded Fabric result into Fabric HorizonDB with a Copy job.
- Build a Power BI semantic model and report.
- Analyze mirrored order data in a Fabric notebook.
- Query federated and loaded data from a small Python application.
- Remove the tutorial resources.
Prerequisites
- A Microsoft Fabric tenant where Fabric HorizonDB is available.
- Access to an eligible Fabric capacity.
- Permission to create a workspace, or access to a workspace where you can create the required items.
- A Microsoft Entra identity enabled for the Fabric HorizonDB preview.
- Contributor or Admin workspace access and permission to create PostgreSQL database, warehouse, Copy job, semantic model, report, and notebook items.
- Permission to read the Fabric sources used for federation and loading.
- Python 3.10 or later for the application step.
You don't need to install a PostgreSQL server. Fabric HorizonDB provides the managed PostgreSQL endpoint.
Use a dedicated development or test workspace and synthetic, nonsensitive data. Confirm preview enablement and recovery limitations with your administrator or preview contact. Only the Microsoft Entra identity that creates the database can connect to PostgreSQL. Use that identity throughout the database, Copy job destination, and local application exercises.
The Fabric item is named contoso-orders-db, but its PostgreSQL connection database is postgres in the current preview. Copy connection values from the item's Visual Studio Code pane.
Tutorial sequence
Complete the steps in order:
- Create a workspace.
- Create a HorizonDB database.
- Create tables and load sample data.
- Query and inspect the database.
- Use the SQL analytics endpoint.
- Query mirrored Fabric data from Fabric HorizonDB.
- Load Fabric data into Fabric HorizonDB with a Copy job.
- Create Power BI visualizations.
- Analyze data in a notebook.
- Connect a PostgreSQL application.
- Clean up resources.