Syrian Telecom Warehouse
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.
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.
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.