Wiki
Data wiki.
41 articles on SQL, Python, Spark, Delta Lake, Databricks, modeling, Power BI, DAX and machine learning. 94 code examples; the output next to each Python and SQL example was checked by running it (PySpark, DAX and Databricks SQL, by review).
41 articles
SQL
- SELECT and the logical orderSQL is written starting with SELECT, but it is evaluated starting with FROM. Understanding this avoids half of the mistakes.
- JOINsINNER, LEFT, anti-join and semi-join, and what happens to a row that finds no match.
- GROUP BY, HAVING and aggregationsCOUNT(*) counts rows, COUNT(column) counts values; HAVING filters groups; NULL becomes a group.
- NULL and three-valued logicNULL is not zero or an empty string: it is "unknown". Comparing with it gives unknown, and WHERE only lets true through.
- Window functionsCalculate over a group of rows without collapsing the rows: ranking, running total, previous row.
- CTEs (WITH) and recursionNaming a step of the query makes the SQL readable from top to bottom. With RECURSIVE, it generates sequences and walks hierarchies.
- CASE, conditional aggregation and pivotCASE is SQL's if. Inside a SUM, it turns into one column per category without needing PIVOT.
- DeduplicationRepeated events are the rule in raw data (resends, retries, reprocessing). DISTINCT solves the exact copy; ROW_NUMBER solves "the most recent one for each key".
- Subqueries, IN and EXISTSScalar subquery, IN, EXISTS and correlated. When each one is the right fit and why EXISTS is the safest with NULL.
Python for data
- Python for data: lists, dictionaries and comprehensionsThe basics that show up in every data script: comprehensions, counting by key, stable sorting and None.
- Generators and lazy evaluationA generator produces one item at a time, only when asked. It is the same idea as lazy Spark, on a small scale.
- CSV and JSON with the standard libraryRead CSV with quotes and commas inside fields, convert types carefully and flatten nested JSON, with no dependencies at all.
- Dates, times and time zonesA timestamp without a time zone is ambiguous. Store in UTC, convert when displaying, and mind the difference between a date and an instant.
- SQL from inside Python (sqlite3)An in-memory database to test SQL rules without a server, and ? parameters so you never build SQL from concatenated text.
PySpark
- Lazy evaluation: transformations and actionsIn Spark, filter, select and join only draw a plan. Nothing is read until an action (count, show, write, collect) asks for the result.
- Partitions, repartition and coalesceA partition is the unit of parallelism: one task per partition. Too few leave the cluster idle; too many small ones cost more in scheduling than in work.
- Spark joins: broadcast and shuffleJoining a large table with a small one does not need to move the large one across the network. Knowing which strategy Spark chose is reading the plan.
- UDFs or native functionsA Python UDF takes the row out of the JVM, serializes it, runs it in Python and sends it back. A native function runs inside the optimized plan. Use a UDF only when there is no native one.
- Cache and persistcache() keeps an intermediate result so it is not recomputed. It helps when it is reused; it hurts when it takes up memory for nothing.
Delta Lake
- Delta Lake: ACID transactions on top of filesA Delta table is Parquet plus a transaction log (_delta_log). The log is what gives atomicity, isolation, schema and history.
- Time travel and historyEvery commit is a version. You can read the table as it was, compare versions and restore, as long as the old files exist.
- MERGE (upsert)Update what already exists and insert what is new in a single atomic operation. It is the heart of incremental loading in the silver layer.
- OPTIMIZE, Z-ORDER, clustering and VACUUMMany small files make every read slow. OPTIMIZE compacts, Z-ORDER and liquid clustering bring similar data together, VACUUM cleans up what is left.
Architecture and modeling
- Medallion architecture (bronze, silver, gold)Three layers of increasing quality. Bronze keeps data as it arrived, silver cleans and standardizes it, gold answers business questions.
- ETL and ELTThe difference is where the transformation happens: before loading (ETL) or inside the platform, after loading the raw data (ELT).
- Dimensional modeling (star schema)Facts (what happened, with numbers) in the center; dimensions (who, what, when, where) around them. It is the format BI reads best.
- Changing dimensions (SCD types 1 and 2)A customer changes city. Overwriting (type 1) loses the past; versioning (type 2) keeps each version with its validity period.
Databricks
- Jobs and Workflows: orchestration on DatabricksA job is a graph of tasks with dependencies, a schedule, retries and alerts. The schedule uses Quartz-format cron, with seconds.
- Compute: clusters, job clusters and serverlessAn interactive cluster is for exploring; a job cluster is born and dies with the job. Choosing the compute is the biggest cost lever in Databricks.
- Unity Catalog and governanceOne place to say who can read what, with a three-level name (catalog.schema.table), inherited permissions and automatic lineage.
Quality, CI/CD and cost
- Data qualityRules written as queries, which run on every load and block or set aside whatever violates them. Quality that depends on someone looking is not quality.
- CI/CD for dataNotebooks and SQL are code too: versioned in Git, tested on every change and deployed by a pipeline, never copied by hand to production.
- Pipeline cost optimizationCost is machine × time. Reading less data, using the right compute and not leaving anything on for nothing solves most of the bill.
Power BI and DAX
- Power BI for people who deliver the dataStar schema, Import or DirectQuery, and measures instead of calculated columns. What engineering delivers decides whether the report flies or crawls.
- Row context and filter contextEvery DAX expression runs inside a context. The filter context restricts the rows a measure sees; the row context points at a current row.
- CALCULATE: changing the filter contextCALCULATE evaluates an expression in a modified filter context. A Boolean filter replaces whatever already existed on the same column.
- Time intelligence: date table, YTD and prior yearTime functions depend on a continuous date table, marked as a date table and linked to the fact table. With it, YTD and prior year become a single line.
- SUM or SUMX: when to iterate row by rowSUM aggregates a column; SUMX, AVERAGEX and the like evaluate an expression row by row and aggregate the result. The iterated table sets the grain.
Machine learning
- Evaluating a classifier: confusion matrix, precision and recallHow to count the hits and misses of a binary classifier and why accuracy alone is misleading when the classes are imbalanced.
- Recommendation by cosine similarityMeasuring how much two rating vectors point in the same direction and using that to recommend items that a similar user already liked.
- Gradient descent: how the network learnsThe loop that adjusts a parameter in the direction that reduces the error, shown on a one-weight model, and the effect of the learning rate.
Nothing found. Try another word.