# 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.
