❓
Knowledge Base
🥋

Query blocks

You can use existing blocks as the source for a new block. This means you can query across databases and different source types. For example:

  • Join the results of a MySQL query to the results of a Postgres query
  • Join the results of a Google Sheet to the results of a Postgres query
  • Join a CSV, MySQL table and Postgres table

Here's an example with three blocks:

block1 - Google Sheets

select some_col, another_col
from {{some_sheet}}

block2 - Postgres

select some_col, post_col
from postgres.table

block3 - MySQL

select some_col, my_col
from mysql.table

block4 "Sheets / CSV / Block"

image
select a.some_col, a.another_col, b.post_col, c.my_col
from
block1 as a inner join
block2 as b on a.some_col = b.some_col left join
block3 as c on a.some_col = c.some_col

💡
We use
🕹️
SQLite
 as the database engine when querying blocks.

Demo

💡
Please note, large datasets will slow down performance. You can speed things up by only selecting the necessary columns in the Block (e.g. list the columns you need instead of SELECT *) and by using a WHERE clause to filter for just the rows you need

This can be a powerful way to pull from disparate data sources without the overhead of setting up ETL pipelines. And as with any block, you can send the results of the query to any destination.

Tutorial

  1. Start by writing a query like below. Note the braces around {{block1}}, these will be replaced by your block at run time.
    2. select a.*
from 
{{block1}} as a
  2. You'll notice a new Parameters section on the right sidebar. Select "Block" as the type then find the block title you want to use (if you don't see your block in the list, make sure you've titled it)
    3. image
  3. Run the query! This will just return the results of the prior block, but you can now start joining to other blocks and source types (e.g. a Sheet or CSV). To join make sure you give the block as alias
    4. select a.email, b.revenue
from 
{{block1}} as a left join
{{block2}} as b on a.email = b.email

Demo / Case Study

A SaaS company has revenue projections for each customer by email address built in a Google Sheet. An analyst want to join that Sheet to data from Postgres and another Sheet containing a "VIP" flag. In summary, the analyst wants the following pieces of data:

  • Customer (Postgres)
    • email
    • channel
    • plan
  • Revenue (Sheets)
    • email
    • revenue
  • VIP status (Sheets)
    • email
    • vip_status