Capital S Consulting
A Commercial Data Warehouse Your Launch Can Stand On
You bought the data. Millions of rows of claims and prescriptions, delivered as CSV files on somebody else's schedule. The warehouse is what turns that purchase into a launch asset
You Already Bought the Data
A historical data purchase shows up as a set of CSV files. Prescriptions, diagnoses, provider records, plan detail. Getting an answer out of them is manual work at most firms. Someone joins what they can by hand and produces a spreadsheet of the providers seeing the highest volume and frequency of patients who match the condition. That spreadsheet goes to the reps as a call list, and it gets rebuilt from scratch the next cycle. Linking files and patients across sources on a repeating schedule is out of reach without a dedicated analytics team. Every vendor feed arrives in its own layout, so each one is a fresh mapping problem rather than a repeat of the last. A pharma commercial data warehouse takes the sources you bought, normalizes them, links records across sources into one patient profile, and turns that into a weekly pipeline of leads that refreshes automatically. The warehouse comes first because nothing can be pushed into a CRM until the data has been normalized. In a launch program it is one of the first systems we stand up. It is the foundation under most of our life sciences CRM work
The Purchase Is Rarely the Problem
What breaks is everything between the file drop and a rep's screen. Four patterns show up again and again
The Analysis Is Done By Hand
Someone pulls what they can into a spreadsheet, ranks providers by how many matching patients they see and how often, and sends it out as a call list. It is real work and it produces something usable. It also starts from zero every cycle
Every Feed Its Own Layout
IQVIA does not format like Komodo Health. Symphony Health does not format like a specialty pharmacy dispense file. Each source needs its own mapping and its own validation rules before it can join anything else
No Team to Run It Weekly
Keeping the links current across sources is a standing job rather than a one-time project. Without analysts dedicated to it, the refresh happens whenever somebody has a free week, which is a different thing from every week
The CRM Never Hears About It
Even when the analysis gets done, the output is a spreadsheet. Reps work in Salesforce. If the warehouse does not push into the CRM, the field never sees any of it
The Foundation, Piece by Piece
We build the warehouse in your AWS account with Terraform, load the data you already bought, and keep the recurring feeds running. Here is what that covers
The Warehouse, In Your AWS Account
Built with Terraform inside your AWS organization and managed on your behalf, with automated backups on a schedule you approve. You own the account. The Terraform code sits in your repository. You are never renting your own infrastructure back from a vendor, and if we part ways, nothing has to move
Historical Data Ingestion
Loading, validation, and normalization of historical claims, prescription, provider, plan, and patient demographic data, with exception handling that cleans as it loads and reconciliation against source row counts. We have loaded enough historical claims sets to know the first pass never reconciles clean. Duplicate provider records, plan names that changed mid-year, NPIs that resolve to two addresses. That is expected work, not a surprise
Recurring Feed Pipelines
Scheduled ingestion for the subscription feeds you already pay for. Weekly is typical for prescription data. Claims tend to run on monthly or quarterly cycles depending on the vendor. Each feed carries its own mapping, normalization, and validation rules, plus anomaly flags when a delivery deviates from the pattern. A file that arrives at a third of its usual row count should raise a hand before anyone builds a report on it
Identified Versus De-Identified Architecture
The data architecture splits deliberately. De-identified real-world data sits in its own zone, keyed to tokenized patient identifiers so records link across sources without exposing identity. Identified data sits apart from it, with access controls matched to what that zone holds. The separation is structural rather than a permission someone can flip by accident
Salesforce Integration and Reporting
CRM activity syncs to the warehouse daily. Weekly summaries push back the other way, including warehouse-derived provider records and target records built from the data. Reps see current numbers on the account they are about to call without logging into a BI tool. On the reporting side, database views make Tableau and Power BI dashboards reusable instead of one-off, so analysts stop rewriting the same join every time leadership asks a variant of last quarter's question. The list itself is built in HCP targeting, and the sync follows the same approach as our broader integration work
Built to Outlast Vendor Changes
Nothing in the foundation is hard-wired to a single data vendor or one pharmacy partner. Bringing in a new vendor is real work, and every feed gets its own mapping and its own validation rules. What stays put is the schemas, the linked profiles, and the reporting on top of them, so the platform adapts instead of starting over. That matters most for specialty pharmacy integration and patient services hub feeds, which usually arrive after the warehouse is already running
Why Commercial Teams Bring Us In
Every Claims Feed Arrives Differently
Claims data comes in differently from every vendor and every source. We know what the first reconciliation looks like, which layouts change without notice, and how long a full historical load takes at real volume
Infrastructure and CRM in One Team
The people writing the Terraform are the people writing the Salesforce integration. Nothing gets dropped in a handoff between a data shop and a CRM shop that have never met
You Keep Everything
Your AWS account, your Terraform code, your data. We manage it for as long as you want that, and the exit is a credential change rather than a migration project
Frequently Asked Questions
Why not load everything directly into Salesforce?
Volume, cost, and fit. A historical claims purchase can run to tens of millions of rows, and Salesforce storage is priced for records reps act on, not for a full claims history. Query performance suffers too, because the platform was not built for the analytical scans this data invites.
The split we use is simple. Salesforce holds what reps act on, which is providers, targets, activity, and current pipeline. The warehouse holds the full history and does the heavy computation. The integrations move results between them on a schedule.
Who owns the warehouse?
You do. It is built in your AWS account inside your own organization, and the Terraform code that defines it sits in your repository. We manage it on your behalf, including backups and pipeline monitoring, for as long as you want us to.
If you bring the work in house or move to another partner, nothing has to be migrated. You change credentials and the infrastructure stays where it is. Too many teams find out late that they were renting their own data platform back from a vendor.
Which data vendors do you work with?
IQVIA, Komodo Health, Symphony Health, and MMIT formulary data are the ones that come up most often, along with specialty pharmacy dispense files and patient hub feeds. Each source gets its own mapping and its own validation rules.
The foundation itself is vendor neutral. Nothing in the base architecture assumes a particular provider, so adding a source later is a new pipeline rather than a rebuild.
How is patient-level data protected?
The patient-level data we bring into the CRM is deliberately thin: a gender and a date of birth tied to a unique patient identifier. We recommend not storing PII in the systems we build at all. Identity stays in the hub and specialty pharmacy systems, and they send back de-identified data feeds for reporting.
Everything is encrypted in transit and at rest, access is role-based, and loads are logged for audit.
How long does the foundation take to build?
A foundation with historical data loaded typically runs 8 to 12 weeks. The range depends mostly on how many data sets are in hand when we start and what condition they arrive in.
Infrastructure provisioning is the fast part. Terraform does that in days. The time goes into mapping each source, working exceptions, and reconciling loads against source counts until the numbers agree.
Turn the data you already bought into a launch asset
Get a free, no obligation consultation