Meta tags:
description=Star schemas and SCDs get you started. Here s what actually breaks once a warehouse hits the real world — snowflake schemas, fact table types, junk dimensions, bridge tables, and why not every table needs a measure. Tagged with dataengineering, datamodeling, sql, tutorial.;
keywords=dataengineering, datamodeling, sql, tutorial, software, coding, development, engineering, inclusive, community;

Headings (most frequently used words):

dimensions, the, fact, when, dimension, tables, data, schema, needs, same, many, one, modeling, concepts, nobody, mentions, after, star, 101, dev, community, its, own, snowflake, not, every, table, looks, there, nothing, to, measure, factless, too, tiny, junk, metadata, that, doesn, need, degenerate, values, bridge, keeping, speaking, language, conformed, step, further, vault, briefly, actual, lesson, here, top, comments, more, from, rahman,

Text of the page (most frequently used words):
the (65), and (38), table (31), one (29), you (28), fact (25), that (23), fullscreen (16), mode (16), for (15), dimension (15), with (12), dev (12), #schema (10), same (10), row (10), data (9), star (9), your (8), more (8), this (8), just (8), #tables (8), exit (8), enter (8), not (8), 2025 (8), sql (7), from (7), have (7), many (7), three (7), when (7), every (7), share (6), which (6), shape (6), instead (6), dimensions (6), per (6), order (6), nobody (5), here (5), modeling (5), than (5), own (5), only (5), desks (5), community (4), about (4), are (4), post (4), but (4), what (4), junk (4), column (4), event (4), number (4), into (4), like (4), between (4), attributes (4), build (4), furniture (4), deal (4), 5001 (4), count (4), can (4), its (4), true (4), 201 (4), snowflake (4), create (3), account (3), log (3), where (3), their (3), built (3), software (3), use (3), code (3), career (3), interview (3), problem (3), tutorial (3), rahman (3), building (3), people (3), further (3), reporting (3), abuse (3), comments (3), want (3), will (3), store (3), these (3), things (3), bridge (3), real (3), snapshot (3), everything (3), keys (3), each (3), did (3), name (3), once (3), warehouse (3), layer (3), say (3), last (3), rep (3), sales (3), hold (3), gets (3), needs (3), product (3), doesn (3), need (3), was (3), get (3), there (3), change (3), null (3), day (3), balance (3), month (3), product_key (3), most (3), two (3), flat (3), desk (3), dim_product (3), mentions (3), category (3), place (2), made (2), 2026 (2), other (2), source (2), conduct (2), postgres (2), database (2), help (2), engineering (2), isn (2), dataengineering (2), webassembly (2), sep (2), engineer (2), work (2), hide (2), comment (2), still (2), via (2), report (2), looking (2), all (2), time (2), overkill (2), really (2), world (2), attached (2), model (2), first (2), ever (2), might (2), vault (2), schemas (2), splits (2), customer (2), hubs (2), know (2), won (2), see (2), system (2), conformed (2), make (2), team (2), version (2), skip (2), finance (2), dim_customer (2), dim_date (2), also (2), fact_sales (2), revenue (2), add (2), multiple (2), students (2), credit_weight (2), rep_key (2), start (2), reps (2), credit (2), key (2), columns (2), without (2), degenerate (2), because (2), 20250915 (2), date_key (2), has (2), way (2), credit_card (2), false (2), them (2), shows (2), orders (2), used (2), correct (2), five (2), tiny (2), measure (2), events (2), factless (2), happened (2), happen (2), packed (2), shipped (2), anything (2), transaction (2), think (2), basic (2), now (2), query (2), join (2), neither (2), wins (2), genuinely (2), category_name (2), category_key (2), oak (2), product_name (2), department (2), next (2), enough (2), concepts (2), after (2), 101 (2), copy (2), link (2), search (2), coders, stay, date, grow, careers, love, 2016, ruby, rails, powers, inclusive, communities, open, forem, terms, privacy, policy, mlh, shop, free, contact, showcase, organization, accounts, advertise, education, tracks, videos, challenges, home, space, discuss, keep, development, manage, warns, solving, classic, gaps, islands, modern, approaches, programming, why, put, postgresql, fix, prep, webdev, joined, infocepts, developer, currently, datacurlew, master, production, level, follow, actions, may, consider, blocking, person, confirm, child, well, sure, become, hidden, visible, permalink, dismiss, preview, submit, templates, let, quickly, answer, faqs, snippets, template, trusted, user, personal, subscribe, top, had, reach, job, broke, none, everywhere, boolean, relationship, direction, skill, memorizing, seven, patterns, recognizing, reaching, forcing, learned, actual, lesson, land, somewhere, heavy, audit, compliance, requirements, banking, insurance, healthcare, run, types, business, ids, relationships, descriptive, timestamp, rigid, verbose, makes, fully, traceable, probably, early, knowing, means, lost, legacy, satellites, links, step, briefly, shared, less, technique, rule, reference, those, quietly, slightly, different, back, marketing, mismatch, deeper, someone, eventually, ask, customers, bought, filed, support, ticket, quarter, question, works, cleanly, both, point, definitions, fact_support_tickets, keeping, speaking, language, multiply, totals, correctly, across, even, though, pattern, handles, patients, diagnoses, majors, anywhere, refuses, deal_id, bridge_deal_reps, sits, carries, weighting, factor, numbers, don, double, triple, crm, sharing, expects, breaking, moment, fourth, added, fact_deals, values, attribute, lives, separate, would, pure, overhead, 58291, ord, 9001, customer_key, order_number, order_line_id, context, identifier, rather, directly, dim_order, metadata, holds, foreign, four, elegant, textbook, sense, deliberate, tradeoff, purity, cleaner, paypal, is_rush_order, payment_method, is_gift_wrapped, attributes_key, dim_order_attributes, bundles, combination, handful, small, yes, short, gift, wrapped, payment, method, rush, giving, technically, practically, annoying, extra, cluttering, too, expect, eligibility, promotions, applied, security, check, ins, catch, yourself, adding, fake, feels, proper, usually, sign, fine, 1204, class_session_key, student_key, fact_attendance, track, attended, class, sessions, amount, quantity, nothing, status, filled, milestones, natural, pipeline, fulfillment, instantly, how, stuck, scanning, 5002, days_to_deliver, delivered_date, shipped_date, packed_date, placed_date, order_id, fact_order_fulfillment, tracking, something, defined, end, update, moves, through, stages, going, placed, delivered, accumulating, generate, september, 15th, photograph, state, our, tells, changes, states, units_on_hand, warehouse_key, snapshot_date, fact_inventory_daily, captures, measurement, regular, interval, whether, inventory, taken, night, midnight, periodic, refunds, called, common, type, take, looks, rename, touches, cost, actually, independently, often, speed, matters, theoretical, tidiness, teams, default, department_key, dim_category, snowflaked, renamed, updating, string, live, pine, 202, department_name, some, repetition, including, repeated, single, plain, words, running, example, online, then, split, clean, asks, realize, stores, balances, bug, they, mentioned, intro, tutorials, stop, done, working, finished, learning, datamodeling, posted, mastodon, facebook, linkedin, copied, clipboard, pick, gem, boost, save, jump, fire, raised, hands, exploding, head, unicorn, reaction, close, powered, algolia, navigation, menu, content,

Text of the page (random words):
the data modeling concepts nobody mentions after star schema 101 dev community skip to content navigation menu search powered by algolia search log in create account dev community close add reaction like unicorn exploding head raised hands fire jump to comments save boost pick as gem more copy link copy link copied to clipboard share to x share to linkedin share to facebook share to mastodon share post via report abuse rahman posted on sep 16 the data modeling concepts nobody mentions after star schema 101 dataengineering datamodeling sql tutorial most data modeling tutorials stop at the same place here s a fact table here s a dimension table join them done and that s genuinely enough to get a warehouse working it s also enough to make you think you re finished learning then two things happen in the same month a sales deal gets credit split between three reps instead of one and your fact table built for one rep per deal has no clean way to hold that and finance asks for account balance on the last day of every month and you realize your fact table only stores balance change events not balances neither of these is a bug they re just the next layer of modeling that nobody mentioned in the intro to star schemas post here s that next layer in plain words with a running example an online furniture store when a dimension needs its own dimensions snowflake schema in a basic star schema dim_product might hold everything about a product in one flat row including its category name and department name repeated for every single product in that category dim_product star schema flat some repetition product_key product_name category_name department_name 201 oak desk desks furniture 202 pine desk desks furniture enter fullscreen mode exit fullscreen mode if desks ever gets renamed to work desks you re updating that string in every row that mentions it a snowflake schema splits the dimension further so category and department live in their own tables dim_product snowflaked product_key product_name category_key 201 oak desk 55 dim_category category_key category_name department_key 55 desks 9 enter fullscreen mode exit fullscreen mode now a rename touches one row the cost is that a query which used to need one join now needs two or three neither version is correct a snowflake schema wins when a dimension s attributes actually change independently and often a flat star schema wins when the query speed matters more than that theoretical tidiness most teams default to star and only snowflake the one or two dimensions that genuinely need it not every fact table looks the same the sales and refunds fact table from a basic tutorial one row per event is called a transaction fact table it s the most common type but it s not the only shape a fact table can take a periodic snapshot fact table captures a measurement at a regular interval whether or not anything happened think of a warehouse s inventory count taken every night at midnight fact_inventory_daily snapshot_date product_key warehouse_key units_on_hand 2025 09 14 201 3 42 2025 09 15 201 3 39 enter fullscreen mode exit fullscreen mode nobody did anything to generate the september 15th row it s just a photograph of the state of things that day this is the shape you want for what was our balance on the last day of the month because a transaction table only tells you about changes not states an accumulating snapshot fact table is for tracking something with a defined start and end where you update the same row as it moves through stages an order going from placed to packed to shipped to delivered fact_order_fulfillment order_id placed_date packed_date shipped_date delivered_date days_to_deliver 5001 2025 09 01 2025 09 01 2025 09 02 2025 09 05 4 5002 2025 09 03 2025 09 03 null null null enter fullscreen mode exit fullscreen mode instead of one row per status change there s one row per order and columns get filled in as milestones happen this is the natural shape for pipeline and fulfillment reporting you can instantly see how many orders are stuck between packed and shipped without scanning an event log when there s nothing to measure factless fact tables a fact table doesn t have to have a number in it say you want to track which students attended which class sessions there s no amount no quantity just the fact that an event happened fact_attendance student_key class_session_key date_key 77 1204 20250915 enter fullscreen mode exit fullscreen mode you get your measure from count not from a column this shows up more than people expect eligibility events promotions applied security check ins if you catch yourself adding a fake count 1 column just so the table feels like a proper fact table that s usually a sign it s a factless fact table and that s fine too many tiny dimensions junk dimensions say your orders have a handful of small yes no or short code attributes is it gift wrapped what payment method was used was it a rush order giving each one its own dimension table is technically correct and practically annoying five tiny tables five extra keys cluttering the fact table a junk dimension bundles them into one table with one row per real combination that shows up in your data dim_order_attributes junk dimension attributes_key is_gift_wrapped payment_method is_rush_order 1 true credit_card false 2 false paypal true 3 true credit_card true enter fullscreen mode exit fullscreen mode the fact table holds one foreign key instead of three or four it s not elegant in the textbook sense it s a deliberate tradeoff of purity for a cleaner schema metadata that doesn t need a dimension degenerate dimensions every order has an order number it s not really context the way a customer or product is it doesn t have its own attributes it s just an identifier from the source system rather than build a dim_order table with one column in it you store the order number directly in the fact table fact_sales order_line_id order_number date_key customer_key revenue 9001 ord 58291 20250915 55 39 98 enter fullscreen mode exit fullscreen mode that s a degenerate dimension a dimension like attribute that lives in the fact table because building a separate table for it would be pure overhead when one fact needs many dimension values bridge tables here s the many to many problem from the start of this post a deal in your crm can have three sales reps sharing credit for it a fact table row expects one key per dimension so fact_deals can t just hold three rep_key columns without breaking the model the moment a fourth rep gets added a bridge table sits between the fact and the dimension and carries a weighting factor so the numbers don t double or triple count bridge_deal_reps deal_id rep_key credit_weight 5001 12 0 5 5001 19 0 3 5001 24 0 2 enter fullscreen mode exit fullscreen mode multiply revenue by credit_weight when reporting by rep and the totals still add up correctly across the team even though three people are attached to one deal this same pattern handles patients with multiple diagnoses students with multiple majors anywhere the real world refuses to be one to one keeping fact tables speaking the same language conformed dimensions once you have more than one fact table fact_sales and fact_support_tickets say someone will eventually ask which customers bought furniture and also filed a support ticket last quarter that question only works cleanly if both fact tables point to the same dim_customer and dim_date tables with the same keys and the same definitions that shared table is a conformed dimension it s less a technique and more a rule build dim_date once build dim_customer once and make every fact table in the warehouse reference those same tables instead of each team quietly building their own slightly different version skip this and you re back to the finance vs marketing mismatch problem just one layer deeper one step further data vault briefly if you ever land somewhere with heavy audit or compliance requirements banking insurance healthcare you might run into data vault modeling instead of star schemas it splits everything into three table types hubs just the business keys like customer ids links relationships between hubs and satellites the descriptive attributes each with a timestamp it s more rigid and more verbose than a star schema but it makes what did we know and when did we know it fully traceable you probably won t build one as an early career engineer but knowing the name and the shape means you won t be lost the first time you see one in a legacy system the actual lesson here none of these are things to use everywhere all the time a junk dimension on a table with one boolean column is overkill a bridge table where the relationship really is one to one is overkill in the other direction the skill isn t memorizing all seven patterns it s recognizing which real world shape you re looking at a snapshot a many to many an event with no number attached and reaching for the model built for that shape instead of forcing everything into the one you learned first which one of these have you had to reach for on the job and what broke that made you go looking for it top comments 0 subscribe personal trusted user create template templates let you quickly answer faqs or store snippets for re use submit preview dismiss code of conduct report abuse are you sure you want to hide this comment it will become hidden in your post but will still be visible via the comment s permalink hide child comments as well confirm for further actions you may consider blocking this person and or reporting abuse rahman follow developer currently building datacurlew to help people master production level sql work data engineer at infocepts joined sep 2 2026 more from rahman why i put postgresql in webassembly to fix sql interview prep postgres sql webassembly webdev solving the classic sql gaps and islands problem 3 modern approaches sql database tutorial programming nobody warns you the data engineering interview isn t about data engineering dataengineering interview career sql dev community a space to discuss and keep up software development and manage your software career home dev challenges dev videos dev education tracks dev help advertise on dev organization accounts dev showcase about contact free postgres database dev shop mlh code of conduct privacy policy terms of use built on forem the open source software that powers dev and other inclusive communities made with love and ruby on rails dev community 2016 2026 we re a place where coders share stay up to date and grow their careers log in create account