A construction operations data pipeline for SQL and Power BI
A construction-sector client had operational records distributed across an external API and on-premise reporting systems, making cross-project analysis dependent on manual movement.
Problem
The data needed a dependable path from the operational API into normalized SQL structures and Power BI-ready views, with synchronization that could handle incremental changes.
Existing systems
- External operations API
- On-premise SQL Server
- Power BI
Constraints
- The pipeline had to run across an on-premise boundary on a schedule.
- Incremental synchronization needed high-water marks, retries and transactional handling.
- API details, infrastructure identifiers, volumes and performance metrics remain private.
Solution architecture
An on-premise .NET ETL extracts API data, transforms it into normalized SQL tables and exposes Power BI-ready views, with incremental synchronization and scheduled operations.
Text alternative
- API
- ETL
- SQL Server
- Power BI
Implementation
Built a .NET 8 API-to-SQL ETL with pagination, high-water marks, transactional upserts, SQL bulk loading, retry and circuit-breaker resilience, CI validation and Windows Task Scheduler operations.
Technologies used
- C# 12 / .NET 8
- SQL Server
- Dapper
- SqlBulkCopy
- DbUp
- PowerShell
- Windows Task Scheduler
- GitHub Actions
Outcome
- Created a structured operational data flow from the API into SQL Server.
- Provided Power BI-ready views for project and operational reporting.
- Established resilient incremental synchronization and scheduled operations.
Lessons / reusable pattern
- Operational reporting improves when extraction, normalization and reporting views are designed as one pipeline.
- Incremental synchronization needs explicit state, transactional boundaries and recovery behavior before it is scheduled.