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
- SELECT and WHEREA query describes the result; the database decides how
- JoinsFour joins differ only in which unmatched rows they keep
- GroupingGROUP BY collapses rows into one row per group
- Logical query orderClauses run in a different order from how they are written
- NULLA comparison with an unknown is itself unknown
3Designing tables
4SQL for analysis
- SubqueriesA query can feed another query
- Common table expressionsName each step, and the query reads top to bottom
- Window functionsCompute over a group of rows without collapsing them
- Running totalsA frame slides down the rows, one row at a time
- RankingThree ranking functions differ only in how they treat ties
- ViewsA view stores the question; a materialised view stores the answer
- MERGEUpdate what matches, insert what is new, in one statement
5Indexes and how a query runs
- Full table scansWithout an index, one row costs the whole table
- B-tree indexesEach page of the tree narrows the search by hundreds
- Seek or scanAn index only wins when the query wants a small share of the table
- Query plansThe plan shows what the database actually did
- Composite indexesColumn order decides which queries an index can serve
- Index costsEvery index speeds some reads and slows every write
- PartitioningSplit a table by a key, and queries skip what they cannot need
6Transactions
- TransactionsCOMMIT is the moment a change becomes real
- Write-ahead logWrite a small log record first; the data can follow later
- Lost updatesTwo transactions that read, then write, can overwrite each other
- Isolation levelsEach stronger level rules out one more anomaly
- MVCCReaders see a snapshot, so they never wait for writers
- DeadlocksTwo transactions each wait for the other, so one must die
7NoSQL databases
- Scaling outPast one server, data must be split, and joins get expensive
- Data modelsThe same order, four ways, each cheap for one kind of question
- Partition keysA key with many evenly used values spreads the load; a busy one does not
- Access patternsDesign keys from the questions, so each answer is one read
- ReplicationReads see the latest write when read and write sets must overlap
- Consistency levelsConsistency is a dial with useful stops between the ends
- Graph databasesStored links make each hop cheap, however large the graph
- NoSQL servicesEvery model is a managed service on every cloud
8Data for analysis
- OLTP and OLAPTransactions touch a few whole rows; analysis reads a few columns
- Columnar storageA column store keeps each column together, so queries read less
- Bytes readFor one sum over a million orders, the column store read 161× fewer bytes
- Column compressionColumns compress well because the values in a column are alike
- Star schemaNumbers go in one fact table; descriptions go in dimensions around it
- Facts and dimensionsQuestions join facts to dimensions, group, and add up the measures
- Slowly changing dimensionsType 2 keeps history by adding a row when an attribute changes
- Power BI data modelsRelationships carry filters from dimensions to facts; measures sum
- Data visualisationsPick the visual from the question the viewer is asking
9Warehouses, lakes and lakehouses
- Warehouse, lake, lakehouseA lakehouse adds a transaction log to cheap, open lake files
- Storage and computeSeparate compute clusters of any size can share one copy of the data
- Delta Lake and IcebergThe table is whatever its log of commits says it is now
- Snowflake architectureSnowflake splits into shared services, many warehouses, one storage
- Time TravelKept old versions let you read, restore or clone the past
- Microsoft FabricFabric puts warehouse, Spark and Power BI on one lake, OneLake
- Unity CatalogEvery Databricks table has a three-part name and one place of control
- Analytics servicesEvery cloud offers object storage, a table catalogue and query engines
10Moving data
- ETL and ELTELT loads raw data first and transforms it where it lives
- Medallion architectureBronze keeps data as it came, silver cleans it, gold shapes it
- Incremental loadsLoad only what changed since the last run, tracked by a watermark
- Idempotent loadsA step that gives the same result when re-run makes retries safe
- OrchestrationA pipeline runs as a graph: each task starts when its inputs succeed
- dbtdbt builds SQL models in dependency order and tests what they produce
- File ingestionIngestion tools remember which files they have loaded
- Pipeline servicesEach platform has connectors, transform engines and an orchestrator
11Batch processing with Spark
- Spark architectureA driver plans the work; executors run one task per partition
- Lazy evaluationTransformations only describe the result; an action runs the plan
- Shuffles and stagesGrouping and joining move rows between machines, splitting stages
- Broadcast joinsCopy a small table to every executor and the large one never moves
- Data skewOne oversized partition makes the whole stage wait
- The Spark UIA max far above the median means skew; spill means memory ran out
- Adaptive Query ExecutionAQE re-plans the query at each shuffle using real sizes
- Databricks computeChoose compute by who is waiting; let the table layout cut reading
12Streaming
- Batch and streamingStreams process each event moments after it happens
- Event streamsPartitions split a topic; each consumer group reads all of it
- Offsets and replayEach consumer group remembers its position, so the log can be replayed
- WindowsThe window type decides which events are counted together
- Late data and watermarksA watermark sets how long to wait for late events
- Exactly-once processingExactly-once needs a replayable source and a sink that tolerates repeats
- Change data captureCDC turns every committed change in a database into an event
- Streaming servicesA streaming platform carries, processes, stores and acts on events
13Data quality and governance
- Data qualityQuality is measured per dimension, with a rule and a threshold
- Quality checksEach rule needs a decision: record it, drop the row, or stop
- Data lineageLineage traces each column back to its source and forward to its users
- Data cataloguesA catalogue says what data exists, what it means and who owns it
- Access controlGrant the least access to groups, on the object and its containers
- Row and column securityOne table can return different rows and values to each user
- EncryptionEncrypt in transit and at rest; whoever holds the key holds the data
- Privacy and retentionA deleted row survives in history and copies until those are purged
14From a file of orders to a dashboardWorked case
- The problemA number everyone trusts is the real work
- The estimateCount the rows, then measure the bytes
- The first designAppend each night’s file to one table, and point the dashboard at it
- The causeThree failures, and the worst is a number that looks fine
- The fixBronze keeps it, silver makes it right, gold makes it fast and stable
- The trade-offMore to run, in exchange for numbers that agree
- The finished pipelineOrdinary steps, done every time, with checks that can stop it
- A real caseAt Airbnb, each metric is defined once and shared
Sources and credit
42 sourcesThe 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.