0%
Forward Deployed Engineering
The Role
Discovery and Scoping
Building in the Field
Running Deployments
Cleaning and Entity Resolution
Loading customer data is only half of the work. The data that arrives is rarely ready to use. Dates arrive as text, the same supplier appears under four names, quantities mix units and a third of the phone numbers are blank. Users notice these problems within minutes of seeing the first screen, and once they stop trusting the numbers they rarely come back. This lesson covers how to profile data, standardize fields, resolve records into real entities, handle missing and conflicting values and keep clean data clean as the sources change.
Why profiling comes first
Profiling means measuring what the data actually contains before deciding how to clean it. It is tempting to start writing cleaning rules from the documentation or from a quick look at the first rows. Both mislead. Documentation describes what a system was designed to hold, not what users typed into it over ten years, and the first rows of a table are usually the oldest and cleanest. A profile of the whole table, or a large random sample, shows the real shape of the problem.
Column level statistics
For every column, compute a small set of numbers: row count, null count, distinct count, the most common values with their frequencies, the minimum and maximum, and the length range for text. These few numbers answer most early questions. A customer identifier with fewer distinct values than rows has duplicates. A date column whose maximum is in the year 2099 has placeholder values. A status column with forty distinct values when the documentation lists five has free text hiding in it.
Patterns and formats
Beyond counts, look at the shapes values take. Replace every digit with a nine and every letter with an A, then count the resulting patterns. A phone column might show ten patterns, from bare digits to international formats with spaces and brackets. A postcode column might show both national formats and values that are clearly something else, such as city names. Pattern counts tell you how many cleaning rules you will need and which ones cover most of the rows.
Distributions and outliers
For numeric columns, look at the distribution, not just the minimum and maximum. A quantity column where most values are between one and fifty but a few are in the thousands may be mixing units, cases and individual items. A price column with a spike at exactly zero may hold free samples or missing values entered as zero. Outliers are not always errors, so take them to someone who knows the business before deciding what they mean.
Relationships between tables
Profile the joins as well as the columns. For each foreign key, count how many values have no match in the table they point to. Orphaned order lines, shipments for customers that do not exist and products referenced by code but missing from the product list all point to gaps between systems or deleted records. Join coverage is one of the most useful numbers to show a customer, because it explains why a report built on these tables will never balance.
Placeholder values
Every long lived system collects placeholder values that look like data but mean unknown: a birth date of the first of January 1900, an email of none at none, a phone number of all zeros, a customer called test or do not use. Profiling finds them because they appear far more often than any real value would. Treat each placeholder as missing, and keep a list of them per source so the same rules apply on every load.
Profiling over time
A single profile is a snapshot. Run the same profile on every load and keep the results, because changes in the profile are often the first sign that something changed in the source. A null rate that jumps from two percent to forty percent overnight usually means a source field was renamed or an upstream job failed, not that customers stopped giving their phone numbers. Profiles over time also show whether cleaning work is improving the data.
Sharing the profile with the customer
Show the profile to the customer early, in plain language. A short page that says one product code in five on order lines has no match in the product list is more persuasive than any amount of explanation later. Customers often know about some problems and are surprised by others. The profile starts the conversation about which problems the deployment will fix, which the customer will fix at the source and which everyone will live with.
Choosing what to clean
Not every problem is worth fixing. Rank problems by how much they affect the workflow the deployment supports. A messy fax number column that no screen uses can wait forever. A product code mismatch that hides a fifth of order lines from the planning view must be fixed before launch. Write the ranked list down and agree it with the sponsor, so the cleaning effort goes where users will feel it.
Profiling text fields
Free text fields deserve their own look. Count how many values contain digits, punctuation or more than one line, and read a random sample of fifty values in full. Notes fields often hold structured information that users had nowhere else to put, such as a second contact name, a delivery instruction or a reference number from another system. Finding this early can save a source change later, because the information the workflow needs may already exist, just in the wrong place.
Profiling at scale
Large tables cannot always be profiled in full on every load. Exact distinct counts and full pattern counts become expensive past a few hundred million rows. Use approximate counts and a fixed random sample for the detailed statistics, and keep full counts for the few numbers that matter most, such as row count and null counts on key fields. Make the sample stable across loads, for example by hashing the primary key, so changes in the profile reflect the data and not the sample.
Asking the people who enter the data
Numbers show what is wrong but rarely why. Spend an hour with the people who type the data in: the customer service agent who creates accounts, the warehouse clerk who books receipts. They will explain that the phone field is required, so agents type zeros when a customer refuses, or that returns are booked as negative orders because the system has no returns screen. These explanations turn mysterious patterns into rules you can write with confidence.
Writing down the findings
Keep the profile findings in a short document per source: what was measured, what was found, what each problem means for the workflow and what the plan is. Link it from the data contract. When the customer asks months later why a record was excluded or changed, the answer is already written down, and the next engineer on the deployment does not have to rediscover the same quirks.
A profile is a baseline
The first full profile becomes the baseline that later work is measured against. When cleaning rules go live, rerun the profile and show the change: phone patterns down from ten to one, join coverage up from eighty one to ninety eight percent. These before and after numbers are some of the clearest evidence of progress an FDE can give a sponsor.
Profile every column and every join on the whole table, find patterns, outliers and placeholders, and keep the profile for every load so jumps raise alerts. Share the findings with the customer in plain language and rank problems by how much they affect the workflow before writing any cleaning rule.