Data and Databases overview

99 topics in 14 lessons, each in one line. Every topic opens the page that teaches it.

After this course you can

  • Design a normalised schema, write the SQL to question it, and explain what a transaction, an isolation level and an index each do.
  • Choose between a relational database, a NoSQL store, a warehouse and a lakehouse for a given workload, and say why.
  • Build the shape of a modern data platform: batch and streaming ingestion, ELT into bronze, silver and gold, Spark jobs that run fast, and the controls that keep the data correct and private.

1Data and tables

2SQL basics

3Designing tables

4SQL for analysis

5Indexes and how a query runs

6Transactions

7NoSQL databases

8Data for analysis

9Warehouses, lakes and lakehouses

10Moving data

11Batch processing with Spark

12Streaming

13Data quality and governance

14From a file of orders to a dashboardWorked case

Sources and credit

42 sources

The chapters and drawings are written by NeuroqAI from these sources. Each lesson lists its own at the end of its chapter, and figures on the pages are taken from them or computed from them.

PostgreSQL documentation10 sourcesData definitionTables, keys and constraintsLessons 1, 3Data typesNumbers, text, dates and why the type mattersLessons 1, 3QueriesSELECT, joins, grouping and the order they run inLesson 2Aggregate functionscount, sum, avg and how they treat NULLLesson 2Window functionsRunning totals and ranks without losing rowsLesson 4WITH queries (common table expressions)Naming a step of a queryLesson 4IndexesB-trees, multicolumn and index-only scansLesson 5Using EXPLAINReading the plan a query will followLesson 5Transaction isolationThe levels and the anomalies each one allowsLesson 6Write-ahead loggingHow a database survives a crash mid-writeLesson 6
Microsoft6 sourcesExam DP-900 study guide: Microsoft Azure Data FundamentalsThe skills measured for the data fundamentals examLessons 1, 2, 3, 7, 8SELECT (Transact-SQL)The logical processing order of a SELECTLesson 2Consistency levels in Azure Cosmos DBFive levels from strong to eventualLesson 7Access tiers for blob dataHot, cool, cold and archive, and their minimum staysLesson 7Model relationships in Power BI DesktopCardinality and filter directionLesson 8Azure Event HubsAzure's event streaming serviceLesson 12
Databricks5 sourcesMERGE INTOInsert, update and delete in one statementLesson 4What is a data lakehouse?Warehouse features on open files in a lakeLesson 9What is the medallion lakehouse architecture?Bronze, silver and goldLessons 10, 14What is Auto Loader?Loading only the new files, with schema changes handledLesson 10What is Unity Catalog?Privileges, lineage, row filters and column masksLesson 13
Amazon Web Services4 sourcesCore components of Amazon DynamoDBTables, items, partition keys and sort keysLesson 7AWS Certified Data Engineer Associate (DEA-C01) exam guideThe domains of the AWS data engineering examLessons 9, 10, 12, 13What is Amazon Kinesis Data Streams?Shards, their limits and retentionLesson 12AWS Glue Data CatalogFinding and describing the data in a lakeLesson 13
Apache Spark3 sourcesCluster mode overviewDriver, executors, jobs, stages and tasksLesson 11RDD programming guideLazy transformations and the actions that run themLesson 11Performance tuning (Spark SQL)Shuffle partitions, broadcast joins and adaptive executionLesson 11
Standards, research and other publishers14 sourcesA Relational Model of Data for Large Shared Data Banks (1970) · E. F. Codd, Communications of the ACMThe paper that started relational databasesLessons 1, 3The Floating-Point Guide · floating-point-gui.deWhy 0.1 + 0.2 is not 0.3, and what to use for moneyLessons 1, 3Modern B-Tree Techniques (2011) · Goetz GraefeEverything about the structure under most indexesLesson 5Transaction isolation levels (InnoDB) · Oracle MySQLMySQL's default of REPEATABLE READLesson 6Delta Lake documentation · Delta LakeTransactions on files in object storageLessons 6, 9Parquet file format · Apache ParquetRow groups, column chunks and statisticsLessons 8, 14Dimensional modeling techniques · Kimball GroupStar schemas, grain, facts and slowly changing dimensionsLessons 8, 14Key concepts and architecture · SnowflakeStorage, compute and services in separate layersLesson 9BigQuery overview · Google CloudA data warehouse that separates storage from the compute that queries itLesson 9Apache Airflow documentation · Apache AirflowPipelines as code: tasks, dependencies and retriesLesson 10Apache Kafka documentation · Apache KafkaTopics, partitions, consumer groups and offsetsLesson 12What is Pub/Sub? · Google CloudGoogle Cloud's messaging and streaming serviceLesson 12How Airbnb Achieved Metric Consistency at Scale · The Airbnb Tech BlogMinerva: every metric defined once, in code, and used everywhereLesson 14Upsert into a table using merge · Delta Lake documentationMERGE: the statement that makes a load safe to re-runLesson 14