top of page

Background

RetailCore Ltd is a mid-market retail operator running 12 regional warehouses with close to 80,000 active SKUs, where inventory data was tracked in an on-premise PostgreSQL database but moved into Microsoft Fabric through a manual daily CSV export — a process that had already failed three times in a single quarter and left merchandising teams making high-value stock decisions on data they couldn't verify as current.

 

This case study covers the automated Fabric Pipeline I designed to replace that manual step: an On-premises Data Gateway connecting securely to the PostgreSQL server, a Copy Data activity performing a full extract and overwrite into a governed Lakehouse Delta table.

Problem

  • ​​Inventory data for ~80,000 SKUs across 12 warehouses was maintained in an on-premise PostgreSQL database, but moved into Microsoft Fabric only through a manual daily CSV export handled by a single analyst. 

  • No automated mechanism existed to transfer data from PostgreSQL into Fabric, making the entire reporting layer dependent on one person's availability. 

  • Three export failures occurred in a single quarter, each one leaving merchandising and procurement teams without a reliable view of current stock. 

  • Manual CSV files were prone to truncation, encoding issues, and silent column omissions whenever the source schema changed, with no schema enforcement to catch these errors.​

Solution

On-Prem Gateway Connection

Started the setup by installing the On-premises Data Gateway software directly on the local server hosting the PostgreSQL database, accepting the terms of use to begin the installation

Once the installation completed successfully, signed in with the work email address to register the gateway and link it to the organization's Fabric account

Chose to register a new gateway on this computer, setting it up as a fresh connection point rather than migrating or restoring an existing one

Named the new gateway and set a recovery key, which is required to restore the gateway later if needed and must be stored safely since it cannot be changed afterward

Confirmed the gateway was fully set up and running, with the Status page showing it ready to securely connect the on-premise PostgreSQL server to Fabric

Verified the gateway came online and showed "Ready" status specifically for Microsoft Fabric, confirming it was properly connected and available to be used as the secure tunnel for the pipeline.

Fabric Settings

  • Went to Fabric's Settings menu and opened "Manage connections and gateways" to check the gateway from the Fabric side, not just the local installer.

  • Confirmed the gateway appeared in the On-premises data gateways list with an "Online" status, showing it was successfully registered and ready for use in Fabric

PostgreSQL

Set up the source database in PostgreSQL, creating the inventory table with the correct structure and an audit log table to track pipeline runs, then loaded it with sample data to simulate the real warehouse environment before building the pipeline

Reviewed the sample inventory data to confirm it looked realistic, with 672,007 rows spread across product names, warehouse codes, stock counts, timestamps, and unit costs, ready to be used as the source for the pipeline

Copy Data Activity in Fabric

Created a new pipeline named "Pipeline_CS08" in the Fabric workspace to start building out the automated ingestion process

Added a Copy Data activity to the pipeline and configured its source, connecting to the PostgreSQL database through the gateway and pointing it to the InventorySnapshot table, with the connection tested successfully.

Set the destination to the Lakehouse, targeting a new table for the inventory snapshot and choosing "Overwrite" as the table action so every run fully replaces the previous data rather than appending to it.

Lakehouse

Confirmed the pipeline ran successfully by checking the Lakehouse, where the InventorySnapshot table now held the inventory data pulled in from PostgreSQL, complete with SKU IDs, product names, warehouse codes, stock counts, and pricing

Outcome

  • ​The manual CSV export process — responsible for three failed transfers in a single quarter — was fully replaced with an automated pipeline, removing the single point of human failure.

  • The On-premises Data Gateway allowed Fabric to reach the internal PostgreSQL server securely, without exposing it to a public endpoint or changing any firewall rules

bottom of page