Quenda

Quenda is a Windows PowerShell tool for preparing source data in DuckDB and running analyst-maintained SQL query packs against it. It is aimed at workflows such as loading Microsoft Unified Audit Log CSV exports, deduplicating overlapping rows, and exporting focused result CSVs for review.

Current version: v0.2.2

What It Does

• Manages a local DuckDB CLI binary under tools\duckdb\.
• Loads one CSV file, multiple CSV files, or all supported files in a non-recursive source folder into a DuckDB database.
• Supports using an existing .duckdb database instead of importing CSV again.
• Runs selected .sql query templates from the queries\ directory.
• Mirrors query subfolders into the output directory.
• Wraps each query in DuckDB COPY (...) TO ... so output handling is centralised.
• Writes timestamped logs with DuckDB paths, source paths, generated SQL paths, command hints, query durations, output paths, warnings, and errors.
• Supports GUI and headless CLI use.

Requirements

• Windows.
• Windows PowerShell 5.1 or PowerShell 7.
• DuckDB CLI, either installed by Quenda or already present at the configured path.
• Pester for running tests.

Quenda is built around Windows Forms, so the GUI is Windows-only.

Quick Start

From PowerShell:

cd C:\Tools\Quenda
.\Run-Quenda.ps1

If DuckDB is not installed yet, open the Setup tab and click Install.

To install DuckDB from the CLI:

.\Run-Quenda.ps1 -InstallDuckDb -NoGui

GUI Workflow

1. Open Run-Quenda.ps1.
2. On the Setup tab, check or install DuckDB.
3. On the Queries tab, refresh and select the queries to run.
4. On the Run tab, choose one load mode:
   • CSV file(s) or folder.
   • Existing DuckDB database.
5. Click Load data or Use database.
6. Choose an output folder.
7. Click Run queries.
8. Review the Run status box and Logs tab.

The Run tab also includes quick open buttons for the output directory and working directory cleanup. The Setup, Queries, and Logs tabs include Open directory buttons for their relevant folders.

CLI Examples

Run a full import and query pass:

.\Run-Quenda.ps1 -NoGui -SourcePath C:\Source\UAL\Lab2-1-UAL.csv -OutputPath .\output

Import data only:

.\Run-Quenda.ps1 -NoGui -Stage Import -SourcePath C:\Source\UAL

Run queries against an existing database:

.\Run-Quenda.ps1 -NoGui -Stage Query -DatabasePath .\working\quenda-20260524T072856Z.duckdb -OutputPath .\output

Run one selected query:

.\Run-Quenda.ps1 -NoGui -Stage Query `
  -DatabasePath .\working\analysis.duckdb `
  -QueryRelativePath 'UAL\Exchange\Summary-of-emails-sent-by-users.sql' `
  -OutputPath .\output

Clear generated working files:

.\Run-Quenda.ps1 -NoGui -ClearWorking

Query Files

Queries live under queries\ and may be organised in subfolders.

Each .sql file should contain a result-producing query, normally a SELECT. Quenda handles output creation by wrapping the query in DuckDB COPY.

Supported template token:

{{SOURCE_TABLE}}

Example:

SELECT
    UserIds,
    COUNT(*) AS EventCount
FROM {{SOURCE_TABLE}}
WHERE Operations = 'Send'
GROUP BY UserIds
ORDER BY EventCount DESC;

If that query is saved as:

queries\UAL\Exchange\Summary-of-emails-sent-by-users.sql

Quenda writes output to:

output\UAL\Exchange\Summary-of-emails-sent-by-users.csv

Data Loading

CSV imports use DuckDB read_csv_auto(...) into a raw table, then copy into the final table.

The default imported table is:

data

The raw import table is:

data_raw

When deduplication is enabled, the final table is created with:

SELECT DISTINCT * FROM data_raw

This means duplicates are exact row duplicates as DuckDB imported them. Quenda does not trim, normalise case, alter whitespace, or add source-file metadata columns.

Logs And Reproducibility

Logs are written under logs\ using timestamped file names.

The GUI creates a current session log when it starts and reuses that log for:

• CSV import.
• Existing database selection.
• Query execution.

Logs include generated .sql file paths and DuckDB command hints such as:

"C:\Tools\Quenda\tools\duckdb\duckdb.exe" -csv "C:\Tools\Quenda\working\analysis.duckdb" -c ".read 'C:/Tools/Quenda/working/sql/duckdb-example.sql'"

These hints are intended to help analysts reproduce Quenda's load/query steps in DuckDB CLI, notebooks, or other analysis environments.

Directory Layout

Run-Quenda.ps1              Launcher for GUI and CLI modes
config.json                 App defaults and processing settings
CHANGELOG.md                Release history
LICENSE                     Apache 2.0 license
modules\Quenda.Core.psm1    Testable non-GUI logic
modules\Quenda.Gui.psm1     Windows Forms GUI
assets\                     Quenda logo and application icon assets
queries\                    Analyst SQL query templates
tests\                      Pester tests and fixtures
logs\                       Runtime logs, ignored by git
output\                     Query outputs, ignored by git
working\                    Generated DuckDB databases and SQL scratch files, ignored by git
tools\duckdb\               Managed DuckDB CLI location, ignored by git

Configuration

Defaults live in config.json.

Important settings:

• duckdb.path: folder or executable path for DuckDB.
• paths.defaultQueryDirectory: default query directory.
• paths.defaultOutputDirectory: default output directory.
• paths.logDirectory: log directory.
• paths.workDirectory: working database and scratch SQL directory.
• processing.defaultSourceTableName: table name used by query templates.
• processing.deduplicate: default CSV deduplication behavior.
• processing.overwriteExistingOutput: whether outputs can overwrite existing files.
• logging.includeRawSql: whether raw query SQL is written to logs.

Testing

Run the focused test suite:

Invoke-Pester .\tests\Quenda.Core.Tests.ps1, .\tests\Run-Quenda.Tests.ps1

The current suite focuses on GUI-independent behavior: config loading, path resolution, source/query discovery, SQL generation, output mapping, DuckDB command construction, staged manifests, cleanup, and launcher behavior.

GitHub Notes

Generated evidence-adjacent files should not be committed.

The repository ignores:

• logs\*
• output\*
• working\*
• legacy work\
• tools\duckdb\*

The directory-local .gitignore files keep the runtime directories present without committing their contents.

Before publishing, review the workspace for accidental analyst data, logs, outputs, databases, and downloaded binaries.

License

Apache License 2.0. See LICENSE.