Ingest and Query a CSV

1 + 1 isn't very interesting. trilogy ingest points the CLI at a file - local or remote, CSV, TSV, or Parquet - and writes a model for it.

We will use Seattle's daily weather from 2012 through 2015.

trilogy ingest "https://cdn.jsdelivr.net/npm/vega-datasets@3.2.1/data/seattle-weather.csv" duckdb

Trilogy reads the file, verifies that date is a unique grain, and infers types from the values:

Detected potential unique key combinations: [['date']]
Using verified unique grain: ['date']
Detected enum type for 'weather': enum<'drizzle', 'fog', 'rain', 'snow', 'sun'>

That lands in raw/seattle_weather.preql - trilogy ingest writes to raw/ next to the nearest trilogy.toml, or under the current directory when there is no config yet. Pass -o to send it somewhere else.

# Datasource ingested from https://cdn.jsdelivr.net/npm/vega-datasets@3.2.1/data/seattle-weather.csv
key date date;

properties date (
    precipitation float,
    temp_max float,
    temp_min float,
    wind float,
    weather enum<string>['drizzle', 'fog', 'rain', 'snow', 'sun'],
);

root datasource seattle_weather (
    date,
    precipitation,
    temp_max,
    temp_min,
    wind,
    weather,
)
grain (date)
file `https://cdn.jsdelivr.net/npm/vega-datasets@3.2.1/data/seattle-weather.csv`;

Now we can answer the question - does it rain in Seattle?

--import prepends the import so an inline query can reach the model:

trilogy run --import raw.seattle_weather "select weather, count(date) as days order by days desc;" duckdb

  weather │ days
 ═════════╪══════
  rain    │ 641
  sun     │ 640
  fog     │ 101
  drizzle │ 53
  snow    │ 26

It sure does, almost half the time.

Some callouts here: because the grain was verified, count(date) is a guaranteed correct row count.

weather is a typed enum, so will catch types before you hit the DB.

trilogy run --import raw.seattle_weather "select count(date ? weather = 'sunny') as sunny_days;" duckdb

Syntax error in stdin: Comparison `local.weather = 'sunny'` can never match
enum field 'local.weather', which contains only these values: 'drizzle',
'fog', 'rain', 'snow', 'sun'. It is always false and should be removed.

Push a little further with a filtered aggregate. The ? operator filters a single expression's input, so differently-filtered measures can share one query:

trilogy run --import raw.seattle_weather "select date.year, count(date ? weather = 'sun') as sunny_days, round(avg(temp_max),1) as avg_high order by date.year asc;" duckdb

  date_year │ sunny_days │ avg_high
 ═══════════╪════════════╪══════════
  2012      │ 118        │ 15.3
  2013      │ 173        │ 16.1
  2014      │ 187        │ 17.0
  2015      │ 162        │ 17.4

See the query examples for more of this.