Pipeloom DocsQueries
Write SQL or use the Builder against Pipeloom's data or your warehouse, chart the result, and save it.
Open Insights → Queries → New query. Pick where it runs: Pipeloom (your syncs and runs, see the data reference) or one of your warehouses.
The schema browser on the left lists the tables and their columns. Click a column to insert it. Press ⌘↵ (Ctrl+Enter) to run.
The Builder
For Pipeloom data you don't have to write SQL. The Builder tab beside SQL lets you pick a measure, what to break it down by, and the time grain. It writes the SQL for you, and you can see it. Editing that SQL yourself turns the Builder off for the query.
Charts
A result shows as a table or a chart: number, line, bar, area or pie. The first run picks one from the shape of the result. A time column with numbers becomes a line, a category with a number a bar, and a single number a number tile. You can change it, and set the axes, the series and whether bars are stacked.
Variables
Variables make one query work for many questions. Write them in SQL as {{ name }}. Each variable
gets a picker above the result.
| Type | Picker | In SQL |
|---|---|---|
| Date range | Presets (today, last 7 days, this month…) or two dates | {{ date_range(column) }}, which becomes column >= from and column < to |
| Time grain | Hour, day, week, month, quarter, year | {{ grain(column) }}, the warehouse's own date_trunc |
| List | Fixed values, one or many | {{ name }} |
| Text, number | An input box | {{ name }} |
| Pipeline, connection, environment, warehouse | Pipeloom's own pickers: you pick by name, the id is used | {{ name }} |
Wrap an optional part in [[ … ]]. It is left out when its variables have no value, so "all
pipelines" needs nothing special:
select {{ grain(started_at) }} as period, sum(records_loaded) as records
from syncs
where {{ date_range(started_at) }}
[[ and pipeline_id in ({{ pipeline }}) ]]
group by 1
order by 1Values are always passed to the database as parameters, never pasted into the SQL, so a text variable can't change what the query does.
Saving
Save keeps the query in the environment, in a folder if you give it one. From the editor you can also add the query to a dashboard. Saved queries can be used by dashboards, monitors and Data check steps.