Operational databases run the business; data warehouses let the business understand itself, and data mining finds patterns in the data that nobody asked about directly. In today’s vocabulary this is the ancestor of the data lake, analytics engineering and machine learning. This article revisits the five units of the JNTUK course and connects each to current tools.
Unit 1: Data warehousing concepts and architecture
What a data warehouse is
A data warehouse is a central store of integrated, historical data for analysis. W. H. Inmon’s definition gives four properties:
| Property | Meaning |
|---|---|
| Subject-oriented | Organised around business subjects (sales, customers), not applications |
| Integrated | Data from many sources made consistent |
| Time-variant | Keeps history, with a time dimension |
| Non-volatile | Loaded and read; not updated in place |
OLTP versus OLAP
| Point | OLTP (operational) | OLAP (analytical) |
|---|---|---|
| Purpose | Run day-to-day transactions | Analysis and decisions |
| Data | Current, detailed | Historical, summarised |
| Queries | Short reads and writes | Complex, read-heavy aggregates |
| Design | Normalised | Denormalised (dimensional) |
| Users | Many clerks and apps | Analysts and managers |
Architecture
The usual flow: source systems (databases, files, logs) → ETL → warehouse (with data marts for departments) → front-end tools (reports, dashboards, OLAP, mining). Variants: enterprise warehouse, data mart (one department) and virtual warehouse (views on sources). Metadata describes the data, its origin and its meaning.
Dimensional modelling
Data is organised into facts and dimensions.
| Schema | Structure |
|---|---|
| Star | A central fact table (measures such as sales amount) joined directly to dimension tables (time, product, store, customer) |
| Snowflake | Dimensions are further normalised into sub-tables; saves space, needs more joins |
| Fact constellation (galaxy) | Several fact tables share dimensions |
A measure is additive (sum across all dimensions), semi-additive or non-additive. A concept hierarchy (day → month → quarter → year) lets you summarise at different levels.
Today: Amazon Redshift, Google BigQuery, Snowflake and Azure Synapse are cloud data warehouses, and they still use star schemas. The “OLTP database feeds the warehouse” pattern is behind every company that runs MySQL for the app and a warehouse for reports.
Unit 2: OLAP and ETL
OLAP operations
OLAP views data as a multi-dimensional cube (for example, product × region × time).
| Operation | Meaning |
|---|---|
| Roll-up | Aggregate to a higher level (city to country) |
| Drill-down | Go to more detail (year to month) |
| Slice | Fix one dimension to get a sub-cube |
| Dice | Select ranges on several dimensions |
| Pivot (rotate) | Rearrange the axes of the view |
| Server type | Storage |
|---|---|
| ROLAP | Relational database; scales well, slower queries |
| MOLAP | Special multi-dimensional arrays; fast, limited size |
| HOLAP | Hybrid |
Design aids include materialized views (pre-computed aggregates), bitmap indexes and partitioning.
ETL
Extract, Transform, Load is the process of populating the warehouse.
| Step | Tasks |
|---|---|
| Extract | Read from sources, full or incremental (change data capture) |
| Transform | Clean, convert types and units, deduplicate, apply business rules, derive fields |
| Load | Insert into the warehouse; initial load then periodic refresh |
A staging area holds raw data between steps. ELT reverses the order for the cloud: load raw data first and transform inside the warehouse. Slowly changing dimensions handle history when an attribute (such as a customer’s city) changes: overwrite (type 1), add a new row (type 2) or add a column (type 3).
Today: Airflow, dbt, AWS Glue, Kafka Connect and Debezium (change data capture) are modern ETL and ELT tools. A “data pipeline” in a DevOps job is exactly this, and pipeline monitoring (late or failed loads) is an operations task.
Unit 3: Data mining introduction and preprocessing
Data mining is the process of discovering useful patterns in large data sets. It is one step of Knowledge Discovery in Databases (KDD): cleaning → integration → selection → transformation → mining → pattern evaluation → presentation.
Kinds of patterns
| Task | Example |
|---|---|
| Association | Customers who buy bread often buy butter |
| Classification | Is this loan applicant likely to default? |
| Prediction (regression) | Next month’s sales |
| Clustering | Customer segments with similar behaviour |
| Outlier (anomaly) detection | Fraudulent transactions, failing servers |
| Characterization and discrimination, trends | Summaries and comparisons |
Data and preprocessing
Data has types (numeric, categorical, ordinal) and sources (relational, transactional, text, time series, graphs). Real data is dirty, so preprocessing often takes most of the effort.
| Step | Techniques |
|---|---|
| Cleaning | Fill missing values (mean, model), smooth noise (binning, regression), remove duplicates and outliers |
| Integration | Merge sources, resolve conflicts, find redundancy by correlation |
| Reduction | Dimensionality reduction (PCA), feature selection, sampling, compression |
| Transformation | Normalisation (min-max, z-score), discretisation, aggregation |
Data similarity uses distance measures (Euclidean, Manhattan, cosine).
Today: “data scientists spend most of their time cleaning data” is true. Pandas, SQL, Spark and feature stores do this work, and the same preprocessing steps are the first stage of every machine-learning pipeline. KDD is today’s data science lifecycle.
Unit 4: Association rules and classification
Association rule mining (market basket)
A rule A → B says that transactions containing A tend to contain B. Two measures:
| Measure | Meaning |
|---|---|
| Support | Fraction of all transactions containing A and B |
| Confidence | Of those with A, the fraction that also have B: support(A ∪ B) / support(A) |
A rule is “strong” if both exceed user thresholds. Lift (confidence / support of B) shows whether the association is better than chance (above 1 means positive).
Apriori algorithm: finds frequent itemsets level by level, using the Apriori property (every subset of a frequent itemset is also frequent) to prune candidates, then makes rules from them. It needs repeated scans of the data. FP-growth compresses the data into a prefix tree (FP-tree) and avoids candidate generation, so it is faster.
Classification
Classification learns a model from labelled training data and predicts the class of new cases. The data is split into training and test sets to measure accuracy honestly.
| Method | Idea |
|---|---|
| Decision tree | Repeatedly split on the attribute that best separates the classes, measured by information gain (entropy), gain ratio or Gini index (ID3, C4.5, CART). Easy to read; pruning prevents overfitting |
| Naive Bayes | Uses Bayes’ theorem and assumes features are independent; fast and surprisingly good for text |
| k-nearest neighbours | Classify by the majority among the k closest examples; no training, slow at prediction |
| Rule-based classifiers | IF-THEN rules |
| Neural networks and SVM | Powerful non-linear models |
| Ensembles | Bagging and random forest, boosting (AdaBoost, gradient boosting) combine many models |
Evaluation: the confusion matrix (true/false positives and negatives), accuracy, precision (TP / (TP + FP)), recall (TP / (TP + FN)), F1 score, ROC curve and cross-validation. Overfitting (memorising the training data) and underfitting are the central risks. Regression predicts numeric values.
Today: recommendation engines (“customers also bought”) are association mining, spam and fraud filters are classifiers, and gradient-boosted trees (XGBoost, LightGBM) win many industry prediction tasks. Precision and recall also describe alert quality: how many alerts are real, and how many real problems were alerted.
Unit 5: Clustering and advanced topics
Clustering groups similar objects without labels (unsupervised learning). Good clusters have high similarity inside and low similarity between them.
| Method | Idea | Notes |
|---|---|---|
| k-means | Choose k centres; assign each point to the nearest, recompute centres, repeat | Fast; needs k; assumes round clusters; sensitive to outliers |
| k-medoids (PAM) | Centres are real data points | Robust to outliers |
| Hierarchical | Agglomerative (merge the closest clusters) or divisive; shown as a dendrogram | No need to choose k in advance |
| DBSCAN | Grows clusters from dense regions; labels sparse points as noise | Finds irregular shapes and outliers |
| Grid and model-based methods (EM, SOM) | Cells; probability models | Used for large data and soft clustering |
Cluster quality is measured by the silhouette coefficient or the sum of squared distances.
Outliers and advanced mining
Outlier detection uses statistical, distance-based and density-based methods; in operations this is anomaly detection on metrics and logs. Other topics in the syllabus: text mining, web mining (content, structure, usage), spatial and multimedia mining, stream mining, and privacy and ethics issues, because mining personal data can harm people.
Today: k-means groups customers, servers or log lines; DBSCAN finds anomalies in telemetry; clustering embeddings is the first step of many search and AI systems. The “big data” tools, Hadoop and Spark, were built for mining at scale, and the data lakehouse (Delta Lake, Iceberg) merges the warehouse and the lake. Machine learning is, in large part, data mining with better models and more data.
One-minute recap
| Unit | Key idea | You meet it in |
|---|---|---|
| 1. Warehousing | Subject-oriented, integrated, time-variant, non-volatile; star schema | Redshift, BigQuery, Snowflake |
| 2. OLAP and ETL | Roll-up, drill-down, slice, dice; ETL and ELT | Airflow, dbt, Glue, Debezium |
| 3. Mining and preprocessing | KDD steps, cleaning, normalisation, reduction | Pandas and Spark pipelines, feature engineering |
| 4. Association and classification | Support, confidence, Apriori; decision trees, Naive Bayes | Recommenders, spam and fraud filters, precision and recall |
| 5. Clustering | k-means, hierarchical, DBSCAN, outliers | Customer segments, anomaly detection |
Next in Engineering Revisited: E-Commerce.
Written by Claude, Anthropic’s AI assistant, as part of Engineering Revisited, a quick refresher of the B.Tech subjects one at a time.