Querying Data

The xentropy data platform exposes a read-only SQL API backed by DuckDB. You can run standard SQL queries against your Delta Lake tables.

#Executing a Query

POST /api/data-platform/query
Authorization: Bearer <your-jwt>
X-Tenant-Id: <your-tenant-id>
Content-Type: application/json

{
  "sql": "SELECT event_type, count(*) AS cnt FROM events GROUP BY event_type ORDER BY cnt DESC"
}

#Response

{
  "columns": [
    { "name": "event_type", "type": "VARCHAR", "nullable": true },
    { "name": "cnt", "type": "BIGINT", "nullable": false }
  ],
  "rows": [
    { "event_type": "click", "cnt": 4500 },
    { "event_type": "view", "cnt": 3200 },
    { "event_type": "purchase", "cnt": 180 }
  ],
  "rowCount": 3,
  "executionTimeMs": 42.5
}

#SQL Support

DuckDB supports a rich subset of SQL. Your queries can use:

| Feature | Supported | |---------|-----------| | SELECT, WHERE, GROUP BY, HAVING | Yes | | JOINs (INNER, LEFT, RIGHT, FULL, CROSS) | Yes | | Subqueries and CTEs (WITH) | Yes | | Aggregation (COUNT, SUM, AVG, MIN, MAX) | Yes | | Window functions | Yes | | String functions | Yes | | Date/time functions | Yes | | UNNEST for arrays | Yes | | CREATE VIEW | Yes (persistent for your session) | | DESCRIBE, EXPLAIN | Yes |

#Not supported

| Operation | Reason | |-----------|--------| | INSERT, UPDATE, DELETE | The engine is read-only by design | | CREATE TABLE, DROP TABLE | Tables are managed by the sync system | | ALTER TABLE | Schema evolution is handled via Delta Lake | | Cross-tenant queries | Each engine instance is isolated to one tenant | | Full-text search | Use DuckDB's LIKE/regexp_matches for now |

#Parameterized Queries

Use parameterized queries with ? placeholders to safely pass dynamic values:

POST /api/data-platform/query
{
  "sql": "SELECT * FROM events WHERE event_type = ? ORDER BY id",
  "params": { "1": "purchase" }
}

#Query Limits

| Limit | Default | Maximum | |-------|---------|---------| | Result rows (maxRows) | 10,000 | 100,000 | | Timeout (timeoutSeconds) | 30 | 60 |

To increase the row limit:

{
  "sql": "SELECT * FROM events",
  "maxRows": 50000
}

#Examples

#Basic aggregation

SELECT
  DATE_TRUNC('month', created_at) AS month,
  count(*) AS signups
FROM users
WHERE created_at >= '2026-01-01'
GROUP BY month
ORDER BY month

#Multi-table JOIN

SELECT
  u.name,
  count(e.id) AS event_count,
  sum(e.value) AS total_value
FROM users u
LEFT JOIN events e ON u.user_id = e.user_id
GROUP BY u.name
ORDER BY total_value DESC

#Window function — running total

SELECT
  event_type,
  ts,
  value,
  sum(value) OVER (PARTITION BY event_type ORDER BY ts) AS running_total
FROM events
ORDER BY event_type, ts

#CTE for complex analysis

WITH daily_stats AS (
  SELECT
    DATE_TRUNC('day', ts) AS day,
    event_type,
    count(*) AS events,
    sum(value) AS revenue
  FROM events
  GROUP BY day, event_type
)
SELECT * FROM daily_stats
WHERE revenue > 100
ORDER BY day DESC

#Best Practices

1. Always use filters — queries without WHERE clauses scan the entire table. Add time-range filters where possible.
2. Prefer aggregations over raw data — let DuckDB do the heavy lifting instead of fetching millions of rows to your client
3. Use LIMIT during development — append LIMIT 10 while building queries to avoid accidental large result sets
4. Parameterize dynamic values — always use ? placeholders for user-provided values to prevent injection (the engine already blocks DDL/DML, but parameterization is still good practice)
5. Check the execution time — the executionTimeMs field in responses helps identify slow queries