Analytics and BI¶

The part of the data platform people actually look at: modelled tables, metrics with one agreed definition, and dashboards that do not take a minute to load. The work sits between the pipeline and the decision, and it is mostly about shape (a star schema), trust (tests and lineage) and cost (what each dashboard refresh scans).

flowchart LR
  RAW[(Raw and cleaned data)] --> MODEL[Modelled layer<br/>dbt, SQL, star schemas]
  MODEL --> SEM[Semantic layer<br/>metrics defined once]
  SEM --> BI[BI tools<br/>Power BI, Looker, Tableau, Quick Sight]
  SEM --> NB[Notebooks and ad hoc SQL]
  TEST{{Tests, lineage, cost limits}} -.guard.- MODEL
  TEST -.guard.- BI

From raw tables to a dashboard. The modelled layer and a shared set of metric definitions are where analytics engineering lives; tests and cost controls keep the numbers right and the bill bounded.

Learn¶

• Data modeling for analytics - star schemas, grain, slowly changing dimensions
• Slowly changing dimensions - Type 1 vs Type 2, and keeping history honest
• Analytics engineering and dbt - transformation SQL with tests, code review and lineage
• SQL joins explained - join types and fan-out, the usual cause of a wrong total
• Window functions explained - running totals, rankings, latest row per key
• OLTP vs OLAP - why analytics runs on a different store
• Warehouses, lakes, and lakehouses - where the modelled tables live
• Data quality and lineage - catching a wrong number before a dashboard shows it
• Warehouse cost control - scan and compute cost, with hard limits
• Feature stores for data engineers - the same metrics, served to models
• Day one: your first SQL query - the on-ramp if you have not written SQL yet

Compare¶

• BI tools - Power BI, Looker, Tableau, Quick Sight, Superset
• Data warehouses - Redshift, Fabric, BigQuery, Snowflake, Databricks SQL
• ETL and orchestration - dbt, Dagster, Airflow and managed options

Reference¶

• Architecture pattern: semantic layer - metrics defined once for BI, notebooks and AI
• Architecture pattern: medallion - bronze, silver and gold, and what belongs in each
• Architecture pattern: reverse ETL - modelled data back into operational tools
• Troubleshooting: slow SQL queries - plans, indexes, pruning
• Interview: analytics engineer - dbt layering, incremental models, tests, SCDs and metrics
• Interview: SQL - the query patterns analytics roles are tested on

Build¶

• Design a star schema - from business questions to grain, facts and dimensions
• Build a dbt project - a tested star schema with snapshots, contracts and CI
• Orchestrate a pipeline with Dagster - partitions, a quality check and safe reruns

Certify¶

• Power BI Data Analyst (PL-300)
• Azure Fabric Analytics Engineer (DP-600)
• Snowflake SnowPro Core (COF-C03)
• Snowflake SnowPro Advanced Architect
• Databricks Data Engineer Associate - SQL warehouses and dashboards

Roadmap¶

The ordered paths are the Data Analyst roadmap and the Analytics Engineer roadmap.

Related topics: Data engineering for how data reaches the modelled layer, and Data governance for who may see which column.