Use a workspace

How-to · Going further

A transformation is SQL you have finished writing. A workspace is where you write it: your own schema, holding a copy of the Storage tables you ask for, where a wrong query costs you nothing.

Before you start

Where you are you have tables in Storage and a query you are not sure about.

→

When you finish the same query, tested against real rows, ready to paste into a transformation.

You need

• A Keboola project. No project yet? Create or join a project.

• A table in Storage, such as staffing_outlook from Transform data if you are following the guide.

What each tab needs

• Prompt tab Kai, the Kai Agent button in the top bar, on supported stacks. A new Free Plan project has it from the start; elsewhere an organization admin turns it on once: Get started with Kai.
• UI tab a browser.
• >_ CLI / API tab kbagent, connected with an admin (master) token.

Prompt, UI and CLI / API: three ways to do the same task, not three steps. Pick one tab below; later pages open on it.

Most of it can be undone: if a step builds something else, say so in the same chat.

The Prompt tab reaches the tested query without a workspace at all, which is the honest shortest path; the other two build one. All three end with SQL you trust.

What a workspace is

A workspace is a schema of your own in the project’s backend, with the tables you choose copied into it. SQL workspaces follow the backend the project runs on, Snowflake or BigQuery; Python and R workspaces give you a notebook against the same data.

Nothing you do in one reaches Storage. The copy is taken at the moment you load it and stays as it was until you load it again, so dropping a table or rewriting a query twenty times leaves production untouched. The workspaces reference covers the rest, including the Python and R flavours.

Set it up

Prompt

Most of the time you do not need a workspace. You want one because you want to try SQL against your data, and Kai does that without creating anything: open Kai Agent in the top bar and ask the question you would have written the query for.

Prompt

Using out.c-Boolabean-staffing-outlook.staffing_outlook, which café and which day has the highest expected_units_per_person? Show me the SQL you ran.

Asking for the SQL matters. The answer is the thing you were developing, and it is what you paste into a transformation once it says what you meant.

Open a real workspace when the chat stops being the right tool:

• you are iterating for an hour rather than asking two questions;
• you want your own IDE or notebook against the data, not a chat window;
• you need Python or R libraries that a transformation does not have yet.

Creating the workspace is then the UI or CLI / API tab’s job.

Check: the answer names a café and a day, and the SQL it shows runs on its own.

UI

1. Go to Workspaces and click Create Workspace.

   

2. Pick the SQL workspace for your backend, Snowflake SQL Workspace or Google BigQuery Workspace. The list also offers Python and R, which give you a notebook instead of a SQL console.

   

3. Name it, describe it, and click Create Workspace. You can grant other project users access here, so a colleague can open the same workspace rather than build their own copy.

   

   Grant read-only access to all storage data is the alternative to loading tables one by one: the workspace sees everything in Storage without copying anything. Leave it off to follow the steps below.

4. A job creates the workspace. In the Workspaces list it shows as Creating until that job finishes, then as ready.

5. Click the workspace name to open it.

6. Set the table input mapping, exactly as a transformation does, then click Load Data. The tables are copied as they are at that moment; click Load Data again to refresh them.

7. Open the Connect menu. On a Snowflake backend it holds two ways in: a Connect button that signs you in to Snowflake’s own Snowsight interface, and, beside it, the key-pair credentials for a database client of your own.

   Both of those need a project with a dedicated Snowflake backend. A project on the shared Snowflake backend that Pay As You Go and multi-tenant projects use has no direct connection, and a Free Plan project, the kind this guide starts from, runs on BigQuery rather than Snowflake. In either case the SQL Editor is the way in: it runs the same queries against the same workspace, in the browser, on Snowflake and BigQuery alike.

CLI / API

Every click above is an API call underneath, and kbagent is the same calls from a terminal. It needs to be installed and pointed at your project once with kbagent project add, which stores the connection under an alias. The examples use docs-demo, substitute yours. Creating a workspace needs an admin (master) token.

Create the workspace

kbagent workspace create --project docs-demo --name "Boolabean SQL"

It prints the workspace ID; keep it, because every command below takes it. The backend is taken from the project unless you pass --backend.

Read-only access to all of Storage, the checkbox in the UI tab’s step 3, is on here, the other way round from the dialog. That is the --read-only default; --no-read-only turns it off. It does not stop you loading tables or writing in your own schema: it decides whether the workspace can also see the rest of Storage without copying it.

The private key is printed once

On a Snowflake backend the workspace authenticates with a key pair, and create prints the private key with the warning that it cannot be retrieved later. Redirect it into a file you are willing to hold, or plan to throw the workspace away when you are done. workspace detail will tell you the host, database, schema and user afterwards, but never the key.

Load the tables

kbagent workspace load --project docs-demo --workspace-id <workspace-id> --tables out.c-Boolabean-staffing-outlook.staffing_outlook

Repeat --tables for each one. The load clears the workspace first unless you add --preserve, which is the difference between refreshing a copy and adding to it.

Run your SQL

On a Snowflake workspace, for a table built with the SQL on Transform data:

kbagent workspace query --project docs-demo --workspace-id <workspace-id> --sql 'SELECT "store_name", "date", "expected_units_per_person" FROM "staffing_outlook" ORDER BY "expected_units_per_person" DESC LIMIT 5'

For a table Kai built from that page’s Prompt tab, the same query with the column names in upper case:

kbagent workspace query --project docs-demo --workspace-id <workspace-id> --sql 'SELECT "STORE_NAME", "DATE", "EXPECTED_UNITS_PER_PERSON" FROM "staffing_outlook" ORDER BY "EXPECTED_UNITS_PER_PERSON" DESC LIMIT 5'

On Snowflake, case is not a detail

Double quotes mean exactly this name; without them Snowflake reads any name as upper case. The table is stored in lower case, the way the output mapping wrote it. The columns depend on the SQL that built the table: the SQL on Transform data quotes its aliases, so the names stay lower case, while the SQL Kai wrote from that page’s Prompt tab left them unquoted in our runs, and Snowflake upper-cased them.

You write	What happens
FROM staffing_outlook	fails; looks for STAFFING_OUTLOOK, the table is stored lower case
FROM "staffing_outlook"	works
SELECT "store_name"	works on a table built with the SQL on Transform data; on one Kai built, fails with invalid identifier
SELECT STORE_NAME or SELECT "STORE_NAME"	works on a table Kai built; fails on one built with the SQL on Transform data

When a name refuses to resolve, print one row and write the names the way its header spells them:

kbagent workspace query --project docs-demo --workspace-id <workspace-id> --sql 'SELECT * FROM "staffing_outlook" LIMIT 1'

On a BigQuery workspace, which is what a Free Plan project creates, double quotes make a string literal rather than a name, so write the names plain, with date in backticks as on Explore your data:

kbagent workspace query --project docs-demo --workspace-id <workspace-id> --sql 'SELECT store_name, `date`, expected_units_per_person FROM staffing_outlook ORDER BY expected_units_per_person DESC LIMIT 5'

--file takes a .sql file instead, which is the shape you want once the query is longer than a line, and --full fetches the whole result rather than the first page.

Look at it, then throw it away

kbagent workspace detail --project docs-demo --workspace-id <workspace-id>

kbagent workspace delete --project docs-demo --workspace-id <workspace-id>

kbagent workspace from-transformation --project docs-demo --component-id … --config-id … is the shortcut for the other direction: it builds a workspace loaded with exactly what a transformation reads. It needs that configuration to have an input mapping, and one created by kbagent transformation create does not have one yet; see Transform data.

Check it worked

Coming from the Prompt tab? You have no workspace to check. Your answer arrived with the SQL that produced it, which is what the other two tabs spend a workspace getting to. You are done.

Your query returns rows, and the same query returns the same rows after a reload: the copy does not move under you. The workspace itself shows up in Workspaces, and from a terminal in kbagent workspace list --project docs-demo, with its ID, its backend, and whether it is read-only.

If it goes wrong

• A name will not resolve, but the table is loaded. Case. Object '<NAME>' does not exist means an unquoted name was upper-cased and the stored one is not; invalid identifier "<name>" means you quoted a name that is stored in a different case. The table above shows both.
• The workspace is empty after loading. Loading again replaces what was there rather than adding to it, in the UI’s Load Data and in workspace load alike. Pass --preserve when you meant to add a table.
• You cannot find the password. Workspace credentials are shown once, when they are created, and are not readable back. Reissue them from the Connect dialog, or with kbagent workspace password.
• Old workspaces you forgot about. They are all in Workspaces; from a terminal, kbagent workspace list --project docs-demo --orphaned finds the ones whose configuration is already gone, and kbagent workspace gc --dry-run says what it would clean up before it does anything.

Going further

• Transform data is where the query you just tested belongs.
• Workspaces covers the Python and R flavours, access for other users, and read-only access to all of Storage.

Next: Ad-hoc data analysis →