Why query design matters

Mediation executes the configured Snowflake query exactly as written. It does not automatically inject processing windows, timestamps, or checkpoint values into the query.

If the same query runs again and returns the same rows, those rows are processed again.

Example:

SELECT *
FROM CUSTOMER_USAGE;

Every time this query runs, all rows in the table are exported and processed. If no downstream deduplication exists, repeated runs can produce duplicate processing.

Option 1: Filter by time window

Use a date or timestamp filter when you want each run to process a defined time range, such as the current day, previous day, previous week, previous month, or a custom billing period.

Example:

SELECT *
FROM CUSTOMER_USAGE
WHERE DATE_TRUNC('month', created_at) =
  DATE_TRUNC('month', CURRENT_DATE());

Option 2: Maintain processing metadata

For recurring production workloads, many customers maintain a metadata table that stores the processing window for the next meter run. Your query can read that window and return only the next unprocessed range.

Example:

WITH bounds AS (
  SELECT
    start_datetime,
    end_datetime
  FROM meter_run
  WHERE latest = TRUE
)
SELECT t.*
FROM CUSTOMER_USAGE t
CROSS JOIN bounds b
WHERE t.created_at >= b.start_datetime
  AND t.created_at < b.end_datetime;

Option 3: External orchestration

Customers can also orchestrate Snowflake processing externally. Examples include Zuora Workflow, enterprise schedulers, CI/CD pipelines, and other workflow automation tools.

Filter at the source

Filtering can be performed directly in Snowflake using Query mode. Filtering at the source is generally preferred because less data is exported, transferred, and processed downstream. You can still use a Filter operator in the pipeline for additional record-level filtering.

Examples:

SELECT *
FROM CUSTOMER_USAGE
WHERE account_number = 'A1001'

SELECT *
FROM CUSTOMER_USAGE
WHERE DATE(event_time) = CURRENT_DATE()

Incremental processing and duplicates

The Snowflake source does not maintain processing offsets or automatically exclude previously processed rows.

If duplicate processing must be avoided, design the query so that each run retrieves only the intended processing window, or implement downstream deduplication using a unique business key.

Handle Snowflake column name case sensitivity

When you configure a Snowflake source, ensure that the column names returned by your query match the field names defined in your event schema.

By default, Snowflake converts unquoted identifiers to uppercase. As a result, a query such as SELECT accountNumber FROM accounts; returns the column name as ACCOUNTNUMBER, not accountNumber.

If your event schema expects accountNumber, Mediation cannot map the returned data correctly. The meter can still process the expected number of records successfully, but the field values in the audit trail, and in downstream processing that depends on those fields, can appear blank because of the name mismatch.

To preserve the required column names, use quoted aliases in your query.

SELECT accountNumber AS "accountNumber",
       chargeNumber AS "chargeNumber"
FROM accounts;

Alternatively, define your event schema by using the exact column names returned by Snowflake.

For more information about Snowflake identifier behavior, see Snowflake identifier requirements.

Example Query

SELECT accountNumber, chargeNumber FROM usage_data;

Snowflake output

ACCOUNTNUMBER
CHARGENUMBER

Event schema

accountNumber
chargeNumber

Because the event schema expects accountNumber while the query returns ACCOUNTNUMBER, the mapping fails and the audit trail displays blank field values.

Best Practices:

• Use quoted aliases (AS "fieldName") to preserve the desired column names.

• Ensure the event schema field names exactly match the column names returned by the query, including case.

• Verify the generated JSON output if you are unsure how Snowflake is returning column names.