Memory hook: Question, grain, query, chart.
Must remember
- Define the business question and row grain before writing SQL. Joins can multiply rows; aggregate at the intended grain and validate totals against source data.
- Use SELECT, WHERE, JOIN, GROUP BY and window functions deliberately. Filter partition columns and select required fields to reduce scanned data; inspect the query plan and bytes estimate.
- Jupyter or Colab Enterprise notebooks combine code, explanation and charts for exploration. Keep credentials out of cells, pin dependencies and separate experiments from scheduled production work.
- Looker uses a governed semantic model expressed through LookML; dimensions describe attributes, measures define aggregates and Explores expose joins. Correct join relationships help avoid double counting.
- Looker Studio provides accessible dashboards and connectors; Looker suits governed reusable metrics and embedded enterprise analytics. Neither fixes incorrect source definitions.
- Choose charts by question: lines for change over time, bars for category comparisons, scatterplots for relationships. Label units and time zones; share dashboards only with appropriate data permissions.
Review details
SQL recall: WHERE filters input rows, GROUP BY sets aggregation grain, HAVING filters aggregate results, and window functions calculate across related rows without collapsing every row. COUNT(*) counts rows; COUNT(column) skips nulls. A left join preserves unmatched left rows, but a right-side filter in WHERE can unintentionally remove them. Check cardinality before blaming the chart for inflated revenue.
Read-only pattern: SELECT department, COUNT(*) AS n FROM employees GROUP BY department HAVING COUNT(*) > 5. Explain which records are grouped and why the aggregate condition belongs in HAVING. In LookML, views/dimensions/measures define reusable structure; an Explore exposes relationships. Correct join declarations and distinct primary keys matter for trustworthy aggregates.
Choose under exam pressure
| Requirement | Choice and reason |
|---|---|
| Different teams calculate revenue differently | A governed shared metric definition in Looker. |
| Quick visual report from supported sources | Looker Studio with appropriate connector and sharing controls. |
Traps
- A dashboard filter is not a substitute for enforced row access.
- A visually convincing chart can still contain duplicated join totals.
Active recall
1. Why establish grain first?
To know what one row represents and avoid invalid joins or aggregations.
2. What reduces BigQuery scan cost?
Project needed columns and prune partitions before scanning unnecessary data.
3. What is a LookML measure?
A reusable aggregate such as sum or count, evaluated within the model.
4. How should notebook findings become production?
Extract tested, versioned transformations into an automated pipeline.
5. What should a dashboard answer?
A specific business question with clear measures and actionable context.