Microsoft Fabric
Delta Lake

Customer DataTrust Transformation

Low-code data standardisation for a telecom Customer 360 initiative.

Role: Independent project
Year: 2026
Stack: Microsoft Fabric · Dataflow Gen2 · Power Query (M) · Delta Lake · SQL

Project description

Built a low-code data cleansing and standardisation pipeline in Microsoft Fabric to rebuild the trustworthiness of a telecom operator’s customer data foundation. The project addressed inconsistent address formats, mismatched state representations, and contaminated phone number fields that were undermining marketing campaigns, churn prediction models, and downstream Customer 360 reporting. Using Dataflow Gen2, raw customer records were transformed against governed reference data and loaded into a validated, analytics-ready Silver layer table in the Lakehouse.

Technologies used

Microsoft Fabric, Dataflow Gen2, Lakehouse, Delta Lake, Power Query (M), reference data lookups, SQL (validation scripting), data quality flagging.

Project scope

Ingest raw customer records from a Bronze-layer source table containing address, state, and phone data defects
Apply reference data lookups to standardise street address suffixes (Street, St, Str., Road) to a single canonical form
Normalise state values (abbreviations, full names, mixed case) to consistent two-letter codes
Clean and validate phone numbers, stripping non-numeric characters and distinguishing salvageable entries from unsalvageable ones
Engineer a data quality flag to separate records that pass all checks from those requiring downstream review
Publish a schedulable Dataflow Gen2 pipeline and load the standardised output into a defined Customer_Silver schema in the Lakehouse
Build a SQL validation script to confirm suffix standardisation, phone numeric purity, and quality flag completeness

Challenges

Inconsistent source data. Address suffixes appeared in at least six variants, and state values in over a dozen representations across just four canonical states, with no existing standard to reconcile them.

Non-standard contamination. Phone numbers mixed valid formats with punctuation, invalid placeholder values, and nulls, requiring logic that could tell a recoverable format issue apart from genuinely bad data.

Competing stakeholder priorities. Marketing needed trusted addresses urgently, data science needed stable model inputs, and engineering needed a maintainable, schedulable solution, all from the same pipeline.

Governed reference data dependency. Standardisation logic depended on external lookup tables (state and address suffix mappings) that had to be ingested and joined correctly rather than hardcoded.

Solution

Inconsistent source data

Merge with the state lookup table to get a clean state column:

Power Query Merge dialog merging customer_bronze csv with state_lookup csv on a left outer join. The state_lookup table maps Raw_State values including CA, ca, Calif. and California to a single Standard_State value of CA. A message confirms the selection matches 2,500 of 2,500 rows from the first table. — Every raw state variant resolves to one of four canonical two-letter codes.

For the Address field, a two-step split isolated the unit and street components: the column was first split by a comma delimiter to extract the unit component (e.g. Apt 4B), then split again by a space delimiter to separate the street portion from any trailing suffix text, isolating the street name and suffix (Street, Ave, Rd) cleanly for the suffix lookup join.

Power Query M script showing the full sequence of applied steps: splitting the Address column by comma and by space, merging with address_suffix_lookup on a left outer join, expanding the Standard_Suffix column, adding a custom column that falls back to the original suffix when no lookup match exists, removing helper columns, recombining the street and suffix into Address_Street, and renaming the remaining column to Address_Unit. — The full column-splitting and suffix-lookup sequence, run as a single Power Query script.

Non-standard contamination

A custom column, added from the Add Column tab, strips non-numeric characters from the phone field and formats whatever ten-digit number remains:

Power Query Custom Column dialog defining Phone_Clean as a Text data type, with a formula that selects only digit characters from Phone, then formats them as a hyphenated ten-digit number if exactly ten digits remain, or returns null otherwise. — Anything that isn't exactly ten digits after stripping punctuation becomes null rather than a guess.

Validation check. A second custom column engineers the quality flag itself, checking every standardised field at once:

Power Query Custom Column dialog defining Data_Quality_Flag as a Whole Number, with a formula that returns 1 when CustomerID, Address_Street and Standard_State are all non-null and Phone_Clean is either null or exactly ten characters long, otherwise 0. — One flag, derived from every column the schema depends on, rather than a separate check per field.

Competing stakeholder priorities

The final output conforms exactly to a defined Customer_Silver schema, is published on a daily execution schedule, and is backed by a SQL validation script confirming standardisation, phone purity, and quality flag accuracy, turning a one-time cleanse into a repeatable, low-code standardisation pattern.

Power Query data view of the Customer_Silver output table, with columns CustomerID, Address_Street, Address_Unit, Standard_State, Phone_Clean and Data_Quality_Flag populated across many rows. The query settings panel on the right shows the data destination: Lakehouse AnjaliLakehouse, schema CaseStudy10, table CUSTOMER_SILVERTARGET, update method Replace. — Every row carries its own quality flag, so downstream consumers can filter for trusted records without re-deriving the checks.

Full script

Complete Power Query M script for the Customer_Silver query, from the initial Lakehouse.Contents source navigation through importing the Bronze CSV, merging and expanding the state lookup, splitting and rejoining the address against the suffix lookup, deriving Phone_Clean, and finally deriving Data_Quality_Flag. — One script, source to Silver: reference data joins, address parsing, phone cleaning and the quality flag, in order.