Relational database management systems like PostgreSQL are known for their data storage and security mechanisms. While Postgres is a high-performing database, you must install additional extensions to process and analyze varied data types in your growing datasets.
Modern data analysis requires data unification from various sources and real-time insight generation. Microsoft Fabric is a robust SaaS platform that provides integrated Microsoft services, including PowerBI, Azure Synapse Analytics, and Azure Data Factory, in a single environment. Streaming your data from Postgres to Fabric gives you a one-stop solution for data storage, management, analytics, and visualization.
To conduct the Postgres to Fabric integration, an advanced, real-time data movement platform like Estuary Flow simplifies the streaming process. This blog is a hands-on guide on using Estuary Flow to build a Postgres to Microsoft Fabric data pipeline.
Why Stream Postgres Data to Microsoft Fabric?
- Integration with the Microsoft Ecosystem: Microsoft Fabric allows you to utilize PowerBI’s analytical features as well as the processing potential from Azure Data Factory and Data Engineering services. Being able to access Microsoft’s powerful integrated tools under one roof eliminates the need for manual infrastructure management.
- Advanced Data Science and AI Capabilities: While PostgreSQL offers an open-source extension, pgml, for ML and AI tasks, Fabric offers a dedicated data science component. You can integrate this component with Azure Machine Learning to build and deploy ML models. Your organization’s developer team can leverage its built-in experiment tracking and model registry to enrich datasets and generate predictive insights.
- Access to Multiple Data Sources: Importing data into PostgreSQL requires using the COPY command or additional extensions. However, with Fabric’s Data Factory component, you can simplify data ingestion using its 140+ native connectors. These include on-premises as well as cloud-based sources.
- Support for Horizontal Scaling: Conducting horizontal scaling in Postgres requires you to use Citus, an open-source extension. Conversely, in Fabric, you get built-in support to scale Service Fabric clusters even when you have workloads running on the same cluster. You can even deploy a new primary node type and deploy autoscale rules to reduce manual intervention in cluster management.
Watch this quick overview of how Microsoft Fabric unifies your data architecture and how Estuary Flow seamlessly integrates with it before diving into the step-by-step guide.
Step-by-Step Guide: Streaming Postgres to Microsoft Fabric Using Estuary Flow
Estuary Flow is an efficient, real-time data integration platform that offers you the flexibility to build ELT/ETL pipelines. Using its extensive 200+ connector library, you can swiftly create low-to-no-code data pipelines. This tool has an easy-to-use, intuitive interface with several convenient features, making it an ideal solution for moving data from Postgres to Microsoft Fabric.
To simplify the migration process, Estuary Flow provides easily-configurable Postgres and Microsoft Fabric connectors. With these in place, all you have to do is follow the steps below, and your pipeline will be up and running in just a few minutes.
Prerequisites
- An active Estuary Flow account.
For PostgreSQL:
Configure the following for your PostgreSQL database:
- Enable logical replication.
- Your user role must have a REPLICATION attribute.
- Create a replication slot, publication, and watermarks table.
For Microsoft Fabric:
You will require the following for your Microsoft Fabric connector:
- A connection string for your Fabric warehouse.
- The Client ID and Client Secret of a service principal with access to your Fabric workspace.
- A storage account, along with its key and a container name to store staging files.
While this tutorial assumes these are prerequisites, see the Microsoft Fabric Integration guide if you would like detailed instructions on setting up and configuring these resources across Microsoft Azure.
Step 1: Set up PostgreSQL as the Source
- Sign in to your Estuary Flow account.
- Select Sources from the left navigation pane.
- On the Sources page, click the + NEW CAPTURE button.
- Search for Postgres using the Search connectors field. Once you see the PostgreSQL real-time connector in the search results, click its Capture button.
- The connector’s configuration page will open up. Here, you must fill out all the mandatory fields:
- Name: You must provide a unique name for your capture.
- Server Address: Add the host or host:port through which Estuary can reach your Postgres database.
- User: Provide the name of the database user.
- Password: Specify the password for the provided database user.
- Database: Mention the logical database name from where the data is to be captured.
- After you’ve provided the necessary details, click NEXT, followed by SAVE AND PUBLISH.
Upon completing the configuration, the connector will continuously capture updates from your PostgreSQL database using CDC. The captured updates will be consolidated into one or more Flow collections.
Step 2: Set up Azure Fabric Warehouse as the Destination
- After setting up the source, you will see a pop-up window summarizing the Postgres data capture details. Click on the MATERIALIZE COLLECTIONS button in this pop-up to start configuring Microsoft Fabric as your destination.
- Alternatively, you can select the Destinations tab from the left navigation pane, which will redirect you to the Destinations page.
- Click the + NEW MATERIALIZATION button and search for Azure Fabric Warehouse in the Search connectors field.
- When you click the Materialization button of the Azure Fabric Warehouse connector, the Create Materialization page will open up.
- You must fill in all the mandatory fields on this page. These include:
- Name: You must give a unique name for this materialization.
- Client ID and Client Secret: These are for the service principal that Estuary Flow will use to connect to your Azure Fabric Warehouse.
- Warehouse: Provide the correct name of the Azure Fabric Warehouse you wish to connect to.
- Schema: Your schema should be for bound collection tables and the associated materialization metadata tables.
- Connection String: This represents the SQL connection string for your Azure Fabric Warehouse.
- Storage Account Name and Key: Add the name of your storage account and its associated key. This is where temporary files will be written.
- Storage Account Container Name: The name of the container within your storage account that will hold the temporary files.
- Typically, the data collection from your PostgreSQL database capture would already be linked to your materialization. If not, you can manually add it by clicking the SOURCE FROM CAPTURE button in the Source Collections section.
- Finally, click NEXT > SAVE AND PUBLISH to complete the process of configuring the destination connector.
After configuring the destination, your PostgreSQL data in Flow collections will materialize into your Azure Fabric Warehouse tables. In the next section, take a look at some key advantages of deploying a streaming data pipeline from Postgres to Fabric through Estuary Flow connectors.
Why Estuary Flow? The Best Way to Stream Postgres to Fabric
- End-to-end CDC: Estuary Flow supports end-to-end Change Data Capture (CDC), ensuring that all changes made at the source get reflected in your destination database. Through the CDC feature, you can conduct real-time data streaming with a sub-100ms latency.
- Multi-cloud Deployment: Estuary Flow offers three deployment options. Public Deployment is a fully managed hosting option, whereas Private Deployment gives you complete control over your infrastructural environment. With BYOC (Bring Your Own Cloud), you can deploy Estuary in your own cloud environment for more scalability and flexibility.
- Schema Evolution: With this robust feature, you can easily handle dynamic changes in your data schemas. Estuary Flow ensures that you can maintain uninterrupted data integration across multiple systems while adapting to the changes in the source schema.
- Data Transformation Options: With Estuary Flow, you can apply custom data transformations on your ELT pipelines with dbt. For your batch and streaming ETL pipelines, you can use either SQLite or TypeScript for transformations.
- Horizontal Scalability: Estuary Flow offers a 7+GBs throughput capacity, making it an adaptive and scalable data movement tool for enterprises of all sizes. You can easily manage your fluctuating workloads through the platform’s horizontal scaling feature.
Use Cases: Real-World Applications
Microsoft Fabric is a comprehensive solution that finds applications in various domains of your organization. Here are a few real-world use cases where Fabric is beneficial:
Monitor Currency Exchange Rate
PostgreSQL offers a money data type to help you store currency and transaction values. However, its currency format is dependent on the database locale setting, making it unsuitable for financial applications that must adhere to global monetary policies.
If you are a financial institution managing currency exchange rates, migrating your Postgres data to Microsoft Fabric will prove advantageous. Fabric’s Real-Time Intelligence feature facilitates instant event-driven data processing. This allows you to track foreign transactions, import-export rates, and real-time currency fluctuations. As currency rates keep updating, you can visualize trends through dashboards and PowerBI reports.
Building Recommendation Systems
While Postgres is adept at storing customer information, building a personalized recommendation system becomes cumbersome. The process requires user-defined functions, complex SQL queries to compute similarity scores, and manual ML model training.
Streaming your consumer datasets into Microsoft Fabric provides the opportunity to access Azure AI Foundry. Fabric’s Azure AI Foundry offers pre-built AI templates for recommendation systems. You can customize and deploy these templates to Azure using either VS Code or GitHub CodeSpaces. Your developer team can focus on efficiently testing and debugging software, ensuring a robust recommendation system gets built with less time and effort.
Creating Sustainability Reports
Postgres supports geospatial data and has a geography data type under its PostGIS extension. But if you are looking to create an Environmental, Social, and Governance (ESG) report for all your construction projects, this wouldn’t suffice. Instead, transferring your geographic data into Fabric would be beneficial.
Microsoft Fabric integrates with the Microsoft Cloud for Sustainability, including Microsoft Sustainability Manager. Here, you can ingest all your real estate data and monitor carbon footprint, water consumption, and waste generation. You can leverage pre-built ESG metric definitions to compute your company’s benchmarks against regulatory standards like CSRD. After analyzing all the data, you can visualize and present it as an ESG sustainability report.
Conclusion
Migrating data from Postgres to Fabric is beneficial because Microsoft Fabric empowers you to unify vital data processing functions in a single platform. With Estuary Flow’s Postgres and Fabric connectors, even non-technical members of your team can set up a data pipeline in minutes. You can also benefit from several Estuary features, such as CDC, varied deployment options, and schema evolution, to strengthen your pipeline performance.
Create an Estuary Flow account and set up many-to-many integrations in just a few minutes!
Related Articles
Popular Articles
