MCP and CLI in Tandem: Best Practices for AI Data Analysis Workflows

When you explore data with an AI assistant, you often want to follow up, double-check calculations, and try again from a different angle; and once you find a useful query, you want to save it so you can rerun it directly the next time you export data. MCP (Model Context Protocol) and CLI (Command Line Interface) happen to satisfy these two needs respectively. This article draws on the real-world workflow of the BigdataSight author to break down how the two work together and offers reusable practices.

Why MCP and CLI Are Not an Either/Or Choice

MCP lets AI clients discover tools and context resources, making it well suited to exploratory analysis within a conversation; CLI, on the other hand, turns validated queries into repeatable commands, making it well suited for scripting and daily processes. The author maintains both interfaces, with the core principle: use MCP for exploration, use CLI for execution.

This division of labor isn't new, but many people tend to go to extremes in practice—either manually copying SQL in a chat window the whole time, or writing scripts from the start and ending up with debugging costs that are too high. The truly efficient approach lets the two hand off to each other: first use MCP to get a clear picture of the data, then hand the confirmed logic over to CLI to solidify.

MCP (Model Context Protocol) is a standardized protocol proposed and open-sourced by Anthropic at the end of 2024, used to let AI clients (such as Claude Desktop and various IDE assistants) connect to external tools, data sources, and context resources in a unified way. Its core abstractions include tools (callable actions, such as executing a query), resources (readable context, such as schemas and file contents), and prompts (reusable prompt templates). A server only needs to implement the protocol once, and any compatible client can discover and use these capabilities without separate integration for each client. This is exactly the meaning of "AI clients can discover tools and context resources" in the article—during the exploration phase you don't need to hand-write API calls; the assistant can read column structures, preview data, and compose queries on its own.

CLI (Command Line Interface) here specifically refers to wrapping data queries as executable commands (such as bds). Unlike MCP's interactive, session-based calls, CLI's input is explicit parameters or JSON requests, and its output is a standard stream that can be redirected to files, making it naturally suited to shell scripts, cron jobs, or CI pipelines. The key characteristics of CLI are exit status codes and deterministic replay: the same command can be run repeatedly on the same data, which facilitates automated validation. MCP handles "asking," CLI handles "running," and the two target different stages of the workflow rather than competing.

Example: Finding the Service with the Most Failures

Suppose there is an execution log runs.csv from multiple services, and the goal is to count yesterday's (October 18, UTC) failures and failure rates for each service, counting only the succeeded and failed statuses, sorted by failure count.

The data is as follows:

run_id,service,status,finished_at
r1,billing,failed,2026-10-18 09:00:00
r2,billing,succeeded,2026-10-18 10:00:00
r3,billing,failed,2026-10-18 11:00:00
r4,search,succeeded,2026-10-18 12:00:00
r5,search,failed,2026-10-18 13:00:00
r6,ingest,succeeded,2026-10-18 14:00:00
r7,billing,cancelled,2026-10-18 15:00:00
r8,billing,failed,2026-10-19 00:00:00

Watch out for two pitfalls: r7 is in the cancelled status and should not be counted in the denominator; r8 occurs at midnight on October 19 and falls outside the statistical window. Details like these are easy to miss by eyeballing, which is exactly where the value of the MCP exploration phase lies.

Use MCP to Get a Clear Picture of the Schema First

After connecting to an AI client via MCP, you can directly ask the assistant to inspect the file's column names, preview a few rows, and then generate a query. The author's prompt was roughly: first show the schema and a preview, then count successes and failures for October 18 by UTC time, return the completed count, failed count, and failure rate, sort by failure count, and show the SQL before executing.

The key to this step is look at the schema before writing the query. If timestamps are stored as text, or the status field uses error instead of failed, the assistant can adjust the SQL based on the actual situation. The query obtained after confirmation is as follows:

SELECT
    service,
    COUNT(*) AS finished_runs,
    SUM(CASE WHEN status = 'failed' THEN 1 ELSE 0 END) AS failed_runs,
    ROUND(
        100.0 * SUM(CASE WHEN status = 'failed' THEN 1 ELSE 0 END)
        / COUNT(*),
        2
    ) AS failure_pct
FROM runs
WHERE finished_at >= TIMESTAMP '2026-10-18 00:00:00'
  AND finished_at < TIMESTAMP '2026-10-19 00:00:00'
  AND status IN ('succeeded', 'failed')
GROUP BY service
ORDER BY failed_runs DESC, service;

The results are clear: billing completed 3 times with 2 failures, a failure rate of 66.67%; search completed 2 times with 1 failure, a failure rate of 50%; ingest completed 1 time with zero failures. The cancelled r7 and the cross-day r8 are both correctly excluded.

Turn the Query into a CLI Command

After the SQL is validated, the author saves it as a command in BigdataSight 1.2: open the data source, select that source in "Settings → External Access" and enable "Structure and data" access permission, then copy the command via "Run → Copy as Command…", where the dialog lets you choose the shell, the result row limit, and the timeout.

After installing the bds tool via "Command Line → Set Up bds…", save the copied command as daily-failures.sh, and running it is straightforward:

sh ./daily-failures.sh > daily-failures-result.json

The generated command automatically includes the data source selection and query request, and the output file is a JSON-format query response. Two operational details are easy to overlook here: first, check the command's exit status before using the results; second, > overwrites an existing file, so if you want to keep historical results, use a new file name each time.

Going further, you can have the AI assistant extract the date range and data source into parameters to make a reusable script. But the author cautions that before putting the script into production, you should test-run it on a known date to confirm the logic is correct before incorporating it into your daily workflow.

Exit status is the integer returned to the caller after each command in the shell, where 0 means success and non-zero means failure, readable via $?. The article emphasizes "check the command's exit status before using the results" because the redirect operator > creates or truncates the target file before the command runs: if the query fails due to permissions, timeout, or a syntax error, the output file will still exist, but its contents may be empty or contain an error message. If you hand that file to downstream processing without checking the status code, you'll get silent errors that are hard to trace. A safer approach is sh ./daily-failures.sh > out.json && echo done, or explicitly check if [ $? -ne 0 ]; then ....

Shell redirection and quoting are also common pitfalls when scripting: > overwrites a file, >> appends; JSON requests contain double quotes, and wrapping them in single quotes or passing them via printf '%s\n' avoids premature shell interpretation. The article feeds the request to bds jobs get --request - through the pipe |, where - by convention means "read from standard input," a common pattern for CLI tools handling long arguments or structured input.

Task Handoff: Start at One Entry Point, Retrieve at Another

For long-running queries or exports, BigdataSight 1.2 Pro supports independent jobs. The approach is to have the AI client submit the operation as a job, keep the returned job ID, then close the MCP connection and later check progress from the terminal.

Check job status:

printf '%s\n' '{"version":1,"jobID":"TASK_ID_FROM_RECEIPT"}' |
  bds jobs get --request -

Retrieve the result after the job completes:

printf '%s\n' '{"version":1,"jobID":"TASK_ID_FROM_RECEIPT"}' |
  bds jobs result --request -

The author validated with the same eight records: querying directly in SQL, submitting via MCP, closing the connection, and then retrieving from both the CLI and a new MCP session all returned the same three-row summary result. This shows that the task handoff mechanism is reliable across entry points.

Note that rerunning a saved query recalculates based on the current data source, whereas retrieving a completed job returns the result captured during that run. If the data source has changed, keeping earlier results is actually useful for comparison. In addition, jobs expire 24 hours after submission, you need to keep your Mac from sleeping during the run, and retrieving results requires the appropriate data source access permission and Pro authorization.

The difference between asynchronous jobs and synchronous queries lies in lifecycle and result ownership. A synchronous query is a one-off "request—wait—return" call that aborts when the connection drops; an asynchronous job is persisted by the server, returning a job ID (referred to as task ID or receipt in the article) immediately upon submission, and the caller can query status or retrieve results with this ID at any time, even if the original connection has been closed. This pattern suits long-running exports, large data scans, or scenarios that need to survive client restarts.

The job ID is essentially a receipt for one run: it binds the input and output of that execution, not the current state of the data source. That's why the article distinguishes two semantics—rerunning a saved query recalculates against current data, while retrieving a job reads a historical snapshot. This distinction matters especially when the data source is continuously written to, since historical results can be used for comparison and auditing. Jobs typically have an expiration time (24 hours in the article), after which the ID becomes invalid and the result can no longer be retrieved, requiring resubmission.

How to Choose Between the Two Interfaces

The author's criteria are practical: use MCP when exploring data in a connected AI client, where data source discovery, query results, and follow-ups all happen in one conversation; use saved CLI commands when you need repeated computation or to embed into scripts.

In fact, both support exploration and execution—AI coding assistants with terminal access can also read CLI help and run queries directly, and connected MCP clients can equally repeat known queries. The real difference lies in the surrounding workflow: MCP requires the client to support the server's tools, whereas scripts require you to handle shell quoting, exit status, and JSON output yourself.

Practical Tips and Data Boundaries

For computations you intend to reuse, the author recommends saving the SQL, data source selection, date, timezone, status definitions, and result row limit together with the output. That way, before the next run you have complete context to review and adjust, rather than facing a JSON file whose origin you don't know.

There's one boundary you must pay attention to regarding data security: queries execute on the local Mac, but the schema, previews, and results returned to the AI client may be sent to the model provider. When choosing what to share, check your client's data settings.

Local CSV and Parquet queries can be tried with BigdataSight's free Viewer; the Pro version adds more formats, remote data sources, comparison, broader export ranges, and independent jobs. The steps in this article are based on BigdataSight 1.2.

For those who frequently do data analysis in AI assistants, the combination of MCP and CLI is not a pile of tools but a natural workflow: let AI handle understanding and validation, let scripts handle repetition and stability.

One-line Summary

Use MCP for exploration, CLI for execution: solidify validated queries into repeatable scripts.

Paragraph Summary

Based on the BigdataSight author's real workflow, the article explains that MCP and CLI are not an either/or choice but a relay relationship: MCP lets AI clients discover tools, inspect schemas, preview data, and generate queries in conversation, making it suitable for exploratory analysis; CLI solidifies validated SQL into repeatable commands, making it easy to script and rerun daily. The author uses an example of counting each service's failure rate for yesterday to illustrate the whole process, demonstrating how to look at the schema before writing queries, avoid pitfalls like cancelled statuses and cross-day records, and then save as a shell script via "Copy as Command." Long-running jobs can be submitted via MCP with a retained job ID and retrieved from the terminal after closing the connection, with reminders about exit status codes, file overwrites, 24-hour job expiration, and the possibility of data being sent to the model provider.