Relational design & PostgreSQL
Define tables, primary and foreign keys, types and constraints. Keep an imperfect import in a staging table before promoting valid records.
AGENTIC INITIAL STUDIO
Databases & data analysis
Use PostgreSQL, pandas and NumPy to inspect missing values, verify keys, join tables and produce an honest summary from synthetic early-learning data.
DATABASES + PYTHON
Keep the unknowns visible all the way to the summary.
SQL queries & joins
Practise WHERE, IS NULL, GROUP BY, HAVING, window functions and LEFT/INNER JOIN. Compare row counts before and after joining to catch lost or multiplied records.
pandas & NumPy
Use pandas for tables, isna(), duplicate detection, merge validation and grouping. Use NumPy for numeric arrays and explicit handling of NaN. They complement a database.
Summary & validation
State the denominator, retained rows, unknown values and unmatched keys. Reconcile SQL and Python outputs; explain why missing attendance is different from zero attendance.
Suggested starting point: basic Python and spreadsheet familiarity. Learn SQL independently, then connect it with Python analysis in one-to-one online sessions.
EARLY-LEARNING DATA LAB
Inspect → clean → join → summarise → reconcile.
Four fictional centres and eight centre-week records include one repeated record, one missing attendance, an unknown capacity and an unmatched centre ID. There are no real children, families or organisations in this dataset.
Schema, synthetic data & cleaning policy
CREATE TABLE centres (
centre_id TEXT PRIMARY KEY,
centre_name TEXT NOT NULL,
capacity INTEGER CHECK (capacity >= 0)
);
CREATE TABLE enrolments_raw (
source_row INTEGER PRIMARY KEY,
record_id TEXT NOT NULL,
centre_id TEXT NOT NULL,
week TEXT NOT NULL,
enrolled INTEGER NOT NULL,
attended INTEGER
);
INSERT INTO centres VALUES
('C01','Demo Centre A',24),('C02','Demo Centre B',20),
('C03','Demo Centre C',18),('C04','Demo Centre D',NULL);
INSERT INTO enrolments_raw VALUES
(1,'E01','C01','W1',20,18),(2,'E02','C02','W1',15,NULL),
(3,'E03','C03','W1',12,0),(4,'E04','C99','W1',8,6),
(5,'E05','C01','W2',21,19),(6,'E05','C01','W2',21,19),
(7,'E06','C02','W2',16,14),(8,'E07','C04','W1',9,8);
-- Keep staging records intact. Inspect repeats before choosing a policy.
CREATE VIEW enrolments_clean AS
SELECT source_row,record_id,centre_id,week,enrolled,attended
FROM (SELECT *, ROW_NUMBER() OVER
(PARTITION BY record_id ORDER BY source_row) AS occurrence
FROM enrolments_raw) ranked
WHERE occurrence = 1;
Actual PostgreSQL runs in memory in a browser worker through PGlite. First use downloads about 15 MB. Query transactions are read-only; reset discards this temporary database. No server or data upload is involved.
PANDAS / NUMPY
Run the same audit in Python.
Python, pandas and NumPy load only when requested (about 22 MB together). This is actual Python execution with six fixture checks. Edits are temporary; download them before refreshing.
INTERPRET THE RESULT
A tidy table can still hide a wrong assumption.
Keep the audit trail
The fixture has 8 raw rows, 7 unique records and 6 matched centre-week rows. The unmatched C99 record stays visible in the LEFT JOIN audit, while the centre summary excludes it.
Count observations
Matched data has 93 enrolled child-weeks and 59 known attendance child-weeks. Only 5 of the 6 attendance values are observed; these totals are not unique-child counts.
Do not invent a denominator
Centre B has observed mean attendance 14. Replacing its missing week with zero makes the mean 7. Unknown capacity cannot support a defensible occupancy percentage.
Should every duplicate be dropped automatically?
No. This fixture repeats an identical record and explicitly keeps the first source row. Conflicting repeats need an agreed review policy; a database constraint should protect the curated layer.
-- NULL is not zero. COUNT(column) counts observed values.
SELECT COUNT(*) AS rows, COUNT(attended) AS observed,
AVG(attended) AS mean_observed
FROM enrolments_clean;APPLIED DATA PROJECT
From a public dataset to a geographic dashboard.
Explore an Australian migration-data case: official monthly SkillSelect snapshots, preserved suppression, independent state and points slices, snapshot comparisons and downloadable reports. Its observations use transparent rules, without live LLM calls.
Open the migration observatory ↗