A consolidated archive of coursework, projects, and side experiments completed during my undergraduate degree in Management Information Systems at Utah State University (Jon M. Huntsman School of Business).
This monorepo gathers three previously-separate repositories — Python, SQL, and Data-Architecture-Modeling — into a single, organized portfolio. Full per-file commit history from each source repo is preserved.
About me: Data Engineer at Entrata. SQL, Python, AI/ML, and BI work. See github.com/TylerShep for current projects.
The MIS program at USU sits at the intersection of business and technology. Coursework that produced the artifacts in this repo includes:
- DATA 3500 — Data Analytics & Programming (Python, financial data, trading strategies)
- DATA 6300 / DATA 6400 — Data Warehousing & Architecture (dimensional modeling, star/snowflake schemas, ETL design)
- MIS 5550 / MIS 5600 — Database Systems & Application Development (PostgreSQL, MS SQL Server, schema design)
- MIS 5800 — Cloud / AWS Project work (S3 → RDS → QuickSight pipelines)
The work is intentionally rough in places — these are student projects from 2022–2024, kept as historical artifacts of the learning process. Where production-quality patterns weren't followed (hardcoded paths, naive SQL formatting, sparse error handling), I've left the originals intact rather than retroactively polish them. Newer, production-grade work lives in my other public repos (e.g. domo-scheduled-ext-reporting, domo-semantic-layer-beast, kbtc-v2).
All committed credentials, API keys, and former-employer email addresses have been retroactively scrubbed from the git history of this monorepo via
git filter-repo. Anyos.environ[...]references in the code are placeholders — bring your own credentials.
usu-management-info-sys/
├── Python/ # Python coursework + side projects
│ ├── nfl_iron_will_db/ # Capstone-style: NFL betting data warehouse
│ ├── alpha_avantage_multi_trading_strategies.py # DATA 3500 final
│ ├── AWS_S3_to_postgres_pipeline.py
│ ├── AWS_lambda_transaction_generator.py
│ ├── backfill_stats_date_func.py
│ ├── jira_issues_api_to_df.py
│ ├── send_Domo_visualization_to_Slack_channel.py
│ ├── twilio_flex_insights_to_csv.py
│ └── youtube_url_to_text_summary.py
│
├── SQL/ # Hand-written queries & stored procs
│ ├── MSSQL/ # T-SQL (CTEs, PIVOT, recursive dates)
│ ├── PostgreSQL/ # PL/pgSQL stats variance work
│ └── SnowSQL/ # Snowflake DW DDL
│
└── Data-Architecture-Modeling/ # ERDs, dimensional models, AWS architecture
├── Iron_Will_DB_ERD.png
├── JensenCo_Snowflake_DW_Design.png
├── v1 & v2 Forecasting Data Model.png
├── Client Satisfaction Dataset.png
└── AWS_FinalProject_Outline_S3_to_RDS_to_Quicksight_.mp4
A classroom data-warehouse project simulating a fictional sportsbook, "Iron Will." Builds a normalized PostgreSQL data mart (iwdm) sourced from a transactional MS SQL Server database (ironwill) plus open NFL/spread CSVs.
Architecture:
- Source OLTP: MS SQL Server (customers, raw bet log)
- Target DW: PostgreSQL (
iwdm) with five dimensional tables:nfl_team,nfl_stadium,nfl_games,placed_bet,bet_customer - Ingest pattern: per-table Python scripts using
pyodbc(source) +psycopg2(target)
Files:
| File | Purpose |
|---|---|
create_tables.py |
DDL for the 5 destination tables |
ingest_nfl_teams.py |
Loads team metadata (incl. pre-2002 conference history) from CSV |
ingest_nfl_stadiums.py |
Loads stadiums + nearest weather-station mapping |
ingest_nfl_games.py |
Loads game results + spread/over-under data; computes bet_overunder flag |
ingest_betting_customers.py |
Pulls customers from MS SQL → Postgres |
ingest_placed_nfl_bet.py |
Pulls bet log from MS SQL → Postgres |
update_placed_nfl_bets.py |
Adds derived columns (bet_results, iw_commission) using a tiered commission model |
iron_will_db_test_queries.sql |
Six analytical queries: top-paying customers, luckiest bettors, team-vs-spread stats, weekly bet win/loss %, costliest customers |
What it demonstrates: end-to-end ELT design, lateral joins, conditional aggregations, dimensional modeling.
Pulls daily-adjusted prices from the Alpha Vantage API for ten tickers (AAPL, ADBE, AMZN, BA, COST, CSCO, GOOG, MSFT, NVDA, TWTR), then backtests three trading strategies and ranks the most profitable ticker × strategy combo.
Strategies implemented:
- Simple Moving Average with short-selling
- Mean Reversion (5-day window, 2% threshold)
- Bollinger Bands (5-day window, ±5% bands)
What it demonstrates: REST API consumption, JSON parsing, file-based price persistence, basic quant logic, dictionary-driven result ranking.
A two-part AWS pipeline:
- Lambda generates 1,000 fake credit-card transactions per invocation using
Fakerand uploads to S3. - Local script reads the CSV from S3, normalizes a CFB-stats dataset, and bulk-inserts into a Postgres RDS instance.
What it demonstrates: boto3 (Lambda + S3), AWS RDS connectivity, ETL pattern with synthetic data generation.
A dated-recovery helper for a Kinesis-fed daily snapshot table. Detects gaps between the last loaded date and yesterday, returns a list of dates to re-process. Pattern was used to recover from upstream stream backups in a production BI environment.
Wraps Atlassian's REST API v3 with HTTP basic auth across two domains and normalizes the issues response into a flat pandas DataFrame.
Programmatically pulls a card image from a Domo dashboard and posts it to a Slack channel. Was the predecessor experiment to my domo-scheduled-ext-reporting production project.
Multi-step OAuth flow against Twilio Flex Insights (GoodData/ytica): super-secure-token → temporary token → report URI → CSV download.
Fetches English captions from a YouTube URL, frequency-ranks sentences via NLTK, and computes a BERT embedding of the resulting summary using bert-base-uncased.
Small, focused SQL pieces written across the three flavors I worked with most.
| Path | What it shows |
|---|---|
MSSQL/cartesianStatsDates.sql |
Recursive CTE generating a 730-day date range, cross-joined to an entity table to produce a fully-dense ID × date series |
MSSQL/communityTypesPvt.sql |
Dynamic SQL PIVOT reformatting a long-format type table into per-entity boolean columns |
MSSQL/statsProcedure.sql |
Header for an optimized stored procedure that reduced runtime from 1m 50s → 4s (legacy + optimized SQL redacted) |
PostgreSQL/day_to_day_variance_stats.sql |
Day-over-day variance calculation header (logic redacted) |
SnowSQL/JensenCoWarehouse.sql |
Snowflake DW DDL for the JensenCo class project |
Several files contain only the documented header — the actual queries reference proprietary table names from a former-employer DB and were intentionally redacted at the time of original publication. Diagrams of the schemas they targeted live in Data-Architecture-Modeling/.
Static images and one video walk-through of various data models and architectures designed during the program.
| File | Description |
|---|---|
Iron_Will_DB_ERD.png |
ERD for the NFL betting data warehouse (paired with Python/nfl_iron_will_db/) |
JensenCo_Snowflake_DW_Design.png |
Snowflake-schema dimensional model for JensenCo (paired with SQL/SnowSQL/JensenCoWarehouse.sql) |
v1 & v2 Forecasting Data Model.png |
Two iterations of a forecasting data model — shows how the model evolved from v1 to v2 |
Client Satisfaction Dataset.png |
Dataset structure for a client satisfaction analysis |
AWS_FinalProject_Outline_S3_to_RDS_to_Quicksight_.mp4 |
Recorded walkthrough of the S3 → RDS → QuickSight pipeline architecture |
| Layer | Tools |
|---|---|
| Languages | Python 3, T-SQL, PL/pgSQL, Snowflake SQL |
| Databases | PostgreSQL, Microsoft SQL Server, Snowflake, AWS RDS |
| Cloud | AWS (S3, Lambda, RDS, QuickSight) |
| Python libs | pandas, psycopg2, pyodbc, boto3, requests, Faker, NLTK, transformers (BERT), PyTorch, pytube, PIL |
| APIs consumed | Alpha Vantage, Atlassian Jira v3, Twilio Flex Insights, Slack, Domo, YouTube |
| Modeling | ERDs, star/snowflake schemas, dimensional modeling |
These are historical artifacts — most scripts were written to run in a specific local or AWS environment and have not been packaged for redistribution. If you want to actually run something:
- Install the obvious dependencies (
pip install pandas psycopg2-binary boto3 requests faker). - Set the relevant environment variables (every credential in the code reads from
os.environ[...]). - Be aware that hardcoded file paths (e.g.
/home/ubuntu/environment/final_project/...) point to the original Cloud9 environment used for class.
For new work using similar patterns done properly, see my current public projects on github.com/TylerShep.
Personal/educational use. No license attached — these are coursework artifacts kept for reference.