Looky
Back to site

Docs / Reference

Source adapter differences

Why this page exists

Looky supports three source adapters: BigQuery, Postgres, and MySQL. Most of the platform behaves identically across all three. A handful of areas behave differently — those are the ones model and dashboard authors trip on. This page lists each one in observable terms (what you see, not how it is implemented).

Source declaration

BigQuery

  • Required: type: bigquery, project_id (the GCP project paying for queries), credentials_file (plain filename of a service-account JSON inside secrets/ — no path).
  • Optional: location for multi-region datasets, datasets (required at the time of schema introspection).

Postgres

  • Required: type: postgres, dsn (libpq URI without user:password@; carries host, port, database, and any libpq query-string flag like sslmode=require, application_name, connect_timeout, …), credentials_file (filename of a JSON inside secrets/ declaring {"user", "password"}).
  • Optional: name (cosmetic label), schemas (limits introspection scope; default is all non-system schemas).

MySQL

  • Required: type: mysql, dsn (mysql://host:port/database URI without user:password@ and without any ?query parameters — the port defaults to 3306), credentials_file (filename of a JSON inside secrets/ declaring {"user", "password"}).
  • Optional: name (cosmetic label), schemas (databases to introspect; default is the database named in the dsn).
  • Note: MySQL connections are not encrypted — there is no TLS option yet, so use MySQL over a trusted network.

See Sources for working examples per adapter.

Schema introspection

  • BigQuery — requires an explicit dataset list. Without it, introspection cannot find tables.
  • Postgres — discovers tables across the schemas in your schemas list (or the smart default of every non-system schema). No table-list declaration is needed.
  • MySQL — discovers tables in the database named in the dsn (or the databases in your schemas list). System databases (mysql, performance_schema, sys, information_schema) are always excluded. No table-list declaration is needed.

The error messages on introspection failures look completely different per adapter — when in doubt, run a small test query first to confirm Looky can reach the source at all.

Parameter binding

String, numeric, and array parameters

Behave identically on all three adapters. No special handling needed.

Date and timestamp parameters

  • BigQuery — bind natively, no extra work.
  • Postgres and MySQL — native binding fails with an explicit error in some query shapes. Add an @param placeholder in the underlying SQL of that source so Looky substitutes the value into the SQL string. See Malloy support for a worked example.

Filter types that produce date / timestamp parameters: cutoff_date, date_range, date_range_preset. Plan for the @param pattern when those filters drive a Postgres- or MySQL-backed model.

NULL parameter values

Each adapter writes a typed null differently: BigQuery and MySQL require an explicit cast, Postgres uses a bare typed null. Looky handles this for you — there is nothing to configure — but it is the reason a malformed nullable parameter can fail on one adapter and silently work on another.

Boolean values (MySQL only)

MySQL has no real boolean type — boolean-looking columns come back as numbers (0/1). To filter on a true/false condition, add an explicit cast(… as boolean) in the model. BigQuery and Postgres have native booleans and need no such cast.

Query execution

  • BigQuery — exposes a free pre-flight cost estimate (in scanned bytes) before the actual run. Useful for planning around scan-heavy queries.
  • Postgres and MySQL — pre-flight validates the SQL plan (via EXPLAIN) but does not estimate cost.

Filters and cross-filtering

The filter resolver and the cross-filter pill mechanism are adapter-agnostic. Differences only show up at the parameter-binding step described above.

  • A select filter behaves identically on all three.
  • The date filters land on the temporal-parameter caveat for Postgres and MySQL — fix on the model side with @param.
  • Pill values from Cross-filtering are typically strings or short identifiers, so they are unaffected.

Pagination (grid & report_matrix)

Server-side pagination uses the same protocol on all three adapters. Cost characteristics differ:

  • BigQuery — every uncached page is a billed scan. Larger page size + caching is cheaper overall. Avoid unbounded grids on un-cached queries.
  • Postgres and MySQL — connection roundtrip latency dominates; cost depends mostly on indexed-scan performance of the underlying tables. Make sure the columns referenced in order by have appropriate indexes.

Cache TTL recommendations

Cache logic itself is the same on all three adapters. The TTL choice is editorial:

  • BigQuery — favor longer TTLs for queries that scan large tables. A 30-minute TTL on a daily-batch dataset is overkill; a 24-hour TTL is usually fine.
  • Postgres and MySQL — TTL is mostly about freshness rather than cost. Caching helps less if the underlying table is small and well-indexed; consider whether the cost saved outweighs the staleness introduced.

Aggregations and dialect features

Looky does not branch on adapter for aggregate or windowing functions. Anything dialect-specific (BigQuery-only ARRAY functions, Postgres-only date_trunc on certain types, MySQL-only date functions, etc.) surfaces as a Malloy compilation error. The fix is on the Malloy side — adjust the model to use a feature every target adapter supports, or guard the model against the wrong adapter.

Worked migration patterns

A date filter that works on BigQuery but breaks on Postgres or MySQL

The native-binding model:

# works on BigQuery, fragile on Postgres / MySQL in some shapes
##! experimental.parameters

source: ec_orders(
  p_date_from::date is @2024-01-01,
  p_date_to::date   is @2024-12-31
) is ecommerce.table('bigquery-public-data.thelook_ecommerce.order_items') extend {

  view: revenue is {
    where:
      created_at::date >= p_date_from
      and created_at::date <= p_date_to
    aggregate:
      revenue is sum(sale_price)
  }
}

Switch the source to sql() with placeholders for Postgres / MySQL compatibility. The SQL body runs in the source's own dialect, so the snippet below is Postgres-only — its ::date casts are a syntax error on MySQL and its lowercase-idiom SQL is not meant for BigQuery. (The alias warehouse is the workspace's own connection alias, not the adapter name.)

# works on Postgres
##! experimental.parameters

source: orders(
  p_date_from::date is null,
  p_date_to::date   is null
) is warehouse.sql("""
  select *
  from orders
  where (@date_from::date is null or created_at::date >= @date_from::date)
    and (@date_to::date   is null or created_at::date <= @date_to::date)
""") extend {
  view: revenue is {
    aggregate:
      revenue is sum(sale_price)
  }
}

On MySQL the same pattern applies, with two dialect rules: casts are written CAST(… AS DATE) — the :: operator is Postgres-only and is a MySQL syntax error — and anything the dialect extracts (an hour, a truncated date) is best computed as a column in the SQL itself:

# works on MySQL
##! experimental.parameters

source: orders(
  p_date_from::date is null,
  p_date_to::date   is null
) is shop.sql("""
  SELECT
    o.sale_price           AS sale_price,
    o.created_at           AS created_at,
    HOUR(o.created_at)     AS created_hour
  FROM orders o
  WHERE (CAST(@date_from AS DATE) IS NULL OR DATE(o.created_at) >= CAST(@date_from AS DATE))
    AND (CAST(@date_to   AS DATE) IS NULL OR DATE(o.created_at) <= CAST(@date_to   AS DATE))
""") extend {
  dimension: created_hour_of_day is created_hour

  view: revenue is {
    aggregate:
      revenue is sum(sale_price)
  }
}

The extra created_hour column shows the "compute extractions in the SQL" rule in action: HOUR() runs in MySQL, and the Malloy layer consumes the ready-made column instead of asking the dialect for an extraction it may not support.

Each @param placeholder must have a matching declaration on the source signature; Looky substitutes the value (or typed NULL when unset) before compile. The source parentheses are required in every model — () when there are no parameters — and a date_range filter's date_from / date_to values match the declared p_date_from / p_date_to automatically.

A grid that paginates well on Postgres / MySQL but blows BigQuery scan budget

Increase pagination.page_size, add a cache sidecar with a long TTL, and pre-aggregate when possible. On BigQuery, paging through millions of rows uncached is expensive — pre-summarise.

Quick reference

  • Source auth — BigQuery: service-account file. Postgres / MySQL: connection string + a {"user","password"} secret.
  • Transport — BigQuery / Postgres: TLS available. MySQL: not encrypted yet.
  • Introspection — BigQuery: explicit datasets required. Postgres: smart-default schemas. MySQL: the DSN's database (or a schemas list).
  • Numeric / string parameters — identical.
  • Date / timestamp parameters — BigQuery: native. Postgres / MySQL: need @param placeholder in SQL.
  • Booleans — native on BigQuery / Postgres. MySQL: numeric — cast explicitly.
  • NULL parameters — handled automatically; no configuration.
  • Pre-flight — BigQuery: free cost estimate. Postgres / MySQL: SQL plan check.
  • Filters & cross-filter routing — identical.
  • Pagination protocol — identical; cost characteristics differ.
  • Aggregations / dialect features — delegated entirely to Malloy; no Looky-side branches.