SQLite - How to fill a table with a resource data generator?

About

This how-to will show you how to:

define the generation of data via a generator
and load it into a table

Steps

Prerequisites

You should have Tabulify installed on your computer.

Create the Data Generation file

The below file is a resource generator that defines the structure of the data and the data suppliers

kind: generator
spec:
  Comment: An example of date dimension generator based on the `date_dim` table of TPCDS
  primary-columns: [ "d_date_sk" ]
  Columns:
    - name: d_date_sk
      comment: A surrogate key
      Type: integer
      data-supplier:
        type: sequence
    - name: d_date
      comment: A business key in date format
      Type: date
      data-supplier:
        type: sequence
        arguments:
          start: 2025-05-13
    - name: d_date_id
      comment: A business key in string
      Type: varchar
      data-supplier:
        type: expression
        arguments:
          column-variable: d_date
          expression: "d_date.toISOString().substring(0,10)"
    - name: d_month_seq
      comment: An ascendant sequence for the month
      Type: integer
      data-supplier:
        type: expression
        arguments:
          column-variable: d_date
          expression: "function pad(number) {if (number < 10) { return '0' + number; } return number; }; d_date.getFullYear()+''+(pad(d_date.getMonth()+1))"
    - name: d_day_name
      comment: The name of the day
      Type: varchar
      data-supplier:
        type: expression
        arguments:
          column-variable: d_date
          expression: "var days = ['Sunday','Monday','Tuesday','Wednesday','Thursday','Friday','Saturday']; days[d_date.getDay()]"
    - name: d_moy
      comment: the month number in year
      Type: Integer
      data-supplier:
        type: expression
        arguments:
          column-variable: d_date
          expression: "d_date.getMonth()+1"
    - name: d_year
      comment: The year number
      Type: Integer
      data-supplier:
        type: expression
        arguments:
          column-variable: d_date
          expression: "d_date.getFullYear()"

Drop the target table if it exists

With tabul drop, we will:

drop the date_dim table from sqlite
not fail if there is no table found
drop the foreign keys thanks to the --force if any exists

tabul data drop --no-strict-selection --force date_dim@sqlite

Fill the table

The tabul data fill command takes the data definition as input and can load it into a table.

tabul data upsert date_dim--generator.yml@howto date_dim@sqlite

Transfer results
input             target            latency   record_count   error_code   error_message
---------------   ---------------   -------   ------------   ----------   -------------
date_dim@memgen   date_dim@sqlite   0.888s             100

Verify

The table date_dim was filled with generated data

tabul data head date_dim@sqlite

The first 10 rows of the data resource (date_dim@sqlite):
d_date_sk   d_date       d_date_id    d_month_seq   d_day_name   d_moy   d_year
---------   ----------   ----------   -----------   ----------   -----   ------
        1   2025-05-12   2025-05-12        202505   Monday           5     2025
        2   2025-05-11   2025-05-11        202505   Sunday           5     2025
        3   2025-05-10   2025-05-10        202505   Saturday         5     2025
        4   2025-05-09   2025-05-09        202505   Friday           5     2025
        5   2025-05-08   2025-05-08        202505   Thursday         5     2025
        6   2025-05-07   2025-05-07        202505   Wednesday        5     2025
        7   2025-05-06   2025-05-06        202505   Tuesday          5     2025
        8   2025-05-05   2025-05-05        202505   Monday           5     2025
        9   2025-05-04   2025-05-04        202505   Sunday           5     2025
       10   2025-05-03   2025-05-03        202505   Saturday         5     2025