Writing Custom Queries

Warehouse and database data sources like BigQuery, Snowflake, Redshift, PostgreSQL, MySQL, and SQL Server let you write custom SQL queries to pull exactly the data you need into WasteNot. This guide covers how to structure your queries so WasteNot can process the results correctly.

How it works

When you create a stream on a warehouse data source, you provide a SQL query. WasteNot runs this query on a schedule, and each row in the result set becomes an event in WasteNot. These events are what power your audience rules.

WasteNot passes two timestamp parameters to your query for incremental syncing: the start and end of the time window to fetch. Use a half-open range that includes the start and excludes the end. This keeps an event on the boundary from being selected in two consecutive runs.

BigQuery

BigQuery uses named parameters. Reference them with @start_datetime and @end_datetime:

WHERE created_at >= @start_datetime
  AND created_at < @end_datetime

Snowflake and MySQL

These databases use positional placeholders. The first ? is the start time, the second ? is the end time:

WHERE created_at >= ?
  AND created_at < ?

PostgreSQL and Redshift

Use $1 for the start time and $2 for the end time:

WHERE created_at >= $1
  AND created_at < $2

SQL Server

Use @1 for the start time and @2 for the end time:

WHERE created_at >= @1
  AND created_at < @2

Required columns

Your query must return rows with the following columns. Column names are case-insensitive.

id (required)

A unique identifier for each row. This must be stable across runs — the same event should always produce the same ID. WasteNot uses this to deduplicate data.

occurred_at (required)

A timestamp for when the event happened. WasteNot uses this for time-based audience rules (e.g., "customers who ordered in the last 30 days"). This should be a TIMESTAMP or DATETIME type.

email or phone or customer_id (at least one required)

WasteNot needs a way to identify who the event belongs to. Provide at least one of:

• email — The customer's email address (preferred, used for ad platform matching)
• phone — The customer's phone number
• customer_id — An internal customer identifier from your system

WasteNot resolves identity in this priority order: email first, then phone, then customer_id. If you can provide email, that gives the best match rates when syncing audiences to ad platforms.

value (optional)

A numeric value associated with the event (e.g., order total, charge amount). Defaults to 0. Used for value-based audience rules like "customers who spent more than $100."

Event types

When creating a stream, you choose an Event Type that tells WasteNot what kind of data the query returns. This determines how the data is used in audience rules. You can select from commerce events (orders, refunds, charges), email events (opens, clicks, bounces), SMS events, and session events. The full list is available in the stream creation form.

Example queries

Orders (BigQuery)

SELECT
  CAST(order_id AS STRING) AS id,
  customer_email AS email,
  order_total AS value,
  created_at AS occurred_at
FROM `my_project.shopify.orders`
WHERE created_at >= @start_datetime
  AND created_at < @end_datetime

Orders (Snowflake)

SELECT
  order_id AS id,
  email,
  order_total AS value,
  created_at AS occurred_at
FROM orders
WHERE created_at >= ?
  AND created_at < ?

Email engagement (BigQuery)

SELECT
  CAST(event_id AS STRING) AS id,
  recipient_email AS email,
  opened_at AS occurred_at
FROM `my_project.email.events`
WHERE event_type = 'open'
  AND opened_at >= @start_datetime
  AND opened_at < @end_datetime

Refunds with customer ID fallback (BigQuery)

SELECT
  CAST(refund_id AS STRING) AS id,
  customer_email AS email,
  CAST(customer_id AS STRING) AS customer_id,
  refund_amount AS value,
  refunded_at AS occurred_at
FROM `my_project.shopify.refunds`
WHERE refunded_at >= @start_datetime
  AND refunded_at < @end_datetime

Tips

• Always use the half-open time window. Include the start and exclude the end so WasteNot can sync incrementally without selecting boundary events twice.
• Use stable IDs. If the same row gets a different id on each run, WasteNot will create duplicates.
• Provide email when possible. Email gives the best match rate when syncing audiences to ad platforms like Google Ads and Facebook.
• Keep queries efficient. WasteNot runs these on a schedule, so make sure your query performs well on your warehouse. Use partitioned or clustered tables where possible.

Creating a stream

1. Go to your warehouse data source detail page in WasteNot
2. Click New Stream
3. Enter a Name for the stream
4. Select the Event Type that matches your data
5. Paste your SQL query
6. Enable the stream and click Save

WasteNot will begin running your query on a schedule and syncing the results automatically.