Writing Custom Queries

Warehouse and database data sources like BigQuery, Snowflake, 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. How these are referenced depends on your warehouse:

BigQuery

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

WHERE created_at BETWEEN @start_datetime AND @end_datetime

Snowflake, PostgreSQL, MySQL, and SQL Server

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

WHERE created_at BETWEEN ? AND ?

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
  order_id AS id,
  email,
  order_total AS value,
  created_at AS occurred_at
FROM orders
WHERE created_at BETWEEN @start_datetime AND @end_datetime

Orders (Snowflake)

SELECT
  order_id AS id,
  email,
  order_total AS value,
  created_at AS occurred_at
FROM orders
WHERE created_at BETWEEN ? AND ?

Email engagement (BigQuery)

SELECT
  event_id AS id,
  recipient_email AS email,
  sent_at AS occurred_at
FROM email_events
WHERE event_type = 'open'
  AND sent_at BETWEEN @start_datetime AND @end_datetime

Refunds with customer ID fallback (BigQuery)

SELECT
  refund_id AS id,
  COALESCE(customer_email, NULL) AS email,
  CAST(customer_id AS STRING) AS customer_id,
  refund_amount AS value,
  refunded_at AS occurred_at
FROM refunds
WHERE refunded_at BETWEEN @start_datetime AND @end_datetime

Tips

• Always filter by the time window. This allows WasteNot to sync incrementally instead of re-reading your entire dataset every time.
• 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.