instruments like dbt make setting up SQL knowledge pipelines simple and systematic. However even with the added construction and clearly outlined knowledge fashions, pipelines can nonetheless turn into complicated, which makes debugging points and validating adjustments to knowledge fashions troublesome.
The growing complexity of information transformation logic provides rise to the next points:
- Conventional code evaluation processes solely have a look at code adjustments and exclude the information affect of these adjustments.
- Information affect ensuing from code adjustments is difficult to hint. In sprawling DAGs with nested dependencies, discovering how and the place knowledge affect happens is extraordinarily time-consuming, or close to not possible.
Gitlab’s dbt DAG (proven within the featured picture above) is the right instance of a knowledge challenge that’s already a house-of-cards. Think about making an attempt to observe a easy SQL logic change to a column by this complete lineage DAG. Reviewing a knowledge mannequin replace can be a frightening process.
How would you strategy the sort of evaluation?
What’s knowledge validation?
Information validation refers back to the course of used to find out that the information is right by way of real-world necessities. This implies making certain that the SQL logic in a knowledge mannequin behaves as supposed by verifying that the information is right. Validation is normally carried out after modifying a knowledge mannequin, equivalent to accommodating new necessities, or as a part of a refactor.
A singular evaluation problem
Information has states and is instantly affected by the transformation used to generate it. That is why reviewing knowledge mannequin adjustments is a singular problem, as a result of each the code and the information must be reviewed.
As a consequence of this, knowledge mannequin updates ought to be reviewed not just for completeness, but in addition context. In different phrases, that the information is right and present knowledge and metrics weren’t unintentionally altered.
Two extremes of information validation
In most knowledge groups, the particular person making the change depends on institutional data, instinct, or previous expertise to evaluate the affect and validate the change.
“I’ve made a change to X, I feel I do know what the affect ought to be. I’ll verify it by working Y”
The validation methodology normally falls into one in all two extremes, neither of which is good:
- Spot-checking with queries and a few high-level checks like row depend and schema. It’s quick however dangers lacking precise affect. Important and silent errors can go unnoticed.
- Exhaustive checking of each single downstream mannequin. It’s gradual and useful resource intensive, and might be expensive because the pipeline grows.
This leads to a knowledge evaluation course of that’s unstructured, arduous to repeat, and sometimes introduces silent errors. A brand new methodology is required that helps the engineer to carry out exact and focused knowledge validation.
A greater strategy by understanding knowledge mannequin dependencies
To validate a change to a knowledge challenge, it’s necessary to grasp the connection between fashions and the way knowledge flows by the challenge. These dependencies between fashions inform us how knowledge is handed and remodeled from one mannequin to a different.
Analyze the connection between fashions
As we’ve seen, knowledge challenge DAGs might be enormous, however a knowledge mannequin change solely impacts a subset of fashions. By isolating this subset after which analyzing the connection between the fashions, you possibly can peel again the layers of complexity and focus simply on the fashions that truly want validating, given a selected SQL logic change.
The varieties of dependencies in a knowledge challenge are:
Mannequin-to mannequin
A structural dependency by which columns are chosen from an upstream mannequin.
--- downstream_model
choose
a,
b
from {{ ref("upstream_model") }}Column-to-column
A projection dependency that selects, renames, or transforms an upstream column.
--- downstream_model
choose
a,
b as b2
from {{ ref("upstream_model") }}Mannequin-to-column
A filter dependency by which a downstream mannequin makes use of an upstream mannequin in a the place, be a part of, or different conditional clause.
-- downstream_model
choose
a
from {{ ref("upstream_model") }}
the place b > 0Understanding the dependencies between fashions helps us to outline the affect radius of a knowledge mannequin logic change.
Determine the affect radius
When making adjustments to a knowledge mannequin’s SQL, it’s necessary to grasp which different fashions is perhaps affected (the fashions you could verify). On the excessive stage, that is finished by model-to-model relationships. This subset of DAG nodes is called the affect radius.
Within the DAG under, the affect radius contains nodes B (the modified mannequin) and D (the downstream mannequin). In dbt, these fashions might be recognized utilizing the modified+ selector.
Figuring out modified nodes and downstream is a good begin, and by isolating adjustments like this you’ll scale back the potential knowledge validation space. Nonetheless, this might nonetheless end in numerous downstream fashions.
Classifying the sorts of SQL adjustments can additional enable you to to prioritize which fashions truly require validation by understanding the severity of the change, eliminating branches with adjustments which might be recognized to be protected.
Classify the SQL change
Not all SQL adjustments carry the identical stage of threat to downstream knowledge, and so ought to be categorized accordingly. By classifying SQL adjustments this manner, you possibly can add a scientific strategy to your knowledge evaluation course of.
A SQL change to a knowledge mannequin might be labeled as one of many following:
Non-breaking change
Modifications that don’t affect the information in downstream fashions equivalent to including new columns, changes to SQL formatting, or including feedback and so forth.
-- Non-breaking change: New column added
choose
id,
class,
created_at,
-- new column
now() as ingestion_time
from {{ ref('a') }}Partial-breaking change
Modifications that solely affect downstream fashions that reference sure columns equivalent to eradicating or renaming a column; or modifying a column definition.
-- Partial breaking change: `class` column renamed
choose
id,
created_at,
class as event_category
from {{ ref('a') }}Breaking change
Modifications that affect all downstream fashions equivalent to filtering, sorting, or in any other case altering the construction or which means of the remodeled knowledge.
-- Breaking change: Filtered to exclude knowledge
choose
id,
class,
created_at
from {{ ref('a') }}
the place class != 'inner'Apply classification to cut back scope
After making use of these classifications the affect radius, and the variety of fashions that must be validated, might be considerably diminished.
Within the above DAG, nodes B, C and F have been modified, leading to doubtlessly 7 nodes that must be validated (C to E). Nonetheless, not every department comprises SQL adjustments that truly require validation. Let’s check out every department:
Node C: Non-breaking change
C is classed as a non-breaking change. Due to this fact each C and H don’t must be checked, they are often eradicated.
Node B: Partial-breaking change
B is classed as a partial-breaking change as a result of change to the column B.C1. Due to this fact, D and E must be checked solely in the event that they reference column B.C1.
Node F: Breaking change
The modification to mannequin F is classed as a breaking-change. Due to this fact, all downstream nodes (G and E) must be checked for affect. For example, mannequin g would possibly combination knowledge from the modified upstream column
The preliminary 7 nodes have already been diminished to five that must be checked for knowledge affect (B, D, E, F, G). Now, by inspecting the SQL adjustments on the column stage, we are able to scale back that quantity even additional.
Narrowing the scope additional with column-level lineage
Breaking and non-breaking adjustments are simple to categorise however, in the case of inspecting partial-breaking adjustments, the fashions must be analyzed on the column stage.
Let’s take a better have a look at the partial-breaking change in mannequin B, by which the logic of column c1 has been modified. This modification might doubtlessly end in 4 impacted downstream nodes: D, E, Okay, and J. After monitoring column utilization downstream, this subset might be additional diminished.
Following column B.c1 downstream we are able to see that:
- B.c1 → D.c1 is a column-to-column (projection) dependency.
- D.c1 → E is a model-to-column dependency.
- D → Okay is a model-to-model dependency. Nonetheless, as D.c1 shouldn’t be utilized in Okay, this mannequin might be eradicated.
Due to this fact, the fashions that must be validated on this department are B, D, and E. Along with the breaking change F and downstream G, the whole fashions to be validated on this diagram are F, G, B, D, and E, or simply 5 out of a complete of 9 doubtlessly impacted fashions.
Conclusion
Information validation after a mannequin change is troublesome, particularly in giant and complicated DAGs. It’s simple to overlook silent errors and performing validation turns into a frightening process, with knowledge fashions usually feeling like black packing containers in the case of downstream affect.
A structured and repeatable course of
By utilizing this change-aware knowledge validation method, you possibly can carry construction and precision to the evaluation course of, making it systematic and repeatable. This reduces the variety of fashions that must be checked, simplifies the evaluation course of, and lowers prices by solely validating fashions that truly require it.
Earlier than you go…
Dave is a senior technical advocate at Recce, the place we’re constructing a toolkit to allow superior knowledge validation workflows. He’s all the time joyful to speak about SQL, knowledge engineering, or serving to groups navigate their knowledge validation challenges. Join with Dave on LinkedIn.
Analysis for this text was made doable by my colleague Chen En Lu (Popcorny).
