From seats to prompts: reclaiming CRM licenses with a governed read model

As Snowflake’s go-to-market engineering team, we build and run the systems our sales and support teams use every day. Like many companies, we gave more people full CRM access than their work required. That meant paying for seats people did not fully use, and it meant more people could change data than needed to. This post describes how we addressed both problems with a governed read model on Snowflake in collaboration with the Data Engineering team.

The license paradox

Audit how people actually use your CRM and you’ll find a pattern almost nobody acts on. A large share of licensed users never create a record, never edit a field and never submit an approval. They open the app, look up a case status, an order amount or an agreement date, and close the tab.

These people read. The license charges the same whether you’re closing a large deal or checking whether a case was resolved. We call this the license paradox: you pay for a full license and get read-only behavior. It’s like buying a season pass to the gym for someone who only uses the vending machine.

The obvious fix that isn’t one

Moving these users to a cheaper license tier looks like the answer. It runs into three problems.

1. The cheap read-only tier may not exist. CRM licensing is complicated, and not every platform sells a tier that can read everything a user’s job needs. Once you add the objects and fields a real job depends on, the cheapest tier that still works can cost close to a full seat. Check what your own platform offers before you plan around a discount that may not exist.
2. Administration doesn’t shrink. A lower tier still needs objects, fields, page layouts and sharing rules maintained, for a group of people who only ever read.
3. The interface still doesn’t fit the job. Read-only users want answers, and the app gives them navigation. Someone who wants every case opened last quarter doesn’t want to build a list view. They want the list.

So we changed the question. Instead of asking which seat is cheapest for these users, we asked whether they need a seat at all.

What we built

Our CRM data was already synced into Snowflake, governed and queryable, so the data was never the problem. What we had to build was a read experience that meets the same security bar as the CRM.

The split is simple:

• Users who write keep their CRM license.
• Users who only read move to a governed read model in Snowflake, which they reach through a natural-language agent, the command line or plain SQL.

The seat is released, and nobody loses access to anything they were entitled to.

Architecture

The high-level flow

The design has four parts:

• The CRM is the system of record and the only write layer. Nothing in this design writes back.
• Snowflake is the governed read layer. It is unified, reasonably fresh and queryable.
• An entitlement manifest is the access contract. It maps CRM permissions onto Snowflake views. The next section explains why it works the way it does.
• A natural-language agent is the experience layer. A question goes in and a formatted answer comes out.

The design decision that carries the model: a permission profile with zero assignees

For each team we publish exactly one CRM permission profile, and we assign it to nobody.

Read_only_permission_profile_per_team -> 0 assignees

Yes, a permission profile that no one has. It sounds like a gym membership card in a drawer, but it’s the most important object in the design.

The profile isn’t for the CRM. It’s a manifest for Snowflake. It declares the exact objects and fields a team may read, and Snowflake opens those and nothing else.

That gives us three things we couldn’t get another way:

1. One place for access control. CRM administrators manage it with the tools they already use.
2. Auditable without SQL. A security reviewer inspects a native permission structure, not a view definition.
3. Safe by default. While the permission profile has no assignees, it can’t grant a live CRM user anything. We recommend a scheduled check that alerts if the assignee count rises above 0.

Engineering detail

Layer 1: the sync

The CRM syncs into Snowflake every fifteen minutes through a managed ingestion modality. Raw tables land with no heavy transformation.

SELECT …
FROM <raw_db>.<crm_schema>.<object_name>
WHERE is_active = TRUE

Fifteen minutes is fresh enough for a status lookup and cheaper than continuous streaming. It’s too slow for anything that must react to a record within seconds; see “What this design doesn’t solve”.

The permission data travels the same way. The replicated permission tables are:

• permission profiles (sets)
• permission profile assignments
• object permissions
• field permissions
• users

On top of these sit three access control views:

• User mapping view: maps a Snowflake user to a CRM identity.
• Object access view: the objects each user may read.
• Field access view: the fields each user may read.

The key design choice is that every permission layer is a view. Nothing is materialized and nothing needs refreshing, so permissions are read live from the replicated data on every query.

Layer 2: the read surface

The governed schema holds about thirty views over CRM objects, one per exposed object. Next to them are a smaller sets of views and tables that hold the entitlement data and resolve identity. Every view over CRM data is a secure view.

Every view governs itself and doesn’t trust the caller. At query time it works out who is asking and what that person may see. There are two gates.

Gate 1: object-level access

Object-level authorization is an EXISTS check against the caller’s mapped entitlements:

WHERE is_deleted = FALSE
AND EXISTS (
SELECT 1
FROM <auth_schema>.<object_entitlements>
WHERE LOWER(user_identity) = LOWER(CURRENT_USER())
AND object_name = '<target_object>'
AND can_read = TRUE
)

• The caller’s team grants read on the object: rows come back.
• It doesn’t: the view returns zero rows, not an error and not a partial result. As far as that user can tell, the object doesn’t exist. Very polite, very mysterious.

Gate 2: field-level access

Fields come in two kinds:

• Standard fields (ID, name, phone, created date and similar) are not governed by CRM field permissions, so they are never masked and always return real values. On our widest object, that’s 432 of 919 columns.
• Custom fields are gated. That’s the remaining 487 of 919 columns.

For gated fields, typed masking policies (one each for text, number, boolean, date and timestamp) return the value only if the caller is entitled to the field:

CASE
WHEN CURRENT_ROLE() IN (<privileged_admin_roles>) THEN val
WHEN EXISTS (
SELECT 1
FROM <auth_schema>.<field_entitlements>
WHERE LOWER(user_identity) = LOWER(CURRENT_USER())
AND object_name = '<object>'
AND field_key = '<object>.<field>'
AND can_read = TRUE
) THEN val
ELSE NULL
END

• The caller has field read: real value.
• They don’t: NULL.

Making field-level checks fast: the permission vector

The obvious way to mask a column is a scalar function call per field:

SELECT
id,
name,
CASE WHEN <auth_schema>.can_read_field('<object>.<field_a>')
THEN field_a ELSE NULL END AS field_a,
CASE WHEN <auth_schema>.can_read_field('<object>.<field_b>')
THEN field_b ELSE NULL END AS field_b
FROM <raw_db>.<crm_schema>.<object_name>

It’s easy to read. It also calls the function once per field, per row. On an object with hundreds of gated fields, that’s the query equivalent of checking someone’s ID at every door in the building, including the broom closet.

So we resolve entitlements once per query. A common table expression computes the whole permission vector a single time, and the query joins it to the base table:

WITH perm_vector AS (
SELECT
MAX(IFF(LOWER(field_key) = LOWER('<object>.<field_a>'), 1, 0)) AS can_field_a,
MAX(IFF(LOWER(field_key) = LOWER('<object>.<field_b>'), 1, 0)) AS can_field_b
 — one flag per gated field
FROM <auth_schema>.<field_entitlements>
WHERE LOWER(user_identity) = LOWER(CURRENT_USER())
)
SELECT
IFF(pv.can_field_a = 1, src.field_a, NULL) AS field_a,
IFF(pv.can_field_b = 1, src.field_b, NULL) AS field_b
FROM <raw_db>.<crm_schema>.<object_name> AS src, perm_vector AS pv

The aggregate has no GROUP BY, so the CTE always returns exactly one row. The join is a cross join against that single row, and every per-row function call becomes a flag lookup. Governance survives and the query stays fast.

What a user actually sees

Here is a user whose team is entitled to the custom field field_a but not to field_b, next to a user with no entitlement on either:

• Standard fields (ID, name, phone): both users see real values.
• field_a (custom, granted): the entitled user sees the real value; the other user sees NULL.
• field_b (custom, not granted to the team): both see NULL. Even a fully entitled user can’t see a field the manifest doesn’t list. That’s field-level enforcement, exactly as the CRM defines it.

One query and one view give two users different results, each matching that user’s CRM permissions.

Identity resolution

The chain runs from the database session to the entitlement set:

Snowflake user -> employee record -> team -> permission profile -> entitled objects and fields

Every link has to resolve. “What this design doesn’t solve” explains what happens when one doesn’t.

How a permission change flows through

1. A CRM admin grants a new field permission on the team’s profile.
2. The ingestion modality syncs it on its next run (up to fifteen minutes).
3. The field access view reflects the change at once, because it’s a view over live replicated data.
4. The user’s next query returns the real value.

No code changes, no pipeline runs and no policy updates. Governance stays with the CRM admins, and the data platform team never becomes the access-request help desk.

Layer 3: access control and compute isolation

Every read-model query runs down one narrow path:

USE ROLE <read_only_role>;
USE DATABASE <read_db>;
USE SCHEMA <read_schema>;
USE WAREHOUSE <read_warehouse>;

One role, one schema and one warehouse. Three things follow from that:

1. Auditing is straightforward. Every read-model query is attributable to a single role in QUERY_HISTORY.
2. Spend is measurable. A dedicated warehouse isolates the compute, so the program’s cost is measurable on its own.
3. No accidental writes. The role holds no write privileges.

To be clear, isolation does nothing for CRM performance; the two systems never shared compute. What it gives you is clean cost attribution and separation from other Snowflake workloads.

Layer 4: the prompt

All of the above is invisible to the reader. What they see is a prompt:

show <object>: <record_id>, fields <field_a>, <field_b>, <field_c>

The agent passes the prompt to a language model. The model writes SQL against the governed schema. The secure views evaluate entitlements at query time. Results come back as text or a table.

Governance lives in the views, not the prompt layer, and that’s what makes a crafted prompt harmless. The view resolves CURRENT_USER() itself, so the model never holds a privilege the caller lacks. You can ask the agent very nicely for someone’s SSN. It will still come back NULL.

Try the pattern in fifteen lines

You don’t need a CRM to see the permission vector in action. Run this in any Snowflake worksheet. It uses inline values, creates nothing and needs no privileges.

WITH entitlements AS (
SELECT * FROM VALUES
('alice', 'case.subject'),
('alice', 'case.amount')
AS t(user_name, field_key)
),
perm_vector AS (
SELECT
MAX(IFF(LOWER(field_key) = LOWER('case.subject'), 1, 0)) AS can_subject,
MAX(IFF(LOWER(field_key) = LOWER('case.amount'), 1, 0)) AS can_amount,
MAX(IFF(LOWER(field_key) = LOWER('case.ssn'), 1, 0)) AS can_ssn
FROM entitlements
WHERE LOWER(user_name) = LOWER('alice') — change to 'bob' and run again
),
src AS (
SELECT * FROM VALUES (1, 'Broken widget', 99.50, '111-22-3333')
AS s(id, subject, amount, ssn)
)
SELECT
src.id,
IFF(pv.can_subject = 1, src.subject, NULL) AS subject,
IFF(pv.can_amount = 1, src.amount, NULL) AS amount,
IFF(pv.can_ssn = 1, src.ssn, NULL) AS ssn
FROM src, perm_vector AS pv;

• As alice: you get the subject and the amount. The national identifier comes back NULL.
• As bob, who has no entitlement rows: every gated column comes back NULL. (The id column isn’t gated in this example, so it still shows.)

Bob’s result comes from a quirk worth knowing. An aggregate over an empty set still returns one row, and every flag in it is NULL. Because IFF(NULL = 1, x, NULL) returns NULL, an unmapped user sees a row with no values. The pattern fails closed on the data, which is what you want. It also fails silently, which is a problem covered below.

Scoping: who doesn’t move

Phase one looked at twenty teams and moved eighteen. We dropped two during scoping because their daily work includes creating and approving records.

The rule we held to: anyone who creates, edits or approves keeps a seat.

We considered a write-back path for the near misses and decided against it. A write path through a read model turns a clean audit story into a hard one, and the integrity of the model was worth more to us than two more teams.

Validating entitlements

For each team we ran the same test structure on both surfaces, the CRM and the prompt:

Test case: TC-<team>-01
Object: <gated_object>
Fields: <field_a>, <field_b>, <field_c>
CRM check: Sign in as a test user holding Read_only_permission_profile_per_team. Confirm read access on the listed fields. Confirm create and edit are blocked.
Prompt check: Ask the same question in natural language. Confirm the returned values match the CRM. Confirm restricted fields return no value.

Every team needed both kinds of case:

• Positive cases prove entitled access works.
• Negative cases prove restricted fields stay blocked. These are the ones that found the bugs. Nobody ever filed a ticket saying “I can see too much”.

We validated in five phases:

1. Permission data: the profile, its object permissions and its field permissions (including the __c naming) arrived intact in Snowflake.
2. Access control views: the object and field access views resolve the test user correctly, and the readable field count matches the profile.
3. End to end: an entitled user gets rows and an unentitled user gets 0. Granted custom fields return real values, ungranted ones return NULL, and standard fields always return real values.
4. Dynamic change: grant a field in the CRM, wait one sync, and it appears. Revoke the object, wait one sync, and the row count drops to 0. No deploys in between.
5. Regression: the privileged admin bypass still returns real values for every column.

What this design doesn’t solve

Know these edges before you copy it.

• It doesn’t write. There’s no record creation, field editing, approvals or attachments. Anyone whose work includes those keeps a seat.
• Data is up to fifteen minutes old. Any question whose answer changes within a minute belongs in the CRM.
• Restricted and empty look identical. A blocked field returns NULL, and so does a genuinely empty one. Without clear messaging, a user can’t tell “you may not see this” from “there’s nothing here”. Fix it in the interface by showing restricted fields as “No access” rather than blank.
• Identity gaps fail quietly. A contractor with no employee record resolves to no entitlements, and as the walkthrough shows, that returns a row of NULLs rather than an error. Add an explicit check that raises a diagnostic when identity resolution finds nothing, and design clear messaging for the user.
• Permission drift is a live risk. The manifest lives in the CRM and enforcement lives in Snowflake, so a scheduled pipeline has to carry every change across. Alert when it doesn’t.
• Upstream schema changes break views. When CRM metadata shifts, view definitions need automated updates and someone needs to be told. A silently broken view in a governance layer is worse than an obviously broken one.
• Slow governance has a human failure mode. Heavy per-row logic doesn’t let users bypass a secure view. It does something subtler: it pushes users back to the CRM, or pushes an engineer to “simplify” the view and weaken the control. That’s why the queries must stay fast.
• Check your CRM license agreement. This design moves read access outside the licensed application. Whether your agreement permits that is a question about your contract, and this post can’t answer it. Raise it with your vendor and your legal team before you build anything, and don’t assume the answer either way.

Why we think the trade was right

Negotiating a cheaper license tier would have been easier. This is the comparison we used:

• Allocation. Seat-based reads: full seats held by read-only users. Governed read model: writers keep seats and readers move to Snowflake.
• Access management. Seat-based reads: per-user field-level security across hundreds of users. Governed read model: one declarative, zero-assignee manifest per team.
• Experience. Seat-based reads: manual search, navigation and scrolling. Governed read model: a question in plain language and a formatted answer.
• Cost visibility. Seat-based reads: bundled into the seat. Governed read model: read compute isolated on a dedicated warehouse.
• What it can’t do. Seat-based reads: nothing is off limits. Governed read model: no writes, no approvals and up to fifteen minutes of lag.

The model is narrower than a seat on purpose. It works only because the people it serves were already only reading, and that’s the first thing to check in your own org.

Phase one moved eighteen teams onto it. If you’re paying for full seats for people who only read, this pattern is worth a try.

Team: Mahesh Naiknaware, Akash Dhabe, Rishabh Sharma, Komal Kulkarni, Ankit Dahiya, Amit Vashisht, Abhishek Mishra, Roshni Mishra

Tags: Snowflake Cortex AI · SnowWork · Snowflake Cortex Code · GTM · Data Engineering

From seats to prompts: reclaiming CRM licenses with a governed read model was originally published in Snowflake Builders Blog: Data Engineers, App Developers, AI, & Data Science on Medium, where people are continuing the conversation by highlighting and responding to this story.