Data pipeline
Reliable inputs from raw data
Snowflake layers, dbt models and tests, PySpark transformations, incremental updates and failure checks.
- dbt models
- PySpark job
- Data tests
FOR DATA ANALYSTS MOVING INTO DATA ENGINEERING
Follow one retail project from data modelling and reliable pipelines to churn modelling and AWS batch scoring. AI helps draft and debug; you run, verify and modify the work.
Proposed: two weeks · four guided sessions · three hours each, including breaks
Course preview · schedule to be confirmed


ONE PROJECT · THREE DELIVERABLES
Prepare reliable sales metrics and customer churn scores. Connect raw data, model inputs and repeatable processing and scoring tasks.
Reliable inputs from raw data
Snowflake layers, dbt models and tests, PySpark transformations, incremental updates and failure checks.
A model built on customer data
Historical snapshots, features and labels, a training notebook, leakage checks, evaluation and a saved model.
Processing and scoring as a batch workflow
S3 and Glue, Snowflake features, SageMaker batch scoring, Step Functions orchestration and CloudWatch records.
These are course project goals. Complete Snowflake, Spark, ML and AWS reference outputs are still being prepared.
Three workflows connect data preparation, model training and batch prediction. AI assists with code, debugging and code review; you validate the results.
dbt models and tests handle Snowflake transformations and data quality. The feature dataset feeds both training and batch scoring.
Build historical features and labels from the feature dataset, check data leakage, then evaluate and save a model artifact for Batch Transform.
Export features to S3. Batch Transform reads the feature export and model artifact, then writes prediction files to S3.
Planned course architecture. Open the executed Python / SQLite reference →
RETAILFLOW / LOCAL REFERENCE
RetailFlow orders: additions and cancellations change revenue from A$150 to A$130. Re-running a batch preserves the result; a missing product blocks publication.
Checks passed
Duplicate O100-L1 is removed; three valid lines belong to two orders.
Reference scope: one business date and fixed AUD. Same-key line changes, equal-timestamp precedence, taxes and refunds are not implemented. No scheduling, cloud or live AI execution.
Download the source and inputs and follow the README to reproduce the calculation locally. This page shows precomputed outputs.
# RetailFlow local reference Synthetic, pre-generated teaching reference. Run `python3 pipeline.py --output result.json` using Python 3 with its standard library. No installation, network, API key, AI call, cloud account or charge is needed. `orders-batch-1.csv` and `lines-batch-1.csv` produce AUD 150 revenue, 2 orders, 4 units and AUD 75 average order value after duplicate line removal. Batch 2 cancels O101 and adds O102, producing AUD 130, 2 orders, 4 units and AUD 65 average order value. Repeating batch 2 leaves the result unchanged. An older O101 paid status cannot override the cancellation. A deliberately injected P999 line must fail validation; it is rolled back and the last valid result is retained. The SQL models and Python runner are the complete implementation of this small SQLite reference. The course's planned Snowflake/dbt models, cloud permissions, scheduler and AI workflow are not implemented here. This is not a production platform or a finished implementation of every course outcome. Scope: a single predefined business date, fixed AUD, current-value product categories, immutable line keys. Changed existing line amounts, equal-timestamp conflicts, full source reconciliation, tax/refund accounting, multiple dates, concurrency and production access controls are outside this reference. Explain these limitations when presenting the project. Ownership: course reference template. A learner must add and verify their own change before describing personal contributions. Do not present the reference as real client work, real learner work, or measured career outcomes.Open file
"""RetailFlow local reference: stdlib SQLite, synthetic data, no cloud/API calls.
Run: python3 pipeline.py --output result.json
This validates the teaching logic, not the planned Snowflake/dbt implementation.
"""
import argparse
import csv
import json
import sqlite3
from pathlib import Path
ROOT = Path(__file__).resolve().parent
def execute():
db = sqlite3.connect(':memory:')
db.row_factory = sqlite3.Row
db.executescript((ROOT / 'models.sql').read_text())
def load_orders(filename):
for row in csv.DictReader((ROOT / filename).open()):
db.execute('''INSERT INTO orders VALUES (:order_id,:status,:updated_at)
ON CONFLICT(order_id) DO UPDATE SET status=excluded.status,
updated_at=excluded.updated_at WHERE excluded.updated_at > orders.updated_at''', row)
def load_lines(filename):
for row in csv.DictReader((ROOT / filename).open()):
db.execute('''INSERT INTO lines VALUES (:order_id,:line_id,:product_id,:quantity,:unit_price)
ON CONFLICT(order_id,line_id) DO NOTHING''', row)
def validate():
missing = db.execute('''SELECT l.order_id,l.line_id,l.product_id FROM lines l
LEFT JOIN products p USING(product_id) WHERE p.product_id IS NULL''').fetchall()
if missing:
raise ValueError('Missing product reference: ' + json.dumps([dict(r) for r in missing]))
invalid = db.execute('SELECT COUNT(*) FROM lines WHERE quantity<=0 OR unit_price<0').fetchone()[0]
if invalid:
raise ValueError('Invalid quantity or unit price')
def snapshot():
validate()
row = dict(db.execute('SELECT * FROM daily_sales').fetchone())
row['order_count'] = int(row['order_count'])
return row
db.executemany('INSERT INTO products VALUES (?,?)', [('P100','Accessories'),('P101','Stationery'),('P102','Home')])
load_orders('orders-batch-1.csv')
load_lines('lines-batch-1.csv')
first = snapshot()
assert first['revenue'] == 150 and first['order_count'] == 2 and first['units'] == 4
load_orders('orders-batch-2.csv')
load_lines('lines-batch-2.csv')
second = snapshot()
assert second['revenue'] == 130 and second['aov'] == 65
load_orders('orders-batch-2.csv')
load_lines('lines-batch-2.csv')
repeated = snapshot()
assert second == repeated
load_orders('orders-late.csv')
assert snapshot() == second, 'Older paid status must not overwrite latest cancellation'
db.execute('SAVEPOINT invalid_batch')
db.execute("INSERT INTO lines VALUES ('O102','L2','P999',1,99)")
try:
snapshot()
except ValueError as error:
failure = str(error)
else:
raise AssertionError('Missing product should block result publication')
db.execute('ROLLBACK TO invalid_batch')
restored = snapshot()
assert restored == second
return {
'evidence': 'Executed local SQLite reference; synthetic data; no Snowflake/dbt or scheduler validation',
'business_date': '2026-10-01',
'currency': 'AUD',
'stages': [
{'id':'first','label':'第一批:完成去重','status':'passed','result':first,
'explanation':'重复的 O100-L1 只保留一次,三条有效明细对应两个订单。'},
{'id':'second','label':'第二批:更新与取消','status':'passed','result':second,
'explanation':'O101 最新状态变为 cancelled,移出 A$50;O102 新增 A$30。'},
{'id':'repeat','label':'同批重跑','status':'passed','result':repeated,
'explanation':'第二批再次执行,业务结果与上次完全一致;更旧的 paid 状态也不会覆盖取消。'},
{'id':'failure','label':'异常批次:阻止发布','status':'blocked','result':restored,
'explanation':'P999 不存在,检查失败,异常批次回滚;这里显示上一次有效结果,不是本次新结果。','error':failure},
],
'checks': [
{'name':'Duplicate line removed','status':'passed'},
{'name':'Cancellation applied','status':'passed'},
{'name':'Repeated batch unchanged','status':'passed'},
{'name':'Older status ignored','status':'passed'},
{'name':'Missing product blocks publication','status':'passed'},
{'name':'Rollback retains last valid result','status':'passed'},
],
'limitations': ['One business date; fixed AUD; no tax/refund accounting',
'Line changes with the same key are outside this reference implementation',
'Equal update timestamps require a defined tie-breaker in the course implementation',
'No cloud, AI execution, scheduling, or production deployment demonstrated'],
}
if __name__ == '__main__':
parser = argparse.ArgumentParser()
parser.add_argument('--output', type=Path, default=ROOT / 'result.json')
args = parser.parse_args()
args.output.write_text(json.dumps(execute(), ensure_ascii=False, indent=2) + '\n')
print('Local reference executed: 6 checks passed; wrote', args.output)
Open fileCREATE TABLE products(product_id TEXT PRIMARY KEY, category TEXT NOT NULL);
CREATE TABLE orders(order_id TEXT PRIMARY KEY, status TEXT NOT NULL, updated_at TEXT NOT NULL);
CREATE TABLE lines(
order_id TEXT NOT NULL REFERENCES orders(order_id), line_id TEXT NOT NULL,
product_id TEXT NOT NULL, quantity INTEGER NOT NULL, unit_price REAL NOT NULL,
PRIMARY KEY(order_id,line_id)
);
-- One-date reference. All synthetic orders belong to 2026-10-01 Brisbane time.
-- The course Snowflake/dbt implementation must derive the business date explicitly.
CREATE VIEW valid_lines AS
SELECT l.*, p.category FROM lines l JOIN orders o USING(order_id)
JOIN products p USING(product_id) WHERE o.status IN ('paid','completed');
CREATE VIEW daily_sales AS
SELECT ROUND(COALESCE(SUM(quantity*unit_price),0),2) AS revenue,
COUNT(DISTINCT order_id) AS order_count,
COALESCE(SUM(quantity),0) AS units,
ROUND(SUM(quantity*unit_price)/NULLIF(COUNT(DISTINCT order_id),0),2) AS aov
FROM valid_lines;
Open fileorder_id,status,updated_at O100,paid,2026-10-01T00:00:00Z O101,paid,2026-10-01T00:05:00ZOpen file
order_id,line_id,product_id,quantity,unit_price O100,L1,P100,2,40 O100,L1,P100,2,40 O100,L2,P101,1,20 O101,L1,P102,1,50Open file
order_id,status,updated_at O101,cancelled,2026-10-02T00:00:00Z O102,paid,2026-10-02T00:01:00ZOpen file
{
"evidence": "Executed local SQLite reference; synthetic data; no Snowflake/dbt or scheduler validation",
"business_date": "2026-10-01",
"currency": "AUD",
"stages": [
{
"id": "first",
"label": "第一批:完成去重",
"status": "passed",
"result": {
"revenue": 150.0,
"order_count": 2,
"units": 4,
"aov": 75.0
},
"explanation": "重复的 O100-L1 只保留一次,三条有效明细对应两个订单。"
},
{
"id": "second",
"label": "第二批:更新与取消",
"status": "passed",
"result": {
"revenue": 130.0,
"order_count": 2,
"units": 4,
"aov": 65.0
},
"explanation": "O101 最新状态变为 cancelled,移出 A$50;O102 新增 A$30。"
},
{
"id": "repeat",
"label": "同批重跑",
"status": "passed",
"result": {
"revenue": 130.0,
"order_count": 2,
"units": 4,
"aov": 65.0
},
"explanation": "第二批再次执行,业务结果与上次完全一致;更旧的 paid 状态也不会覆盖取消。"
},
{
"id": "failure",
"label": "异常批次:阻止发布",
"status": "blocked",
"result": {
"revenue": 130.0,
"order_count": 2,
"units": 4,
"aov": 65.0
},
"explanation": "P999 不存在,检查失败,异常批次回滚;这里显示上一次有效结果,不是本次新结果。",
"error": "Missing product reference: [{\"order_id\": \"O102\", \"line_id\": \"L2\", \"product_id\": \"P999\"}]"
}
],
"checks": [
{
"name": "Duplicate line removed",
"status": "passed"
},
{
"name": "Cancellation applied",
"status": "passed"
},
{
"name": "Repeated batch unchanged",
"status": "passed"
},
{
"name": "Older status ignored",
"status": "passed"
},
{
"name": "Missing product blocks publication",
"status": "passed"
},
{
"name": "Rollback retains last valid result",
"status": "passed"
}
],
"limitations": [
"One business date; fixed AUD; no tax/refund accounting",
"Line changes with the same key are outside this reference implementation",
"Equal update timestamps require a defined tie-breaker in the course implementation",
"No cloud, AI execution, scheduling, or production deployment demonstrated"
]
}
Open fileDA, BI or reporting experience; SQL JOIN and GROUP BY; basic Python reading. Check Git and terminal skills before class.
One project, prepared starter environments and checkpoints. AI helps draft; sessions focus on the key engineering steps.
Change a metric, feature or quality rule. Keep the expected output, code difference and verification, and explain your decisions.
CURRICULUM / 4 GUIDED SESSIONS
All five teacher modules remain: roles, warehouses and lakes, ML, Snowflake/Spark, and data products with AWS. Each session includes a ten-minute break. Preparation and homework are additional.
Allow a proposed 60–90 minutes to check SQL, Python, Git and terminal basics. Snowflake, notebooks, historical snapshots, AWS sandbox and templates must be prepared and rehearsed before teaching; access and credits remain unconfirmed.
Session two uses provided historical snapshots. Newly loaded orders are not treated as already-observed churn labels. Teaching order differs from the production batch dependency order.
L01 / 180 MIN
Define retail order grain and a star schema, plan Bronze/Silver/Gold layers, and load the first Snowflake batch.
AI drafts DDL and model notes; you verify grain, joins and amount definitions.
Existing SQL skills; prepared CSV inputs and Snowflake environment.
0–15 min
Review the project goal and distinguish available evidence from planned outputs
15–35 min
DA/DE/DS responsibilities and hand-offs
35–60 min
OLTP/OLAP, warehouse/lake/lakehouse, Iceberg/Delta and SCD2 boundaries
60–90 min
AI-assisted star schema and layers; inspect grain, keys and amounts
90–100 min
Break
100–145 min
Run DDL, stage and COPY INTO in a prepared Snowflake environment; validate CSV loads
145–170 min
Use a reference transformation to create customer inputs; check types, fields and prediction time
170–180 min
Record the data contract and inputs for the next session
Review the model, change a field or rule and record its meaning.
Explain grain, keys and layers; retain load and verification evidence.
L02 / 180 MIN
Use historical retail snapshots to build features and labels, train a simple classifier, explain evaluation and save a batch-scoring model.
AI drafts features and training code; you reject leaky features and explain evaluation.
Provided historical snapshots; newly loaded orders are not mature churn labels.
0–15 min
Revisit retail churn: define prediction time and the intended consumer
15–40 min
Supervised/unsupervised learning and generative AI; features, labels, splits, overfitting and metrics
40–65 min
Review AI feature suggestions for time boundaries and future-data leakage
65–90 min
Prepare customer features and labels from course snapshots; check train/validation/test boundaries
90–100 min
Break
100–140 min
AI-assisted scikit-learn logistic regression or tree notebook; split before fitting preprocessing
140–165 min
Read confusion matrix, precision, recall and AUC; discuss missed churn and false alarms
165–180 min
Save the model, feature notes, split and evaluation as inputs to AWS batch scoring
Review or change one feature; explain leakage risks and validation changes without inventing gains.
Explain labels, prediction time, splits and evaluation; keep a runnable notebook and model.
L03 / 180 MIN
Build updateable transformations with Snowflake and dbt tests, implement a PySpark counterpart, and verify increments, repeated batches and invalid data.
AI rewrites SQL/PySpark and helps debug; you independently compare outputs.
Session-one models, session-two feature definitions and a prepared notebook.
0–20 min
ETL/ELT, batch/micro-batch/streaming; Snowflake compute/storage separation and Spark execution
20–65 min
AI-assisted Silver cleaning of types, duplicates and nulls; MERGE into Gold
65–90 min
dbt models, dependencies and not_null/unique/relationships tests; check order and feature definitions
90–100 min
Break
100–145 min
Read Parquet and implement corresponding PySpark transformations; inspect explain and shuffle; write outputs
145–170 min
Compare engine output semantics; test repeated batches, late updates and deliberately injected errors
170–180 min
Submit SQL/dbt/PySpark outputs and assign a personal change and handover materials
Independently change a metric, feature or quality rule; submit verification, repeated-batch and failure records.
Compare Snowflake and PySpark outputs and explain tests, repeated runs and model inputs.
L04 / 180 MIN
Connect transforms, customer features and the saved model in a prepared sandbox. Run batch scoring, inspect orchestration and logs, test a failure, document and clean up.
AI explains configurations, logs and errors; you verify access, job state and score outputs.
Previous session outputs and a rehearsed sandbox/template; cloud access and credits must be confirmed.
0–20 min
Complete a data-product canvas: consumer, decision, owner, contract, freshness, quality, access and cost cap
20–45 min
Explain S3 → Glue/Spark → Snowflake features → SageMaker scoring; check prepared permissions and resources
45–80 min
Upload data in the prepared sandbox; run Glue and update/check Snowflake Gold and features
80–90 min
Break
90–125 min
Upload the session-two model to SageMaker, check artifacts and image compatibility, run batch transform and save scores; teacher rehearsal required
125–150 min
Use a Step Functions template; inspect CloudWatch failures/late alerts; inject and verify a schema error
150–170 min
Present the pipeline, evaluation and batch scores; explain personal changes, AI review and gaps
170–180 min
Check endpoints/jobs and sandbox cleanup; explain project review and next steps
Submit code, model, evaluation, AWS run/score/failure evidence, personal work and limitations for one review and revision.
Real pipeline/model and scoring evidence must correspond. Missing deployment evidence is marked incomplete.
Session three and homework
Handle new orders, cancellations and late updates without double counting.
Batch comparisons, business keys and update rules.
Sessions three and four
Inject duplicate keys, missing references or schema errors; block bad outputs and repair.
Failure, diagnosis, repair and last-valid-result records.
Homework and final review
Independently modify a metric, feature or quality rule and explain it.
Code differences, expected results and verification.
All sessions
Keep the task, AI draft, decisions, personal changes and independent checks.
At least one justified review of an AI suggestion.
Preparation and each submission
Starter, reference checkpoints, architecture, contract, README and cleanup steps.
Reproducible artifacts and honest readiness notes.
One written review and revision review
Record independent, guided, conceptual and uncovered skills. Teaching window must be confirmed.
Feedback, corrections and evidence-linked gaps.
These topics explain trade-offs; the course does not promise complete implementation of every tool in twelve hours.
Iceberg/Delta, SCD2, partitions, small files and scale costs; complex history and tuning are extension material.
Feature stores, drift, RAG/vector search/text-to-SQL data dependencies; optional Cortex comparison.
Batch/micro-batch/streaming, Snowpipe/Dynamic Tables, Airflow/Dagster, Git/PR/CI and IaC; explain uses, do not build every tool.
Access, masking, row permissions, PII, freshness and costs; optional MWAA and real-time SageMaker endpoints with API Gateway.
Provide business rules, grain and expected results first. Generate small SQL, PySpark or training changes. Keep the draft, reasons for accepting or rejecting advice, personal revisions and independent checks. Running code must also produce correct data.
The course design includes one personal written project review and one revision review. Teaching staff, feedback windows, recordings, support and tool credits must be confirmed before enrolment.
Incomplete work is marked for revision. Distinguish independent work, guided work, conceptual understanding and topics not covered. Templates and synthetic data must be disclosed; do not present coursework as client delivery or employment.
Proposed fee: 899, provisionally AUD. Currency and GST treatment remain unconfirmed. Proposed format: four guided sessions over two weeks. Dates, time zone, teaching staff, class size, refund rules and AI/cloud costs will be confirmed before enrolment.
This is an information page: no enrolment collection, orders or payment. Two weeks is an initial engineering project, not a guarantee of a job, role change or pay increase.
Read variables, loops, functions and file operations. Full programming fundamentals must be prepared before the four sessions.
Yes. Features, labels, leakage checks, simple classification, evaluation and model delivery are core session-two work. Later sessions connect reliable inputs to batch scoring.
You use prepared environments and templates for one batch data product. Real-time endpoints, MWAA and advanced tuning are extensions.
Not yet. Explore the curriculum and completed local reference first. Enrolment opens only after delivery arrangements are confirmed.