End-to-End Pipeline Build and Peer Review

Objective. Students will independently build a complete, documented ETL pipeline from a new data source to a PostgreSQL target database and conduct a structured peer review using a rubric drawn from DAMA CDMP and DP-300 exam criteria.

This lab brings together every skill from the ETL course into a single, assessed deliverable: a complete pipeline you design, build, document, and then review with a classmate. By working through an unfamiliar specification under realistic conditions, you practice the kind of independent problem-solving employers expect from a working database administrator.

What an End-to-End Pipeline Requires

A complete ETL pipeline is more than working code. It has four visible layers that an employer or auditor can inspect:

1. Architecture — a diagram showing the source system, any staging area, transformation logic, and the target database, with data-flow arrows and notes on connection method.

2. Code — extract, transform, and load scripts that are readable, version-controlled, and include inline comments explaining non-obvious decisions.

3. Documentation — written explanation of data source characteristics, every transformation rule, the load strategy chosen (full refresh vs. incremental/upsert), error-handling design, and a run log proving the pipeline executed successfully.

4. Operational evidence — the run log itself: timestamps, row counts in and out, any warnings, and confirmation that the target table matches expectations.

When you receive the instructor's new specification, read it completely before writing a single line of code. Identify the source schema, check for null-able columns or type mismatches, and note any business rules embedded in the transformation instructions. Sketch the architecture diagram first; gaps in the diagram reveal gaps in your understanding before they become bugs.

Designing for Correctness and Safety

Correctness means the pipeline loads exactly the right data, no more and no less. Test each stage in isolation: run the extract and inspect a sample of rows; apply transformations and verify a calculated field against a hand-checked record; load to a test schema before touching production. Use row-count assertions — compare source row count to target row count after each run and raise an error if they diverge beyond an expected tolerance.

Security is a first-class concern at every stage. Never hard-code database credentials in your script. Store connection strings and passwords in environment variables or a secrets file that is excluded from version control (add it to `.gitignore`). When connecting with psycopg2, use parameterized queries for any dynamic SQL to prevent SQL injection. Limit the database user your pipeline runs as to only the privileges it needs — typically INSERT and UPDATE on the target table, SELECT on source objects, nothing else. These practices align directly with DP-300 security objectives and DAMA CDMP data governance principles.

For error handling, wrap each stage in a try/except block. Log the exception with enough context (stage name, timestamp, row identifier if available) to diagnose the failure without re-running. Decide explicitly whether a failed row should stop the entire pipeline (fail-fast) or be written to a quarantine table for later review (partial-load). Document your choice and the reason.

Load Strategies: Choosing and Justifying

Your documentation must state which load strategy you used and why. The three common choices are:

Full refresh (truncate-and-reload): Drop or truncate the target table and reload all rows every run. Simple and reliable for small, slowly changing datasets. Risky for large tables or when downstream processes read the table concurrently.
Incremental append: Insert only rows that are new since the last successful run, identified by a watermark column (e.g., a created-at timestamp or auto-increment key). Efficient but requires a reliable watermark.
Upsert (merge/INSERT … ON CONFLICT): Insert new rows and update changed rows in a single operation. PostgreSQL supports this with `INSERT … ON CONFLICT DO UPDATE`. Correct for datasets where source records can be updated after initial creation.

Your justification should reference the source data's characteristics as given in the specification — update frequency, volume, and whether a watermark column exists.

Writing Documentation That Passes Review

Good pipeline documentation is written for a colleague who was not in the room when you built it. Each required section serves a purpose:

Data source description: system name, format (CSV, API, relational table), access method, and any known data quality issues.
Transformation rule table: one row per rule, with input field, output field, rule description, and an example. A table format makes peer review efficient.
Load strategy and rationale: see above.
Error handling approach: what happens when the extract fails, when a row fails validation, and when the database load raises an exception.
Run log: a copy of actual console or log-file output from a successful test run, with row counts visible.

Conducting a Structured Peer Review

The peer review is not proofreading — it is a technical audit. Work through the rubric item by item. For each criterion, cite specific evidence from the code or documentation: a line number, a section heading, or a log entry. If something is missing or incorrect, describe it precisely and suggest a fix. Vague comments such as "error handling could be better" are not acceptable; write "the load function has no exception handler around the `cursor.execute` call at line 47; if the INSERT fails the script will crash without logging the failed row."

Before submitting your written review, discuss your findings with your peer. They may clarify intent that was not obvious from the code, or you may identify an issue together that neither of you caught alone. Revise your written review after the discussion if new information changes your assessment.

Safety Note

This is a software lab; physical hazards are minimal. However, always follow your program's acceptable-use policy for shared servers. Do not store real personally identifiable information in lab databases. Shut down test database connections when you finish to free server resources for classmates.

Key terms10

ETL pipelineA structured process that Extracts data from a source, Transforms it according to business rules, and Loads it into a target data store; the three stages are typically coded, logged, and documented as a unit.

upsertA combined insert-and-update operation that inserts a row if it does not already exist in the target table or updates it if it does; in PostgreSQL implemented with INSERT … ON CONFLICT DO UPDATE.

watermarkA column value — often a timestamp or incrementing integer — used to identify rows that are new or changed since the last pipeline run, enabling incremental data loads.

parameterized queryA database query in which user-supplied or variable values are passed as separate parameters rather than concatenated into the SQL string, preventing SQL injection attacks.

row-count assertionA programmatic check that compares the number of rows read from the source to the number successfully loaded to the target, raising an error if the counts differ beyond an acceptable tolerance.

quarantine tableA separate database table used to hold rows that failed validation or transformation so they can be reviewed and corrected without blocking the rest of the pipeline load.

full refreshA load strategy in which the entire target table is cleared and reloaded from the source on every pipeline run; simple to implement but not suitable for very large datasets or tables in continuous use.

incremental appendA load strategy that inserts only rows that are new since the last successful run, identified by a watermark; more efficient than full refresh for growing datasets.

run logA timestamped record of a pipeline execution showing each stage completed, row counts in and out, any warnings or errors encountered, and the final success or failure status.

peer review rubricA structured evaluation form listing specific criteria — such as correctness, error handling, security practice, and documentation quality — against which a reviewer assesses a colleague's work with evidence-based ratings.

In class: Receive a new pipeline specification from the instructor (a different source schema, transformation rules, and target table from earlier labs); design the pipeline in a one-page architecture diagram, then code and test the full extract, transform, and load in sequence. · Write pipeline documentation including: data source description, transformation rule table, load strategy chosen and rationale, error handling approach, and a run log from a successful test execution; submit code and documentation together. · Exchange code and documentation with a peer; complete the instructor-provided peer-review rubric covering correctness, error handling, security practice, and documentation quality; write a one-page written review and discuss findings with the peer before submitting both documents.
Materials: Student laptops, Python 3, psycopg2, PostgreSQL 15, instructor-prepared pipeline specification, peer-review rubric (maps to DAMA CDMP data integration and DP-300 objectives), architecture diagram template
Check: Instructor evaluates pipeline code (runs correctly against instructor test data), documentation (all required sections present and accurate), and peer-review (substantive and rubric-criteria-based); pass = pipeline produces correct output on instructor test data AND documentation and peer-review both scored as satisfactory by instructor

Practice check8

Answer each question, then check it. Nothing is recorded; this is your own practice.