Working with Databases

How ti builds SQL, and why you should collect() last

Everything is lazy

ti never touches your data when you call a time-intelligence function. ytd(), mtd(), yoy() and the rest all return a blueprint — an object describing the calculation to run. Nothing executes until you ask for it.

There are three distinct stages:

Stage	Call	What happens
Describe	mtd(...)	Returns a ti object. No query, no data movement.
Compile	calculate()	Builds the query. Still lazy — a tbl_dbi, not a tibble.
Execute	dplyr::collect()	Runs the query and pulls the result into R.

This matters because the expensive part — the windowed aggregation — happens in the database, not in R.

Connecting to a database

ti works on any dbplyr-backed table. Here is a DuckDB connection with the Contoso sales data loaded into it:

con <- dbConnect(duckdb::duckdb())
dbWriteTable(con, "sales", contoso::sales)

db_sales <- tbl(con, "sales")

db_sales

# A query:  ?? x 20
# Database: DuckDB 1.5.2 [hagan@Linux 7.0.11-76070011-generic:R 4.6.1/:memory:]
   order_key line_number order_date delivery_date customer_key store_key
       <dbl>       <dbl> <date>     <date>               <dbl>     <dbl>
 1    233000           0 2021-05-18 2021-05-18         1855811       585
 2    233100           0 2021-05-19 2021-05-19         1345436       550
 3    233100           1 2021-05-19 2021-05-19         1345436       550
 4    233100           2 2021-05-19 2021-05-19         1345436       550
 5    233200           0 2021-05-20 2021-05-20          926315       370
 6    233200           1 2021-05-20 2021-05-20          926315       370
 7    233200           2 2021-05-20 2021-05-20          926315       370
 8    233200           3 2021-05-20 2021-05-20          926315       370
 9    233300           0 2021-05-21 2021-05-21          116391        60
10    233300           1 2021-05-21 2021-05-21          116391        60
# ℹ more rows
# ℹ 14 more variables: product_key <dbl>, quantity <dbl>, unit_price <dbl>,
#   net_price <dbl>, unit_cost <dbl>, currency_code <chr>, exchange_rate <dbl>,
#   gross_revenue <dbl>, net_revenue <dbl>, unit_discount <dbl>,
#   discounts <dbl>, cogs <dbl>, gross_margin <dbl>, unit_margin <dbl>

Optional: let contoso build the database for you

Loading tables by hand is fine for one table. If you want the whole Contoso model — sales, products, customers, stores, orders and the rest — contoso will build the DuckDB database for you in a single call:

db <- contoso::create_contoso_duckdb(size = "small")

names(db)
#> [1] "sales"     "product"   "customer"  "store"     "orders"
#> [6] "orderrows" "fx"        "calendar"  "con"

The return value is a named list of lazy tbl_dbi tables, plus the connection itself as db$con. Each element drops straight into a ti pipeline:

db$sales |>
  mtd(.date = order_date, .value = gross_margin, calendar_type = "standard") |>
  calculate() |>
  collect()

Remember to close it when you are done:

DBI::dbDisconnect(db$con, shutdown = TRUE)

The size argument is the reason this is worth knowing about. It is how you test whether a pipeline actually scales:

size	Sales rows
"small"	7,794
"medium"	2,349,091
"large"	23,719,935
"mega"	237,245,485

"small" serves the same data as the bundled contoso::sales. The larger sizes are where the lazy-pipeline advice in this article stops being theoretical — at 237 million rows, the difference between filtering before and after collect() is the difference between a query and an out-of-memory error.

NoteThis requires network access

create_contoso_duckdb() streams Parquet files from cloud storage rather than reading a local dataset, so it needs an internet connection. The chunks in this section are not executed when this site is built, which is why they show static output — everything else in this article runs live against the local connection created above.

The syntax from here is identical to the tibble syntax used everywhere else in these articles:

result <- db_sales |>
  mtd(.date = order_date, .value = gross_margin, calendar_type = "standard")

class(result)

[1] "ti::ti"    "S7_object"

Still no query. calculate() compiles it:

compiled <- calculate(result)

class(compiled)

[1] "tbl_duckdb_connection" "tbl_dbi"               "tbl_sql"              
[4] "tbl_lazy"              "tbl"                  

Note that compiled is a tbl_dbi — a lazy database table. The data is still in DuckDB.

Seeing the generated SQL

Because the result is a lazy dbplyr table, dplyr::show_query() will show you exactly what ti wrote:

compiled |> show_query()

<SQL>
SELECT
  date,
  "year",
  "month",
  "quarter",
  "week",
  "day",
  gross_margin,
  SUM(gross_margin) OVER (PARTITION BY "year", "month" ORDER BY date ROWS UNBOUNDED PRECEDING) AS mtd_gross_margin,
  missing_date_indicator
FROM (
  SELECT
    date,
    "year",
    "quarter",
    EXTRACT(MONTH FROM date) AS "month",
    "week",
    "day",
    gross_margin,
    missing_date_indicator
  FROM (
    SELECT
      date,
      "year",
      EXTRACT(QUARTER FROM date) AS "quarter",
      "month",
      "week",
      "day",
      gross_margin,
      missing_date_indicator
    FROM (
      SELECT
        date,
        EXTRACT(year FROM date) AS "year",
        "quarter",
        "month",
        "week",
        "day",
        gross_margin,
        missing_date_indicator
      FROM (
        SELECT
          date,
          "year",
          "quarter",
          "month",
          "week",
          "day",
          COALESCE(gross_margin, 0.0) AS gross_margin,
          CASE WHEN ((gross_margin IS NULL)) THEN 1.0 WHEN NOT ((gross_margin IS NULL)) THEN 0.0 END AS missing_date_indicator
        FROM (
          SELECT
            COALESCE(LHS.date, RHS.date) AS date,
            "year",
            "quarter",
            "month",
            "week",
            "day",
            gross_margin
          FROM (
            SELECT date, "year", "quarter", "month", "week", "day"
            FROM (
              SELECT
                DATE_TRUNC('day', date) AS date,
                "year",
                "quarter",
                "month",
                "week",
                "day"
              FROM (
                SELECT *, EXTRACT(day FROM date) AS "day"
                FROM (
WITH DATE_SERIES AS (
SELECT

GENERATE_SERIES(
   MIN(DATE_TRUNC('day', DATE '2021-05-18'::date::date))::DATE
  ,MAX(DATE_TRUNC('day', DATE '2024-04-20'::date::date))::DATE
  ,INTERVAL '1 day'
) AS DATE_LIST),

CALENDAR_TBL AS (
      SELECT

      UNNEST(DATE_LIST)::DATE AS date

      FROM DATE_SERIES

      )
SELECT *
,EXTRACT(YEAR FROM date) AS year
,EXTRACT(QUARTER FROM date) AS quarter
,EXTRACT(month FROM date) AS month
,FLOOR((EXTRACT(DOY FROM date) - 1) / 7) + 1 AS week

FROM CALENDAR_TBL

                ) AS q01
              ) AS q01
            ) AS q01
            GROUP BY date, "year", "quarter", "month", "week", "day"
          ) AS LHS
          FULL JOIN (
            SELECT date, SUM(gross_margin) AS gross_margin
            FROM (
              SELECT
                *,
                DATE_TRUNC('day', order_date) AS date,
                'day' AS time_unit
              FROM sales
            ) AS q01
            GROUP BY date
          ) AS RHS
            ON (LHS.date = RHS.date)
        ) AS q01
      ) AS q01
    ) AS q01
  ) AS q01
) AS q01

The period-to-date calculation compiles down to a SQL window function partitioned by the period columns. ti is not looping in R and it is not pulling your table into memory — it is writing the query you would have written by hand.

Collect last, not first

The single most important habit: keep the pipeline lazy for as long as possible and call collect() at the very end.

compiled |>
1  filter(year == 2023, month == 6) |>
  arrange(date) |>
2  collect() |>
  head(5)

1

Filtering happens in the database, on the full result set.

2

Only the filtered rows cross the boundary into R.

# A tibble: 5 × 9
  date                 year month quarter  week   day gross_margin
  <dttm>              <dbl> <dbl>   <dbl> <dbl> <dbl>        <dbl>
1 2023-06-01 00:00:00  2023     6       2    22     1        3132.
2 2023-06-02 00:00:00  2023     6       2    22     2        3380.
3 2023-06-03 00:00:00  2023     6       2    22     3       12390.
4 2023-06-04 00:00:00  2023     6       2    23     4           0 
5 2023-06-05 00:00:00  2023     6       2    23     5         810.
# ℹ 2 more variables: mtd_gross_margin <dbl>, missing_date_indicator <dbl>

If you had called collect() before the filter(), DuckDB would have handed R every row and then R would have thrown most of them away.

Tibbles get a database too

You do not need to set up a connection to benefit from this. When you pass a plain data.frame or tibble, ti registers it into a temporary in-memory DuckDB instance with make_db_tbl():

contoso::sales |>
  mtd(order_date, gross_margin, "standard") |>
  calculate() |>
  class()

[1] "tbl_duckdb_connection" "tbl_dbi"               "tbl_sql"              
[4] "tbl_lazy"              "tbl"                  

The result is a tbl_duckdb_connection, not a tibble — even though the input was a tibble. Registration uses DuckDB’s duckdb_register(), which is a virtual registration rather than a physical copy, so it is fast and does not duplicate your data.

This is why every example in these articles ends with dplyr::collect().

Dialect support

Date arithmetic differs across SQL engines, so ti detects the backend from the connection and generates the appropriate expression. Three dialects are recognised explicitly:

Backend	Status
DuckDB	Recognised
Snowflake	Recognised
Postgres	Recognised
Anything else	Falls back to the DuckDB dialect

The fallback is often fine, since many engines accept DuckDB-compatible date arithmetic. But it is a fallback, not a guarantee — if you are running ti against a backend not in the list above, verify the output of show_query() before trusting the numbers.

Key points

• Time-intelligence functions describe work; calculate() compiles it; collect() runs it.
• Use show_query() to inspect the SQL — period-to-date becomes a window function.
• Filter, join and arrange before collect() so the database does the work.
• Tibbles are transparently promoted to DuckDB, so the return value is always a lazy table.
• DuckDB, Snowflake and Postgres get dialect-specific date arithmetic; other backends fall back to DuckDB’s.