In a Snowflake share, how do you let each consumer account see only its own rows?

## The problem You have one large fact table and forty partners, each of whom may see only their own slice. The naive answers are both bad: forty physical tables (forty copies to keep in step) or forty shares over forty views (forty objects to maintain and to get wrong). Snowflake's intended answer is one table, one secure view, one share. ## The mechanism: CURRENT_ACCOUNT() in a secure view When a consumer queries an object through a share, the query runs in the **consumer's** session on the **consumer's** warehouse, even though the objects and the view definition belong to the provider. Context functions therefore report the consumer. `CURRENT_ACCOUNT()` returns the identifier of the account executing the query, which gives the provider a server-side handle on "who is asking" that the consumer cannot forge. The pattern is an entitlement (mapping) table plus a filtered secure view: ```sql -- provider-owned, never granted to the share CREATE TABLE analytics.public.entitlements ( snowflake_account VARCHAR, customer_id NUMBER ); CREATE SECURE VIEW analytics.public.orders_shared AS SELECT o.order_id, o.customer_id, o.order_ts, o.amount FROM analytics.public.orders o JOIN analytics.public.entitlements e ON o.customer_id = e.customer_id WHERE e.snowflake_account = CURRENT_ACCOUNT(); GRANT SELECT ON VIEW analytics.public.orders_shared TO SHARE sales_share; ``` Adding a partner is now an `INSERT` into `entitlements`; removing one is a `DELETE`. No DDL, no new share, no redeploy. ## Why the view must be SECURE Two distinct reasons, and a strong answer names both. 1. **Shares reject non-secure views outright.** You cannot grant a standard view to a share, so this is not optional. 2. **Secure views change how the optimizer treats the view body.** For a normal view, Snowflake may inline the definition and push user predicates and expressions inside it, and the view's text is visible to anyone who can see the object. Both are leak channels: a consumer could supply an expression — a UDF, a division, a function that errors on certain values — that gets evaluated against rows the filter was supposed to remove, and infer their contents from timing or from error messages. A secure view blocks those optimizations and hides its definition from non-owners. The cost is real: you are deliberately forgoing optimizations, so secure views can be measurably slower than the equivalent plain view. That is the price of the isolation, and it is why interviewers like this question — it has a genuine tradeoff rather than a free win. ## Getting the performance back Since predicates can no longer be pushed through the view body as freely, the physical layout has to do the work: - Keep the entitlement table **small** — it is a mapping, not a fact table. A small build side keeps the join cheap. - Make sure the base table's **clustering key** includes the column the entitlement joins on (here `customer_id`), or at least correlates with it, so that a consumer's query still prunes micro-partitions instead of scanning the whole fact table before filtering. - If the filter column is high-cardinality and lookups are point-shaped, the search optimization service is the other lever. - Test the plan as a consumer would run it, not as the provider: a query you profile in your own account with your own privileges is not the query the partner runs. ## What not to grant The entitlement table itself must never be added to the share. Granting it would tell every consumer who else is a consumer and which keys they hold — a confidentiality breach in its own right and, in some industries, a commercial one. The same applies to the base fact table: if you grant `SELECT` on `orders` as well as on the view, the view's filter is decoration. ## Related but distinct surfaces Snowflake also has row access policies and column-level masking policies, which attach to the object rather than living in a view body. They compose with sharing and are the more modern expression of the same intent; the secure-view-plus-`CURRENT_ACCOUNT()` pattern remains the canonical sharing answer and the one interviewers expect, partly because it is the one that predates the policy features and appears throughout existing deployments. Whichever you use, the invariant is identical: the filter is evaluated on the provider's side, against an identity the consumer cannot choose.