Data Modeling, Storage, and File Formats

Introduction

Data modeling is the discipline of representing business facts, relationships, events, and constraints in structures that support reliable storage and efficient use. In big data and cloud engineering, modeling decisions extend beyond tables and relationships. They include the choice of storage service, partitioning strategy, file format, compression method, schema policy, indexing approach, and metadata architecture.

A technically elegant model can perform poorly if data is partitioned incorrectly. Conversely, inexpensive object storage can support demanding analytical workloads when data is organized into suitable columnar files, governed by accurate metadata, and processed through engines that exploit partition and column pruning. The central engineering challenge is therefore to align the logical model with workload patterns, data-change behavior, governance requirements, and cost constraints.

This chapter examines relational, dimensional, NoSQL, document, key-value, graph, and time-series models. It then connects these models to cloud object storage, data lakes and lakehouses, partitioning, indexing, compression, schemas, metadata, and analytical performance.

Learning Objectives

By the end of this chapter, you should be able to:

1. Distinguish major logical data models and select among them for business and analytical requirements.
2. Explain normalization, denormalization, dimensional modeling, and schema evolution.
3. Evaluate object storage, databases, data warehouses, data lakes, and lakehouses.
4. Design partitioning and indexing strategies for distributed analytical workloads.
5. Compare row-oriented and column-oriented file formats and explain compression trade-offs.
6. Use metadata, catalogs, lineage, and quality information to improve discovery and governance.
7. Analyze a storage design in terms of performance, scalability, reliability, security, and cost.

1. Data Modeling as a Workload-Alignment Problem

A data model has at least three related layers:

• Conceptual model: business entities and relationships, such as Customer, Account, Product, and Sale.
• Logical model: attributes, keys, cardinalities, constraints, and structures independent of a particular storage product.
• Physical model: tables, files, partitions, indexes, clustering, replication, and encoding choices used by an implementation.

Modeling should begin with questions such as:

• What decisions or processes will the data support?
• Are requests transactional, analytical, operational, or graph-oriented?
• What is the expected volume, velocity, and variety of data?
• Which queries are frequent, expensive, or time-sensitive?
• How often do records change, and must historical states be preserved?
• What consistency, retention, privacy, and regulatory requirements apply?

A model is not universally good or bad. It is fit or unfit for a workload. A normalized relational design is often appropriate for order processing, whereas a denormalized dimensional model is generally better for repetitive aggregate queries. A graph model is valuable when the primary questions concern paths and relationships rather than simple attribute filtering.

2. Relational and Dimensional Models

2.1 Relational modeling

The relational model represents data as relations, commonly implemented as tables. A relation contains tuples (rows) and attributes (columns). A primary key uniquely identifies a row; a foreign key references a key in another table. Integrity constraints can enforce uniqueness, required values, valid domains, and referential relationships.

Normalization reduces redundancy by decomposing data into related tables. In simplified terms:

• First normal form (1NF): attributes contain atomic values and repeating groups are removed.
• Second normal form (2NF): in addition to 1NF, non-key attributes depend on the whole candidate key, relevant mainly to composite keys.
• Third normal form (3NF): in addition to 2NF, non-key attributes do not depend transitively on another non-key attribute.

Normalization can improve consistency. If a customer address is stored once, an address update does not require changing many order rows. However, normalization may require joins, and joins across very large distributed datasets can be expensive. Physical designs therefore often denormalize carefully, using replicated descriptive attributes, materialized views, or precomputed aggregates.

2.2 Dimensional modeling

Dimensional modeling organizes analytical data around a measurable business process. A fact table contains measurements and foreign keys; dimension tables contain descriptive context. For retail sales, a fact table may contain date key, product key, store key, customer key, quantity, revenue, discount, and cost.

A star schema has one fact table connected directly to relatively denormalized dimensions. A snowflake schema normalizes some dimensions into additional tables. Star schemas generally simplify queries and reduce the number of joins, while snowflake schemas may reduce dimension redundancy.

The grain must be stated explicitly. For example, “one row per product per store per day” is a grain statement. Measures must be compatible with that grain. Additive measures can be summed across all dimensions; semi-additive measures can be summed across some dimensions but not others; non-additive measures, such as ratios, should usually be recalculated from additive components.

For example, margin percentage should not normally be summed across products. If revenue is R and cost is C, margin percentage is:

Here, R and C must use the same currency and reporting scope, and R must be nonzero. For a group of products, compute total revenue and total cost first, then calculate the ratio. Averaging product-level percentages can produce a misleading result when product revenues differ.

Slowly changing dimensions preserve changes in descriptive attributes. Type 1 overwrites the old value; Type 2 creates a new versioned row with effective dates; Type 3 stores limited previous values in additional columns. Type 2 is useful when historical reports must reflect the dimension value that was valid at the time of a fact event.

3. NoSQL Models

NoSQL is not one model but a family of approaches that relax or reinterpret traditional relational assumptions to support scale, flexible schemas, high availability, or particular access patterns.

3.1 Key-value model

A key-value store maps a unique key to a value. The value may be opaque to the database or may have limited structure. This model is effective for session data, shopping carts, feature flags, caching, and rapidly accessed aggregates. It is less suitable for ad hoc multi-attribute queries unless additional indexes or access structures are provided.

The key design is crucial. A poor key can create a hot partition, where disproportionate traffic is directed to one storage node. Keys should distribute load while preserving the lookup patterns required by the application.

3.2 Document model

Document stores represent records as JSON-like documents containing nested objects and arrays. They support flexible attributes and are suitable when an aggregate is naturally retrieved or updated as a unit, such as a customer profile with preferences and addresses.

Document duplication can improve read performance but creates update and consistency challenges. Large documents can also cause inefficient rewrites when only a small nested value changes. Document modeling should therefore reflect access boundaries rather than simply reproduce source JSON.

3.3 Wide-column model

Wide-column systems organize data by partition key and clustering or sort columns. They are designed for high write throughput and predictable queries over very large datasets. A query-driven design is normally required: tables are created for known access patterns, and duplication across tables is accepted when it reduces expensive joins.

3.4 Graph model

Graph databases represent nodes, relationships, and properties. They are appropriate for fraud networks, recommendation systems, identity resolution, supply chains, and knowledge graphs. Graph queries can express paths such as “find accounts connected through shared devices within three hops.”

Graph storage is not automatically superior for every connected dataset. Large-scale scans, simple aggregations, and time-window reporting may be more efficient in columnar analytical systems. The decisive issue is whether relationship traversal is central to the workload.

3.5 Time-series model

Time-series systems optimize records associated with timestamps, such as sensor readings, application metrics, financial prices, and industrial telemetry. Important design concepts include timestamp precision, retention windows, downsampling, tags or dimensions, late-arriving events, and ordering.

A useful time-series schema separates relatively stable dimensions, such as device and location, from measurements that change frequently. High-cardinality tags can create excessive index size and should be selected carefully.

4. Storage Architectures in the Cloud

4.1 Databases and warehouses

Operational databases provide transactions, constraints, and low-latency point operations. Analytical warehouses provide optimized scans, joins, aggregation, workload management, and SQL interfaces. Many cloud warehouses separate compute from storage, allowing compute capacity to be scaled independently of persistent data.

4.2 Object storage

Cloud object storage stores objects in buckets or containers addressed by keys. An object generally consists of content, a key, system properties, and user-defined metadata. Object storage is durable, elastic, and economical for large files, backups, data lakes, and analytical datasets. It is not a conventional hierarchical file system, although key prefixes can create a directory-like appearance.

Object storage usually offers weaker update semantics than a database. Replacing or committing many related objects requires careful coordination. Analytical table formats address this problem by maintaining transaction logs or metadata layers that describe valid snapshots, schema changes, and file additions or removals.

4.3 Data lakes and lakehouses

A data lake stores raw, refined, and curated data in inexpensive scalable storage. Its flexibility can become a liability if files, schemas, ownership, and quality rules are unmanaged. A lakehouse combines object-storage economics with table-like capabilities such as ACID transactions, schema enforcement or evolution, time travel, and unified batch-stream processing.

The architectural distinction is not merely product branding. A successful platform requires explicit contracts for data ownership, quality, retention, access control, and lifecycle management.

5. File Formats and Physical Layout

5.1 Row and column orientation

Row-oriented formats store fields from the same record together. They are effective when applications retrieve complete records or update individual rows. Column-oriented formats store values from the same column together. They are effective when analytical queries read a small subset of columns from many rows.

Suppose a table has 100 columns but a query needs only 8. A columnar engine may read approximately 8 relevant columns, subject to metadata, encoding, and implementation details, rather than processing all 100. This is not a guarantee of an exact 92% reduction because file headers, indexes, compression blocks, and nonselected operations also consume resources.

Parquet and ORC are widely used columnar formats. They commonly store data in row groups or stripes, divide columns into pages or streams, and maintain statistics such as minimum and maximum values. These structures support column pruning and predicate pushdown. Avro is a row-oriented, schema-based format often used for event exchange and streaming because records can be serialized and schemas can be managed explicitly. JSON and CSV are portable and human-accessible but are generally less efficient for large-scale analytics.

5.2 Compression and encoding

Compression reduces storage and I/O but requires CPU for encoding and decoding. Columnar data often compresses well because adjacent values in a column have similar types and distributions. Techniques include dictionary encoding, run-length encoding, delta encoding, bit packing, and general-purpose codecs such as Snappy, Zstandard, and Gzip.

For a compressed dataset, a simple storage ratio can be expressed as:

Both sizes must be measured in the same units. A ratio greater than 1 indicates reduction; for example, 1,000 GB compressed to 250 GB gives a ratio of 4:1. Compression choices should be benchmarked because a smaller file may require more CPU, while a faster codec may increase storage cost.

5.3 File sizing and the small-file problem

Too many small files create excessive metadata operations, task scheduling overhead, and inefficient scans. A few extremely large files may reduce parallelism and make recovery or incremental processing difficult. Appropriate file size depends on the engine, object-store behavior, workload, and data arrival pattern. Compaction combines small files while preserving logical table semantics.

6. Partitioning, Clustering, and Indexing

Partitioning divides data into independently addressable segments. Common partition keys include event date, tenant, region, or business unit. A query that specifies a partition predicate can avoid reading irrelevant partitions; this is called partition pruning.

Partition design involves competing risks:

• Too few partitions produce large scans and limited parallelism.
• Too many partitions create metadata and scheduling overhead.
• A low-cardinality key may produce unbalanced partitions.
• A high-cardinality key may create many tiny partitions.
• A monotonically increasing key can create write concentration.

For time-oriented data, hierarchical paths such as year/month/day can support retention and date pruning, but adding hour or minute partitions may over-fragment the dataset. Partition columns should reflect common selective predicates, not every available attribute.

Clustering, sorting, or data skipping organizes records within larger partitions. It can improve pruning on columns such as customer, geography, or status without creating a separate physical partition for each value.

Indexes provide auxiliary access paths. B-tree indexes are effective for ordered ranges and equality predicates in many transactional systems. Hash indexes are strong for equality lookups but not naturally for range scans. Bitmap indexes can be useful for low-cardinality analytical columns, although write-heavy workloads may make them unsuitable. Distributed analytical systems may rely more on file statistics, zone maps, bloom filters, clustering, and materialized views than on traditional indexes.

A practical design process is:

1. Identify representative queries and their filters, joins, grouping, and ordering.
2. Estimate data volume, growth, skew, and update frequency.
3. Select a partition key with useful pruning and acceptable balance.
4. Select clustering or indexing for frequent residual predicates.
5. Test with realistic data distributions, including skew and late data.
6. Monitor scan volume, execution time, file counts, and cost.

7. Schemas, Metadata, and Governance

A schema defines data types, fields, constraints, relationships, and interpretation. Schema-on-write validates structure before data becomes available; schema-on-read interprets structure when data is queried. Schema-on-write supports consistency and predictable downstream behavior, whereas schema-on-read accommodates exploratory and heterogeneous data. Mature platforms often use both: raw data is retained flexibly, while curated products enforce stronger contracts.

Schema evolution includes adding fields, widening compatible types, renaming attributes, changing meanings, and removing fields. Backward compatibility means newer data can be read by older consumers; forward compatibility means older data can be read by newer consumers. Compatibility rules should be automated where possible, and semantic changes should not be disguised as harmless technical changes.

Metadata includes:

• Technical metadata: data types, schemas, partitions, file locations, formats, and statistics.
• Business metadata: definitions, owners, sensitivity classifications, and approved uses.
• Operational metadata: refresh times, job status, quality results, and service-level measures.
• Lineage metadata: upstream sources, transformations, and downstream consumers.

A data catalog makes assets discoverable, but catalog quality depends on stewardship. A field named value is technically valid but operationally weak if its unit, currency, time basis, and business meaning are unknown. Metadata should answer who owns the data, what it means, how current it is, whether it is trustworthy, and whether access is permitted.

Governance must include classification, least-privilege access, encryption, key management, retention, deletion, audit logging, and jurisdictional controls. Personal data may require minimization, masking, tokenization, purpose limitation, and support for lawful deletion. Copies in staging areas, caches, backups, and derived tables must be included in the governance design.

8. Worked Design Example

A retailer receives 2 billion transaction events per year. Analysts commonly query sales by date, store, product category, and customer segment. Operational applications require rapid lookup of an individual order.

A suitable polyglot design could include:

• A normalized relational system for orders, payments, and inventory transactions.
• An append-oriented event stream for transaction changes.
• Object storage containing immutable raw events and curated Parquet datasets.
• A dimensional warehouse with a sales fact table and conformed date, product, store, and customer dimensions.
• A key-value cache for frequently accessed order summaries.

The curated sales table could be partitioned by transaction date, clustered by store and product category, and stored in compressed Parquet. The partition choice supports common date filters; clustering supports additional pruning without creating a partition for every store or product. A Type 2 customer dimension would preserve segment history if reports must represent the segment known at the time of purchase.

If annual uncompressed data is 8 PB and compression produces a 5:1 ratio, compressed storage is:

This calculation assumes that the stated ratio applies consistently to the complete dataset. Actual capacity planning must add replication or erasure-coding overhead, metadata, temporary processing space, snapshots, and retained historical versions.

9. Illustrative Case: HarborView Logistics

Background

HarborView Logistics operates warehouses, trucks, and delivery hubs across several countries. Its platform receives shipment events, vehicle telemetry, customer orders, and claims data. Executives report that dashboards are slow, data scientists cannot reliably locate trusted datasets, and storage costs are rising.

Problem

The organization stores daily CSV exports in thousands of object-storage folders. Each team invents field names and date conventions. Analysts scan entire datasets for relatively small date ranges. Duplicate customer and shipment records are common, and no authoritative definition exists for “delivered.”

Analysis

The architecture team profiles query logs and discovers that 80% of dashboard queries filter by event date and hub, while vehicle engineers query telemetry by vehicle and time interval. Shipment relationships, including rerouting and shared consignments, are also important. The team therefore rejects a single universal model.

Approach and solution

Operational orders remain in a relational service with enforced keys and transaction constraints. Shipment events are retained in an append-only event format with a registered schema. Curated events are converted to Parquet and partitioned by event month and region, with clustering by hub. A dimensional model supports executive reporting, using a declared fact grain of one shipment-status event. A time-series store serves recent telemetry, while older telemetry is compacted into object storage. A graph projection represents shipment dependencies and route relationships.

A catalog records business definitions, owners, sensitivity, freshness, lineage, and quality checks. A controlled vocabulary defines delivery statuses. Automated compaction reduces small files, and data-quality checks quarantine records with invalid timestamps or unknown hubs.

Outcome

Dashboard scans become substantially smaller because date and region predicates prune files. Analysts find certified datasets through the catalog rather than searching folders. Operational systems are protected from heavy analytical queries, while graph and time-series workloads use structures designed for their access patterns. Storage costs decline after compression and lifecycle policies move old raw data to lower-cost tiers.

Learning points

The case demonstrates that performance improvements came from coordinated choices, not from a single database product. Logical models, file formats, partitioning, metadata, governance, and workload isolation must be designed together. It also illustrates the value of polyglot persistence: different workloads can use different models while sharing governed definitions and lineage.

10. Challenges, Trade-offs, and Contemporary Developments

No storage design eliminates trade-offs. Denormalization improves reads but increases duplication. Strong consistency can limit availability or throughput in some distributed designs. Flexible schemas accelerate ingestion but transfer complexity to consumers. Fine-grained partitioning improves pruning until metadata overhead dominates. Compression lowers I/O but consumes CPU. Replication improves resilience but increases cost.

Modern table formats increasingly support ACID transactions over object storage, schema evolution, time travel, change data capture, and streaming ingestion. Open table abstractions reduce dependence on a single processing engine, although interoperability is not perfect. Vectorized execution, adaptive query optimization, learned statistics, and intelligent clustering can improve performance, but automation does not remove the need for sound modeling.

Artificial intelligence and machine learning introduce additional requirements. Feature stores must preserve point-in-time correctness so that training data does not include information unavailable at prediction time. Embedding stores and vector indexes support similarity search, but they require attention to dimensionality, freshness, privacy, and evaluation quality. AI-generated metadata can assist discovery, yet business owners must validate definitions and classifications.

Best Practices

1. Model from documented business questions and access patterns.
2. State the grain of every fact table and event dataset.
3. Separate operational, analytical, archival, and serving workloads when their requirements differ.
4. Prefer columnar formats for large analytical scans and row-oriented formats for record-oriented exchange or transactions.
5. Partition on selective, stable, commonly filtered attributes, especially time where appropriate.
6. Monitor skew, small files, scan volume, cache behavior, and total cost.
7. Treat schemas and metadata as governed products, not incidental documentation.
8. Test schema evolution and compatibility with representative consumers.
9. Apply encryption, least privilege, retention, lineage, and privacy controls to every copy.
10. Benchmark with realistic data distributions rather than synthetic uniform data alone.

Chapter Summary

Data modeling connects business meaning to physical storage and workload performance. Relational models emphasize integrity and transactions; dimensional models support analytical reporting through facts, dimensions, and explicit grain. Key-value, document, wide-column, graph, and time-series models serve specialized access patterns and should be selected deliberately.

Cloud object storage provides scalable, durable foundations for data lakes and lakehouses, but efficient analytics depends on physical organization. Columnar formats reduce unnecessary I/O through column pruning, compression, and data statistics. Partitioning and clustering can reduce scans, while poor choices create skew, metadata overhead, or small-file problems. Schemas, catalogs, lineage, quality information, and governance make data discoverable and trustworthy. The strongest architecture aligns logical models, file layouts, query behavior, operational constraints, and regulatory responsibilities.

Key Terms

Data model: A structured representation of entities, attributes, relationships, and constraints.

Normalization: Decomposition of data to reduce redundancy and update anomalies.

Denormalization: Deliberate duplication or prejoining of data to improve read performance.

Primary key: Attribute or attribute set that uniquely identifies a record.

Foreign key: Attribute that references a key in another relation.

Fact table: Dimensional-model table containing measurements and dimensional references.

Dimension table: Table containing descriptive context for analytical facts.

Grain: Precise statement of what one row or event represents.

Slowly changing dimension: Technique for managing changes in dimensional attributes over time.

Key-value store: Database organized around lookup keys and associated values.

Document store: Database that manages semi-structured, nested documents.

Graph database: System representing nodes, relationships, and properties.

Time-series database: System optimized for timestamped observations.

Object storage: Scalable storage of objects addressed by keys in buckets or containers.

Columnar format: File organization that stores values from the same column together.

Partition pruning: Avoidance of partitions that cannot satisfy a query predicate.

Clustering: Organization of records within larger storage regions to improve locality.

Schema evolution: Controlled change to a dataset’s structure over time.

Data lineage: Record of data origins, transformations, and downstream use.

Small-file problem: Performance degradation caused by excessive numbers of tiny files.

Review Questions

Short Answer Questions

1. What is the difference between a conceptual, logical, and physical data model?
2. Why must the grain of a fact table be stated explicitly?
3. Give one appropriate use for a key-value store and one limitation.
4. How does columnar storage support analytical performance?
5. What types of information should a data catalog contain?

Long Answer Questions

1. Compare normalized relational modeling with dimensional modeling for operational and analytical workloads.
2. Explain the benefits and risks of partitioning, clustering, and indexing in a cloud data platform.
3. Compare Parquet, Avro, JSON, and CSV in terms of structure, performance, and typical use.
4. Discuss schema-on-write, schema-on-read, and schema evolution in a governed data lake.
5. Explain how privacy, lineage, retention, and access control affect physical storage design.

Analytical / Application Questions

1. A company stores five years of sales data in one large CSV file. Design a migration to a cloud analytical platform, specifying logical model, file format, partitioning, compression, metadata, and governance decisions.
2. A time-series platform has excellent write throughput but slow queries by customer and region. Diagnose possible modeling, partitioning, cardinality, and indexing problems and propose tests.
3. An enterprise wants one database for orders, dashboards, graph-based fraud detection, and IoT telemetry. Evaluate the proposal and recommend an architecture, explaining the trade-offs of polyglot persistence.

Multiple Choice Questions with Answers

Question 1

Which statement best describes the grain of a fact table?

A. The physical compression codec used by the table

B. The precise meaning represented by one row

C. The number of dimensions connected to the table

D. The maximum number of rows permitted

Correct Answer: B. The precise meaning represented by one row

Question 2

Which model is most directly suited to traversing relationships such as accounts connected through shared devices?

A. Key-value model

B. Graph model

C. Flat-file model

D. Document model

Correct Answer: B. Graph model

Question 3

A query selects 6 columns from a table containing 60 columns and scans many rows. Which format is generally most advantageous?

A. Columnar format

B. Uncompressed row text

C. Single-record key-value format

D. Image format

Correct Answer: A. Columnar format

Question 4

What is a principal risk of creating a separate partition for every customer in a large dataset?

A. Loss of all primary keys

B. Excessive partition count and small files

C. Inability to store timestamps

D. Automatic removal of compression

Correct Answer: B. Excessive partition count and small files

Question 5

A dataset of 900 GB compresses to 300 GB. What is its compression ratio?

A. 0.33:1

B. 1:3

C. 3:1

D. 600:1

Correct Answer: C. 3:1

Question 6

Which slowly changing dimension technique preserves complete historical versions using effective dates?

A. Type 0

B. Type 1

C. Type 2

D. Type 3

Correct Answer: C. Type 2

Question 7

Which workload is a strong fit for a key-value store?

A. Unconstrained multi-table analytical joins

B. Fast lookup of a session by a known identifier

C. Multi-hop route traversal

D. Large columnar scans by date

Correct Answer: B. Fast lookup of a session by a known identifier

Question 8

What is the main purpose of partition pruning?

A. To avoid reading partitions that cannot satisfy query predicates

B. To convert all data into JSON

C. To enforce third normal form

D. To increase the number of small files

Correct Answer: A. To avoid reading partitions that cannot satisfy query predicates

Question 9

Which metadata item records upstream sources and downstream consumers?

A. Lineage metadata

B. Compression metadata

C. Display metadata

D. Cache metadata

Correct Answer: A. Lineage metadata

Question 10

Why are additive measures important in dimensional modeling?

A. They can generally be summed across their applicable dimensions

B. They must always be stored as text

C. They eliminate the need for dimensions

D. They cannot be aggregated

Correct Answer: A. They can generally be summed across their applicable dimensions