SQL Models

SQL models let you write queries against your BigQuery tables to make custom views, aggregations, and transformations. You can sync the output to any compatible destination.

Creating a SQL transformation

1. Open Studio.
2. Click New Data Model.
3. Select SQL.
4. Enter a name and an optional description.
5. Choose the Materialization: Live view or Materialized table.
6. Write your SQL query.
7. Click Validate to check the query against your BigQuery schema.
8. Click Save Model.

Writing Queries

Your SQL runs against BigQuery. Reference tables in your Vendo dataset directly:

SELECT
  customer_id,
  SUM(total_price) AS lifetime_value,
  COUNT(*) AS order_count,
  MIN(created_at) AS first_order_date,
  MAX(created_at) AS last_order_date
FROM orders
WHERE financial_status = 'paid'
GROUP BY customer_id

Validation

When you click Validate, Vendo:

1. Does a dry run of the query against your BigQuery schema.
2. Returns the column names and types.
3. Shows up to 10 preview rows.
4. Shows syntax and schema errors.

Fix all errors before you save. You can sync only validated models to destinations.

Incremental Syncing

For large datasets, use incremental syncing so that Vendo does not process all data again on every run. For a Materialized table, set these fields in the Refresh tab:

• Sync mode: Full processes all input data again on every run. Incremental reads only the last rolling window of data.
• Rolling window (days): the number of days that an incremental run reads.

Scheduling

A Live view always reads current source data. It does not run as a pipeline job.

A Materialized table runs when:

• Manually triggered: click Run on the model detail page.
• After a source syncs: the table refreshes after its sources finish syncing.
• After an upstream model: set the run policy to Wait for all upstreams or Run on any upstream.

Example: Daily Revenue by Channel

SELECT
  DATE(o.created_at) AS date,
  COALESCE(a.utm_source, 'direct') AS channel,
  COUNT(DISTINCT o.id) AS orders,
  SUM(o.total_price) AS revenue
FROM orders o
LEFT JOIN events a
  ON o.customer_id = a.user_id
  AND a.event = 'checkout_started'
WHERE o.financial_status = 'paid'
GROUP BY date, channel
ORDER BY date DESC

Related

• Data Studio
• Python Models: for advanced transformations with pandas
• Audiences: for user lists that you sync to ad platforms