Why Do We Need Semantic Views? Avoiding Subtle Mistakes in Complex Calculations

-- This will duplicate results and give an incorrect SALE_TOTAL
SELECT CLIENT_LASTNAME, CLIENT_FIRST_NAME, SUM(SALE_QTY), SUM(SALE_TOTAL)
FROM client join sale on (CLIENT_ID = CLIENT_ID) 
join SALE_MODEL on (SALE_ID = SALE_ID)
GROUP BY 1, 2

WITH sale_agg AS ( 
-- Aggregate sale totals by client first 
SELECT CLIENT_ID, 
             SUM(SALE_TOTAL) AS TOTAL_AMOUNT 
FROM sale 
GROUP BY CLIENT_ID 
), model_agg AS ( 
-- Aggregate quantity by client (joining sale to get the CLIENT_ID) 
-- This keeps the qty calculation separate from the sale amount 
SELECT s.CLIENT_ID, 
            SUM(sm.SALE_QTY) AS TOTAL_QTY 
FROM SALE_MODEL sm 
     JOIN sale s ON sm.SALE_ID = s.SALE_ID 
GROUP BY s.CLIENT_ID 
) 
SELECT c.CLIENT_LASTNAME, 
  c.CLIENT_FIRST_NAME, 
  ma.TOTAL_QTY, 
  sa.TOTAL_AMOUNT 
FROM client c 
LEFT JOIN sale_agg sa ON c.CLIENT_ID = sa.CLIENT_ID 
LEFT JOIN model_agg ma ON c.CLIENT_ID = ma.CLIENT_ID;

<INCLUDE PREVIOUS EXAMPLE SUB QUERIES>
WITH client_metrics AS ( 
  -- This is the same logic as the query above 
  SELECT c.CLIENT_ID, 
         (SUM(sa.TOTAL_AMOUNT) / NULLIF(SUM(ma.TOTAL_QTY), 0)) AS AVG_PRICE 
  FROM client c 
      JOIN sale_agg sa ON c.CLIENT_ID = sa.CLIENT_ID 
      JOIN model_agg ma ON c.CLIENT_ID = ma.CLIENT_ID 
  GROUP BY c.CLIENT_ID 
) 
SELECT MIN(AVG_PRICE) AS HIGHEST_CLIENT_AVG_PRICE 
FROM client_metrics;

-- THE INCORRECT JOIN (The Chasm Trap)
SELECT 
    s.SERVICE, 
    SUM(il.NB_GUESTS) AS TOTAL_PAST_GUESTS, 
    SUM(rl.FUTURE_GUESTS) AS TOTAL_FUTURE_GUESTS
FROM SERVICE s
JOIN INVOICE_LINE il ON s.SERVICE_ID = il.SERVICE_ID
JOIN RESERVATION_LINE rl ON s.SERVICE_ID = rl.SERVICE_ID
WHERE s.SERVICE = 'Guided Tour'
GROUP BY 1;

WITH past_usage AS (
    -- Aggregate guests to the grain of service_id
    SELECT SERVICE_ID, SUM(NB_GUESTS) as TOTAL_PAST
    FROM INVOICE_LINE
    GROUP BY SERVICE_ID
),
future_usage AS (
    -- Aggregate future guests to the grain of service_id
    SELECT SERVICE_ID, SUM(FUTURE_GUESTS) as TOTAL_FUTURE
    FROM RESERVATION_LINE
    GROUP BY SERVICE_ID
)
-- Final Join now has a single row per service_id for each side
SELECT 
    s.SERVICE,
    COALESCE(p.TOTAL_PAST, 0) as TOTAL_PAST_GUESTS,
    COALESCE(f.TOTAL_FUTURE, 0) as TOTAL_FUTURE_GUESTS
FROM SERVICE s
LEFT JOIN past_usage p ON s.SERVICE_ID = p.SERVICE_ID
LEFT JOIN future_usage f ON s.SERVICE_ID = f.SERVICE_ID;

CREATE OR REPLACE SEMANTIC VIEW sales_analysis_view
TABLES (
    client AS client
        PRIMARY KEY (client_id),
    sale AS sale
        PRIMARY KEY (sale_id),
    sale_model AS sale_model
        PRIMARY KEY (sale_id, model_id)
)
RELATIONSHIPS (
    sale_to_client AS
        sale (client_id) REFERENCES client,
    sale_model_to_sale AS
        sale_model (sale_id) REFERENCES sale
)
DIMENSIONS (
    client.last_name AS client_lastname,
    client.first_name AS client_first_name
)
METRICS (
    sale.total_amount AS SUM(sale_total),
    sale_model.total_qty AS SUM(sale_qty)
)
COMMENT = 'Semantic view for demonstrating blog post';

SELECT first_name,         last_name, 
       AGG(total_amount) AS correct_sale_total, 
       AGG(total_qty) AS total_quantity 
FROM sales_analysis_view 
GROUP BY first_name, last_name;

SELECT * FROM SEMANTIC_VIEW(
    sales_analysis_view 
    METRICS total_amount, total_qty 
    DIMENSIONS last_name, first_name
);

WITH state_sales_2002 AS (
    SELECT
      ca.ca_state,
      COALESCE(SUM(CASE WHEN d1.d_year = 2002 THEN cs.cs_ext_sales_price END), 0) + COALESCE(SUM(CASE WHEN d2.d_year = 2002 THEN ws.ws_ext_sales_price END), 0) AS total_sales
    FROM AUTOSQL_DATASET_TPCDS.TPCDS.CUSTOMER AS c
    JOIN AUTOSQL_DATASET_TPCDS.TPCDS.CUSTOMER_ADDRESS AS ca
      ON c.c_current_addr_sk = ca.ca_address_sk
    LEFT JOIN AUTOSQL_DATASET_TPCDS.TPCDS.CATALOG_SALES AS cs
      ON c.c_customer_sk = cs.cs_bill_customer_sk
    LEFT JOIN AUTOSQL_DATASET_TPCDS.TPCDS.DATE_DIM AS d1
      ON cs.cs_sold_date_sk = d1.d_date_sk
    LEFT JOIN AUTOSQL_DATASET_TPCDS.TPCDS.WEB_SALES AS ws
      ON c.c_customer_sk = ws.ws_bill_customer_sk
    LEFT JOIN AUTOSQL_DATASET_TPCDS.TPCDS.DATE_DIM AS d2
      ON ws.ws_sold_date_sk = d2.d_date_sk
    GROUP BY
      ca.ca_state
    HAVING
      total_sales > 500000000
), qualifying_states AS (
    SELECT
      ca_state
    FROM state_sales_2002
)
SELECT
  ca.ca_state AS state,
  COUNT(DISTINCT c.c_customer_sk) AS customer_count
FROM AUTOSQL_DATASET_TPCDS.TPCDS.CUSTOMER AS c
JOIN AUTOSQL_DATASET_TPCDS.TPCDS.CUSTOMER_ADDRESS AS ca
  ON c.c_current_addr_sk = ca.ca_address_sk
WHERE
  ca.ca_state IN (
      SELECT
        ca_state
      FROM qualifying_states
  )
  AND NOT ca.ca_state IS NULL /* Exclude the null/empty state */
GROUP BY
  ca.ca_state
ORDER BY
  customer_count ASC
LIMIT 5000

-- 1. Client Table (The "One" side)
CREATE OR REPLACE TABLE client (
    client_id INT PRIMARY KEY,
    client_first_name STRING,
    client_lastname STRING
);

-- 2. Sale Table (The "One" side of the sale_model relationship)
CREATE OR REPLACE TABLE sale (
    sale_id INT PRIMARY KEY,
    client_id INT REFERENCES client(client_id),
    sale_total DECIMAL(10,2) -- This is the metric that gets "trapped"
);

-- 3. Sale Model Table (The "Many" side)
CREATE OR REPLACE TABLE sale_model (
    sale_id INT REFERENCES sale(sale_id),
    model_id INT,
    sale_qty INT,
    PRIMARY KEY (sale_id, model_id)
);

INSERT INTO client (client_id, client_first_name, client_lastname) 
VALUES (1, 'John', 'Doe');

-- John Doe has ONE sale worth $100.00
INSERT INTO sale (sale_id, client_id, sale_total) 
VALUES (101, 1, 100.00);

-- That ONE sale contains TWO different items (Models)
-- Total quantity = 3 (1 + 2)
INSERT INTO sale_model (sale_id, model_id, sale_qty) 
VALUES 
    (101, 501, 1), -- Item 1
    (101, 502, 2); -- Item 2


-- Semantic View that models the table hierarchy
CREATE OR REPLACE SEMANTIC VIEW sales_analysis_view
TABLES (
    client AS client
        PRIMARY KEY (client_id),
    sale AS sale
        PRIMARY KEY (sale_id),
    sale_model AS sale_model
        PRIMARY KEY (sale_id, model_id)
)
RELATIONSHIPS (
    sale_to_client AS
        sale (client_id) REFERENCES client,
    sale_model_to_sale AS
        sale_model (sale_id) REFERENCES sale
)
DIMENSIONS (
    client.last_name AS client_lastname,
    client.first_name AS client_first_name
)
METRICS (
    sale.total_amount AS SUM(sale_total),
    sale_model.total_qty AS SUM(sale_qty)
)
COMMENT = 'Semantic view for sales analysis';

-- Correct SQL that queries over the semantic view, sales_analysis_view, as if it were a regular view
SELECT first_name, 
       last_name, 
       AGG(total_amount) AS correct_sale_total, 
       AGG(total_qty) AS total_quantity 
FROM sales_analysis_view 
GROUP BY first_name, last_name;

-- Correct SQL that queries over the semantic view, sales_analysis_view, using the SEMANTIC_VIEW table function
SELECT * FROM SEMANTIC_VIEW(
    sales_analysis_view 
    METRICS total_amount, total_qty 
    DIMENSIONS last_name, first_name
);

-- THE FAN TRAP (Incorrect Result)
-- This returns $200.00 for John Doe because of the "Join Explosion"
SELECT 
    c.client_first_name,
    c.client_lastname,
    SUM(s.sale_total) as incorrect_total_sum,
    SUM(sm.sale_qty) as total_qty
FROM client c
JOIN sale s ON c.client_id = s.client_id
JOIN sale_model sm ON s.sale_id = sm.sale_id
GROUP BY 1, 2;

-- Using a union to make the results easier to see:)
SELECT 'Correct result using SQL' as label, 
       first_name, 
       last_name, 
       AGG(total_amount) AS sale_total, 
       AGG(total_qty) AS total_quantity 
FROM sales_analysis_view 
GROUP BY first_name, last_name
UNION ALL BY NAME
SELECT 'Correct result using SEMANTIC_VIEW' as label, * FROM SEMANTIC_VIEW(
    sales_analysis_view 
    METRICS total_amount AS sale_total, total_qty  AS total_quantity
    DIMENSIONS last_name, first_name
)
UNION ALL BY NAME
SELECT 
    'Incorrect result because of fan trap' as label,
    c.client_first_name as first_name,
    c.client_lastname as last_name,
    SUM(s.sale_total) as sale_total,
    SUM(sm.sale_qty) as total_quantity
FROM client c
JOIN sale s ON c.client_id = s.client_id
JOIN sale_model sm ON s.sale_id = sm.sale_id
GROUP BY 1, 2, 3
;