Skip to main content

Pipelines

A Pipeline is a Plot whose result is produced once and kept, instead of computed again for every reader. Turn the Pipeline switch on beside a Plot’s results, or set the Plot’s requiresReportingRun field to true through the Piece tools; that field is the Pipeline switch. A Plot that is not a Pipeline reads the Pools live each time it runs, and a Plot read as a view runs its SQL again inside every read that names it. Parables are product experiences, and speed is part of the product: a Page that keeps its reader waiting has failed, however right its numbers are. So make a Pipeline of any query that is expensive to run on every read, and judge expense with what you know about query cost: how much data it scans, how many tables it joins, how much it aggregates, whether it calls a model, and how many Plots and Pages would repeat it. Estimate a Plot query before running it measures part of that before the query runs: the bytes it reads, their compute cost, and the most paid AI calls it can make. A measured read that is slow settles the question. Also make a Pipeline of any number everyone must read the same way. Stored data is honest about its age: every reader sees when the data they read was produced, and people with access can refresh it. A Pipeline’s output is as fresh as its last Report run, so schedule runs as often as the Pages need. Measure when you can. While the person records in the editor’s Performance tab, each of their Messages carries the recording, and you can read it: every Page load, switch, Page open and press, with each Plot read’s time, rows and size, whether it was live or stored, and where its server time went. A live read that held a Page back on each open is the measurement that settles the question, and the recording’s hints name it. Read it before recommending a Pipeline, and say which reads you measured. When a Message carries no recording, ask the person to turn on Record in the Performance tab and open the Page again. A Pipeline runs as part of a Report run. Each run captures the published Version and every Pipeline’s SQL and inputs, keeps the moment it started, runs the Pipelines against the Provider Pool as of one time, and stores each Pipeline’s output with its row count, cost, and provenance. A Pipeline reads Provider, Plot and artifact tables at that time, a Pipeline of another Parable from that Parable’s newest Report run completed by that time, each Workspace table (such as workspace.users) once when the run plans it, and each Pane’s saved rows by name (parables.<parable>.<pane>, This Parable’s over the Parable’s Default, and _by_actor for every team’s) as they were when the run started, never a person’s own; Prepare refuses a Pipeline that reads anything else, such as information_schema, and names the table. Readers then see as Current the output in the newest completed Report run of the Release’s major line, or choose a specific run in the Report picker. A minor Release reads the runs of earlier Releases in its major line. A major Release needs a run of its own. A Pipeline starts from the Provider Pool. When this Parable’s Pipelines permission is off, in Staff Settings, no Plot can become a Pipeline, no Refresh or Preview runs, and no schedule can be added or changed. Do not propose one. A schedule can still be turned off or deleted, and a Pipeline can still become a plain Plot. Plots read live data, and stored Refreshes stay readable. When this Parable’s Pools permission is off, a Pipeline that reads the Provider Pools fails; read Artifacts, Workspace data, Pane data or other Parables’ Refreshes instead. A Pipeline keeps the same contract as any Plot: the Pane’s Perception declares its parameters and columns, and its SQL must return exactly those columns with those types. Select the declared columns by name and cast them to the declared types. SELECT * stores every Provider column, and every read of that output fails against the contract. A Pipeline can read another Pipeline of the same Report: the run is a graph, so a Pipeline runs after the Pipelines it reads and reads what they wrote in that run. Pipelines that read each other in a cycle are refused. A Pipeline can also read Plots of its Parable that are not Pipelines: the run reads each as a view of its SQL from the captured Version, so a chain of plain Plots feeding one Pipeline stores only the result. A Provider table that has never been written, whose Provider the Workspace has not connected, or whose stream the Workspace has not turned on, reads as an empty table with its declared columns, so the run still completes and its Pipelines store no rows for that input. A hierarchy, such as everyone below each manager at any depth, is a Pipeline with a recursive query: WITH RECURSIVE, its two terms joined by UNION. UNION stops when an iteration adds no new row, so a loop in the data, such as a person listed as their own manager, ends; UNION ALL is refused with QL-PL-014. Order the output by the column readers look it up by, so each reader’s lookup reads few rows, and let plain Plots read it for each reader.
  1. Work down from the experience. For each Page, list what a reader must see first and the Plots behind it, then trace each Plot to the queries it needs. Store each expensive one as a Pipeline. Leave live only cheap reads over stored outputs: reader-specific filters, per-person settings, and small lookups.
  2. Declare each Pipeline’s columns in the Pane’s Perception, write SQL that returns exactly those columns, and preview the Plot against real data.
  3. Turn on the Pipeline switch and Preview the Report on your Changes: every Pipeline runs in order on a sample, and the Pages read its outputs when you choose it in the Refresh menu (see the Reports Power). A Preview captures each Pipeline’s SQL and the shared settings when it starts, so an edit or a settings save after that does not show in it. Fix what reads wrong and Preview again.
  4. Publish the Version, then run a Report or schedule one, and wait for it to complete. A Report needs no setup. Until a Version’s first run completes, its Pages show Power on Parable. Read each Pipeline through Current and through that run, and compare the row counts with the run’s results.
  5. After changing a Pipeline’s SQL or columns, publish and run again; Current reads only outputs that fit the published definition.
A good Pipeline returns exactly its declared columns, completes on a Workspace that lacks some Providers, is read through Current by the plain Plots and Panes that use it, and never fakes a result: an output that does not fit its definition is unavailable, not substituted. A good Parable opens every Page fast on the Workspace’s full data, because its heavy work comes from stored outputs.

Ids across Refreshes

Use parable_stable_id(namespace, natural_key) when an identity has a stable natural key. It hashes the namespace and key with SHA-256. A new record with the same key gets the same id, even when it has an earlier creation time. Two runs over the same keys get the same ids. No prior Refresh is needed. Choose one namespace for the definition. Normalize the key before hashing it. For example, use parable_norm_email(email) for a work-email key. The hash does not normalize text. A NULL or empty namespace or key returns NULL, not an id shared by all missing records. Do not replace a missing natural key with the same empty string for every record. The promise needs the same nonempty key under the same definition. A changed key or definition may give a new id. A fuzzy merge or a merge across several keys has no such promise unless every reader can derive the same natural key. A representative record, its creation time, or the smallest visible record key does not provide that guarantee. Switching from representative ids to stable-key ids is a definition change. Old results keep their old ids. Refresh stored vertex and edge Plots together under the new definition. The function does not rewrite old results. A stable id grants no access. Restricted readers must read a live Plot whose sources their Policies filter before it chooses values. They may see fewer records and different values for the same id. A stored Refresh still refuses a reader whose Provider Policies or source permissions narrow its sources with QL-TN-007. A live view over such a stored result does not bypass that refusal. Use live sources for that path too. A Pipeline still cannot read its own last completed output. Reading its own Plot is a dependency cycle, including a read through a plain Plot. Stable-key ids add no identity map and change no Refresh or retention rule.

Incremental Pipelines

An incremental Pipeline stores its output as time intervals in one table. When a source changes, Parable runs again only the intervals that the change touched, and replaces only their rows. A Refresh does not run an incremental Pipeline. It pins the table as the newest interval run left it, and the Pipelines that read it run against that pin. Make a Pipeline incremental when a full run is slow or costly and each output row belongs to one moment: an event, or a total for one hour or one day. A full run reads and computes every row again. An interval run reads and computes only the rows of its interval. A Pipeline that reads a large event table, or calls an AI function on each row, gains the most. The AI functions are allowed: the inference log keeps each answer by its inputs, so a second run of an interval reads the same answers. Keep a Pipeline full when its rows do not each belong to one moment: the current state of each record, a lifetime total for each person, or a ranking across all time. Declare it in the Plot’s incremental field through the Piece tools, beside requiresReportingRun. Only a Pipeline can declare it. A save that declares it on a Plot that is not a Pipeline is refused. A Parable whose Home Page declares a react contribution cannot declare it either, because a Refresh about a Patch cannot use an incremental Pipeline. The fields:
  • by: the output column that holds each row’s event time. It must be a TIMESTAMP column (Temporal.DateTime), not a DATE. Each interval run keeps only the output rows whose by falls in that interval. So derive by from the time column of a source, and cast it: CAST(date_trunc('hour', occurred_at) AS TIMESTAMP) AS event_hour. Without the CAST, the check cannot derive the column’s type and reports PV_INC_011.
  • grain: the length of one interval, as a duration such as 15m, 1h or 24h. A duration has no day unit, so a day is 24h. Intervals count from 1970-01-01 in UTC, so 24h intervals start at midnight UTC and 168h intervals start on a Thursday. Make the grain equal to the bucket that by holds, or a whole multiple of it. Hourly totals need 1h or 24h. With 15m, each hourly total holds only its first 15 minutes of events. Raw event rows fit any grain. For weekly totals, store daily rows at 24h and add them up to weeks in a plain Plot. A finer grain runs less data for each change, but it makes more runs, and the intervals of one Pipeline run one at a time. A Pipeline that reads another incremental Pipeline needs the same grain or a coarser one.
  • sources: each table that the Pipeline reads by event time. source is the table exactly as the SQL names it: providers.<schema>.<table>, artifacts.<table>, or parables.<parable>.<plot> for an incremental Pipeline of the same Parable. timeColumn is the TIMESTAMP event-time column of that table, or the DATE column _parable_event_date when grain and the source’s lookback are whole numbers of days. No other DATE column can be a timeColumn: a change marks intervals by the table’s event time, and _parable_event_date is the UTC day of that time, but a business date such as due_date is not. So a DATE source limits the Pipeline to whole-day grains. An interval run reads only the rows whose timeColumn falls in the interval, widened back by lookback. A row with a NULL timeColumn is in no interval. List every large event table here, and each of its child tables (<table>__<child>) that the SQL reads. A child table holds its parent’s event time under the same name. Check that column’s type in the catalog first, because the column stays text until a person approves the tap’s rebuild. When the catalog gives the parent’s type, declare that column, and join the child to its parent on parent_pk and that column. Each interval run then reads only the files of the child that overlap the interval. When the catalog gives text, a save reports PV_INC_011 and each interval run fails, so declare timeColumn: _parable_event_date, and join the child on parent_pk and _parable_event_date. Each interval run reads a table that is not in sources in full, and onSourceChange decides what a change to that table does.
  • lookback: in each source, how far before the start of the interval the run also reads. The default is 0s. It cannot be negative. Set it when the rows of an interval depend on earlier rows, such as a LAG to the previous event or a rolling window. A 7-day rolling count of daily rows needs 144h. Rows that the run reads only for the lookback never enter the output. A lookback also widens what a change runs again: a change at one time runs every interval whose read window holds that time. Use the shortest lookback that the SQL needs. A LAG whose previous row is older than the lookback reads NULL. A source with a DATE timeColumn needs a lookback of whole days, such as 0s or 24h.
  • onSourceChange: what a change does when the Pipeline does not read the changed thing by time. That thing is a table that is not in sources (a roster, a mapping, a dimension), a shared setting, or a Pane row. RESTATE, the default, runs every interval again, so all history uses the new state. FORWARD_ONLY gives the new state only to intervals that run after the change. An earlier interval keeps what it read until a change in its own time range runs it again. Choose RESTATE when history must agree with the current state, such as a corrected category mapping. Choose FORWARD_ONLY when each interval must keep what was true when it ran, or when the source changes often. With RESTATE, a roster that syncs every hour runs every interval again every hour. A restate whose estimated cost of paid AI calls is above the Workspace’s Refresh cost threshold waits in the Pipeline’s History until a person with the budget override permission and the Primary seat approves it. A restate at or below the threshold runs with no approval. You cannot approve a restate.
  • quietMinutes and maxDelayMinutes: the debounce. A changed interval runs when its sources stay unchanged for quietMinutes (default 2), or when its oldest change is maxDelayMinutes old (default 15). maxDelayMinutes is at least quietMinutes. Keep the defaults unless the person wants fewer, later runs.
  • split: optional. It divides each interval into parts (2 to 1024) by a hash of key, an output column such as a person or account id. Use it only when one interval is too large for one run. Every GROUP BY and every window PARTITION BY must include the key. The outermost SELECT cannot aggregate without a GROUP BY, run a window without a PARTITION BY, or end in LIMIT, OFFSET or FETCH. A Plot that groups by ROLLUP, CUBE or GROUPING SETS cannot split. The parts run one at a time, so a split lowers the memory of each run, not the time of the interval. Decide on split before the first publish. After the line publishes the Pipeline as incremental, a save that adds, removes or changes split is refused.
Two runs of one interval must give the same rows. So the SQL cannot read the clock, a random value, the calling run or a search index: now(), current_date, random(), uuid(), parable_context(), request_principal(), search_available_data() and search_everything() are refused (PV_INC_006). Do not bound the time range with now(). The interval bounds each run. embed() is refused too (PV_INC_007). An interval run refuses a Pipeline that reads a Pipeline that is not incremental, or a Pipeline of another Parable. Read their sources directly, or make the upstream Pipeline of this Parable incremental too. Saving, Preview and Prepare check the declaration against the SQL. Read a Piece’s problems lists each PV_INC finding, and each finding holds Publish. The table starts empty. An interval gets a row only when a change lands in its time range after the publish. Until the first interval completes, a Refresh runs the Pipeline in full, as it runs any Pipeline. After that, the Refresh pins the table, and the table holds only the intervals that changed since the publish. History from before the publish needs a backfill. A person with the budget override permission and the Primary seat starts it in the Pipeline’s History: Backfill, under Intervals, with a From and a To day. The dialog counts the intervals before they confirm. A backfill spends the Refresh budget, so you cannot start one. So before you make a Pipeline incremental, tell the person that readers see only new intervals until the backfill, and suggest the days to backfill. The intervals run in parallel, on up to half of the Workspace’s query-run slots so that a Refresh does not wait, and their commits to the table take turns, so readers see part of the history until every interval completes. One backfill marks at most 100,000 interval runs. A publish that changes the SQL, the parameters, the Pane’s Policies, the output columns, a plain Plot that the Pipeline reads, or by, grain or sources builds a new table. Every interval of the current table runs again into it. Reports read the current table until every interval of the new one is complete. Before the publish, the change preview lists how many intervals run again and their estimated cost. Ponder reads the same list with Estimate what publishing these Changes would run again. A change of quietMinutes, maxDelayMinutes or onSourceChange keeps the table.
  1. Check that each output row belongs to one moment, and that the SQL can give the same rows each time an interval runs. If not, keep a full Pipeline.
  2. Write the SQL. Derive by from the time column of a source, and keep each bucket inside one interval. Declare incremental in the same save that sets requiresReportingRun to true, or on a Plot that is already a Pipeline.
  3. Fix each PV_INC finding. Then Preview the Report. A Preview runs every Pipeline in full on a sample, incremental ones too, so it checks the SQL and the contract, not the intervals.
  4. Tell the person about the backfill, then publish. After the publish, point them to Backfill in the Pipeline’s History, with the days you suggest.
  5. Ask the person to open the Pipeline’s History. Intervals shows the state, rows and error of each interval. Every interval runs again shows a restate that waits, with its reason and its estimated cost.
A good incremental Pipeline gives the same rows each time an interval runs, lists in sources every event table that it reads by time, uses the shortest lookback that its windows need, and has readers who know which history its table holds. Related Powers: Plots, Reports, Pages, Perceptions, Providers, Publishing. Find each by its title; do not guess stored names.