Projects

Syrian Telecom Warehouse

Repository

Two Syrian telecom operators — Syriatel and MTN Syria — each running an independent sales system, differing in schema, naming, language and data format. The Ministry of Communications needs one view across both.

That’s the brief — the data mining project at Antioch Syrian Private University, which took full marks. What makes it a real exercise rather than a UNION ALL is that the two source systems were built to disagree along nine specific dimensions, and the ETL has to resolve every one of them.

I led it: architecture, ETL, the warehouse, the modelling and the infrastructure. Luf8y contributed on the OLTP side, the analytics queries and the dashboard UI.

The divergence table

This is the heart of the project, so it’s worth showing in full.

Syriatel MTN Syria Resolution
Table naming customers, products, orders clients, items, transactions Conformed to dim_customer, dim_product, fact_sales
Primary key SERIAL integer UUID Surrogate *_sk in every dimension and fact
City storage Arabic — دمشق, حلب English — Damascus, Aleppo Both map to a canonical form via a city reference file
Phone format E.164 +963944123456 National 0944123456 Normalized to +9639XXXXXXXX
Price type INTEGER, whole SYP NUMERIC(12,2) NUMERIC(14,2); integers promoted
Order total Stored column Not stored Computed for MTN; validated for Syriatel
Date storage One TIMESTAMP Separate DATE + TIME Both reduce to a date_sk joining dim_date
Product category Title Case UPPERCASE Normalized to upper
Product names Arabic — “باقة 5GB” English — “5GB Internet Bundle” Canonical English via a product catalogue

Row six is the one that taught me something. Syriatel stores an order total; MTN doesn’t. The obvious move is to compute MTN’s and trust Syriatel’s. What the ETL actually does is recompute Syriatel’s too and compare — the recomputed total is what gets loaded, and any row where the stored total disagrees with quantity × price is also written to etl_errors.

A stored aggregate in a source system is a claim, not a fact. It was written by code you didn’t review, possibly years ago, possibly before a schema change. Trusting it because it’s there is how a warehouse quietly ends up with numbers nobody can reproduce.

stored totalSyriatelquantity × pricerecomputedcomparefact_salesetl_errorsloads the recomputed totaldisagrees: also logged

The divergences are also checked, not assumed: make verify introspects both live schemas and exits non-zero if any of the nine has gone missing. A migration that accidentally aligns the two sources would otherwise silently turn most of the ETL into dead code.

Three tiers

Sources — two independent Postgres instances on separate ports, one Arabic-tilted with four tables and SERIAL keys, one English-tilted with three tables and UUID keys.

ETL — a Python package on pandas and psycopg. Extract → Transform → Load, idempotent, so a re-run is safe. Every batch is recorded in etl_runs; bad rows are quarantined in etl_errors rather than dropped, because a row that fails validation is information.

Warehouse and analytics — the unified star schema, fact_sales against four conformed dimensions, feeding six SQL analyses, the data-mining outputs, and a Plotly Dash dashboard.

Scheduling runs weekly through pg_cron with LISTEN/NOTIFY, so the ETL listener wakes on a signal from the database rather than polling.

SyriatelMTN Syria9 axes apartETLerror tablefact

On top of the schema

RFM segmentation — recency, frequency, monetary — over the unified customer base, which is only meaningful because it’s unified: a customer’s behaviour across both operators is the thing neither source system can see.

Holt-Winters for monthly forecasting, chosen over something heavier because the series is twelve months of monthly data and there isn’t enough history to justify anything more elaborate. Twelve months isn’t enough for a seasonal component either, so the model has a trend and no seasonality.

Scale: roughly 1,000 customers and 10,000 orders per operator over twelve months, giving about 20,000 rows in fact_sales, 2,000 unique customers, 30 products and 17 Syrian cities.

Stack

PostgreSQL, Python with pandas and psycopg, Docker Compose for all four services, pg_cron for scheduling, Plotly Dash for the dashboard, Metabase for exploration, and a Makefile as the entry point to all of it.