ollamalocal-aiprivacydata-analysisfree

Query Your Data Lake with AI for Free: Ollama + DataSpoc Lens

Michael San Martim · 2026-04-28

Every AI data tool requires an API key. OpenAI charges per token. Anthropic charges per token. Your company’s data goes to someone else’s servers. For a quick question about your own data, that is a lot of friction and risk.

DataSpoc Lens works with Ollama to run AI queries entirely on your machine. No API key, no internet, no data leaving your laptop. Ask “what were top products last month?” and get a real SQL query executed against your data lake — all local.

Why Local AI for Data Queries

Three reasons:

Free — no API costs, no usage limits, no billing surprises
Private — your data and queries never leave your machine
Fast for small models — sub-second responses for SQL generation on a MacBook

The tradeoff: local models are less capable than GPT-4o or Claude. For SQL generation on known schemas, they are often good enough.

Setup: One Command

dataspoc-lens setup-ai

This command:

Checks if Ollama is installed; if not, installs it
Pulls the recommended model for SQL generation
Configures Lens to use the local model
Runs a test query to verify

[1/4] Checking Ollama... not found
[2/4] Installing Ollama...
      curl -fsSL https://ollama.ai/install.sh | sh
      ✓ Ollama installed (v0.3.x)
[3/4] Pulling model: duckdb-nsql:7b (4.1 GB)
      ████████████████████████████████ 100%
      ✓ Model ready
[4/4] Running test query...
      Question: "How many tables are available?"
      Generated SQL: SELECT COUNT(*) FROM information_schema.tables
      ✓ AI queries working

Setup complete. Run: dataspoc-lens ask "your question"

Your First AI Query

dataspoc-lens ask "what are our top 10 customers by revenue?"

SQL: SELECT c.name, SUM(o.amount) as total_revenue
     FROM raw.customers c
     JOIN raw.orders o ON c.customer_id = o.user_id
     WHERE o.status = 'completed'
     GROUP BY c.name
     ORDER BY total_revenue DESC
     LIMIT 10

┌──────────────────┬───────────────┐
│       name       │ total_revenue │
├──────────────────┼───────────────┤
│ Acme Corporation │    1,245,800  │
│ TechFlow Inc     │      892,340  │
│ Global Systems   │      756,120  │
│ DataVentures     │      623,450  │
│ CloudFirst Ltd   │      589,200  │
│ ...              │          ...  │
└──────────────────┴───────────────┘

The model read your table schemas, generated a DuckDB SQL query, Lens executed it, and you got real results. All local.

How It Works Under the Hood

When you run dataspoc-lens ask, here is what happens:

1. Lens reads table schemas from the catalog
2. Lens builds a prompt with schemas + your question
3. Prompt is sent to Ollama (local HTTP on port 11434)
4. Ollama generates SQL using the local model
5. Lens validates the SQL syntax
6. Lens executes the SQL via DuckDB
7. Results are displayed

No data goes to the internet. The prompt contains schema metadata (table and column names) and your question. The response is a SQL query. DuckDB executes it against your local cache or cloud bucket.

Choosing the Right Model

DataSpoc Lens supports multiple Ollama models. Here is how they compare for SQL generation:

duckdb-nsql:7b (Recommended Default)

A 7B parameter model fine-tuned specifically for DuckDB SQL generation.

ollama pull duckdb-nsql:7b
dataspoc-lens config set ai.model duckdb-nsql:7b

Size: 4.1 GB
RAM: 8 GB minimum
Speed: ~2 seconds per query on M1 Mac
Accuracy: High for standard SQL (aggregations, joins, filters)
Weakness: Struggles with complex window functions

qwen2.5-coder:1.5b (Lightweight)

A tiny model that runs on almost any machine.

ollama pull qwen2.5-coder:1.5b
dataspoc-lens config set ai.model qwen2.5-coder:1.5b

Size: 1.0 GB
RAM: 4 GB minimum
Speed: ~500ms per query
Accuracy: Good for simple queries, misses complex joins
Best for: Low-resource machines, quick lookups

qwen2.5-coder:7b (Balanced)

A general-purpose coding model with good SQL capabilities.

ollama pull qwen2.5-coder:7b
dataspoc-lens config set ai.model qwen2.5-coder:7b

Size: 4.4 GB
RAM: 8 GB minimum
Speed: ~2 seconds per query
Accuracy: Good across query types
Best for: When you also want the model for non-SQL coding tasks

Accuracy Comparison: Local vs. Cloud

We tested 50 natural language questions against a standard e-commerce dataset (customers, orders, products, events). Each question was tested against three providers:

Model	Correct SQL	Minor Errors	Wrong	Avg Time
Claude claude-sonnet-4-20250514	47/50 (94%)	2/50	1/50	1.2s
GPT-4o	45/50 (90%)	3/50	2/50	1.8s
duckdb-nsql:7b (local)	40/50 (80%)	6/50	4/50	2.1s
qwen2.5-coder:7b (local)	38/50 (76%)	7/50	5/50	2.3s
qwen2.5-coder:1.5b (local)	30/50 (60%)	10/50	10/50	0.5s

Where local models excel:

Simple aggregations: “total revenue this month”
Filtering: “orders from California”
Basic joins: “orders with customer names”
Group by: “revenue by product category”

Where local models struggle:

Complex window functions: “running average with partition”
Multi-step CTEs: “cohort retention analysis”
Ambiguous questions: “how is the business doing?”
Date math edge cases: “fiscal quarter calculations”

80% accuracy from a free, local model is impressive. For the 20% of complex queries, use cloud AI or write the SQL yourself.

When to Go Local vs. Cloud

Go Local When

You are exploring data interactively (quick questions, iteration)
Data privacy is a hard requirement (healthcare, finance, government)
You want to avoid API costs for frequent queries
You are offline or on a restricted network
The queries are straightforward (filters, aggregations, simple joins)

Go Cloud When

You need high accuracy on complex analytical questions
The query involves multi-step reasoning (cohort analysis, funnels)
You are building a product where accuracy matters more than cost
You want natural language explanations alongside results

Use Both

Configure Lens to use local by default, cloud on demand:

# Default: local model
dataspoc-lens config set ai.provider ollama
dataspoc-lens config set ai.model duckdb-nsql:7b

# Quick local query
dataspoc-lens ask "total orders today"

# Complex query: override to cloud
dataspoc-lens ask "build a cohort retention analysis by signup month" --provider anthropic

SDK Usage

Use local AI from Python:

from dataspoc_lens import LensClient

lens = LensClient()

# Uses the configured AI provider (Ollama by default after setup-ai)
answer = lens.ask("what are the top 5 product categories by revenue?")
print(answer)

# Explicit provider override
answer = lens.ask(
    "complex cohort retention analysis",
    provider="anthropic",
    model="claude-sonnet-4-20250514",
)
print(answer)

Running on a Server

Ollama runs as a daemon. For a shared analytics server:

# Start Ollama as a service
sudo systemctl enable ollama
sudo systemctl start ollama

# Pull models
ollama pull duckdb-nsql:7b

# Multiple users can query simultaneously
# Each dataspoc-lens instance connects to localhost:11434

For GPU-equipped servers, Ollama automatically uses the GPU for faster inference:

# Check GPU detection
ollama ps

# On a T4 GPU, expect ~500ms per query for 7B models

Troubleshooting

Ollama not running:

# Start the Ollama daemon
ollama serve

# Or check the service
systemctl status ollama

Model too slow:

# Switch to the smaller model
dataspoc-lens config set ai.model qwen2.5-coder:1.5b

# Or check if GPU is being used
ollama ps

Incorrect SQL generated:

# See the generated SQL without executing
dataspoc-lens ask "your question" --show-sql-only

# Fix and run manually
dataspoc-lens query "your corrected SQL"

Out of memory:

# Check model size vs available RAM
ollama list

# Use the 1.5B model on machines with < 8 GB RAM
dataspoc-lens config set ai.model qwen2.5-coder:1.5b

Free AI queries on your own data. No API key, no internet, no data leaving your machine. Install once, ask forever.

← Back to blog