Start with what each record is supposed to mean
A data quality problem is not always an obvious error. A table may load successfully and still produce misleading results if its values do not match the meaning users expect. Before cleaning a dataset, write down what one row represents, which fields identify it, and which fields may legitimately be missing. An orders table might contain one row per order; an order-items table might contain several rows for that same order. Repeated order IDs are expected in the second table but may be suspicious in the first.
This definition gives you a basis for checking nulls, duplicates and types. It also helps you decide whether to reject a record, correct it from a trusted source or keep it with a visible warning. Avoid a blanket rule that every missing value is bad or every repeated value is a duplicate.
Nulls: distinguish missing from empty and zero
A null usually means that a value is absent or unknown. It is not the same as the number zero, an empty text string or a label such as “not applicable.” These differences matter in calculations: zero is a known value, while a null may need separate handling. Likewise, a blank delivery address could mean the field was skipped, whereas “not applicable” could be a valid answer for an order that requires no delivery.
Start by checking null rates in fields that a workflow depends on, such as an order ID, event timestamp or product code. Then look at where missing values originate. An optional form field may reasonably be null; a field that disappeared after a file import suggests a mapping problem. If you use SQL, test for absent values with IS NULL rather than treating them as ordinary values in an equality comparison.
Do not replace every null with zero just to make a dashboard run. That can make missing measurements look like real measurements. Instead, document which fields are required, preserve meaningful distinctions where possible, and decide explicitly how each report should handle unknown values.
Duplicates: define the key before deleting rows
Duplicates often enter a system when an import is run twice, an event is retried, or two sources provide the same record. But identical-looking rows are not always mistakes. Two customers can share a name, and one customer can make two purchases for the same amount. A useful duplicate check therefore starts with a key: a unique order ID, or a documented combination of fields when no single identifier exists.
For each suspected duplicate, ask whether the rows represent one real-world event or separate events. If they represent one event, decide which record is authoritative. You might prefer a record from the primary source, or use an update timestamp if that field is reliable. Keep the rule consistent and retain enough information to explain what was removed or merged.
Prevention is often simpler than repeated cleanup. Where appropriate, enforce uniqueness in the destination table and make recurring imports safe to rerun. A stable source identifier can help a pipeline recognize a previously processed record. Check that the chosen identifier truly describes the entity you intend to keep unique.
Inconsistent types: make the schema explicit
A field that looks uniform in a spreadsheet may contain mixed types. An account ID might arrive as a number in one file and text in another. Converting it to a number could strip leading zeros, even though those zeros are part of the identifier. Dates can arrive in different text formats, and an amount field may contain both numbers and strings with currency symbols. These differences can cause failed imports, incorrect sorting or comparisons that silently miss matches.
Define the intended type for each important field before loading it into a shared table. Treat identifiers as identifiers rather than assuming they are quantities. Choose a consistent representation for timestamps, including how time zones are recorded. For amounts, decide how currency is represented and use a numeric type suited to the calculations you need. Validate incoming values against those rules, and set aside records that cannot be converted safely rather than guessing.
Build checks into the pipeline
A lightweight quality checklist can catch problems before they reach a report. For each new batch, check required fields for nulls, count repeated keys, and compare incoming types with the expected schema. Record how many rows pass, fail or need review. If a check fails, keep the raw input available so you can investigate without losing the original values.
Make the checks reflect how the data is used. A missing optional note may be harmless, while a missing order ID may prevent reliable joins. Review the rules when a source form, export or business process changes. Data quality improves when a team can explain not only that a value passed a check, but also why the check matters.
