Week 132 – Basic

“A little knowledge is a dangerous thing”—a quote attributed to many, yet still undeniably true. We’ve all witnessed situations where people jump to conclusions based on partial information rather than considering the full picture.

Today, we’re tackling this issue by enforcing restrictions that prevent users from viewing only a fragment of the data.

Join Policies to the Rescue

Join Policies in Snowflake allow us to enforce joins, ensuring that users cannot retrieve data from a table unless they join it with another table. And that’s exactly what we’re going to implement in today’s challenge.

The Challenge
Step 1: Create and Populate the Tables

CREATE OR REPLACE TABLE join_table (
col1 INT,
col2 VARCHAR,
col3 NUMBER )
JOIN POLICY my_join_policy
;

INSERT INTO join_table (col1, col2, col3) VALUES (1, 'Sample Data', 123.45);

CREATE OR REPLACE TABLE join_table_details (col1 INT,  col4 VARCHAR,  col5 DATE);

INSERT INTO join_table_details (col1, col4, col5) VALUES (1, 'Additional Info', '2025-03-07');

Step 2: Enforce Join Restriction

Your task is to configure the join policy so that only the ACCOUNTADMIN role can query join_table directly.

Any other role attempting to query join_table without a join (e.g., with SELECT * FROM join_table) should encounter an error message instead of retrieving data.

Goal

If anyone besides ACCOUNTADMIN runs a direct query on join_table, the expected result should be an error message similar to: