Industries

The constraints are the design

Two questions come up in the first session on every engagement: who is allowed to see a given row, and how far back a published number has to be reproducible. The answers set the access model and the amount of history the warehouse carries. Both are expensive to change once the warehouse is loaded, so we settle them before the first table is built.

01Sector

Healthcare and Life Sciences

Clinical, claims and operational records have to be joined together, and every access rule attached to any one of them still applies to the joined result.

What makes it hard

Patient identity resolved across systems that hold no identifier in common
Clinical events arriving continuously while claims arrive weeks after the encounter
De-identified extracts that can be re-identified once they are joined to something else
Retention periods longer than the service life of the systems holding the data

What it means for the design

HIPAA obligations shape the access model from the first design session. Row-level security policies and masking on identifier columns are built early, because adding them to a populated warehouse later means reloading tables and re-running every downstream test.

02Sector

Financial Services

Regulators and auditors ask for a published figure to be reproduced months later, down to the individual transactions behind it.

What makes it hard

Regulatory reports that have to be regenerated for a date in the past
Reference and market data with several competing sources of truth
Late-arriving and restated transactions that change figures already published
Entitlements that vary by desk, region and role

What it means for the design

We store history so that a report can be regenerated exactly as it was published. Effective dating and slowly changing dimensions are settled during the modeling phase, well before anyone asks for a restatement.

03Sector

Insurance

Policy, claims and actuarial data sits across separate administration systems, some of which arrived through acquisition and were never consolidated.

What makes it hard

Policy administration systems that each count an in-force policy differently
Long-tail claims that stay open across many reporting periods
Actuarial extracts that began as one-time pulls and are now production dependencies
Broker and third-party files that arrive with no agreed schema

What it means for the design

Conformance across the acquired systems is the bulk of the work. Agreeing one definition of a policy or a claim usually takes longer than building the pipelines, because the team running each source system can defend the one it already uses. We document the differences and put the choice to a single business owner.

04Sector

Retail and Consumer

Transaction volumes are high enough that modeling decisions show up on the monthly bill, and the business wants yesterday's numbers before the stores open.

What makes it hard

Point-of-sale and ecommerce data that has to reconcile to the finance ledger
Inventory positions that are correct only if every movement is captured
Customer identity across loyalty accounts, guest checkout and in-store purchases
Seasonal peaks that make a fixed warehouse size either wasteful or too small

What it means for the design

We agree on a monthly Snowflake spend target during design and report consumption against it. Warehouse sizing and clustering keys are what change that number most, and both are easier to get right while the model is still on paper.

05Sector

Manufacturing and Supply Chain

Sensor readings and ERP records describe the same plant at grains that do not match.

What makes it hard

Sensor and machine readings at a grain far finer than any report needs
ERP data that describes intent while operational data describes what happened
Supplier files that arrive in a different format from each supplier
Plant systems that no one is willing to modify or replace

What it means for the design

Keeping every reading at full resolution costs more than the reports justify. The reporting grain is settled with the plant and planning teams before modeling starts, and we retain the raw feed only where a stated requirement needs it.

06Sector

Software and Technology

Product telemetry, billing and customer records feed the same platform, and some of it usually has to be exposed back to your own customers through the product itself.

What makes it hard

Event volumes that make a naive model expensive within weeks
Usage data that has to agree with what was billed
Embedded, customer-facing analytics with a hard tenant boundary
Schema changes shipped by product teams with no notice to the data team

What it means for the design

We treat multi-tenant isolation as a correctness requirement. The access model is designed first, then tested with a suite that attempts cross-tenant reads through every query path, including the ones the application does not use today. That suite runs on every release.

Not on this list?

These six are where the constraints above appear most often. The same access and history questions apply outside them. Describe what you are dealing with even if your sector is not named here, and we will tell you which of these six it resembles.

Start a conversation