sorenson_workstation_data_modeling.ipynb

{"cells":[{"source":"# Sorenson Workstation Data Modeling\nAs a data analyst, I've come to understand the profound impact that effective data modeling can have on business operations. As Serge Gershkovich eloquently stated, \"A model is not simply the shape that data takes once uploaded to a database, but rather, the blueprint for business operations.\" With this guiding principle in mind, I'm excited to embark on a new project that harnesses the power of data modeling to drive insights and decision-making.\n\nOur project revolves around a rich dataset spanning the years 1980 to 1996, capturing the sales of computer workstations. Sourced from the Dartmouth College repository (http://five.dartmouth.edu/datasets), this dataset presents a wealth of opportunities for analysis and interpretation.\n\n# Primary objective:\nMy primary goal is to craft a robust dimensional data model, utilizing both star and snowflake schema methodologies. By structuring our data in this manner, I aim to facilitate efficient querying and analysis, empowering stakeholders to extract actionable insights effortlessly.\n\n# Secondary objective:\nIn addition to our primary goal, I'm also focusing on building a basic data transformation pipeline tailored for analytics. Recognizing the importance of streamlined data processing, this pipeline will ensure that raw data is refined and prepared for analysis in a timely and efficient manner.\n\n# Method:\nFurthermore, I've chosen to embrace the Kimball method as our preferred approach, opting to depart from traditional OLAP techniques in favor of a methodology that prioritizes simplicity, flexibility, and user accessibility.\n\nWhy this emphasis on data transformation? Because poorly modeled data can lead to long and expensive queries, impeding decision-making processes and hindering organizational progress. By investing in a robust data modeling strategy and implementing an effective transformation pipeline, I aim to mitigate these risks and unlock the true potential of our data.","metadata":{},"cell_type":"markdown","id":"1a2d82d5-dafe-4a23-a874-c633e64eb946"},{"source":"# techs used - duckdb for a local dummy database\n# columnar database\nimport duckdb\nimport timeit\nimport pandas as pd","metadata":{"executionCancelledAt":null,"executionTime":1198,"lastExecutedAt":1704221233744,"lastScheduledRunId":null,"lastSuccessfullyExecutedCode":"# techs used - duckdb for a local dummy database\n# columnar database\nimport duckdb\nimport timeit\nimport pandas as pd"},"id":"471dc3e8","cell_type":"code","execution_count":1,"outputs":[]},{"source":"### DDL to instantiate database ###\n\n# create initial database connection (and instantiate database if it doesn't exist already) \ncon = duckdb.connect('dim-model.db')","metadata":{"executionCancelledAt":null,"executionTime":84,"lastExecutedAt":1704221233828,"lastScheduledRunId":null,"lastSuccessfullyExecutedCode":"### DDL to instantiate database ###\n\n# create initial database connection (and instantiate database if it doesn't exist already) \ncon = duckdb.connect('dim-model.db')"},"id":"db8c2ee4","cell_type":"code","execution_count":2,"outputs":[]},{"source":"# create two separate schemas\ncon.sql(\"\"\"\nCREATE SCHEMA IF NOT EXISTS snow;\nCREATE SCHEMA IF NOT EXISTS star;\n\"\"\")","metadata":{"executionCancelledAt":null,"executionTime":49,"lastExecutedAt":1704221233877,"lastScheduledRunId":null,"lastSuccessfullyExecutedCode":"# create two separate schemas\ncon.sql(\"\"\"\nCREATE SCHEMA IF NOT EXISTS snow;\nCREATE SCHEMA IF NOT EXISTS star;\n\"\"\")"},"id":"8850afff-7651-4ef2-8228-3a4c381e2eb1","cell_type":"code","execution_count":3,"outputs":[]},{"source":"# create a table using the initial data - auto create columns based on information\nsql = \"\"\"\nCREATE OR REPLACE TABLE raw_companies AS\nSELECT * FROM read_csv_auto('SorensonworkfirmFIVEdata.csv')\n\"\"\"\ncon.sql(sql)","metadata":{"executionCancelledAt":null,"executionTime":62,"lastExecutedAt":1704221233941,"lastScheduledRunId":null,"lastSuccessfullyExecutedCode":"# create a table using the initial data - auto create columns based on information\nsql = \"\"\"\nCREATE OR REPLACE TABLE raw_companies AS\nSELECT * FROM read_csv_auto('SorensonworkfirmFIVEdata.csv')\n\"\"\"\ncon.sql(sql)"},"id":"f91a89ce","cell_type":"code","execution_count":4,"outputs":[]},{"source":"# display the raw_companies table\ncon.table('raw_companies')","metadata":{"executionCancelledAt":null,"executionTime":48,"lastExecutedAt":1704221233991,"lastScheduledRunId":null,"lastSuccessfullyExecutedCode":"# display the raw_companies table\ncon.table('raw_companies')"},"id":"9d1f7880-4f4a-4195-9ffc-a4e6b4167cf4","cell_type":"code","execution_count":5,"outputs":[{"output_type":"execute_result","data":{"text/plain":"┌───────┬──────────────────────┬────────────┬────────┬───────────────┬───┬───────┬─────────┬───────┬────────┬───────┐\n│ YEAR  │       FIRMNAME       │ FIVEFIRMID │ FIRMID │ CUSIPHEADERID │ … │ COMM  │ MONITOR │ DISK  │ MEMORY │ BOARD │\n│ int64 │       varchar        │   int64    │ int64  │    varchar    │   │ int64 │  int64  │ int64 │ int64  │ int64 │\n├───────┼──────────────────────┼────────────┼────────┼───────────────┼───┼───────┼─────────┼───────┼────────┼───────┤\n│  1987 │ Accent Systems Corp. │          1 │      1 │ NULL          │ … │     0 │       0 │     0 │      0 │     0 │\n│  1982 │ Adage, Inc.          │          2 │      2 │ NULL          │ … │     0 │       0 │     0 │      0 │     0 │\n│  1983 │ Adage, Inc.          │          2 │      2 │ NULL          │ … │     0 │       0 │     0 │      0 │     0 │\n│  1984 │ Adage, Inc.          │          2 │      2 │ NULL          │ … │     0 │       0 │     0 │      0 │     0 │\n│  1985 │ Adage, Inc.          │          2 │      2 │ NULL          │ … │     0 │       0 │     0 │      0 │     0 │\n│  1986 │ Adage, Inc.          │          2 │      2 │ NULL          │ … │     0 │       0 │     0 │      0 │     0 │\n│  1987 │ Adage, Inc.          │          2 │      2 │ NULL          │ … │     0 │       0 │     0 │      0 │     0 │\n│  1988 │ Adage, Inc.          │          2 │      2 │ NULL          │ … │     0 │       0 │     0 │      0 │     0 │\n│  1989 │ Adage, Inc.          │          2 │      2 │ NULL          │ … │     0 │       0 │     0 │      0 │     0 │\n│  1990 │ Adage, Inc.          │          2 │      2 │ NULL          │ … │     0 │       0 │     0 │      0 │     0 │\n│    ·  │      ·               │          · │      · │  ·            │ · │     · │       · │     · │      · │     · │\n│    ·  │      ·               │          · │      · │  ·            │ · │     · │       · │     · │      · │     · │\n│    ·  │      ·               │          · │      · │  ·            │ · │     · │       · │     · │      · │     · │\n│  1990 │ Zuken, Inc.          │        173 │    173 │ NULL          │ … │     0 │       0 │     0 │      0 │     0 │\n│  1991 │ Zuken, Inc.          │        173 │    173 │ NULL          │ … │     0 │       0 │     0 │      0 │     0 │\n│  1992 │ Zuken, Inc.          │        173 │    173 │ NULL          │ … │     0 │       0 │     0 │      0 │     0 │\n│  1993 │ Zuken, Inc.          │        173 │    173 │ NULL          │ … │     0 │       0 │     0 │      0 │     0 │\n│  1982 │ Florida Computer G…  │        174 │    174 │ NULL          │ … │     0 │       0 │     0 │      0 │     0 │\n│  1983 │ Florida Computer G…  │        174 │    174 │ NULL          │ … │     0 │       0 │     0 │      0 │     0 │\n│  1984 │ Florida Computer G…  │        174 │    174 │ NULL          │ … │     0 │       0 │     0 │      0 │     0 │\n│  1983 │ Orcatech             │        175 │    175 │ NULL          │ … │     1 │       0 │     0 │      0 │     0 │\n│  1984 │ Orcatech             │        175 │    175 │ NULL          │ … │     1 │       0 │     0 │      0 │     0 │\n│  1985 │ Orcatech             │        175 │    175 │ NULL          │ … │     1 │       0 │     0 │      0 │     0 │\n├───────┴──────────────────────┴────────────┴────────┴───────────────┴───┴───────┴─────────┴───────┴────────┴───────┤\n│ 682 rows (20 shown)                                                                         31 columns (10 shown) │\n└───────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘"},"metadata":{},"execution_count":5}]},{"source":"# verify initial data types\nsql = \"\"\"\nDESCRIBE raw_companies;\n\"\"\"\n\ninitial_cols_dict = {col[0]:col[1] for col in con.sql(sql).fetchall()}","metadata":{"executionCancelledAt":null,"executionTime":46,"lastExecutedAt":1704221234037,"lastScheduledRunId":null,"lastSuccessfullyExecutedCode":"# verify initial data types\nsql = \"\"\"\nDESCRIBE raw_companies;\n\"\"\"\n\ninitial_cols_dict = {col[0]:col[1] for col in con.sql(sql).fetchall()}"},"id":"477f22b8","cell_type":"code","execution_count":6,"outputs":[]},{"source":"# print the initial columns dictionary\ninitial_cols_dict","metadata":{"executionCancelledAt":null,"executionTime":51,"lastExecutedAt":1704221234090,"lastScheduledRunId":null,"lastSuccessfullyExecutedCode":"# print the initial columns dictionary\ninitial_cols_dict"},"id":"58c64f45","cell_type":"code","execution_count":7,"outputs":[{"output_type":"execute_result","data":{"text/plain":"{'YEAR': 'BIGINT',\n 'FIRMNAME': 'VARCHAR',\n 'FIVEFIRMID': 'BIGINT',\n 'FIRMID': 'BIGINT',\n 'CUSIPHEADERID': 'VARCHAR',\n 'CUSIPHeaderName': 'VARCHAR',\n 'CUSIPHISTORYID': 'VARCHAR',\n 'CUSIPHistoryName': 'VARCHAR',\n 'FOUND': 'BIGINT',\n 'FATE': 'VARCHAR',\n 'ESTATE': 'BIGINT',\n 'SALES': 'DOUBLE',\n 'EMPLOY': 'DOUBLE',\n 'ZIP': 'BIGINT',\n 'LAT': 'DOUBLE',\n 'LON': 'DOUBLE',\n 'RANDD': 'BIGINT',\n 'PRODUCTS': 'BIGINT',\n 'PTYPES': 'BIGINT',\n 'SYSTEM': 'BIGINT',\n 'GRAPHIC': 'BIGINT',\n 'CEO': 'VARCHAR',\n 'OWN': 'BIGINT',\n 'CPU': 'BIGINT',\n 'OS': 'BIGINT',\n 'APPS': 'BIGINT',\n 'COMM': 'BIGINT',\n 'MONITOR': 'BIGINT',\n 'DISK': 'BIGINT',\n 'MEMORY': 'BIGINT',\n 'BOARD': 'BIGINT'}"},"metadata":{},"execution_count":7}]},{"source":"## A snowflake data model\n\nSteps of Kimball dimensional modeling:\n\n1. Select the business process\n2. Declare the grain\n3. Identify the dimensions\n4. Identify the facts\n\n![](snow.png)","metadata":{},"id":"b0d75458","cell_type":"markdown"},{"source":"# check lat/long/zip data columns\ncon.sql('SELECT DISTINCT lat, lon, zip FROM raw_companies')","metadata":{"executionCancelledAt":null,"executionTime":48,"lastExecutedAt":1704221234138,"lastScheduledRunId":null,"lastSuccessfullyExecutedCode":"# check lat/long/zip data columns\ncon.sql('SELECT DISTINCT lat, lon, zip FROM raw_companies')"},"id":"04c4ea15","cell_type":"code","execution_count":8,"outputs":[{"output_type":"execute_result","data":{"text/plain":"┌───────────────────┬───────────────────┬───────┐\n│        LAT        │        LON        │  ZIP  │\n│      double       │      double       │ int64 │\n├───────────────────┼───────────────────┼───────┤\n│ 0.705875992774963 │ -1.39546799659729 │ 15213 │\n│  0.74406099319458 │ -1.24499404430389 │  1851 │\n│ 0.574072003364563 │ -2.04558706283569 │ 92121 │\n│ 0.742465019226074 │ -1.24769496917725 │  1460 │\n│ 0.743335008621216 │ -1.24683105945587 │  1886 │\n│ 0.590389013290405 │ -2.06493091583252 │ 90501 │\n│ 0.531078994274139 │ -1.47060298919678 │ 32301 │\n│ 0.743398010730743 │  -1.2454240322113 │  1824 │\n│ 0.655035972595215 │ -2.12879991531372 │ 94538 │\n│ 0.578755021095276 │ -2.04770708084106 │ 92008 │\n│          ·        │         ·         │    ·  │\n│          ·        │         ·         │    ·  │\n│          ·        │         ·         │    ·  │\n│  0.73657500743866 │ -1.24679398536682 │  1746 │\n│ 0.734311997890472 │ -1.53622198104858 │ 60008 │\n│  0.70123702287674 │ -1.83435499668121 │ 80501 │\n│ 0.651730000972748 │ -2.12721490859985 │ 95112 │\n│ 0.591771006584168 │ -1.46928703784943 │ 30093 │\n│ 0.738604009151459 │  -1.2425320148468 │  2159 │\n│ 0.741008996963501 │ -1.24572396278381 │  1742 │\n│ 0.653389990329742 │ -2.12743210792542 │ 95035 │\n│ 0.596495985984802 │ -2.07023811340332 │ 91367 │\n│ 0.717025995254517 │ -1.28358101844788 │  6904 │\n├───────────────────┴───────────────────┴───────┤\n│ 156 rows (20 shown)                 3 columns │\n└───────────────────────────────────────────────┘"},"metadata":{},"execution_count":8}]},{"source":"### INITIAL DATA CLEANSING ###\n\n# change column zip to type varchar\nsql = \"\"\"\nALTER TABLE raw_companies ALTER COLUMN zip SET DATA TYPE VARCHAR;\n\"\"\"\n\n# ensure leading 0's present in zip\nsql += \"\"\"\nUPDATE raw_companies\nSET zip = RIGHT('000' || zip, 5);\n\"\"\" \n\n# convert lat/long variables to degrees from radians\nsql += f\"\"\"\nUPDATE raw_companies\nSET lat = DEGREES(lat), lon = DEGREES(lon);\n\"\"\"\n\ncon.sql(sql)","metadata":{"executionCancelledAt":null,"executionTime":48,"lastExecutedAt":1704221234186,"lastScheduledRunId":null,"lastSuccessfullyExecutedCode":"### INITIAL DATA CLEANSING ###\n\n# change column zip to type varchar\nsql = \"\"\"\nALTER TABLE raw_companies ALTER COLUMN zip SET DATA TYPE VARCHAR;\n\"\"\"\n\n# ensure leading 0's present in zip\nsql += \"\"\"\nUPDATE raw_companies\nSET zip = RIGHT('000' || zip, 5);\n\"\"\" \n\n# convert lat/long variables to degrees from radians\nsql += f\"\"\"\nUPDATE raw_companies\nSET lat = DEGREES(lat), lon = DEGREES(lon);\n\"\"\"\n\ncon.sql(sql)"},"id":"dea282de","cell_type":"code","execution_count":9,"outputs":[]},{"source":"# display the results of cleansing\ncon.sql('SELECT DISTINCT lat, lon, zip FROM raw_companies')","metadata":{"executionCancelledAt":null,"executionTime":48,"lastExecutedAt":1704221234234,"lastScheduledRunId":null,"lastSuccessfullyExecutedCode":"# display the results of cleansing\ncon.sql('SELECT DISTINCT lat, lon, zip FROM raw_companies')"},"id":"1a6809f5","cell_type":"code","execution_count":10,"outputs":[{"output_type":"execute_result","data":{"text/plain":"┌────────────────────┬─────────────────────┬─────────┐\n│        LAT         │         LON         │   ZIP   │\n│       double       │       double        │ varchar │\n├────────────────────┼─────────────────────┼─────────┤\n│ 40.443715245612374 │  -79.95442665060105 │ 15213   │\n│ 42.540112037753566 │  -71.48765585356176 │ 01460   │\n│ 33.826798732433275 │ -118.31182646328722 │ 90501   │\n│  34.23130644074354 │ -119.04630402549516 │ 93010   │\n│ 30.428584959960574 │  -84.25934462029849 │ 32301   │\n│  37.53079665895287 │ -121.97125057528321 │ 94538   │\n│  33.16022008076424 │ -117.32497341124682 │ 92008   │\n│  42.46344999450934 │  -82.90069022078883 │ 48080   │\n│ 33.027864675027736 │ -117.08558894121654 │ 92127   │\n│   42.3824917622772 │  -71.23653028834754 │ 02154   │\n│           ·        │           ·         │   ·     │\n│           ·        │           ·         │   ·     │\n│           ·        │           ·         │   ·     │\n│ 39.021461539646026 │   -77.3986313348846 │ 22170   │\n│  33.60025160294771 │ -117.70850219352162 │ 92653   │\n│ 37.436488821987886 │ -121.89288098474681 │ 95035   │\n│ 45.144606602252026 │ -122.85831456354957 │ 97077   │\n│  40.46422630368992 │  -79.94543129146405 │ 15224   │\n│ 37.720447106542046 │ -122.15867096610819 │ 94577   │\n│  42.75640363820038 │  -71.46674181432287 │ 03060   │\n│  42.86669753291775 │  -71.51274996857242 │ 03054   │\n│  28.75234042442356 │   -81.3380658820948 │ 32746   │\n│   33.8109834294101 │ -118.21500170161322 │ 90810   │\n├────────────────────┴─────────────────────┴─────────┤\n│ 156 rows (20 shown)                      3 columns │\n└────────────────────────────────────────────────────┘"},"metadata":{},"execution_count":10}]},{"source":"# set schema\n# templated sql - DRY\nschema = 'snow'","metadata":{"executionCancelledAt":null,"executionTime":47,"lastExecutedAt":1704221234281,"lastScheduledRunId":null,"lastSuccessfullyExecutedCode":"# set schema\n# templated sql - DRY\nschema = 'snow'"},"id":"40e4691e","cell_type":"code","execution_count":11,"outputs":[]},{"source":"# create compustat dimension\n# MD5 hash as keys vs ordered int\n# surrogate vs. natural keys\n# primary keys (collisions)\nsql = f\"\"\"\n\n\nCREATE OR REPLACE TABLE {schema}.compustat (\n    cusip_id VARCHAR,\n    cusip_name VARCHAR,\n    cusip_historical_id VARCHAR,\n    cusip_historical_name VARCHAR,\n    PRIMARY KEY (cusip_id)\n);\n\n\"\"\"\n\nsql += f\"\"\"\nINSERT INTO {schema}.compustat (cusip_id, cusip_name, cusip_historical_id, cusip_historical_name)\nWITH cid AS (\n    SELECT DISTINCT\n        CUSIPHEADERID AS cusip_id,\n        CUSIPHEADERNAME AS cusip_name,\n        CUSIPHISTORYID AS cusip_historical_id,\n        CUSIPHISTORYNAME AS cusip_historical_name\n    FROM\n        raw_companies\n)\nSELECT\n    MD5(COALESCE(cusip_id || cusip_name || cusip_historical_id || cusip_historical_name, '-1')) AS cusip_id, --1 added for nulls\n    cusip_name,\n    cusip_historical_id,\n    cusip_historical_name\nFROM\n    cid;\n\"\"\"\n\ncon.sql(sql)","metadata":{"executionCancelledAt":null,"executionTime":47,"lastExecutedAt":1704221234329,"lastScheduledRunId":null,"lastSuccessfullyExecutedCode":"# create compustat dimension\n# MD5 hash as keys vs ordered int\n# surrogate vs. natural keys\n# primary keys (collisions)\nsql = f\"\"\"\n\n\nCREATE OR REPLACE TABLE {schema}.compustat (\n    cusip_id VARCHAR,\n    cusip_name VARCHAR,\n    cusip_historical_id VARCHAR,\n    cusip_historical_name VARCHAR,\n    PRIMARY KEY (cusip_id)\n);\n\n\"\"\"\n\nsql += f\"\"\"\nINSERT INTO {schema}.compustat (cusip_id, cusip_name, cusip_historical_id, cusip_historical_name)\nWITH cid AS (\n    SELECT DISTINCT\n        CUSIPHEADERID AS cusip_id,\n        CUSIPHEADERNAME AS cusip_name,\n        CUSIPHISTORYID AS cusip_historical_id,\n        CUSIPHISTORYNAME AS cusip_historical_name\n    FROM\n        raw_companies\n)\nSELECT\n    MD5(COALESCE(cusip_id || cusip_name || cusip_historical_id || cusip_historical_name, '-1')) AS cusip_id, --1 added for nulls\n    cusip_name,\n    cusip_historical_id,\n    cusip_historical_name\nFROM\n    cid;\n\"\"\"\n\ncon.sql(sql)"},"id":"6f74e1c3","cell_type":"code","execution_count":12,"outputs":[]},{"source":"# display the head of the snow.compustat table\ncon.sql('select * from snow.compustat limit 5')","metadata":{"executionCancelledAt":null,"executionTime":47,"lastExecutedAt":1704221234378,"lastScheduledRunId":null,"lastSuccessfullyExecutedCode":"# display the head of the snow.compustat table\ncon.sql('select * from snow.compustat limit 5')"},"id":"164c2c08-bb06-478a-a1b3-08a930c57f6f","cell_type":"code","execution_count":13,"outputs":[{"output_type":"execute_result","data":{"text/plain":"┌──────────────────────────────────┬─────────────────────────────┬─────────────────────┬───────────────────────────┐\n│             cusip_id             │         cusip_name          │ cusip_historical_id │   cusip_historical_name   │\n│             varchar              │           varchar           │       varchar       │          varchar          │\n├──────────────────────────────────┼─────────────────────────────┼─────────────────────┼───────────────────────────┤\n│ 94af45e233e4d5f91cc562c17ef0f2e6 │ APOLLO COMPUTER INC         │ 3790410             │ APOLLO COMPUTER INC       │\n│ ee32ebbae037a9ffaf5a4d7681e32414 │ AUTO-TROL TECHNOLOGY CORP   │ 5275410             │ AUTO-TROL TECHNOLOGY CORP │\n│ c1ab7ceff2b8979a731e9ff6a8a7c9f5 │ AYDIN CORP                  │ 5468110             │ AYDIN CORP                │\n│ 4f019ba669ea4833fdb0d951f855eda3 │ CADNETIX CORP               │ 12759010            │ CADNETIX CORP             │\n│ ddda16d2d6741556439f68b2b5ec7617 │ AMERIQUEST TECHNOLOGIES INC │ 12589740            │ C M S ENHANCEMENTS INC    │\n└──────────────────────────────────┴─────────────────────────────┴─────────────────────┴───────────────────────────┘"},"metadata":{},"execution_count":13}]},{"source":"# create ceo dimension\n# naming conventions\n# joining on strings vs. others\nsql = f\"\"\"\n\nCREATE OR REPLACE TABLE {schema}.ceos (\n    ceo_id VARCHAR,\n    ceo_name VARCHAR,\n    PRIMARY KEY (ceo_id)\n);\n\n\"\"\"\n\nsql += f\"\"\"\n\nINSERT INTO {schema}.ceos (ceo_id, ceo_name)\n\nSELECT DISTINCT\n    MD5(COALESCE(ceo, '-1')) AS ceo_id,\n    ceo AS ceo_name\nFROM\n    raw_companies;\n\n\"\"\"\n\ncon.sql(sql)","metadata":{"executionCancelledAt":null,"executionTime":46,"lastExecutedAt":1704221234425,"lastScheduledRunId":null,"lastSuccessfullyExecutedCode":"# create ceo dimension\n# naming conventions\n# joining on strings vs. others\nsql = f\"\"\"\n\nCREATE OR REPLACE TABLE {schema}.ceos (\n    ceo_id VARCHAR,\n    ceo_name VARCHAR,\n    PRIMARY KEY (ceo_id)\n);\n\n\"\"\"\n\nsql += f\"\"\"\n\nINSERT INTO {schema}.ceos (ceo_id, ceo_name)\n\nSELECT DISTINCT\n    MD5(COALESCE(ceo, '-1')) AS ceo_id,\n    ceo AS ceo_name\nFROM\n    raw_companies;\n\n\"\"\"\n\ncon.sql(sql)"},"id":"1e0c45df","cell_type":"code","execution_count":14,"outputs":[]},{"source":"# display the snow.ceos table\ncon.table('snow.ceos')","metadata":{"executionCancelledAt":null,"executionTime":48,"lastExecutedAt":1704221234474,"lastScheduledRunId":null,"lastSuccessfullyExecutedCode":"# display the snow.ceos table\ncon.table('snow.ceos')"},"id":"b3b1f3e3-f54f-49e2-9f11-99477e9c7bbc","cell_type":"code","execution_count":15,"outputs":[{"output_type":"execute_result","data":{"text/plain":"┌──────────────────────────────────┬───────────────────────┐\n│              ceo_id              │       ceo_name        │\n│             varchar              │        varchar        │\n├──────────────────────────────────┼───────────────────────┤\n│ c35bc6945e2054750eb18ed6b9fed71a │ Kelly T. Hickel       │\n│ f058b6eb7f7202e7fa1ee269a287a67c │ William T. Mason      │\n│ e40fabee5589c9be667f61dd1d299af4 │ Dick Wauk             │\n│ f30900f5f0907fcb2a46bc4e5a267fda │ Thomas J. Goliash     │\n│ 04244f82d9e865b19061a47f35cc5ad7 │ Ronald Gruner         │\n│ d259b6064c2b1f1dfe67ea7449606e14 │ Craig J. Mundie       │\n│ fad7f43d9fa01027796bd56bda4a9a2e │ Robert Bruce          │\n│ c8099fe7c4782d51be8495e08542d938 │ Charles P. Spector    │\n│ 0bd712a991f0356000fb0d2f482c5e71 │ Thomas A. Vanderslice │\n│ 47600976a8997349959fa69b00d27297 │ Lawrence T. Kou       │\n│                ·                 │       ·               │\n│                ·                 │       ·               │\n│                ·                 │       ·               │\n│ 37bbc5c04ca06f5dfcff0910013cee16 │ William Walker        │\n│ 67637e4ee27eca52b17c771a0f39a08a │ Delbert Yocam         │\n│ de50b3d0bb7217d785101939e51fa0b6 │ J. Gay                │\n│ b1fffbc9f852f1dc43b792ac6cdaac87 │ Gareth Stenner        │\n│ 409d4b2932ced8aaa07f3605e9a5e9e0 │ Edon Gnuse            │\n│ da3717a441fedc3bf656ea404382127b │ John R. McPherson     │\n│ eb0c49e27341778e4561dc661460004b │ Paul A. Allaire       │\n│ 3305cf5d8db8248177e009315eaefd3f │ Paul A. Frost         │\n│ 936c52aa61f40811b0e79ab43cbe3a4b │ J. Katsube            │\n│ 0199eae6b3c6f152874c636fa91e85e8 │ Michael R. Coffman    │\n├──────────────────────────────────┴───────────────────────┤\n│ 235 rows (20 shown)                            2 columns │\n└──────────────────────────────────────────────────────────┘"},"metadata":{},"execution_count":15}]},{"source":"# create locations dimension\n# type 2 dimensions - change slow\n# insert only\n# primary keys\nsql = f\"\"\"\n\nCREATE OR REPLACE TABLE {schema}.locations (\n    location_id VARCHAR,\n    zipcode VARCHAR,\n    latitude DOUBLE,\n    longitude DOUBLE,\n    PRIMARY KEY (location_id)\n);\n\n\"\"\"\n\nsql += f\"\"\"\nINSERT INTO {schema}.locations (location_id, zipcode, latitude, longitude)\n\nSELECT DISTINCT\n    MD5(COALESCE(zip || CAST(lat AS VARCHAR) || CAST(lon AS VARCHAR), '-1')),\n    zip,\n    lat,\n    lon\nFROM\n    raw_companies;\n\n\"\"\"\n\ncon.sql(sql)","metadata":{"executionCancelledAt":null,"executionTime":47,"lastExecutedAt":1704221234521,"lastScheduledRunId":null,"lastSuccessfullyExecutedCode":"# create locations dimension\n# type 2 dimensions - change slow\n# insert only\n# primary keys\nsql = f\"\"\"\n\nCREATE OR REPLACE TABLE {schema}.locations (\n    location_id VARCHAR,\n    zipcode VARCHAR,\n    latitude DOUBLE,\n    longitude DOUBLE,\n    PRIMARY KEY (location_id)\n);\n\n\"\"\"\n\nsql += f\"\"\"\nINSERT INTO {schema}.locations (location_id, zipcode, latitude, longitude)\n\nSELECT DISTINCT\n    MD5(COALESCE(zip || CAST(lat AS VARCHAR) || CAST(lon AS VARCHAR), '-1')),\n    zip,\n    lat,\n    lon\nFROM\n    raw_companies;\n\n\"\"\"\n\ncon.sql(sql)"},"id":"748d493d","cell_type":"code","execution_count":16,"outputs":[]},{"source":"# create companies dimension\n# normalization benefits\n\nsql = f\"\"\"\n\nCREATE OR REPLACE TABLE {schema}.companies (\n    company_id VARCHAR,\n    company_name VARCHAR,\n    cusip_id VARCHAR,\n    ceo_id VARCHAR,\n    founding_year INTEGER,\n    ownership_type VARCHAR,\n    exit_type VARCHAR,\n    exit_comment VARCHAR,\n    location_id VARCHAR,\n    PRIMARY KEY (company_id)\n    \n);\n\n\"\"\"\n\nsql += f\"\"\"\n\nINSERT INTO {schema}.companies (company_id, company_name, cusip_id, ceo_id, founding_year, ownership_type, exit_type, exit_comment, location_id)\n\nSELECT DISTINCT\n    MD5(COALESCE(   COALESCE(firmname, '-1') ||\n                    COALESCE(CAST(CUSIPHEADERID AS VARCHAR),'-1')||\n                    COALESCE(CAST(firmid AS VARCHAR),'-1')||\n                    COALESCE(CAST(found AS VARCHAR),'-1') ||\n                    COALESCE(CAST(own AS VARCHAR),'-1') ||\n                    COALESCE(CAST(fate AS VARCHAR),'-1') ||\n                    COALESCE(CAST(estate AS VARCHAR),'-1') ||\n                    COALESCE(ceo,'-1') ||\n                    COALESCE(zip,'-1')\n                    , '-1')) as id,\n    firmname,\n    MD5(COALESCE(CUSIPHEADERID || CUSIPHEADERNAME || CUSIPHISTORYID || CUSIPHISTORYNAME, '-1')),\n    MD5(COALESCE(ceo, '-1')),\n    found,\n    CASE own\n        WHEN 0 THEN 'Private'\n        WHEN 1 THEN 'Public'\n        WHEN 2 THEN 'Subsidiary'\n        ELSE NULL END,\n    CASE estate\n        WHEN 0 THEN 'Censored'\n        WHEN 1 THEN 'Exited market'\n        WHEN 2 THEN 'Acquired'\n        WHEN 3 THEN 'Spun off'\n        WHEN 4 THEN 'Changed Name'\n        ELSE NULL END,\n    fate,\n     MD5(COALESCE(zip || CAST(lat AS VARCHAR) || CAST(lon AS VARCHAR), '-1'))\n    \nFROM\n    raw_companies\n\n    ;\n\"\"\"\n\ncon.sql(sql)","metadata":{"executionCancelledAt":null,"executionTime":47,"lastExecutedAt":1704221234569,"lastScheduledRunId":null,"lastSuccessfullyExecutedCode":"# create companies dimension\n# normalization benefits\n\nsql = f\"\"\"\n\nCREATE OR REPLACE TABLE {schema}.companies (\n    company_id VARCHAR,\n    company_name VARCHAR,\n    cusip_id VARCHAR,\n    ceo_id VARCHAR,\n    founding_year INTEGER,\n    ownership_type VARCHAR,\n    exit_type VARCHAR,\n    exit_comment VARCHAR,\n    location_id VARCHAR,\n    PRIMARY KEY (company_id)\n    \n);\n\n\"\"\"\n\nsql += f\"\"\"\n\nINSERT INTO {schema}.companies (company_id, company_name, cusip_id, ceo_id, founding_year, ownership_type, exit_type, exit_comment, location_id)\n\nSELECT DISTINCT\n    MD5(COALESCE(   COALESCE(firmname, '-1') ||\n                    COALESCE(CAST(CUSIPHEADERID AS VARCHAR),'-1')||\n                    COALESCE(CAST(firmid AS VARCHAR),'-1')||\n                    COALESCE(CAST(found AS VARCHAR),'-1') ||\n                    COALESCE(CAST(own AS VARCHAR),'-1') ||\n                    COALESCE(CAST(fate AS VARCHAR),'-1') ||\n                    COALESCE(CAST(estate AS VARCHAR),'-1') ||\n                    COALESCE(ceo,'-1') ||\n                    COALESCE(zip,'-1')\n                    , '-1')) as id,\n    firmname,\n    MD5(COALESCE(CUSIPHEADERID || CUSIPHEADERNAME || CUSIPHISTORYID || CUSIPHISTORYNAME, '-1')),\n    MD5(COALESCE(ceo, '-1')),\n    found,\n    CASE own\n        WHEN 0 THEN 'Private'\n        WHEN 1 THEN 'Public'\n        WHEN 2 THEN 'Subsidiary'\n        ELSE NULL END,\n    CASE estate\n        WHEN 0 THEN 'Censored'\n        WHEN 1 THEN 'Exited market'\n        WHEN 2 THEN 'Acquired'\n        WHEN 3 THEN 'Spun off'\n        WHEN 4 THEN 'Changed Name'\n        ELSE NULL END,\n    fate,\n     MD5(COALESCE(zip || CAST(lat AS VARCHAR) || CAST(lon AS VARCHAR), '-1'))\n    \nFROM\n    raw_companies\n\n    ;\n\"\"\"\n\ncon.sql(sql)"},"id":"975db7fa","cell_type":"code","execution_count":17,"outputs":[]},{"source":"# create product_offerings dimension\n# what is a fact vs. a dimension\n# CTE\nsql = f\"\"\"\n\nCREATE OR REPLACE TABLE {schema}.product_offerings (\n    product_offering_id VARCHAR,\n    product_types_offered VARCHAR,\n    PRIMARY KEY (product_offering_id)\n);\n\n\"\"\"\n\nsql += f\"\"\"\n\nINSERT INTO {schema}.product_offerings (product_offering_id, product_types_offered)\n\nWITH po AS (\n     SELECT\n        CASE\n            WHEN system = 1 AND graphic = 0 THEN 'Desktop systems'\n            WHEN system = 0 AND graphic = 1 THEN 'Graphics systems'\n            WHEN system = 1 AND graphic = 1 THEN 'Desktop + Graphics systems'\n            ELSE NULL END as offering\n\n    from raw_companies\n)\nSELECT DISTINCT\n    MD5(COALESCE(offering, '-1')),\n    offering    \nFROM\n    po;\n\"\"\"\n\ncon.sql(sql)","metadata":{"executionCancelledAt":null,"executionTime":44,"lastExecutedAt":1704221234613,"lastScheduledRunId":null,"lastSuccessfullyExecutedCode":"# create product_offerings dimension\n# what is a fact vs. a dimension\n# CTE\nsql = f\"\"\"\n\nCREATE OR REPLACE TABLE {schema}.product_offerings (\n    product_offering_id VARCHAR,\n    product_types_offered VARCHAR,\n    PRIMARY KEY (product_offering_id)\n);\n\n\"\"\"\n\nsql += f\"\"\"\n\nINSERT INTO {schema}.product_offerings (product_offering_id, product_types_offered)\n\nWITH po AS (\n     SELECT\n        CASE\n            WHEN system = 1 AND graphic = 0 THEN 'Desktop systems'\n            WHEN system = 0 AND graphic = 1 THEN 'Graphics systems'\n            WHEN system = 1 AND graphic = 1 THEN 'Desktop + Graphics systems'\n            ELSE NULL END as offering\n\n    from raw_companies\n)\nSELECT DISTINCT\n    MD5(COALESCE(offering, '-1')),\n    offering    \nFROM\n    po;\n\"\"\"\n\ncon.sql(sql)"},"id":"86493556","cell_type":"code","execution_count":18,"outputs":[]},{"source":"# create integration dimension\n# type 4 dimension - change fast\nsql = f\"\"\"\n\nCREATE OR REPLACE TABLE {schema}.integrations (\n   integration_id VARCHAR,\n    cpu_source VARCHAR,\n    os_source VARCHAR,\n    application_source VARCHAR,\n    communications_hardware_source VARCHAR,\n    disk_source VARCHAR,\n    ram_source VARCHAR,\n    motherboard_source VARCHAR,\n    PRIMARY KEY (integration_id)\n);\n\n\"\"\"\n\nsql += f\"\"\"\nINSERT INTO {schema}.integrations\n(\n    integration_id, \n    cpu_source, \n    os_source, \n    application_source, \n    communications_hardware_source, \n    disk_source, \n    ram_source, \n    motherboard_source\n)\n\nSELECT DISTINCT\n    MD5(\n        CASE WHEN cpu = 0 THEN 'Bought' ELSE 'Produced' END || \n        CASE WHEN os = 0 THEN 'Bought' ELSE 'Produced' END || \n        CASE WHEN apps = 0 THEN 'Bought' ELSE 'Produced' END || \n        CASE WHEN comm = 0 THEN 'Bought' ELSE 'Produced' END || \n        CASE WHEN disk = 0 THEN 'Bought' ELSE 'Produced' END || \n        CASE WHEN memory = 0 THEN 'Bought' ELSE 'Produced' END || \n        CASE WHEN board = 0 THEN 'Bought' ELSE 'Produced' END\n    ), \n    CASE WHEN cpu = 0 THEN 'Bought' ELSE 'Produced' END,\n    CASE WHEN os = 0 THEN 'Bought' ELSE 'Produced' END,\n    CASE WHEN apps = 0 THEN 'Bought' ELSE 'Produced' END,\n    CASE WHEN comm = 0 THEN 'Bought' ELSE 'Produced' END,\n    CASE WHEN disk = 0 THEN 'Bought' ELSE 'Produced' END,\n    CASE WHEN memory = 0 THEN 'Bought' ELSE 'Produced' END,\n    CASE WHEN board = 0 THEN 'Bought' ELSE 'Produced' END\nFROM\n    raw_companies;\n\n\n\"\"\"\n\ncon.sql(sql)","metadata":{"executionCancelledAt":null,"executionTime":42,"lastExecutedAt":1704221234657,"lastScheduledRunId":null,"lastSuccessfullyExecutedCode":"# create integration dimension\n# type 4 dimension - change fast\nsql = f\"\"\"\n\nCREATE OR REPLACE TABLE {schema}.integrations (\n   integration_id VARCHAR,\n    cpu_source VARCHAR,\n    os_source VARCHAR,\n    application_source VARCHAR,\n    communications_hardware_source VARCHAR,\n    disk_source VARCHAR,\n    ram_source VARCHAR,\n    motherboard_source VARCHAR,\n    PRIMARY KEY (integration_id)\n);\n\n\"\"\"\n\nsql += f\"\"\"\nINSERT INTO {schema}.integrations\n(\n    integration_id, \n    cpu_source, \n    os_source, \n    application_source, \n    communications_hardware_source, \n    disk_source, \n    ram_source, \n    motherboard_source\n)\n\nSELECT DISTINCT\n    MD5(\n        CASE WHEN cpu = 0 THEN 'Bought' ELSE 'Produced' END || \n        CASE WHEN os = 0 THEN 'Bought' ELSE 'Produced' END || \n        CASE WHEN apps = 0 THEN 'Bought' ELSE 'Produced' END || \n        CASE WHEN comm = 0 THEN 'Bought' ELSE 'Produced' END || \n        CASE WHEN disk = 0 THEN 'Bought' ELSE 'Produced' END || \n        CASE WHEN memory = 0 THEN 'Bought' ELSE 'Produced' END || \n        CASE WHEN board = 0 THEN 'Bought' ELSE 'Produced' END\n    ), \n    CASE WHEN cpu = 0 THEN 'Bought' ELSE 'Produced' END,\n    CASE WHEN os = 0 THEN 'Bought' ELSE 'Produced' END,\n    CASE WHEN apps = 0 THEN 'Bought' ELSE 'Produced' END,\n    CASE WHEN comm = 0 THEN 'Bought' ELSE 'Produced' END,\n    CASE WHEN disk = 0 THEN 'Bought' ELSE 'Produced' END,\n    CASE WHEN memory = 0 THEN 'Bought' ELSE 'Produced' END,\n    CASE WHEN board = 0 THEN 'Bought' ELSE 'Produced' END\nFROM\n    raw_companies;\n\n\n\"\"\"\n\ncon.sql(sql)"},"id":"169188d1","cell_type":"code","execution_count":19,"outputs":[]},{"source":"### create years table by dynamically assigning values ###\n\n# integer values as key which helps with partitioning.\n# date table's grain goes, other date information\n# CTAS\n\nsql = \"\"\"\nCREATE OR REPLACE TABLE snow.years AS\n\nWITH RECURSIVE years AS (\n    SELECT 1970 as year\n    UNION ALL\n    SELECT year + 1\n    FROM years\n    WHERE year < 2000\n)\nSELECT \n    year,\n    CASE \n        WHEN MOD(year - 1970, 12) = 0 THEN 'Dog'\n        WHEN MOD(year - 1970, 12) = 1 THEN 'Pig'\n        WHEN MOD(year - 1970, 12) = 2 THEN 'Rat'\n        WHEN MOD(year - 1970, 12) = 3 THEN 'Ox'\n        WHEN MOD(year - 1970, 12) = 4 THEN 'Tiger'\n        WHEN MOD(year - 1970, 12) = 5 THEN 'Rabbit'\n        WHEN MOD(year - 1970, 12) = 6 THEN 'Dragon'\n        WHEN MOD(year - 1970, 12) = 7 THEN 'Snake'\n        WHEN MOD(year - 1970, 12) = 8 THEN 'Horse'\n        WHEN MOD(year - 1970, 12) = 9 THEN 'Sheep'\n        WHEN MOD(year - 1970, 12) = 10 THEN 'Monkey'\n        WHEN MOD(year - 1970, 12) = 11 THEN 'Rooster'\n    END as zodiac_type\nFROM years;\n\"\"\"\n\ncon.sql(sql)","metadata":{"executionCancelledAt":null,"executionTime":47,"lastExecutedAt":1704221234705,"lastScheduledRunId":null,"lastSuccessfullyExecutedCode":"### create years table by dynamically assigning values ###\n\n# integer values as key which helps with partitioning.\n# date table's grain goes, other date information\n# CTAS\n\nsql = \"\"\"\nCREATE OR REPLACE TABLE snow.years AS\n\nWITH RECURSIVE years AS (\n    SELECT 1970 as year\n    UNION ALL\n    SELECT year + 1\n    FROM years\n    WHERE year < 2000\n)\nSELECT \n    year,\n    CASE \n        WHEN MOD(year - 1970, 12) = 0 THEN 'Dog'\n        WHEN MOD(year - 1970, 12) = 1 THEN 'Pig'\n        WHEN MOD(year - 1970, 12) = 2 THEN 'Rat'\n        WHEN MOD(year - 1970, 12) = 3 THEN 'Ox'\n        WHEN MOD(year - 1970, 12) = 4 THEN 'Tiger'\n        WHEN MOD(year - 1970, 12) = 5 THEN 'Rabbit'\n        WHEN MOD(year - 1970, 12) = 6 THEN 'Dragon'\n        WHEN MOD(year - 1970, 12) = 7 THEN 'Snake'\n        WHEN MOD(year - 1970, 12) = 8 THEN 'Horse'\n        WHEN MOD(year - 1970, 12) = 9 THEN 'Sheep'\n        WHEN MOD(year - 1970, 12) = 10 THEN 'Monkey'\n        WHEN MOD(year - 1970, 12) = 11 THEN 'Rooster'\n    END as zodiac_type\nFROM years;\n\"\"\"\n\ncon.sql(sql)"},"id":"5dff6c0d","cell_type":"code","execution_count":20,"outputs":[]},{"source":"# display the snow.years table\ncon.table('snow.years')","metadata":{"executionCancelledAt":null,"executionTime":52,"lastExecutedAt":1704221234759,"lastScheduledRunId":null,"lastSuccessfullyExecutedCode":"# display the snow.years table\ncon.table('snow.years')"},"id":"6ab936ce-3406-4126-8419-34d54aa61474","cell_type":"code","execution_count":21,"outputs":[{"output_type":"execute_result","data":{"text/plain":"┌───────┬─────────────┐\n│ year  │ zodiac_type │\n│ int32 │   varchar   │\n├───────┼─────────────┤\n│  1970 │ Dog         │\n│  1971 │ Pig         │\n│  1972 │ Rat         │\n│  1973 │ Ox          │\n│  1974 │ Tiger       │\n│  1975 │ Rabbit      │\n│  1976 │ Dragon      │\n│  1977 │ Snake       │\n│  1978 │ Horse       │\n│  1979 │ Sheep       │\n│    ·  │   ·         │\n│    ·  │   ·         │\n│    ·  │   ·         │\n│  1991 │ Sheep       │\n│  1992 │ Monkey      │\n│  1993 │ Rooster     │\n│  1994 │ Dog         │\n│  1995 │ Pig         │\n│  1996 │ Rat         │\n│  1997 │ Ox          │\n│  1998 │ Tiger       │\n│  1999 │ Rabbit      │\n│  2000 │ Dragon      │\n├───────┴─────────────┤\n│ 31 rows (20 shown)  │\n└─────────────────────┘"},"metadata":{},"execution_count":21}]},{"source":"### making the fact table","metadata":{},"id":"e37f2ac5","cell_type":"markdown"},{"source":"# create workstation_sales table\n# additive facts vs. non-additive\n\nsql = f\"\"\"\n\nCREATE OR REPLACE TABLE {schema}.workstation_sales (\n   year_id INTEGER,\n    company_id VARCHAR,\n    integration_id VARCHAR,\n    product_offering_id VARCHAR,\n    sales DOUBLE,\n    research_budget DOUBLE,\n    employee_count DOUBLE,\n    product_offering_count INTEGER,\n    product_category_count INTEGER,\n);\n\n\"\"\"\n\ncon.sql(sql)\n\nsql += f\"\"\"\n\nINSERT INTO {schema}.workstation_sales \n   (year_id,\n    company_id ,\n    integration_id ,\n    product_offering_id ,\n    sales ,\n    research_budget ,\n    employee_count ,\n    product_offering_count ,\n    product_category_count\n    )\n\nSELECT\n    year,\n    MD5(COALESCE(   COALESCE(firmname, '-1') ||\n                    COALESCE(CAST(CUSIPHEADERID AS VARCHAR),'-1')||\n                    COALESCE(CAST(firmid AS VARCHAR),'-1')||\n                    COALESCE(CAST(found AS VARCHAR),'-1') ||\n                    COALESCE(CAST(own AS VARCHAR),'-1') ||\n                    COALESCE(CAST(fate AS VARCHAR),'-1') ||\n                    COALESCE(CAST(estate AS VARCHAR),'-1') ||\n                    COALESCE(ceo,'-1') ||\n                    COALESCE(zip,'-1')\n                    , '-1')) as id,\n\n    MD5(\n        CASE WHEN cpu = 0 THEN 'Bought' ELSE 'Produced' END || \n        CASE WHEN os = 0 THEN 'Bought' ELSE 'Produced' END || \n        CASE WHEN apps = 0 THEN 'Bought' ELSE 'Produced' END || \n        CASE WHEN comm = 0 THEN 'Bought' ELSE 'Produced' END || \n        CASE WHEN disk = 0 THEN 'Bought' ELSE 'Produced' END || \n        CASE WHEN memory = 0 THEN 'Bought' ELSE 'Produced' END || \n        CASE WHEN board = 0 THEN 'Bought' ELSE 'Produced' END\n    ),\n    \n    MD5(COALESCE(\n        CASE\n            WHEN system = 1 AND graphic = 0 THEN 'Desktop systems'\n            WHEN system = 0 AND graphic = 1 THEN 'Graphics systems'\n            WHEN system = 1 AND graphic = 1 THEN 'Desktop + Graphics systems'\n            ELSE NULL END,\n        '-1'\n    )),\n    \n    sales,\n    randd,\n    employ,\n    products,\n    ptypes\n    \n    \nFROM\n    raw_companies\n    ;\n\n\"\"\"\n\ncon.sql(sql)","metadata":{"executionCancelledAt":null,"executionTime":50,"lastExecutedAt":1704221234809,"lastScheduledRunId":null,"lastSuccessfullyExecutedCode":"# create workstation_sales table\n# additive facts vs. non-additive\n\nsql = f\"\"\"\n\nCREATE OR REPLACE TABLE {schema}.workstation_sales (\n   year_id INTEGER,\n    company_id VARCHAR,\n    integration_id VARCHAR,\n    product_offering_id VARCHAR,\n    sales DOUBLE,\n    research_budget DOUBLE,\n    employee_count DOUBLE,\n    product_offering_count INTEGER,\n    product_category_count INTEGER,\n);\n\n\"\"\"\n\ncon.sql(sql)\n\nsql += f\"\"\"\n\nINSERT INTO {schema}.workstation_sales \n   (year_id,\n    company_id ,\n    integration_id ,\n    product_offering_id ,\n    sales ,\n    research_budget ,\n    employee_count ,\n    product_offering_count ,\n    product_category_count\n    )\n\nSELECT\n    year,\n    MD5(COALESCE(   COALESCE(firmname, '-1') ||\n                    COALESCE(CAST(CUSIPHEADERID AS VARCHAR),'-1')||\n                    COALESCE(CAST(firmid AS VARCHAR),'-1')||\n                    COALESCE(CAST(found AS VARCHAR),'-1') ||\n                    COALESCE(CAST(own AS VARCHAR),'-1') ||\n                    COALESCE(CAST(fate AS VARCHAR),'-1') ||\n                    COALESCE(CAST(estate AS VARCHAR),'-1') ||\n                    COALESCE(ceo,'-1') ||\n                    COALESCE(zip,'-1')\n                    , '-1')) as id,\n\n    MD5(\n        CASE WHEN cpu = 0 THEN 'Bought' ELSE 'Produced' END || \n        CASE WHEN os = 0 THEN 'Bought' ELSE 'Produced' END || \n        CASE WHEN apps = 0 THEN 'Bought' ELSE 'Produced' END || \n        CASE WHEN comm = 0 THEN 'Bought' ELSE 'Produced' END || \n        CASE WHEN disk = 0 THEN 'Bought' ELSE 'Produced' END || \n        CASE WHEN memory = 0 THEN 'Bought' ELSE 'Produced' END || \n        CASE WHEN board = 0 THEN 'Bought' ELSE 'Produced' END\n    ),\n    \n    MD5(COALESCE(\n        CASE\n            WHEN system = 1 AND graphic = 0 THEN 'Desktop systems'\n            WHEN system = 0 AND graphic = 1 THEN 'Graphics systems'\n            WHEN system = 1 AND graphic = 1 THEN 'Desktop + Graphics systems'\n            ELSE NULL END,\n        '-1'\n    )),\n    \n    sales,\n    randd,\n    employ,\n    products,\n    ptypes\n    \n    \nFROM\n    raw_companies\n    ;\n\n\"\"\"\n\ncon.sql(sql)"},"id":"cfcea844","cell_type":"code","execution_count":22,"outputs":[]},{"source":"# con.sql('select count(*) from workstation_snowflake.workstation_sales a join workstation_snowflake.companies b on a.company_id = b.company_id')\n\n# con.sql('select count(*) from workstation_snowflake.workstation_sales a join workstation_snowflake.integrations b on a.integration_id = b.integration_id')\n\n# con.sql('select count(*) from workstation_snowflake.companies') # 388\n\n# con.sql(\"select 'locations', count(*) from workstation_snowflake.companies c join workstation_snowflake.locations l on c.location_id = l.location_id\")\n\ncon.sql(\"select 'locations', count(*) from snow.companies c join snow.compustat s on c.cusip_id = s.cusip_id\")\n\n# con.sql(\"select 'locations', count(*) from workstation_snowflake.companies c join workstation_snowflake.ceos s on c.ceo_id = s.ceo_id\")\n\n# con.sql('select company_name, c.location_id, l.location_id from workstation_snowflake.companies c left join workstation_snowflake.locations l on c.location_id = l.location_id')\n\n# con.sql('select count(*) from raw_companies')","metadata":{"executionCancelledAt":null,"executionTime":49,"lastExecutedAt":1704221234859,"lastScheduledRunId":null,"lastSuccessfullyExecutedCode":"# con.sql('select count(*) from workstation_snowflake.workstation_sales a join workstation_snowflake.companies b on a.company_id = b.company_id')\n\n# con.sql('select count(*) from workstation_snowflake.workstation_sales a join workstation_snowflake.integrations b on a.integration_id = b.integration_id')\n\n# con.sql('select count(*) from workstation_snowflake.companies') # 388\n\n# con.sql(\"select 'locations', count(*) from workstation_snowflake.companies c join workstation_snowflake.locations l on c.location_id = l.location_id\")\n\ncon.sql(\"select 'locations', count(*) from snow.companies c join snow.compustat s on c.cusip_id = s.cusip_id\")\n\n# con.sql(\"select 'locations', count(*) from workstation_snowflake.companies c join workstation_snowflake.ceos s on c.ceo_id = s.ceo_id\")\n\n# con.sql('select company_name, c.location_id, l.location_id from workstation_snowflake.companies c left join workstation_snowflake.locations l on c.location_id = l.location_id')\n\n# con.sql('select count(*) from raw_companies')"},"id":"b7363459","cell_type":"code","execution_count":23,"outputs":[{"output_type":"execute_result","data":{"text/plain":"┌─────────────┬──────────────┐\n│ 'locations' │ count_star() │\n│   varchar   │    int64     │\n├─────────────┼──────────────┤\n│ locations   │          388 │\n└─────────────┴──────────────┘"},"metadata":{},"execution_count":23}]},{"source":"## A star data model\n\nSteps of Kimball dimensional modeling:\n\n1. Select the business process\n2. Declare the grain\n3. Identify the dimensions\n4. Identify the facts\n\n![](star.png)","metadata":{},"id":"6b7d3c1e","cell_type":"markdown"},{"source":"# update schema\nschema = 'star'","metadata":{"executionCancelledAt":null,"executionTime":50,"lastExecutedAt":1704221234909,"lastScheduledRunId":null,"lastSuccessfullyExecutedCode":"# update schema\nschema = 'star'"},"id":"bc4696a9","cell_type":"code","execution_count":24,"outputs":[]},{"source":"# make new companies attribute which contains\n# pros/cons of denormalizing to star\nsql = f\"\"\"\n\nCREATE OR REPLACE TABLE {schema}.companies (\n       company_id VARCHAR,\n    company_name VARCHAR,\n    cusip_id VARCHAR,\n    cusip_name VARCHAR,\n    cusip_historical_id VARCHAR,\n    cusip_historical_name VARCHAR,\n    ceo_name VARCHAR,\n    founding_year INTEGER,\n    ownership_type VARCHAR,\n    exit_type VARCHAR,\n    exit_comment VARCHAR,\n    zipcode VARCHAR,\n    latitude DOUBLE,\n    longitude DOUBLE,\n    PRIMARY KEY (company_id),\n);\n\n\"\"\"\n\ncon.sql(sql)\n\nsql += f\"\"\"\n\nINSERT INTO {schema}.companies (\n    company_id ,\n    company_name ,\n    cusip_id ,\n    cusip_name ,\n    cusip_historical_id ,\n    cusip_historical_name ,\n    ceo_name ,\n    founding_year ,\n    ownership_type ,\n    exit_type ,\n    exit_comment ,\n    zipcode ,\n    latitude ,\n    longitude )\n\nSELECT DISTINCT\n    MD5(COALESCE(   COALESCE(firmname, '-1') ||\n                    COALESCE(CAST(CUSIPHEADERID AS VARCHAR),'-1')||\n                    COALESCE(CAST(firmid AS VARCHAR),'-1')||\n                    COALESCE(CAST(found AS VARCHAR),'-1') ||\n                    COALESCE(CAST(own AS VARCHAR),'-1') ||\n                    COALESCE(CAST(fate AS VARCHAR),'-1') ||\n                    COALESCE(CAST(estate AS VARCHAR),'-1') ||\n                    COALESCE(ceo,'-1') ||\n                    COALESCE(zip,'-1')\n                    , '-1')) as id,\n    firmname,\n    CUSIPHEADERID,\n    CUSIPHeaderName,\n    CUSIPHISTORYID,\n    CUSIPHistoryName,\n    ceo,\n    found,\n    CASE own\n        WHEN 0 THEN 'Private'\n        WHEN 1 THEN 'Public'\n        WHEN 2 THEN 'Subsidiary'\n        ELSE NULL END,\n    CASE estate\n        WHEN 0 THEN 'Censored'\n        WHEN 1 THEN 'Exited market'\n        WHEN 2 THEN 'Acquired'\n        WHEN 3 THEN 'Spun off'\n        WHEN 4 THEN 'Changed Name'\n        ELSE NULL END,\n    fate,\n    ZIP,\n    lat,\n    lon\n    \nFROM\n    raw_companies;\n\"\"\"\n\ncon.sql(sql)","metadata":{"executionCancelledAt":null,"executionTime":51,"lastExecutedAt":1704221234961,"lastScheduledRunId":null,"lastSuccessfullyExecutedCode":"# make new companies attribute which contains\n# pros/cons of denormalizing to star\nsql = f\"\"\"\n\nCREATE OR REPLACE TABLE {schema}.companies (\n       company_id VARCHAR,\n    company_name VARCHAR,\n    cusip_id VARCHAR,\n    cusip_name VARCHAR,\n    cusip_historical_id VARCHAR,\n    cusip_historical_name VARCHAR,\n    ceo_name VARCHAR,\n    founding_year INTEGER,\n    ownership_type VARCHAR,\n    exit_type VARCHAR,\n    exit_comment VARCHAR,\n    zipcode VARCHAR,\n    latitude DOUBLE,\n    longitude DOUBLE,\n    PRIMARY KEY (company_id),\n);\n\n\"\"\"\n\ncon.sql(sql)\n\nsql += f\"\"\"\n\nINSERT INTO {schema}.companies (\n    company_id ,\n    company_name ,\n    cusip_id ,\n    cusip_name ,\n    cusip_historical_id ,\n    cusip_historical_name ,\n    ceo_name ,\n    founding_year ,\n    ownership_type ,\n    exit_type ,\n    exit_comment ,\n    zipcode ,\n    latitude ,\n    longitude )\n\nSELECT DISTINCT\n    MD5(COALESCE(   COALESCE(firmname, '-1') ||\n                    COALESCE(CAST(CUSIPHEADERID AS VARCHAR),'-1')||\n                    COALESCE(CAST(firmid AS VARCHAR),'-1')||\n                    COALESCE(CAST(found AS VARCHAR),'-1') ||\n                    COALESCE(CAST(own AS VARCHAR),'-1') ||\n                    COALESCE(CAST(fate AS VARCHAR),'-1') ||\n                    COALESCE(CAST(estate AS VARCHAR),'-1') ||\n                    COALESCE(ceo,'-1') ||\n                    COALESCE(zip,'-1')\n                    , '-1')) as id,\n    firmname,\n    CUSIPHEADERID,\n    CUSIPHeaderName,\n    CUSIPHISTORYID,\n    CUSIPHistoryName,\n    ceo,\n    found,\n    CASE own\n        WHEN 0 THEN 'Private'\n        WHEN 1 THEN 'Public'\n        WHEN 2 THEN 'Subsidiary'\n        ELSE NULL END,\n    CASE estate\n        WHEN 0 THEN 'Censored'\n        WHEN 1 THEN 'Exited market'\n        WHEN 2 THEN 'Acquired'\n        WHEN 3 THEN 'Spun off'\n        WHEN 4 THEN 'Changed Name'\n        ELSE NULL END,\n    fate,\n    ZIP,\n    lat,\n    lon\n    \nFROM\n    raw_companies;\n\"\"\"\n\ncon.sql(sql)"},"id":"e0fa2005","cell_type":"code","execution_count":25,"outputs":[]},{"source":"# inner join the count of snow.workstation_sales to star.companies by company ID\ncon.sql(\"\"\"\nselect count(*) \n    from snow.workstation_sales a \n    join star.companies b \n        on a.company_id = b.company_id\n\"\"\")","metadata":{"executionCancelledAt":null,"executionTime":47,"lastExecutedAt":1704221235010,"lastScheduledRunId":null,"lastSuccessfullyExecutedCode":"# inner join the count of snow.workstation_sales to star.companies by company ID\ncon.sql(\"\"\"\nselect count(*) \n    from snow.workstation_sales a \n    join star.companies b \n        on a.company_id = b.company_id\n\"\"\")"},"id":"fbb73bca","cell_type":"code","execution_count":26,"outputs":[{"output_type":"execute_result","data":{"text/plain":"┌──────────────┐\n│ count_star() │\n│    int64     │\n├──────────────┤\n│          682 │\n└──────────────┘"},"metadata":{},"execution_count":26}]},{"source":"### Some query testing","metadata":{},"id":"36c410ee","cell_type":"markdown"},{"source":"# Select the zipcode and sum of sales \n# from snow.workstation_sales\n# inner join to snow.companies on company ID\n# inner join to snow.locations on location ID\n# group by 1st column\n# order by 2nd column in descending order\ncon.sql(\"\"\"\nselect zipcode, sum(sales) \n    from snow.workstation_sales a \n    join snow.companies c \n        on a.company_id=c.company_id\n    join snow.locations l \n        on c.location_id=l.location_id\n    group by 1 \n    order by 2 desc\n\"\"\")","metadata":{"executionCancelledAt":null,"executionTime":48,"lastExecutedAt":1704221235058,"lastScheduledRunId":null,"lastSuccessfullyExecutedCode":"# Select the zipcode and sum of sales \n# from snow.workstation_sales\n# inner join to snow.companies on company ID\n# inner join to snow.locations on location ID\n# group by 1st column\n# order by 2nd column in descending order\ncon.sql(\"\"\"\nselect zipcode, sum(sales) \n    from snow.workstation_sales a \n    join snow.companies c \n        on a.company_id=c.company_id\n    join snow.locations l \n        on c.location_id=l.location_id\n    group by 1 \n    order by 2 desc\n\"\"\")"},"id":"cee590b9","cell_type":"code","execution_count":27,"outputs":[{"output_type":"execute_result","data":{"text/plain":"┌─────────┬────────────────┐\n│ zipcode │   sum(sales)   │\n│ varchar │     double     │\n├─────────┼────────────────┤\n│ 10504   │ 598003998720.0 │\n│ 45479   │ 154701496704.0 │\n│ 94304   │ 141994998272.0 │\n│ 01754   │ 132260221440.0 │\n│ 95054   │ 121301996099.0 │\n│ 14650   │  95705997312.0 │\n│ 01821   │  81184972152.0 │\n│ 95134   │  67506182944.0 │\n│ 07660   │  48000000000.0 │\n│ 95008   │  36615437056.0 │\n│   ·     │             ·  │\n│   ·     │             ·  │\n│   ·     │             ·  │\n│ 03060   │            1.0 │\n│ 15276   │            1.0 │\n│ 97006   │            1.0 │\n│ 27709   │           NULL │\n│ 94577   │           NULL │\n│ 92803   │           NULL │\n│ 97124   │           NULL │\n│ 94063   │           NULL │\n│ 19380   │           NULL │\n│ 77094   │           NULL │\n├─────────┴────────────────┤\n│   156 rows (20 shown)    │\n└──────────────────────────┘"},"metadata":{},"execution_count":27}]},{"source":"# Select the zipcode and sum of sales \n# from snow.workstation_sales\n# inner join to snow.companies on company ID\n# group by 1st column\n# order by 2nd column in descending order\ncon.sql(\"\"\"\nselect zipcode, sum(sales) \nfrom snow.workstation_sales a \njoin star.companies c on a.company_id=c.company_id\ngroup by 1 order by 2 desc\"\"\")","metadata":{"executionCancelledAt":null,"executionTime":43,"lastExecutedAt":1704221235101,"lastScheduledRunId":null,"lastSuccessfullyExecutedCode":"# Select the zipcode and sum of sales \n# from snow.workstation_sales\n# inner join to snow.companies on company ID\n# group by 1st column\n# order by 2nd column in descending order\ncon.sql(\"\"\"\nselect zipcode, sum(sales) \nfrom snow.workstation_sales a \njoin star.companies c on a.company_id=c.company_id\ngroup by 1 order by 2 desc\"\"\")"},"id":"8cb2af57","cell_type":"code","execution_count":28,"outputs":[{"output_type":"execute_result","data":{"text/plain":"┌─────────┬────────────────┐\n│ zipcode │   sum(sales)   │\n│ varchar │     double     │\n├─────────┼────────────────┤\n│ 10504   │ 598003998720.0 │\n│ 45479   │ 154701496704.0 │\n│ 94304   │ 141994998272.0 │\n│ 01754   │ 132260221440.0 │\n│ 95054   │ 121301996099.0 │\n│ 14650   │  95705997312.0 │\n│ 01821   │  81184972152.0 │\n│ 95134   │  67506182944.0 │\n│ 07660   │  48000000000.0 │\n│ 95008   │  36615437056.0 │\n│   ·     │             ·  │\n│   ·     │             ·  │\n│   ·     │             ·  │\n│ 70503   │            1.0 │\n│ 90260   │            1.0 │\n│ 94608   │            1.0 │\n│ 92803   │           NULL │\n│ 97124   │           NULL │\n│ 94063   │           NULL │\n│ 19380   │           NULL │\n│ 77094   │           NULL │\n│ 27709   │           NULL │\n│ 94577   │           NULL │\n├─────────┴────────────────┤\n│   156 rows (20 shown)    │\n└──────────────────────────┘"},"metadata":{},"execution_count":28}]},{"source":"%%timeit -n 10\n\ncon.sql(\"\"\"\nselect zipcode, sum(sales) \nfrom snow.workstation_sales a \njoin snow.companies c on a.company_id=c.company_id\njoin snow.locations l on c.location_id=l.location_id\ngroup by 1 order by 2 desc\"\"\")","metadata":{"executionCancelledAt":null,"executionTime":null,"lastExecutedAt":null,"lastScheduledRunId":null,"lastSuccessfullyExecutedCode":null,"outputsMetadata":{"0":{"height":37,"type":"stream"}}},"id":"881516e0","cell_type":"code","execution_count":29,"outputs":[{"output_type":"stream","name":"stdout","text":"165 µs ± 28.3 µs per loop (mean ± std. dev. of 7 runs, 10 loops each)\n"}]},{"source":"%%timeit -n 10\n\ncon.sql(\"\"\"\nselect zipcode, sum(sales) \nfrom snow.workstation_sales a \njoin star.companies c on a.company_id=c.company_id\ngroup by 1 order by 1 desc\"\"\")","metadata":{"executionCancelledAt":null,"executionTime":52,"lastExecutedAt":1704221235214,"lastScheduledRunId":null,"lastSuccessfullyExecutedCode":"%%timeit -n 10\n\ncon.sql(\"\"\"\nselect zipcode, sum(sales) \nfrom snow.workstation_sales a \njoin star.companies c on a.company_id=c.company_id\ngroup by 1 order by 1 desc\"\"\")","outputsMetadata":{"0":{"height":37,"type":"stream"}}},"id":"266cbad2","cell_type":"code","execution_count":30,"outputs":[{"output_type":"stream","name":"stdout","text":"139 µs ± 13.2 µs per loop (mean ± std. dev. of 7 runs, 10 loops each)\n"}]}],"metadata":{"language_info":{"name":"python","version":"3.8.10","mimetype":"text/x-python","codemirror_mode":{"name":"ipython","version":3},"pygments_lexer":"ipython3","nbconvert_exporter":"python","file_extension":".py"},"kernelspec":{"display_name":"Python 3 (ipykernel)","language":"python","name":"python3"},"editor":"DataCamp Workspace"},"nbformat":4,"nbformat_minor":5}