Guided Projects / Can I Trust This Data? — Agricultural Exports, Prices and Technology

🧭 PROJECT BRIEF

Can I Trust This Data? — Agricultural Exports, Prices and Technology

Intermediate Data Quality 🕒 ~20 hours 📦 0+ published takes

A client has sent three spreadsheets: exports by country, commodity and year; daily commodity prices; and technology adoption on a hundred farms. They want to know what the data says about prices and trade, and what the exports were worth. Before it can say anything, you have to decide whether it can be trusted.

Work through the seventeen tasks with the book Can I Trust This Data? beside you: each task follows one chapter, and each chapter ends with a Try It box that describes the same task. Profile every table before you query it. Find the duplicate rows, the missing values and rows, and the values that are wrong but look right. Standardize the spellings, check the data against the business rules and the country master, and record every issue in a log. Build the clean views without altering the raw tables. Then join exports to an annual price, measure how prices and tonnage changed, design a data-quality dashboard, write the analysis up, and name the causes of the faults.

The project is one notebook for the whole book. Add each chapter's blocks below the previous chapter's, in order, and assign them to that chapter's task. Later tasks read views built by earlier ones (country_aliases from Task 8, quality_issues from Task 10, exports_clean, prices_clean and technology_clean from Task 11, export_value from Task 12, price_by_year from Task 13), so keep the blocks that create a view above the blocks that use it. Where a task asks you to save a number as a view named like ch01_answer, its automatic check reads that view. Views you create are gone after a page reload until you press Run all, so after reopening the project press Run all before running anything else.

You need basic SQL (SELECT, WHERE, GROUP BY). Your notebook is the deliverable: assign every block to its task, write the notes each task asks for, run the automatic checks, press Run all until it passes from top to bottom, and submit.

🧪 Runs in your browser

Dataset → Profile → Clean → SQL → Chart → Quality → Python → Output

Real SQL (DuckDB) and real Python (pandas) run entirely in your browser — nothing to install, and your data never leaves it. ▶▶ Run all replays the whole notebook top-to-bottom; a clean pass marks the published repo as reproducible ⛓.

☑️ The tasks

Measure the cost of a wrong number

Chapter 1. In SQL blocks, run the chapter's queries: the plain SUM of Export_tons, the breakdown into rows loaded twice, rows with no year and rows with a tonnage of zero or below, and the row counts behind it. Then save the cleaned total of ag_exports as a view named ch01_answer with one column, cleaned_total: the sum of Export_tons after removing the exact duplicates (SELECT DISTINCT *), the rows with no Year and the rows whose Export_tons is zero or below. In a note, write the one sentence you would put at the top of a report: the cleaned total and what it still does not account for. Assign the blocks to Task 1.

Test a candidate key

Chapter 2. Run the chapter's queries, and in a note write the grain sentence and the key of each of the four tables. Then test whether Date alone could be the key of ag_prices: among the rows that have a date, count how many repeat a date already present (rows minus distinct dates), and save the count as a view named ch02_answer with one column, repeated_dates. In the note, list the dates that occur more than once and say for each whether the repetition breaks the grain or is what the grain allows. Assign the blocks to Task 2.

Count three ways

Chapter 3. Run the chapter's queries, including CREATE OR REPLACE VIEW wheat_exports, which the check also looks for. Then run the three forms of COUNT on ag_prices, counting Date instead of Year, and save them as a view named ch03_counts with the columns all_rows, rows_with_date and distinct_dates. In a note, state how many rows have no date and how many distinct dates there are. Assign the blocks to Task 3.

Profile the three data tables

Chapter 4. Add the chapter's profiling queries as you go, including CREATE OR REPLACE TABLE ch04_profile, which the check also looks for. Open the Profile & Quality panel for each table and, in a note, list where it and your SUMMARIZE results differ. Then count the distinct Country values of ag_exports and save the count as a view named ch04_answer with one column, countries. In the note, list every symptom the profile shows, one per line, with its column. Assign the blocks to Task 4.

Count the key duplicates

Chapter 5. Add the chapter's queries as you go. Save the number of key duplicates in ag_exports as a view named ch05_answer with one column, key_duplicates: rows minus distinct (Country, Commodity, Year), over the rows that have a Year, with the country as recorded. List every contested key with its lowest and highest tonnage (HAVING COUNT(*) greater than 1), and in a note write the one-sentence question you would send to the owner of the data. Assign the blocks to Task 5.

Find the missing values and rows

Chapter 6. Add the chapter's queries as you go. Save the number of rows in ag_prices with no Date as a view named ch06_answer with one column, no_date. Then list every row with a missing value in ag_prices or ag_exports (five rows), and every commodity and year from 2015 to 2022 that has export rows but no price, written with NOT EXISTS (seventeen). In a note, write what each missing value or missing row prevents you from calculating. Assign the blocks to Task 6.

Catch the wrong values

Chapter 7. Add the chapter's queries as you go. Count the rows, across the three data tables, that break one of the chapter's range rules: Export_tons above zero, Adoption_Year from 2000 to 2026, Price_per_ton from 50 to 1,000 and Efficiency_Gain from 0 to 1 (a missing value is not counted here), and save the count as a view named ch07_answer with one column, range_violations. Then write the wrong-values report with UNION ALL, and in a note give each row an action (exclude, correct by a stated rule, or ask the owner) and a reason. Assign the blocks to Task 7.

Standardize the country names

Chapter 8. Start with the chapter's CREATE OR REPLACE VIEW country_aliases (columns alias and canonical, aliases in lower case). Tasks 9 to 17 use this view, so keep its block above theirs. Then run the standardized GROUP BY, the unmapped-values query (it should return no rows) and the key-duplicate count before and after standardizing. In a note, write two sentences explaining to the owner of the data why the count rose from 33 to 39. Assign the blocks to Task 8.

Check the rules and the master list

Chapter 9. This task uses country_aliases from Task 8, which sits above it in the notebook. Run the chapter's queries, including the rules table. Count the export rows whose Country, as recorded, is not a country_name in country_master, and save the count as a view named ch09_answer with one column, not_in_master. In a note, write the rules as a plain-language catalog, one line per rule with its severity and the person or team you would expect to own it, and one line saying which definition of the annual price you will use in Task 12, and why. Assign the blocks to Task 9.

Log every issue

Chapter 10. This task uses country_aliases from Task 8. Create the chapter's quality_issues view exactly as printed (columns table_name, issue, row_key, value); Tasks 14, 15 and 17 read it. Run the count by table and issue. In a note, for each issue write the treatment from Table 10-1 you will apply in Task 11 and whom, in a real organization, you would send its lines to. Save the list of contested keys in a block of its own: it is the question for the owner of the exports that Task 5 promised. Assign the blocks to Task 10.

Build the clean views

Chapter 11. This task uses country_aliases from Task 8. Create exports_clean, prices_clean and technology_clean as in the chapter, with lower-case column names (country, commodity, year, export_tons; commodity, price_date, price_per_ton; farm_id, tech_type, adoption_year, efficiency_gain), and then exports_contested_keys. Tasks 12 to 17 read these views. Run the test table until every row reads true. In a note, write one line for each exclusion in Table 11-1 in your own words, and one sentence on what exports_clean does not fix. Assign the blocks to Task 11.

Join exports to an annual price

Chapter 12. This task uses country_aliases (Task 8) and exports_clean and prices_clean (Task 11). Create the chapter's export_value view (columns country, commodity, year, export_tons, avg_price, export_value). Before trusting it, keep three checks beside it: the row count before and after the left join to the annual prices (95 and 95), the anti-join listing the export rows with no price (59) and the row count of the view (36). In a note, write the coverage sentence for the value results, and the two tonnage rankings (all years, and 2020 to 2022) with one line on why they differ. Assign the blocks to Task 12.

Measure the change

Chapter 13. This task uses the views of Tasks 8, 11 and 12. Create the chapter's price_by_year view (commodity, year, observations, avg_price) and run the chapter's window-function queries. Then repeat the like-for-like analysis for 2021 to 2022: classify each country-commodity pair as present in both years, gone or new, compute the change within the pairs present in both years, and write one sentence in a note saying whether the 5.4 percent rise in total tonnage from 2021 to 2022 is a rise in exports or a change in coverage. Assign the blocks to Task 13.

Build the quality dashboard

Chapter 14. This task uses the views of Tasks 8, 10, 11 and 13. Save the chapter's dashboard query as a view named ch14_dashboard, keeping its five column names (rows_received, rows_usable, price_dates_pct, key_dup_pct, issues_logged), and add a sixth column, unvalued_export_rows: the rows of exports_clean with no matching year in price_by_year for their commodity. In a note, write the one-sentence finding for the rice price with its observation counts, name the reader you have in mind and the form (report, dashboard or infographic) you would give them, and say why the sixth figure belongs on the dashboard. Assign the blocks to Task 14.

Write up the analysis

Chapter 15. This task uses the views of Tasks 8, 10, 11 and 12. Create the chapter's writeup_figures view (columns n, figure, value). Then press Run all: it reruns the whole notebook from the top, in order, and stops at the first block that fails, usually one that reads a view created further down. Move blocks until it runs to the end. In a note, write the six-part write-up in your own words, taking every number from a row of writeup_figures, putting each limitation in the sentence of the figure it limits, and ending with the questions you would send to the owners of the exports and prices data. Assign the blocks to Task 15.

Run the ten checks

Chapter 16. Run the chapter's ten checks on ag_technology, adapting each query to its columns: its grain is one row per farm and its key is Farm_ID. For check 5 list Tech_Type; for check 7 use adoption years 2018 to 2022; for check 8 use the four standard names AI-based sensors, Automated tractors, Drones and Irrigation systems as the master list. Finish with a rules table of four rules (key unique, gain between 0 and 1, adoption year not after 2026, type in the standard list), and save the number of rows that break at least one rule as a view named ch16_answer with one column, flagged_rows. In a note, write one line per check saying what it found. Assign the blocks to Task 16.

Name the causes and submit

Chapter 17. This task uses quality_issues from Task 10. Create a view named issue_causes with two columns, issue and cause, written with VALUES like country_aliases: one row for each of the eleven issue names in quality_issues, with the number (1 to 10) of the cause in the chapter that best explains it. Join it to quality_issues with a LEFT JOIN, confirm that no line of the log is left without a cause, and count the lines by cause. In a note, write the one-paragraph request you would send to the source owner for the cause with the most lines. Assign the blocks to Task 17, press Run all until it passes, and submit with a one-line description.