Developer ToolsFree generatorPDF, Excel, Word export

Master Data Completeness Profiling SQL Generator

Completeness is the easiest data quality dimension to measure and the one most often skipped. This generator writes the profiling SQL for any ERP master table: how many records have a null, blank or placeholder value in the fields that matter, and how many records fail at least one test.

Fill in

Master table name

Column 1

Column 2

Column 3

Column 4

Placeholder text to flag

Values users type to get past a required field, such as TBD, N/A, XXX or a single dot.

Status column

Active status value

Generated code

Download:

1. Row count baseline

SELECT COUNT(*) AS total_active
FROM item_master
WHERE status = 'A';

2. Null, blank and placeholder count per column

SELECT 'col1: description' AS field,
       SUM(CASE WHEN description IS NULL THEN 1 ELSE 0 END) AS null_count,
       SUM(CASE WHEN LTRIM(RTRIM(CAST(description AS VARCHAR(255)))) = '' THEN 1 ELSE 0 END) AS blank_count,
       SUM(CASE WHEN UPPER(CAST(description AS VARCHAR(255))) = UPPER('TBD') THEN 1 ELSE 0 END) AS placeholder_count
FROM item_master WHERE status = 'A'
UNION ALL
SELECT 'col2: unit_of_measure',
       SUM(CASE WHEN unit_of_measure IS NULL THEN 1 ELSE 0 END),
       SUM(CASE WHEN LTRIM(RTRIM(CAST(unit_of_measure AS VARCHAR(255)))) = '' THEN 1 ELSE 0 END),
       SUM(CASE WHEN UPPER(CAST(unit_of_measure AS VARCHAR(255))) = UPPER('TBD') THEN 1 ELSE 0 END)
FROM item_master WHERE status = 'A'
UNION ALL
SELECT 'col3: product_group',
       SUM(CASE WHEN product_group IS NULL THEN 1 ELSE 0 END),
       SUM(CASE WHEN LTRIM(RTRIM(CAST(product_group AS VARCHAR(255)))) = '' THEN 1 ELSE 0 END),
       SUM(CASE WHEN UPPER(CAST(product_group AS VARCHAR(255))) = UPPER('TBD') THEN 1 ELSE 0 END)
FROM item_master WHERE status = 'A'
UNION ALL
SELECT 'col4: weight',
       SUM(CASE WHEN weight IS NULL THEN 1 ELSE 0 END),
       SUM(CASE WHEN LTRIM(RTRIM(CAST(weight AS VARCHAR(255)))) = '' THEN 1 ELSE 0 END),
       SUM(CASE WHEN UPPER(CAST(weight AS VARCHAR(255))) = UPPER('TBD') THEN 1 ELSE 0 END)
FROM item_master WHERE status = 'A';

3. Records failing any of the four completeness tests

SELECT COUNT(*) AS incomplete_records
FROM item_master
WHERE status = 'A'
  AND ( description IS NULL OR LTRIM(RTRIM(CAST(description AS VARCHAR(255)))) = ''
     OR unit_of_measure IS NULL OR LTRIM(RTRIM(CAST(unit_of_measure AS VARCHAR(255)))) = ''
     OR product_group IS NULL OR LTRIM(RTRIM(CAST(product_group AS VARCHAR(255)))) = ''
     OR weight IS NULL OR LTRIM(RTRIM(CAST(weight AS VARCHAR(255)))) = '' );

4. Most common values in column 3 (spot dumping grounds)

SELECT product_group AS value, COUNT(*) AS occurrences
FROM item_master
WHERE status = 'A'
GROUP BY product_group
ORDER BY occurrences DESC;

Names are placeholders: confirm them in your data dictionary. CAST to VARCHAR is SQL Server style; on Oracle use TO_CHAR and VARCHAR2, on PostgreSQL use CAST AS TEXT. Numeric columns often store 0 instead of NULL when missing, so also look at the count of zero values for fields like weight. Completeness percent for a column is (total_active minus nulls minus blanks minus placeholders) divided by total_active.

Automate your master data scorecard

Optional: we can schedule these checks against your live ERP and publish a scorecard to your team.

Optional. The tool and every export work without this. No spam.

How to use the generator

Name the table and up to four columns you consider mandatory for the business process, for example description, unit of measure, product group and weight on an item table. Add the placeholder value your users type to bypass required-field checks. Run block 1 for the baseline, block 2 for field-by-field counts, block 3 for the number of incomplete records and block 4 to see which values dominate a classification field. Repeat with the next four columns until every critical attribute is profiled. Paste the output into a spreadsheet and compute percentages.

Why three kinds of empty

A field can be empty in three ways. NULL means the database holds no value. A blank is an empty or whitespace string, which passes a NOT NULL constraint but carries no information. A placeholder such as TBD, N/A, XXX or a single dot is a deliberate bypass of a mandatory-field rule. Counting only NULLs understates the problem, sometimes by half. Profiling all three gives an honest completeness figure. Numeric fields add a fourth case: a zero that really means unknown, such as a weight of 0 on a stocked item.

Reading the results

Express each column as a completeness percentage and compare to a target agreed with the data owner. Many teams set 98 to 100 percent for fields that drive transactions, such as unit of measure and item type, and lower targets for descriptive fields. Block 4 is a useful trap: if one product group value holds a huge share of records, it is probably a dumping ground such as MISC or OTHER, and the classification field is complete but useless.

Completeness percent equals valid records divided by active records
Track the trend monthly, not only the snapshot
Target the fields that block transactions first
Treat dominant catch-all values as a quality finding

Common mistakes

Profiling inactive and obsolete records inflates the defect count and wastes cleanup effort, so always filter on status. Another mistake is checking completeness without checking validity: a populated field can still hold a wrong unit of measure or a nonexistent group code. Completeness is the first gate, followed by validity, uniqueness and consistency checks. Finally, do not mass-populate blanks with defaults just to improve the score; that converts an honest gap into a hidden error.

Frequently Asked Questions

What completeness target should I use?

Set it by field criticality. Fields that block a transaction, such as unit of measure, item type or payment terms, should be at or near 100 percent. Descriptive or optional fields can be lower. Agree the targets with the data owner and review them yearly.

Will this slow down my ERP?

Profiling scans the whole table, so run it off peak or on a reporting replica. On tables with millions of rows, add a status filter and consider running one block at a time. It only reads data.

Why count placeholder values separately?

Users often type TBD or a dot to get past a mandatory-field check. Those records look complete to a NULL test but carry no information. Counting them separately shows the real completeness and points to forms that need better validation.

Can I use it for more than four columns?

Yes. Change the column names and rerun, or copy a block and add another UNION ALL branch. Keep each profiling run focused on the fields that matter for one business process, such as purchasing or planning.

If you want these checks scheduled against your live ERP with a dashboard and owner alerts, we can build that on your own infrastructure.

Talk to an Expert Explore More Free Tools

Related Tools

Developer Tools

More Developer Tools tools

See all

Back to All Tools