Data Preparation and Blending
What to know
Data Preparation and Blending is about shaping raw inputs into clean, typed, analysis-ready streams. Candidates should be fluent with the Input Data tool options (file format, field delimiters, first-row-contains-data, code page/encoding), the Data Cleansing tool (replacing nulls, removing whitespace, nulls vs. empty strings, modifying case, removing punctuation), Select (renaming, retyping, reordering, deselecting), and Auto Field for type inference. Understand record-level filters, sampling, sorting, and record ID assignment, plus how to blend multiple sources by field name versus position.
Traps
- Null vs. empty string vs. whitespace: Data Cleansing treats them differently depending on which checkboxes are active; a question may describe a value that looks blank but is a space.
- Type coercion: Auto Field guesses, and a numeric-looking string may stay V_String; forcing a type on a field with bad values produces nulls silently.
- Sort before Sample: Sampling without a prior sort returns arbitrary rows; "top N" questions almost always imply a Sort first.
- Multi-field vs. single-field tools: Data Cleansing can act on all fields or selected ones; read which is specified.
Rule of thumb
Prepare types early and verify them: after any Input or Auto Field, a Select tool that pins the expected types makes downstream formula and join behavior predictable. If a value "disappears," suspect a type mismatch before blaming the source.
Worked example
A CSV shows a numeric column with blanks. Auto Field may type it as V_String because of the blanks. Fix by Data Cleansing (replace null with 0, trim whitespace) then Select to set the type to Double, rather than relying on implicit conversion in a downstream Formula.