SQL metrics

After you finish Step 1 (Metric Info), Step 2 (Configure Metric) appears in the main form:

1. Unlike most metrics, a custom SQL metric isn't automatically associated with a data asset. To give your metric a home in the Explorer tree, under Place metric in Explorer select a data asset.

2. Enter your SQL in the Custom SQL box. To produce a metric, your SQL clauses must have certain elements:

• The SELECT clause must have at least two output columns: (1) a time period equal to the desired aggregation interval, extracted from a time-based column; and (2) an aggregated value column. These become (1) the y-axis and (2) the x-axis of your metric's chart. Any additional output columns must not be aggregated; these can be used for slices.

• The WHERE clause must specify a date range using the time-based column and two tags: one producing the start of the range and another for the end of the range. These tags reflect the kind of time-based column you have: (1) use {start_ts} and {end_ts} for a timestamp column, (2) use {start_date} and {end_date} for a date column, and (3) use {start_datetime} and {end_datetime} for a datetime column.

• Don't forget to GROUP BY the aggregation interval column.

See the tabs below for simple SQL examples for each supported datasource.

Athena

SELECT

timestamp(date) as date,

count(*) AS count

FROM lightup_demo.dqfreshness

WHERE timestamp >= {start_ts}

AND timestamp < {end_ts}

GROUP BY date

BigQuery

SELECT

timestamp(date) as date,

count(*) AS count

FROM lightup_demo.dqfreshness

WHERE timestamp >= {start_ts}

AND timestamp < {end_ts}

GROUP BY date;

Databricks

SELECT

DATE_TRUNC('day', "timestamp" AT TIME ZONE 'UTC') AT TIME ZONE 'UTC' AS "day",

COUNT(*) AS "value"

FROM "lightup_demo"."dqfreshness"

WHERE "timestamp" >= {start_ts}

AND "timestamp" < {end_ts}

GROUP BY "day"

Microsoft SQL

SELECT

DATETRUNC(hh, "timestamp") AS "hour",

COUNT(*) as "value"

FROM

"lightup_demo"."dqfreshness"

WHERE "timestamp" >= {start_ts}

AND "timestamp" < {end_ts}

GROUP BY

"hour"

Oracle

SELECT

TRUNC("timestamp", 'DD') AS "day",

COUNT(*) AS "count"

FROM "lightup_demo"."dqfreshness"

WHERE "timestamp" >= {start_ts}

AND "timestamp" < {end_ts}

GROUP BY "day"

Postgres

SELECT

date_trunc('hour', "timestamp") AS "hour",

COUNT(*) as "value"

FROM

"lightup_demo"."dqfreshness"

WHERE

"timestamp" >= {start_ts}

AND "timestamp" < {end_ts}

GROUP BY

"hour"

Redshift

SELECT

DATE_TRUNC(hh, timestamp) AS "hour",

COUNT(*) AS "count"

FROM lightup_demo.dqfreshness

WHERE DATE_TRUNC(hh, timestamp) >= {start_ts}

AND DATE_TRUNC(hh, timestamp) < {end_ts}

GROUP BY "hour"

Snowflake

SELECT

TRUNC("timestamp", hh) AS "hour",

COUNT(*)

FROM "lightup_demo"."dqfreshness"

WHERE TRUNC("timestamp", hh) >= {start_ts}

AND TRUNC("timestamp", hh) < {end_ts}

GROUP BY "hour"

You can nest SELECT statements in your SQL. If you do, the clauses described above must be in the innermost SELECT statement.

3. When your SQL is ready, select Validate Query. If the query validates, you'll be able to select required setting values. Note that Seasonality and Slices are optional but recommended to improve query performance.

4. Add any tags, then in the top right corner select Next to proceed to Step 3 (Preview).

Step 3 (Preview)

1. Select View sample data to see a sample set of rows from the data asset. Lightup returns 100 rows of data from the source table, including metadata. Select Stop Viewing when you're done.

2. Optionally, adjust the date range for the preview. If you adjust the range, you must pick a start date and an end date.

3. Select Preview to see a chart of the metric for the date range from the preceding step.

After you're satisfied with the preview, select Create at the top right corner. The Explorer tab opens and displays your new metric in the Explorer tree.