Skip to main content

Datasets and SQL

A dataset is a saved SQL query whose result is cached and shared by every card built on top of it. You write the query once; the cards take care of their own charting. Authoring datasets requires the Builder tier (T4) or above.

Writing the query​

Open the dataset editor, give the dataset a name and write your SQL. The editor shows which dialect applies: queries against your BigQuery connection use BigQuery SQL, while queries over ingested sources such as connected Google Sheets run on the built-in query engine with its own SQL dialect. You can run the query from the editor to preview rows before saving -- preview runs are ad hoc and do not touch the cache.

The cached result​

The first time a card needs the dataset, Flynt runs the query and caches the rows. Every other card on the same dataset reads that cache, so ten cards cost one query, not ten. Two settings control it, both per dataset:

  • Cache lifetime -- how long a result stays fresh, 5 minutes by default. After it expires, the next builder request re-runs the query. Setting it to 0 disables caching for builders, so every fetch re-runs.
  • Max rows -- a guardrail on result size, 50,000 by default and capped at 200,000. A query that returns at least this many rows fails with a clear error: aggregate further upstream in your SQL or raise the limit.

Viewers (T1) are always served the cached result and never trigger a query run themselves -- a popular dashboard cannot stampede your warehouse. Builders can force a refresh at any time, which re-runs the query and updates the cache for everyone.

Query parameters​

Reference a parameter anywhere in your SQL with curly-brace placeholders:

SELECT order_date, SUM(amount) AS revenue
FROM sales.orders
WHERE order_date >= {start_date} AND region = {region}
GROUP BY order_date

Values are supplied at view time, typically by dashboard filters whose names match the placeholders. They are sent to the warehouse as proper query parameters -- never pasted into the SQL as text -- so quoting and injection are not your problem. Supported parameter types are date, datetime, text, number and select.

Two behaviours worth knowing:

  • A request that omits a value for a referenced placeholder fails with an error naming the missing parameter.
  • The cache is keyed per parameter combination, so the same filter values hit the cache and new values trigger a fresh run.

Editing updates every card​

Saving a change to a dataset clears its cache immediately. Every card bound to it picks up the new query the next time it loads -- there is nothing to redeploy or re-link. The flip side: renaming or removing a column your cards rely on breaks those cards, so check what is built on a dataset before reshaping it. Deleting a dataset is blocked while any card still references it.

Trust and visibility​

Datasets themselves carry no certification badge; trust is signalled one level up, where the cards and dashboards built on them can be certified -- see Certification and peer review. A dataset can also be marked sensitive, which restricts who can see it and its data -- see Sensitive data.