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 as the database engine when querying blocks.
SQLite
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 needThis 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
- Start by writing a query like below. Note the braces around
{{block1}}
, these will be replaced by your block at run time. - 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)
- 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
select a.*
from
{{block1}} as a
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)
- channel
- plan
- Revenue (Sheets)
- revenue
- VIP status (Sheets)
- vip_status