Building a Data Quality Scorecard with Talend: Profiling, Rule Sets, and Gates That Block Bad Loads

Most teams can tell you whether last night's jobs ran. Far fewer can tell you whether the data those jobs loaded is any good. "Green in the scheduler" is not a data quality programme, and the gap usually surfaces the same way: a finance analyst finds 4,000 customer rows with a country code of XX, and nobody can say when they appeared or how long they have been feeding the revenue model.

This tutorial builds the missing layer: a repeatable data quality scorecard driven by Talend. Profiling to find out what is actually in the source, rule sets that encode what "good" means, a metrics table that keeps the score over time, and thresholds that fail a load instead of quietly publishing bad numbers.

It assumes Talend Studio 8 or Qlik Talend Cloud. Where a component is Data Quality edition only, we give the Open Studio / plain DI fallback, because plenty of shops reading this are still on a DI-only licence.

1. Profile before you write a single rule

Writing rules from a data dictionary produces rules about the data someone intended to load. Profile first and you write rules about the data you have.

In Studio, switch to the Profiling perspective, connect to the source, and run a Column Analysis over the tables you are about to certify. The three indicators worth turning on for every column:

• Row count, null count, blank count. Nulls are expected; blanks masquerading as values are not.
• Distinct and unique count. This is how you discover that the "primary key" from the source system has 12 duplicates.
• Pattern frequency. Talend collapses values into patterns (aaa9999, 99/99/9999). Anything below about 1% frequency is where your edge cases live.

If you do not have the Profiling perspective, you can get 80% of the value from a throwaway DI job: tDBInput with a wide GROUP BY query into tLogRow, or a tJavaRow that accumulates counts per column into a HashMap. Ugly, disposable, and still better than guessing.

Record the findings somewhere durable. The profile is the baseline you will compare against in six months when somebody asks whether quality is improving.

2. Turn findings into a rule set, not a pile of tFilterRows

The mistake we see most often is quality logic sprinkled through a dozen jobs as tFilterRow conditions. It works until the definition of a valid customer changes and you have to find all twelve.

Model rules as data instead. A small dq_rule table:

CREATE TABLE dq_rule (
  rule_id        VARCHAR(40)  PRIMARY KEY,
  dataset        VARCHAR(100) NOT NULL,   -- 'customer_master'
  column_name    VARCHAR(100),            -- NULL for row/table-level rules
  dimension      VARCHAR(20)  NOT NULL,   -- completeness|validity|uniqueness|consistency|timeliness
  severity       VARCHAR(10)  NOT NULL,   -- block|warn
  expression     VARCHAR(500) NOT NULL,   -- SQL boolean, true = row passes
  threshold_pct  DECIMAL(5,2) NOT NULL,   -- minimum pass rate, e.g. 99.50
  active         CHAR(1)      DEFAULT 'Y'
);

Rows look like this:

rule_id	dataset	column	dimension	severity	expression	threshold
CUST_EMAIL_FMT	customer_master	email	validity	warn	email IS NULL OR email LIKE '%_@_%.__%'	98.00
CUST_ID_UNIQ	customer_master	customer_id	uniqueness	block	(table-level, see below)	100.00
CUST_CTRY_ISO	customer_master	country_code	validity	block	country_code IN (SELECT code FROM ref_country)	99.90
ORD_DATE_FUT	orders	order_date	validity	block	order_date <= CURRENT_DATE	100.00
ORD_FRESH	orders	–	timeliness	block	(freshness, see §5)	100.00

Two things this buys you immediately. Analysts can add a rule without opening Studio, and the same rule set can be evaluated by a Talend job today and by a dbt test or a warehouse-native check later without rewriting the definitions.

Keep expression as a SQL boolean where the rule is pushdown-able. It is far cheaper to evaluate 200 million rows in Snowflake or BigQuery than to pull them through a Talend row buffer.

3. The evaluation job

One generic job, DQ_EvaluateRuleSet, parameterised by dataset. Shape:

1. tDBInput reads active rules for context.dataset into a flow.
2. tFlowToIterate turns each rule into an iteration.
3. Inside the loop, tDBRow/tDBInput executes a generated measurement query.
4. tDBOutput appends one row per rule per run to dq_result.

The generated query for a column rule is the same shape every time:

SELECT
  COUNT(*)                                              AS rows_evaluated,
  SUM(CASE WHEN (<expression>) THEN 1 ELSE 0 END)       AS rows_passed
FROM <dataset>
WHERE load_batch_id = '<batch>';

Build it in a tJavaRow with plain string assembly, and scope it to the current batch. Scoring the whole table every night means yesterday's already-quarantined garbage keeps failing today's load, and the score never moves.

Table-level rules (uniqueness, referential integrity) do not fit the row-count template, so give the rule table a rule_type of row or query; for query rules the expression column holds a full SQL statement that must return rows_evaluated and rows_passed itself:

SELECT COUNT(*) AS rows_evaluated,
       COUNT(DISTINCT customer_id) AS rows_passed
FROM customer_master
WHERE load_batch_id = '<batch>';

The results table is deliberately narrow:

CREATE TABLE dq_result (
  run_id         VARCHAR(36),
  rule_id        VARCHAR(40),
  dataset        VARCHAR(100),
  batch_id       VARCHAR(40),
  measured_at    TIMESTAMP,
  rows_evaluated BIGINT,
  rows_passed    BIGINT,
  pass_pct       DECIMAL(6,3),
  threshold_pct  DECIMAL(5,2),
  status         VARCHAR(10)   -- pass|warn|fail
);

If you already built the job-run audit table from our error handling and observability tutorial, use the same run_id. The value of a quality score doubles when you can join it to the run that produced it.

4. Sample the failures, not just the count

A score of 99.2% tells you there is a problem. It does not tell anyone what to fix. Every evaluation should also persist a bounded sample of offending rows:

SELECT '<rule_id>' AS rule_id, '<run_id>' AS run_id, <pk_columns>, <column_name> AS bad_value
FROM <dataset>
WHERE load_batch_id = '<batch>' AND NOT (<expression>)
LIMIT 50;

Fifty rows is plenty. Cap it, because the first run of a new rule against a dirty table will otherwise try to write nine million samples and your DQ schema becomes the largest thing in the warehouse.

Two rules about samples, learned the hard way:

• Never sample raw PII into a table analysts browse. Hash it, or truncate it, or reuse the masking patterns from our tDataMasking tutorial. A quality dashboard is not a lawful basis for storing plaintext national ID numbers.
• Store the primary key, not the whole row. The row will change; the key is what a steward needs to go fix the record in the source.

5. Freshness and volume: the two rules everyone forgets

Most quality incidents we get called into are not malformed values. They are the data that never arrived, or arrived at a tenth of its usual size because an upstream export ran against an empty partition. No column-level rule catches either.

Freshness. For every certified dataset, assert a maximum age:

SELECT 1 AS rows_evaluated,
       CASE WHEN MAX(source_updated_at) > CURRENT_TIMESTAMP - INTERVAL '26 hours'
            THEN 1 ELSE 0 END AS rows_passed
FROM orders;

Volume. Compare this batch against the trailing median rather than a hard-coded number, so seasonality does not generate noise:

WITH hist AS (
  SELECT rows_evaluated
  FROM dq_result
  WHERE rule_id = 'ORD_VOLUME' AND status = 'pass'
  ORDER BY measured_at DESC
  LIMIT 14
)
SELECT 1 AS rows_evaluated,
       CASE WHEN (SELECT COUNT(*) FROM orders WHERE load_batch_id = '<batch>')
                 BETWEEN (SELECT MEDIAN(rows_evaluated) FROM hist) * 0.6
                     AND (SELECT MEDIAN(rows_evaluated) FROM hist) * 1.6
            THEN 1 ELSE 0 END AS rows_passed;

Widen the band for genuinely spiky feeds. A rule that cries wolf every Monday gets muted within a fortnight, and a muted rule is worse than no rule because it looks like coverage.

6. Make the score do something

A scorecard nobody acts on is a wall poster. Wire the result back into the pipeline:

• After evaluation, a tDBInput counts status = 'fail' rows for the batch where the rule severity is block.
• If the count is greater than zero, tDie with a clear message so the orchestrator (TMC task, Airflow DAG, Kubernetes CronJob) reports a real failure.
• Crucially, fail before the publish step, not after. Load into a staging schema, score it, then swap or MERGE into the serving tables only on a pass. A quality gate downstream of the publish is a report, not a gate.
• warn severity rules never block. They post to Slack or Teams and accumulate in the trend so somebody can argue about the threshold with evidence.

The publish-on-pass pattern is the whole point. It is the difference between "we detected bad data" and "our users never saw bad data."

7. The scorecard itself

One view, aggregated per dataset and dimension, is enough to start:

CREATE VIEW dq_scorecard AS
SELECT dataset,
       dimension,
       DATE(measured_at)                                     AS score_date,
       ROUND(AVG(pass_pct), 2)                               AS avg_pass_pct,
       SUM(CASE WHEN status = 'fail' THEN 1 ELSE 0 END)      AS failed_rules,
       COUNT(*)                                              AS rules_evaluated
FROM dq_result
GROUP BY dataset, dimension, DATE(measured_at);

Point Qlik, Power BI, or whatever your users already open at that view. Resist the urge to reduce a dataset to a single number early on; a "94% quality score" invites arguments about weighting. Pass rate per dimension, trended, plus a list of currently failing rules, is what actually drives remediation.

8. Rollout that does not stall

The programmes that die are the ones that try to certify the warehouse. The ones that work look like this:

1. Pick one dataset that a named person complains about. One.
2. Profile it. Write no more than ten rules, all warn.
3. Run for two weeks. Tune thresholds until the noise is gone.
4. Promote the rules people would genuinely stop a load for to block, and move the gate in front of publish.
5. Only then take the same generic job to the next dataset. The job does not change; only rows in dq_rule do.

Checklist

• Profile before writing rules; keep the baseline.
• Rules live in a table, not in job logic.
• Push evaluation down to the warehouse where you can.
• Score the current batch, not the whole table.
• Persist bounded, de-identified failure samples with keys.
• Always include freshness and volume rules.
• Gate before publish, with block and warn severities.
• Trend by dimension; start with one dataset.

Data quality work is unglamorous and it is the highest-leverage thing most integration teams are not doing. If you want help standing up a rule set, a scorecard, and the gates around your Talend pipelines, get in touch — it is what our Talend Data Quality practice does every week.