Data & Analytics

Turning data into answers you can trust: cleaning and understanding data, spreadsheets used well, SQL and Python for analysis, charts that tell the truth, and the pipelines, warehouses and rules that keep analytical data reliable and lawful.

18 pages 147 glossary terms Data+

How to use this topic

This topic builds on Databases & SQL, on statistics for IT in Math for IT and on Python. The first three pages are about data itself: what analytics is for, data quality and file formats. Pages 4 to 7 treat the spreadsheet as a serious tool, and say when it is not enough. Pages 8 to 11 are about analysis with SQL, Python and statistics, pages 12 to 14 about charts and BI tools, and the last four about pipelines, warehouses, data models and governance.

Most examples use one small, invented data set of the fictional energy supplier Kestrel Energy: customers, contracts, smart meters, daily readings and invoices, with a few data-quality problems planted on purpose. SQL examples run in DuckDB, Python examples in Python and pandas, and spreadsheet formulas are recalculated in LibreOffice Calc; every example shows its exact output.

Every page except the one on spreadsheet habits is marked Data+: it covers material from the CompTIA Data+ exam (DA0-002).

Learning path

  1. 01What Is Data Analytics?What analytics is for and who does it: descriptive, diagnostic, predictive and prescriptive analysis, the roles of analysts, data engineers and data scientists, and the steps from question to decision.BeginnerData+
  2. 02Data Types and Data QualityStructured, semi-structured and unstructured data, measurement scales, the dimensions of data quality, and cleaning data: missing values, duplicates, inconsistent codes and outliers.BeginnerData+
  3. 03CSV, JSON and ParquetThe file formats data travels in: CSV and its many dialects, JSON and JSON Lines, Parquet as a columnar format, and the classic traps with decimal commas, leading zeros, dates and encodings.BeginnerData+
  4. 04Spreadsheets Done RightUsing a spreadsheet as a serious tool: one table per sheet, tidy columns, formatted tables, data validation, named ranges, no merged cells, and keeping inputs, calculations and outputs apart.Beginner
  5. 05Spreadsheet FormulasThe formulas that do the real work: relative and absolute references, IF and IFS, SUMIFS and COUNTIFS, XLOOKUP and INDEX/MATCH, text and date functions, dynamic arrays such as FILTER and UNIQUE, and LET.BeginnerData+
  6. 06Pivot Tables and SummariesSummarizing data without formulas: pivot tables with rows, columns, values and filters, grouping dates, percentages of totals, and when GROUPBY formulas or SQL are the better choice.BeginnerData+
  7. 07When a Spreadsheet Is Not EnoughThe limits and risks of spreadsheets: known spreadsheet disasters, row limits, silent conversions, missing version control and review, and how to move a spreadsheet process to a database, a script or a BI tool.BeginnerData+
  8. 08Analytical SQLSQL for analysis: window functions for running totals and rankings, common table expressions, GROUPING SETS and ROLLUP, date bucketing, and querying CSV and Parquet files directly with DuckDB.IntermediateData+
  9. 09Data Analysis with PythonAnalyzing data in Python: the standard library for small jobs, pandas DataFrames for real work, reading files, selecting, grouping, joining and reshaping, and avoiding the common pandas traps.IntermediateData+
  10. 10Exploratory Data AnalysisGetting to know a dataset before drawing conclusions: summary statistics, distributions, outliers, relationships, correlation versus causation, and Simpson's paradox.IntermediateData+
  11. 11Statistics for AnalystsThe statistics analysts use every week: samples and populations, confidence intervals, hypothesis tests and p-values, A/B tests, and the mistakes that make numbers lie.IntermediateData+
  12. 12Choosing the Right ChartMatching the chart to the question: comparison, trend, distribution, composition and relationship, with bar, line, histogram, scatter and the charts to avoid.BeginnerData+
  13. 13Honest and Accessible VisualizationCharts that tell the truth and that everyone can read: axes and baselines, scales, color for meaning and for color-blind readers, labels instead of legends, and annotations.IntermediateData+
  14. 14Business Intelligence ToolsWhat BI tools such as Power BI, Tableau, Looker, Metabase and Superset do: data sources, semantic layers and measures, dashboards and reports, refresh and access control, and defining KPIs.BeginnerData+
  15. 15Data PipelinesMoving data from source to analysis: ETL and ELT, batch and streaming, incremental and idempotent loads, orchestration with tools such as Airflow and Dagster, and monitoring data freshness.IntermediateData+
  16. 16Warehouses, Lakes and LakehousesWhere analytical data lives: data warehouses, data lakes and lakehouses with open table formats such as Iceberg and Delta Lake, the medallion layers, and transformation with dbt.IntermediateData+
  17. 17Data Modeling for AnalyticsShaping data for analysis: facts and dimensions, star schemas, grain, slowly changing dimensions, and defining metrics once so everyone gets the same number.IntermediateData+
  18. 18Data Governance and PrivacyKeeping data trustworthy and lawful: ownership and stewardship, data catalogs and lineage, data contracts, the GDPR for analytics, pseudonymization and anonymization, and retention.IntermediateData+

Data & Analytics glossary

Short definitions of the terms used in this topic. The full glossary covers every topic.