# Master Data Completeness Profiling SQL Generator

> Source: https://netray.co/tools/erp-master-data-completeness-profiling-sql-generator
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: Free Tools

## Short answer

Enter a table and up to four columns to get SQL that reports null, blank and placeholder rates for each field.

## Category

developer

## Audience

- ERP admin
- MDM analyst
- Data steward

## Tool

**Kind:** template

**Output:** code

**Language:** sql

**Fields:**

#### Master table name

**Key:** table

**Type:** text

**Default:** item_master

#### Column 1

**Key:** col1

**Type:** text

**Default:** description

#### Column 2

**Key:** col2

**Type:** text

**Default:** unit_of_measure

#### Column 3

**Key:** col3

**Type:** text

**Default:** product_group

#### Column 4

**Key:** col4

**Type:** text

**Default:** weight

#### Placeholder text to flag

**Key:** placeholder

**Type:** text

**Default:** TBD

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

#### Status column

**Key:** status_col

**Type:** text

**Default:** status

#### Active status value

**Key:** active_value

**Type:** text

**Default:** A

**Blocks:**

#### 1. Row count baseline

**Body:** SELECT COUNT(*) AS total_active
FROM {{table}}
WHERE {{status_col}} = '{{active_value}}';

#### 2. Null, blank and placeholder count per column

**Body:** SELECT 'col1: {{col1}}' AS field,
       SUM(CASE WHEN {{col1}} IS NULL THEN 1 ELSE 0 END) AS null_count,
       SUM(CASE WHEN LTRIM(RTRIM(CAST({{col1}} AS VARCHAR(255)))) = '' THEN 1 ELSE 0 END) AS blank_count,
       SUM(CASE WHEN UPPER(CAST({{col1}} AS VARCHAR(255))) = UPPER('{{placeholder}}') THEN 1 ELSE 0 END) AS placeholder_count
FROM {{table}} WHERE {{status_col}} = '{{active_value}}'
UNION ALL
SELECT 'col2: {{col2}}',
       SUM(CASE WHEN {{col2}} IS NULL THEN 1 ELSE 0 END),
       SUM(CASE WHEN LTRIM(RTRIM(CAST({{col2}} AS VARCHAR(255)))) = '' THEN 1 ELSE 0 END),
       SUM(CASE WHEN UPPER(CAST({{col2}} AS VARCHAR(255))) = UPPER('{{placeholder}}') THEN 1 ELSE 0 END)
FROM {{table}} WHERE {{status_col}} = '{{active_value}}'
UNION ALL
SELECT 'col3: {{col3}}',
       SUM(CASE WHEN {{col3}} IS NULL THEN 1 ELSE 0 END),
       SUM(CASE WHEN LTRIM(RTRIM(CAST({{col3}} AS VARCHAR(255)))) = '' THEN 1 ELSE 0 END),
       SUM(CASE WHEN UPPER(CAST({{col3}} AS VARCHAR(255))) = UPPER('{{placeholder}}') THEN 1 ELSE 0 END)
FROM {{table}} WHERE {{status_col}} = '{{active_value}}'
UNION ALL
SELECT 'col4: {{col4}}',
       SUM(CASE WHEN {{col4}} IS NULL THEN 1 ELSE 0 END),
       SUM(CASE WHEN LTRIM(RTRIM(CAST({{col4}} AS VARCHAR(255)))) = '' THEN 1 ELSE 0 END),
       SUM(CASE WHEN UPPER(CAST({{col4}} AS VARCHAR(255))) = UPPER('{{placeholder}}') THEN 1 ELSE 0 END)
FROM {{table}} WHERE {{status_col}} = '{{active_value}}';

#### 3. Records failing any of the four completeness tests

**Body:** SELECT COUNT(*) AS incomplete_records
FROM {{table}}
WHERE {{status_col}} = '{{active_value}}'
  AND ( {{col1}} IS NULL OR LTRIM(RTRIM(CAST({{col1}} AS VARCHAR(255)))) = ''
     OR {{col2}} IS NULL OR LTRIM(RTRIM(CAST({{col2}} AS VARCHAR(255)))) = ''
     OR {{col3}} IS NULL OR LTRIM(RTRIM(CAST({{col3}} AS VARCHAR(255)))) = ''
     OR {{col4}} IS NULL OR LTRIM(RTRIM(CAST({{col4}} AS VARCHAR(255)))) = '' );

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

**Body:** SELECT {{col3}} AS value, COUNT(*) AS occurrences
FROM {{table}}
WHERE {{status_col}} = '{{active_value}}'
GROUP BY {{col3}}
ORDER BY occurrences DESC;

**Notes:** 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.

## Content

**Intro:** 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.

**Sections:**

#### How to use the generator

**Body:** 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

**Body:** 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

**Body:** 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.

**Bullets:**

- 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

**Body:** 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.

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

**Faq:**

#### What completeness target should I use?

**Answer:** 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?

**Answer:** 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?

**Answer:** 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?

**Answer:** 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.

## Search terms

data profiling sql query, count null and blank values sql, master data completeness check, item master missing fields query, erp data quality sql, percent complete field sql, customer master blank fields

---
Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json