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"
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 (for example, 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 the example below. Note the braces around {{block1}}, these will be replaced by your block at run time.

    select a.*
from
{{block1}} as a

  2. A new Parameters section appears in the right sidebar. Select Block as the type, then find the title of the block you want to use. If your block does not appear in the list, make sure the block is not untitled.

    Add Block as parameter

  3. Run the query. This query will only return the results of the prior block, but you can now start joining to other blocks and source types (for example, a Sheet or CSV). To join, make sure you give the block as alias.

    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

Was this page helpful?
×