Data Lake and Warehouse Architecture Questions

-- Pre-aggregate & dedupe staging into a transient view/table
WITH staging_daily AS (
  SELECT
    event_date,
    SUM(revenue)           AS total_revenue,
    COUNT(DISTINCT user_id) AS user_count
  FROM staging_events
  -- optional: filter only the date(s) you're loading, e.g. event_date = '2025-12-05'
  GROUP BY event_date
)

MERGE INTO daily_summary AS tgt
USING staging_daily AS src
  ON tgt.event_date = src.event_date
WHEN MATCHED THEN
  UPDATE SET
    total_revenue = src.total_revenue,
    user_count    = src.user_count
WHEN NOT MATCHED THEN
  INSERT (event_date, total_revenue, user_count)
  VALUES (src.event_date, src.total_revenue, src.user_count);

-- mapping table
CREATE TABLE dbo.user_region(user_principal_name nvarchar(256), region_id int);

-- inline table-valued function returning allowed regions for current user
CREATE FUNCTION dbo.fn_allowed_regions()
RETURNS TABLE
AS
RETURN
  SELECT region_id
  FROM dbo.user_region
  WHERE user_principal_name = ORIGINAL_LOGIN(); -- or SUSER_SNAME() / SESSION_CONTEXT key set by gateway

CREATE VIEW vw_sales_secure AS
SELECT s.*
FROM dbo.sales s
JOIN dbo.fn_allowed_regions() r ON s.region_id = r.region_id;