Data Sharing And Access Patterns

Why this matters

As a Data Engineer, you enable safe, fast, and reliable ways for others to use warehouse data. Good sharing and access patterns reduce cost, protect sensitive data, and keep analytics and ML teams productive.

• Serve BI dashboards without overloading the warehouse.
• Enable data scientists to explore at scale with guardrails.
• Share governed datasets with partners or other domains.
• Apply row/column policies to protect PII while preserving utility.

Concept explained simply

Data sharing and access patterns are reusable ways to let others read warehouse data safely and efficiently. You pick the right interface (views, extracts, files, APIs), apply the right policies (RLS, masking), and tune performance (aggregates, caching) so consumers get what they need without risking cost spikes or data leaks.

Mental model

Think of your warehouse as a library:

• Collections: curated data products (facts/dimensions, features).
• Library cards: roles and permissions per reader.
• Reading rooms: interfaces (dashboards, SQL views, files, notebooks).
• Librarian rules: policies like row/column security and data contracts.
• Study guides: aggregates and semantic layers for faster answers.

Key patterns you should know

Interfaces

• SQL Views and Materialized Views: stable schemas, can enforce policies and pre-aggregation.
• Batch Extracts: periodic snapshots to object storage (e.g., Parquet/CSV) for downstream tools.
• Federated/Direct Query: live queries to curated views with caching and limits.
• Open Data Shares/Protocols: read-only, versioned access to tables for external consumers.
• APIs/Services: narrow, pre-validated slices for apps or operations.

Governance controls

• Row-Level Security (RLS): filter rows per role/user (e.g., region = 'EU').
• Column-Level Security (CLS)/Masking: hide or hash sensitive fields by role.
• Object Tagging and Policies: mark PII and auto-enforce rules.
• Data Contracts: define schema, freshness, SLAs, quality expectations.

Performance and cost

• Precompute heavy aggregations for dashboards.
• Sampling and downscaling for exploration; full scans only when needed.
• Workload isolation (separate compute/warehouses) to prevent noisy neighbors.
• Caching and extract refresh cadence aligned to business SLAs.

Worked examples

-- Aggregated table for dashboard
CREATE MATERIALIZED VIEW mv_sales_hourly AS
SELECT store_id, region, date_trunc('hour', ts) AS hour,
       SUM(amount) AS revenue, COUNT(*) AS orders
FROM fct_sales
GROUP BY 1,2,3;

-- RLS via view
CREATE VIEW v_sales_kpi AS
SELECT * FROM mv_sales_hourly
WHERE region = current_setting('app.region');

-- Sampled view for exploration
CREATE VIEW v_events_sample AS
SELECT * FROM fct_events TABLESAMPLE SYSTEM (5);

-- Usage policy: DS role has read on curated tables via isolated warehouse.

-- Publish curated columns only
CREATE VIEW v_products_share AS
SELECT product_id, name, category, price, last_updated
FROM dim_product
WHERE is_active = true;

-- Mask anything sensitive at source if present
-- (e.g., no emails or internal costs exposed)

Exercises

Do these to lock in the concepts. Quick test is available to everyone; log in to save your progress.

• Choose interface.
• Define modeling/aggregations.
• Describe governance (RLS/CLS, roles).
• Plan performance and refresh cadence.
• Monitoring and rollback plan.

CREATE VIEW v_orders_eu AS
SELECT
  CONCAT(REPEAT('*', GREATEST(LENGTH(customer_id)-4, 0)), RIGHT(customer_id, 4)) AS customer_id_masked,
  region,
  amount
FROM orders
WHERE region = 'EU';

Self-check checklist

• Did you enforce least privilege (only needed columns/rows)?
• Did you separate compute for different consumer groups?
• Did you define refresh cadence that matches business SLAs?
• Did you include monitoring and a rollback/disable plan?

Common mistakes

• Granting broad table access instead of views with RLS/CLS. Fix: expose only curated views; tag and mask PII.
• Relying only on live queries for BI. Fix: pre-aggregate and cache where possible.
• No workload isolation. Fix: separate warehouses/compute pools per consumer/team.
• Unbounded notebook scans. Fix: default to sampled views, quotas, and cost alerts.
• Sharing raw staging data with partners. Fix: publish stable, versioned curated datasets with contracts.

Practical projects

• Project 1: Publish a sales analytics data product with a data contract, RLS by region, and a BI-ready semantic view plus a daily materialized aggregate.
• Project 2: Create a partner share (or scheduled Parquet export) for a product catalog, excluding PII, with monitoring and revoke procedure.
• Project 3: Build a DS sandbox: sampled views, isolated compute, and a pathway to request full scans with approval.

Learning path

• Start: RLS/CLS basics, roles/permissions, curated views.
• Next: Materialized views and aggregate tables for BI performance.
• Then: Data contracts, versioning, and change management.
• Advanced: External data sharing, open protocols, and cross-domain governance.

Who this is for

• Data Engineers who publish warehouse datasets to BI, DS, and partners.
• Analytics Engineers defining semantic layers and aggregates.

Prerequisites

• SQL proficiency (views, joins, aggregations).
• Basics of dimensional modeling.
• Understanding of roles/permissions in your warehouse.

Next steps

• Instrument usage metrics (bytes scanned, concurrency, cache hits).
• Draft data contracts for your top 3 data products.
• Pilot RLS/CLS on a sensitive dataset and document results.

Mini challenge

Choose an access pattern for each scenario and justify briefly:

• Near-real-time operations dashboard with strict cost controls.
• Monthly partner report; they need only 6 columns and last 90 days.
• Company-wide self-serve metrics with sensitive user attributes.

Keep answers to 3–5 sentences each.