Witify Logo Witify Logo

Thousands of artists. Measured every week.

MID, the database an HEC Montréal research team uses to study the music industry, designed and run by Witify. Every week, measurements for thousands of artists come in, each one dated.

Client
Sector
Academic research
Technologies
Laravel, MySQL, Amazon S3

01 The numbers

Six figures, and where they come from

Orders of magnitude first. The volumes are those of the production database; the other numbers are counted in the import’s code and in its journal.

  • More than 1.9billion

    dated measurements Each row carries the date of the delivery that brought it.

  • 3

    tables of more than half a billion rows Track statistics, track indicators and album indicators.

  • More than 275GB

    of data Indexes included.

  • More than 6,500

    artists tracked With their 1.4 million albums and 3.8 million tracks.

  • 21

    files per delivery Imported in 18 steps, always in the same order.

  • 170

    deliveries recorded One a week since August 2023.

Volumes read in the production database on November 13, 2026, and rounded. The last two numbers come from the import’s code and its journal.

02 Before

A list of artists, and nowhere to keep their history

At the start, the team keeps a hand-picked list of artists in a workbook. To study their careers, it needs every measurement of every week, kept with its date.
Fig. 01 The starting list: artists picked one by one

03 The flow

From 21 files to the database, without losing a week

Every week, the supplier drops its files. From the drop folder to the database, nobody touches them while all is well, and everything stops as soon as a row cannot be read.

01 The route

Five stops between the supplier and the researcher

Dated drop, reception, import, database, extracts: every delivery goes through the same five stops, in that order. Every five minutes, the system checks whether a new delivery has arrived.
Fig. 02 The route of a delivery

02 Receive

21 files expected. Not 20.

Nothing starts until all 21 files are in the drop folder. If one is missing, the system checks again every five minutes; after the second day, the week is marked skipped and the next one can go through.
Fig. 03 The November 11 delivery, being received

03 Import

18 steps, always in the same order

Artists first, then albums and tracks, so every measurement finds what it belongs to; the series come next. One delivery at a time, oldest first.
Fig. 04 The import under way, step 17 of 18

04 Resume

One unreadable row stops everything. On purpose.

The step records the row, the file and the message, and Witify gets an email. Nothing more is loaded until the cause is fixed; the import then picks up from that row, not from the start.
Fig. 05 The failed step, with the row to resume from

05 Close

Every week leaves a record

Status, start time, duration: the journal keeps every delivery, including the one that had to be skipped. The original files stay in the drop folder.
Fig. 06 The latest deliveries

04 The model

The artist at the centre. A date on every measurement.

An album or a track enters the database only through one of its artists. Every week the whole catalogue comes back, and fifteen series get new dated rows.
Fig. 07 Three reference tables, fifteen dated series

05 Extracts

A research question becomes a file

The researchers describe a scope: some artists, a period, some series. Witify turns it into an extract in CSV files, through a read-only connection.

Format

One row per track and per date

The database’s narrow rows are gathered: one column per measurement.

Scope

Narrow it down before exporting

A request that is too broad takes weeks to export. We narrow it with the researchers, then the large table is read in slices.

06 Our choices

Three choices so the series holds, week after week

Three decisions mattered more than the others. For each: the risk to avoid, what we chose, and what the research team gains.

  1. Files, not millions of calls.

    The risk
    Querying the supplier artist by artist, track by track: millions of calls every week.
    Our choice
    One complete delivery a week, dropped in one go by the supplier into a storage space, and kept as it came.
    The gain
    A consistent snapshot of the whole catalogue every week, with the original files kept.

  2. Stop rather than leave a gap.

    The risk
    A week loaded halfway, or loaded before the previous one: a skewed series nobody notices until an analysis.
    Our choice
    One delivery at a time, in date order. At the slightest error, the import stops, alerts us and waits; it then resumes from the row where it stopped.
    The gain
    Every week is whole, or clearly marked as skipped in the journal: never in between.

  3. Narrow tables built to last.

    The risk
    Tables swelling by several million rows a week until they slow down the import, then the researchers’ queries.
    Our choice
    Where the volume is huge, a few typed columns and nothing more; the supplier’s raw detail is kept only where it is rare. One of the two variants of the track statistics was dropped to lighten the largest table.
    The gain
    Since August 2023, the database has grown every week without changing shape.

07 Under the hood

What the database is made of

An application that runs with almost no screen: scheduled commands, a database and a storage space. Here are its parts, and what has been put into service since August 2023.
Application
Laravel, PHP, scheduled commands; the import runs without a screen
Data
MySQL, tuned for heavy writes; a connection reserved for reading serves the extracts
Storage
Amazon S3, compressed CSV files, one dated folder per delivery
Import
Custom orchestrator, 18 steps, streamed reading, batched writes, resume from the row
Monitoring
Sentry, health checks, disk, database and scheduler; an email on every failure
Backups
Monthly snapshots

Key milestones

  1. August 2023 Weekly import The first delivery loaded, the database validated with the team.
  2. September 2023 Data dictionary Every table and every field described for the researchers.
  3. January 2024 First extracts Files prepared at the researchers’ request.
  4. September 2024 Backups and checks Monthly snapshots, monitoring of the disk, the database and the scheduler.
  5. March 2025 Hardened import Artists, albums and tracks carry a unique key: loading them again no longer creates duplicates. An incomplete delivery no longer blocks the next one.
  6. April 2026 Large extract The largest tables read in slices.

Is your data arriving faster than you can file it?

Thirty minutes to follow one of your files from its source to your database.

Or write to us
Next case study Trottier Energy Institute Read the case study

Names and figures shown in the interfaces are examples.