Data profiling: what it is, what it's for, and how to know your database before you trust it

Data profiling is the systematic analysis of a database to find out how it really is, and not how everyone thinks it is. In practice, it means looking column by column, field by field, and measuring what's in there: how many records are filled in, how many are blank, which values repeat, which formats show up, how many duplicates exist, and where the data breaks the rule it should follow. In Portuguese it goes by perfilagem de dados, or perfilamento de dados, and the goal is always the same: know the data before you trust it.

Notice that data profiling is not data cleansing. Many people confuse the two. Profiling is the diagnosis, the exam that shows where the problems are. Data cleansing comes afterward, it's the treatment. Anyone who tries to clean a database without doing data profiling first is operating in the dark, fixing what they see on the surface and letting slip what's hidden among thousands of records. I usually say that data profiling is the first honest conversation the company has with its own data, and it's almost always an uncomfortable one, because the database is never as good as the official story says it is.

What data profiling is for in practice

Data profiling is for answering simple questions that, without it, nobody can answer with confidence. How many duplicate suppliers exist in the ERP? How many materials are missing an NCM (Mercosur Common Nomenclature, Brazil's tax classification code)? How many customers have an invalid CNPJ (Brazil's company registration number)? What percentage of the material master has a generic description, like "bolt" with no specification at all? These questions seem basic, but most companies can't answer them off the top of their heads, and when they go looking for the answer, the number is frightening.

An example that repeats on almost every project. The purchasing area swears the material master is under control. Then you run a profiling and discover that thirty percent of the items have the same description written five different ways, that there's a unit-of-measure field with a free-text value typed by hand, and that a relevant share of the materials never had any movement. Profiling didn't invent the problem, it just made visible what was already there costing money in repeat purchases, idle stock, and weak negotiation with suppliers.

It also serves as the starting point for any large project. Before a migration to SAP S/4HANA, before implementing an MDM, before connecting the database to an AI model, data profiling is what gives the real size of the work. Whoever skips this step discovers the scale of the problem at the worst possible moment, in the middle of the project, with the deadline blowing up.

How data profiling works

Data profiling works on several complementary fronts. The first is column analysis, looking at each field in isolation. Here you measure completeness, unique values, most frequent values, minimums and maximums, text length, and format patterns. This is where the email field shows up with "none" typed in place of the address, or the birth date with the year 1900 used as an automatic filler.

The second front is analysis across columns and across tables. Here you check whether the relationships make sense. Does every order have a valid customer attached? Does every material with movement exist in the master? Is there a dependency that should be respected and isn't, such as a state that doesn't match the CEP (Brazil's postal code)? This relationship analysis is the one that reveals the most structural problems, because it shows where the data contradicts itself.

The third front is checking against business rules. Here you compare what's in the database with what the company's rule requires. The CNPJ must be valid and unique per supplier. A purchasing material must have a material group. An active customer must have a complete address. Profiling counts how many records violate each rule and shows the size of the deviation in numbers, not opinions.

The important point is that the result of data profiling is always quantitative. It doesn't say "the database is bad," it says "forty-two percent of suppliers are unclassified and there are twelve hundred duplicate records by CNPJ." That number turns a generic complaint into a case that leadership understands and approves for correction.

Who does it and who is responsible

Data profiling is an activity that crosses technology and business, and so it doesn't belong to a single area. The person who usually runs the analysis is the data professional, the data steward, the data quality analyst, or the technical team that masters the database and the tools. It's the person who knows how to extract, measure, and interpret what the numbers say.

But interpreting the result requires the business alongside. The technical team sees that a field has an irregular format, but whoever knows whether that's acceptable or not is the area that owns the data. Purchasing understands the material and supplier master. Sales and customer service understand the customer master. Tax understands NCM and tax classification. That's why data profiling that works is done in pairs, someone who runs the analysis and someone who knows the business rule to say what's a real error. In the governance structure, it's a data steward tool and feeds the decisions of the data owner and the data committee, because it provides the baseline, the picture of the before, so that later you can measure whether quality improved or worsened over time.

Which data profiling tools exist

There is a wide range of tools, from free to enterprise. In the world of data quality and governance platforms, the best known with strong profiling capabilities are Ataccama, Informatica with its Data Quality module, Talend, IBM InfoSphere, and Collibra on the catalog and observability side. These platforms do profiling in an automated, scheduled way, connected to quality rules, which makes sense when the volume is large and the analysis needs to run on a recurring basis.

Those who work with Microsoft find capabilities inside Purview and Power Query. Those in the SAP ecosystem have quality capabilities tied to SAP MDG and SAP Data Services. And there is plenty of good material in open source and libraries, like pandas profiling in the Python world, which handles one-off analyses well.

A note I always repeat. The tool is not the starting point. You can do a data profiling that already delivers enormous value using only well-written SQL directly on the database. Counting records, grouping values, finding duplicates by key, measuring completeness, all of that is simple queries. The tool helps with scale, automation, and governance of the process, but the profiling mindset starts with someone who knows how to ask the database the right question. As I discuss in the book "Master Data Management: Fundamentos e Aplicações" (book in Portuguese), published by Editora Brasport, technology accelerates but doesn't replace understanding of the data.

Data profiling and artificial intelligence

This is the point that's growing the most in importance. Everyone wants to connect their database to some AI model, build internal copilots, automate decisions. And that's where the problem lives. AI doesn't fix bad data, it learns from what exists and reproduces the error at scale. If the material master is duplicated and poorly described, the model will treat the same item as if it were several, and will suggest wrong purchases with a confidence that misleads.

Data profiling is what prevents that disaster before it happens. Before feeding any model, profiling shows whether the database has enough consistency, whether the keys are intact, and whether the fields the model will use are filled in and standardized. It's a blood test before surgery. A company that does profiling before investing in AI discovers early that it needs to get its house in order, and saves the money it would spend training a model on a database that can't support the result.

How to learn data profiling

The path to learning data profiling goes through three layers. The first is query technique, knowing how to extract and group data, which in practice means enough SQL to interrogate your own database. You don't need to be a database specialist, you need to know how to count, group, filter, and join tables. The second layer is data quality, understanding the dimensions that get measured, such as completeness, uniqueness, validity, consistency, accuracy, and conformity. That vocabulary is what turns a loose analysis into a structured diagnosis.

The third layer is governance and business, knowing how to tie the number to the impact. It's no use discovering there are a thousand duplicates if you can't explain how much that costs in repeat purchases or in tax risk. To walk this path in an organized way, references like the DAMA-DMBOK help draw the map, and certifications like DAMA's CDMP give structure to those who want to pursue a career in data management and governance. The most important thing, though, is to practice on a real database, because data profiling is learned by looking at truly dirty data, not at a classroom example.

Career opportunities

Demand for people who know how to look at data with method is growing fast, and data profiling is one of the most accessible entry points into this field. It's a skill that a master data analyst, a purchasing analyst, or someone in systems support can start developing from the database they already use day to day. Many people who work in data governance today started exactly this way, running analyses on their own database and showing problems nobody had seen.

Data steward, data quality analyst, MDM specialist, and governance coordinator are roles that depend heavily on this competency, and they're in-demand positions in the Brazilian market, with salaries that grow well as the person shows business impact. The professional who knows how to profile a database, translate the finding into money, and propose the fix becomes rare and valuable, because they combine technique with business vision, which is the combination companies have the hardest time finding.

Main questions about data profiling

Is data profiling the same as data cleansing? No. Data profiling is the diagnosis, it measures and shows where the problems are. Data cleansing is the treatment that comes afterward. One supports the other, but they are different stages.

Do I need an expensive tool to get started? No. You can do very valuable profiling using SQL directly on the database or free libraries. An enterprise tool helps with scale and automation, but the mindset starts with the right question, not the software.

How often should I do data profiling? At the start of every large project, such as an ERP migration or an MDM implementation, and then on a recurring basis, to monitor whether quality improves or worsens over time. Profiling is not a one-off event, it's ongoing monitoring.

Does data profiling solve the duplicate problem? It finds and sizes the duplication, showing how many duplicate records exist and by which key. Eliminating the duplication is the next step, deduplication and data cleansing.

Who should do data profiling at the company? The technical team or the data steward runs the analysis, but the interpretation needs the area that owns the data, who knows whether the deviation found is a real error or an acceptable exception.

MDM Academy

MDM Academy is 4MDG's school of governance and master data, created to train the professionals the Brazilian market is looking for and can't find. The idea came from a simple observation by someone who has lived projects for years: there aren't enough people who can combine data technique with business vision, and that bridge is exactly what the Academy teaches how to build.

In the content you learn data governance, Master Data Management, data quality, the material, supplier, and customer master, and practical skills like the data profiling this article covered, always with real examples from purchasing, supply chain, SAP, and master data. It's training designed for professionals to apply at work as early as the following week, not theory far from the operation.

If you want to get into or grow in this field, it's worth getting to know MDM Academy and joining the Data Club, where the community exchanges experience, discusses real cases, and follows the news of a market that only keeps growing. It's the place for those who have decided to take a career in data seriously.

Conclusion

Data profiling is the habit of looking at your own database honestly before making any decision that depends on it. It seems like a technical detail, but it's what separates the company that trusts its data out of conviction from the one that trusts it out of evidence. And the difference shows up at the most expensive moment, when a purchase goes wrong, a report doesn't add up, or an AI project delivers a biased result because of data nobody had looked at closely.

The good news is that profiling a database doesn't require starting big. It starts with a simple question asked to the database and the courage to face the answer. Whoever builds that habit stops being surprised by their own data and starts truly governing it. At your company, has anyone stopped to actually measure how the database is, or does everyone still trust the data on a hunch?