Precisely compute
only what changed.

authorization.sql

DECLARE RECURSIVE VIEW group_can_read (
    group_id BIGINT NOT NULL,
    file_id BIGINT NOT NULL
);

-- A group can read a file it views, or anything inside a folder it can read
CREATE MATERIALIZED VIEW group_can_read AS
(SELECT group_id, file_id FROM group_file_viewer)
UNION ALL
(
  SELECT group_can_read.group_id, files.id AS file_id
  FROM group_can_read
  JOIN files ON group_can_read.file_id = files.parent_id
);

-- Users inherit their groups' access, unless banned
CREATE MATERIALIZED VIEW user_can_read AS
SELECT DISTINCT members.user_id, group_can_read.file_id
FROM members
JOIN group_can_read ON members.group_id = group_can_read.group_id
JOIN users ON users.id = members.user_id
WHERE NOT users.is_banned;

medallion.sql

-- Bronze: follow the raw Delta table as it changes
CREATE TABLE bronze_orders (
    order_id BIGINT NOT NULL PRIMARY KEY,
    user_id BIGINT,
    order_status VARCHAR,
    order_total DECIMAL(12, 2),
    created_at TIMESTAMP NOT NULL
) WITH ('connectors' = '[{"transport": {
    "name": "delta_table_input",
    "config": {"uri": "s3://lake/bronze_orders", "mode": "snapshot_and_follow"}}}]');

-- Gold: always current, recomputed only for orders that changed
CREATE MATERIALIZED VIEW gold_order_status_summary AS
SELECT
    order_status,
    COUNT(DISTINCT order_id) AS order_count,
    COALESCE(SUM(order_total), 0) AS total_revenue,
    COUNT(DISTINCT user_id) AS unique_customers
FROM bronze_orders
GROUP BY order_status;

fraud_features.sql

-- Spend features per card, over 1, 7 and 30 days
CREATE VIEW feature AS
SELECT
    t.cc_num,
    AVG(amt) OVER window_1_day AS avg_spend_pd,
    AVG(amt) OVER window_7_day AS avg_spend_pw,
    AVG(amt) OVER window_30_day AS avg_spend_pm,
    -- Number of transactions in the last 24 hours
    COUNT(*) OVER window_1_day AS trans_freq_24,
    amt, unix_time, zip, city_pop, is_fraud
FROM transaction AS t
JOIN demographics AS d ON t.cc_num = d.cc_num
WINDOW
  window_1_day AS (PARTITION BY t.cc_num ORDER BY unix_time
                   RANGE BETWEEN 86400 PRECEDING AND CURRENT ROW),
  window_7_day AS (PARTITION BY t.cc_num ORDER BY unix_time
                   RANGE BETWEEN 604800 PRECEDING AND CURRENT ROW),
  window_30_day AS (PARTITION BY t.cc_num ORDER BY unix_time
                    RANGE BETWEEN 2592000 PRECEDING AND CURRENT ROW);