Sources

A source is a named connection to some external system or set of files, exposed to the query engine as one or more tables. Every table is addressed in SQL as <source>.<table>; the source name is the schema prefix. Sources are declared under sources: in your workspace pawrly.yaml (see Configuration, and the examples/pawrly.yaml worked file).

sources:
  - name: data
    kind: file
    config:
      path: ./data/*.parquet

SELECT * FROM data.orders          -- table `orders` in source `data`

Pawrly has three core backends, file (local files, or object storage), http (any REST/GraphQL API), and mcp (an external MCP server's tools), plus a set of database and lakehouse builtins that run through an in-process DuckDB engine.

This page is the reference for the source block (every top-level field) and the per-kind config for each kind. For the surrounding config (secrets, caching internals, safety semantics, defaults, and multi-file assembly), see Configuration.

The source block

Every entry under sources: is one source. These are the top-level fields:

¹ Whether config or tables is required depends on the kind (see each kind below).

The config-layer source block also accepts from: (load the body from another file) — see Configuration → Multi-file configs.

Strict keys. The source block rejects unknown top-level fields: a typo'd or misplaced key (e.g. safty:, or a kind-specific key written outside config:/tables:) fails the config load with an error rather than being silently ignored. The full machine-readable shape lives in the generated JSON Schema at schemas/pawrly.schema.json, which editors can use for completion and validation.

name

A valid SQL identifier (letter or _, then alphanumerics/_). It's the schema under which the source's tables are registered, so SELECT … FROM <name>.<table>. Names must be unique across the merged config.

kind

The kind selects the backend and the shape of config/tables. The list is closed (adding a kind is a code change). Matching is case-insensitive; the aliases below resolve to the same kind.

The DuckDB-backed kinds load a DuckDB extension on first use (e.g. postgres, iceberg, ducklake, httpfs; Snowflake's is a community extension). The first registration in a fresh environment may need network access to fetch the extension.

description

Optional human-readable text. No effect on behavior; shown by pawrly source list.

wiki

Optional free-text usage notes aimed at the MCP/agent surface rather than execution: which filters to set, identifier quirks, how to decode a column. Declared on a source (applies to all its tables) and/or on individual table entries; describe_table returns both joined (source notes first). Richer than description, which stays a one-liner for listings.

sources:
  - name: gh
    kind: http
    wiki: |
      All endpoints need `owner` and `repo` filters. Timestamps are ISO-8601 UTC.
    tables:
      - name: pulls
        wiki: |
          `state` defaults to `open`; pass `state = 'all'` to include merged PRs.
        # …

examples

Optional list of SQL statements that must run successfully against this source. pawrly check executes them as live probes, so a broken endpoint or credential is caught at check time rather than first query. describe_table also returns the examples that mention the described table, giving agents known-good starting queries.

sources:
  - name: gh
    kind: http
    examples:
      - SELECT * FROM gh.pulls WHERE owner = 'pawrly' AND repo = 'pawrly' LIMIT 1
    # …

config

A per-kind mapping, opaque to the config layer and interpreted by the kind's builder (so each kind documents its own keys below). Strings here may use ${secret:NAME}, ${env:NAME}, and ${file:PATH} interpolation (see Configuration → Secrets).

tables

Explicit per-table declarations. Per-table fields are written flat — the kind-specific keys (path, format, endpoint, params, response, query, …) sit directly under the table entry, not under a nested config::

tables:
  - name: orders                 # required; the SQL table name
    description: Daily orders     # optional
    path: ./data/orders.parquet   # ← kind-specific fields, flat
    format: parquet
    cache:  { mode: ttl, ttl: 1h }  # optional; overrides the source-level cache
    safety: { max_rows: 100000 }    # optional; overrides the source-level safety

Only name, description, wiki, cache, and safety are common; everything else is kind-specific. Some kinds auto-discover tables when tables: is omitted (file globs, sqlite/postgres/mysql/duckdb/snowflake/ducklake enumerate). Others require tables: (iceberg, delta, and object-store file).

Whether a kind needs tables:, and whether it reads per-table fields at all:

For the attach-style catalog kinds (postgres/mysql/duckdb/snowflake/ducklake), tables are surfaced lazily straight from the remote catalog, so adding tables: entries does not restrict or rename them. Use the semantic layer if you need curated views over an attached database.

The cache block

Opt-in caching for a source (or an individual table). With no block, reads always go live.

cache:
  mode: ttl        # none | ttl | refresh | cron | append
  ttl: 10m

Durations use humantime (30s, 10m, 1h). Storage location, namespacing, and cache-management commands are covered in Configuration → Caching.

The safety block

Guard rails enforced before a scan runs. All fields are optional and default to permissive.

safety:
  require_filters_on: [order_date]   # error unless a filter touches each of these columns
  require_at_least_one_filter: true  # refuse a full-table scan
  max_rows: 1000000                  # hard cap on returned rows
  max_pages: 50                      # cap on HTTP pagination calls
  timeout: 30s                       # per-query timeout
  required_predicates:               # predicates AND-ed into every scan
    - "tenant_id = ${param:tenant_id}"

required_predicates is most useful with the semantic layer, where ${param:NAME} placeholders are bound from query params as safe literals (row-level security). See Configuration → Safety.

raw_table / raw_table_safety

Typed HTTP tables fix the endpoint and output columns in config. Use raw_table: true when the request path must be chosen at query time and no typed table has been declared for it.

The raw table accepts the HTTP method, path, and query string through SQL filters. Each path supplied with = or IN (...) sends one request and returns one row containing the response status and body. Its columns are:

SELECT response_status, response_body
FROM gh                                   -- the source itself is the raw table
WHERE request_path = '/rate_limit'

raw_table_safety overrides the default policy, which requires a filter on request_path (so a bare SELECT * can't fan out arbitrarily).

Unlike typed tables, the raw table is registered unqualified (in the default schema) under the source's own name, so it is queried as FROM <source> — not FROM <source>.<table>. This means a source with raw_table: true reserves its bare name for the raw escape hatch; its typed tables still live at <source>.<table>.

Variables

A source can declare inputs in a variables: block and reference them from its config or table declarations with ${var:NAME}. Variables declared inside a source are visible only to that source.

sources:
  - name: gh
    kind: http
    variables:
      GH_TOKEN:
        kind: secret
        methods:
          - type: oauth
            grant: { type: device_code }
            endpoints:
              device_authorization_url: https://github.com/login/device/code
              token_url: https://github.com/login/oauth/access_token
            client:
              id: { default: my-public-client-id }
            scopes:
              scope:
                delimiter: space
                values: [repo, read:org]
          - type: input
            label: Paste token
            input: GITHUB_TOKEN
    config:
      token: ${var:GH_TOKEN}

See Variables for value types, resolution order, scopes, stored values, and OAuth.

Source Kinds

File Backend (file) — local files & object storage

The file backend serves columnar and row files. Local files use DataFusion's native readers; object storage (S3/GCS/Azure) is expressed with a storage: block and read through DuckDB. A file source needs either a top-level config.path glob or at least one tables: entry.

sources:
  - name: data
    kind: file
    config:
      path: ./data/*.csv          # glob; one table per file, named by file stem

Per-table fields are written flat under each tables: entry: path, format, csv, json, schema, partition_cols. The three big topics follow: formats, globs/partitioning, and object storage.

File formats

format is one of parquet, csv, json. It's inferred from the file extension when omitted (.parquet → parquet; .csv → csv; .json / .jsonl / .ndjson → json). Specify it explicitly for extensionless paths or directories.

CSV — override the dialect with a csv: block (all optional):

    tables:
      - name: metrics
        path: ./data/metrics.tsv
        format: csv
        csv: { header: false, delimiter: "\t" }
        schema:                       # name + type the columns for a headerless file
          - { name: host,  type: varchar }
          - { name: value, type: bigint }

JSON/JSONL — files may be newline-delimited (NDJSON) or a single [ … ] array. The layout is auto-detected from the first non-whitespace byte; force it with a json: block:

      - name: facts
        path: ./data/facts.json
        format: json
        json: { format: array }       # array | ndjson | auto (default)

Explicit schema — a schema: list of { name, type } overrides inference (useful for headerless CSV or mis-inferred columns). Column type values (here and in partition_cols): bool/boolean, int/int32, bigint/int64, float/float32, double/float64, date, and varchar (the default for anything else).

File Partitions

A per-table path may be:

• a single file — ./data/orders.parquet;
• a glob — ./data/orders/*.parquet (all matches unioned into one table);
• a directory — ./lake/events (every file beneath it, read as one table).

For partitioned datasets, declare partition_cols so the partition keys become queryable columns. This applies to all three formats (parquet, csv, json). Two styles, one per table:

Hive Partition — key=value directories (e.g. events/dt=2026-05-31/region=us/*.parquet). The keys are exposed as columns and prune by directory (a filter on dt skips non-matching folders). Streams through the file reader.

      - name: events                  # events/dt=…/region=…/*.parquet
        path: ./lake/events
        format: parquet
        partition_cols:
          - { name: dt,     type: date }
          - { name: region, type: varchar }

SELECT * FROM data.events WHERE dt = '2026-05-31'   -- only that dt= directory is read

Segment — positional partitions for layouts that aren't key=value. Each column takes its value from the directory name at a zero-based index beneath the glob base. Segment-partitioned tables are materialized in memory, so they don't prune.

      - name: sessions                # projects/<project>/*.jsonl
        path: ./projects/*/*.jsonl
        format: json
        partition_cols:
          - { name: project, type: varchar, kind: segment, index: 0 }

Each partition_cols entry is { name, type (default varchar), kind: hive | segment (default hive), index (required for segment) }.

Object storage (S3 / GCS / Azure)

Add a storage: block to read from a bucket. storage.type selects the provider; storage.region and the bucket URLs are the location; credentials live under a typed storage.auth block. Object-store file sources require explicit tables:, each pointing at a remote URL.

sources:
  - name: lake
    kind: file
    config:
      storage:
        type: s3                      # s3 | gcs | azure
        region: us-east-1             # location, not a credential
        auth:
          type: access_key            # access_key | credential_chain
          access_key_id: ${secret:AWS_KEY_ID}
          secret_access_key: ${secret:AWS_SECRET}
          # endpoint: ${env:AWS_ENDPOINT}   # S3-compatible stores (MinIO, R2, …)
    tables:
      - name: events
        path: s3://my-bucket/events/*.parquet
        format: parquet               # parquet (default) | csv | json

auth.type selects the method (default access_key); each provider supports more than one:

With no auth block, the ambient credential chain is used. auth.type: chain is accepted as a shorthand alias for credential_chain.

In addition to s3/gcs/azure, storage.type: http covers authenticated HTTPS file URLs; combine it with a header or basic auth block to attach credentials to plain-HTTP reads (the custom/oauth2 HTTP-source auth styles do not apply here).

Scheme auto-routing. A file source is routed through DuckDB automatically whenever it sees a storage: block or a remote scheme on any path/location — s3://, gs:///gcs://, az:///azure:///abfss://, or http(s)://. A public bucket or HTTPS file therefore works with no storage: block at all; you only need one to supply credentials, a region, or a custom endpoint.

Remote files are read by DuckDB's read_parquet/read_csv/read_json, so the local-file csv/json/partition_cols/schema options do not apply to object-store tables; DuckDB infers the schema and reader from the URL and format. Remote http(s):// paths also cannot be globbed; point each table at a single concrete URL (bucket globs like s3://…/*.parquet are fine).

HTTP Backend (http) — REST & GraphQL APIs

An HTTP source describes how to turn a SQL table scan into API requests and shape its JSON responses into rows.

Each query follows this sequence:

1. Values from SQL filters are inserted into the endpoint path, query string, or request body.
2. Pawrly sends the request and follows the table's pagination rules.
3. response.path selects the JSON array that contains the rows.
4. response.schema extracts columns from each item in that array.

Source-level config block carries the required base_url, authentication, shared request headers, retries, and rate limiting. Each tables: entry defines one SQL table and its request.

sources:
  - name: gh
    kind: http
    config:
      base_url: https://api.github.com     # joined with each table's endpoint
      token: ${secret:GITHUB_TOKEN}
    raw_table: true
    tables:
      - name: pulls
        endpoint: /repos/{owner}/{repo}/pulls
        params:
          - { name: owner, required: true, filterable: true }
          - { name: repo,  required: true, filterable: true }
          - { name: state, required: false, default: open, filterable: true }
        response:
          path: $
          schema:
            - { name: number, type: bigint }
            - { name: title,  type: varchar }
            - { name: state,  type: varchar }
        pagination: { type: link_header }

SELECT number, title FROM gh.pulls
WHERE owner = 'CITGuru' AND repo = 'pawrly' AND state = 'open' LIMIT 20

For this query, Pawrly requests:

GET https://api.github.com/repos/CITGuru/pawrly/pulls?state=open

Each pull request object in the response array becomes one row. Pawrly follows GitHub's Link pagination header until the query has enough rows or there are no more pages.

From an OpenAPI spec (config.type: openapi)

Instead of hand-writing tables:, point an HTTP source at an OpenAPI 3.0.x spec and Pawrly synthesizes one table per GET operation at load time — endpoint, params, columns, rows path, and pagination are read from the document. Here base_url is the spec URL; the real API base comes from the spec's own servers.

sources:
  - name: stripe
    kind: http
    config:
      type: openapi
      base_url: https://raw.githubusercontent.com/stripe/openapi/refs/heads/master/latest/openapi.spec3.yaml
      auth:
        type: header
        headers:
          - name: Authorization
            bearer: ${secret:STRIPE_API_KEY}
      openapi:
        include: { paths: ["/v1/charges*", "/v1/customers*"] }   # optional; default = every GET

SELECT id, amount, currency, status FROM stripe.get_charges LIMIT 10

Only read-only GET operations are exposed; pagination is inferred generically (page, offset, cursor, and a last-row cursor). Where inference is uncertain (e.g. a polymorphic response) the column degrades to json and a diagnostic is logged.

Adjusting a synthesized table. A tables: entry whose name matches a synthesized table patches it — only the fields you set are merged in, the rest of the synthesis is kept; a name that matches nothing is a full new table definition. So fixing one field doesn't mean re-declaring the endpoint and every column:

tables:
  - name: get_charges
    response: { path: "$.data" }          # patch the rows-path; keep the synthesized columns
  - name: get_events
    pagination: null                        # drop the inferred pagination

Fields merge per key; arrays (response.schema, params) and type-tagged blocks (pagination) replace wholesale, and a null clears a field.

Top-level config (the same plumbing as hand-declared HTTP mode):

config.openapi (synthesis-specific):

Source-level safety.max_pages caps the pagination loop for synthesized tables (they inherit no per-table safety). With openapi.cache set, the spec is stored under $PAWRLY_HOME/cache/openapi/ (default ~/.pawrly) keyed by URL; without it, the document is fetched on every load (or point base_url at a vendored file:// copy).

Authentication

Set source-level auth with the config.token shorthand or a full auth: block (the block wins if both are present). The block is tagged by type — header, basic, custom, or oauth2:

header — attach one or more headers (bearer tokens and API keys live here). headers is a list; each entry gives a name plus exactly one of bearer (sent as Bearer <value>) or value (sent verbatim). Multiple entries cover APIs that need several auth headers at once (e.g. Datadog).

    config:
      base_url: https://api.example.com
      auth:
        type: header
        headers:
          - { name: Authorization, bearer: "${secret:GITHUB_TOKEN}" }   # → "Bearer …"
          - { name: X-Api-Key,     value: "${secret:API_KEY}" }         # literal

basic — base64-encodes username:password into Authorization: Basic ….

      auth:
        type: basic
        username: "${secret:API_USER}"
        password: "${secret:API_PASSWORD}"

custom — credentials carried outside headers. query is a list of { name, value } appended to every request as query-string params (the many ?api_key=… APIs); body is a list of { name, value } injected into the request body as a JSON object. When a table also declares its own JSON body, the body fields are merged on top of it; with no table body, they are sent as the whole JSON body. (Merging requires a JSON table body — a form body errors.)

      auth:
        type: custom
        query:
          - { name: api_key, value: "${secret:API_KEY}" }
        body:
          - { name: tenant, value: acme }

oauth2 — client-credentials grant: a token is fetched on first use, cached, re-fetched before expiry, then sent as Authorization: Bearer <token>. Fields: token_url, client_id, client_secret, optional scope, audience.

      auth:
        type: oauth2
        token_url:     https://login.example.com/oauth/token
        client_id:     ${secret:CLIENT_ID}
        client_secret: ${secret:CLIENT_SECRET}
        scope:         read:data       # optional

Shorthand — config.token is the single-bearer shorthand, equivalent to a header block with one Authorization: Bearer <token> entry:

    config:
      base_url: https://api.github.com
      token: ${secret:GITHUB_TOKEN}

Authentication fallback. A malformed auth: block (wrong type, a missing required field) does not raise a config error — it falls back to no authentication, and requests go out unauthenticated. If an API starts returning 401/403, double-check the auth block's shape first.

Allowed Hosts

Because a SQL query shapes the request URL — through path params, the raw table's request_path, http.get, pagination links, and redirects — Pawrly restricts where those requests can go, so a query can't turn the engine into a proxy into your network:

• Requests to the source's own base_url origin are always allowed, and carry the source's credentials.
• A request to any other origin is allowed only if the target is public. Private ranges (RFC1918), cloud-metadata addresses (169.254.169.254), CGN, ULA, and *.internal hosts are refused. localhost/loopback stays allowed for local development.
• The check is on the resolved address, not just the URL string: a public-looking hostname that resolves to a private or metadata IP (DNS rebinding) is rejected at connect time.
• Credentials are pinned to trusted origins. If a source has authentication configured, a request or redirect to an untrusted origin is refused. An unauthenticated source may follow an allowed public cross-origin target without credentials.

allowed_hosts widens that trust to hosts beyond base_url — an API that serves a sibling host (uploads, a CDN, a regional endpoint). A listed host is treated like base_url: reachable and credential-bearing. Entries are exact hostnames or a *.suffix wildcard matching that suffix's subdomains:

    config:
      base_url: https://api.github.com
      token: ${secret:GITHUB_TOKEN}
      allowed_hosts:
        - uploads.github.com     # exact host — release-asset uploads
        - "*.internal.acme.com"  # a subdomain wildcard you control

A listed host receives this source's credentials, so keep the list to hosts you trust with that identity. Entries match hostnames, not ports: a match applies over HTTP or HTTPS on any port.

A wildcard on a public or multi-tenant suffix (*.com, *.s3.amazonaws.com, *.githubusercontent.com) is invalid. Pawrly logs and skips an invalid entry while loading the source. Every accepted wildcard is also logged with a reminder that it carries credentials.

Request headers

config.headers is a source-level string→string map applied to every request the source issues — typed tables and the raw table alike. Use it for the constant headers an API expects on all calls (a media type, an API-version pin) instead of repeating them in each table's headers:

    config:
      base_url: https://api.github.com
      token: ${secret:GITHUB_TOKEN}
      headers:
        Accept: application/vnd.github+json
        X-GitHub-Api-Version: '2022-11-28'

Source headers go on first; a table's own headers are merged on top and override on a key collision. An entry with an invalid header name or non-string value is skipped with a warning rather than failing the source. (Auth headers come from the auth block, not here.)

Request

Each table's request is built from these flat fields:

• endpoint (required) — path appended to base_url. May carry a query string and {param} placeholders; a param whose name matches a {placeholder} fills the URL path. Remaining params become query parameters — except those consumed by the body template, which are sent in the body only, not duplicated onto the query string.
• method — defaults to GET.
• headers — a per-table map of extra request headers. Constant headers shared by every table (e.g. Accept, an API-version header) are better set once at the source level via config.headers (see below); per-table headers are merged on top and win on a key collision.
• body — for POST/PUT/GraphQL: kind (json, the default, sets Content-Type: application/json; or form for application/x-www-form-urlencoded) and template (body text with {param} placeholders; other braces — JSON/GraphQL syntax — are left untouched). In a JSON body, an unbound optional param (no filter and no default) doesn't leave its placeholder behind: the JSON object member holding it is dropped, and any object it empties is dropped too. This is what makes inlined optional GraphQL filters work — filter: { team: { id: { eq: "{team_id}" } } } with team_id unfiltered collapses to no team filter (the variable defaults to null = match-all) instead of sending a literal "{team_id}" that matches nothing.
• requests — conditional request shapes tried in order; the first whose when_filters are all bound replaces the default endpoint/method/body. Each entry is { when_filters: [...], endpoint, method?, body? }. The classic use is a get-by-id endpoint when an id filter is present, falling back to a list endpoint otherwise.

      - name: search
        endpoint: /graphql
        method: POST
        params:
          - { name: q, required: true }
        body:
          kind: json
          template: '{"query": "{ search(q: \"{q}\") { id name } }"}'
        response:
          path: $.data.search
          schema:
            - { name: id,   type: varchar }
            - { name: name, type: varchar }

Query parameters

params declares the columns a table accepts as filters. Each is { name, type (default varchar), required (default false), default, accepts, emit, explode, derive, filterable }:

• required: true — the param must be bound by a SQL filter — an equality, an IN (...) list, or a join key (see dependent joins below) — or the scan fails with a clear error (rather than fetching an unbounded result).
• default — value used when the user doesn't filter on it.
• filterable: true — let the param be used in a WHERE clause. Normally a param can only be filtered on if it's also a response.schema column; this exposes it as a column without declaring one. Use it for a filter the API accepts but doesn't return.

        params:
          - { name: status, filterable: true }   # WHERE status = 'active' → ?status=active

• Equality pushes down by default: WHERE state = 'open' → ?state=open.
• Comparisons — to push >= / <= etc., list them in accepts and map each to a query-parameter name in emit:

        params:
          - name: created
            accepts: [">=", "<="]
            emit: { ">=": since, "<=": until }   # WHERE created >= X → ?since=X

• explode — explode: true pushes a SQL IN (a, b, c) on a query param down as repeated query pairs (?key=a&key=b&key=c) in a single request. Equality still emits a single pair. Only honored for the query string (not path/body params).
• **IN (...) on a path or required param** fans out to one request per value (no explode needed) and unions the results — the typed-table equivalent of the raw table's behavior — bounded by LIMIT and safety.max_rows. An IN on a plain (non-path, non-required, non-explode) param is still filtered in-engine.
• derive — compute the param's value when the query doesn't supply one (a dynamic default), tagged by kind:
  • ago — { kind: ago, seconds: N } → epoch seconds now - N (a relative time window, e.g. "last hour").
  • split — { kind: split, from: <other param>, separator: "-", part: 0 } → a part (0-based) of another bound param's value split by separator. Useful to derive request fields from a composite filter (e.g. an issue key ENG-123 → team ENG + number 123). A derived param that feeds a body template stays out of the query string.

        params:
          - { name: from,   type: bigint, derive: { kind: ago, seconds: 3600 } }
          - { name: status, explode: true }              # WHERE status IN ('open','closed') → ?status=open&status=closed

A param can also be surfaced as an output column with source: param on a response.schema entry (see below).

Dependent (bind) joins. A required param can also be satisfied by a join key, so a get-by-id / detail table is driven from the ids another table produces — ranked t JOIN detail d ON d.id = t.id LIMIT 10. The driver runs once, its distinct keys feed the detail fetch (the IN fan-out above), and the lookup is bounded by an enclosing LIMIT. Works across sources and hosts; limited to inner joins on a single column key.

Response

response describes how to turn the JSON payload into rows:

• path — JSONPath to the array of rows. $ (the default) means the body is the array; $.data digs into a wrapper object.
• reshape — turn a payload that isn't a flat array at path into rows before column extraction. See Reshaping the response.
• schema — the columns to extract per row, each { name, type, source?, expr? }:
  • type ∈ varchar/string/text, bigint/int64, int/int32, double, float, bool/boolean, date, timestamp, timestamptz (ISO-8601 / RFC 3339 strings are parsed), and json (a nested object/array kept as raw JSON text).
  • source — defaults to the row's top-level field of the same name. Set $.nested.field to read a different path, $ to capture the whole row element (usually into a json column), or param to inject a request parameter as a column.
  • expr — a computed expression evaluated per row, for columns a single path can't express. Takes precedence over source when set. See Computed columns.
• allow_404_empty — treat a 404 as an empty result set instead of an error.
• error — surface API failures as a clear scan error: status (a list of codes or matchers like ">=400", "5xx", "<500") and/or path (a JSONPath to an error message inside a 200-with-error body).

        response:
          path: $.data
          allow_404_empty: true
          schema:
            - { name: id,       type: bigint }
            - { name: author,   type: varchar, source: $.user.login }
            - { name: payload,  type: json,    source: $ }
            - { name: repo,     type: varchar, source: param }
          error:
            status: [">=400"]
            path: $.message

Computed columns

When a column needs more than a single JSONPath, give it an expr instead of a source. An expr is a small tree, each node tagged by kind, evaluated against each row (plus the bound request params). A missing path, a shape mismatch, or a failed transform yields NULL — never a scan error.

        schema:
          # title from `attributes.title`, falling back to `title`
          - name: title
            type: varchar
            expr:
              kind: coalesce
              exprs:
                - { kind: path, path: [attributes, title] }
                - { kind: path, path: [title] }
          # array of label objects -> "bug,p1"
          - { name: labels, type: varchar,   expr: { kind: map_join, path: [labels], item_path: [name] } }
          # epoch seconds -> a real timestamp
          - { name: created, type: timestamp, expr: { kind: to_timestamp, unit: seconds, expr: { kind: path, path: [created_at] } } }

Reshaping the response

response.path normally points at an array of rows. response.reshape turns the value at path into rows when it isn't one — applied before column extraction. Tagged by kind:

• dict_entries — the value at path is an object/map; each entry becomes a row: the entry value with its key added as _key (or {_key, _value} when the value isn't an object). Read the key with source: $._key.

      - name: calendar_colors
        endpoint: /colors
        response:
          path: $.calendar
          reshape: { kind: dict_entries }
          schema:
            - { name: id,         type: varchar, source: $._key }
            - { name: background, type: varchar }

• series_points — flatten { <series>: [ { …, <points>: [[t, v], …] } ] } into one row per point: each series object (minus its points field) plus the point's two values, written under the timestamp and value column names. Fields: series, points, timestamp, value.

      - name: metrics
        endpoint: /api/v1/query
        response:
          path: $
          reshape: { kind: series_points, series: series, points: pointlist, timestamp: timestamp, value: value }
          schema:
            - { name: metric,    type: varchar }   # carried from each series object
            - { name: timestamp, type: bigint }    # point[0]
            - { name: value,     type: double }    # point[1]

Pagination

Set pagination to keep fetching pages; absent means a single request. A SQL LIMIT stops pagination early once enough rows are collected, and safety.max_pages caps the loop. The strategy is tagged by type:

pagination: { type: cursor, next_path: $.response_metadata.next_cursor, param: cursor }

Rate limiting & retries

rate_limit keeps requests within the API's quota:

retry governs transient failures (transport errors, 5xx, 429, 503, and any extra_statuses): max_retries (default 3), base_backoff_ms (default 200, doubles each attempt), max_backoff_ms (default 5000). Backoff honours a Retry-After header when present.

    config:
      base_url: https://api.github.com
      rate_limit:
        remaining_header: x-ratelimit-remaining
        reset_header:     x-ratelimit-reset
        extra_statuses:   [403]            # GitHub secondary limit
      retry:
        max_retries: 5

A runnable cache-over-API walkthrough lives at examples/cache-http/.

MCP Backend (mcp) — an MCP server's tools as tables

An MCP source exposes tools from an external Model Context Protocol server as SQL tables.

When the source loads, Pawrly calls tools/list to discover the available tools and their input and output schemas. A query then follows this sequence:

1. The table name selects an MCP tool.
2. Supported WHERE filters and LIMIT values become tool arguments.
3. Pawrly invokes the tool with tools/call.
4. Items in the tool result become rows and their fields become columns.

You can connect over stdio, which runs a local subprocess, or streamable HTTP, which connects to a remote server.

sources:
  - name: linear
    kind: mcp
    config:
      transport: streamable_http
      url: https://mcp.linear.app/mcp
      auth:
        type: header
        headers:
          - name: Authorization
            bearer: ${secret:LINEAR_API_KEY}

  - name: github
    kind: mcp
    config:
      transport: stdio
      command: ["npx", "-y", "@modelcontextprotocol/server-github"]
      env:
        GITHUB_TOKEN: ${secret:GITHUB_TOKEN}

SELECT id, title, status FROM linear.list_issues WHERE assignee = 'me@example.com' LIMIT 20

Here, linear.list_issues selects the list_issues tool and the assignee filter is passed as an argument. The limit is also passed when the tool exposes a compatible limit input; either way, the query returns at most 20 rows.

Two ways to get tables, one dial. A source produces tables from introspection (tools/list) and from declaration (tables:). config.expose sets how much introspection auto-exposes:

include/exclude (by tool name) narrow whatever expose admits; a destructiveHint tool is never auto-exposed.

Output columns. A tool result is exposed as a single result json column unless the tool declares an outputSchema (columns inferred) or a tables: entry declares them. Each tables: entry patches a synthesized table of the same name or defines a new one (tool: + knobs):

Security. A streamable_http url must be https (or http only for loopback), and may not embed credentials in the URL — use the auth block (header/bearer/basic, same shapes as the HTTP backend) with ${secret:…} tokens. Validated at config-load time.

A runnable, per-source-per-file example lives at examples/mcp.yaml.

Databases and Lakehouse Formats

sqlite — local SQLite databases

Attaches a SQLite file read-only and exposes its tables; equality filters push down. When tables: is omitted, every user table is auto-registered. sqlite is the one attach-style kind whose tables: entries are honored — supply a query to restrict or reshape a table.

sources:
  - name: app
    kind: sqlite
    config:
      path: ./app.db
    # tables:                       # optional: restrict / reshape
    #   - name: active_users
    #     query: SELECT id, email FROM users WHERE active = 1

postgres, mysql — foreign databases

DuckDB ATTACHes the database read-only and exposes its tables lazily (<source>.<table>); equality predicates, projection, and limits push down. No tables: needed.

sources:
  - name: oltp
    kind: postgres            # aliases: pg, postgresql
    config:
      host: db.internal
      database: app
      user: readonly
      password: ${secret:PG_PASSWORD}

duckdb — local DuckDB database file

Attaches a .duckdb database file read-only and exposes its tables lazily.

sources:
  - name: local_db
    kind: duckdb
    config:
      path: ./analytics.duckdb

snowflake

DuckDB ATTACH via the Snowflake community extension (installed on first use). Requires account, user, password; optional database, schema, warehouse, role.

sources:
  - name: warehouse
    kind: snowflake
    config:
      account: acme.us-east-1
      user: ${secret:SNOWFLAKE_USER}
      password: ${secret:SNOWFLAKE_PASSWORD}
      database: ANALYTICS
      schema: PUBLIC

iceberg, delta — table formats

Each declared table maps to a DuckDB scan function over a table location. tables: is required, each with a path (or location).

sources:
  - name: lake
    kind: iceberg                 # or: delta (alias deltalake)
    tables:
      - name: orders
        path: s3://bucket/warehouse/orders

For tables on an object store, provide credentials with a storage: block (same keys as object-store file); the httpfs extension loads automatically.

ducklake — DuckLake lakehouse catalog

Attaches a DuckLake catalog (a metadata database plus a data path) and exposes its tables lazily.

sources:
  - name: lake
    kind: ducklake
    config:
      catalog: ./metadata.ducklake     # sqlite/duckdb/postgres catalog
      data_path: ./lake_data            # local or s3://… (+ optional storage block)

Column type reference

Several places take a column type — the file backend's schema: and partition_cols, and the http backend's response.schema. The accepted spellings (case-insensitive) and the contexts that support them:

Any unrecognized type spelling falls back to varchar. For http, timestamp/timestamptz parse ISO-8601 / RFC 3339 strings, and json keeps a nested object/array as raw JSON text (pair it with source: $ to capture a whole row element); see Querying JSON columns to read into it.

Querying JSON columns

A json column is raw JSON text — an object, array, or scalar kept verbatim so nested structure survives the scan. Two function families read into it; they share no names, so mix them freely.

Pawrly JSON Helpers

DataFusion JSON Helpers

Exploding an array of objects

unnest must sit in a projection, not a correlated lateral join, so explode the array in a subquery (a CTE) and read its elements in the outer query:

WITH elems AS (
  SELECT unnest(from_json(payload)) AS e   -- payload is a JSON array of objects
  FROM t
)
SELECT json_extract_string(e, 'code')                   AS code,
       CAST(json_extract_string(e, 'amount') AS DOUBLE)  AS amount
FROM elems
WHERE json_extract_string(e, 'code') = 'USD'

json_length(payload) gives the array length without unnesting.

Gotchas

• **unnest is projection-only.** FROM t, unnest(from_json(t.payload)) (a correlated lateral join) is rejected by the planner; use the CTE form above.
• Operator precedence. ->/->> bind looser than IN and comparisons, so parenthesize: (e ->> 'code') IN ('USD', 'EUR').
• Typed getters match the JSON type. json_get_float(e, 'amount') returns NULL when the value is a JSON string ("12.50") rather than a number — use json_get_str or ->>, then CAST.

Federation

Every source is a table in one DataFusion plan, so you can join across kinds in a single statement (a local Parquet file against a Postgres table against an HTTP query) with no import step:

SELECT u.email, COUNT(p.number) AS open_prs
FROM oltp.users u
JOIN gh.pulls p ON p.user = u.github_login
WHERE p.owner = 'pawrly' AND p.repo = 'pawrly' AND p.state = 'open'
GROUP BY u.email

See examples/pawrly.yaml for a kitchen-sink configuration covering every kind.