Founding an analytics service by moving reporting off the transactional database

claim 1.4dependent — cites claim 1 (Dynamox)

Founded a new reporting service that separates heavy analytical reads from the transactional database, resolving indicator timeouts, framing the OLTP-vs-OLAP trade-off, critically reviewing the architecture decision, and building the walking skeleton and its data layer.

impactFounded the reporting service (founding author) with analytical reads separated from the transactional database, which is the structural fix for the indicator timeouts.Delivered the base service integrated with the analytical warehouse, with a curated, cost-aware, tenant-isolated data model and infrastructure-as-code load routines.Made the architecture decision reviewable and cross-functional by framing it as an explicit matrix with a written comparison, a security/privacy review, and a preliminary recommendation, reviewed and signed off by seven stakeholders across engineering and the platform team rather than decided in isolation.Caught and closed a latency risk before it shipped, by revising my own first-pass recommendation once I identified that the analytical warehouse wasn't inherently low-latency, turning a potential post-launch incident into a design requirement instead.Qualitative: analytical architecture in rollout to eliminate the timeouts; end-state indicator latency not yet captured as a before/after number.

Context

This is my strongest evidence of strategic, data-architecture-level system design and of greenfield ownership. Most of my work extends existing systems; here I started one, which meant making foundational choices about runtime, release process, data model, cost model, and tenant isolation that are expensive to reverse later.

It shows I can hold a decision at the altitude of *where a class of workload belongs* (transactional store vs. analytical warehouse) rather than at the altitude of a single query; that I decompose a conflated decision into independent axes instead of comparing bundled options; and that I revise my own prior analysis when I find a real gap in it, instead of defending the first version. It's also my clearest evidence that I fold security and privacy into an architecture decision as a first-class input rather than a checklist applied afterward, and that the decision itself was reviewed and signed off by stakeholders across engineering and the platform team, not made in isolation.

Problem

A set of customer-facing indicators aggregated large volumes of inspection data. Those aggregations ran as heavy analytical queries, several joins plus runtime calculations (aggregations, percentages, counts), directly against the transactional database that also served live application traffic. As data grew, the indicators started timing out: the schema had been designed for transactional access, not for analytical reads, and the analytical load competed with the transactional workload it shared a store with. Optimizing individual queries was treating the symptom; the workload was in the wrong place, and a related reporting feature was about to need the same aggregations, which would only add to the contention.

Constraints

—The decision had two conflated axes. An earlier pass at this decision compared only two bundled alternatives, each changing *both* which service owns the reporting logic *and* which database backs it, which made it hard to tell which axis was actually driving each trade-off.

—The obvious fix under-weighted a real risk. The analytical warehouse under consideration is not, by nature, a low-latency store, since every query has a floor of hundreds of milliseconds to seconds. Serving a synchronous, customer-facing screen straight from it risked trading timeouts for slowness instead of fixing them.

—Founding a service means irreversible-ish choices. Runtime, project structure, release tooling, and the data model are cheap to pick and expensive to change once code and data accumulate.

—The trade-off space was wide and cross-functional. Load isolation, deploy isolation, operational complexity, eventual consistency, multi-tenancy, cost, and vendor lock-in all interacted, and the decision had to be legible enough for stakeholders across engineering and the platform team to review and sign off on it.

—Cost is a first-class constraint in analytics. An analytical warehouse bills by data scanned, so an unfiltered query is both a cost problem and, at the extreme, an availability problem.

—The data crosses a privacy boundary. The analytical copy would carry multi-tenant operational data, including fields that can identify the person who performed an inspection, making data protection an architecture question rather than something bolted on after.

Decision

I started from the trade-off, corrected my own analysis when it had a gap, then de-risked the build.

—Decomposed the decision into an explicit 2×2 matrix. Which service owns the reporting logic (the existing transactional service vs. a new one) crossed with which database backs it (a tuned relational store vs. an analytical warehouse), instead of comparing bundled alternatives. Isolating the two axes made each trade-off legible on its own: workload isolation turned out to depend almost entirely on the service axis, while analytical fit and cost depended almost entirely on the database axis.

—Went back and corrected my own earlier recommendation. After the first pass, I identified that the analytical warehouse I was recommending is not a low-latency store by nature, and that serving a synchronous, customer-facing screen directly from it could trade one kind of timeout for a different kind of slowness. I revised the decision to require an explicit serving layer rather than querying the warehouse on every request.

—Negotiated an explicit ownership boundary with the platform team. Their existing ingestion pipeline already moved operational events into the analytical warehouse; I scoped the new service to own only the aggregation layer, the API, and the cache on top of it, with one well-defined layer of cleaned tables as the contract between the two domains, rather than duplicating ingestion, retry, and dead-lettering the platform team had already built.

—Ran a full security and privacy risk review as part of the same decision, covering confidentiality (least-privilege access and per-tenant authorization on every read), integrity (deduplicating events that can arrive more than once or out of order), availability (an unfiltered query becomes a cost and availability risk in a scan-billed warehouse), and privacy (fields that can identify the person who performed an inspection), with a concrete mitigation for each, backed by comparative research on stacks and a market benchmark for the rest of the decision.

—Built a walking skeleton, the thinnest end-to-end version of the service (a lean runtime, project scaffolding, containerization, and a working connection to the analytical warehouse), to prove the shape before investing in features. I'm the founding author of the repository.

—Modeled curated analytical tables for cost and tenant isolation, including a daily snapshot and a most-recent view, partitioned by date and clustered with the tenant identifier first so common queries scan less data and one tenant's query can't see another's rows.

—Created the dataset, tables, and load routines as infrastructure-as-code, including the consistency and daily-incremental logic, so the data layer is reproducible rather than hand-built.

Trade-offs

—Separating analytical reads from OLTP over tuning the transactional queries. Moving the workload removes the root cause, resource contention on a store built for transactions, where tuning only postpones it. Accepted cost: a second data store and a pipeline to keep it current.

—A materialized serving layer with a cache in front of it, over querying the warehouse directly on every request. Costs an extra moving part, scheduled materialization and cache invalidation, but is what actually fixes a synchronous, customer-facing screen; querying a scan-billed warehouse live on every request would have re-created the latency problem in a new place.

—A hard cost ceiling that fails a query closed, over trusting every query to be written efficiently. A query missing its required filters is rejected before it runs, rather than allowed to scan (and bill for) an entire table. Costs an occasional rejected query; buys a bounded, predictable bill instead of a silent cost or availability incident.

—Owning only the aggregation layer over owning ingestion end-to-end. The rejected alternative would have rebuilt the messaging consumers, retry, and dead-lettering the platform team's pipeline already provided. The extra operational surface, and the historical-data migration that came with it, wasn't worth the marginal control it bought.

—Eventual consistency for reporting over strict freshness. Reporting can tolerate briefly stale data, so a read model refreshed on a schedule is acceptable, and far cheaper, than keeping an analytical copy strictly in lock-step.

Impact

—Founded the reporting service (founding author) with analytical reads separated from the transactional database, which is the structural fix for the indicator timeouts.

—Delivered the base service integrated with the analytical warehouse, with a curated, cost-aware, tenant-isolated data model and infrastructure-as-code load routines.

—Made the architecture decision reviewable and cross-functional by framing it as an explicit matrix with a written comparison, a security/privacy review, and a preliminary recommendation, reviewed and signed off by seven stakeholders across engineering and the platform team rather than decided in isolation.

—Caught and closed a latency risk before it shipped, by revising my own first-pass recommendation once I identified that the analytical warehouse wasn't inherently low-latency, turning a potential post-launch incident into a design requirement instead.

—Qualitative: analytical architecture in rollout to eliminate the timeouts; end-state indicator latency not yet captured as a before/after number.

Lessons Learned

Reusable engineering knowledge I carry forward from this:

—Decompose a conflated decision into independent axes before comparing alternatives. Bundling two choices into one "either/or" option makes it look like a single trade-off when it's really two, and you can end up trading away something that didn't need to be on the table.

—Revisit your own decision when you find a real gap in it. Catching that a synchronous, customer-facing read path can't tolerate a store's natural latency floor, and fixing the recommendation before it shipped, was worth more than defending the first version.

—Security and privacy analysis belongs inside the architecture decision, not after it. A data model decision already determines your tenant-isolation boundary, your access boundary, and your cost-based availability risk, so reviewing those separately, later, is reviewing them too late to change cheaply.

—Analytical load does not belong on your transactional store. When heavy aggregations and live traffic share a database, the fix is usually to separate the workload, not to tune the query.

—In an analytical warehouse, the data model is a cost decision, and an unbounded query is an availability risk rather than just a slow one. Clustering, curated tables, and a hard cost ceiling per query are what keep a scan-billed store both affordable and predictable.

Evidence

—Founding author of the service repository; built the walking skeleton and data layer.

—Authored a written architecture decision comparing four alternatives across an explicit service × database matrix, with a dedicated security/privacy risk analysis and cost guardrails as part of the recommendation, reviewed and approved by seven stakeholders across engineering and the platform team.

—Revised the decision between two versions after identifying a latency risk the first version had underweighted.

—Curated analytical tables and infrastructure-as-code load routines.

—Verified build-out against the tracker (2026-07): the staging dataset, then two curated tables (a 24-hour delta staging table and a clustered daily current-state table) loaded incrementally by a daily `MERGE` plus a wider-range routine as a safety net for late-arriving messages; a table-valued function as the single entry point the service calls, so query shape stays in versioned infrastructure rather than string-built SQL; scheduled queries for incremental refresh; warehouse permissions for the team; then the same stack in production. Every piece delivered as infrastructure-as-code, staging first.

—Verified performance and cost work: load tests run against production queries parameterized by two real tenant contexts (2026-06-26 to 2026-07-17), and a separate clustering investigation to reduce scanned bytes, both before the service went wide.

—Verified product surface (2026-07 to 2026-08): the anomaly-management page, an adherence chart tab, a recurring-alerts table with its data contract agreed front-to-back before either side was built, the endpoint behind it, and an accumulated-alerts view still in progress, each shipped behind a feature flag, several with a mocked contract landing before the real endpoint.

—Source (private): consolidated career knowledge base; internal architecture decision record; Jira epics and subtasks in the inspection domain, 2026-03 to 2026-08.