Run federated queries

Kiwi Fabric accepts SQL in the T-SQL dialect (it is parsed with Microsoft's ScriptDom parser). Each table name in the statement refers to a registered capability. The engine fetches each table from its own source in parallel, then executes your original statement over the fetched data to perform the joins, aggregation, sorting and paging.

Supported SQL#

• JOIN across sources
• WHERE, GROUP BY, HAVING, ORDER BY
• OFFSET / FETCH paging
• Named parameters written as @name

Run a query from the CLI#

kiwi fabric query --verbose --param minTotal=100,tier=gold --sql "
SELECT o.id, c.name, p.title, o.total
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN products  p ON o.product_id  = p.id
WHERE o.total > @minTotal AND c.tier = @tier"

Here orders, customers and products can each live in a different system. To run a longer statement from a file use --file query.sql.

Run a query from code#

Pick your language. The same call is available in all four SDKs, and your choice is remembered for the other samples on this site:

using Kiwify.Kiwi.Fabric.Client;

using var client = new KiwiFabricClient("http://localhost:8080", apiKey);

var result = await client.QueryAsync(
    "SELECT o.id, c.name FROM orders o JOIN customers c ON o.customer_id = c.id " +
    "WHERE o.total > @minTotal",
    new Dictionary<string, object?> { ["minTotal"] = 100 });

Console.WriteLine($"{result.RowCount} rows in {result.TotalMs}ms");

import com.kiwify.fabric.client.KiwiFabricClient;
import com.kiwify.fabric.client.model.query.QueryResponse;
import java.util.Map;

KiwiFabricClient client = new KiwiFabricClient("http://localhost:8080", apiKey);

QueryResponse result = client.query(
    "SELECT o.id, c.name FROM orders o JOIN customers c ON o.customer_id = c.id " +
    "WHERE o.total > @minTotal",
    Map.of("minTotal", 100));

System.out.println(result.rowCount() + " rows in " + result.totalMs() + "ms");

import { KiwiFabricClient } from "kiwifabric";

const client = new KiwiFabricClient("http://localhost:8080", apiKey);

const result = await client.query(
  "SELECT o.id, c.name FROM orders o JOIN customers c ON o.customer_id = c.id " +
  "WHERE o.total > @minTotal",
  { minTotal: 100 },
);

console.log(`${result.rowCount} rows in ${result.totalMs}ms`);

from kiwifabric import KiwiFabricClient

with KiwiFabricClient("http://localhost:8080", api_key=api_key) as client:
    result = client.query(
        "SELECT o.id, c.name FROM orders o JOIN customers c ON o.customer_id = c.id "
        "WHERE o.total > @minTotal",
        {"minTotal": 100},
    )
    print(f"{result.row_count} rows in {result.total_ms}ms")
    df = result.to_pandas()   # or result.to_polars() / result.arrow

Parameters are named without the leading @. Python results are backed by an Arrow table, so they convert to pandas or polars without copying. See Client SDKs for installation and authentication. Over HTTP, send the statement to POST /api/query (see the REST API reference).

Run a query in the playground#

The Web UI Query Playground has a SQL editor, optional parameters, and results beside it. Results can be copied or exported as CSV, and the Query Log button jumps to the request log. Ask Fabric AI turns a plain-English description into SQL. See Generate SQL and sagas with AI.

Read the telemetry#

Every result carries per-source telemetry: the rows each source returned, how long the fetch took, which predicates were pushed down to the source, and the SQL that was executed there. With --verbose the CLI prints this breakdown after the result table. In the Web UI, the SQL playground has an Execution Plan tab and the request detail page shows a waterfall timeline.

Pushdown is an optimisation, not a requirement for correctness. The engine always re-applies the full WHERE clause itself, so a source that cannot filter still returns correct results, just more slowly. See Performance.

Save a query as a virtual table#

A virtual table is a named, saved query that you can select from and join like any other table:

kiwi fabric vtable-add --name vip_orders --desc "Gold-tier orders" --file vip_orders.sql
kiwi fabric query --sql "SELECT * FROM vip_orders WHERE total > 500"

Virtual tables appear in lineage, so you can see which sources a report ultimately depends on.

Stored procedures#

A capability of kind SpRead runs a stored procedure instead of a table scan. Its output columns are defined when it is registered, and input values are supplied at query time as caller parameters. In the SQL playground there is a panel for these values; from the CLI use kiwi fabric cap-exec --table <name> --param key=value.