SQL, Data Modeling & Analytics Tables
Build trustworthy analytical models and query them efficiently.
Learning objectives
By the end of this lesson, you should be able to:
- Explain the key concepts and when they are useful.
- Apply the concepts to a realistic engineering scenario.
- Identify common failure modes and choose a practical mitigation.
- Complete the assignment and explain your design decisions.
Core concepts
Operational schemas optimize writes and transactional correctness; analytical models optimize aggregation and understanding. A star schema typically has a fact table for measurable events and dimensions for descriptive context. Grain must be explicit: one row per order, order line, event, or customer-day.
Worked example
Use explicit columns rather than SELECT * in production transformations. Handle NULL semantics, time zones, late-arriving records, duplicate keys, and slowly changing attributes deliberately. Validate uniqueness and referential integrity after transformations.
Engineering principles
- Make assumptions explicit. Document expected inputs, outputs, ownership, and failure behavior.
- Validate at boundaries. Treat user input, files, messages, and third-party responses as untrusted.
- Design for failure. Decide what happens when dependencies time out, data is malformed, or a process is retried.
- Prefer observable behavior. Log meaningful outcomes and expose enough diagnostics to debug without leaking secrets.
- Test the invariant. A test should prove a property that must remain true, not merely repeat the implementation.
Practical assignment
Model order revenue by day, product, and customer segment. State the grain of each table and write a query for monthly revenue and order count.
Suggested solution and reasoning
Create a fact_order table with one row per order and dimensions for date, product/customer where appropriate. Aggregate using a documented time zone and test that sum of line items reconciles to order totals.
Use this as a reference design, not the only possible answer. A strong submission states assumptions, includes failure cases, and demonstrates how correctness will be tested.
Knowledge check
- What problem does this design solve, and what simpler alternative did you consider?
- Which input or dependency is most likely to fail?
- What invariant must remain true after a retry?
- How would you test the unhappy path?
Completion checklist
- I can explain the concepts without reading the lesson.
- I completed the practical assignment.
- I tested at least one failure scenario.
- I documented trade-offs and next steps.