Enterprise Reporting Engine

Context & Problem

Enterprise operations required business analysts and compliance teams to export massive, multi-dimensional operational datasets into formatted Microsoft Excel workbooks. The existing implementation executed queries synchronously inside web API request threads, buffering the entire query result set into in-memory object collections before building the Excel file.

As data volumes expanded beyond several hundred thousand rows, this legacy approach triggered severe production failures:

• Web Thread Starvation: Long-running HTTP requests held ASP.NET Core connection threads open for multiple minutes, causing request queuing for unrelated users.
• Out-of-Memory (OOM) Crashes: Buffering both the raw database dataset and the in-memory Excel DOM simultaneously caused sudden garbage collection spikes and process termination.
• No Progress Visibility or Recoverability: If a network timeout occurred after four minutes of computation, the entire job was lost without audit records or retry capability.

Personal Role & Architecture

As the lead backend engineer on this initiative, I re-architected the reporting infrastructure from the ground up around an asynchronous, decoupled worker architecture.

Key Architectural Decisions

1. Decoupled Job Scheduling (Hangfire):

• When a user requests a report, the web tier validates permissions, writes an immutable audit record to SQL Server, enqueues a background job payload with query parameters into Hangfire, and immediately returns a tracking token (202 Accepted).
• The user interface polls a lightweight status endpoint via a short-lived token, freeing the web server to handle standard traffic.

2. Forward-Only Streaming (DataReader Pipeline):

• Replaced ORM collection allocation with SqlDataReader in forward-only, read-only mode (CommandBehavior.SequentialAccess).
• Paired data retrieval with streaming Excel row writers via EPPlus, writing directly to disk-backed temporary file streams rather than keeping millions of cell objects in RAM.

3. Configurable Definition Engine:

• Designed a JSON-driven schema definition format allowing engineers to configure column projections, aggregation pivots, formatting rules, and localized date/currency masks without recompiling the core engine.

Technical Challenges & Trade-offs

• Memory Ceiling vs. Formatted Output: Complex workbooks required corporate styling, auto-filtering, and frozen header panes. Standard open-source streaming tools often generate raw CSV or unformatted XML. By tuning EPPlus cell indexing and flushing worksheet chunks to disk, we preserved styling while capping peak memory consumption below 250 MB regardless of dataset size.
• Data Consistency During Extraction: To prevent dirty reads across multi-minute extraction jobs on active transactional databases, we isolated reporting reads to dedicated read-intent connections with snapshot isolation levels, preventing read locks from blocking ongoing transaction processing.

Outcomes & Lessons

• Zero Thread Starvation: Web API response times returned to sub-100ms baseline, completely insulating user-facing workloads from heavy report jobs.
• Bulletproof Reliability: Over 500,000 rows could be reliably exported into styled multi-sheet workbooks with zero Out-of-Memory events.
• Fail-Safe Observability: Comprehensive status logging and dead-letter queue inspection allowed operators to pinpoint transient SQL connection failures and automatically replay jobs safely.