Template-based sql data warehouse near-duplicate model propagation for knowledge identification and management

Abstract

The application provides a template SQL data warehouse near-duplicate model propagation sensing and management method, natural language phrase preprocessing is performed on model names, a first layer wide recall is performed in combination with low-cost signals, a small amount of candidates are quickly screened out from full models, business concept consistency discrimination and SQL logic deep comparison are sequentially performed on the candidate model pairs, layer-by-layer convergence is performed, and model pairs that truly constitute near-duplicate relationships are confirmed. The confirmed near-duplicate model pairs will be introduced into version history analysis, and through time characteristics such as first occurrence time difference and coexistence time length, two cause types of "creation is redundancy" and "replacement fails residual" are distinguished. For the identified residual, whether it induces derived redundancy is tracked downstream along the pedigree diagram, and multiple near-duplicates on the same propagation chain are merged into the root residual at the most upstream through propagation folding. Finally, the propagation coverage range, coexistence time length and other factors are comprehensively considered to generate a management priority ranking for the root residual.

Description

Technical Field

[0001] This invention relates to the fields of data warehouse engineering and data lineage analysis, and in particular to a method for sensing, identifying and managing the propagation of near-duplicate models in templated SQL data warehouses, as well as an electronic device and computer-readable storage medium for sensing, identifying and managing the propagation of near-duplicate models in templated SQL data warehouses. Background Technology

[0002] dbt (Data Build Tool) is a widely adopted data warehouse transformation framework. Unlike directly writing and executing raw SQL, dbt organizes SQL code using a template-based approach: developers use template functions like `ref()` and `source()` in SQL files to declare dependencies on other models or external data sources. These template functions are parsed and replaced with the actual table names in the target data warehouse by the template engine during the compilation phase. For example, `{{ref('order_items')}}` in the SQL file will be replaced with a complete table reference like `analytics.public.order_items` after compilation. This compile-time dependency declaration mechanism allows dbt to statically extract all reference relationships between models from the source code, constructing a directed acyclic model hierarchy graph. Simultaneously, the dbt compilation process generates structured parsing artifacts (such as `manifest.json`), which record metadata information such as the name, file path, dependencies, and output column definitions of each model, providing a machine-readable entry point for subsequent automated analysis.

[0003] In a DBT project, each data model corresponds to an SQL file, defining the logic for reading data from upstream, performing transformations, and outputting a result table. Models are typically divided into multiple layers according to the transformation stage: the staging layer is responsible for reading raw data from external data sources and performing basic cleaning and type standardization; the intermediate layer performs intermediate calculations and business logic concatenation based on the staging layer; and the mart layer processes the intermediate results into summary tables or dimension tables for final business consumption. Data flows unidirectionally between these layers, with each layer's model referencing the model of the previous layer as input via `ref()`. A typical medium-sized DBT project contains hundreds of models and is maintained collaboratively by multiple people over a long period.

[0004] As projects evolve, redundancy gradually accumulates in the model repository. A common scenario is that a team creates a new model to replace an old one to improve the computational logic of a particular model. However, the old model is not removed promptly after the new model is deployed, and the two coexist in the repository under different names for an extended period. Because these two models often follow different naming conventions or originate from different design phases, it's difficult to discern from their names that they are actually performing the same task. This implicit near-duplication of "different names but the same meaning" worsens over time. Since the dbt model declares its dependency on the upstream model using ref(), when the same business logic is implemented by two different models upstream, different downstream developers will each use one as input to build their own model. In this way, the redundant structure upstream is "replicated" downstream—two corresponding intermediate computational models appear in the intermediate layer, and two corresponding summary tables appear in the mart layer, forming a redundancy propagation chain from upstream to downstream.

[0005] In recent years, the widespread adoption of AI-assisted SQL generation tools in data warehouse development has significantly exacerbated the aforementioned redundancy issues. These tools allow even junior analysts lacking extensive SQL experience to quickly generate fully functional models. However, AI-generated tools typically lack a global awareness of existing models in the target warehouse. They tend to generate a new SQL model based on the user's natural language description, rather than locating and reusing existing, functionally similar models in the warehouse. This "generating new models" rather than "reusing existing models" behavior leads to a faster accumulation of implicit near-duplications with different names but similar meanings in the warehouse. Meanwhile, existing warehouse governance methods still rely primarily on manual and rule-based reviews, supplemented by limited AI checks. Their processing speed is far slower than the growth rate of models generated by AI-assisted generation, creating an imbalance of "fast generation, slow governance," which accelerates the overall degradation of data warehouse quality.

[0006] Existing repository governance tools have limited ability to detect the aforementioned problems. SQL lint tools primarily focus on format and syntax specifications, naming rule checks can detect variants with explicit suffixes such as _v2, _legacy, and _tmp, and graph structure rule checks can identify isolated nodes or simple loop anomalies. However, these tools remain at the surface level and cannot identify two models with completely different names but highly overlapping business semantics and transformation logic. Even when introducing common code clone detection methods already available in software engineering (such as comparisons based on lexical tag sequences or abstract syntax tree structures), there are two limitations in the SQL model scenario: First, the "similarity" of SQL models is often reflected at the business semantic level rather than the text level—two SQL statements can be written in completely different ways (different column aliases, different join orders, different subquery splitting methods) but achieve the same business calculations, and text-level clone detection struggles to capture this semantic equivalence; second, SQL models are embedded in the dependency graph via ref(), and comparing two SQL statements without considering the upstream and downstream dependency context will lose crucial structural information. Furthermore, general code similarity detection can only provide a similarity score at a certain point in time, lacking the ability to distinguish between "reasonable coexistence" and "failed replacement." When redundancy propagates downstream along the spectrum, existing tools report near-duplication at each layer, repeatedly counting a root problem as multiple downstream problems, leading to a distortion of governance priorities.

[0007] In summary, existing technologies have three main shortcomings. First, they struggle to detect implicit near-duplications with different names but the same meaning. Second, they cannot combine temporal evidence to determine whether near-duplication models represent reasonable architectural division of labor or failed replacement residuals that have not been properly cleaned up over a long period. Third, they lack mechanisms for tracking and folding redundancy propagation along the spectrum, making it impossible to trace multi-layered derived redundancy back to its root cause and thus unable to output truly executable governance priorities. Summary of the Invention

[0008] To address the technical problems existing in the prior art, the present invention provides the following technical solution:

[0009] On the one hand, a method for perceiving, identifying, and managing the propagation of near-duplicate models in templated SQL data warehouses is provided, including the following steps:

[0010] Constructing a model dependency graph: Obtain the model nodes in the target data warehouse project and the dependencies between them, and construct a model dependency graph;

[0011] Generate semantic nearest neighbor candidates: Perform natural language phraseification preprocessing on the model name, and recall candidates based on one or more of the following: semantic similarity, name lexical overlap, string similarity, and upstream source overlap;

[0012] Determine the set of near-duplicate objects to be analyzed: Perform business concept consistency judgment and SQL logic near-duplicate confirmation on the recalled candidate model objects, and retain objects that meet the conditions of business semantic similarity, SQL transformation logic similarity and continuous coexistence in combination with the conditions of continuous coexistence.

[0013] For each near-repeating object in the set of near-repeating objects to be analyzed, search downstream along the model dependency graph for downstream model nodes that depend on the corresponding model nodes in the near-repeating objects, and identify downstream derived near-repeating objects formed by the propagation of upstream near-repeating objects;

[0014] Based on the propagation relationship between the downstream derived near-duplicate objects and the corresponding upstream near-duplicate objects, a propagation source identifier is assigned to each near-duplicate object;

[0015] Nearly duplicated objects with propagation source identifiers are marked as derived redundant objects, and nearly duplicated objects without upstream propagation source identifiers are marked as root residual objects.

[0016] Output the set of root residual objects and the set of derived redundant objects covered by each root residual object.

[0017] Preferably, the condition for continued coexistence is determined based on at least one or more of the following information: the time difference of the first appearance of the model node, the duration of coexistence, whether there is cleanup of semantic evidence, whether it is currently in an unresolved state, or whether there is a common modification pattern in subsequent submissions; wherein, the common modification pattern refers to multiple model nodes constituting the same near-duplicate object being modified simultaneously by the same submission in multiple subsequent submissions.

[0018] Preferably, the near-duplicate objects are classified into creation-is-redundant type and replacement-failure type based on the time difference of the first appearance of the model nodes. The creation-is-redundant type refers to multiple model nodes constituting the near-duplicate object being introduced into the repository at similar times or in the same change. The replacement-failure type refers to the new model node being created later than the old model node and the old model node not being removed after the new model node is launched.

[0019] Preferably, the process of determining the set of near-duplicate objects to be analyzed includes: performing natural language phrase preprocessing on the model name, and then performing candidate recall based on one or more of semantic similarity, name lexical overlap, string similarity, and upstream source overlap; performing business concept consistency judgment and SQL logic near-duplicate confirmation on the recalled candidate model objects; and retaining the model objects that simultaneously meet the conditions of business concept consistency, SQL logic near-duplicate, and continuous coexistence as the set of near-duplicate objects to be analyzed.

[0020] Preferably, the confirmation of near-duplicate SQL logic is based on at least one or more of the following features: upstream dependency features, output pattern features, transformation logic features, or granularity constraint features; wherein, the upstream dependency features include the degree of overlap of ref function calls or the degree of overlap of source function calls, and the transformation logic features include similarity of filtering conditions, similarity of join logic, and similarity of aggregation methods.

[0021] Preferably, downstream near-duplicate objects are identified as being formed by the propagation of upstream near-duplicate objects, satisfying at least one or more of the following conditions: multiple model nodes in the downstream near-duplicate objects depend on corresponding model nodes in the upstream near-duplicate objects; the main upstream differences of the downstream near-duplicate objects can be explained by the upstream near-duplicate objects; after removing the corresponding upstream differences, the near-duplicate degree of the downstream near-duplicate objects is reduced; and the downstream near-duplicate objects and the upstream near-duplicate objects form a corresponding relationship in terms of hierarchy, name family, field source, or logical structure.

[0022] Preferably, the method further includes:

[0023] Based on the coexistence duration, propagation layers, number of derived redundant objects covered by propagation, number of downstream affected nodes, frequency of joint modification, and presence or absence of semantic evidence cleanup for each root residual object, the governance priority of the root residual objects is generated.

[0024] On the other hand, a templated SQL data warehouse near-duplicate model propagation awareness, identification, and governance device is provided to implement the above method. It includes a dependency graph construction unit, a near-duplicate object determination unit, a propagation identification unit, a propagation source allocation unit, a root source folding unit, and a result output unit, wherein:

[0025] The dependency graph construction unit is used to obtain the model nodes in the target data warehouse project and the dependency relationships between the model nodes, and to construct the model dependency graph.

[0026] The near-duplicate object determination unit is used to determine the set of near-duplicate objects to be analyzed;

[0027] The propagation identification unit is used to identify downstream derived near-repeating objects formed by the propagation of upstream near-repeating objects along the model dependency graph;

[0028] The propagation source allocation unit is used to assign a propagation source identifier to each near-duplicate object;

[0029] The root source folding unit is used to mark near-duplicate objects with propagation source identifiers as derived redundant objects and to mark near-duplicate objects without upstream propagation source identifiers as root source residual objects.

[0030] The result output unit is used to output the root residual object set and the derived redundant object set covered by each root residual object.

[0031] On the other hand, an electronic device is provided, comprising: a processor; and a memory storing computer-readable instructions, which, when executed by the processor, implement the method described above.

[0032] On the other hand, a computer-readable storage medium is provided, wherein at least one instruction is stored therein, the at least one instruction being loaded and executed by a processor to implement the above method.

[0033] The beneficial effects of the technical solutions provided in the embodiments of the present invention include at least the following:

[0034] First, it improves the ability to detect implicit redundancy. This invention, through the combination of name semantic encoding and deep comparison of SQL logic, can identify model pairs with completely different names but highly overlapping business semantics and transformation logic, thus compensating for the omissions in implicit near-duplication by naming rules and suffix matching methods.

[0035] Second, it reduces candidate noise and the cost of manual verification. The hierarchical funnel mechanism converges the quadratic combination of all model pairs layer by layer to a small number of high-value candidates, so that the high-cost semantic comparison is only applied to the most suspicious objects.

[0036] Third, it distinguishes between reasonable coexistence and failed replacement. By introducing the first occurrence time difference, coexistence duration, and cleanup semantic evidence in the version history, this invention can further converge general similarity model pairs into long-term coexistence near-repetitive residuals with clear governance significance, avoiding misjudging normal architectural division of labor (such as regional sharding, hierarchical derivation, and adjacent business objects) as redundancy.

[0037] Fourth, it eliminates duplicate counting of problems caused by propagation chains. By tracing the downstream propagation of redundancy along the phylogenetic tree and performing propagation folding, this invention retains only the root source residual as an independent governance object, merging multiple layers of derived redundancy on the same propagation chain to its root source, so that the number of output problems is consistent with the actual number of governance objects.

[0038] Fifth, output executable governance priorities. Prioritize actions based on factors such as propagation coverage, coexistence duration, parallel maintenance frequency, and unresolved status, ensuring that cleanup actions target the root causes with the greatest impact, thus improving the efficiency of governance resource allocation.

[0039] Sixth, it balances post-event cleanup with pre-event prevention. This invention can be used for a full-scale inspection of existing warehouses, or integrated into CI / PR processes to detect near-duplication risks between new and existing models in real time when they are introduced, providing early warnings at the initial stage of redundancy. Attached Figure Description

[0040] To more clearly illustrate the technical solutions in the embodiments of the present invention, the accompanying drawings used in the description of the embodiments will be briefly introduced below. Obviously, the accompanying drawings described below are only some embodiments of the present invention. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort.

[0041] Figure 1 This is a general technical concept diagram of a method provided in an embodiment of the present invention;

[0042] Figure 2 This is a schematic diagram of the implementation process of a method provided in an embodiment of the present invention;

[0043] Figure 3 This is a comparison image before and after folding provided by an embodiment of the present invention. Detailed Implementation

[0044] The technical solutions of the present invention will be described in detail below with reference to the accompanying drawings and specific embodiments.

[0045] Terminology Explanation:

[0046] The model described in this application is as follows: In a templated SQL data warehouse framework represented by DBT, the model is the basic unit of data transformation, typically corresponding to an SQL file. Each model defines a piece of logic that reads data from upstream data sources or other models, performs transformations, and outputs a result table.

[0047] Model hierarchy graph: A directed acyclic graph consisting of model nodes and their dependencies. Dependencies are declared using template functions such as `ref()` and `source()`, indicating that the input of one model comes from the output of another model or an external data source.

[0048] Hierarchy: DBT projects typically divide the model into multiple hierarchical levels according to the transformation stage, the most common being staging (raw data cleaning).

[0049] intermediate (intermediate computation) and mart (final business-oriented output). The hierarchy is reflected in the directory structure and model naming prefixes.

[0050] Nearly duplicate model pairs refer to two models that are not exactly the same in name, but reach a predetermined degree of similarity in business semantics, upstream dependencies, output column semantics, and SQL transformation logic. Determining near duplication requires a comprehensive consideration of multiple dimensions; similarity in name or a single metric alone does not constitute near duplication.

[0051] Residual cluster: When near-repetitive relationships involve more than two models (e.g., multiple variants of the same business concept such as A_old, A_new, A_newer, etc.), the set formed by merging these pairwise near-repetitive models is called a residual cluster.

[0052] The long-term coexisting near-repeating residuals in this application may include cause types such as replacement failure residuals and creation-based redundancy.

[0053] Substitution failure residuals: Near-repeating model pairs or residual clusters that meet the following conditions: the near-repeating relationship has persisted for more than a preset coexistence duration threshold, no evidence of substitution completion or cleanup closure has been detected in the version history, and the current snapshot remains unresolved. Substitution failure residuals are a type of cause of long-term coexisting near-repeating residuals.

[0054] Redundancy upon creation: Two nearly duplicate models are introduced into the repository at close intervals or in the same batch of changes, constituting a duplicate cause type from the moment of creation.

[0055] Derived redundancy: As upstream near-repeating models continue to be referenced by different downstream models, redundancy arises in the corresponding near-repeating structures downstream. The near-repeating nature of derived redundancy can be explained by its upstream near-repeating relationships.

[0056] Root residual: After propagation folding, the nearest upstream repeating object that can no longer be explained by more upstream redundant relationships. The root residual is the independent governance object ultimately output by this invention.

[0057] Propagation folding: The operation of merging multiple near-repeating pairs on the same propagation chain into the upstream root residual. After folding, derived redundancy is no longer counted as an independent problem, but only recorded as the coverage of its root residual.

[0058] Cleaning semantic evidence: Records of alternative completions, model deprecation, or cleanup closure intentions expressed in commit messages or PR descriptions in the version history. This can be extracted through keyword matching (such as remove, deprecate, consolidate, migrate, etc.) or semantic judgment using a large language model.

[0059] Parallel maintenance mode: The phenomenon that two nearly identical models are repeatedly modified by the same submission in subsequent maintenance phases serves as an auxiliary signal for residual determination, indicating that both are continuously generating double maintenance costs.

[0060] Uncertain dependency nodes: These are model nodes involving dynamic ref(), macro expansion, conditional activation, etc., whose dependencies cannot be fully determined by static analysis. These nodes have their confidence reduced in residual evaluation and are marked as requiring manual verification in the final output.

[0061] The technical application of this application will be described in detail below.

[0062] I. Technical Problem to be Solved by the Invention

[0063] To address the shortcomings of the aforementioned background technologies, the technical problem this invention aims to solve is: how to automatically discover model pairs with different names but highly overlapping business semantics and transformation logic in a templated SQL data warehouse; then, by combining version history, determine whether these nearly duplicated model pairs belong to reasonable architectural coexistence or long-term coexisting near-duplicate residuals; and trace the propagation of these residuals downstream along the model dependency spectrum. Finally, through a folding mechanism, the multi-layered derived redundancy on the same propagation chain is attributed to its root cause, outputting governance objects with accurate quantity and interpretable priority. Incidentally, this invention also needs to solve two engineering-level problems. First, in a warehouse containing hundreds or even thousands of models, the number of combinations for full pairwise comparisons grows quadratically, requiring a low-cost candidate convergence mechanism to control computational overhead. Second, templated SQL contains dependencies such as dynamic ref(), macro expansion, and conditional activation that cannot be fully determined by static parsing. These uncertainties need to be identified and handled to ensure the deployability of the method in real-world projects.

[0064] II. Technical Objectives of the Invention

[0065] This invention proposes a hierarchical and progressive identification method. First, the method extracts model nodes and their dependencies from the source code or parsed artifacts of templated SQL projects, constructing a model genealogy graph. Based on this, the model names undergo natural language phrase preprocessing, and a first-layer broad recall is performed using low-cost signals such as name semantic similarity and upstream source overlap, quickly filtering out a small number of candidates from the full set of model pairs. Subsequently, business concept consistency judgment and deep SQL logic comparison are sequentially performed on the candidate model pairs, converging layer by layer to confirm the model pairs that truly constitute near-duplicate relationships. The confirmed near-duplicate model pairs are then subjected to version history analysis, distinguishing between "creation-based redundancy" and "replacement failure residuals" based on time features such as first appearance time difference, coexistence duration, and cleanup of semantic evidence. For identified residuals, the method traces downstream along the genealogy graph to determine if they have induced derived redundancy, and merges multiple near-duplicate layers on the same propagation chain into the upstream root residual through propagation folding. Finally, factors such as propagation coverage, coexistence duration, and parallel maintenance frequency are considered to prioritize the governance of root residuals.

[0066] III. Detailed Description of the Technical Solution of the Invention

[0067] The technical solution of this invention comprises eight steps, namely: constructing a dependency graph (S1), generating semantic nearest neighbor candidates (S2), determining business concept consistency (S3), performing deep SQL logic comparison (S4), analyzing version history and determining residual type (S5), tracing the propagation chain (S6), folding propagation (S7), and generating governance priorities (S8). S2 to S4 form a hierarchical funnel, progressively narrowing down the candidate size; S5 introduces a time dimension, converging near-repeating results into residuals; S6 to S7 address propagation issues along the spectrum; and S8 generates the final output. The specific implementation flow of the method is as follows: Figure 2 As shown.

[0068] The following will explain step by step.

[0069] S1 constructs the model dependency graph

[0070] Read the model SQL files, project configuration files, or compiled parsing artifacts from the target data warehouse project, extract all model nodes and their inter-node references, and construct a directed dependency graph. The sources of these references include `ref()` and `source()` calls in the SQL files, as well as node dependency fields recorded in the parsing artifacts. Each node in the graph is associated with the following attributes: model name, file path, SQL text, hierarchy (e.g., staging, intermediate, mart), upstream and downstream adjacency relationships, and historical records such as creation time, modification time, deletion time, commit information, and PR information that can be extracted from the version control system.

[0071] Templated SQL data warehouses may contain dependencies that cannot be fully determined by static parsing. Typical scenarios include: dynamically generated `ref()` calls via variable concatenation, references that require macro expansion to become visible, conditional model activation controlled by `enabled=false` or `if var(...)`, and conditional dependencies based on data source or tenant branch. For model nodes involved in these scenarios, this invention marks them as indeterminate dependency nodes. This marking does not affect the node's participation in subsequent steps, but it reduces the confidence level of the relevant results during the residual determination stage and identifies them separately in the final output as objects requiring manual verification.

[0072] S2 generates semantic nearest neighbor candidate models.

[0073] The goal of this step is to quickly filter out a small number of candidate model pairs that have different names but similar semantics from the full set of model pairs for further in-depth comparison in subsequent steps. In a repository containing n models, the number of full model pairs is n(n-1) / 2. Directly performing high-cost semantic analysis on all model pairs is computationally infeasible, so we need to use low-cost signals for broad recall.

[0074] One key low-cost signal is similarity measurement based on text semantic encoding (embedding). Pre-trained language models can map a piece of text to a point in a high-dimensional vector space. Texts with similar semantics are close to each other in this space, and the semantic proximity of two texts can be quantified by calculating the cosine similarity between the two vectors. This technique has been widely applied in scenarios such as document retrieval and semantic matching. In this step, model names can be encoded as embedding vectors, and cosine similarity can be used to measure the semantic proximity of two model names in terms of business applications. Besides embedding similarity, signals that can be used for candidate recall include name token overlap rate, string edit distance, upstream source overlap, output column set similarity, and directory position or hierarchy constraints. These signals have low computational cost and can be executed quickly on all model pairs. Preferably, this step only retains candidate model pairs with different names but similar semantics, excluding simple repetitions with the same name or only explicit version suffix differences (such as _v2, _legacy), as the latter can already be detected by existing naming rule tools.

[0075] In a preferred implementation, the model name is first converted into a natural language phrase before being fed into the embedding encoder. The conversion process includes: removing prefixes indicating hierarchy (such as stg_, int_, dim_, fct_), replacing underscores with spaces, retaining the business concept words before and after the double underscore separator, and moving the hierarchy semantics to the end of the phrase. For example, the model name stg_ntd_capital_expenditures_time_series_total is converted to ntd capitalexpenditures time series total staging. This preprocessing allows the embedding encoder to better capture the business concept semantics in the model name and reduces the noise caused by prefixes and separators in the DBT naming format.

[0076] S3 Business Concept Consistency Judgment

[0077] The candidate model pairs recalled by S2 already have a certain degree of similarity in name semantics, but they still contain a large number of model pairs that are "in the same business domain but point to different business concepts". The goal of this step is to eliminate these false positive candidates and retain only model pairs that are likely to constitute a substitution relationship in terms of business concepts (hereinafter referred to as likely pairs).

[0078] This discrimination can be achieved through rule templates, classifiers, large language models, or a joint decision using the above methods. The core issue of discrimination is whether the two models are describing the same business entity or calculating the same business metric, or whether they only exhibit similarity in name due to being in adjacent business domains.

[0079] In a preferred implementation, this step employs a large language model for discrimination. Specifically, the two model names of the candidate model pair (in the form of natural language phrase preprocessing as described in S2) are taken as input, prompting the large language model to determine whether they refer to the same business concept, and outputting the classification result (e.g., yes, likely, no, unclear) and the basis for the judgment. The advantage of the large language model in this step lies in its understanding of business domain terminology: it can distinguish between "capitalexpenditures" and "operating expenditures," which, despite their highly similar name structures, belong to different financial concepts, and it can also identify between "capital_expenditures_time_series_total" and "operating_and_capital_funding_time_series_capital_total," which, despite significant naming differences, actually refer to the same business indicator. Candidate model pairs that are only judged as likely or yes proceed to the next step.

[0080] S4 SQL Logic Deep Comparison

[0081] The `likely` pairs retained by S3 are compared using higher-cost SQL logic to confirm whether they constitute substantial near-duplication. The comparison dimensions cover four sets of features. The first set is upstream dependency features, examining whether the `ref()` and `source()` references of the two models point to the same or highly similar upstream sources. The second set is output pattern features, examining whether the output columns of the two models are consistent in name and semantics. The third set is transformation logic features, examining whether the filtering conditions, join logic, aggregation methods, and expression structures are substantially similar. The fourth set is granularity and constraint features, examining whether the aggregation granularity and time window logic are consistent, and whether the differences between them are limited to minor field renaming, field rearrangement, partial supplementation, or minor restructuring.

[0082] In a preferred implementation, this step employs a large language model to perform a comprehensive comparison across the four dimensions mentioned above. Specifically, the SQL texts on both sides of the candidate model pair are taken as input, prompting the large language model to compare them one by one from four dimensions: upstream dependency, output pattern, transformation logic, and granularity constraints. The output includes a degree of overlap determination (e.g., near-duplicate, partial-overlap, different), a structured description of key differences, a list of shared upstream data sources, and a preliminary judgment on whether a substitution failure has occurred, along with its reasoning process. The value of the large language model in this step lies in its ability to understand SQL semantics: it can identify two SQL statements that, although differing in literal spelling (e.g., different column aliases, different field ordering, different source table names), perform essentially the same business transformation logic; it can also identify two SQL statements that, although structurally similar, belong to different business calculations due to differences in granularity, time windows, or aggregation objects.

[0083] When a pair of models exhibits high overlap across the four dimensions mentioned above, and their differences can be interpreted as different expressions of the same business logic, the model pair is identified as a near-duplication model pair.

[0084] In real-world warehouses, substitution failures sometimes involve more than two objects. For example, the same business concept may have multiple variants such as A_old, A_new, and A_newer. In such cases, this invention groups multiple nearly identical models into a single residual cluster, and subsequent steps such as residual determination, propagation tracing, and folding are all processed on a cluster-by-cluster basis.

[0085] S5 Version Historical Analysis and Residual Type Determination

[0086] For near-duplicate model pairs or residual clusters confirmed by S4, this step introduces a time-dimensional analysis to distinguish between "similar but reasonably coexisting" and "similar residuals that have not been cleaned up for a long time." This step extracts the following time features from the version control system: the time when each model first appeared in the repository and the time difference between them; the duration of coexistence from their first appearance to the current snapshot; the time of their most recent joint active modification; and whether the commit messages and PR descriptions contain cleanup semantic evidence. The extraction of cleanup semantic evidence can be achieved through keyword matching or semantic judgment using a large language model. Keyword matching uses words such as remove, deprecate, consolidate, and migrate as trigger conditions, suitable for fast batch scanning. The large language model semantic judgment directly reads the complete text of the submission information and PR (Pull Request) description to determine whether it expresses the intention of replacement completion, model deprecation, or cleanup closure. It can identify implicit semantics missed by keyword methods (e.g., "handle cutover for v3" implies version switching but does not contain the above keywords) and can also eliminate false positives from keyword methods (e.g., "clean up column names" contains the keyword "clean" but actually only renames fields, not model-level cleanup). Both methods can be used individually, or keyword matching can be used for coarse screening first, followed by fine-tuning using the large language model.

[0087] Based on the above characteristics, nearly duplicated objects can be divided into two types of causes. The first is redundancy upon creation, where two nearly duplicated models are introduced into the repository at similar times or during the same change, constituting duplication from the very beginning. The second is replacement failure, where the creation time of the new model is significantly later than that of the old model, and the old model does not exit as expected after the new model goes live, resulting in long-term coexistence.

[0088] Preferably, near-duplicate objects can be identified as replacement failure residuals based on the following rules: the time difference between the first appearances of the two models exceeds a preset threshold, the coexistence duration exceeds a preset threshold, no evidence of replacement completion or cleanup closure is detected in the version history, and the near-duplicate relationship remains unresolved as of the current snapshot. The threshold can be set weekly, monthly, or by project historical quantile, and can be adjusted according to repository size, model hierarchical position, or change frequency.

[0089] Parallel maintenance patterns can serve as auxiliary decision signals. If two nearly identical models are repeatedly modified by the same submission in subsequent maintenance phases, it indicates that they are continuously generating double maintenance costs, which can further enhance their confidence in being used as residuals.

[0090] S6 transmission chain tracing

[0091] For near-repeating model pairs or residual clusters identified as residuals by S5, this step traces downstream along the dependency graph to determine if they induce derived redundancy. The basic logic of the tracing is as follows: If there is a near-repeating model pair (A_old, A_new) upstream, and downstream there are models B_old referencing A_old and B_new referencing A_new, and (B_old, B_new) also satisfies the near-repeating condition, then (B_old, B_new) is identified as a derived redundant pair formed by the propagation of the upstream residual (A_old, A_new). This propagation can span multiple levels, for example, from staging to intermediate, and then from intermediate to mart, forming a multi-level propagation chain.

[0092] Preferably, the following conditions can be considered when determining whether a downstream near-repeating pair is derived from upstream propagation: the two models in the downstream pair depend on their corresponding models in the upstream pair; the main upstream differences in the downstream pair can be explained by the upstream near-repeating pair; the near-repeating nature of the downstream pair is significantly reduced after removing the corresponding upstream differences; and the downstream pair and the upstream pair form a correspondence in terms of hierarchy, name family, field source, or logical structure. In scenarios with mixed dependencies, partial shared upstream sources, or multi-source propagation, the propagation relationship can be represented as a propagation edge with confidence, and the most explanatory upstream source can be selected during the folding phase.

[0093] S7 propagation folding

[0094] This step merges multiple near-repetition pairs on the same propagation chain into the upstream root residual, avoiding the repeated counting of a root problem as multiple downstream problems. Specifically, it assigns a propagation source identifier to each near-repetition pair or residual cluster; if a near-repetition object can be traced back to an identified upstream residual, it is marked as a derived redundancy; root residual labels are only retained for objects that cannot be interpreted by further upstream residuals. After folding, the output is the set of root residuals and the complete set of derived redundancies covered by each root residual.

[0095] S8 governance priority generation

[0096] Based on the root cause residual set output by S7, governance priority is calculated by integrating multiple factors. Factors that can be included in the ranking include: coexistence duration, whether it is still in an unresolved state, parallel maintenance frequency, number of layers spanned by the propagation chain, number of derived redundancies covered by propagation, total number of downstream affected model nodes, whether the root cause residual is located in an upstream critical layer, and whether there is evidence of cleanup attempts that were not completed in the version history.

[0097] The final governance report output includes the following information: the identifier of each root residual pair or residual cluster, the cause type of the residual (creation is redundant or replacement failed), coexistence duration, propagation chain length, downstream impact range, and recommended cleanup priority.

[0098] The technical solution of the present invention will be described in detail below through several embodiments. The repositories used in the embodiments are all real dbt projects obtained from the public code hosting platform GitHub, maintained by different organizations over a long period, and have a complete version control history. The experimental data comes from static analysis and version history mining of the source code of these repositories, and does not involve the actual operation or data access of any data warehouse.

[0099] The embodiments of this invention involve the following four repositories. The first is data-infra, maintained by the California Integrated Transportation Project (Cal-ITP), a data repository for managing California's public transportation system, covering business areas such as the Federal Transportation Administration Annual Report (NTD), Transit Data Standard (GTFS), and payment systems, containing 632 models with a version history spanning approximately 5 years. The second is mattermost-data-warehouse, maintained by the data team of the enterprise communication software Mattermost, used to support business operations such as product analysis, revenue accounting, and server telemetry, containing 489 models with a version history spanning approximately 6 years. The third is spellbook, maintained by the blockchain data analytics platform Dune Analytics, used to convert raw on-chain transaction data into analyzable decentralized finance (DeFi) metrics, containing approximately 8,000 models, making it the largest of the four repositories, with a version history spanning approximately 6 years. The fourth is dagster-open-platform, maintained by the development team of the data orchestration tool Dagster, used to manage its own operational data, primarily using snapshot-type models, with a smaller number of models and a version history spanning approximately 3 years. These four repositories differ significantly in their business domains, model sizes, and team characteristics, enabling them to verify the effectiveness of each step of the invention from different perspectives.

[0100] 5.1 Example 1: Near-duplicate discovery and stratified funnel verification across the entire warehouse

[0101] This embodiment uses the latest snapshot of the publicly available DBT project data-infra (cal-itp / data-infra) as an example to demonstrate the complete process of this invention from dependency graph construction to governance shortlist output. This snapshot contains 632 model nodes.

[0102] S1 execution result. The `ref()` and `source()` calls are parsed from the project's SQL file to construct a directed graph of model dependencies. This graph contains 632 nodes and 867 edges, with the longest path depth being 13 layers. The largest weakly connected component covers 91.6% of the nodes, and the total number of weakly connected components is 32.

[0103] S2 execution results: The total number of model pairs from 632 models is 199,396. For each model name, natural language phrase preprocessing was performed, followed by embedding calculation (using the sentence-transformers / all-MiniLM-L6-v2 model), and string similarity was also calculated simultaneously. Using a cosine similarity of at least 0.55 or a string similarity of at least 0.85 as the recall threshold, the first layer recalled 10,633 candidate pairs, compressing the total number of model pairs by 94.7%.

[0104] Among the recalled candidate pairs, 2,710 pairs exhibited high semantic similarity but low string similarity (cosine similarity not less than 0.6 and string similarity less than 0.6). These candidate pairs are characterized by significant spelling differences in their model names, but very close semantic distances after embedding encoding, representing implicit near-duplicate candidates that traditional string similarity methods cannot cover. Simultaneously, 1,777 candidate pairs showed high string similarity but weak semantic similarity, while 4,428 pairs exhibited both high and low semantic similarity.

[0105] S3 Execution Results. To verify the effectiveness of the method, this embodiment selects the 50 candidate pairs with the highest cosine from 2,710 semantic pairs for business concept consistency judgment (this step can be performed on all semantic pairs during full deployment). The judgment results are: 12 pairs were judged as likely (possibly pointing to the same business concept), and 38 pairs were judged as different concepts, with a filtering rate of 76%. The typical characteristics of the 38 candidate pairs that were filtered out are that they are in the same business domain (such as transportation funds) but calculate different indicators (such as total capital expenditure and total operating expenditure), and their names are semantically similar but their business objects are different.

[0106] S4 execution results: Complete SQL text was extracted from 12 likely pairs, and a deep comparison was performed across four dimensions: upstream dependency, output pattern, transformation logic, and granular constraints. Ultimately, four near-duplicate model pairs were identified, and the remaining eight were determined to be reasonably separated models.

[0107] It is worth noting that among the eight model pairs excluded by S4, several were highly misleading in the name discrimination stage (S3), and could not be correctly determined based solely on name semantics (even with the aid of a large language model). For example, dim_agency and dim_agency_information have a cosine similarity of 0.85. The reasoning in the name discrimination stage is that "both are agency dimension tables, the latter may be a more detailed version." However, a deep SQL comparison reveals that the former calculates GTFS operational metadata (agency_id, timezone, fare_url), while the latter calculates NTD federated reporting attributes (ntd_id, state, population, caltrans_district). The two have no overlapping columns or data sources and belong to completely different business domains. For example, both `stg_ntd_capital_expenses_by_capital_use` and `stg_ntd_capital_expenditures_time_series_total` contain "capital expenses / expenditures," suggesting they might be different aggregation methods of the same core concept. However, the former breaks down expenditure details by capital use category, while the latter tracks the total amount over time by year, resulting in completely different business granularity and analytical purposes. Similarly, `fct_daily_feed_scheduled_service_summary` and `fct_daily_schedule_feeds` suggest the latter might be a more direct version of the former. However, the latter is actually a dimension table defining the daily feed coverage, while the former references the latter via `ref()` and aggregates operational metrics (service hours, shifts, number of sites) onto it. There is a direct upstream / downstream reference relationship between the two, rather than a near-duplication relationship. These cases illustrate that S3 (name-level discrimination) and S4 (SQL logical deep comparison) each solve different types of false positive problems, and the combined use of the two layers produces a level of judgment accuracy that no single layer can achieve.

[0108] From a total of 199,396 model pairs to the final four confirmed near-duplications, the convergence ratio of the entire hierarchical funnel was 49,849:1. This result demonstrates the practical feasibility of the hierarchical funnel mechanism in large-scale model repositories, and also shows that each layer of screening is indispensable: embedding recall (S2) can discover implicit semantic pairs that traditional string methods cannot cover at all, but its false positive rate is extremely high; name concept discrimination (S3) can filter candidates with different concepts in the same domain, but as shown in the above case, 67% of false positives still require deep SQL comparison (S4) to identify; and the computational cost of deep SQL comparison determines that it can only be applied to a small number of candidates after the first two layers have converged significantly.

[0109] The four confirmed near-repeating model pairs are as follows.

[0110] The first pair is stg_ntd_capital_expenditures_time_series_total and stg_ntd_operating_and_capital_funding_time_series_capital_total. Both are located in the same directory staging / ntd_funding_and_expenses / , sharing the same external data source external_ntd_funding_and_expenses. Their conversion logic is almost identical, with differences limited to the referenced source table name, year range (from 1992 to 1991), and individual column names (such as uza_name and primary_uza_name).

[0111] The second pair, stg_ntd_capital_expenditures_time_series_other and stg_ntd_operating_and_capital_funding_time_series_capital_other, is the corresponding version of the first pair in the "other" category, exhibiting the exact same redundancy pattern.

[0112] The third pair, int_ntd_capital_expenditures_time_series_other and int_ntd_operating_and_capital_funding_time_series_capital_other, is located in the intermediate layer and references the two staging models in the second pair. The presence of this pair indicates that near repetition in the staging layer has propagated along the dependency graph to the intermediate layer.

[0113] The fourth pair is dim_gtfs_service_data (located in mart / transit_database / ) and dim_provider_gtfs_data_latest (located in mart / transit_database_latest / ). The two are located in different directories, depend on different upstreams, and output different column sets, but the business semantics of the final output are highly overlapping—the latter is essentially a simplified and filtered version of the former (with the WHERE _is_current condition added).

[0114] 5.2 Example 2: Propagation Chain Tracing and Propagation Folding

[0115] This embodiment focuses on the near duplication of the NTD module found in Embodiment 1, and demonstrates the specific execution process of S6 (propagation chain tracing) and S7 (propagation folding).

[0116] Of the four near-repeating pairs identified in Example 1, the first three are all located in the NTD module and exhibit a clear inter-layer propagation relationship. The following uses the second pair (staging layer) and the third pair (intermediate layer) as examples to illustrate the specific operation process for propagation determination.

[0117] First, examine the dependency relationships. The two intermediate models in the third pair, `int_ntd_capital_expenditures_time_series_other` and `int_ntd_operating_and_capital_funding_time_series_capital_other`, have their SQL `ref()` calls pointing to the two staging models in the second pair, `stg_ntd_capital_expenditures_time_series_other` and `stg_ntd_operating_and_capital_funding_time_series_capital_other`, respectively. In other words, each model in the third pair depends on the corresponding model in the second pair, forming a one-to-one upstream and downstream reference structure.

[0118] The explanatory power of the upstream differences was then evaluated. The `ref()` references to the upstream model in both SQL statements of the third pair were uniformly replaced with the same placeholder (i.e., assuming both referenced the same upstream model), and the remaining differences between the two SQL statements after the replacement were compared. After the replacement, the two SQL statements still had minor differences in column names and field selections (such as `uza_name` vs. `primary_uza_name`, `other` vs. `capital_other`, and the number of columns forming the surrogate key). However, these differences corresponded one-to-one with the column name differences output by the upstream staging model, belonging to derived differences inherited from the upstream. The transformation structures of the two SQL statements (unpivot expansion, type conversion, surrogate key generation, and year parsing) were completely identical. This indicates that the near-duplication of the third pair was mainly caused by the propagation of the second pair from the upstream, and it did not introduce independent redundant logic itself.

[0119] Furthermore, downstream of the third pair, there are also fct_capital_expenditures_time_series_other and fct_operating_and_capital_funding_time_series_capital_other, indicating that the redundancy propagation has actually extended from staging through intermediate to the mart layer, forming a three-layer propagation chain.

[0120] Without propagation folding, the system would treat the second and third pairs as two independent problems, outputting two objects to be governed. After propagation folding, the third pair is marked as a derived redundancy formed by the propagation of the second pair, while only the second pair retains the root residual label. Furthermore, the first and second pairs themselves belong to the same business concept (NTD capital expenditure time series) and are parallel redundancies in the "total" and "other" categories, which can be merged into one residual cluster. After folding, the three near-repeating pairs of the NTD module are reduced to one root residual cluster plus one derived redundancy, decreasing the number of objects to be governed from three to one. A comparison before and after folding is provided. Figure 3 As shown.

[0121] Furthermore, by analyzing the Git commit logs of this repository, the version history of three near-duplicate pairs in the NTD module was traced. The analysis revealed that all three pairs first appeared on the same day (February 3, 2025), and both appeared in the initial table creation commit (February 4, 2025). During the subsequent 62-week coexistence period, each pair was modified four times by the same commit: field type fix (February 18, 2025), usability improvement (June 2, 2025), test fix (July 8, 2025), and annual schema update (November 13, 2025). As of the latest snapshot (April 2026), no commits containing cleanup semantics appeared in the version history. The test fix commit (July 8, 2025) is particularly noteworthy—the same test issue required fixing on two near-duplicate models, indicating that the cost of parallel maintenance is not only reflected in regular development but also in defect fixing. This parallel maintenance mode provides an independent confirmation signal for residual determination that does not depend on similarity calculation.

[0122] This example illustrates two key points. First, propagation folding is necessary: ​​in repositories with multiple layers of dependencies, an upstream redundancy issue, if not folded, will be counted repeatedly at every downstream level, leading to distorted governance priorities. Second, the parallel maintenance pattern in the version history can serve as an independent confirmation signal for residual determination, providing a quantifiable cost basis for governance priorities.

[0123] 5.3 Example 3: Version History Analysis and Governance Lag Quantification

[0124] This example demonstrates the performance of S5 (Version History Analysis) on multiple real repositories, focusing on how the time dimension can converge general near-repetitions into residuals with governance significance.

[0125] Mining the version history of the data-infra repository revealed 10 variant coexistence clusters identifiable by their explicit naming suffix (_v3), concentrated in the Littlepay payment module. Typical examples include stg_littlepay_authorisations and stg_littlepay_authorisations_v3, stg_littlepay_device_transactions and stg_littlepay_device_transactions_v3, etc. These coexistence clusters first appeared in March 2025, and all remained active in the latest snapshot as of April 2026, coexisting for 55 weeks. Furthermore, no subsequent commits containing cleanup semantics (such as remove, deprecate, or consolidate) were detected in the version history. According to S5's decision rules, these coexistence clusters are all considered replacements of failed residuals.

[0126] Meanwhile, the four latent near-duplicate pairs (NTD and GTFS modules) discovered using semantic methods in Example 1 presented a result contrary to common intuition after version history analysis: three of them belonged to the creation-based redundancy type, meaning that the two near-duplicate models were introduced into the repository on the same day (February 3, 2025) by the same batch of changes and existed in parallel from the beginning; only one (the GTFS dimension table pair) was closer to the classic replacement failure pattern. This distribution contradicts the common assumption that "long-term redundancy mainly comes from forgetting to delete old versions"—in this repository, the main cause of redundancy is that new variants are introduced in parallel during the creation stage, rather than due to untimely subsequent cleanup. This finding directly affects the choice of governance strategy: if the proportion of creation-based redundancy in the repository is high, then pre-commit prevention (such as CI / PR warnings as described in Example 5) is more valuable than post-commit cleanup.

[0127] To verify the universality of the above governance lag pattern, the same version history analysis was performed on three other public DBT repositories. In terms of file-level governance lag (the time from a model's first appearance to its first being touched by a cleanup semantic commit), the median for data-infra was 97 days, and the 90th percentile was 452 days; for mattermost-data-warehouse, the median was 306 days, and the 90th percentile was 916 days; for spellbook, the median was 238 days, and the 90th percentile was 619 days; the dagster-open-platform model was characterized by rapid creation and rapid deletion, with as many as 86 models deleted within 30 days, and almost no long-term residuals.

[0128] The four repositories exhibit different patterns in terms of governance lag. `data-infra` is primarily characterized by long-term version coexistence residuals, with all 10 variant clusters remaining unresolved. `spellbook` exhibits both strong version coexistence (5 variant clusters, 3 of which lacked detected cleanup semantics) and long governance lags. `mattermost-data-warehouse` shows weak variant coexistence signals (only 1 short-lived variant cluster, cleaned up after 3 weeks of coexistence), but the lag before the old model is officially marked as deprecated is very long. `dagster-open-platform` shows almost no long-term residuals, resembling a pattern of rapid trial and error followed by cleanup.

[0129] The cross-repository comparison above also reveals a counterintuitive phenomenon: the mattermost-data-warehouse, as the repository with the weakest variant coexistence signal (almost no explicit version conflicts), actually has the longest governance lag of the four repositories (median 306 days, three times that of data-infra). This indicates that "no explicit version coexistence" and "timely governance" are two orthogonal dimensions—a repository may not show _v2 / _v3 coexistence at the naming level, but the old model can still survive in the repository for a long time without being cleaned up. This finding further proves the necessity of introducing time-dimensional analysis in S5: naming features or single-point-of-time similarity detection alone cannot distinguish repositories with different governance features; it is necessary to combine coexistence duration in version history, cleanup semantic evidence, and parallel maintenance patterns to make an effective judgment.

[0130] 5.4 Example 4: Verification of the ability to exclude reasonable separation

[0131] This embodiment uses the mattermost-data-warehouse as a control to verify the invention's ability to exclude "seemingly similar but actually reasonable architectural divisions of labor". After performing the hierarchical funnel S2 to S4 on the latest snapshot (489 models) of mattermost-data-warehouse, there are several model pairs among the highly similar candidates of this warehouse. They are indeed "very similar" in name or partial SQL structure, but should not be judged as residuals after review of business concepts and genealogical relationships.

[0132] For example, the names server_plugin_details and server_plugins_details differ by only one plural suffix, but the former mainly corresponds to the configuration details of config_plugin, while the latter mainly corresponds to the summary of plugin status. The business objects of the two are not the same, and they were correctly excluded in the S3 discrimination.

[0133] For example, `int_notifications_logs_eu_hourly` and `int_notifications_logs_us_hourly` have highly isomorphic SQL structures, but they depend on log data sources from Europe and the United States, respectively. This is a parallel model split by region, a common and reasonable architectural division of labor in data warehouses, and it was correctly excluded in the S4 deep alignment based on differences in upstream dependency features.

[0134] For example, int_latest_server_customer_info and dim_latest_server_customer_info, the latter directly references the former through ref(), which belongs to the standard hierarchical derivation relationship from intermediate to mart. They form a direct upstream and downstream dependency in the genealogy diagram, and are also correctly identified as a reasonable division of labor at different levels in S4.

[0135] The above comparison demonstrates that this invention can distinguish between normal regional sharding, environmental sharding, hierarchical derivation, and adjacent business objects, avoiding misjudging reasonable architectural divisions of labor as governance objects. More importantly, the mattermost-data-warehouse does not exhibit the complete mode of simultaneously satisfying "different names, long-term coexistence, zero-cleanup commits, continuous parallel maintenance, and cross-layer propagation" as the data-infra NTD module. The comparison between the positive comparison repository (data-infra) and the negative comparison repository (mattermost) shows that this method can detect residuals in repositories with residuals, while avoiding a large number of false positives in repositories with different governance statuses, demonstrating practical distinguishing capabilities.

[0136] 5.5 Example 5: Early Warning During the Submission Stage for CI / PR

[0137] In another deployment mode, this approach performs incremental checks on newly added or modified models when developers submit PRs, rather than performing periodic health checks on the entire repository.

[0138] Specifically, when a PR includes a new model, the system first performs natural language phraseification preprocessing on the model's name and calculates the embedding. Then, it searches only the existing model library for highly similar candidates. Since the incremental detection candidate size is O(n) instead of O(n²) (only comparing the new model with each of the existing n models is required), the computational cost of S2 is significantly reduced. After performing S3 and S4 on the recalled candidates, if the new model forms a near-duplicate candidate with an existing model, the system automatically outputs a warning in the PR review or CI check, prompting the developer to confirm whether the model is an intentionally introduced variant or whether it should replace rather than coexist with the old model.

[0139] This deployment pattern is particularly suitable for preventing redundant residuals at creation. Cross-warehouse data from Example 3 has shown that long-term redundancy in some warehouses primarily stems from new variants being introduced in parallel during the creation phase. Intercepting the introduction of such redundancy at the PR stage can prevent subsequent long-term coexistence, downstream propagation, and high governance costs from the outset.

[0140] The above embodiments can be implemented, in whole or in part, by software, hardware (such as circuits), firmware, or any other combination thereof. When implemented using software, the above embodiments can be implemented, in whole or in part, as a computer program product. The computer program product includes one or more computer instructions or computer programs. When the computer instructions or computer programs are loaded or executed on a computer, all or part of the processes or functions described in the embodiments of the present invention are generated. The computer can be a general-purpose computer, a special-purpose computer, a computer network, or other programmable device. The computer instructions can be stored in a computer-readable storage medium or transferred from one computer-readable storage medium to another. The computer-readable storage medium can be any available medium accessible to a computer or a data storage device such as a server or data center containing one or more sets of available media. The available medium can be a magnetic medium (e.g., floppy disk, hard disk, magnetic tape), an optical medium (e.g., DVD), or a semiconductor medium. A semiconductor medium can be a solid-state drive.

[0141] It should be understood that the term "and / or" in this article is merely a description of the relationship between related objects, indicating that three relationships can exist. For example, A and / or B can represent: A existing alone, A and B existing simultaneously, or B existing alone. A and B can be singular or plural. Additionally, the character " / " in this article generally indicates an "or" relationship between the preceding and following related objects, but it can also represent an "and / or" relationship. Please refer to the context for a more accurate understanding.

[0142] In this invention, "at least one" means one or more, and "more than one" means two or more. "At least one of the following" or similar expressions refer to any combination of these items, including any combination of a single item or a plurality of items. For example, at least one of a, b, or c can represent: a, b, c, ab, ac, bc, or abc, where a, b, and c can be a single item or multiple items.

[0143] It should be understood that, in various embodiments of the present invention, the order of the above-mentioned process numbers does not imply the order of execution. The execution order of each process should be determined by its function and internal logic, and should not constitute any limitation on the implementation process of the embodiments of the present invention.

[0144] Those skilled in the art will recognize that the units and algorithm steps of the various examples described in conjunction with the embodiments disclosed herein can be implemented in electronic hardware, or a combination of computer software and electronic hardware. Whether these functions are implemented in hardware or software depends on the specific application and design constraints of the technical solution. Those skilled in the art can use different methods to implement the described functions for each specific application, but such implementations should not be considered beyond the scope of this invention.

[0145] Those skilled in the art will clearly understand that, for the sake of convenience and brevity, the specific working processes of the devices, apparatuses, and units described above can be referred to the corresponding processes in the foregoing method embodiments, and will not be repeated here.

[0146] In the several embodiments provided by this invention, it should be understood that the disclosed devices, apparatuses, and methods can be implemented in other ways. For example, the apparatus embodiments described above are merely illustrative; for instance, the division of units is only a logical functional division, and in actual implementation, there may be other division methods. For example, multiple units or components may be combined or integrated into another device, or some features may be ignored or not executed. Furthermore, the coupling or direct coupling or communication connection shown or discussed may be through some interfaces; the indirect coupling or communication connection between devices or units may be electrical, mechanical, or other forms.

[0147] The units described as separate components may or may not be physically separate. The components shown as units may or may not be physical units; that is, they may be located in one place or distributed across multiple network units. Some or all of the units can be selected to achieve the purpose of this embodiment according to actual needs.

[0148] In addition, the functional units in the various embodiments of the present invention can be integrated into one processing unit, or each unit can exist physically separately, or two or more units can be integrated into one unit.

[0149] If the aforementioned functions are implemented as software functional units and sold or used as independent products, they can be stored in a computer-readable storage medium. Based on this understanding, the technical solution of this invention, or the part that contributes to the prior art, or a part of the technical solution, can be embodied in the form of a software product. This computer software product is stored in a storage medium and includes several instructions to cause a computer device (which may be a personal computer, server, or network device, etc.) to execute all or part of the steps of the methods described in the various embodiments of this invention. The aforementioned storage medium includes various media capable of storing program code, such as USB flash drives, portable hard drives, read-only memory (ROM), random access memory (RAM), magnetic disks, or optical disks.

[0150] The above description is merely a specific embodiment of the present invention, but the scope of protection of the present invention is not limited thereto. Any variations or substitutions that can be easily conceived by those skilled in the art within the technical scope disclosed in the present invention should be included within the scope of protection of the present invention. Therefore, the scope of protection of the present invention should be determined by the scope of the claims.

Claims

1. A method for perceiving, identifying, and managing the propagation of near-duplication models in templated SQL data warehouses, characterized in that, Includes the following steps: Obtain the model nodes and dependencies between model nodes in the target data warehouse project, and construct a model dependency graph; Generate semantic nearest neighbor candidates: Perform natural language phraseification preprocessing on the model name, and recall candidates based on one or more of the following: semantic similarity, name lexical overlap, string similarity, and upstream source overlap; Determine the set of near-duplicate objects to be analyzed: Perform business concept consistency judgment and SQL logic near-duplicate confirmation on the recalled candidate model objects, and retain objects that meet the conditions of business semantic similarity, SQL transformation logic similarity and continuous coexistence in combination with the conditions of continuous coexistence. For each near-repeating object in the set of near-repeating objects to be analyzed, search downstream along the model dependency graph for downstream model nodes that depend on the corresponding model nodes in the near-repeating objects, and identify downstream derived near-repeating objects formed by the propagation of upstream near-repeating objects; Based on the propagation relationship between the downstream derived near-duplicate objects and the corresponding upstream near-duplicate objects, a propagation source identifier is assigned to each near-duplicate object; Nearly duplicated objects with propagation source identifiers are marked as derived redundant objects, and nearly duplicated objects without upstream propagation source identifiers are marked as root residual objects. Output the set of root residual objects and the set of derived redundant objects covered by each root residual object.

2. The method according to claim 1, characterized in that, The conditions for continued coexistence are determined based on at least one or more of the following information: the time difference of the first appearance of the model node, the duration of coexistence, whether there is cleanup of semantic evidence, whether it is currently in an unresolved state, or whether there is a common modification pattern in subsequent submissions; wherein, the common modification pattern refers to multiple model nodes constituting the same near-duplicate object being modified simultaneously by the same submission in multiple subsequent submissions.

3. The method according to claim 2, characterized in that, Based on the time difference of the first appearance of the model nodes, the near-duplicate objects are divided into the creation-is-redundant type and the replacement-failure type. The creation-is-redundant type refers to the multiple model nodes that make up the near-duplicate object being introduced into the repository at similar times or in the same change. The replacement-failure type refers to the new model node being created later than the old model node and the old model node not being removed after the new model node is launched.

4. The method according to claim 1, characterized in that, The process of determining the set of near-duplicate objects to be analyzed includes: performing natural language phrase preprocessing on the model names, and then conducting candidate recall based on one or more of the following: semantic similarity, name lexical overlap, string similarity, and upstream source overlap; performing business concept consistency judgment and SQL logic near-duplicate confirmation on the recalled candidate model objects; and retaining the model objects that simultaneously meet the conditions of business concept consistency, SQL logic near-duplicate, and continuous coexistence as the set of near-duplicate objects to be analyzed.

5. The method according to claim 4, characterized in that, The confirmation of near-duplicate SQL logic is based on at least one or more of the following features: upstream dependency features, output pattern features, transformation logic features, or granularity constraint features; wherein, the upstream dependency features include the degree of overlap of ref function calls or source function calls, and the transformation logic features include similarity of filtering conditions, similarity of join logic, and similarity of aggregation methods.

6. The method according to claim 1, characterized in that, A downstream near-duplicate object is identified as being formed by the propagation of an upstream near-duplicate object, and at least one or more of the following conditions are met: multiple model nodes in the downstream near-duplicate object depend on the corresponding model nodes in the upstream near-duplicate object. The main upstream differences of downstream near-duplicate objects can be explained by upstream near-duplicate objects; after removing the corresponding upstream differences, the near-duplicate degree of downstream near-duplicate objects is reduced. Downstream near-duplicate objects and upstream near-duplicate objects form a correspondence in terms of hierarchy, name family, field source, or logical structure.

7. The method according to claim 1, characterized in that, The method further includes: Based on the coexistence duration, propagation layers, number of derived redundant objects covered by propagation, number of downstream affected nodes, frequency of joint modification, and presence or absence of semantic evidence cleanup for each root residual object, the governance priority of the root residual objects is generated.

8. A device for perceiving, identifying, and managing the propagation of near-duplication models in a templated SQL data warehouse, used to implement the method described in any one of claims 1 to 7, characterized in that, It includes a dependency graph construction unit, a near-duplicate object identification unit, a propagation identification unit, a propagation source allocation unit, a root source folding unit, and a result output unit, wherein: The dependency graph construction unit is used to obtain the model nodes in the target data warehouse project and the dependency relationships between the model nodes, and to construct the model dependency graph. The near-duplicate object determination unit is used to determine the set of near-duplicate objects to be analyzed; The propagation identification unit is used to identify downstream derived near-repeating objects formed by the propagation of upstream near-repeating objects along the model dependency graph; The propagation source allocation unit is used to assign a propagation source identifier to each near-duplicate object; The root source folding unit is used to mark near-duplicate objects with propagation source identifiers as derived redundant objects and to mark near-duplicate objects without upstream propagation source identifiers as root source residual objects. The result output unit is used to output the root residual object set and the derived redundant object set covered by each root residual object.

9. An electronic device comprising a processor and a memory, wherein the memory stores a computer program, characterized in that, When the computer program is run on the processor, it causes the electronic device to perform the method according to any one of claims 1 to 7.

10. A computer-readable storage medium having a computer program stored thereon, characterized in that, When the computer program is executed by a processor, it implements the method described in any one of claims 1 to 7.

Images