Duplicate metric definitions: 90-day cross-team query reuse vs human curation

This article compares automated 90-day cross-team query-reuse scans with human curation for duplicate metric definitions, and it sets a 30-day ratification rule with a freeze condition for unresolved duplicates.

The operating model routes every detected duplicate to a human curation board, which must ratify one canonical definition within 30 days or the metric is frozen read-only.

How duplicate metrics actually form

Duplicate metrics usually form in three ways, and a lineage scan is what makes them visible: forked SQL, semantic-layer drift, and orphaned dashboard logic. Forked SQL starts when a working query is copied for a new report and its WHERE clause, date logic, or status filter is changed to fit that report; the original and the copy then diverge. Semantic-layer drift occurs when a shared metric definition is overridden in a downstream model, view, or transformation, so two layers answer the same question with different logic. Orphaned dashboard logic occurs when a dashboard keeps an embedded filter or calculation after the underlying query or model changes, leaving a definition that no longer matches its source. A lineage scan detects all three by tracing query text, dependencies, and referenced columns back to the same business concept rather than relying on metric names.

The practical check is to query the warehouse's query-history table and compare expressions for the same concept. In Snowflake, use QUERY_HISTORY; in BigQuery, use INFORMATION_SCHEMA.JOBS; in Databricks, use system.query.history. Search for queries that filter the same business concept with different expressions, such as five variants of "active customer" built from different WHERE clauses. Normalize predicates, table references, and date windows, then group by the business entity and grain. Variants that share the same entity and grain but differ in filters, joins, or status logic are duplicate candidates, even when their names or owners differ.

The 90-day window is not arbitrary; it aligns with quarter-end reporting cycles, which are where duplicates spawn. Each quarterly close produces forked SQL as analysts adjust filters for cutoff dates, restatements, or executive requests, and that SQL often never gets reconciled afterward. A scan across a full quarter captures the burst of close-period variants before they are copied into recurring dashboards or semantic models. Shorter windows can miss variants created late in the cycle, while longer windows can blur which duplicate was created first and make adjudication harder.

Verification should separate true duplicates from coincidentally similar queries. Confirm that candidates target the same grain and business entity, return comparable output columns, and use the same join path to the source tables. If lineage shows different source tables, different grain, or a different business entity, treat the query as a separate metric rather than a duplicate. This check keeps the scan from flooding the curation board with false positives and preserves the distinction between a duplicate definition and a legitimately specialized metric.

The evidence: detection methods compared

The converging threshold is recall, not canonical correctness: a scheduled 90-day automated query-lineage reuse scan finds more duplicate metric definitions than ad-hoc human curation, while only human curation can decide which duplicate becomes canonical. Treat this as a detection result, not a governance verdict.

Measure it as an internal audit method, not a published statistic. For a sample of 20 certified metrics, establish the known duplicate set for each metric, run the automated scan and the human curation cycle against that same set, and count how many known duplicates each method finds. Compare the two counts for the same 20 metrics; do not compare one method's count against a vendor's published percentage. Any external percentage claim remains unverified until reproduced in your own warehouse.

Vendor documentation is a method benchmark, not a duplicate-count guarantee. Those tools describe reuse detection capabilities; their docs do not establish how many duplicates your warehouse contains. Audit each claim against your own lineage data, metric inventory, and the 20-metric sample before using it in a business case.

Keep the roles separate. The scan's job is to surface candidates across teams; the curation board's job is to ratify one canonical definition. A higher recall score for automation does not mean an automated result can replace adjudication, and a lower human recall score does not mean human review is optional.

Before changing cadence, document the audit: the 20 certified metrics selected, the known-duplicate criteria, the scan configuration, the curation process, both counts, and the date. Re-run the same audit after any major lineage, semantic-layer, or dashboard change so the recall comparison stays tied to your current warehouse rather than to a vendor benchmark.

90-day scans vs curation boards

Choosing between a 90-day automated scan, a monthly human curation board, and a hybrid is a recall-versus-precision decision, not a tooling preference. The scan is the better finder because it observes query traffic across teams; the board is the better judge because it can weigh business meaning and ownership. The hybrid keeps both roles separate instead of asking either side to do the other's job.

The row that decides the comparison is recall. Pure human curation typically reviews only metrics someone complained about, so its recall is limited to squeaky-wheel metrics; a 90-day scan sees all query traffic in the window and can surface duplicates that no report owner flagged.

The row that kills pure automation is adjudication. Automated matching can flag likely duplicates from query patterns, but it cannot ratify which duplicate becomes canonical; that decision requires human judgment about definitions, ownership, and downstream use.

The hybrid wins by using automation for recall and humans for precision: scans standardize detection and intake, while the curation board makes the canonical decision. A useful operating rule is to treat scan output as a candidate queue rather than a decision, so every detected duplicate enters board review and no automated match should be promoted without ratification.

Costs and the numbers to track

Treat the cost model as a planning heuristic, not a measured benchmark: budget one to two analyst-hours for each detected duplicate that reaches adjudication. If the first 90-day scan surfaces 200 or more candidates, triage by source-column overlap breadth before opening any board session, so analyst time goes to duplicates that touch the widest set of upstream columns rather than to isolated one-off queries.

The number that matters most is board cycle time. The canonical rule caps ratification at 30 days, so track median days from detection to ratification as the primary KPI. A rising median is an early warning that the board is becoming the bottleneck, not the scan.

The read-only freeze is the enforcement cost control, and it cuts both ways: freezing a metric that was actually canonical breaks downstream reports that depended on it, while failing to freeze leaves conflicting definitions in circulation. The 30-day window limits both errors by forcing a decision before either cost compounds.

Keep the cost ledger simple: analyst-hours per adjudicated duplicate, median detection-to-ratification days, and count of metrics frozen beyond the 30-day cap. If frozen count rises while median cycle time stays flat, the board is rejecting or deferring rather than deciding; if both rise, capacity is the constraint.

What this evidence does not establish

The scan evidence shows how many duplicate candidates a 90-day lineage scan can surface, but it does not establish that every candidate is a true duplicate or that the same handling fits every metric type. Treat scan output as a candidate list: some apparent duplicates are one governed definition seen through generated SQL, while some genuine duplicates never appear because their lineage stops at a platform boundary. Before acting on a candidate, verify it against your own lineage data and metric inventory.

For metrics defined once in a semantic layer such as dbt metrics, Cube, or LookML but queried through generated SQL, lineage scans can report many duplicates that are actually one governed definition. Before flagging, check for a shared semantic-layer ID; if it exists, collapse the variants and do not route them to curation. Flag only when no shared ID can be found.

Streaming or near-real-time metrics and their batch twins can share calculation logic while differing in latency, so they are genuinely different metrics rather than duplicates. The 90-day adjudication rule weakens here. Resolve the pair through a naming convention that adds a freshness suffix to the metric name, and keep both definitions instead of forcing one canonical choice.

Duplicates that span platforms may have no shared lineage for a single scan to follow. A warehouse- or BI-scoped scan can miss them or treat platform-specific wrappers as separate metrics. Before flagging, compare cross-platform identifiers such as source table, owner, and query text; where lineage is absent, use attestation or a manual inventory. If the only difference is refresh or access path, classify the pair as a distribution variant, not a duplicate.

In practice, pre-filter candidates with these checks, then let the curation board ratify only true duplicates with shared lineage. Edge cases should be resolved by identity or naming rules before the freeze trigger is considered.

One metric, 90 days

Take one metric through one 90-day cycle. In an illustrative scan of query history touching gross_transactions and refunds, the lineage scan found 340 queries that clustered into four net-revenue logics. The differences were not cosmetic: they split on refund timing and on the date used for currency conversion. Treat the scan as a triage list, not a verdict; it shows where definitions diverged, while the curation board still has to name the canonical one.

Checkpoint 1 is usage share. Variant A, the refund-date-based logic, covered 61% of the 340 queries. That share is a strong starting point for discussion, but it is not proof of correctness; a high-volume report can still encode the wrong business rule. Checkpoint 2 is scope of disagreement. Variant C differed only on the FX conversion date, and that difference affected one EU reporting team. A narrow blast radius can make a variant look harmless, yet it still changes a reported number for that team. Checkpoint 3 is blast radius in assets: count how many dashboards, models, or exports depend on each variant before anyone votes, so the board sees migration cost alongside usage.

The ratification worksheet below is the artifact the board fills in for each detected duplicate. Copy it, one row per variant, and require a single canonical decision and an owner signature within the 30-day window; if the board does not ratify one definition, the metric is frozen rather than left with competing versions.

After ratification, keep the worksheet with the scan output. The next 90-day scan should show whether the non-canonical variants actually retired or merely moved to a new query path; if they persist, the issue is not detection but adoption, and the board should revisit ownership or documentation rather than rerun the same debate.

Worked Example: Run the Numbers

This section works one illustrative governance cycle end to end; every count below is an illustration, not measured program data. Use it as a planning heuristic and reproduce the comparison in your own warehouse before relying on it.

Inputs (illustration): scenario = one cross-team metric family, "monthly active enterprise accounts"; party = Analytics Engineering runs the 90-day query-lineage reuse scan, and the Data Governance Council is the human curation board; dates = a 90-day scan window followed by a 30-day ratification deadline, with day 0 as scan completion and day 30 as the board deadline. No calendar date is asserted.

Calculation steps (illustration): assume human curation finds 12 duplicate definitions in the comparable cycle. At the stated 3-5x range, the automated scan finds 12 × 3 = 36 to 12 × 5 = 60 duplicates. The incremental gain is 36 − 12 = 24 to 60 − 12 = 48 duplicates. Spread across the 30-day ratification window, the board must ratify 36 ÷ 30 = 1.2 to 60 ÷ 30 = 2.0 canonical definitions per day. The scan is treated as a detector only: it does not choose the canonical definition, so 36 to 60 findings become 36 to 60 board items.

Winner for this example: the hybrid design—automated 90-day scan plus human board—because the scan supplies the larger duplicate set (36 to 60 versus 12) while the board still makes the canonical call. Break-even trigger: the board must sustain at least 2.0 canonical ratifications per day to clear the 5x case within 30 days. If sustained capacity is below 1.2 per day, even the 3x case exceeds the deadline; if capacity is from 1.2 to just below 2.0 per day, the 3x case fits but the 5x case triggers freezes. If capacity cannot reach 2.0, this example favors narrowing scan scope or adding board capacity before expanding automation.

Decision rules for metric adjudication

The adjudication rules are five if/then statements: if a detected duplicate has no downstream assets, merge it into the canonical variant immediately; if two variants differ on a definitional choice such as refund timing or FX date, treat the variant feeding more downstream assets as presumed canonical and require the challenger to justify any merge; if the curation board cannot ratify one canonical definition within 30 days, freeze all variants read-only and record the unresolved conflict as a data-quality footnote on every dependent dashboard; if a duplicate is already represented by an open curation case, attach the new scan evidence to that case instead of opening a parallel one; and if a challenger shows that the presumed canonical variant would break a documented reporting obligation, the board must either ratify a documented exception or freeze the variants, but it may not silently merge them.

Apply the no-downstream rule by checking lineage before scheduling board time: when the scan shows zero dashboards, models, or certified reports depending on a duplicate, merge it into the canonical variant and retain the lineage note for audit. This keeps the board focused on conflicts that actually affect consumers.

For definitional splits, compare downstream asset counts rather than debating preference first. The variant with broader downstream use is the starting canonical; the challenger carries the burden of showing why its definition should replace or coexist with it. Refund timing and FX date are typical split points, but the rule is the same for any definitional choice: usage breadth sets the default, and the challenger must make the case for change.

The 30-day clock starts when the duplicate is routed to the board, not when it is detected. If ratification slips, freeze every variant read-only so no new dependency can form, and add the same data-quality footnote to each dependent dashboard until the conflict is resolved. The freeze is a containment step, not a decision; it preserves evidence while the board completes review.

Keep adjudication auditable by attaching each new 90-day scan result to an existing open case when one exists, and by requiring any exception to a presumed-canonical ruling to be documented against a specific reporting obligation. A merge should leave one canonical definition, one retirement record for each duplicate, and no unresolved footnote.

Frequently Asked Questions

How much more duplicate-definition volume do automated scans surface than human curation cycles?

Automated 90-day cross-team query-reuse scans find 3-5x more duplicate metric definitions than human curation cycles.

What comparison should the scan's duplicate count be checked against?

Run the scan on a 90-day window and compare its duplicate count with the count from human curation cycles.

Who has authority to decide which duplicate is canonical?

Only human curation can adjudicate which duplicate is canonical.

What is the required action for every detected duplicate?

Route every detected duplicate to a human curation board for canonical selection.

What triggers the start of the ratification period?

The 30-day period begins when a duplicate is routed to the board.

What happens if no single canonical definition is ratified?

If no single canonical definition is ratified within 30 days, the metric is frozen.

Quick answers

Also worth reading: Cutting audit prep time: Abacus vs Alation vs OneTrust to 5.1 days in 2026: Cutting audit prep time: Abacus · Move data tables safely: Hive to Unity Catalog 1,200-Table Migrate vs Federate: Move data tables safely: Hive · Wiki ROI: The Truth Behind 40% Deflection and 3-Day Onboarding: Wiki ROI: The Truth Behind