SQL and data engineering
The foundation under reliable analytics: pipelines that run, warehouses that scale, and one governed source of truth. When a county, a city or a state program has the same number in four systems and four different answers, this is the layer that fixes it, and no dashboard built on top will fix it instead.
Blue Peak Data Consulting builds that layer on SQL Server, Azure, Azure Data Factory, Amazon Redshift and Oracle, or against the flat exports your software vendor will actually give you. Everything is documented, and the pipeline is yours when the work ends.
Data infrastructure that holds up
Four pieces of work. A first project is usually one or two of them rather than all four.
ETL and ELT development
Ingestion, transformation and scheduled refresh against warehouse, cloud and file sources, built to survive the late, malformed and half empty files your systems actually produce rather than the clean ones a demo assumes.
Warehouse and model design
Star schemas, source of truth views and staged layers designed around the reports you have to produce, not around a textbook diagram. Small enough to hold in your head, which is what makes it maintainable after we leave.
Query optimization
Common table expressions, window functions, indexing and refactoring that turn a job somebody starts before lunch into one that finishes while they read the email about it.
Data cleaning and integration
Deduplication logic, validation rules and reconciliation across systems that have never agreed with each other, with the exceptions surfaced in a report rather than silently dropped.
The data problems underneath your reporting problems
Five systems, five versions of the truth
Finance says one number, the program office says another, and the report going to the board says a third. We consolidate the sources into governed views so every downstream report pulls from one definition.
The nightly load breaks most weeks
Fragile manual steps that live in one person’s calendar get rebuilt as monitored, documented, scheduled pipelines with failure alerting, so a break is a message rather than a discovery.
Queries crawl as the data grows
Modeling and indexing fixes that scale with your data instead of fighting it. The same query on the same server can be a very different query.
One person knows how it works
Everything we build is documented and trained on. When that person retires, transfers or takes a month off, the reporting does not go with them.
SQL and data engineering for public agencies
Public sector data is rarely in one place and rarely under one owner. Finance runs an ERP, the program office runs a case system, permits run somewhere else, and the state wants a file in a format none of them produce. Here is what we build for that.
One dataset behind the council packet
Finance, payroll, permitting and program exports pulled into one governed model on a schedule, so budget to actual by department, headcount and capital project status all come from the same load and reconcile to each other before anyone opens the report.
Reconciling what you report with what you count
State and federal submission files loaded alongside your local counts, with a difference report that shows which records fell out and why. This is the work that stops an amended submission from becoming an annual event.
Grant and subrecipient rollups
Twelve spreadsheets from twelve subrecipients, each with its own column order, validated on load and rolled up to award level. Spend against budget, burn rate and performance measures land in one table your grants manager can actually check.
History that survives a system migration
When a records or permitting system gets replaced, the old data usually stops being readable long before anyone stops needing it. We stage the retired system into a queryable archive and map its codes to the new ones, so a five year trend line still runs across the seam.
Public health and vital statistics feeds
Scheduled loads of state extracts, deduplication across name and date variants, standardized geography down to ZIP code or census tract, and case counts that hold still once published.
The nightly job that used to be a person
The download, the paste, the pivot and the email become one scheduled pipeline with logging, retry and an alert when it fails. The person gets the first week of the month back.
Most of this work starts from exports rather than direct database access, because that is what a public agency can approve quickly. A scheduled CSV, a reporting view your vendor already publishes, or a workbook your analyst already maintains is enough to build a real pipeline against, and it keeps the security conversation short.
Systems we work with
Databases and warehouses
- Microsoft SQL Server
- Azure and Azure Data Factory
- Amazon Redshift
- Oracle SQL, including pattern extraction from free text fields with regular expressions
- Staged and layered warehouse design on any of the above
Microsoft 365 and the Power Platform
- Power BI semantic models and DAX as the reporting layer
- Power Query for transformation close to the report
- SharePoint and Dataverse as governed sources and destinations
- Power Automate for scheduled movement and alerting
- Tableau on SQL Server, where an agency already reports there
Files and line of business exports
- Excel workbooks, including the ones with merged headers
- CSV and fixed width scheduled exports
- Reporting views your software vendor already publishes
- Salesforce and other CRM extracts
- State submission and reporting files
We will not claim familiarity with a records system, ERP or permitting platform we have not worked in. What we do instead is read your export, ask about every ambiguous field, and write down what each one means, so the next person does not have to ask. On a first project we usually do not need direct access to the source system at all.
How the work runs
Four phases, the same on every Blue Peak project (how we work). Each phase is its own written scope, its own acceptance and its own invoice, which is what makes it easy to fit inside a small purchase.
Discovery
We inventory the sources, read a real extract from each one, and list the fields with you. Out of that comes a written scope, a written quote and an honest read on which of your reporting problems are actually data problems. Sometimes the answer is that one query fixes it and no pipeline is needed.
Solution design
The target model, the load schedule, the keys, the validation rules and the definition of every derived field get written down and reviewed with you before anything is built. This is where the disagreements between departments get settled on paper, which is much cheaper than settling them in production.
Implementation
Pipelines get built against your real data, run in parallel with whatever they replace, and compared row by row until the numbers match. Logging, retry and failure alerting are part of the build, not a later phase.
Optimization and handoff
Tuning after the first production runs, then you get the code, the schema documentation, the field level data dictionary and a working session with whoever will maintain it. The pipeline runs in your environment on your schedule. There is no hosted product to keep paying for and no code held back.
Who does the work
Our senior consultants lead every build, and the consultant who scopes your pipeline is the one who writes it. Our lead consultant brings twelve years in production analytics, most of it in SQL Server, Azure and the Microsoft stack.
That consultant led reporting on a $30M multi jurisdictional federal initiative from 2019 to 2022, building Tableau reporting on SQL Server, integrating grantee data across jurisdictions, and producing grant compliance reporting that passed federal funder review. At a national telecom the same consultant unified a sales pipeline spanning six source systems into one environment on Azure Data Factory and SQL Server, wrote Oracle SQL that pulled structured codes out of unstructured free text in production, and built an analytics product line used by more than 100 people.
That record was earned before Blue Peak was formed, so we describe it as our people’s experience rather than as the firm’s past performance, on purpose. What you get is the people who did that work, with no junior handoff.
Questions agencies ask
Do we need a data warehouse, or is that over engineering for us?
Often it is. A single agency with three sources and one monthly report usually needs a set of governed views and a scheduled load, not a warehouse program. The test is how many places a definition currently lives and how often the same extract gets rebuilt by hand. We will tell you in discovery if the smaller answer is the right one, because a warehouse nobody maintains is worse than the spreadsheets it replaced.
Our vendor will not give us direct database access. Can you still help?
Yes, and this is the normal case. A scheduled CSV export, a reporting view the vendor already publishes, or a workbook your analyst maintains is enough to build a real pipeline against. Starting from exports also keeps the security review short, since nothing new touches the source system.
What happens when a load fails at two in the morning?
Somebody gets a message, and the message says which step failed and what it was reading. Logging, retry and alerting are part of the build. The pipeline also fails loudly rather than quietly: a load that would publish half a month of data stops instead, so nobody reports on a partial file.
Can the work stay inside our own environment?
Yes. Pipelines are built and run in your tenant or your subscription, against your storage, under your identity and access rules. Nothing is copied to a Blue Peak server and nothing goes to an outside service for processing. Where data can be aggregated or stripped of identifiers before it moves, that is the version we would rather work with.
Who owns the pipeline afterward?
You do. Handover includes the SQL and pipeline definitions, the schema documentation, a field level data dictionary and a working session with whoever will maintain it. It runs on your schedule in your environment. If you never call us again, it keeps running.
Do you work with agencies outside Kansas?
Yes. Blue Peak is based in Olathe, Kansas, works across the Kansas City metro and Missouri, and delivers remotely beyond both. The build happens inside your environment, so distance changes nothing about the work. Blue Peak is a SAM.gov active small business, UEI PRPBEM9HEND3, CAGE 15CS5, registered under NAICS 541611, 541512 and 541690, and will complete whatever vendor registration your state or agency requires before an award.
More from Blue Peak
Analytics and reporting built for government work covers how Blue Peak contracts with public agencies, including UEI, CAGE and registered NAICS. Power BI dashboards for police departments shows what this foundation gets used for at a small agency, and the public safety demo is a working dashboard built on published city data you can check against the source. The other two service pages are Power BI consulting, for the reporting layer on top, and workflow automation and custom apps, for the manual steps around it.
Send us the report you hate producing
Name the recurring report that costs your team the most time, and tell us roughly where the data comes from. The monthly reconciliation, the quarterly state file, the spreadsheet four departments email back and forth. One line is enough, plus a rough figure for how long it takes today.
You get an honest read back within one business day: what it would take, roughly what it would cost, and whether it is worth doing at all. If one query would fix it, we will say so.