Data & Analytics
Data Platforms & Warehousing
Consolidating operational data into a governed platform for analytics.
What this covers
A data platform pulls data from the systems an agency already runs, cleans and organizes it, and makes it available for reporting and analysis without touching the source systems. HQT designs and builds these platforms on cloud data warehouses and lakehouse architectures.
Typical activities
- Source system inventory and data profiling
- Ingestion pipelines with scheduling, monitoring, and retry logic
- Dimensional or lakehouse modeling suited to the agency's reporting needs
- Historical data loads and change-data-capture for ongoing updates
- Security, masking, and row-level access for sensitive data
- Documentation and a data catalog
What you receive
A running platform with pipelines, models, and documentation; a data catalog; and training for the agency staff who will extend it.
Considerations for public agencies
Data from public safety, health, and human services systems carries specific handling requirements. The platform design separates and protects regulated data while still allowing aggregate reporting.
Signals that a platform is the right fix
Consolidation is worth doing when the cost of answering a question, rather than the question itself, has become the obstacle. The recurring signs:
- Two departments answer the same question with different numbers and no one can say which figure is authoritative.
- Reporting depends on exports, spreadsheets, and manual joins that only a handful of staff understand, and it stops when they are out.
- Reporting queries run against production systems and get pushed to overnight windows because they slow daily operations.
- A routine request from a council member, a board, or a state program takes far longer than the question warrants because the data is reassembled from scratch each time.
- The agency pays a line-of-business vendor for additional reports built on data the agency already owns.
- Grant and state reporting is a scramble each cycle rather than a rerun of an established process.
- A forecasting or performance measurement effort has stalled for want of a clean historical record.
The counter-signal matters as much. If the questions live inside one system and its native reporting can answer them, a warehouse adds cost without adding answers. Fix the report first.
Getting data out of the systems you already run
Access method decides more about schedule and cost than the choice of platform does. In rough order of preference: a read replica of the source database, a vendor-supported API, change-data-capture from the database log, and scheduled file extracts to a secure location. The last works but caps how fresh the data can be.
For vendor-hosted systems, what is possible is set by the maintenance agreement and the vendor's export interface rather than by the platform design, so read the data-access language in the existing contract before scoping and open the vendor request early: vendor scheduling is frequently the longest lead item. Identifiers for people, parcels, and addresses rarely match across financial, permitting, utility billing, records, case management, work order, and GIS systems, so decide early whether the platform resolves entities across sources or only reports within each one. If a source system is mid-replacement, stage its ingestion so the model does not permanently encode the outgoing system's quirks.
What to specify in the solicitation
- Name each source system and the intended access method, and require bidders to state what they assume about incumbent vendor cooperation.
- Require transformation logic in version control in a repository the agency owns, written in standard SQL or a widely used open framework rather than a proprietary tool whose logic cannot be exported.
- Define acceptance as reconciliation against the system of record for named measures over a named period, with a written tolerance and an explanation for each variance.
- State who owns the cloud accounts the platform runs in. Agency-owned accounts with vendor access are easier to unwind than a vendor-owned environment in which the agency is a tenant.
- Ask for one-time build cost and recurring run cost separately, with the refresh frequency, data volume, and query load assumed behind each, plus budget alert thresholds.
- Name the regulated data in scope and require the proposal to describe the segregation design and the security review path it expects to follow.
Measuring whether the platform worked
Set the measures before go-live and report them on a schedule rather than only at acceptance: freshness against a stated target for each dataset, reconciliation variance against the system of record, pipeline failures with time to detect and time to restore, the manual extracts and shadow spreadsheets actually retired, and monthly cost against estimate with the heaviest queries identified. Track query usage as well. A platform whose pipelines run cleanly but whose tables no one queries has usually been modeled around the shape of the source systems rather than around the questions the agency needs answered, and that is a modeling problem rather than an adoption problem.
Working with HQT on this
HQT scopes this kind of work as a fixed-price or time-and-materials engagement with a written statement of work, named staff, and a defined acceptance process. We are candid about what we can take on and prefer a well-run project to an oversized one. Contact us to discuss a requirement, a draft scope, or an upcoming solicitation.
Frequently asked questions
Do we have to replace our ERP or permitting system to build a data warehouse?
No. A data platform reads from those systems and writes nothing back to them unless a write-back is deliberately designed and agreed with the system owner. What each source does need is an agreed access method, whether a read replica, an API, change-data-capture, or a scheduled extract, and a window in which reading it will not compete with daily operations. If a source system is already being replaced, sequence it last so the model is not built around a system on its way out.
What is the difference between a data warehouse and a lakehouse, and which does a city or county need?
A warehouse holds structured, modeled tables optimized for reporting, while a lakehouse keeps files in open table formats on object storage and adds warehouse-style query and governance over them. For an agency whose reporting is mostly financial, permitting, case, and utility data, the warehouse model is usually the simpler fit and the easier one to staff. A lakehouse is worth the added complexity when there is meaningful semi-structured or high-volume data such as sensor readings, telemetry, or document and media metadata. Many agencies end up running a warehouse-style modeled layer on a platform that also supports open table formats, so the choice is less binary than the labels suggest.
Can CJIS, HIPAA, or FERPA regulated data live in the same data platform as other agency data?
Usually yes, provided the design enforces separation through data classification, schema or workspace segregation, encryption, row- and column-level controls, and access limited to authorized and, where required, screened personnel. Which rules apply depends on the data: the FBI CJIS Security Policy for criminal justice information, HIPAA where the agency acts as a covered entity or business associate, FERPA for education records held by an institution subject to it, and IRS Publication 1075 for federal tax information. These are administered by different bodies, the CJIS policy through your state CJIS Systems Agency, and the requirements are revised over time. Treat the segregation design and the security review path as scoped work, and confirm current expectations with the data owner and your security officer before ingestion starts.
What drives the monthly cost of running a government data platform?
Storage is usually the smaller line. Cost is driven mainly by compute: how often pipelines refresh, how much data each run processes, how heavy the dashboard and ad hoc queries are, and how many people query at once. Consumption pricing turns an inefficient query or an unattended refresh into an expense rather than a slowdown, which is the usual source of a surprise bill. Ask any vendor for sizing assumptions, a recurring run estimate separate from build cost, and budget alerts configured before go-live.
How do we keep the platform from becoming another system only the vendor understands?
In a data platform the risk is less about the product than about where the transformation logic lives. Apply a concrete test before acceptance: can agency staff open a repository the agency controls, read the logic in standard SQL or a widely used open framework, and trace a published number back through it to the source field? If that logic exists only inside a vendor's graphical tool or a hosted environment the agency does not own, the answer is no, whatever the contract says about deliverables. Owning the cloud accounts the platform runs in, rather than sitting as a tenant in a vendor environment, is what makes a later transition practical.