Join warehouse and lake data with ChalkSQL
This demo shows how to query orders in Snowflake, sessions in BigQuery, and a Parquet catalog together. Choose a business question to inspect the answer, the SQL sent to each source, and the rows Chalk joins after those sources respond.
Follow one question across three data sources
Choose a business question. Read the result, then inspect what Snowflake, BigQuery, and the Parquet scan each returned. The first result is recorded; Run live executes the same query again. Fewer returned rows show how source-side filters reduce the data sent to Chalk.
Recorded run · 2,894 ms end to end
One result from BigQuery + Snowflake + Parquet on GCS. Read the answer below, then inspect the query and source work.
| product name | category | orders | revenue usd |
|---|---|---|---|
| Dynamic Rope 60m | Climbing | 83 | $28,884 |
| Harness | Climbing | 89 | $10,191 |
| Scree Trail Runner | Footwear | 29 | $5,960 |
| Ridgeline 2P Tent | Tents | 12 | $5,593 |
| Locking Carabiner 4-Pack | Climbing | 85 | $5,130 |
SELECT p.product_name, p.category,
COUNT(*) AS orders,
SUM(CAST(o.QUANTITY AS DOUBLE) * o.UNIT_PRICE_USD) AS revenue_usd
FROM (
SELECT customer_id, session_start
FROM BQ_CHALK_DEVELOP_TESTING.demo_cross_source_sql.web_sessions
WHERE campaign = 'crag-season'
) AS s
JOIN andymoreland_snowflake.SYNTHETIC_EXAMPLES.FERNWOOD_ORDERS AS o
ON o.CUSTOMER_ID = s.customer_id
JOIN read_parquet('gs://chalk-staging-test-data/demos/cross-source-sql/products.parquet') AS p
ON p.sku = o.SKU
WHERE o.ORDER_TS >= s.session_start
AND o.ORDER_TS < s.session_start + INTERVAL '2 hours'
GROUP BY p.product_name, p.category
ORDER BY revenue_usd DESC
LIMIT 5- Filter
- campaign = 'crag-season'
- Columns
- customer_id, session_start
- Filter
- none pushed: every row is read
- Columns
- CUSTOMER_ID, SKU, QUANTITY, UNIT_PRICE_USD, ORDER_TS
- Filter
- none pushed: every row is read
- Columns
- sku, product_name, category
- 1Inner hash join on s.customer_id = o.CUSTOMER_ID, keeping pairs where o.ORDER_TS >= s.session_start AND o.ORDER_TS < s.session_start + INTERVAL '2 hours'
- 2Inner hash join on o.SKU = p.sku
- 3Aggregate by p.product_name, p.category
- 4Sort
Built on Chalk
Chalk plans the cross-source query, pushes supported work into each source, and joins the returned rows. The example supplies a fictional retailer’s orders, sessions, and catalog.
Read the connected sources
Snowflake and BigQuery appear as tables through their integrations. ChalkSQL reads the Parquet catalog directly from GCS; the example does not copy the sources into a new database.
Push supported work to each source
The planner sends each source the columns and filters it can evaluate. Inspect the source SQL and returned row counts to see which work ran there.
Join the returned rows
Chalk’s vectorized engine combines rows across systems and applies the remaining joins, time conditions, aggregates, and sorts in one plan.
Use integration permissions
Each source is read with its own integration credentials. Those permissions determine which data the cross-source query can access.
Query the integrations you already have
Any data source connected to your Chalk environment is already a ChalkSQL table. Run a query from the SQL editor in the dashboard, from Python with run_sql, or from the chalk CLI, and put EXPLAIN in front of it to see what each source will be asked to do.
# Data sources are Chalk integrations. Name them once; ChalkSQL exposes every
# table they can see as <integration>.<schema>.<table>.
from chalk.sql import BigQuerySource, SnowflakeSource
finance_snowflake = SnowflakeSource(name="finance_snowflake")
marketing_bq = BigQuerySource(name="marketing_bq")
# Files need no integration: read_parquet('gs://...') or ('s3://...') is a table too.How can I keep more work in each source?
Filters on a source's own columns
=, <>, <, <=, >, >=, IS NULL and IS NOT NULL against a literal, combined with AND and OR, become a WHERE clause in the source's SQL. Spell short value lists out with OR.
Filter in a subquery when joins compare ranges
When a join also has a time window, write each source's filters in a subquery over that source. They are pushed into the source's SQL instead of applied after the join.
Select what you use
Column lists are pushed to every source, Parquet included. A query that names six columns moves six columns over the network, however wide the table is.
Ask the question, not the pipeline
Bring the warehouses and lakes you already run. Talk to an engineer about querying them together with ChalkSQL.