Functions and types

FastBI catalog names

Use connector.dataset.table names from Data explorer. These examples use the Lake connector in the SQL workbench. See the SQL reference and compatibility guide.

Use these patterns in the SQL workbench. Function names in other connected sources can differ.

Aggregate values

• COUNT(*) counts rows, including rows with null values.
• COUNT(column) counts non-null values in that column.
• COUNT(DISTINCT column) counts unique non-null values.
• SUM, AVG, MIN, and MAX summarize values.

SELECT COUNT(*) AS rows,
       COUNT(customer_id) AS rows_with_customer,
       COUNT(DISTINCT customer_id) AS unique_customers,
       SUM(revenue) AS total_revenue
FROM lake.uploads.orders;

Conditional counts

Use CASE inside an aggregate for conditional counts. Lake does not use BigQuery’s COUNTIF syntax.

SELECT SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) AS completed,
       SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled
FROM lake.uploads.orders;

Nulls and safe division

COALESCE(value, fallback) supplies a default for a null value. NULLIF(value, 0) returns null when the denominator is zero.

SELECT SUM(revenue) / NULLIF(COUNT(DISTINCT customer_id), 0)
         AS revenue_per_customer
FROM lake.uploads.orders
WHERE status = 'completed';

Null can mean unknown or unavailable; do not automatically turn it into zero unless that matches the business definition.

Cast values

CAST converts a value to a type. The FastBI SAFE_NUMBER function returns null for invalid numeric text.

SELECT order_id,
       SAFE_NUMBER(revenue_text) AS revenue
FROM lake.uploads.imported_orders
LIMIT 100;

Check failed conversions before aggregating: aggregates can silently omit null values. When applying string functions to numeric identifiers, cast first, for example substr(CAST(customer_id AS VARCHAR), 1, 3). A cast cannot restore leading zeros already lost during ingestion.

Dates

For daily totals, a date cast can group timestamps by day:

SELECT CAST(order_timestamp AS DATE) AS day,
       SUM(revenue) AS revenue
FROM lake.uploads.orders
GROUP BY CAST(order_timestamp AS DATE)
ORDER BY day
LIMIT 1000;

Confirm timestamp storage and timezone semantics before grouping. Prefer a source field already normalized to the reporting timezone when available.

Text and regular expressions

Use LOWER, UPPER, and TRIM to normalize text. For grouped text, deduplicate input in a CTE before applying STRING_AGG.

Continue with scalar functions and aggregate functions.