Querying & Cleansing

Once your files are imported, the workspace offers two ways to work with them: the Query Editor for full DuckDB SQL, and the Cleanse tab for visual, pipeline-based cleanup.

The Query Editor

Every imported file is a table in an embedded DuckDB session, and the Query Editor is a full SQL console against it:

  • Syntax highlighting and schema-aware autocompletion — table and column names from your session complete as you type.
  • SQL formatting and one-keystroke execution.
  • Full DuckDB SQL — window functions, CTEs, GROUP BY ALL, the lot.

Joining datasets is just SQL — every import is a table, so combining a ragged export with a reference file is a JOIN away:

SELECT s.*, r.region_name
FROM raw_sales_data s
LEFT JOIN region_master r
  ON s.region_code = r.code;

The results grid

Query results land in a grid with pagination, search across rows, sortable columns, and instant download as CSV or Parquet (see Exporting Data).

Alongside the table, two more result tabs show the work behind it:

  • SQL — the exact statement Studio executed (useful for Cleanse pipelines, which generate their SQL for you).
  • Code — an equivalent Python snippet using the Datagrunt library and DuckDB, so anything you build interactively can graduate to a script.

The Cleanse tab

The Cleanse tab turns common cleanup work into a visual pipeline with a live preview of the transformed table. Each step is one operation:

Operation What it does
Deduplicate Remove duplicate rows.
Drop nulls Remove rows where a column is null.
Fill nulls Replace nulls in a column with a value — validated against the column’s type before it runs.
Rename Rename a column.
Cast Convert a column’s type (this is where “load losslessly, transform explicitly” pays off — text arrives as-is, and becomes dates and numbers when you say so).

Steps stack into a pipeline and the preview updates as you build. When it looks right, commit the result under Save Cleansed Table As — the cleansed data becomes a new table, and your original import stays untouched. The generated SQL for the whole pipeline is visible in the results’ SQL tab.

Quick single-column casts are also available directly from the dataset sidebar, without building a pipeline.

Importing DataAI PDF Extraction