Build a Student Results Data Pipeline

Build a tested SQL pipeline that cleans results and charts trusted metrics.

Introduction

30 Second Summary

A subject average can look convincing while a few bad records quietly distort the result. Without checks, those mistakes can travel straight into reports.

In this project, you will build a tested student-results data pipeline in Databricks Free Edition using SQL. You will uncover unreliable records before turning clean data into subject metrics and a bar chart.

What You'll Build

Run your finished pipeline to transform a believable but unreliable summary into a tested bar chart you can explain with confidence.

By the end of this project, you'll have:

  • A bronze baseline that shows how impossible marks and repeated records can distort subject averages.
  • Three data-quality checks that reveal invalid marks, duplicate student records, and inconsistent result labels.
  • A trusted silver-to-gold pipeline that produces six clean records, corrected subject metrics, and a presentation-ready bar chart.
  • Secret Mission: Turn the validation logic into a reusable PASS/FAIL quality scorecard.

Are there any prerequisites?

You need a Windows computer with internet access. You also need an email, Google, or Microsoft sign-in method.

Basic SQL knowledge helps. No existing Databricks workspace or local development tools are required.

Before We Start

Before the hands-on work begins, take a moment to commit to what you are building. Your answer will help you notice whether data analysis, data engineering, or data-quality testing interests you most.

Set Up Your Databricks SQL Workspace

Your tested pipeline needs a no-cost place to run SQL. Databricks Free Edition provides that browser-based workspace.

Before the pipeline can produce a visible result, the Databricks SQL Editor needs an active SQL warehouse. It also needs your workspace catalog with the default schema selected.

In this step, get ready to:
  • Create or enter your Databricks Free Edition workspace.
  • Connect the SQL editor to an available warehouse.
  • Select your data context and confirm it with a SQL query.
Create your Free Edition workspace

Free Edition creates a serverless workspace for learning and experimentation. The service is no-cost with fair-use quotas.

Create a Free Edition workspace

  • Open the official Databricks Free Edition signup page.
  • Choose an available signup method on the page.
  • Complete the account prompts with your preferred method.
  • Enter the workspace that Databricks creates for you.

Use an existing Free Edition workspace

  • Open the official Databricks Free Edition signup page.
  • Select the sign-in option for an existing account.
  • Complete the sign-in process with your original method.
  • Enter your existing Free Edition workspace.

You should see your Databricks workspace with its main sidebar. Your browser is now the home for every query in this pipeline.

Can't Enter Your Workspace?

  • Confirm that you used the same sign-in method associated with your Databricks account.
  • Return to the signup page if Databricks has not created a Free Edition workspace for your account.

Still stuck? Help me access my Databricks Free Edition workspace.

Connect the SQL editor to your data context

The SQL editor sends each query to the selected warehouse for processing. Its catalog and schema selectors determine where unqualified table names are created.

  • Click SQL Editor in the Databricks sidebar.
  • Select an available SQL warehouse from the drop-down near the top of the editor.

You should see a query draft with the selected warehouse shown near the top of the editor.

  • Choose the catalog that shares your workspace's name from the catalog selector.
  • Choose default from the schema selector.

The selectors should now show your workspace catalog and the default schema. Unqualified table names in later steps resolve inside this context.

Run the active-context check

The current-context functions return the catalog and schema that the editor uses. This check catches a wrong selection before the pipeline creates any tables.

  • Add the active-context check to your query draft by pasting this code:
-- Confirm the active data context.
SELECT
  current_catalog() AS catalog_name,
  current_schema() AS schema_name;

What Does This Query Do?

  • The current_catalog() function returns the catalog selected for this query.
  • The current_schema() function returns the schema selected inside that catalog.
  • The aliases give both values clear column names in the results pane.

Databricks continuously autosaves the query draft. Before you run it, which catalog and schema do you expect the results to show?

  • Highlight the complete SELECT statement in the editor.
  • Click Run (1000) above the editor.

You should see one row in the results pane. The catalog_name column shows your workspace catalog.

The schema_name column should show default. That is the data context your pipeline needs.

That's your workspace ready. The editor can now run SQL inside the correct catalog and schema.

No Context Row in the Results?

  • Confirm that an available SQL warehouse is selected near the top of the editor.
  • Highlight only the active-context query if your editor contains other statements.
  • Reselect your workspace catalog if catalog_name shows an unexpected value.

Need another pair of eyes? Help me troubleshoot my Databricks context query.

✔️ Awesome, I've got everything!

Great. Your SQL draft is autosaved with the active-context check.

ⓧ I'd like to double check the full code

-- Confirm the active data context.
SELECT
  current_catalog() AS catalog_name,
  current_schema() AS schema_name;

Your SQL workspace is connected to the right data context. Next, you'll create the bronze table and run a first summary that looks convincing at a glance.

Build the Bronze Table and Baseline

Your Databricks SQL Editor already points to the catalog and schema where your pipeline will live.

A bronze layer keeps source records exactly as received. The first summary can look credible before those records have passed any quality checks.

In this step, get ready to:
  • Load nine raw student-result records into a managed bronze table.
  • Produce one summary row per subject.
  • View the baseline averages plus record counts.
Create the bronze table

The bronze table preserves all nine source rows. This gives your later tests an unchanged starting point.

  • In student_results_pipeline.sql, click after the active-context query.
  • Paste the bronze statement below into the query draft:
-- Bronze: preserve all nine source records, including deliberate defects.
CREATE OR REPLACE TABLE student_results_bronze AS
SELECT * FROM VALUES
  (1, 'Amina',  'MATH',    78,  'PASS'),
  (2, 'Neo',    'MATH',    42,  'PASS'),
  (2, 'Neo',    'MATH',    42,  'PASS'),
  (3, 'Lerato', 'MATH',    140, 'PASS'),
  (4, 'Sipho',  'SCIENCE', 65,  'PASS'),
  (5, 'Zuri',   'SCIENCE', 48,  'FAIL'),
  (6, 'Ayesha', 'SCIENCE', 91,  'PASS'),
  (7, 'Thabo',  'MATH',    55,  'PASS'),
  (8, 'Naledi', 'SCIENCE', -5,  'FAIL')
AS raw(student_id, student_name, subject, mark, recorded_result);

What Does This SQL Do?

  • CREATE OR REPLACE TABLE creates student_results_bronze in your selected catalog and schema.
  • VALUES supplies the nine source rows directly inside the query.
  • AS raw(student_id, student_name, subject, mark, recorded_result) assigns a name to every column.
  • Highlight the CREATE OR REPLACE TABLE student_results_bronze AS statement through its final line.
  • Click Run (1000) above the editor.

You should see the statement finish without an error. Good progress. Your pipeline now has its raw-data starting point.

Bronze Table Not Created?

  • Make sure your highlighted SQL starts with CREATE OR REPLACE TABLE student_results_bronze AS.
  • Include all nine tuples through the AS raw(student_id, student_name, subject, mark, recorded_result) line.
  • Confirm that the SQL warehouse from the previous step remains selected near the top of the editor.

Help me troubleshoot why my bronze table was not created.

Run the baseline summary

An aggregation compresses the nine source rows into one summary row per subject.

  • Click below the bronze table statement in student_results_pipeline.sql.
  • Paste the baseline summary below into the query draft:
-- Deliberately unreliable baseline summary.
SELECT
  subject,
  ROUND(AVG(mark), 1) AS average_mark,
  COUNT(*) AS record_count
FROM student_results_bronze
GROUP BY subject;

How Does the Summary Work?

  • ROUND(AVG(mark), 1) calculates each subject's average mark to one decimal place.
  • COUNT(*) counts the source rows included in each subject's result.
  • GROUP BY subject produces one result row for each subject.
  • Pause briefly so the SQL Editor autosaves the updated student_results_pipeline.sql query draft.

Before you run it, do you expect the two subject averages to look believable?

  • Highlight the baseline SELECT statement from its first line through GROUP BY subject.
  • Click Run (1000) above the editor.

The MATH row shows an average_mark of 71.4. Its record_count is 5.

The SCIENCE row shows an average_mark of 49.8. Its record_count is 4.

You now have a credible-looking baseline. That is exactly what this checkpoint was meant to produce.

That credibility is the intended trap. The raw records still lack explicit quality tests.

Summary Values Do Not Match?

  • Confirm that you highlighted only the baseline SELECT statement.
  • Compare your VALUES section with all nine source rows above.
  • Confirm that the bronze table statement completed before running the summary.

Help me find why my baseline averages or record counts are different.

  • Use the tabs below to compare your query draft with the completed code for this step.

✔️ Awesome, I've got everything!

Your bronze table is ready. Your baseline query matches the target for this step.

ⓧ I'd like to double check the full code

-- Confirm the active data context.
SELECT
  current_catalog() AS catalog_name,
  current_schema() AS schema_name;

-- Bronze: preserve all nine source records, including deliberate defects.
CREATE OR REPLACE TABLE student_results_bronze AS
SELECT * FROM VALUES
  (1, 'Amina',  'MATH',    78,  'PASS'),
  (2, 'Neo',    'MATH',    42,  'PASS'),
  (2, 'Neo',    'MATH',    42,  'PASS'),
  (3, 'Lerato', 'MATH',    140, 'PASS'),
  (4, 'Sipho',  'SCIENCE', 65,  'PASS'),
  (5, 'Zuri',   'SCIENCE', 48,  'FAIL'),
  (6, 'Ayesha', 'SCIENCE', 91,  'PASS'),
  (7, 'Thabo',  'MATH',    55,  'PASS'),
  (8, 'Naledi', 'SCIENCE', -5,  'FAIL')
AS raw(student_id, student_name, subject, mark, recorded_result);

-- Deliberately unreliable baseline summary.
SELECT
  subject,
  ROUND(AVG(mark), 1) AS average_mark,
  COUNT(*) AS record_count
FROM student_results_bronze
GROUP BY subject;

Your bronze table now preserves the raw records. Next, you will challenge its believable summary with three data-quality tests.

Test the Bronze Data

The summary from your bronze data layer in Databricks Free Edition looks believable. However, believable totals can still hide records that should never reach analysis.

SQL validation queries challenge the assumptions behind that summary. Each query checks one rule before the data moves forward.

In this step, get ready to:
  • Find marks outside the accepted range.
  • Detect repeated student-subject records.
  • Compare recorded results with the results implied by valid marks.
Test the accepted mark range

A mark is valid when it falls between 0 and 100. The range test returns every bronze row outside those inclusive limits.

  • Append the mark-range test to student_results_pipeline.sql by pasting this code at the end:
-- Test 1: marks must be between 0 and 100.
SELECT student_id, student_name, subject, mark
FROM student_results_bronze
WHERE mark NOT BETWEEN 0 AND 100;

What does this range test prove?

  • The NOT BETWEEN 0 AND 100 condition returns marks outside the accepted range.
  • The selected identity columns show which source records failed the rule.
  • Pause briefly while the editor autosaves student_results_pipeline.sql.

Before you run this test, predict whether the bronze table contains any marks outside the accepted range.

  • Highlight only the mark-range validation statement in the SQL editor.
  • Click Run (1000) above the editor.

You'll see Lerato with a mark of 140. You'll also see Naledi with a mark of -5.

These are the two records that violate the accepted mark range.

No Out-of-Range Rows?

Confirm that you highlighted the statement ending with WHERE mark NOT BETWEEN 0 AND 100. Check that the selected catalog still matches the context from earlier.

Help me diagnose why my mark-range query returns no rows.

Test student-subject uniqueness

Each student should have one record per subject. Grouping by student_id plus subject exposes any combination that appears more than once.

  • Append the uniqueness test to student_results_pipeline.sql by pasting this code below the range test:
-- Test 2: each student-subject pair must be unique.
SELECT
  student_id,
  subject,
  COUNT(*) AS duplicate_count
FROM student_results_bronze
GROUP BY student_id, subject
HAVING COUNT(*) > 1;

What does this uniqueness test prove?

  • The GROUP BY student_id, subject clause collects matching student-subject records into groups.
  • The duplicate_count value reports the number of rows in each group.
  • The HAVING COUNT(*) > 1 condition keeps only groups that break the uniqueness rule.
  • Pause briefly while the editor autosaves student_results_pipeline.sql.

Before you run this test, predict whether the repeated Neo records will become one result group or two separate result rows.

  • Highlight only the student-subject uniqueness statement in the SQL editor.
  • Click Run (1000) above the editor.

You'll see one group with student_id 2. Its subject is MATH.

The duplicate_count is 2. This proves that the bronze table contains one duplicated student-subject group.

Duplicate Group Missing?

Confirm that the query groups by both student_id and subject. Make sure the HAVING COUNT(*) > 1 condition appears after the grouping clause.

Help me find why my uniqueness query does not return Neo's duplicate group.

Test result consistency

A valid mark still fails data quality when its recorded label follows the wrong rule. This test calculates the expected label before comparing it with recorded_result.

  • Append the result-consistency test to student_results_pipeline.sql by pasting this code below the uniqueness test:
-- Test 3: valid marks must agree with the recorded result.
SELECT student_id, student_name, subject, mark, recorded_result
FROM student_results_bronze
WHERE mark BETWEEN 0 AND 100
  AND recorded_result <>
    CASE WHEN mark >= 50 THEN 'PASS' ELSE 'FAIL' END;

What does this consistency test prove?

  • The mark BETWEEN 0 AND 100 condition limits the comparison to valid marks.
  • The CASE expression calculates PASS for marks of at least 50.
  • The <> comparison returns rows whose recorded label disagrees with the calculated result.
  • Pause briefly while the editor autosaves student_results_pipeline.sql.

Before you run this final test, predict whether any valid mark will disagree with its recorded result.

  • Highlight only the result-consistency statement in the SQL editor.
  • Click Run (1000) above the editor.

You'll see two rows for Neo. Each row has a mark of 42 with a recorded_result of PASS.

Both rows fail because a mark below 50 should produce FAIL.

That's the diagnosis complete. You now have evidence for every defect hidden behind the baseline summary.

Consistency Test Returning the Wrong Rows?

Confirm that the range condition uses BETWEEN 0 AND 100. Check that the CASE expression returns PASS when mark >= 50.

Help me debug my result-consistency query against the bronze table.

✔️ Awesome, I've got everything!

Your query draft now contains all three bronze validation tests. Each test exposes a specific reason the baseline summary cannot be trusted.

ⓧ I'd like to double check the full code

-- Confirm the active data context.
SELECT
  current_catalog() AS catalog_name,
  current_schema() AS schema_name;

-- Bronze: preserve all nine source records, including deliberate defects.
CREATE OR REPLACE TABLE student_results_bronze AS
SELECT * FROM VALUES
  (1, 'Amina',  'MATH',    78,  'PASS'),
  (2, 'Neo',    'MATH',    42,  'PASS'),
  (2, 'Neo',    'MATH',    42,  'PASS'),
  (3, 'Lerato', 'MATH',    140, 'PASS'),
  (4, 'Sipho',  'SCIENCE', 65,  'PASS'),
  (5, 'Zuri',   'SCIENCE', 48,  'FAIL'),
  (6, 'Ayesha', 'SCIENCE', 91,  'PASS'),
  (7, 'Thabo',  'MATH',    55,  'PASS'),
  (8, 'Naledi', 'SCIENCE', -5,  'FAIL')
AS raw(student_id, student_name, subject, mark, recorded_result);

-- Deliberately unreliable baseline summary.
SELECT
  subject,
  ROUND(AVG(mark), 1) AS average_mark,
  COUNT(*) AS record_count
FROM student_results_bronze
GROUP BY subject;

-- Test 1: marks must be between 0 and 100.
SELECT student_id, student_name, subject, mark
FROM student_results_bronze
WHERE mark NOT BETWEEN 0 AND 100;

-- Test 2: each student-subject pair must be unique.
SELECT
  student_id,
  subject,
  COUNT(*) AS duplicate_count
FROM student_results_bronze
GROUP BY student_id, subject
HAVING COUNT(*) > 1;

-- Test 3: valid marks must agree with the recorded result.
SELECT student_id, student_name, subject, mark, recorded_result
FROM student_results_bronze
WHERE mark BETWEEN 0 AND 100
  AND recorded_result <>
    CASE WHEN mark >= 50 THEN 'PASS' ELSE 'FAIL' END;

Your bronze table has now failed all three quality checks. Next, you'll use those failures to build a trusted silver table.

Build the Trusted Silver Table

Your bronze SQL tests exposed two invalid marks. They also exposed a repeated student record plus inconsistent results.

Those failures show why the pipeline needs a validated silver layer.

The transformation keeps marks within the accepted range. A grouped intermediate result removes repeated records.

A CASE expression then calculates every result from the same rule.

In this step, get ready to:
  • Create a silver table containing valid student records.
  • Remove repeated student records through grouping.
  • Recalculate each PASS or FAIL result from the mark.
Create the silver table

The silver transformation applies each correction while preserving the original bronze table. This gives you a clean table for later analysis.

  • Return to the SQL Editor from earlier.
  • Place your cursor after the final bronze validation query in student_results_pipeline.sql.
  • Add the silver transformation below the bronze tests by entering this SQL:
-- Silver: keep valid unique records and calculate the result consistently.
CREATE OR REPLACE TABLE student_results_silver AS
SELECT
  student_id,
  student_name,
  subject,
  mark,
  CASE WHEN mark >= 50 THEN 'PASS' ELSE 'FAIL' END AS result
FROM (
  SELECT
    student_id,
    student_name,
    subject,
    mark
  FROM student_results_bronze
  WHERE mark BETWEEN 0 AND 100
  GROUP BY student_id, student_name, subject, mark
) AS valid_unique_records;

What does this transformation do?

  • The WHERE mark BETWEEN 0 AND 100 condition keeps records within the accepted mark range.
  • The nested query groups records by student_id. Each repeated student record collapses into one row.
  • The CASE WHEN mark >= 50 THEN 'PASS' ELSE 'FAIL' END expression calculates every result from the same threshold.
  • The CREATE OR REPLACE TABLE statement makes the transformation safe to rerun during development.

The SQL Editor continuously autosaves the query draft. The new statement is ready to run once you select it.

  • Highlight the statement from CREATE OR REPLACE TABLE student_results_silver AS through its closing semicolon.
  • Click Run (1000) above the editor.

The statement should finish without an error.

Good progress. The student_results_silver managed table now exists in the selected catalog context.

Silver table statement not completing?

  • Confirm that the available SQL warehouse remains selected near the top of the editor.
  • Check that your selection starts with CREATE OR REPLACE TABLE.
  • Check that your selection includes the closing semicolon after valid_unique_records.

Help me diagnose why my silver table statement is failing in Databricks.

Inspect the cleaned records

A successful table creation confirms that the SQL ran. Inspecting every row confirms that the transformation produced the intended data.

  • Add the inspection query below the silver transformation by entering this SQL:
SELECT * FROM student_results_silver;

What does this query show?

The SELECT * query returns every column from student_results_silver. This lets you verify the complete cleaned dataset in one result table.

✔️ Awesome, I've got everything!

Your silver transformation plus inspection query are now part of the autosaved draft.

ⓧ I'd like to double check the full code

-- Confirm the active data context.
SELECT
  current_catalog() AS catalog_name,
  current_schema() AS schema_name;

-- Bronze: preserve all nine source records, including deliberate defects.
CREATE OR REPLACE TABLE student_results_bronze AS
SELECT * FROM VALUES
  (1, 'Amina',  'MATH',    78,  'PASS'),
  (2, 'Neo',    'MATH',    42,  'PASS'),
  (2, 'Neo',    'MATH',    42,  'PASS'),
  (3, 'Lerato', 'MATH',    140, 'PASS'),
  (4, 'Sipho',  'SCIENCE', 65,  'PASS'),
  (5, 'Zuri',   'SCIENCE', 48,  'FAIL'),
  (6, 'Ayesha', 'SCIENCE', 91,  'PASS'),
  (7, 'Thabo',  'MATH',    55,  'PASS'),
  (8, 'Naledi', 'SCIENCE', -5,  'FAIL')
AS raw(student_id, student_name, subject, mark, recorded_result);

-- Deliberately unreliable baseline summary.
SELECT
  subject,
  ROUND(AVG(mark), 1) AS average_mark,
  COUNT(*) AS record_count
FROM student_results_bronze
GROUP BY subject;

-- Test 1: marks must be between 0 and 100.
SELECT student_id, student_name, subject, mark
FROM student_results_bronze
WHERE mark NOT BETWEEN 0 AND 100;

-- Test 2: each student-subject pair must be unique.
SELECT
  student_id,
  subject,
  COUNT(*) AS duplicate_count
FROM student_results_bronze
GROUP BY student_id, subject
HAVING COUNT(*) > 1;

-- Test 3: valid marks must agree with the recorded result.
SELECT student_id, student_name, subject, mark, recorded_result
FROM student_results_bronze
WHERE mark BETWEEN 0 AND 100
  AND recorded_result <>
    CASE WHEN mark >= 50 THEN 'PASS' ELSE 'FAIL' END;

-- Silver: keep valid unique records and calculate the result consistently.
CREATE OR REPLACE TABLE student_results_silver AS
SELECT
  student_id,
  student_name,
  subject,
  mark,
  CASE WHEN mark >= 50 THEN 'PASS' ELSE 'FAIL' END AS result
FROM (
  SELECT
    student_id,
    student_name,
    subject,
    mark
  FROM student_results_bronze
  WHERE mark BETWEEN 0 AND 100
  GROUP BY student_id, student_name, subject, mark
) AS valid_unique_records;

SELECT * FROM student_results_silver;

Before you run this check, how many rows do you expect after removing two invalid records plus one repeated record?

  • Highlight SELECT * FROM student_results_silver; in the editor.
  • Click Run (1000) above the editor.

You'll see six unique rows in the results pane. Every mark falls between 0 plus 100.

Neo's mark remains 42. His result now reads FAIL.

The records with marks of 140 plus -5 are absent.

That is the core correction complete. Your silver table now contains the six records that the pipeline can trust.

Results do not show six clean rows?

  • Confirm that you ran the CREATE OR REPLACE TABLE student_results_silver AS statement before the inspection query.
  • Check that the range filter reads WHERE mark BETWEEN 0 AND 100.
  • Check that the grouped columns include student_id. The duplicate collapses only when the identifying fields are grouped consistently.

Help me compare my silver table results with the intended six-row output.

Your pipeline now has a clean detail layer with consistent results. Next, you'll turn these trusted records into subject-level metrics plus a chart.

Create Gold Metrics and a Chart

Your silver table now contains six valid student records. That trusted detail is ready for presentation.

Clean detail rows are still awkward to present. In this step, you will use SQL aggregation in Databricks to create subject metrics. You will turn those metrics into a bar chart.

In this step, get ready to:
  • Create a gold table with average marks and pass rates for each subject.
  • Verify the corrected averages in the gold table.
  • Save a bar chart that compares the trusted subject averages.
Create the gold metrics table

A gold table summarizes trusted records for reporting. Each row in this table represents one subject.

  • Return to student_results_pipeline.sql in the SQL Editor from earlier.
  • Scroll below SELECT * FROM student_results_silver;.
  • Add the gold aggregation by copying this SQL:
-- Gold: aggregate the trusted records for presentation.
CREATE OR REPLACE TABLE student_results_gold AS
SELECT
  subject,
  ROUND(AVG(mark), 1) AS average_mark,
  SUM(CASE WHEN result = 'PASS' THEN 1 ELSE 0 END) AS passed_students,
  COUNT(*) AS total_students,
  ROUND(
    100.0 * SUM(CASE WHEN result = 'PASS' THEN 1 ELSE 0 END) / COUNT(*),
    1
  ) AS pass_rate_percent
FROM student_results_silver
GROUP BY subject;

What does this SQL calculate?

  • The ROUND(AVG(mark), 1) expression calculates the average mark to one decimal place.
  • The SUM(CASE WHEN result = 'PASS' THEN 1 ELSE 0 END) expression counts the students who passed.
  • The COUNT(*) expression counts every trusted record in the subject.
  • The pass_rate_percent calculation converts the passed count into a percentage.
  • The GROUP BY subject clause produces one summary row for each subject.
  • Keep the new SQL in student_results_pipeline.sql so the editor can autosave it.
  • Highlight the full gold table creation statement.
  • Click Run (1000) above the editor.

The statement completes successfully. The managed table student_results_gold now contains subject-level metrics built from the silver records.

Gold table not created?

Confirm that you highlighted the complete creation statement. A partial selection can leave the table undefined.

Check that the available SQL warehouse remains selected. Confirm that the workspace catalog still uses the default schema.

Help me diagnose why my Databricks gold table creation statement did not complete.

Check the trusted metrics

The presentation query exposes the finished gold rows. This check proves that the cleaned source records produce corrected averages.

  • Add the presentation query below the gold table statement by copying this SQL:
SELECT * FROM student_results_gold;

What does this query show?

The query returns every subject metric stored in student_results_gold. Its result also supplies the data for your chart.

Before you run it, predict whether MATH or SCIENCE has the higher corrected average.

  • Highlight only the presentation query.
  • Click Run (1000) above the editor.

The results show MATH with an average_mark of 58.3. SCIENCE appears with an average_mark of 68.0.

That is the reporting layer working. Your averages now come from records that passed the silver transformation.

Metrics look different?

Confirm that the presentation query reads from student_results_gold. Check that the gold table creation statement reads from student_results_silver.

Rerun the complete gold table creation statement after correcting either table name.

Help me compare my gold metrics with the expected subject averages.

✔️ Awesome, I've got everything!

Your draft contains the complete bronze-to-silver-to-gold pipeline. The gold query now returns the corrected subject averages.

ⓧ I'd like to double check the full code

-- Confirm the active data context.
SELECT
  current_catalog() AS catalog_name,
  current_schema() AS schema_name;

-- Bronze: preserve all nine source records, including deliberate defects.
CREATE OR REPLACE TABLE student_results_bronze AS
SELECT * FROM VALUES
  (1, 'Amina',  'MATH',    78,  'PASS'),
  (2, 'Neo',    'MATH',    42,  'PASS'),
  (2, 'Neo',    'MATH',    42,  'PASS'),
  (3, 'Lerato', 'MATH',    140, 'PASS'),
  (4, 'Sipho',  'SCIENCE', 65,  'PASS'),
  (5, 'Zuri',   'SCIENCE', 48,  'FAIL'),
  (6, 'Ayesha', 'SCIENCE', 91,  'PASS'),
  (7, 'Thabo',  'MATH',    55,  'PASS'),
  (8, 'Naledi', 'SCIENCE', -5,  'FAIL')
AS raw(student_id, student_name, subject, mark, recorded_result);

-- Deliberately unreliable baseline summary.
SELECT
  subject,
  ROUND(AVG(mark), 1) AS average_mark,
  COUNT(*) AS record_count
FROM student_results_bronze
GROUP BY subject;

-- Test 1: marks must be between 0 and 100.
SELECT student_id, student_name, subject, mark
FROM student_results_bronze
WHERE mark NOT BETWEEN 0 AND 100;

-- Test 2: each student-subject pair must be unique.
SELECT
  student_id,
  subject,
  COUNT(*) AS duplicate_count
FROM student_results_bronze
GROUP BY student_id, subject
HAVING COUNT(*) > 1;

-- Test 3: valid marks must agree with the recorded result.
SELECT student_id, student_name, subject, mark, recorded_result
FROM student_results_bronze
WHERE mark BETWEEN 0 AND 100
  AND recorded_result <>
    CASE WHEN mark >= 50 THEN 'PASS' ELSE 'FAIL' END;

-- Silver: keep valid unique records and calculate the result consistently.
CREATE OR REPLACE TABLE student_results_silver AS
SELECT
  student_id,
  student_name,
  subject,
  mark,
  CASE WHEN mark >= 50 THEN 'PASS' ELSE 'FAIL' END AS result
FROM (
  SELECT
    student_id,
    student_name,
    subject,
    mark
  FROM student_results_bronze
  WHERE mark BETWEEN 0 AND 100
  GROUP BY student_id, student_name, subject, mark
) AS valid_unique_records;

SELECT * FROM student_results_silver;

-- Gold: aggregate the trusted records for presentation.
CREATE OR REPLACE TABLE student_results_gold AS
SELECT
  subject,
  ROUND(AVG(mark), 1) AS average_mark,
  SUM(CASE WHEN result = 'PASS' THEN 1 ELSE 0 END) AS passed_students,
  COUNT(*) AS total_students,
  ROUND(
    100.0 * SUM(CASE WHEN result = 'PASS' THEN 1 ELSE 0 END) / COUNT(*),
    1
  ) AS pass_rate_percent
FROM student_results_silver
GROUP BY subject;

SELECT * FROM student_results_gold;

This reference shows the exact cumulative SQL for the finished pipeline. The final two statements create the gold table and return its metrics.

Build the subject chart

A result table proves the metrics are correct. A visualization makes the difference between the two subject averages easier to demonstrate.

  • Return to the results pane from the presentation query.
  • Click + above the result.
  • Select Visualization.
  • Open the Visualization Type drop-down.
  • Choose Bar.
  • Set subject as the X column.
  • Set average_mark as a Y column.
  • Click Save.

Before you inspect the saved chart, predict which subject produces the taller bar.

  • Compare the two bars in the saved visualization.

You will see bars for MATH and SCIENCE. The SCIENCE bar is taller because its corrected average is 68.0.

Chart missing a subject or value?

Confirm that subject is assigned to the X column. Confirm that average_mark is assigned to a Y column.

Rerun the presentation query if its result pane is no longer active. Recreate the visualization from that refreshed result.

Help me fix my Databricks bar chart configuration.

You have completed the tested bronze-to-silver-to-gold pipeline. Your saved chart now communicates subject metrics built from validated data.

Secret mission

Build a Data-Quality Scorecard

Turn the three validation rules into one reusable quality table. Each row reports a failure count with a clear PASS or FAIL status.

Clean Up Your Resources

Clean Up Your Resources

Choose whether to keep your project resources, pause your work, or delete the managed tables. Databricks Free Edition costs $0 under fair-use quotas.

Your two SQL drafts and saved bar visualization can remain as reusable references. The cleanup options below cover the four managed tables.

Resources you used:

  • The student_results_bronze managed table containing the nine raw records.
  • The student_results_silver managed table containing the six validated records.
  • The student_results_gold managed table containing the subject metrics.
  • The student_results_quality managed table containing the three quality checks.

Keep everything running

No action is needed. Choose this option if you want to review the pipeline or rerun its quality checks.

  • Leave all four managed tables in your selected workspace catalog.
  • Keep student_results_pipeline.sql for rebuilding the bronze, silver, and gold layers.
  • Keep student_results_quality.sql for rerunning the quality scorecard.
  • Use the saved bar visualization to review the corrected subject averages.

Pause - I'll come back to this later

No process needs shutting down because this project has no scheduled workload. Your tables remain available when you return.

  • Close the browser tab when you finish reviewing the project.
  • Leave the four managed tables in your workspace catalog.
  • Return to Databricks Free Edition when you are ready to continue.
  • Rerun the presentation query in student_results_pipeline.sql to display the gold metrics again.

Delete - I don't want to use this again

Removing the tables is permanent. Your SQL drafts and saved bar visualization remain available if you want to rebuild the data later.

  • Confirm that the catalog selector still shows your workspace catalog.
  • Confirm that the schema selector still shows default.
  • Paste the cleanup statements into the SQL Editor from earlier by copying the code below:
DROP TABLE IF EXISTS student_results_quality;
DROP TABLE IF EXISTS student_results_gold;
DROP TABLE IF EXISTS student_results_silver;
DROP TABLE IF EXISTS student_results_bronze;

What Does This Cleanup Do?

Each DROP TABLE IF EXISTS statement removes its named managed table. IF EXISTS lets the cleanup continue when a table is already absent.

The unqualified table names use the catalog and schema selected in the editor. This keeps the cleanup inside the data context used for your pipeline.

Before you run the cleanup, which project artifacts do you expect to remain?

  • Highlight all four cleanup statements.
  • Click Run (1000).

You should see each selected statement finish without an error.

Good work. Your selected data context no longer contains the four project tables.

Cleanup Query Failed?

  • Confirm that the catalog selector still shows your workspace catalog.
  • Confirm that the schema selector still shows default.
  • Run one cleanup statement at a time to identify the table causing the failure.

Help me troubleshoot the table cleanup.

Nice Work!

Nice Work!

That is a strong finish. You built a tested bronze-to-silver-to-gold student-results pipeline in Databricks Free Edition that produces trustworthy subject metrics from deliberately messy records.

What you learned:

  • Preserved nine deliberately messy records in a bronze table. Used SQL validation queries to expose impossible marks, a duplicate student-subject group, and inconsistent PASS labels.
  • Built a trusted silver table with invalid marks removed. Applied deduplication plus consistent conditional logic to correct each PASS or FAIL result.
  • Created a gold table with corrected subject averages plus pass rates. Presented the trusted results in a saved bar chart.
  • Secret Mission: Built a reusable data-quality scorecard in student_results_quality. Combined three checks with UNION ALL so every row reports zero failed records with a PASS status.

Ready to quiz yourself?