Skip to main content

Dynamic Views & Data Filtering

Dynamic views let you serve different data to different customers from a single dataset. Instead of creating separate tables or shares for each customer, you define rules that filter data dynamically based on who's querying. This reduces duplication, centralizes governance, and supports complex use cases at scale.

This page covers data filtering patterns using dynamic views and the current_recipient() function, which reads properties you set when creating recipients.

D2O cost note

Dynamic views in D2O materialize on provider compute before transfer. For cost-sensitive scenarios, consider partitioned sharing instead.

Common patterns​

Tiered access​

Serve different feature sets based on license type:

CREATE VIEW catalog.schema.product_data AS
SELECT 
  id, name, category,
  CASE WHEN current_recipient('license') IN ('professional', 'enterprise') 
       THEN detailed_metrics ELSE NULL END AS detailed_metrics,
  CASE WHEN current_recipient('license') = 'enterprise' 
       THEN proprietary_score ELSE NULL END AS proprietary_score
FROM catalog.schema.base_table;

Territory filtering​

Deliver only data relevant to the recipient's territory:

CREATE VIEW catalog.schema.territory_sales AS
SELECT * FROM catalog.schema.sales
WHERE territory = current_recipient('territory');

Customer filtering​

Filter to customer-specific records:

CREATE VIEW catalog.schema.customer_orders AS
SELECT * FROM catalog.schema.orders
WHERE customer_code = current_recipient('customer_code');

Building dynamic views​

Model your filtering rules​

Create a mapping table as a single source of truth for who can see what:

CREATE TABLE catalog.schema.entitlements (
  customer_code STRING,
  allowed_territories ARRAY<STRING>,
  allowed_products ARRAY<STRING>,
  license STRING,
  effective_date DATE,
  expiration_date DATE
);

Pass context through recipient properties​

When you create a recipient, set key-value pairs that identify the consumer:

CREATE RECIPIENT acme_corp
PROPERTIES (
  'customer_code' = 'ACME-2024',
  'license' = 'professional',
  'territory' = 'north_america'
);

You can read these keys at query time through current_recipient() inside a view.

Build a dynamic row filter​

Use a view that resolves the caller's identity and applies filters:

CREATE VIEW catalog.schema.filtered_data AS
SELECT * FROM catalog.schema.base_table
WHERE territory = current_recipient('territory')
  AND current_recipient('license') = 'professional';

Add column-level masking​

Mask sensitive attributes based on recipient context:

CREATE VIEW catalog.schema.masked_data AS
SELECT
  id, name,
  CASE
    WHEN current_recipient('pii_access') = 'full' THEN ssn
    ELSE 'REDACTED'
  END AS ssn,
  CASE
    WHEN current_recipient('pii_access') = 'full' THEN email
    ELSE CONCAT(LEFT(email, 2), '***@***.com')
  END AS email
FROM catalog.schema.base_table;

Default-deny pattern​

Always design views to return zero rows if no entitlement matches. Never fail open:

CREATE VIEW catalog.schema.secure_data AS
SELECT * FROM catalog.schema.base_table b
WHERE EXISTS (
  SELECT 1 FROM catalog.schema.entitlements e
  WHERE e.customer_code = current_recipient('customer_code')
    AND b.territory = ANY(e.allowed_territories)
    AND CURRENT_DATE BETWEEN e.effective_date AND e.expiration_date
);

If the recipient has no matching entitlement row, the query returns zero rows.

Testing dynamic views​

Create test recipients​

Create recipients that represent different access scenarios:

-- Full access test
CREATE RECIPIENT test_enterprise
PROPERTIES (
  'customer_code' = 'TEST-001',
  'license' = 'enterprise',
  'territory' = 'north_america'
);

-- Limited access test
CREATE RECIPIENT test_standard
PROPERTIES (
  'customer_code' = 'TEST-002',
  'license' = 'standard',
  'territory' = 'emea'
);

-- No access test (should return zero rows)
CREATE RECIPIENT test_none
PROPERTIES (
  'customer_code' = 'INVALID',
  'license' = 'none',
  'territory' = 'unknown'
);

Validate view behavior​

Test each view with different recipient contexts to verify:

  • Premium recipients see expected columns and rows
  • Basic recipients see appropriate subset
  • Invalid recipients see zero rows (not errors)
  • Edge cases (expired entitlements, null values) behave correctly

Governance​

Ownership​

  • Assign view and share ownership to a group, not an individual
  • Document who can modify entitlement logic and mapping tables
  • Use Unity Catalog permissions to restrict who can alter views

Version and document​

  • Store view DDL and entitlement schemas in version control
  • Add change logs with effective dates
  • Communicate breaking changes to recipients in advance

Change management​

  • Require peer review before filtering logic changes go live
  • Test changes against sample recipients in non-production
  • Coordinate with affected recipients before modifying access rules

Operational checks​

  • Test views with multiple sample recipients before sharing
  • Monitor entitlement coverage (are all recipients covered?)
  • Audit entitlement table changes

For detailed governance processes, see the operations runbook.

Performance​

Query optimization​

  • Use partition pruning on large tables—filter on partition columns when possible
  • Avoid SELECT * in views—project only the columns recipients need
  • Keep entitlement mapping tables small and indexed
  • Avoid heavy UDF logic in entitlement joins
  • Cluster or Z-ORDER base tables on entitlement keys:
-- Optimize base table for territory-based filtering
OPTIMIZE catalog.schema.sales
ZORDER BY (territory);

Monitoring​

  • Track view query times to identify slow entitlement logic
  • Monitor for queries that scan excessive data due to missing filters
  • Set up alerts for unusually long-running shared queries

See monitoring for dashboard and alerting patterns.

What's next​