{"id":221,"date":"2026-03-03T15:25:33","date_gmt":"2026-03-03T10:25:33","guid":{"rendered":"https:\/\/gigz.pk\/python\/?post_type=lesson&#038;p=221"},"modified":"2026-03-23T21:45:46","modified_gmt":"2026-03-23T16:45:46","slug":"fact-and-dimension-tables","status":"publish","type":"lesson","link":"https:\/\/gigz.pk\/python\/lesson\/fact-and-dimension-tables\/","title":{"rendered":"Fact and Dimension Tables"},"content":{"rendered":"\n<p>Fact and Dimension Tables are the core components of dimensional modeling in a Data Warehouse. They are commonly used in Star Schema and Snowflake Schema designs.<\/p>\n\n\n\n<p>Understanding these two tables is very important in Data Engineering and Business Intelligence.<\/p>\n\n\n\n<h1 class=\"wp-block-heading\">What is a Fact Table?<\/h1>\n\n\n\n<p>A Fact Table stores quantitative (numeric) data related to business events.<\/p>\n\n\n\n<p>It contains measurable values and foreign keys that connect to dimension tables.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Key Characteristics of Fact Table<\/h2>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Contains numeric metrics (sales_amount, quantity, profit)<\/li>\n\n\n\n<li>Contains foreign keys<\/li>\n\n\n\n<li>Large in size<\/li>\n\n\n\n<li>Grows continuously over time<\/li>\n\n\n\n<li>Used for calculations and aggregations<\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\">Example: Sales Fact Table<\/h2>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-fixed-layout\"><thead><tr><th>sale_id<\/th><th>customer_id<\/th><th>product_id<\/th><th>date_id<\/th><th>region_id<\/th><th>quantity<\/th><th>amount<\/th><\/tr><\/thead><\/table><\/figure>\n\n\n\n<p>Here:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>quantity and amount are facts (measures)<\/li>\n\n\n\n<li>customer_id, product_id, date_id, region_id are foreign keys<\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\">Types of Fact Tables<\/h2>\n\n\n\n<ol class=\"wp-block-list\">\n<li>Transaction Fact Table<br>Stores individual transactions (e.g., each sale).<\/li>\n\n\n\n<li>Snapshot Fact Table<br>Stores data at a specific time (e.g., daily inventory).<\/li>\n\n\n\n<li>Accumulating Fact Table<br>Tracks process milestones (e.g., order lifecycle).<\/li>\n<\/ol>\n\n\n\n<h1 class=\"wp-block-heading\">What is a Dimension Table?<\/h1>\n\n\n\n<p>A Dimension Table stores descriptive information about the business.<\/p>\n\n\n\n<p>It provides context to the numeric data in the fact table.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Key Characteristics of Dimension Table<\/h2>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Contains descriptive attributes<\/li>\n\n\n\n<li>Smaller than fact tables<\/li>\n\n\n\n<li>Used for filtering, grouping, and labeling<\/li>\n\n\n\n<li>Usually contains a primary key<\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\">Example: Customer Dimension Table<\/h2>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-fixed-layout\"><thead><tr><th>customer_id<\/th><th>name<\/th><th>city<\/th><th>segment<\/th><th>age_group<\/th><\/tr><\/thead><\/table><\/figure>\n\n\n\n<p>This table describes the customer.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Common Dimension Tables<\/h2>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Customer<\/li>\n\n\n\n<li>Product<\/li>\n\n\n\n<li>Date<\/li>\n\n\n\n<li>Region<\/li>\n\n\n\n<li>Employee<\/li>\n<\/ul>\n\n\n\n<h1 class=\"wp-block-heading\">Fact vs Dimension Table<\/h1>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-fixed-layout\"><thead><tr><th>Feature<\/th><th>Fact Table<\/th><th>Dimension Table<\/th><\/tr><\/thead><tbody><tr><td>Data Type<\/td><td>Numeric (measures)<\/td><td>Descriptive (attributes)<\/td><\/tr><tr><td>Size<\/td><td>Large<\/td><td>Smaller<\/td><\/tr><tr><td>Keys<\/td><td>Foreign keys<\/td><td>Primary key<\/td><\/tr><tr><td>Purpose<\/td><td>Calculations<\/td><td>Filtering &amp; grouping<\/td><\/tr><tr><td>Example<\/td><td>Sales amount<\/td><td>Customer details<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<h1 class=\"wp-block-heading\">How They Work Together<\/h1>\n\n\n\n<p>Example Question:<\/p>\n\n\n\n<p>What is the total sales amount by region?<\/p>\n\n\n\n<p>Step 1: Fact table provides sales amount<br>Step 2: Dimension table provides region name<br>Step 3: Join them to calculate total sales by region<\/p>\n\n\n\n<p>Fact Table + Dimension Tables = Business Insights<\/p>\n\n\n\n<h1 class=\"wp-block-heading\">Real-World Example<\/h1>\n\n\n\n<p>In a retail company:<\/p>\n\n\n\n<p>Fact Table:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Sales transactions<\/li>\n<\/ul>\n\n\n\n<p>Dimension Tables:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Customer details<\/li>\n\n\n\n<li>Product information<\/li>\n\n\n\n<li>Store location<\/li>\n\n\n\n<li>Time (date, month, year)<\/li>\n<\/ul>\n\n\n\n<p>This structure is commonly used in tools like:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Snowflake<\/li>\n\n\n\n<li>Amazon Redshift<\/li>\n\n\n\n<li>Google BigQuery<\/li>\n<\/ul>\n\n\n\n<h1 class=\"wp-block-heading\">Interview Answer (Short Version)<\/h1>\n\n\n\n<p>A Fact Table stores measurable business data and foreign keys, while a Dimension Table stores descriptive attributes that provide context to the facts.<\/p>\n\n\n\n<h1 class=\"wp-block-heading\">Final Summary<\/h1>\n\n\n\n<p>Fact Table = Numbers (What happened?)<br>Dimension Table = Description (Who? What? When? Where?)<\/p>\n\n\n\n<p>Together, they form the foundation of dimensional data modeling in a Data Warehouse.<\/p>\n\n\n<div class=\"yoast-breadcrumbs\"><span><span><a href=\"https:\/\/gigz.pk\/python\/\">Home<\/a><\/span> \u00bb <span class=\"breadcrumb_last\" aria-current=\"page\">PYTHON FOR DATA ENGINEERING (PYDE) > Data Warehousing Concepts > Fact and Dimension Tables<\/span><\/span><\/div>\n\n\n<div class=\"schema-faq wp-block-yoast-faq-block\"><div class=\"schema-faq-section\" id=\"faq-question-1774284256652\"><strong class=\"schema-faq-question\"><\/strong> <p class=\"schema-faq-answer\"><\/p> <\/div> <\/div>\n","protected":false},"menu_order":133,"template":"","class_list":["post-221","lesson","type-lesson","status-publish","hentry"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.5 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>Fact and Dimension Tables - One Language. Endless Possibilities<\/title>\n<meta name=\"description\" content=\"Learn fact vs dimension tables with examples, differences, and how they work together in data warehouse design.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/gigz.pk\/python\/lesson\/fact-and-dimension-tables\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Fact and Dimension Tables - One Language. Endless Possibilities\" \/>\n<meta property=\"og:description\" content=\"Learn fact vs dimension tables with examples, differences, and how they work together in data warehouse design.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/gigz.pk\/python\/lesson\/fact-and-dimension-tables\/\" \/>\n<meta property=\"og:site_name\" content=\"One Language. Endless Possibilities\" \/>\n<meta property=\"article:modified_time\" content=\"2026-03-23T16:45:46+00:00\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data1\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":[\"WebPage\",\"FAQPage\"],\"@id\":\"https:\\\/\\\/gigz.pk\\\/python\\\/lesson\\\/fact-and-dimension-tables\\\/\",\"url\":\"https:\\\/\\\/gigz.pk\\\/python\\\/lesson\\\/fact-and-dimension-tables\\\/\",\"name\":\"Fact and Dimension Tables - One Language. Endless Possibilities\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/gigz.pk\\\/python\\\/#website\"},\"datePublished\":\"2026-03-03T10:25:33+00:00\",\"dateModified\":\"2026-03-23T16:45:46+00:00\",\"description\":\"Learn fact vs dimension tables with examples, differences, and how they work together in data warehouse design.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/gigz.pk\\\/python\\\/lesson\\\/fact-and-dimension-tables\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/gigz.pk\\\/python\\\/lesson\\\/fact-and-dimension-tables\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/gigz.pk\\\/python\\\/lesson\\\/fact-and-dimension-tables\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/gigz.pk\\\/python\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"PYTHON FOR DATA ENGINEERING (PYDE) > Data Warehousing Concepts > Fact and Dimension Tables\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/gigz.pk\\\/python\\\/#website\",\"url\":\"https:\\\/\\\/gigz.pk\\\/python\\\/\",\"name\":\"One Language. Endless Possibilities\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/gigz.pk\\\/python\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Fact and Dimension Tables - One Language. Endless Possibilities","description":"Learn fact vs dimension tables with examples, differences, and how they work together in data warehouse design.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/gigz.pk\/python\/lesson\/fact-and-dimension-tables\/","og_locale":"en_US","og_type":"article","og_title":"Fact and Dimension Tables - One Language. Endless Possibilities","og_description":"Learn fact vs dimension tables with examples, differences, and how they work together in data warehouse design.","og_url":"https:\/\/gigz.pk\/python\/lesson\/fact-and-dimension-tables\/","og_site_name":"One Language. Endless Possibilities","article_modified_time":"2026-03-23T16:45:46+00:00","twitter_card":"summary_large_image","twitter_misc":{"Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":["WebPage","FAQPage"],"@id":"https:\/\/gigz.pk\/python\/lesson\/fact-and-dimension-tables\/","url":"https:\/\/gigz.pk\/python\/lesson\/fact-and-dimension-tables\/","name":"Fact and Dimension Tables - One Language. Endless Possibilities","isPartOf":{"@id":"https:\/\/gigz.pk\/python\/#website"},"datePublished":"2026-03-03T10:25:33+00:00","dateModified":"2026-03-23T16:45:46+00:00","description":"Learn fact vs dimension tables with examples, differences, and how they work together in data warehouse design.","breadcrumb":{"@id":"https:\/\/gigz.pk\/python\/lesson\/fact-and-dimension-tables\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/gigz.pk\/python\/lesson\/fact-and-dimension-tables\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/gigz.pk\/python\/lesson\/fact-and-dimension-tables\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/gigz.pk\/python\/"},{"@type":"ListItem","position":2,"name":"PYTHON FOR DATA ENGINEERING (PYDE) > Data Warehousing Concepts > Fact and Dimension Tables"}]},{"@type":"WebSite","@id":"https:\/\/gigz.pk\/python\/#website","url":"https:\/\/gigz.pk\/python\/","name":"One Language. Endless Possibilities","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/gigz.pk\/python\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"}]}},"_links":{"self":[{"href":"https:\/\/gigz.pk\/python\/wp-json\/wp\/v2\/lesson\/221","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/gigz.pk\/python\/wp-json\/wp\/v2\/lesson"}],"about":[{"href":"https:\/\/gigz.pk\/python\/wp-json\/wp\/v2\/types\/lesson"}],"wp:attachment":[{"href":"https:\/\/gigz.pk\/python\/wp-json\/wp\/v2\/media?parent=221"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}