Loading Fixed-Width CDR Files into a Star Schema on Snowflake

Last updated: October 2026

Telecom call detail records (CDRs) combine three problems that each have a standard solution, but rarely all at once: a file format designed for network switches; a volume measured in billions of rows a month; and regulatory reports that must reflect how the network and each customer's plan looked on the day of the call.

This article describes the model and loading approach we used for a telecom operator, covered in our telecom CDR case study. The operator was moving its CDR analytics off an eight-year-old Teradata system onto Snowflake. Project details come from that engagement. The SQL and dbt examples are simplified illustrations written for this article.

Set the grain first

The operator processes 2.1 billion CDRs a month, about 70 million a day. Each record is one call event: caller and recipient numbers, cell tower, start time, duration, call type, and around 40 more fields such as signal strength, handoff events and billing codes.

We set the fact table's grain at the individual CDR. Aggregating at load time saves storage, but regulatory and network reports slice the same calls by different dimensions, and only the event grain can answer all of them. The fact table, fact_cdr, references six dimensions:

Keeping time of day as its own dimension lets analysts group busy hours across a quarter by an hour attribute directly, without extracting the hour from a timestamp on billions of rows.

Parsing fixed-width files from a layout definition

The CDRs arrived as fixed-width flat files, written to a SAN by the network switches every 15 minutes. In a fixed-width file each field occupies specific character positions, and different switch vendors used slightly different layouts. One vendor used a 2-byte country code and another 3 bytes. One encoded timestamps as epoch seconds and another as ISO 8601 strings.

In the project, Talend handled extraction with a configuration layer that held the column positions for each vendor, so adding a new switch type meant updating a configuration file instead of changing ETL code. That saved rework twice during the project, when switch firmware updates changed a field width.

The same principle applies if you parse the files in Snowflake instead. Snowflake has no fixed-width file format, so the common approach is to load each line into a single column and cut the fields out with SUBSTR. The file format needs three settings: no field delimiter, no escape character, and no space trimming, because leading and trailing spaces are part of the layout.

CREATE OR REPLACE FILE FORMAT bronze.fixed_width_lines
  TYPE = CSV
  FIELD_DELIMITER = NONE
  ESCAPE_UNENCLOSED_FIELD = NONE
  TRIM_SPACE = FALSE;

CREATE TABLE IF NOT EXISTS bronze.cdr_lines (
    raw_line    VARCHAR,
    source_file VARCHAR,
    loaded_at   TIMESTAMP_NTZ DEFAULT CURRENT_TIMESTAMP()
);

COPY INTO bronze.cdr_lines (raw_line, source_file)
FROM (SELECT $1, METADATA$FILENAME FROM @bronze.cdr_stage)
FILE_FORMAT = (FORMAT_NAME = 'bronze.fixed_width_lines');

Keep the layouts in a table, one row per vendor and field, and generate the parsing SQL from it, for example with a dbt macro. When a firmware update changes a field width, you change one row and rebuild, and because the raw lines are kept, history can be re-parsed with the corrected layout.

-- One row per vendor and field.
CREATE TABLE IF NOT EXISTS ref.cdr_layout (
    vendor      VARCHAR,
    field_name  VARCHAR,
    start_pos   NUMBER,   -- 1-based character position
    field_len   NUMBER,
    valid_from  DATE      -- layouts change with firmware
);

-- The SQL generated for one vendor looks like this.
SELECT
    TRIM(SUBSTR(raw_line,  1, 15))                       AS caller_number,
    TRIM(SUBSTR(raw_line, 16, 15))                       AS recipient_number,
    TRIM(SUBSTR(raw_line, 31,  3))                       AS country_code,
    TO_TIMESTAMP(TRY_TO_NUMBER(SUBSTR(raw_line, 34, 10))) AS call_start_ts,  -- epoch seconds
    TRY_TO_NUMBER(SUBSTR(raw_line, 44, 6))               AS duration_sec,
    source_file
FROM bronze.cdr_lines
WHERE source_file ILIKE '%/vendor_b/%';

Two details are easy to miss. SUBSTR counts characters, while switch specifications often define positions in bytes, so the two agree only while every character is a single byte. And the TRY_ conversion functions return NULL instead of failing on a malformed value, so count the NULLs after each load; a sudden rise usually means a layout has shifted.

SCD Type 2 with dbt snapshots

Regulatory reporting drove the history requirement. The regulator required network quality metrics to be reported against the tower configuration that was active at the time of each call, and a customer's plan six months ago cannot change retroactively. Without Type 2 history, a tower upgrade would rewrite past reports.

We implemented Type 2 on dim_subscriber and dim_cell_tower with dbt snapshots. Since dbt 1.9, snapshots can be configured in YAML, which dbt recommends for new work. The timestamp strategy compares an updated_at column and is the better choice when the source has a reliable one; the check strategy compares listed columns and suits sources without one.

snapshots:
  - name: snap_cell_tower
    relation: source('network', 'cell_towers')
    config:
      schema: snapshots
      unique_key: tower_id
      strategy: check
      check_cols: ['technology_generation', 'capacity', 'latitude', 'longitude']
      hard_deletes: invalidate

  - name: snap_subscriber
    relation: source('crm', 'subscribers')
    config:
      schema: snapshots
      unique_key: subscriber_id
      strategy: timestamp
      updated_at: updated_at
      hard_deletes: invalidate

Each snapshot row carries dbt_valid_from and dbt_valid_to, with dbt_valid_to NULL for the current version unless you set dbt_valid_to_current. The hard_deletes setting decides what happens when a source row disappears: ignore (the default) leaves the last version open, invalidate closes it, and new_record adds a row flagged with dbt_is_deleted.

Loading existing history is the hard part. The initial load of historical tower changes from Teradata took three days to reconcile. We recommend budgeting for that work separately from building the snapshots.

Joining calls to the right dimension version

With Type 2 dimensions in place, the fact model has to pick the version that was valid when each call started. The join condition uses the natural key plus the validity window.

SELECT
    c.cdr_id,
    c.call_start_ts,
    c.duration_sec,
    t.dbt_scd_id AS cell_tower_key,
    s.dbt_scd_id AS subscriber_key
FROM {{ ref('stg_cdr') }} c
LEFT JOIN {{ ref('snap_cell_tower') }} t
       ON t.tower_id = c.tower_id
      AND c.call_start_ts >= t.dbt_valid_from
      AND c.call_start_ts <  COALESCE(t.dbt_valid_to, '9999-12-31'::TIMESTAMP_NTZ)
LEFT JOIN {{ ref('snap_subscriber') }} s
       ON s.subscriber_id = c.caller_subscriber_id
      AND c.call_start_ts >= s.dbt_valid_from
      AND c.call_start_ts <  COALESCE(s.dbt_valid_to, '9999-12-31'::TIMESTAMP_NTZ)

Using a half-open window, greater than or equal to the start and strictly less than the end, means a call made at the exact moment of a change matches one version only. A dbt test that counts fact rows with a NULL dimension key catches calls that fall outside every window, which usually points to a gap in the loaded history.

Sizing and clustering at billions of rows

At about 48,000 records a minute, the CDR volume was handled without special tuning. We sized the warehouses at Medium for ETL loads and Small for analytical queries, with multi-cluster scaling adding clusters at peak times. Total Snowflake cost came to $40,000 a month, against $267,000 a month for Teradata, and report run times fell from more than four hours to about 90 seconds.

Clustering keys become worth considering once a fact table reaches several terabytes and queries consistently filter on the same columns. Snowflake's guidance is to use three or four columns at most, to put selective filter columns first, and, for date-based fact queries, to cluster on the date. High-cardinality columns such as a timestamp can be reduced with an expression like TO_DATE(call_start_ts).

-- How well would the table cluster on call date?
SELECT SYSTEM$CLUSTERING_INFORMATION(
    'mart.fact_cdr', '(TO_DATE(call_start_ts))');

-- If the overlap and depth figures justify it:
ALTER TABLE mart.fact_cdr CLUSTER BY (TO_DATE(call_start_ts));

The cost model changed on 1 September 2026. Tables that are clustered from that date onwards use Optima Clustering, billed on the uncompressed volume ingested, multiplied by a per-GB rate and an overlap factor between 0 and 1. Data that arrives roughly in date order and is clustered by date typically has an overlap factor near zero, which suits CDRs. Tables clustered before that date stay on Clustering Classic, which bills serverless compute.

Migrate the reports people use

The Teradata system had accumulated more than 300 BTEQ scripts. Only 47 had run in the previous 90 days, so we rebuilt those 47 reports against the star schema and validated each one against Teradata output: row counts had to match, and metric totals had to agree within 0.01% to allow for floating-point differences. Teradata was then decommissioned in three phases over four weeks. Our Teradata to Snowflake migration guide covers the conversion side in more detail.