Update to MarcoPolo

{
  "name": "query-and-analyze",
  "description": "Queries connections, joins results across sources through DuckDB, and analyzes workspace data. Use this skill whenever the user wants to look at, count, summarize, filter, group, join, aggregate, compare, or analyze data, or when exploring a connection's schema or doing follow-up analysis on a previous query.",
  "included_files": [],
  "skill_md_contents": "---\nname: query-and-analyze\ndescription: Queries connections, joins results across sources through DuckDB, and analyzes workspace data. Use this skill whenever the user wants to look at, count, summarize, filter, group, join, aggregate, compare, or analyze data, or when exploring a connection's schema or doing follow-up analysis on a previous query.\n---\n\n# Query and analyze data\n\nUse this skill for all agent-side analytics: query authoring, schema exploration,\nDuckDB materialization, joins across sources, and file analysis.\n\n**Tool selection:**\n- `workspace_shell(\"connection query ...\")` is the correct tool for all agent\n  analytics. The full result is always materialized into DuckDB; `--sample-rows`\n  only controls how many rows come into the agent's context window.\n- `data_query` is for generated code that re-queries live data at view or load\n  time: Remote Artifacts, external web apps, scheduled scripts. Do not use it\n  for agent analytics or one-off snapshot visualizations — embed those inline.\n\n**Session compatibility:** Some sessions (e.g. ChatGPT) expose only\n`workspace_shell` and do not have `connections_list` or `data_query`. Check\nwhich tools are available before deciding on a path. `workspace_shell` works\nin every session and is the primary analytics tool in all cases.\n\n## Required workflow — follow every step\n\n### Step 1 — Orient in the workspace\n\n```text\nworkspace_shell(\"cat /workspace/RULES.md\")\n```\n\nThis is the workspace's long-term memory. Read it before touching any connection.\n\n### Step 2 — Discover connections\n\nUse `connections_list` if the current session exposes it. Otherwise:\n\n```text\nworkspace_shell(\"connection list --json\")\n```\n\nPick the connection(s) needed. Confirm `query` appears in each connection's\n`capabilities`.\n\n### Step 3 — Load connection context\n\nFor each connection:\n\n```text\nworkspace_shell(\"cat connections/<name>/README.md connections/<name>/SYNTAX.md connections/<name>/RULES.md\")\n```\n\n`RULES.md` is long-term memory for that connection — field quirks, naming\nconventions, reliable query patterns, and known limitations accumulated from\nprior sessions. Read it before authoring any query.\n\n### Step 4 — Inspect schema and existing queries\n\n```text\nworkspace_shell(\"ls connections/<name>/queries/ connections/<name>/metadata/\")\n```\n\nIf `describe` is in capabilities and metadata is missing or stale:\n\n```text\nworkspace_shell(\"connection describe <name> --json\")\n```\n\nPrefer adapting an existing query over writing from scratch.\n\n### Step 5 — Author a query file\n\nWrite to a file under `connections/<name>/queries/`. Use a business-readable\nfilename. The file extension and query format are determined by the connection\ntype — use `SYNTAX.md` (loaded in Step 3) as the authoritative reference for\nboth. Do not default to `.sql` unless SYNTAX.md confirms the connection uses SQL.\n\n```text\nworkspace_shell(\"\"\"cat > connections/<name>/queries/<filename>.<ext> <<'EOF'\n<query content per SYNTAX.md>\nEOF\"\"\")\n```\n\n**Common patterns by connection type:**\n\n| Connection type | Extension | Query format |\n|---|---|---|\n| SQL databases (Snowflake, BigQuery, DuckDB) | `.sql` | Standard SQL `SELECT` |\n| Salesforce (SOQL) | `.json` | `{\"soql\": \"SELECT ... FROM Object WHERE ...\"}` |\n| Object storage (S3, SFTP) | `.json` | Path or glob pattern per SYNTAX.md |\n| Document storage (Google Drive, OneDrive) | `.json` | File path or search spec per SYNTAX.md |\n| Other SaaS APIs | `.json` | Endpoint + parameters object per SYNTAX.md |\n\nIf SYNTAX.md does not specify an extension, default to `.json` for API-based\nconnections and `.sql` for SQL-native connections.\n\n### Step 6 — Execute the query\n\n```text\nworkspace_shell(\"connection query <name> --file connections/<name>/queries/<filename>.<ext> --sample-rows 10 --json\")\n```\n\nThe full result is always materialized into DuckDB. `--sample-rows <n>` controls\nhow many rows appear in `preview` (default 10; omitting it truncates silently).\nPass `--sample-rows -1` when you need all rows in the payload. For large result\nsets prefer a DuckDB follow-up query instead. See the `using-connection-cli`\nskill for full flag reference and timeout guidance.\n\n`preview` in the response envelope is a JSON-encoded **string** — call\n`json.loads(resp[\"preview\"])` to get `list[dict]`. `rows` in the envelope is an\nint count, not a record list. For group-bys, totals, or joins, skip parsing\n`preview` and run a DuckDB query over `relation_name` (Step 7) instead.\n\n### Step 7 — Analyze and join through DuckDB\n\nEach upstream query materializes as a `relation_name` in DuckDB. Use it for\naggregations, joins across connections, and transformations:\n\n```text\nworkspace_shell(\"connection query DUCKDB --file connections/DUCKDB/queries/<file>.sql --json\")\n```\n\nSave reusable joins in `connections/DUCKDB/queries/`.\n\n### Step 8 — Export large results for the user\n\nWhen the user needs to retrieve a large result set, export from DuckDB to CSV\nin `/workspace/data/downloads/` for pickup from the MarcoPolo web UI:\n\n```sql\nCOPY (SELECT * FROM <relation_name>) TO '/workspace/data/downloads/<filename>.csv' (HEADER, DELIMITER ',');\n```\n\nRun via:\n\n```text\nworkspace_shell(\"connection query DUCKDB --file connections/DUCKDB/queries/export.sql --json\")\n```\n\n### Step 9 — Offer to save learnings\n\nAfter answering the user's question, offer to save any new facts discovered —\nschema quirks, reliable query patterns, field naming conventions, known\nlimitations — to the appropriate RULES.md:\n\n- Connection-specific: `connections/<name>/RULES.md`\n- Workspace-wide: `/workspace/RULES.md`\n\nAsk the user to confirm before writing. Saving these enriches the context layer\nfor future sessions.\n\n## Join across connections through DuckDB\n\nDuckDB is the in-workspace analytical connection.\n\n1. Run each upstream `connection query` first and note each `relation_name`.\n2. Write a DuckDB SQL file that joins or transforms those relations.\n3. Execute through DuckDB.\n\n   ```text\n   workspace_shell(\"connection query DUCKDB --file connections/DUCKDB/queries/<file>.sql --json\")\n   ```\n\n## Work with files in the remote workspace\n\n- User-provided files belong in `data/uploads/`.\n- Files fetched via `connection download` land in `data/downloads/`.\n- Use `data/databases/` for database files when needed.\n\nDuckDB can read CSV, Parquet, and JSON files directly:\n\n```text\nworkspace_shell(\"\"\"cat > connections/DUCKDB/queries/<file>.sql <<'SQL'\nSELECT * FROM read_csv_auto('data/uploads/<file>.csv') LIMIT 100\nSQL\"\"\")\nworkspace_shell(\"connection query DUCKDB --file connections/DUCKDB/queries/<file>.sql --json\")\n```\n\n## Common pitfalls\n\n- The `connection` CLI only exists inside the remote workspace — always use\n  `workspace_shell` for workspace commands.\n- Trust `connection list --json` or `connections_list` for capabilities.\n- Query through named files, not inline SQL.\n- **`--sample-rows` defaults to 10 and silently truncates.** Omitting it does\n  not return all rows — it caps `preview` at 10. If `row_count` exceeds the\n  length of `preview`, the result is truncated; use a higher `--sample-rows`\n  value to get more rows, or `--sample-rows -1` to get all rows in the payload.\n  The full dataset is always in DuckDB regardless of this flag.\n- Query file paths in `--file` resolve from `/workspace`, not from your current\n  directory — always include the `connections/<name>/` prefix. A bare\n  `queries/<file>` resolves to `/workspace/queries/<file>` and fails with\n  \"No such file or directory\" even if you just created the file via\n  `cd <connection-dir> && cat > queries/<file>`.\n\n## Pointers\n\n- adding a connection or installing a demo → `setup-connection`\n- per-verb flag reference → `using-connection-cli`\n- workspace layout → `using-marcopolo-workspace`\n- visualizing results → `build-dashboard`\n- building a scheduled workflow → `build-scheduled-pipeline`\n- managing an existing recurring run → `setup-automation`\n"
}