1. Background

Hadson Toko, a local Asian snack distributor, currently stores their product and inventory data in a Microsoft Access table linked to a local-only SQL Server database.

The master inventory is updated manually once per day through SQL queries, which is:

• Time-consuming
• Dependent on manual execution
• Prone to human error

The company wants to move toward an automated daily update system while enabling future cloud-based analytics.

2. New Proposed Solution Pipeline

Step 1:

Set up a self-hosted integration runtime (SHIR) on the local SQL Server machine to enable Azure Data Factory (ADF) to securely connect to the local LAN server.

Step 2:

Use Azure Data Factory pipelines to:

• Extract data directly from the local SQL Server via SHIR
• Apply any necessary transformations
• Push the data directly to Airtable using HTTP connector/PATCH requests

Step 3 (Future):

Leverage Airtable data with Prometheus + Grafana for sales graphs and real-time monitoring.

Architecture Overview:

[Local SQL Server]
      |
      v
(Self-Hosted Integration Runtime)
      |
      v
[Azure Data Factory Pipeline] ---> [Airtable]
                                         |
                                         v
                            [Prometheus + Grafana Dashboards]