Universal Music Data Parser for 20+ Platforms

The Problem: Data Chaos in Music Industry

It was 2023 when we first sat down with a UK music label facing a common but frustrating problem. Every month, they received royalty reports from their distributors - ADA (Warner), The Orchard (Sony), Merlin, FUGA, Bandcamp, and a dozen others. Each report contained the same basic information: how many times their artists' songs were streamed, and how much money they earned.

ADA sent Excel files with columns named "Release Artist", "Release Title", and "Net Payable". Merlin called them "Artist name", "Track name", and "Payable". The Orchard went with "Artist Name", "Track Name", and "Label Share Net Receipts".

Same data. Different languages.

And that was just the beginning.

The Date Format Nightmare

Consider how different distributors format dates:

One distributor. Four date formats. And we had 20+ distributors to support.

The Currency Problem

Some distributors report in USD. Others in EUR, GBP, or the original local currency. Some files contain mixed currencies. Some hardcode "USD" even when the actual currency varies by territory.

The "Same But Different" Problem

"UNITED STATES" vs "USA" vs "US" vs "United States of America". All the same country. All different strings that need to be normalized.

"Sony Music" vs "SONY" vs "Sony Music Entertainment" vs "SME". Same label. Four different spellings across different platforms.

The Solution: Adapter Pattern with Field Mapping

We needed a system that could:

1. Accept any file format (CSV, XLS, XLSX, XLSB)
2. Map arbitrary column names to a standard schema
3. Handle date parsing edge cases per distributor
4. Normalize currencies, countries, and entity names
5. Scale to hundreds of millions of records

The Architecture

At its core, the system uses the Adapter Pattern - a universal base class that handles all common operations, with specialized adapters for each distributor that define only what's different:

• Base SDLImporter - handles file parsing, chunked processing, country normalization, currency conversion, and Elasticsearch indexing
• Distributor Adapters - each adapter contains only a field mapping dictionary and any date/currency parsing overrides specific to that distributor

class ADAAdapter(BaseSDLImporter):
    FIELD_MAP = {
        "Release Artist": "artist",
        "Release Title":  "track",
        "Net Payable":    "income",
    }

    def parse_date(self, raw):
        # ADA uses Excel serial numbers
        return excel_serial_to_date(raw)

Supported Distributors and Adapters

We currently support 25+ adapters covering the major music distribution ecosystem:

Major Distributors

Specialized Platforms

Regional Distributors

• Phonofile (Nordic region)
• Zebralution (Germany)
• IDOL (France)
• Altafonte (Spain/Latin America)

Supported File Formats

The system handles every file format encountered in the music distribution industry:

File Format Quirks We Handle

• XLS files containing CSV data - The Orchard and ADA sometimes send .xls files that are actually CSV
• Mixed encodings - Automatic fallback from UTF-8 to ISO-8859-1
• Files with metadata headers - Skip non-data rows (Merlin stores dates in row 1)
• Multi-sheet workbooks - Automatic sheet detection

Scale: 200 Million Records and Growing

Our production system currently manages:

Performance Metrics

The ability to generate complex royalty reports across 200 million records in just a few seconds is what sets the system apart. This is achieved through:

• Elasticsearch for aggregations and full-text search
• Pre-computed rollups for common report dimensions
• Chunked async processing during import (2000 rows per batch)
• Celery task queues for parallel processing

The 20 Standard Fields

After analyzing dozens of distributor formats, we defined a universal schema that captures all essential royalty data:

Real-World Challenges We Solved

The ADA Complexity

ADA (Warner's distribution arm) required 5 different adapters due to format inconsistencies:

Entity Resolution

We maintain mapping databases to normalize inconsistent naming:

"SME"                  →  "Sony Music Entertainment"
"SPOTIFY AB"           →  "Spotify"
"UNITED KINGDOM (GB)"  →  "GB"

Encoding and Format Detection

Every file goes through automatic format detection and encoding fallback — because "just UTF-8" is never the reality in international music data.

Technologies Used

Results

After 18 months of development:

• 200 million records processed and searchable
• 25+ adapters for major music distributors
• Report generation in 5-15 seconds regardless of data volume
• Sub-second search across the entire catalog
• Zero manual data entry - fully automated pipeline

Development in Numbers

Building a system of this complexity doesn't happen overnight. Here's what the development effort looked like behind the scenes:

The development followed an intense sprint delivery model — 80% of all commits landed within a focused 3-month window (December 2024 – February 2025), averaging nearly 9 commits per active day. Each distributor adapter went through multiple iterations: for every new feature shipped, approximately 3 follow-up fixes were committed to handle real-world edge cases in date parsing, encoding, and field normalization.

This iterative approach — build, import real data, discover edge cases, fix, repeat — was essential. Music distribution data is messy by nature, and no amount of upfront design can replace the feedback loop of processing actual royalty files from 20+ platforms.

What We Learned

What's Next?

We're exploring:

• ClickHouse as the analytics engine - For the next project of this scale, we would replace Elasticsearch with ClickHouse for analytical queries. Its columnar storage and vectorized execution are purpose-built for aggregating billions of rows, offering significantly faster performance for royalty reporting workloads
• ML-based column mapping - Automatically detect field mappings for new distributors
• Real-time streaming ingestion - Move from batch files to API integrations
• Anomaly detection - Flag unusual patterns in royalty data