Data Transformation That Reconciles Revenue

A dashboard can be technically correct and still answer the wrong question. One sales report counts an order when a customer clicks “buy.” Another waits for payment. A finance table subtracts refunds on the day they are issued, while a product table revises the original order. All three can begin with the same records, run without errors, and produce different revenue totals.

That gap between captured records and usable answers is the work of data transformation. It is not merely moving columns or converting files. It is where a team decides what one row represents, which records belong together, how conflicting values are handled, and which business event a metric is meant to describe. Those decisions determine whether downstream reports agree and whether a reader can understand what the resulting number actually means.

My default is to treat transformation as a maintained product rather than a cleanup task attached to a dashboard. Keep source records recognizable, make each important business rule explicit, build reusable models at declared grains, and test the assumptions on which decisions depend. That approach takes more initial design than a one-off query. Its return is that the next analyst does not have to reconstruct the same meaning from raw fields again.

Data transformation turns recorded events into defined facts#

Data transformation changes the format, structure, or values of data so that the result can serve a downstream use. Typical work includes cleaning, standardizing, joining, aggregating, and applying business rules. A transformation may convert a text timestamp into a timestamp type, map several source labels to one status, join an order to its customer, or summarize order lines into daily sales. These are different operations, but each changes how a downstream consumer can use the records. dbt Labs describes transformation as converting data into a format or structure useful for decision-making.

The downstream use must come first. “Clean the customer data” is too vague to design against. “Produce one current row per billable account for a weekly retention report” gives the work a target. It names the object, the grain, the relevant state, and the use. A separate model may be needed for a support analysis that requires every historical change rather than only the current account state.

This distinction matters because raw data is not an inferior version of a finished table. It is a record produced for the source system’s purpose. A payment processor may record attempts, authorizations, captures, failures, reversals, and refunds because each is operationally distinct. A revenue model has to decide which of those events count, when they count, and how later events change earlier conclusions. Dropping “unneeded” rows before making that decision can erase information that the model needs.

Transformation therefore changes both shape and meaning. Shape includes column types, names, tables, and levels of detail. Meaning includes definitions such as “active customer,” “completed order,” or “net revenue.” A cast from text to date is mainly structural. Choosing the date that determines a sale is semantic. The second choice deserves more explanation because two perfectly valid dates can support different questions.

The safest starting point is a short contract for the intended model:

• One sentence stating what a row represents.
• The key, or combination of keys, expected to identify that row.
• The event time or reporting period attached to it.
• The included and excluded records.
• The business rules used to derive important fields.
• The expected update schedule and known delay.

This contract is small enough to read before editing a model. It also exposes ambiguity early. If a team cannot finish the sentence “one row represents…,” adding transformation code will conceal the disagreement rather than resolve it.

The grain is the decision that controls the rest of the model#

Grain means what one row represents. It is the most consequential design choice in a transformation because keys, joins, calculations, and tests all depend on it. An order-line table has one row per line, an order table has one row per order, and a daily account table has one row per account per day. None is universally better. Each answers a different class of questions.

Consider an illustrative order with three line items and two payment attempts. Joining the line table directly to the payment-attempt table on order ID produces six rows. Summing line revenue after that join doubles the apparent order value. The SQL engine has done exactly what it was asked to do; the model failed because two many-sided datasets were combined before either was reduced to a compatible grain.

The remedy is not a final distinct. That may remove legitimate repeated values and leave the underlying relationship unexplained. Instead, decide the target grain and prepare each input for it. For a one-row-per-order model, aggregate lines to order ID and select or summarize payment events to order ID before joining. For a payment-attempt analysis, preserve the attempt grain and attach an order-level attribute without pretending each attempt is an order.

Keys follow the same logic. A unique source identifier is useful only if it is unique at the intended grain and stable for the required period. An account ID may identify an account but not an account-month. A product code may be reused across source systems. A composed key may therefore include account and month, or source system and product code. The point is not to manufacture a complicated identifier; it is to make the row definition checkable.

Aggregations should also preserve the question. Summing line amounts can produce an order total. Averaging daily averages does not necessarily produce a correct monthly average when days contain different numbers of observations. Counting rows does not necessarily count customers when customers can generate several events. Before using an aggregate, name its denominator and determine whether the input grain supports it.

This is why I would review grain before syntax in any transformation design. Most syntax errors stop a job. A grain error can complete on time, populate every field, and quietly misstate the business.

A dependable flow separates source repair from business meaning#

A useful transformation flow usually moves from source-shaped records toward consumer-shaped models in deliberate stages. The exact number of stages is less important than the separation of responsibilities.

The first stage should remain close to each source. Rename unclear fields, assign usable types, normalize obvious representations, and retain source identifiers. If a source emits Y, yes, and TRUE for the same boolean state, this is a sensible place to standardize them. If malformed timestamps cannot be converted, preserve enough of the original value to investigate rather than silently replacing every failure with a plausible date.

The next stage can reconcile entities and events across sources. Customer IDs from a billing platform and a support platform may not match, so the team needs a declared mapping rather than a convenient join on customer name. Orders may need line aggregation, payment events may need classification, and duplicate deliveries may need handling. Intermediate models earn their keep when they make one difficult operation understandable and reusable; they become clutter when they merely rename another model without clarifying anything.

The final stage should present stable objects for a particular type of consumption: orders, customers, subscriptions, inventory positions, or another recognizable business concept. These models can contain shared calculations and descriptive attributes so that dashboards do not each recreate the same rules. A consumer model should not force every reader to understand the source application’s internal event vocabulary.

Suppose, as an illustration, a team needs daily net captured sales. The source contains order lines, payment events, and refunds. A defensible flow could standardize timestamps and currencies within each source-shaped model; aggregate lines to one row per order; classify payment events and choose captured amounts; aggregate refunds by order and reporting day; then build a daily result with explicit treatment of refund timing. The last choice is not technical housekeeping. Reporting refunds on the refund date answers a cash-activity question, while revising the original sale date answers a cohort or order-value question. The team may legitimately need both models, with names that distinguish them.

Cleaning belongs in this flow, but “clean” should not mean “looks convenient.” Removing a record because it is an outlier can remove a real large order. Filling a missing country from an account’s current address can misstate the country at purchase time. Standardization is valuable when the equivalence is known; imputation and exclusion require a rule tied to the use.

The practical design principle is progressive commitment. Early models should correct representations without discarding potentially meaningful distinctions. Later models can make stronger business choices because their purpose is narrower and documented. That leaves room to reuse the same source for a different question without undoing hidden assumptions.

ETL and ELT place transformation at different points#

Transformation can happen before or after data reaches its main destination. In extract-transform-load, or ETL, records are transformed before they are loaded into the destination. In extract-load-transform, or ELT, source records are loaded first and transformed inside the destination environment. The documented ELT sequence loads data into a centralized warehouse before performing its transformations there.

The order is an architecture choice, not a maturity ranking. I would favor ELT for analytical work when the destination can perform the required computation, retaining source-shaped data is acceptable, and the team expects definitions to evolve. Loading first lets different downstream models reuse the landed records and allows a changed rule to be applied again without necessarily extracting the source again.

I would transform before loading when raw sensitive fields must not enter the destination, when data must be reduced before transfer, or when the destination cannot safely or economically perform the work. A hybrid is often the honest answer: filter or protect restricted fields before loading, then do reusable analytical modeling in the destination.

Neither pattern removes the need to define grain, ownership, retention, or tests. ELT can preserve flexibility, but it can also create a warehouse full of unexplained tables if every analyst builds an isolated chain. ETL can enforce an early contract, but that contract can become a bottleneck if every new question requires an upstream redesign. Choose based on constraints and change patterns, not on which acronym sounds current.

Latency introduces another decision. A daily dashboard does not need every model to update after every source event. A time-sensitive operational process may. More frequent execution costs more computation and creates more opportunities to expose partially arrived data. Define how fresh the answer must be, how late source events can arrive, and whether a provisional result is acceptable. “Real time” is not a transformation rule; it is a service expectation that needs a measurable time boundary.

Business rules should live once and remain inspectable#

Transformation becomes fragile when a business definition is copied into many reports. If five dashboards each define an active subscriber, one cancellation rule change creates five chances for disagreement. A shared transformation model can place the definition in one maintained location and expose the result to each consumer.

Centralization does not mean forcing unlike questions into one metric. Marketing may need accounts that can receive a campaign, finance may need accounts with recognized revenue, and product may need people who used a feature in the last reporting window. Calling all three “active customers” creates false consistency. Give materially different concepts different names, even when that makes the catalog less tidy.

A well-formed rule states its inputs, time basis, precedence, and exceptional treatment. For example, a subscription status may depend on activation time, cancellation time, trial state, and reporting date. If source states conflict, the transformation needs a precedence rule. If events arrive late, the result may change on a later run. Those conditions belong with the definition because they affect how a reader should use the field.

Modularity helps here. A model that standardizes payment events can feed both an order model and a cash-activity model. A model that embeds order logic, marketing attribution, support classifications, and presentation formatting in one query is harder to reuse and harder to change safely. Split at stable conceptual boundaries, not simply whenever a query becomes long.

Documentation should explain the decisions that a column name cannot. “Revenue” needs its inclusion rule, currency treatment, timing basis, and refund treatment. “Customer key” needs the entity it represents and the source or matching process behind it. Descriptions that merely expand net_revenue to “net revenue” create the appearance of documentation without giving the reader a definition.

Version control then gives the logic a revision history. The useful unit is not only the query text but the combination of code, documentation, tests, and release context. A changed result should be traceable to a changed source, a changed rule, or both. Modular models, documentation, testing, and version control are all parts of the analytics-engineering approach described in the dbt ELT workflow; comparable controls can be implemented without that particular product.

Tests check declared assumptions, not truth in the abstract#

A transformation should state what must be true for its result to be usable. A data test is an assertion about a source or transformed model. The dbt documentation describes built-in patterns for non-null, unique, accepted-value, and relationship checks, as well as custom assertions that return failing records. These tests report whether the configured assertion passes or fails for the records examined.

The four basic patterns are valuable when attached to a reason:

• A uniqueness check asks whether a key appears once at the declared grain. It can catch join multiplication or an incorrect deduplication rule, but a passing result does not show that the chosen grain answers the business question.
• A non-null check asks whether a required field is present. It can protect a downstream join or calculation, but a populated value can still be inaccurate or late.
• An accepted-value check asks whether a field belongs to a configured set, such as a defined group of order states. It can expose a new source label, but membership does not show that the source classified the event correctly.
• A relationship check asks whether a referenced key resolves in another model. It can find orphaned order records, but matching identifiers do not by themselves show that two rows describe the same real-world entity.

Tests should follow the model’s failure modes. A one-row-per-order model needs a unique, non-null order key. A daily account snapshot may need uniqueness on account plus date. A revenue model may need a rule against impossible combinations of status and amount. A relationship that is optional in the business should not be made mandatory just because a generic test exists.

Checks also belong at more than one point. Testing a landed source can reveal that an expected field disappeared or a batch arrived empty. Testing an intermediate model can locate multiplication introduced by a join. Testing a consumer model can protect its contract. A final total alone tells the operator that something changed; checks near the change make the cause easier to isolate.

Automated checks cannot establish that the source captured every intended event. If an application never emitted a valid purchase, a transformation can process the received data perfectly and still undercount purchases. Reconciliation to an independent operational total can reveal some omissions, but even that requires the two systems to describe compatible populations and periods. State the boundary: transformation quality covers the records processed through a declared flow and version, not events that never reached it.

Tools can run rules in different places. AWS documents that Glue Data Quality can evaluate configured rules on cataloged data and within Glue ETL jobs. That illustrates a useful architectural choice: check stored datasets, check records while they move through a job, or do both. The specific syntax and operating behavior are product-specific; the general decision is where a bad record can be detected soon enough to prevent a harmful downstream use.

When a check fails, the response should depend on impact. A broken primary key in a financial output may justify stopping publication. A newly observed optional category may justify warning the owner while preserving the rest of the run. Treating every failure as fatal makes teams disable noisy tests; treating every failure as informational allows known damage to pass. Severity is part of the assertion’s design.

Incremental processing trades simplicity for speed and cost#

Rebuilding every transformed model from all available records is conceptually simple. The same logic sees the full population each time, so corrections to historical rules can be applied consistently. As the population grows, however, full rebuilds can consume too much time or destination compute. Incremental processing limits work to new or changed records, but it adds state and therefore adds ways for a result to become incomplete.

The crucial question is how a transformation recognizes change. A creation timestamp misses later updates. An update timestamp works only if the source maintains it reliably. A monotonically increasing ID does not capture changes to old IDs. A small overlap window can catch some late arrivals, but its width must reflect observed delay rather than convenience. Deletions require their own signal, such as a tombstone or a periodic comparison, because an absent source row does not announce itself.

Incremental logic must also be idempotent: processing the same input again should not create extra business facts. An upsert keyed at the target grain can replace a previous version; an append without a deduplication rule can turn retries into duplicates. If corrections can reach far back, schedule a wider rebuild or maintain a method to target affected partitions.

I would begin with full builds while the dataset and runtime allow it. Move to incremental processing only after defining the target key, change signal, late-arrival policy, deletion behavior, and recovery procedure. The cost is additional engineering and more complicated incident handling. The benefit is worthwhile only when reduced runtime, latency, or compute use matters enough to pay that cost.

Performance tuning should preserve meaning. Filtering earlier, scanning fewer partitions, and avoiding repeated heavy joins can reduce work. Pre-aggregation can help when consumers repeatedly need the same stable grain. But a faster model that changes the population or collapses required detail is not an optimization of the same product; it is a different product. Compare outputs across relevant periods before accepting such a change.

The operating workflow matters as much as the SQL#

A dependable transformation process starts with a question, not a tool. Identify the consumer and the decision, then write the row contract. Inspect the source fields and their actual states. Design the stages, rules, and failure handling. Implement the smallest model that answers the question. Add checks at the assumptions most likely to produce a plausible but wrong result.

Before release, compare the transformed output with an understandable reference. That might be a source-system report for the same defined population and period, a hand-worked sample of known orders, or the previous model version. A difference is not automatically an error: the transformation may intentionally define the population differently. The comparison is useful because it forces the team to explain the difference in business terms.

Review should concentrate on semantic risk. Ask what one row means before debating naming style. Trace a small number of records through joins and aggregations. Examine null handling, time zones, late events, and many-to-many relationships. Confirm that excluded records are excluded by an explicit rule. Then consider readability and performance.

Release transformed models with their dependencies and expectations visible. A consumer needs to know whether a table is current, whether a failed check affected it, and whether a definition changed. If a breaking change cannot be avoided, use a new model or field version and give consumers a transition path. Silently redefining a familiar column is cheap for the producer and expensive for every reader.

Ownership should be concrete. A named team should decide definition changes, respond to failed runs, and retire obsolete models. Ownership does not mean that only one team may contribute. It means someone is accountable when two valid-looking definitions conflict or a source change breaks the flow.

The transformation’s unit of reproducibility is a declared flow and version over a stated population and period. Keep the code version, run time, source cutoff, and relevant parameters available. Without that context, two outputs with the same table name may reflect different inputs or rules, and a later comparison becomes guesswork.

Common shortcuts create results that are hard to trust#

One-off dashboard logic is the most common shortcut. It solves the immediate request but hides an important definition inside a presentation layer. When the same question returns, another person writes another expression. Move shared, decision-bearing logic into a maintained model; leave purely visual calculations with the dashboard.

Another shortcut is broad deduplication. Selecting distinct rows can mask repeated delivery, a many-to-many join, or legitimate repeated events. Define which record is a duplicate, by what key, and which version wins. If two records share all selected columns but represent two real transactions, distinct destroys a fact.

Premature consolidation causes a different problem. A universal customer table sounds efficient, but billing accounts, product users, households, and marketing contacts may not map one-to-one. Merging them behind a single “customer ID” without a declared identity rule creates certainty the sources do not support. Preserve source identities and make entity matching an explicit transformation with known limits.

Silent defaults are equally dangerous. Replacing every null amount with zero asserts that missing means none. Mapping every unknown status to “other” may keep a chart neat while hiding a source change. Defaults should represent a justified business interpretation; otherwise retain an unknown state and make it visible.

Finally, tool-first design mistakes features for requirements. A platform may offer scheduling, lineage, graphical mapping, reusable SQL, or managed quality rules. Those capabilities can reduce operating work, but none decides what an order is or when revenue counts. Select tools after identifying execution location, languages, source and destination compatibility, latency, security, versioning, documentation, testing, observability, and cost needs.

Choose the smallest architecture that preserves clear meaning#

For a small, stable workload, a few well-structured queries with version control, documentation, scheduled execution, and focused tests may be enough. Adding a large framework can create more surface area than value. The trigger to add structure is not fashion; it is repeated work, unclear dependencies, slow recovery, conflicting definitions, or a volume of changes that the current approach cannot handle safely.

For a growing analytical environment, I would establish source-shaped staging models, a limited set of reusable intermediate models, and consumer models with explicit grains. I would retain landed records where policy allows, prefer ELT when definitions change often and destination compute is suitable, and introduce incremental logic only where runtime or cost demands it. Critical assertions would stop publication; lower-impact exceptions would be visible and assigned.

That choice has a cost. The team must maintain model contracts, tests, documentation, and release practices. More layers can increase runtime and make navigation harder if each layer lacks a distinct purpose. The condition that reverses the decision is a genuinely simple use: one source, one stable output, low consequence, and little expectation of reuse. In that case, keep the implementation direct while still stating the row grain and main rule.

The central judgment is straightforward: data transformation succeeds when a downstream user can tell what each row means, how an important value was derived, which records were included, and which version produced it. Formatting and movement are necessary mechanics. The durable product is shared meaning that can survive the next source change, definition change, and question.

Frequently asked questions#