Suggest an efficient automated pipeline idea

Hi  LB-Tech ,

Thanks for reaching out to Microsoft Fabric community support.

from above question your trying to set up a real-time data pipeline that integrates SQL and .bak backups. please follow below mentioned steps and let me know if this helps:

1. Setup a Centralized Database

• Use a SQL Server instance(on-premise or cloud)
• Centralize all data by, Loading SQL backups directly into the database and Restoring .bak files periodically.

2. Automate Backup Processing

For SQL Files:

• Write a script (Python, PowerShell, or Bash) to Detect new SQL files in the backup folder. Execute the SQL files to update the database.

For .bak Files:

• Use SQL Server commands or tools to automate .bak file restoration. please refer to similar community post

Solved: Re: Is it possible to connect to a .BAK file? - Microsoft Fabric Community

Automate this process using:

• Windows Task Scheduler or CRON jobs, ADF (Azure Data Factory) or custom scripts for orchestration.

3. Data Transformation

• Use an ETL tool or custom scripts toClean the data, Join and aggregate data, Apply business rules for reporting

4. Real-Time Data Integration

• DirectQuery for Real-Time Reporting: Connect Power BI directly to the centralized database. Changes in the database are reflected in Power BI in near-real-time.
• Scheduled Refresh: Use Power BI Import mode for pre-aggregated data. Set a refresh schedule to pull the latest data.

Thanks,

Prashanth Are

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly