Understand your source data

1. File formats and encodings

File formats and encodings

A file extension describes a container, not the meaning or quality of its contents. CSV stores rows and delimiters as text; it has no dependable field types. JSON can nest objects and arrays, so one object does not always become one analytical row. Excel can contain several sheets, formulas and formatted blanks. Select the specific sheet and header rather than treating every visible cell as data. Encoding translates bytes into characters. UTF-8 is common; a byte-order mark can attach an invisible character to the first header if decoded incorrectly.

Work through the case

Cedar Supply supplies orders_raw.csv, products.json and a separate monthly planning table. The order CSV uses UTF-8 with a byte-order mark and comma delimiters. It contains 15 data records; the header is not a record. Unit prices use a decimal point and EUR. The product file contains three objects with sku and category. Planning is a different business process; its values are targets, not recorded sales.

A method you can reproduce

Copy the source to a working folder. Record filename, owner, export time, encoding, delimiter and SHA-256. Open CSV with an explicit encoding and newline handling; inspect the parsed headers and a few records before converting types. In a spreadsheet, import identifier fields as text to preserve leading zeros. Keep identifiers such as L001 unchanged. If characters display incorrectly, investigate the declared encoding rather than replacing unfamiliar characters. A hash identifies the bytes you received; it does not certify that those bytes describe reality.

with open('orders_raw.csv', encoding='utf-8-sig', newline='') as f:
    rows = list(csv.DictReader(f))
print(len(rows), list(rows[0]))

Check your reasoning

A successful open is not evidence of correct parsing. Splitting each line on a comma breaks quoted fields containing commas. Let a CSV parser handle quoting and record boundaries; do not guess the delimiter from a single unusual row.

Further reading: pandas merge validation, pandas melt, Python CSV parsing. These official references support the methods; the case and exercises are authored for DataCareerHub.

Exercise

Inventory all three supplied sources. Count data records and inspect the first header for a byte-order mark. Explain why products.json and the planning CSV cannot simply be appended to order rows.

Write your answer in learner_worksheet.txt and retain the evidence file. Attempt this before opening the solution.

2. Data types and analytical grain

Data types and analytical grain

Grain is what one row represents. A stable key identifies that entity at that grain. Distinguish an order from an order line: an order can contain several products. Counts and totals change meaning when grain changes. Text that looks numeric may be an identifier; a postcode or SKU should not be summed. A date string also needs an explicit format. Ambiguous dates cannot be repaired safely without source evidence. Prices need a currency, unit and permitted precision; a blank quantity is not the same as zero.

Work through the case

In this case each accepted line_id represents one order line. order_id repeats legitimately because two lines can belong to the same order. The same line may appear twice in the raw export; repeated order_id alone is not a duplicate defect. quantity must be a positive integer. unit_price_eur allows zero but excludes negative values and fractions smaller than a cent. Dates follow YYYY-MM-DD. The string 04/05/2026 violates that contract because day and month cannot be inferred.

A method you can reproduce

Draft the dictionary before cleaning: field, meaning, type, units, null rule, allowed values and key role. State the proposed output grain as one row per accepted line_id. Add a uniqueness test for line_id after resolving defects, not a blanket distinct across order_id. Compute line_value_cents as quantity multiplied by integer unit_price_cents; keep sums in cents and convert only for display. Verify that the business contract actually allows free items before treating zero prices as valid.

Check your reasoning

The quantity and unit-price rules are teaching assumptions, not universal business rules. Returns or credit notes would need their own signed transaction model. Changing a negative price to positive would create revenue rather than correct evidence.

Further reading: pandas merge validation, pandas melt, Python CSV parsing. These official references support the methods; the case and exercises are authored for DataCareerHub.

Exercise

Write dictionary entries for line_id, order_id, quantity, unit_price_eur and order_date. Classify L005 repeated with a changed quantity and O001 repeated across two lines. State which quantity may be summed.

Write your answer in learner_worksheet.txt and retain the evidence file. Attempt this before opening the solution.

3. Build a source inventory and dictionary

Build a source inventory and dictionary

An inventory explains where a dataset came from and how it can be used. A dictionary explains what its fields mean. Both are living artifacts: version them alongside the transformation rules so someone else can reproduce a result later. Include provenance, owner, extraction window, coverage, refresh frequency, permitted use and unresolved questions. These are different from a profiling report, which measures the file you currently hold. A good contract combines intended meaning with measurable checks.

Work through the case

The fictional Cedar sources contain no customer names or personal identifiers. The order extract is a prepared snapshot, not a live accounting ledger. Its unit prices are teaching values, not complete recognised revenue. The planning source contains category-month targets for April and May. The order snapshot has only April dates. Comparing May targets with absent May orders would confuse unavailable data with zero activity.

A method you can reproduce

Use source_inventory_template.csv and data_dictionary_template.csv. Record the grain and join key for each source, list expected headers and date/price rules, and name a disposition for each failed rule. Mark unknown business definitions as questions for the source owner. Define acceptance checks before transformations: input rows must reconcile to accepted plus quarantined rows; accepted line keys must be unique; lookup matches must be complete. Include the source hash and rule version in the eventual handover. Separate technical acceptance from business approval.

Check your reasoning

A well-formatted dictionary cannot establish source truth. Missing export dates or authoritative rules remain unknown. Do not invent an owner approval, company certification or successful production reconciliation for this synthetic practice case.

Further reading: pandas merge validation, pandas melt, Python CSV parsing. These official references support the methods; the case and exercises are authored for DataCareerHub.

Exercise

Complete both templates and write three questions for a real source owner. Add a row-count reconciliation rule, date rule and key rule. Explain whether a zero May sales figure is defensible.

Write your answer in learner_worksheet.txt and retain the evidence file. Attempt this before opening the solution.