Foundations14 lessons · about 6 hoursHigh complexity

Data and Databases

How data is stored, queried, moved and governed, from one table to a lakehouse. Thirteen lessons go from tables and SQL through table design, indexes and transactions, to NoSQL, column stores, lakehouses, pipelines, Spark and streams, and end with how a company keeps its data trustworthy. Taught to the depth of the AWS Data Engineer, Databricks and SnowPro exams; Azure DP-900 sits comfortably inside it. A worked case at the end puts the whole course to work on one realistic problem.

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.

Lessons

14 lessons
1 Data and tablesKinds of data, tables, keys, relationships and constraints 25 min 2 SQL basicsSELECT, WHERE, joins, GROUP BY, NULLs and the order a query runs in 25 min 3 Designing tablesRedundancy, normalisation, the normal forms and denormalising on purpose 25 min 4 SQL for analysisSubqueries, CTEs, window functions, views and MERGE 25 min 5 Indexes and how a query runsB-trees, query plans, seeks and scans, composite indexes, partitioning 25 min 6 TransactionsACID, the write-ahead log, isolation levels, MVCC, locks and deadlocks 25 min 7 NoSQL databasesKey-value, document, wide-column and graph, partition keys, replication and consistency 25 min 8 Data for analysisOLTP and OLAP, column storage, compression, star schemas, slowly changing dimensions 25 min 9 Warehouses, lakes and lakehousesParquet, Delta Lake and Iceberg, separate storage and compute, Snowflake, Fabric and Databricks 25 min 10 Moving dataBatch loads, incremental loads, dbt, Airflow, idempotent jobs, medallion layers 25 min 11 Batch processing with SparkDriver and executors, partitions, lazy evaluation, shuffles, joins, skew and spill 25 min 12 StreamingEvents, Kafka topics and partitions, consumer groups, windows, exactly-once, change data capture 25 min 13 Data quality and governanceQuality checks, lineage, catalogues, access control, masking, privacy and retention 25 min 14 From a file of orders to a dashboardOne nightly CSV taken to a trusted dashboard: broken three ways, rebuilt in layers, and set beside Airbnb’s Minerva Worked case25 min

Certifications

4 exams

When you have finished the lessons, take the exam course for the certification you want. This course teaches the subject to the depth of the hardest exam below; each exam course adds only what its exam asks on top: the vendor's own names, services and limits, and how the questions are built.

DP-900 Microsoft Azure Data FundamentalsMicrosoft · Foundational · Moderate Exam course coming
DEA-C01 AWS Certified Data EngineerAWS · Associate · High Exam course coming
Databricks Databricks Data Engineer AssociateDatabricks · Associate · Standard Exam course coming
COF-C03 SnowPro CoreSnowflake · Associate · Standard Exam course coming

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