Reconcile revision-aware transaction facts
A hands-on PL-300 lab. You produce the real artefact and 9 automated checks verify it behaves the way the exam expects.
Try this labAll PL-300 practice
- Certification
- PL-300
- Format
- SQL query
- Difficulty
- medium
- Estimated time
- 30 min
- Automated checks
- 9
The brief
Write one read-only SQLite query over raw_lines and products. Return exactly order_id, line_no, product_key, quantity, unit_price_cents, revenue_cents, in that column order; row order is unrestricted. The business key is (order_id,line_no). Choose its greatest revision first, then greatest source_seq within that revision. Choose the latest record BEFORE any status, value or product rejection; never fall back to an older row. Identical replays at the same key/revision/sequence have identical payloads and contribute one fact row. Keep the chosen row only if status='active'. Normalize product_code with surrounding ASCII spaces removed and ASCII letters uppercased; require a matching products.canonical_code and output that product_key. Product codes and keys are unique in products. After removing surrounding ASCII spaces, quantity_text must be 1–4 ASCII digits and its integer value 1–9999; price_cents_text must be 1–6 ASCII digits, giving 0–999999 cents. NULL, empty, signs, decimals and trailing letters are invalid. Leading zeros are valid within those lengths. Reject a chosen row with invalid numbers or an unmapped/NULL product. Output integer quantity and price; revenue_cents is their exact product. Keep distinct business lines even when all other values match. An empty source returns no rows. Derive results for new identities and repair without Reset. These source cleanup rules are fictional local requirements, not Power Query defaults.
What the checks verify
Your work is graded on 9 independent properties, not on matching one reference answer.
- One bounded read-only source query executes.
- Return the six fact columns in their required order.
- Choose the greatest revision, then source_seq, separately for each order/line.
- Choose the latest record before rejecting cancellations, invalid values or unknown products.
- Accept only the supplied trimmed ASCII digit grammar and bounds, then derive integer revenue.
- Normalize product codes by ASCII upper case and surrounding spaces; join actual dimension keys.
- Identical ingestion replays contribute one business line without collapsing different lines.
- Derive the fact table for unfamiliar orders and product keys and an empty source.
- Reconcile the visible mixed example to two accepted lines and 950 cents.
Where this sits in the PL-300 blueprint
- Domain
- Prepare the Data
- Objective
- Profile and Clean the Data
- Skill
- Nulls, Errors and Inconsistencies
Part of PL-300 preparation
Labs are written by ExamNova to teach the decisions the exam tests. They are not reproductions of vendor lab content.