Query pipeline data with Query Studio and control who can access it

Select Query Studio (the SQL icon) in the left sidebar;

Select the + next to the session tabs to open a new session;

Open the SQL-capable Agent list, select the pipeline Shop orders, and then select Shop orders-source (SOURCE).

In the schema browser, expand the shop schema;

Select customers to list its columns with their types, primary key, and personal data tags;

Select the three-dot menu next to customers to see the table actions.

Select a column header, for example revenue, to sort the result by that column; select it again to reverse the order;

Select the magnifier icon above the grid and type a word, for example GBP, to keep only the rows that contain it, or select the funnel in a column header to tick the values of that column to keep;

Select the record icon at the right of the grid toolbar to read one row as a list of fields;

Select the Stats tab to see the minimum, maximum, and mean of numbers and the length and unique count of text for each column.

Run the aggregate query again;

Select the Chart tab;

Select Bar, Line, or Pie;

Pick the Label column and the Value column, for example country and revenue.

Select Export in the toolbar and choose Export CSV, Export XLSX, or Export JSON to download the result;

Or select Copy result and choose Copy as CSV, Copy as JSON, Copy as Markdown, Copy as HTML, Copy as XML, or Copy as INSERT… to put it on the clipboard.

Run the aggregate query;

Select Save query in the toolbar;

Enter Query name Revenue per country and currency, an optional Description, and an optional Folder;

Under Who can see this query, keep Personal (only me);

Select Save;

Select Saved queries to list your queries, and Query history to open the History tab.

Select the round launcher at the bottom right of the editor;

Read the panel Spark in Query Studio.

Open a new session and select Shop orders-target (TARGET);

Switch the Read-only toggle to Writable. A banner warns that destructive queries run against the database;

Optional: if you do not already have a table you can safely change, create a scratch one. Run each of these statements in turn:

CREATE SCHEMA IF NOT EXISTS shop_qs

CREATE TABLE IF NOT EXISTS shop_qs.notes (id integer PRIMARY KEY, topic text NOT NULL, note text, created_at timestamp DEFAULT now())

INSERT INTO shop_qs.notes (id, topic, note) VALUES (1, 'access', 'Policies are per user and per connection'), (2, 'export', 'CSV, XLSX and JSON downloads'), (3, 'history', 'Every run is recorded')

Run SELECT * FROM shop_qs.notes ORDER BY id;

Double-click the note cell of row 3, type Every run is recorded in History, and press Enter;

Select the eye icon in the grid toolbar to review the pending change, and then select Save changes.

Select the source session tab;

Run SELECT id, full_name, email, country, tier FROM shop.customers ORDER BY id;

Look at the full_name and email columns;

As a Super Admin, switch PII masked to PII visible and run the query again.

In Query Studio, select the shield icon Manage query data access next to the agent list;

In Applies to, keep User;

In User, select the user to configure, for example qs-viewer;

In Connection, select the database connection to configure, for example Shop orders-source.

With qs-viewer and Shop orders-source selected, set Connection mode to Restricted;

Select Add rule, enter Schema shop and Table (optional, * for all) orders, and switch Allow on;

Select Save policy;

In the second browser, sign in as qs-viewer and open Query Studio on Shop orders-source;

Expand the shop schema;

Run SELECT id, customer_id, amount, currency, status FROM shop.orders WHERE status = 'paid' ORDER BY amount DESC;

Run SELECT c.country, o.currency, SUM(o.amount) AS revenue FROM shop.orders o JOIN shop.customers c ON c.id = o.customer_id GROUP BY c.country, o.currency.

As qs-viewer, open a session on Shop orders-target (TARGET);

Switch Read-only to Writable;

Run UPDATE shop_qs.notes SET note = 'changed by qs-viewer' WHERE id = 1.

As qs-analyst (a Manager), open Query Studio on Shop orders-source;

Switch PII masked to PII visible;

Run SELECT id, full_name, email, country, tier FROM shop.customers ORDER BY id.

As a Super Admin, open Settings → Query data access, select qs-analyst and the connection Shop orders-source, choose the template MANAGER (read-only default), and select Apply template;

As qs-analyst, reload Query Studio, switch PII visible on, and run the query again.

As a Super Admin, select qs-analyst and the connection Shop orders-target;

Set Connection mode to Restricted;

Select Add rule, enter shop_qs in Schema and * (all tables) in the Table field, and switch Allow on;

If the matrix contains an empty PII unmask grants row, delete it, and select Save policy;

As qs-analyst, open a session on Shop orders-target (TARGET);

Run SELECT * FROM shop_qs.notes ORDER BY id;

Run SELECT * FROM shop.customers LIMIT 5.