Enrichment¶

In Cantrip, enrichment dimensions are virtual or external dimensions that can be joined with metrics based on implicit conditions like time and/or space. They allow you to do operations like computing a metric on a monthly time grain; rolling up a metric per ZIP code when all you have is latitude/longitude; or even computing metrics only when it was raining. It’s a very powerful feature, and it opens up the exploratory analyses you can do on your data.

Database selector loading…

Virtual¶

Let’s use a fact table similar to the one in the previous example, with added time and space columns:

PRAGMA foreign_keys = ON;

SELECT InitSpatialMetaData(1);

CREATE TABLE fact_orders (
  id INTEGER PRIMARY KEY,
  amount INTEGER,
  event_time DATETIME,
  latitude REAL,
  longitude REAL,
  city TEXT
);

SELECT AddGeometryColumn('fact_orders', 'geom', 4326, 'POINT', 'XY');
SELECT CreateSpatialIndex('fact_orders', 'geom');
UPDATE fact_orders SET geom = MakePoint(longitude, latitude, 4326);

INSTALL spatial;
LOAD spatial;

CREATE TABLE fact_orders (
  id INTEGER PRIMARY KEY,
  amount INTEGER,
  event_time TIMESTAMP,
  latitude REAL,
  longitude REAL,
  city TEXT,
  geom GEOMETRY
);

UPDATE fact_orders
SET geom = ST_Point(longitude, latitude);

CREATE EXTENSION IF NOT EXISTS postgis;

CREATE TABLE fact_orders (
  id INTEGER PRIMARY KEY,
  amount INTEGER,
  event_time TIMESTAMP,
  latitude REAL,
  longitude REAL,
  city TEXT,
  geom geometry(Point, 4326)
);

UPDATE fact_orders
SET geom = ST_SetSRID(ST_Point(longitude, latitude), 4326);

Together with this metric:

CREATE VIEW total_orders (total_orders) AS
SELECT SUM(amount) AS "total_orders [type=INTEGER]"
FROM fact_orders;

CREATE VIEW total_orders AS
SELECT SUM(amount) AS total_orders
FROM fact_orders;
COMMENT ON COLUMN total_orders.total_orders IS 'type=INTEGER';

Let’s see which dimensions are available to total_orders:

% curl 'http://localhost:1974/db/dimensions?q=metrics:!(total_orders)' | jq

{
  "database": "db",
  "dimensions": [
    {
      "name": "Time.day",
      "type": "DATE",
      "grain": "day",
      "incremental": false,
      "description": null
    },
    {
      "name": "Time.hour",
      "type": "DATETIME",
      "grain": "hour",
      "incremental": false,
      "description": null
    },
    {
      "name": "Time.minute",
      "type": "DATETIME",
      "grain": "minute",
      "incremental": false,
      "description": null
    },
    {
      "name": "Time.month",
      "type": "DATE",
      "grain": "month",
      "incremental": false,
      "description": null
    },
    {
      "name": "Time.second",
      "type": "DATETIME",
      "grain": "second",
      "incremental": false,
      "description": null
    },
    {
      "name": "Time.year",
      "type": "DATE",
      "grain": "year",
      "incremental": false,
      "description": null
    },
    {
      "name": "fact_orders.amount",
      "type": "INTEGER",
      "grain": null,
      "incremental": false,
      "description": null
    },
    {
      "name": "fact_orders.city",
      "type": "STRING",
      "grain": null,
      "incremental": false,
      "description": null
    },
    {
      "name": "fact_orders.event_time",
      "type": "DATETIME",
      "grain": null,
      "incremental": false,
      "description": null
    },
    {
      "name": "fact_orders.geom",
      "type": "POINT",
      "grain": null,
      "incremental": false,
      "description": null
    },
    {
      "name": "fact_orders.latitude",
      "type": "NUMBER",
      "grain": null,
      "incremental": false,
      "description": null
    },
    {
      "name": "fact_orders.longitude",
      "type": "NUMBER",
      "grain": null,
      "incremental": false,
      "description": null
    }
  ]
}

As we can see, in addition to columns from the fact table, we also have multiple Time.* dimensions. These are virtual dimensions, since they’re not stored in the database. Instead, they are computed on the fly when the SQL is generated by transforming the time column in the fact table:

% curl 'http://localhost:1974/db/sql?q=metrics:!(total_orders),dimensions:!(Time.day)' \
> | jq -r '.query.sql'

SELECT
  "__annotated"."Time.day" AS "Time.day [DATE]",
  "__annotated"."total_orders" AS "total_orders [INTEGER]"
FROM (
  SELECT
    "__final"."Time.day" AS "Time.day",
    "__final"."total_orders" AS "total_orders"
  FROM (
    SELECT
      SUM(amount) AS "total_orders",
      STRFTIME('%Y-%m-%d', "fact_orders"."event_time") AS "Time.day"
    FROM fact_orders
    GROUP BY
      STRFTIME('%Y-%m-%d', "fact_orders"."event_time")
  ) AS __final
) AS __annotated

SELECT
  "__final"."Time.day" AS "Time.day",
  "__final"."total_orders" AS "total_orders"
FROM (
  SELECT
    SUM("fact_orders"."amount") AS "total_orders",
    CAST(DATE_TRUNC('DAY', "fact_orders"."event_time") AS DATE) AS "Time.day"
  FROM "fact_orders" AS "fact_orders"
  GROUP BY
    CAST(DATE_TRUNC('DAY', "fact_orders"."event_time") AS DATE)
) AS __final

Note that, if the fact table had multiple time columns (for example, event_time and log_time), the metric definition would need an annotation for the preferred join (join=event_time or join=log_time). Also of note is the time_grain= annotation, which defines the lowest grain of Time.* available. For example, if we annotate the event_time with an hourly grain (PT1H in ISO):

CREATE VIEW fact_orders_annotated (
  id,
  amount,
  event_time,
  latitude,
  longitude,
  geom,
  city
) AS
SELECT
   id,
   amount,
   event_time AS "event_time [time_grain=hour]",
   latitude,
   longitude,
   geom AS "geom [type=POINT]",
   city
FROM fact_orders;

CREATE VIEW total_orders AS
SELECT SUM(amount) AS "total_orders"
FROM fact_orders_annotated;

CREATE VIEW fact_orders_annotated AS
SELECT
  id,
  amount,
  event_time,
  latitude,
  longitude,
  geom,
  city
FROM fact_orders;
COMMENT ON COLUMN fact_orders_annotated.event_time IS 'time_grain=PT1H';
COMMENT ON COLUMN fact_orders_annotated.geom IS 'type=POINT';

CREATE VIEW total_orders AS
SELECT SUM(amount) AS total_orders
FROM fact_orders_annotated;

Then it can’t be queried with the Time.second or Time.minute dimensions:

% curl 'http://localhost:1974/db/dimensions?q=metrics:!(total_orders)' \
> | jq -r '.dimensions[] | select(.name | startswith("Time.")) | .name'
Time.day
Time.hour
Time.month
Time.year

Calendar¶

Another way of enriching metrics is via a calendar relation. Cantrip uses the naming convention of cantrip__dim_* for enrichment relations. A special one is cantrip__dim_calendar:

CREATE TABLE cantrip__dim_calendar (
  date DATE PRIMARY KEY,
  us_holiday TEXT,
  is_weekend BOOLEAN
);

INSERT INTO cantrip__dim_calendar (date, us_holiday, is_weekend) VALUES
  ('2026-01-01', 'New Year', FALSE),
  ('2026-01-02', NULL, FALSE),
  ('2026-01-03', NULL, TRUE);

Cantrip will create a dimension for each column other than date; in this case, we would have the dimensions Calendar.us_holiday and Calendar.is_weekend available to any metrics that are connected to a time column.

% curl 'http://localhost:1974/db/dimensions?q=metrics:!(total_orders)' \
> | jq -r '.dimensions[] | select(.name | startswith("Calendar."))'

{
  "name": "Calendar.is_weekend",
  "type": "BOOLEAN",
  "grain": null,
  "incremental": false,
  "description": null
}
{
  "name": "Calendar.us_holiday",
  "type": "STRING",
  "grain": null,
  "incremental": false,
  "description": null
}

And here’s the generated SQL for total_orders by Calendar.us_holiday:

% curl 'http://localhost:1974/db/sql?q=metrics:!(total_orders),dimensions:!(Calendar.us_holiday)' \
> | jq -r '.query.sql'

SELECT
  "__annotated"."Calendar.us_holiday" AS "Calendar.us_holiday [TEXT]",
  "__annotated"."total_orders" AS "total_orders [INTEGER]"
FROM (
  SELECT
    "__final"."Calendar.us_holiday" AS "Calendar.us_holiday",
    "__final"."total_orders" AS "total_orders"
  FROM (
    SELECT
      SUM("fact_orders_annotated"."amount") AS "total_orders",
      "cantrip__dim_calendar"."us_holiday" AS "Calendar.us_holiday"
    FROM "fact_orders_annotated" AS "fact_orders_annotated"
    JOIN "cantrip__dim_calendar"
      ON STRFTIME('%Y-%m-%d', "fact_orders_annotated"."event_time") = "cantrip__dim_calendar"."date"
    GROUP BY
      "cantrip__dim_calendar"."us_holiday"
  ) AS __final
) AS __annotated

SELECT
  "__final"."Calendar.us_holiday" AS "Calendar.us_holiday",
  "__final"."total_orders" AS "total_orders"
FROM (
  SELECT
    SUM("fact_orders_annotated"."amount") AS "total_orders",
    "cantrip__dim_calendar"."us_holiday" AS "Calendar.us_holiday"
  FROM "fact_orders_annotated" AS "fact_orders_annotated"
  JOIN "cantrip__dim_calendar"
    ON CAST(DATE_TRUNC('DAY', "fact_orders_annotated"."event_time") AS DATE) = "cantrip__dim_calendar"."date"
  GROUP BY
    "cantrip__dim_calendar"."us_holiday"
) AS __final

Geometry¶

Similar to the calendar relation, Cantrip also supports a special cantrip__dim_geometries relation with geometries. Here’s a simple example:

CREATE TABLE cantrip__dim_geometries (
  id INTEGER PRIMARY KEY,
  grain TEXT NOT NULL,
  value TEXT NOT NULL
);
SELECT AddGeometryColumn('cantrip__dim_geometries', 'geometry', 4326, 'MULTIPOLYGON', 'XY');

INSERT INTO cantrip__dim_geometries (id, grain, value, geometry) VALUES
  (1, 'country', 'US', GeomFromText('MULTIPOLYGON(((...)))', 4326)),
  (2, 'country', 'UK', GeomFromText('MULTIPOLYGON(((...)))', 4326)),
  (3, 'city', 'New York', GeomFromText('MULTIPOLYGON(((...)))', 4326)),
  (4, 'city', 'Los Angeles', GeomFromText('MULTIPOLYGON(((...)))', 4326)),
  (5, 'city', 'London', GeomFromText('MULTIPOLYGON(((...)))', 4326));

CREATE TABLE cantrip__dim_geometries (
  id INTEGER PRIMARY KEY,
  grain TEXT NOT NULL,
  value TEXT NOT NULL,
  geometry GEOMETRY
);

INSERT INTO cantrip__dim_geometries (id, grain, value, geometry) VALUES
  (1, 'country', 'US', ST_GeomFromText('MULTIPOLYGON(((...)))')),
  (2, 'country', 'UK', ST_GeomFromText('MULTIPOLYGON(((...)))')),
  (3, 'city', 'New York', ST_GeomFromText('MULTIPOLYGON(((...)))')),
  (4, 'city', 'Los Angeles', ST_GeomFromText('MULTIPOLYGON(((...)))')),
  (5, 'city', 'London', ST_GeomFromText('MULTIPOLYGON(((...)))'));

Differently from the cantrip__dim_calendar relation, the geometries relation always has the same schema: the grain column indicates the spatial grain, and the value is a geometry type. Each distinct value of grain becomes a dimension – Geometry.country and Geometry.city, for the example above. The generated SQL uses spatial functions to rollup the POINT-level metric into the desired spatial grain. For example, here’s the generated SQL for total_orders by Geometry.country:

% curl 'http://localhost:1974/db/sql?q=metrics:!(total_orders),dimensions:!(Geometry.country)' \
> | jq -r '.query.sql'

SELECT
  "__annotated"."Geometry.country" AS "Geometry.country [GEOMETRY]",
  "__annotated"."total_orders" AS "total_orders [INTEGER]"
FROM (
  SELECT
    "__final"."Geometry.country" AS "Geometry.country",
    "__final"."total_orders" AS "total_orders"
  FROM (
    SELECT
      SUM("fact_orders_annotated"."amount") AS "total_orders",
      JSON_OBJECT(
        'type',
        'Feature',
        'properties',
        JSON_OBJECT('name', "cantrip__dim_geometries__country"."value"),
        'geometry',
        JSON(ASGEOJSON("cantrip__dim_geometries__country"."geometry"))
      ) AS "Geometry.country"
    FROM "fact_orders_annotated" AS "fact_orders_annotated"
    JOIN "cantrip__dim_geometries" AS "cantrip__dim_geometries__country"
      ON "cantrip__dim_geometries__country"."grain" = 'country'
      AND ST_WITHIN("fact_orders_annotated"."geom", "cantrip__dim_geometries__country"."geometry")
    GROUP BY
      JSON_OBJECT(
        'type',
        'Feature',
        'properties',
        JSON_OBJECT('name', "cantrip__dim_geometries__country"."value"),
        'geometry',
        JSON(ASGEOJSON("cantrip__dim_geometries__country"."geometry"))
      )
  ) AS __final
) AS __annotated

SELECT
  "__final"."Geometry.country" AS "Geometry.country",
  "__final"."total_orders" AS "total_orders"
FROM (
  SELECT
    SUM("fact_orders_annotated"."amount") AS "total_orders",
    JSON_OBJECT(
      'type',
      'Feature',
      'properties',
      JSON_OBJECT('name', "cantrip__dim_geometries__country"."value"),
      'geometry',
      CAST(ST_AsGeoJSON("cantrip__dim_geometries__country"."geometry") AS JSON)
    ) AS "Geometry.country"
  FROM "fact_orders_annotated" AS "fact_orders_annotated"
  JOIN "cantrip__dim_geometries" AS "cantrip__dim_geometries__country"
    ON "cantrip__dim_geometries__country"."grain" = 'country'
    AND ST_WITHIN("fact_orders_annotated"."geom", "cantrip__dim_geometries__country"."geometry")
  GROUP BY
    JSON_OBJECT(
      'type',
      'Feature',
      'properties',
      JSON_OBJECT('name', "cantrip__dim_geometries__country"."value"),
      'geometry',
      CAST(ST_AsGeoJSON("cantrip__dim_geometries__country"."geometry") AS JSON)
    )
) AS __final

Note that the Geometry.country dimension is returned as GeoJSON.

Bring your own¶

While cantrip__dim_calendar and cantrip__dim_geometries are special relations, you can bring any relation (table or view) as dimensions by following the same convention. The relation name should start with cantrip__dim_, and its columns determine the possible joins:

  • Having a date column allows for time joins.

  • Having both grain and value allows for spatial joins.

Relations can be time-only, space-only, or have both. For example, if you want to slice and dice metrics by weather data, you can create a relation like this:

CREATE TABLE cantrip__dim_weather (
    date DATE,
    grain TEXT,
    value TEXT,
    temp_high REAL,
    is_sunny BOOLEAN,
    PRIMARY KEY (date, grain, value)
);
INSERT INTO cantrip__dim_weather VALUES
    ('2026-01-01', 'city', 'New York', 79.0, 'TRUE'),
    ('2026-01-01', 'city', 'Los Angeles', 69.0, 'FALSE'),
    ('2026-01-01', 'city', 'London', 61.0, 'FALSE'),
    ('2026-01-05', 'city', 'New York', 74.0, 'FALSE'),
    ('2026-01-05', 'city', 'Los Angeles', 67.0, 'FALSE'),
    ('2026-01-05', 'city', 'London', 63.0, 'TRUE'),
    ('2026-02-01', 'city', 'New York', 77.0, 'TRUE'),
    ('2026-02-01', 'city', 'Los Angeles', 72.0, 'FALSE'),
    ('2026-02-01', 'city', 'London', 65.0, 'FALSE');

The table above adds 2 new dimensions that can be used with total_orders:

% curl 'http://localhost:1974/db/dimensions?q=metrics:!(total_orders)' \
> | jq -r '.dimensions[] | select(.name | startswith("Weather."))'

{
  "name": "Weather.is_sunny.city",
  "type": "BOOLEAN",
  "grain": null,
  "incremental": false,
  "description": null
}
{
  "name": "Weather.temp_high.city",
  "type": "NUMBER",
  "grain": null,
  "incremental": false,
  "description": null
}

Spatial joins go through the cantrip__dim_geometries, mapping from POINT to GEOMETRY to the grain column:

% curl 'http://localhost:1974/db/sql?q=metrics:!(total_orders),dimensions:!(Weather.is_sunny.city)' \
> | jq -r '.query.sql'

SELECT
  "__annotated"."Weather.is_sunny.city" AS "Weather.is_sunny.city [BOOLEAN]",
  "__annotated"."total_orders" AS "total_orders [INTEGER]"
FROM (
  SELECT
    "__final"."Weather.is_sunny.city" AS "Weather.is_sunny.city",
    "__final"."total_orders" AS "total_orders"
  FROM (
    SELECT
      SUM("fact_orders_annotated"."amount") AS "total_orders",
      cantrip__dim_weather__city.is_sunny AS "Weather.is_sunny.city"
    FROM "fact_orders_annotated" AS "fact_orders_annotated"
    JOIN "cantrip__dim_geometries" AS "cantrip__dim_geometries__city"
      ON "cantrip__dim_geometries__city"."grain" = 'city'
      AND ST_WITHIN("fact_orders_annotated"."geom", "cantrip__dim_geometries__city"."geometry")
    JOIN "cantrip__dim_weather" AS "cantrip__dim_weather__city"
      ON STRFTIME('%Y-%m-%d', "fact_orders_annotated"."event_time") = "cantrip__dim_weather__city"."date"
      AND "cantrip__dim_weather__city"."grain" = 'city'
      AND "cantrip__dim_weather__city"."value" = "cantrip__dim_geometries__city"."value"
    GROUP BY
      cantrip__dim_weather__city.is_sunny
  ) AS __final
) AS __annotated

SELECT
  "__final"."Weather.is_sunny.city" AS "Weather.is_sunny.city",
  "__final"."total_orders" AS "total_orders"
FROM (
  SELECT
    SUM("fact_orders_annotated"."amount") AS "total_orders",
    "cantrip__dim_weather__city"."is_sunny" AS "Weather.is_sunny.city"
  FROM "fact_orders_annotated" AS "fact_orders_annotated"
  JOIN "cantrip__dim_geometries" AS "cantrip__dim_geometries__city"
    ON "cantrip__dim_geometries__city"."grain" = 'city'
    AND ST_WITHIN("fact_orders_annotated"."geom", "cantrip__dim_geometries__city"."geometry")
  JOIN "cantrip__dim_weather" AS "cantrip__dim_weather__city"
    ON CAST(DATE_TRUNC('DAY', "fact_orders_annotated"."event_time") AS DATE) = "cantrip__dim_weather__city"."date"
    AND "cantrip__dim_weather__city"."grain" = 'city'
    AND "cantrip__dim_weather__city"."value" = "cantrip__dim_geometries__city"."value"
  GROUP BY
    "cantrip__dim_weather__city"."is_sunny"
) AS __final

digraph enrichment_weather_city_query { graph [fontsize=10, fontname="SFMono-Regular, SF Mono, Menlo, Monaco, monospace", nodesep="0.35", ranksep="0.55", bgcolor="transparent"]; node [fontname="SFMono-Regular, SF Mono, Menlo, Monaco, monospace", fontsize=10, style="filled", penwidth=1.2]; edge [fontname="SFMono-Regular, SF Mono, Menlo, Monaco, monospace", fontsize=9, color="#4b5563", fontcolor="#4b5563", arrowsize=0.8]; metric_total_orders [ label="METRIC total_orders", shape=box, style="rounded,filled", peripheries=2, fillcolor="#dbeafe", color="#1d4ed8", fontcolor="#0b1220" ]; dimension_weather_sunny_city [ label="DIMENSION Weather.is_sunny.city", shape=box, style="rounded,filled", peripheries=2, fillcolor="#ccfbf1", color="#0f766e", fontcolor="#0b1220" ]; view_fact_orders_annotated [ label="VIEW fact_orders_annotated\nAS fact", shape=box, style="filled,dashed", fillcolor="#fef3c7", color="#b45309", fontcolor="#0b1220" ]; table_dim_geometries_city [ label="TABLE cantrip__dim_geometries\nAS geometries", shape=box, style="filled", fillcolor="#fef3c7", color="#b45309", fontcolor="#0b1220" ]; table_dim_weather_city [ label="TABLE cantrip__dim_weather\nAS weather", shape=box, style="filled", fillcolor="#fef3c7", color="#b45309", fontcolor="#0b1220" ]; join_spatial [ label="JOIN ST_WITHIN(fact.geom, geometries.geometry)", shape=box, style="filled,dotted", fillcolor="#fef3c7", color="#b45309", fontcolor="#0b1220", ]; join_weather [ label="JOIN\nDATE_TRUNC(day, fact.event_time) = weather.date\nweather.grain = 'city'\nweather.value = geometries.value", shape=box, style="filled,dotted", fillcolor="#fef3c7", color="#b45309", fontcolor="#0b1220", ]; request [ label="QUERY {total_orders} by {Weather.is_sunny.city}", shape=note, fillcolor="#f3f4f6", color="#4b5563", fontcolor="#0b1220" ]; metric_total_orders -> view_fact_orders_annotated; view_fact_orders_annotated -> join_spatial; table_dim_geometries_city -> join_spatial; dimension_weather_sunny_city -> table_dim_weather_city; join_spatial -> join_weather [label="provides city value"]; view_fact_orders_annotated -> join_weather; table_dim_weather_city -> join_weather; metric_total_orders -> request; dimension_weather_sunny_city -> request; join_weather -> request; { rank=same; view_fact_orders_annotated; table_dim_geometries_city; table_dim_weather_city; } }

digraph enrichment_weather_city_query { graph [fontsize=10, fontname="SFMono-Regular, SF Mono, Menlo, Monaco, monospace", nodesep="0.35", ranksep="0.55", bgcolor="transparent"]; node [fontname="SFMono-Regular, SF Mono, Menlo, Monaco, monospace", fontsize=10, style="filled", penwidth=1.2]; edge [fontname="SFMono-Regular, SF Mono, Menlo, Monaco, monospace", fontsize=9, color="#94a3b8", fontcolor="#94a3b8", arrowsize=0.8]; metric_total_orders [ label="METRIC total_orders", shape=box, style="rounded,filled", peripheries=2, fillcolor="#1e3a8a", color="#93c5fd", fontcolor="#e5e7eb" ]; dimension_weather_sunny_city [ label="DIMENSION Weather.is_sunny.city", shape=box, style="rounded,filled", peripheries=2, fillcolor="#134e4a", color="#5eead4", fontcolor="#e5e7eb" ]; view_fact_orders_annotated [ label="VIEW fact_orders_annotated\nAS fact", shape=box, style="filled,dashed", fillcolor="#78350f", color="#fcd34d", fontcolor="#e5e7eb" ]; table_dim_geometries_city [ label="TABLE cantrip__dim_geometries\nAS geometries", shape=box, style="filled", fillcolor="#78350f", color="#fcd34d", fontcolor="#e5e7eb" ]; table_dim_weather_city [ label="TABLE cantrip__dim_weather\nAS weather", shape=box, style="filled", fillcolor="#78350f", color="#fcd34d", fontcolor="#e5e7eb" ]; join_spatial [ label="JOIN ST_WITHIN(fact.geom, geometries.geometry)", shape=box, style="filled,dotted", fillcolor="#78350f", color="#fcd34d", fontcolor="#e5e7eb" ]; join_weather [ label="JOIN\nDATE_TRUNC(day, fact.event_time) = weather.date\nweather.grain = 'city'\nweather.value = geometries.value", shape=box, style="filled,dotted", fillcolor="#78350f", color="#fcd34d", fontcolor="#e5e7eb" ]; request [ label="QUERY {total_orders} by {Weather.is_sunny.city}", shape=note, fillcolor="#334155", color="#cbd5e1", fontcolor="#e5e7eb" ]; metric_total_orders -> view_fact_orders_annotated; view_fact_orders_annotated -> join_spatial; table_dim_geometries_city -> join_spatial; dimension_weather_sunny_city -> table_dim_weather_city; join_spatial -> join_weather [label="provides city value"]; view_fact_orders_annotated -> join_weather; table_dim_weather_city -> join_weather; metric_total_orders -> request; dimension_weather_sunny_city -> request; join_weather -> request; { rank=same; view_fact_orders_annotated; table_dim_geometries_city; table_dim_weather_city; } }

Note that, if the fact table already has a column that matches the grain, we can instead annotate it with geo_grain=, and perform a direct join instead of having to do a spatial query. In this example, since fact_orders has a column city in addition to latitude, longitude, and geom, we can annotate it like this:

CREATE VIEW fact_orders_annotated (
  id,
  amount,
  event_time,
  latitude,
  longitude,
  geom,
  city
) AS
SELECT
   id,
   amount,
   event_time AS "event_time [time_grain=hour]",
   latitude,
   longitude,
   geom AS "geom [type=POINT]",
   city AS "city [geo_grain=city]"
 FROM fact_orders;

CREATE VIEW fact_orders_annotated AS
SELECT
  id,
  amount,
  event_time,
  latitude,
  longitude,
  geom,
  city
FROM fact_orders;
COMMENT ON COLUMN fact_orders_annotated.event_time IS 'time_grain=PT1H';
COMMENT ON COLUMN fact_orders_annotated.geom IS 'type=POINT';
COMMENT ON COLUMN fact_orders_annotated.city IS 'geo_grain=city';

Then, because cantrip__dim_weather has rows with grain='city', the query will be optimized to:

% curl 'http://localhost:1974/db/sql?q=metrics:!(total_orders),dimensions:!(Weather.is_sunny.city)' \
> | jq -r '.query.sql'

SELECT
  "__annotated"."Weather.is_sunny.city" AS "Weather.is_sunny.city [BOOLEAN]",
  "__annotated"."total_orders" AS "total_orders [INTEGER]"
FROM (
  SELECT
    "__final"."Weather.is_sunny.city" AS "Weather.is_sunny.city",
    "__final"."total_orders" AS "total_orders"
  FROM (
    SELECT
      SUM("fact_orders_annotated"."amount") AS "total_orders",
      cantrip__dim_weather__city.is_sunny AS "Weather.is_sunny.city"
    FROM "fact_orders_annotated" AS "fact_orders_annotated"
    JOIN "cantrip__dim_weather" AS "cantrip__dim_weather__city"
      ON STRFTIME('%Y-%m-%d', "fact_orders_annotated"."event_time") = "cantrip__dim_weather__city"."date"
      AND "cantrip__dim_weather__city"."grain" = 'city'
      AND "cantrip__dim_weather__city"."value" = "fact_orders_annotated"."city"
    GROUP BY
      cantrip__dim_weather__city.is_sunny
  ) AS __final
) AS __annotated

SELECT
  "__final"."Weather.is_sunny.city" AS "Weather.is_sunny.city",
  "__final"."total_orders" AS "total_orders"
FROM (
  SELECT
    SUM("fact_orders_annotated"."amount") AS "total_orders",
    "cantrip__dim_weather__city"."is_sunny" AS "Weather.is_sunny.city"
  FROM "fact_orders_annotated" AS "fact_orders_annotated"
  JOIN "cantrip__dim_weather" AS "cantrip__dim_weather__city"
    ON CAST(DATE_TRUNC('DAY', "fact_orders_annotated"."event_time") AS DATE) = "cantrip__dim_weather__city"."date"
    AND "cantrip__dim_weather__city"."grain" = 'city'
    AND "cantrip__dim_weather__city"."value" = "fact_orders_annotated"."city"
  GROUP BY
    "cantrip__dim_weather__city"."is_sunny"
) AS __final

Note that the query no longer needs the join with cantrip__dim_geometries, and neither the use of spatial functions, improving its performance.

digraph enrichment_weather_city_query { graph [fontsize=10, fontname="SFMono-Regular, SF Mono, Menlo, Monaco, monospace", nodesep="0.35", ranksep="0.55", bgcolor="transparent"]; node [fontname="SFMono-Regular, SF Mono, Menlo, Monaco, monospace", fontsize=10, style="filled", penwidth=1.2]; edge [fontname="SFMono-Regular, SF Mono, Menlo, Monaco, monospace", fontsize=9, color="#4b5563", fontcolor="#4b5563", arrowsize=0.8]; metric_total_orders [ label="METRIC total_orders", shape=box, style="rounded,filled", peripheries=2, fillcolor="#dbeafe", color="#1d4ed8", fontcolor="#0b1220" ]; dimension_weather_sunny_city [ label="DIMENSION Weather.is_sunny.city", shape=box, style="rounded,filled", peripheries=2, fillcolor="#ccfbf1", color="#0f766e", fontcolor="#0b1220" ]; view_fact_orders_annotated [ label="VIEW fact_orders_annotated\nAS fact", shape=box, style="filled,dashed", fillcolor="#fef3c7", color="#b45309", fontcolor="#0b1220" ]; table_dim_weather_city [ label="TABLE cantrip__dim_weather\nAS weather", shape=box, style="filled", fillcolor="#fef3c7", color="#b45309", fontcolor="#0b1220" ]; join_weather [ label="JOIN\nDATE_TRUNC(day, fact.event_time) = weather.date\nweather.grain = 'city'\nweather.value = fact.city", shape=box, style="filled,dotted", fillcolor="#fef3c7", color="#b45309", fontcolor="#0b1220", ]; request [ label="QUERY {total_orders} by {Weather.is_sunny.city}", shape=note, fillcolor="#f3f4f6", color="#4b5563", fontcolor="#0b1220" ]; metric_total_orders -> view_fact_orders_annotated; dimension_weather_sunny_city -> table_dim_weather_city; view_fact_orders_annotated -> join_weather; table_dim_weather_city -> join_weather; metric_total_orders -> request; dimension_weather_sunny_city -> request; join_weather -> request; { rank=same; view_fact_orders_annotated; table_dim_weather_city; } }

digraph enrichment_weather_city_query { graph [fontsize=10, fontname="SFMono-Regular, SF Mono, Menlo, Monaco, monospace", nodesep="0.35", ranksep="0.55", bgcolor="transparent"]; node [fontname="SFMono-Regular, SF Mono, Menlo, Monaco, monospace", fontsize=10, style="filled", penwidth=1.2]; edge [fontname="SFMono-Regular, SF Mono, Menlo, Monaco, monospace", fontsize=9, color="#94a3b8", fontcolor="#94a3b8", arrowsize=0.8]; metric_total_orders [ label="METRIC total_orders", shape=box, style="rounded,filled", peripheries=2, fillcolor="#1e3a8a", color="#93c5fd", fontcolor="#e5e7eb" ]; dimension_weather_sunny_city [ label="DIMENSION Weather.is_sunny.city", shape=box, style="rounded,filled", peripheries=2, fillcolor="#134e4a", color="#5eead4", fontcolor="#e5e7eb" ]; view_fact_orders_annotated [ label="VIEW fact_orders_annotated\nAS fact", shape=box, style="filled,dashed", fillcolor="#78350f", color="#fcd34d", fontcolor="#e5e7eb" ]; table_dim_weather_city [ label="TABLE cantrip__dim_weather\nAS weather", shape=box, style="filled", fillcolor="#78350f", color="#fcd34d", fontcolor="#e5e7eb" ]; join_weather [ label="JOIN\nDATE_TRUNC(day, fact.event_time) = weather.date\nweather.grain = 'city'\nweather.value = fact.city", shape=box, style="filled,dotted", fillcolor="#78350f", color="#fcd34d", fontcolor="#e5e7eb" ]; request [ label="QUERY {total_orders} by {Weather.is_sunny.city}", shape=note, fillcolor="#334155", color="#cbd5e1", fontcolor="#e5e7eb" ]; metric_total_orders -> view_fact_orders_annotated; dimension_weather_sunny_city -> table_dim_weather_city; view_fact_orders_annotated -> join_weather; table_dim_weather_city -> join_weather; metric_total_orders -> request; dimension_weather_sunny_city -> request; join_weather -> request; { rank=same; view_fact_orders_annotated; table_dim_weather_city; } }

Next let’s take a look at derived metrics.