Data Quality & Transformation
Your transform logic lives in SQL scripts and Python jobs that only you can debug, and every new source format means another file to maintain.
In DataFuseAI you build transformations on a visual pipeline canvas: drop a transform node — Filter, Join, Aggregate, Window, and eight more — onto the flow and configure it in a modal. Profiling nodes return a per-column report showing distinct, non-distinct and null counts, value ranges, length, cardinality and the high-frequency value.
order_key IS NOT NULL passChallenges We Solve
Four problems show up again and again in transformation work: transform logic that only exists as a script, source formats that never line up, transforms run against columns nobody has looked at, and no record of what each step did to the data. DataFuseAI answers each one on the pipeline canvas, in the profiling report, or in the SQL query editor.
Transform logic that only exists as a script
Every rule lives in a SQL file or a Python job. Reading it means reading a hundred lines; changing it means changing that one file, then re-testing everything downstream. The person who wrote it is the person who has to maintain it.
Formats that never line up
One source sends dates as text, the next as timestamps. One identifies a supplier by code, another by name, a third by a name someone typed by hand. Reconciling that in SQL gets harder with every source you add.
Running a transform before you look at the column
You write the aggregation, run it, and only then find the column holds one distinct value or a run of nulls. The rerun costs an afternoon — and that is the short path to wrong numbers reaching a dashboard.
Nothing to show when someone asks what happened
Two weeks later a number gets questioned, and the answer is buried in a script nobody has open. What you need is the step, the run it belongs to, and the column statistics from that run — for review and debugging, not from memory.
Every transform is a node you drop on the canvas
The Transformations palette sits on the left of the pipeline canvas and holds 12 transform types: Aggregate, Dedupe, Derived, Explode, Filter, Join, Pivot, Route, Split, Union, Unpivot and Window. You drag one onto the canvas, wire it between the sources you connected and the sink, and configure it in a modal. No script file is created and no repository is involved.
The palette and the canvas
Sources sit above the palette and Sink below it, so a pipeline reads top to bottom in the order it runs. The screenshot shows a trade-intelligence pipeline that fans PostgreSQL and MySQL sources through Join, Filter, Derived, Route, Aggregate and Window nodes into PostgreSQL sinks, with Profiling nodes hanging off the analytics tables at the end of each branch.
- Aggregate
- roll rows up into totals, counts and averages by group.
- Dedupe
- find and remove duplicate rows.
- Derived
- build a calculated column from an expression.
- Explode
- flatten nested data such as a JSON array.
- Filter
- keep the rows that meet a condition, exact or approximate.
- Join
- merge tables from separate sources on a matching key.
- Pivot
- reshape rows into columns.
- Route
- send rows down separate branches by rule.
- Split
- parse a column of delimited values.
- Union
- append rows from several datasets into one.
- Unpivot
- reshape columns back into rows.
- Window
- running totals and ranks over a partition.
Aggregate, Derived, Filter, Join, Route and Window open their modal further down this page.
Filter: standard conditions, or a fuzzy match
The Filter modal opens on two tabs, Standard Filter and Fuzzy Filter. On the standard tab you click field chips and operator buttons — ==, !=, >=, LIKE, NOT LIKE, IS NULL, IS NOT NULL, AND, OR — and the expression box fills in as you go; the one in the screenshot reads order_status == 'F' OR order_status == 'O' OR order_status == 'P'. Approximate matching, where records are similar rather than identical, gets its own tab — the mechanics of fuzzy matching are worth reading before you use it.
Join: map source to source, pick the output columns
Join is configured by drawing a line between two source structures. In the screenshot, line_orderkey on the lineitem side connects to order_key on orders, the join type dropdown reads Inner Join, and the Output Column panel on the right lists every column carried through with its alias and data type. Checkboxes decide what continues down the pipeline. There is a Fuzzy Match tab here too, for the case when systems disagree about the same record.
Configure the transform in a modal, not in a script
Four of the heavier transform types — Aggregate, Derived, Window and Route — open into full configuration screens with searchable column lists, per-column dropdowns and an expression box. Nothing is hidden behind a wizard: you see every incoming column, every function you picked and the expression you built before you save.
Aggregate: roll up to totals and averages by group
The Aggregate modal lists every incoming column with an editable Alias, its Default Data Type, and an Aggregation Type dropdown. In the screenshot line_orderkey is aliased total_line_items with count, line_quantity becomes total_quantity with sum, and order_key becomes total_orders with count distinct. The open dropdown shows min, mean, skewness, skewness sample, standard deviation, standard deviation sample, sum, sum distinct, variance and variance sample — and it scrolls past the top of the modal, so the list runs longer than the screenshot shows.
Derived: add a calculated column without leaving the canvas
The Derived modal takes a Column Name and a DataType, then builds the expression from three sources: field chips for every available column, operator buttons, and collapsible function groups — String Functions, Numeric Functions, Date And Time Functions and Case Expressions. The example creates discount_amount as a decimal from round(line_extendedprice * line_discount, 2).
Window: running totals and ranks over a partition
The Window modal carries a Window Columns table where each row defines one new column: a Column Name, a Window Type, an Order By column with a direction, a Partition column, and the resulting expression. Both example rows order by net_revenue descending and partition by region_name — one row set to a sum, the other to a dense rank.
Route: send rows down different branches by rule
Route builds a condition the same way — fields, operators, expression box — and then Save Route adds it to the Saved Route list, where each rule has its own edit and delete control. In this example the list holds five rules, including order_priority == 'High', order_priority == 'Medium', order_priority == 'Low' and customer_acctbal >= 200000.
Configuration is all these modals do. Running the finished flow on a schedule is a different job, handled by pipeline automation and covered on its own page.
How DataFuseAI Helps
The transform half and the quality half of this work happen in one tool, on one pipeline. You build the flow visually, check the columns before you run it, keep the per-run column statistics, and drop into SQL for the checks that are quicker to write than to assemble.
Build the transform on the canvas
Your transformation logic becomes a chain of named nodes anyone on your team can read. Twelve transform types cover joins, aggregations, dedupe, pivots, splits, routing and window functions, each with its own configuration modal.
Check the data before you transform it
A profiling node on the pipeline returns the column report, so the distinct, non-distinct and null split for a column exists before a transform depends on it — rather than surfacing later as a wrong figure downstream.
Keep what each run produced
Every profiling run is stamped with its own Session ID and Data Run time, and the report exports to CSV. When a number gets questioned two weeks later, you have that run's column statistics to open instead of a recollection.
Write the check yourself when that is the right tool
The Query Editor sits in the same workspace as the pipeline builder and connects through the same connection profiles, so a duplicate-detection query is a tab away from the transform that built the table.
See what is actually in the column before you transform it
A profiling node returns a Data Profiling report: one row per column, carrying its data type, a distinct / non-distinct / null count split, a min-mean-max value range, a min-avg-max length range, cardinality, sum and the high-frequency value. The header strip names the Pipeline ID, Session ID, record count, Data Run time and column count for that run. Any column can be opened as a histogram or tracked across runs as a line graph.
Read the column report
The report opens over the pipeline. Column Name and Data Type come first, then a Chart column with an icon for each visual available on that column, then the statistics: Count( Distinct → Non Distinct → Null ), Value( Min → Mean → Max ), Length( Min → Avg → Max ), Cardinality, Sum and High Frequency Value — with further columns sitting to the right of the visible area. Hovering a count bar breaks it out into its distinct, non-distinct and null shares with a percentage on each.
See the distribution
The chart icon on a row opens that column's histogram in three panes: the bar chart itself, a Distribution Table listing each value with its count, and a Column Result panel carrying Total Records, Min. Value, Max. Value, Sum, Mean, Standard Deviation, No. of Columns and Data Run. On a categorical column such as supplier_health_label, that is where a skew becomes visible — one label carrying most of the rows while the rest sit far behind it.
Track it across runs
The line-graph view plots one statistic for one column across pipeline executions. A dropdown selects which statistic — the screenshot is on max for avg_acct_balance — and a Tabular View sits beside the chart listing the value and the execution time behind every point, with range filters above it. A shift in a column's shape becomes something you can see rather than something you infer after the fact.
Get it out of the tool
The report header carries its own export and share controls. The envelope icon opens Share Profiling Link, where recipients are added one at a time as removable chips and sent the report link directly — no separate export step required to get a column's findings in front of a teammate.
When you would rather write the check yourself
The Query Editor is part of the same workspace as the pipeline builder. You pick a connection profile, database and schema, browse the tables and their column types on the left, write SQL on the right, and run it against the same tables your pipeline writes — with named tabs and a Saved Queries list.
The tab in the screenshot is named Record Uniqueness Validation and holds a 13-line query: it selects region_name, market_segment, order_year and order_month from analytics_monthly_revenue_trend, counts the rows in each group as duplicate_count, and keeps only the groups where HAVING COUNT(*) > 1. That is a duplicate check written the way you would write it anywhere else — Run to execute it, Save to keep it, and a Saved Queries tab to come back to it later.
The no-code path is not the right instrument for everything. Some checks are quicker to type than to assemble from chips, and some behaviour is more direct to control in code — which is why both live here rather than one instead of the other. If you want the checks themselves written out, we have covered five data quality validation checks worth running on every pipeline.
Frequently Asked Questions About Data Quality and Transformation
Fuzzy matching compares records that are similar rather than identical, so "Acme Corp." and "ACME Corporation" can be treated as the same supplier. It is what you reach for when two systems share no key and exact string comparison misses duplicates a person would spot immediately. In DataFuseAI, the Filter transform carries a Fuzzy Filter tab beside its standard condition builder, and the Join transform carries a Fuzzy Match tab. The algorithms behind it are walked through in our post on fuzzy matching in ETL pipelines.
Use a deduplication step in a visual pipeline builder instead of a script. In DataFuseAI, Dedupe is one of the 12 transform types in the Transformations palette: you drop it onto the canvas between the source and the next step, and it runs as part of the pipeline. If you would rather see the duplicates before removing them, the Query Editor in the same workspace will run a GROUP BY … HAVING COUNT(*) > 1 query against the table.
Window functions compute a value across a set of rows related to the current row, without collapsing those rows into one the way an aggregation does. Running totals, moving averages, ranks and row numbers within a group are the usual uses. In DataFuseAI's Window transform, each new column is defined by a window type, an order-by column with a direction, and a partition column — for example a sum of net revenue ordered by net revenue and partitioned by region.
Pivot turns row values into columns — a month column becomes one column per month, which is the shape a summary table usually wants. Unpivot does the reverse, folding a set of columns back into rows of name-and-value pairs, which is the shape most charting and BI tools prefer. They are two separate transform types in DataFuseAI's Transformations palette, so a reshape in either direction is a node you place on the canvas rather than a query you rewrite.
Data profiling is examining a dataset to describe what is actually in it — the data type of each column, how many values are distinct, how many are null, the range of values and lengths it holds, and which value appears most often. It is the check you run before writing transformation logic, so the logic is built against the real data rather than an assumption about it. In DataFuseAI it runs as a profiling node inside the pipeline and returns a per-column report you can read, chart and export.
At minimum, one row per column with its data type and the counts that describe completeness and uniqueness. DataFuseAI's Data Profiling report gives each column a distinct / non-distinct / null count split, a min-mean-max value range, a min-avg-max length range, cardinality, sum and the high-frequency value, and stamps the run with a Pipeline ID, Session ID, record count and timestamp. Any column in it can also be opened as a histogram with its own distribution table.
Read the per-column report first: data types, distinct and null counts, value and length ranges, cardinality. Then look at the distribution of the columns that matter, usually as a histogram, to see how values are spread rather than only how they summarise. Then compare the same statistic across runs to see whether the shape is drifting. Finally get the result out — export it or share the link — so the people who asked about the data can read it for themselves.
Profiling describes the data; cleansing changes it. A profiling pass tells you that a column is mostly null, or that it holds several spellings of the same region name. A cleansing step is what then removes the duplicates, handles the nulls and standardises the spellings. In DataFuseAI they are separate objects in one pipeline: profiling nodes produce the report, and transform types such as Dedupe, Filter and Derived do the changing.
Data validation is checking that data meets a defined rule before it is trusted — that a key is unique, a required field is populated, or a value sits inside an allowed set or range. It returns pass or fail, which is what separates it from profiling's descriptive statistics. In DataFuseAI you can express a validation as a filter condition on the canvas, or as SQL in the Query Editor — a GROUP BY with HAVING COUNT(*) > 1 catches duplicate keys. Five worth automating are set out in our post on data quality validation checks.
Data quality is the goal; profiling and validation are two ways of working toward it. Profiling describes what is in the data — types, distinct and null counts, ranges, distributions — without judging it. Validation applies a rule and returns pass or fail, such as "this key must be unique." In practice you profile first to learn what the rules should be, then validate against those rules on every run.
Data quality is how fit a dataset is for the decision or process that depends on it — accurate, complete, consistent, current, unique and valid enough for that particular use. It matters because everything downstream inherits it: a transformation cannot repair a column it was never told was broken, and the fault surfaces as a plausible-looking wrong figure rather than as an error message. That is the argument for checking quality inside the pipeline rather than at the end of it.
The six dimensions most commonly taught are accuracy, completeness, consistency, timeliness, uniqueness and validity. Accuracy and validity are about whether values are correct and well-formed; completeness and uniqueness are about missing and duplicated records; consistency and timeliness are about agreement across sources and how current the data is. Column-level statistics map onto several of them directly — null counts to completeness, distinct counts and cardinality to uniqueness, value and length ranges to validity.
Key Benefits
Four things change for your team once transformation and profiling happen on one canvas. Logic becomes legible without opening a repository, column facts arrive before the transform runs, the profiling result leaves the tool as a file or a link, and SQL stays available for the checks that are better written than assembled.
Transform logic your whole team can read
A pipeline someone else built is legible on the canvas: the node names, the order they run in, and each modal's saved configuration. Bringing a new analyst onto it does not start with a repository walkthrough.
Column facts before the run, not after
Profiling runs as part of the pipeline, so a column's distinct, null and range facts exist before a transform depends on them — and the same statistics are there for the next run to be compared against.
Results that leave the tool
The profiling report exports as CSV and shares as a link, so whoever asked about a column gets the report itself rather than a screenshot pasted into a thread.
A code path that is not a fallback
The Query Editor runs against the same connection profiles as your pipelines, so a hand-written check and a no-code transform sit in one workspace — and the tables that come out the far side carry on as analytics-ready data for your BI layer.