All posts

Data Modeling for Data Engineers: From Foundations to Lakehouse Design

A detailed guide to data modeling for engineers: relational and dimensional design, Data Vault, CDC and SCD history, lakehouse storage, governance, performance, and forward and reverse engineering.

48 min read Modeling

TL;DR

  • Begin with the business process and declare what one row means before choosing columns, keys, or partitions.
  • Keep measurements at a consistent grain. Put descriptive, filterable attributes in dimensions and store facts that can be aggregated correctly.
  • Use stable business keys to identify source entities and surrogate keys to identify warehouse dimension versions. They solve different problems.
  • For Type 2 history, join each fact to the dimension version valid at the event time. An equality join on customer ID alone silently rewrites history.
  • CDC is a capture mechanism, not a target model. Snapshots establish a base; ordered log changes maintain state and history.
  • A logical model is separate from indexes, partitions, shards, file ordering, and lakehouse table formats. Choose each physical technique from measured query and operational requirements.
  • Data mesh and data fabric address different architectural concerns, while lineage and metadata make ownership, dependencies, and impact discoverable.

Part 1: Foundations of Data Modeling

1.1 What is data modeling, and why is it important?

A data model is not a diagram that happens to have tables on it. It is an executable agreement about what a row represents, what its keys identify, what time its attributes describe, and which operations are safe to perform on its measures.

That agreement is what lets one team ingest changing operational data while another team builds reports without re-learning source-system quirks for every query. When the agreement is absent, SUM(order_total) may count an order once per line, a customer rename can rewrite historical sales, and a retry can double a batch. Those failures appear in dashboards, but their causes are in the model and load process.

This guide uses an order-line sales process to connect the decisions that are usually taught separately: source events, grain, facts, dimensions, history, keys, quality checks, and lakehouse layout. The example is intentionally small; the rules are the same when the fact table has billions of rows.

The target reader is a data engineer building batch or streaming pipelines and serving multiple query engines. This guide connects modeling choices to the code that ingests, changes, tests, and physically stores the resulting data.

Without an explicit model, every downstream job has to rediscover entity identity, relationship cardinality, temporal meaning, and metric definitions. That spreads business rules into SQL notebooks and dashboards, where inconsistent copies are difficult to detect. Modeling makes those rules reviewable and testable at a shared boundary. It does not eliminate source quality problems; it tells the pipeline how to detect and represent them.

1.2 The three abstraction levels: conceptual, logical, and physical

These levels answer different questions. Mixing them makes design reviews argue about implementation before agreeing on the business meaning.

Level Question Typical artifact Sales example
Conceptual Which business concepts exist and how do they relate? Entity map or domain model Customers place orders; orders contain lines; products are sold
Logical What attributes, keys, constraints, and cardinalities define the model? ER model, relational schema, dimensional model One row per order line, with customer, product, and date references
Physical How does a specific engine store and access the model? DDL, indexes, partitions, files, clustering, distribution PostgreSQL B-tree indexes, or Parquet files in an Iceberg table

The same logical model can be implemented as PostgreSQL tables, Spark DataFrames, or Iceberg tables. The logical relationships should survive a storage migration. Physical choices can change when access patterns or engine capabilities change; a customer identifier is not a good physical partition key merely because it is an important logical key.

1.3 Core building blocks: entities, attributes, relationships, and constraints

An entity is a distinguishable business object or event, such as a customer, product, order, or payment. An attribute records a property of that entity. A relationship records how entities associate. A constraint states which values and combinations are valid.

For the order process, Customer and Product are entities; customer_id, email, and country_code are attributes; Customer places Order is a relationship. A constraint may state that an order-line quantity is positive or that (order_id, line_number) is unique. A model should distinguish a business fact from a convenient source-system column: a generated row number does not become a durable product identity just because it is unique in one extract.

The data type is part of the attribute contract. A monetary measure needs a precision-preserving decimal and a currency policy; an event timestamp needs a time-zone interpretation; an identifier that may contain leading zeroes is usually text, not an integer. Types constrain values, but they do not capture all business rules. Use NOT NULL, uniqueness, foreign keys, checks, and pipeline assertions where the storage system can enforce them.

1.4 Identity management: primary keys, foreign keys, and alternate keys

Key Purpose Example
Primary key Identifies one row in a relation; unique and non-null (order_id, line_number)
Foreign key References a candidate key in another relation fact_order_line.product_sk -> dim_product.product_sk
Alternate key A candidate identifier not chosen as the primary key Unique email within a defined identity scope
Business key Identifies an entity in a source or business process customer_id from the CRM
Surrogate key Warehouse-controlled key for a model row or version customer_sk = 102 for one Type 2 version

Do not assume these mechanisms have equal enforcement in every platform. PostgreSQL constraints can reject a row that violates a primary key, foreign key, or unique constraint. Many lakehouse engines store table schemas and metadata without enforcing the same cross-table foreign-key contract during every write. In that case, the pipeline must test referential integrity explicitly.

Composite keys are not a modeling failure. They are often the honest source identity, such as (order_id, line_number). Surrogate keys are useful when source keys are unstable, multiple sources collide, or dimension history needs a distinct key per version. A surrogate key generated independently on every rebuild is not stable; preserve a key map or regenerate the facts that refer to it.

1.5 Understanding cardinality and modality

Cardinality describes how many instances can participate in a relationship. Modality (also called optionality or minimum cardinality) describes whether participation is optional or required. Read a relationship by its minimum and maximum on both ends:

Relationship Customer end Order end Meaning
Customer places Order 0..many orders exactly 1 customer A customer may have no orders; each order has one customer
Order contains Line 1..many lines exactly 1 order A valid order has at least one line; each line belongs to one order
Product appears on Line 0..many lines exactly 1 product A product may never have sold; each line names one product

A many-to-many relationship usually needs an associative entity or bridge. For example, a sales line attributed to several campaigns cannot simply be joined to all campaigns and then summed: its amount is duplicated. Store the relationship at its own grain, then define whether queries filter through the bridge or allocate measures by weights.

1.6 Types of data: structured, semi-structured, and unstructured

These terms describe how much organization is represented in the data, not whether it is important or valuable.

Kind Shape Modeling treatment
Structured Named fields with stable types and relationships Declare columns, keys, nullability, and semantics explicitly
Semi-structured Self-describing or nested records whose fields may vary Preserve the source payload, then project governed fields and track schema versions
Unstructured Content without a stable row-and-column schema, such as documents or images Store the object and model searchable metadata, identity, ownership, and lifecycle separately

JSON is not automatically a model, and Parquet is not automatically a good model. A nested payload may be the right raw representation for replay, while consumer-facing fields need explicit types, names, and evolution rules. A separate metadata row can model an image or document’s owner, classification, creation time, and object location without pretending its bytes are relational attributes.

Part 2: Relational vs. Dimensional vs. Vault Modeling

2.1 Relational modeling: normalization (1NF-BCNF) vs. denormalization

Normalization organizes a relational model to reduce update anomalies and inconsistent repetition. The normal forms are tests against a logical schema, not a contest to reach the largest possible number.

Form Working rule Typical defect it prevents
1NF Each row-column position holds one value from the column’s domain; repeating groups become related rows phone_1, phone_2, phone_3 columns that break when another phone is added
2NF In 1NF, and every non-key attribute depends on the whole candidate key, not a proper subset Product name repeated on every (order_id, line_number) row when it depends only on product_id
3NF In 2NF, and non-key attributes do not depend transitively on a candidate key through another non-key attribute customer_id -> postal_code -> city stored redundantly on every order
BCNF For every nontrivial functional dependency X -> Y, X is a superkey Determinants that are not candidate keys, even when the schema meets 3NF

The forms are stated with candidate keys and functional dependencies, not visual table aesthetics. BCNF is stricter than 3NF; decomposition can remove redundancy but may fail to preserve every dependency as a local constraint. Check whether the decomposed relations still enforce the business rules.

Denormalization deliberately duplicates or precomputes information to serve a read workload. It trades simpler or faster reads for extra write, storage, and consistency obligations. A star schema denormalizes descriptive attributes into dimensions, while its fact table remains narrow and keyed. That is not the same as copying every source field into one enormous table.

2.2 Dimensional modeling core: fact tables vs. dimension tables

Begin with a business event, not a target table. For sales, the source process might record an order with one or more product lines. A useful first statement is:

One row in fact_order_line represents one product line accepted on one customer order.

That sentence is the grain declaration. It names the real-world measurement event represented by one row. It is more useful than saying “the grain is order_id, line_number”: those columns may implement the grain, but the business sentence tells the team what the row means and which facts belong on it. Kimball’s guidance is to declare the grain before selecting dimensions or facts.

Begin with a business event, not a target table. For sales, the source process might record an order with one or more product lines. A useful first statement is:

One row in fact_order_line represents one product line accepted on one customer order.

That sentence is the grain declaration. It names the real-world measurement event represented by one row. It is more useful than saying “the grain is order_id, line_number”: those columns may implement the grain, but the business sentence tells the team what the row means and which facts belong on it. Kimball’s guidance is to declare the grain before selecting dimensions or facts.

Once grain is clear, the design follows:

Operational events                 Analytical model
-------------------                 ----------------
orders + order lines  ----------->  fact_order_line
customer change log   ----------->  dim_customer (versioned)
product catalog       ----------->  dim_product
calendar              ----------->  dim_date

One accepted order line becomes one fact row.
Each dimension key identifies the entity version relevant to that line.

The fact should contain measures that are true at this grain. For an order line, quantity, extended price, and line discount qualify. A customer lifetime total does not: it describes many orders and changes as more orders arrive. Store its derivation in the model or calculate it at query time, rather than repeating it on every line and making it additive by accident.

Grain controls joins as well as measures. If a dimension join matches two rows for one order line, the join has changed the fact grain. Every measure then appears twice. A model that passes a schema check can still be wrong by a factor of two; uniqueness and join-cardinality checks are correctness tests.

2.2.1 Choose the fact-table shape

The business process determines whether rows represent individual events, periodic states, or the progress of a workflow.

Fact shape One row represents Typical use Main trade-off
Transaction One event, such as an accepted order line Flexible drill-down and reconciliation Large, sparse, and append-oriented; corrections need an explicit policy
Periodic snapshot One entity at a regular interval, such as account balance per day “As of” reporting and trend analysis Predictably dense; the same unchanged balance may be repeated
Accumulating snapshot One workflow instance, updated as milestones occur Order fulfillment, claims, applications Easy lifecycle analysis; a row is revisited and prior milestone values must be managed

These are modeling shapes, not storage formats. An Iceberg or Delta table can store any of them. A streaming pipeline can feed a transaction fact or update an accumulating snapshot; the table format does not decide what one row means.

2.2.2 Choose a serving model for its consumers

The staging model and the serving model need not have the same shape. Keep source-facing data faithful enough to replay and debug ingestion. Then publish a model whose joins, names, and measures match the questions consumers need to answer.

Approach Use it for Cost to account for
Normalized relational model Operational writes and consistent entity updates Analytical queries traverse more joins and need shared semantic rules
Dimensional star BI filtering, grouping, and additive analysis Requires clear grain, conformed dimensions, and disciplined history handling
Data Vault Auditable integration of multiple changing sources More structures and transformations before it is comfortable for analysts
One wide table A narrow, stable workload or a delivery boundary Repeated attributes, harder history semantics, and possible measure multiplication

These patterns can coexist in layers. A raw CDC landing table is not a star schema, and a medallion label such as “silver” does not define a grain. Decide what contract each layer provides, then materialize the serving model for its actual workload.

Several dimension patterns change how a star model is queried without changing the fact grain:

  • Conformed dimensions reuse the same definition and key domain across business processes, so sales and returns can be analyzed together.
  • Role-playing dimensions use one logical dimension in multiple roles. An order fact may carry order_date_key, ship_date_key, and delivery_date_key, all referencing the date dimension.
  • Degenerate dimensions keep a transaction identifier, such as order_id, on the fact when a separate dimension would contain little more than that identifier.
  • Factless facts and bridge tables model events or many-to-many relationships without ordinary numeric measures. A bridge needs a filtering or allocation rule if joining it would repeat sales.
  • Unknown and not-applicable members give missing references explicit keys. Do not collapse “not yet arrived,” “not collected,” “not applicable,” and “invalid source value” if they require different handling.

Classify each measure before publishing it:

Measure Behavior Safe operation
Quantity sold Additive across order lines, products, and dates Sum
Net line amount Additive when each line appears once and currencies are handled consistently Sum within a currency or after conversion to a declared reporting currency
Unit price Non-additive Recalculate from summed amount / summed quantity, or report a weighted measure
Account balance Semi-additive across time Select a defined closing or as-of balance; do not sum daily balances across dates
Margin percentage Non-additive ratio Recompute from its additive numerator and denominator

Never average a ratio by averaging row-level percentages unless the business definition explicitly requires an unweighted average. Retain the additive components needed to derive unit price, conversion rates, and percentages.

2.3 Schema topologies: star schema vs. snowflake schema

A star schema connects a fact directly to descriptive dimensions. A snowflake schema normalizes some dimension attributes into additional related tables. The difference is dimension topology, not fact grain.

Choice Typical form Advantage Cost
Star fact_sales -> dim_product(category, subcategory, ...) Fewer joins and a compact semantic surface for common analysis Repeated descriptions across product rows; attribute updates touch more rows
Snowflake fact_sales -> dim_product -> dim_subcategory -> dim_category Shared hierarchy values and less duplicated dimension storage More joins, more relationship paths, and more opportunities for confusing filter behavior

Do not normalize a dimension merely because the source is normalized, and do not flatten every hierarchy automatically. Choose the form that makes the consumer contract understandable and can meet measured query and update requirements. A large, shared hierarchy with independently governed attributes may justify snowflaking; a small presentation dimension often benefits more from a direct star join.

Data Vault separates durable business identity, relationships, and changing descriptions into different structures. A hub records a business key; a link records an association between hubs; a satellite records descriptive attributes and their history for a hub or link. Load time and source lineage are commonly retained so integrations can be audited.

hub_customer             link_order_customer       satellite_customer
business_key = C01  <--  order_key + customer_key  customer attributes
                                                       load time + source

This separation can help when multiple sources contribute changing records and the integration history must remain available. It also introduces more joins and more structures than most analysts should query directly. A common architecture uses a Vault as an integration/history layer and publishes star schemas as marts for reporting. Hash keys are a common implementation choice, not a substitute for canonicalizing business keys, testing collisions, or maintaining source identity.

Part 3: Advanced Data Engineering Concepts & Storage Patterns

Part 2 defined the serving model. This part follows how that model changes as source data arrives, business attributes evolve, and storage formats change.

                         dim_date
                            |
dim_customer ---- fact_order_line ---- dim_product
                            |
                       dim_channel

fact_order_line grain: one row per order_id + line_number
Measures: quantity, gross_amount, discount_amount

3.1 Slowly Changing Dimensions (SCD Types 0–6 Deep Dive)

The SCD number is a response policy for changed attributes, not a property of the whole dimension table. Apply policies at the attribute level. Type 2 is the workhorse when historical facts must retain the dimension values that were true when the event occurred; other types fit distinct questions.

This guide follows Kimball’s named patterns. Type numbers beyond 1–3 are not used consistently by every vendor or author, so document the chosen behavior instead of relying on a number alone.

Type Pattern What is retained Use when
0 Retain original The first accepted value never changes The original value is durable or explicitly labeled “original”
1 Overwrite Only the latest value Correcting an error or intentionally restating all history
2 Add a row A row and surrogate key for each effective version Historical facts must resolve to the attribute version valid at event time
3 Add a column Current and one or a few prior values A limited comparison such as current region vs. previous region is enough
4 Mini-dimension A separate dimension for volatile attribute profiles Several attributes change rapidly or create too many combinations in the base dimension
5 Mini-dimension plus current pointer Type 4 profile history plus a Type 1 current-profile reference on the base dimension Facts need historical profile and consumers also need easy access to the current profile
6 Type 1 + Type 2 + Type 3 hybrid Version rows plus current-value overlays on historical rows Consumers need both “as-was” and “as-is” reporting without a separate current-key join

Types 5 and 6 duplicate or update information to make current-state questions easier. That convenience creates maintenance work: every affected Type 6 version row must receive the current overlay, and a Type 5 current pointer must be updated when the profile changes. If that synchronization is late, “current” attributes become stale while the Type 2 history may still be correct.

Keep business identity separate from warehouse identity

A business key comes from the source domain: customer_id = C01 identifies the customer across source updates. A surrogate key is assigned by the warehouse to identify one dimension row or version, for example customer_sk = 101 for the customer’s Gold version and customer_sk = 102 for the later Silver version.

The fact normally stores the surrogate key. If the customer changes segment, the current source key remains C01, but the new dimension version receives a new surrogate key. This preserves both identity and history. A generated surrogate key must be stable across incremental runs; monotonically_increasing_id() and an unpersisted row_number() are not durable key-assignment strategies. Use a persisted key map or the warehouse platform’s governed key-generation process.

Composite business keys are valid when they describe source identity. If a source identifies an order line with (order_id, line_number), do not discard one part and hope the remaining ID is unique. Preserve the components or map them to a stable key, and test uniqueness at the declared grain.

Type 2: make dimension history explicit

For Type 2, model a half-open validity interval [valid_from, valid_to). The old version is valid at its start and stops being valid exactly when the next version starts. Half-open intervals avoid double matches at a boundary:

customer_id  customer_sk  segment  valid_from           valid_to
C01          101          Gold     2025-01-01 00:00:00  2025-02-01 00:00:00
C01          102          Silver   2025-02-01 00:00:00  9999-12-31 00:00:00

To attach the right customer to a sale, match both the business key and event time:

o.customer_id = c.customer_id
AND o.order_ts >= c.valid_from
AND o.order_ts <  c.valid_to

Joining on customer_id alone can match every historical version and multiply the sale. Joining only to the current row keeps one result but restates old sales using today’s attributes. Neither is a harmless implementation choice; they answer different business questions.

3.2 Change Data Capture (CDC): mechanics and design patterns

Change Data Capture records changes at the source boundary so downstream systems can maintain a copy without repeatedly scanning the entire source. There are three common capture paths:

Capture path What it reads Main operational concern
Log-based Database transaction log or WAL Log retention, replication slots, privileges, and source-specific ordering
Trigger-based Changes written by database triggers to an outbox or audit table Trigger overhead, transaction behavior, and cleanup of the capture table
Query-based Periodic polls using a timestamp or monotonically increasing key Deletes and intermediate updates may be invisible; timestamps can be late or non-unique

Log-based CDC is not simply “a stream of row JSON.” A Debezium event can include a key, before/after values, operation type, source metadata and log position. A connector may first emit a consistent snapshot, then continue from the corresponding log position. Consumers must distinguish snapshot reads from inserts, updates, deletes, and tombstones, and must use the source’s ordering metadata rather than a connector processing timestamp when ordering matters.

Source transaction log
        |
        v
CDC connector: snapshot + ordered change events
        |
        v
Immutable landing log: key, op, source position, event time, payload
        |
        v
Deduplicate and order -> current-state tables / SCDs / facts

The raw event log and the analytical model answer different questions. The event log is useful for replay and audit. A current-state table applies the latest accepted operation per key. A Type 2 dimension turns selected attribute changes into effective versions. A transaction fact records business events at their declared grain. Do not interpret every update as a new business event, or every delete as a physical row removal.

Define how the consumer handles at-least-once delivery: retain a source position such as an LSN or log offset, use a stable row key and deterministic ordering, make replays idempotent, and test duplicate delivery. A primary-key change may be represented as deletion of the old key plus insertion of the new key. A source snapshot can also overlap the live stream unless the connector’s snapshot protocol resolves that boundary. The pipeline’s idempotency and ordering rules must cover both cases.

CDC application patterns

  • Append-only event fact: keep each distinct business event and deduplicate by event identity. Preserve correction events instead of silently rewriting a prior event when the business requires an audit trail.
  • Current-state upsert: group changes by business key and apply them in source order. A delete becomes a tombstone or explicit deleted state if downstream consumers need to know that the key existed.
  • Type 2 history: compare tracked attributes, expire the current version, and insert a new version with effective-time boundaries.
  • Snapshot plus stream: bootstrap from a consistent snapshot and continue from its source-log boundary. Reconciliation compares the snapshot’s key set and values with the resulting target state.

CDC does not provide free exactly-once business effects. A sink may commit a row while the offset commit fails, causing a replay. Coordinate the sink transaction and offset where supported; otherwise use idempotent writes and durable source positions.

3.3 Storage evolution: warehouses vs. data lakes vs. data lakehouses

These names describe broad storage and access patterns, not one required product or modeling method.

Platform shape What it commonly provides Modeling consequence What it does not provide by itself
Data warehouse Managed SQL tables, transaction processing, catalogs, and query optimization Curated schemas and constraints are often close to the serving engine Automatic agreement on metric meaning or source history
Data lake Low-cost files and object storage for varied data and compute engines Raw, semi-structured, and curated data can coexist; contracts need explicit ownership Atomic table transactions merely because the files are Parquet
Data lakehouse Lake storage plus an open table format and metadata/catalog layer Multiple engines can share schema, snapshots, and table-level commits Every relational constraint, cross-table transaction, or semantic definition

Parquet is a file format. Iceberg, Delta Lake, and Hudi add table metadata and commit behavior around data files; they do not make the data model correct. Table-level atomicity is also not automatically a transaction spanning several tables. If a fact and dimension publish in separate commits, define how consumers identify a compatible model version or batch.

Medallion architecture: bronze, silver, and gold

Medallion architecture is a way to organize a lakehouse’s data lifecycle into progressively more validated and purpose-shaped layers. It is a logical pattern, not a different fact-table design, a particular file format, or a requirement that every transformation create another physical table. The bronze/silver/gold labels also do not define row grain; each table still needs its own contract.

Layer Responsibility Sales example Keep explicit
Bronze Preserve source-shaped records and ingestion metadata for replay and audit Raw order CDC envelope with key, operation, source position, and before/after payload Source identity, capture time, source event time, and schema/version metadata
Silver Validate, type, deduplicate, and standardize detailed records Curated order lines and customer change history with normalized timestamps and currencies Business keys, source ordering, invalid-row handling, and Type 2 effective-time rules
Gold Publish business-facing dimensional models and workload-specific aggregates fact_order_line, dim_customer, dim_product, and sales summaries Declared grain, conformed dimensions, measure definitions, freshness, and consumer contract 
CDC snapshot + change log
     |
     v
   bronze.order_events       source-faithful, replayable
     |
     v
   silver.order_line         validated grain and types
   silver.customer_change    ordered source history
     |
     v
   gold.fact_order_line      one row per order line
   gold.dim_customer         governed customer versions
   gold.sales_summary        measures defined for reporting

For the running example, Bronze retains the original event envelope so a parser or business-rule change can be replayed. Silver resolves duplicate delivery using the source key and log position, applies delete semantics, and quarantines malformed rows instead of silently dropping them. Gold performs the Type 2 lookup using the order’s event time and publishes the tested order-line fact. A gold layer can contain detailed facts as well as aggregates; “gold means aggregated” is a convention, not a data-model rule.

Do not force every intermediate DataFrame into a permanent layer. Materialize a boundary when it supports replay, independent reuse, quality enforcement, ownership, or a measured workload. Otherwise, extra tables add storage, commits, metadata, orchestration, and schema contracts without adding a useful recovery point. The layering pattern is recommended in some lakehouse platforms, but it is not mandatory; choose boundaries that fit the system’s failure and consumption requirements.

Test each transition independently. Bronze-to-silver should prove source positions are neither lost nor applied out of order. Silver should expose duplicate and rejected-row counts. Gold should prove fact grain, dimension coverage, Type 2 non-overlap, and reconciled measures. A single end-to-end row count cannot identify which boundary lost or multiplied records.

3.4 Schema evolution and data contracts

A schema contract is more than a list of field names. It should state field types, nullability, meaning, units, valid values, key semantics, event-time meaning, privacy classification, and compatibility expectations. Version the contract and identify an owner who can approve changes.

Change Usually compatible for readers? Modeling question
Add a nullable field Often, but depends on the consumer What does null mean for old rows, and which contract version adds the field?
Rename a field Not automatically Is this the same semantic field or a new definition? Provide a migration alias or transition
Widen a numeric type Often, if values preserve meaning Does the target engine support the widening, and can all producers write it?
Narrow or reinterpret a type Usually breaking What backfill, validation, and consumer migration are required?
Change a metric definition Semantically breaking even if the SQL type is unchanged Version the metric or publish a new one; schema compatibility cannot preserve meaning

Parquet readers may infer different schemas from different files. Spark supports schema merging, but it is disabled by default because collecting schemas across files has a cost. For table formats, schema and partition evolution are metadata operations with format-specific compatibility rules. Validate the logical contract before accepting either change.

The example starts with curated order lines and customer change records. It derives end-exclusive customer validity ranges, resolves the customer version at order time, joins the product dimension, and creates one fact row per order line. It uses in-memory fixtures and local Spark; it does not write to the playground’s shared Hive or MinIO tables.

The public datalake-playground provides the Spark runtime used by this site’s demos. Start it from its root:

git clone https://github.com/rangareddy/datalake-playground.git
cd datalake-playground
SPARK_VERSION=3.5.9 sh run_datalake.sh start
sh run_datalake.sh status

Save the following as data_modeling_demo.py, then copy it to the Spark container and run it in local mode:

from decimal import Decimal

from pyspark.sql import SparkSession, Window, functions as F
from pyspark.sql.types import (
    DecimalType,
    IntegerType,
    StringType,
    StructField,
    StructType,
)

spark = (SparkSession.builder
         .appName("data-modeling-demo")
         .master("local[2]")
         .config("spark.ui.enabled", "false")
         .getOrCreate())

order_schema = StructType([
    StructField("order_id", StringType(), False),
    StructField("line_number", IntegerType(), False),
    StructField("customer_id", StringType(), False),
    StructField("product_id", StringType(), False),
    StructField("ordered_at", StringType(), False),
    StructField("quantity", IntegerType(), False),
    StructField("unit_price", DecimalType(10, 2), False),
    StructField("discount_amount", DecimalType(10, 2), False),
])
orders = (spark.createDataFrame([
    ("O1001", 1, "C01", "P01", "2025-01-15 10:00:00", 2,
     Decimal("10.00"), Decimal("0.00")),
    ("O1002", 1, "C01", "P01", "2025-02-10 11:00:00", 1,
     Decimal("10.00"), Decimal("1.00")),
    ("O1003", 1, "C02", "P02", "2025-02-11 12:00:00", 3,
     Decimal("5.00"), Decimal("0.00")),
    ("O1004", 1, "C404", "P404", "2025-02-12 09:30:00", 1,
     Decimal("12.00"), Decimal("0.00")),
], order_schema)
   .withColumn("order_ts", F.to_timestamp("ordered_at")))

version_schema = StructType([
    StructField("customer_id", StringType(), False),
    StructField("customer_sk", IntegerType(), False),
    StructField("segment", StringType(), False),
    StructField("country", StringType(), False),
    StructField("valid_from", StringType(), False),
])
customer_versions = spark.createDataFrame([
    ("C01", 101, "Gold", "US", "2025-01-01 00:00:00"),
    ("C01", 102, "Silver", "US", "2025-02-01 00:00:00"),
    ("C02", 201, "Silver", "CA", "2025-01-01 00:00:00"),
  ("__UNKNOWN__", -1, "Unknown", "Unknown", "1900-01-01 00:00:00"),
], version_schema).withColumn("valid_from", F.to_timestamp("valid_from"))

customer_window = Window.partitionBy("customer_id").orderBy(
    "valid_from", "customer_sk")
dim_customer = (customer_versions
    .withColumn("valid_to", F.lead("valid_from").over(customer_window))
    .withColumn("valid_to", F.coalesce(
        F.col("valid_to"),
        F.to_timestamp(F.lit("9999-12-31 00:00:00")))))

product_schema = StructType([
    StructField("product_id", StringType(), False),
    StructField("product_sk", IntegerType(), False),
    StructField("category", StringType(), False),
])
dim_product = spark.createDataFrame([
    ("P01", 11, "Hardware"),
    ("P02", 12, "Parts"),
  ("__UNKNOWN__", -1, "Unknown"),
], product_schema)

customer_join = orders.alias("o").join(
    dim_customer.alias("c"),
    (F.col("o.customer_id") == F.col("c.customer_id"))
    & (F.col("o.order_ts") >= F.col("c.valid_from"))
    & (F.col("o.order_ts") < F.col("c.valid_to")),
    "left",
)
resolved = customer_join.join(
    dim_product.alias("p"),
    F.col("o.product_id") == F.col("p.product_id"),
    "left",
)

fact_order_line = resolved.select(
    F.col("o.order_id").alias("order_id"),
    F.col("o.line_number").alias("line_number"),
    F.date_format(F.col("o.order_ts"), "yyyyMMdd")
     .cast("int").alias("order_date_key"),
    F.coalesce(F.col("c.customer_sk"), F.lit(-1))
     .cast("int").alias("customer_sk"),
    F.coalesce(F.col("p.product_sk"), F.lit(-1))
     .cast("int").alias("product_sk"),
    F.col("o.quantity").alias("quantity"),
    (F.col("o.quantity") * F.col("o.unit_price"))
     .cast("decimal(12,2)").alias("gross_amount"),
    F.col("o.discount_amount").alias("discount_amount"),
    (F.col("o.quantity") * F.col("o.unit_price")
     - F.col("o.discount_amount"))
     .cast("decimal(12,2)").alias("net_amount"),
)

duplicate_grains = (fact_order_line
    .groupBy("order_id", "line_number")
    .count()
    .filter(F.col("count") != 1)
    .count())
assert duplicate_grains == 0
assert fact_order_line.count() == orders.count()
assert fact_order_line.agg(F.sum("net_amount")).first()[0] == Decimal("56.00")
assert (fact_order_line.filter(F.col("order_id") == "O1001")
        .select("customer_sk").first()[0] == 101)
assert (fact_order_line.filter(F.col("order_id") == "O1002")
        .select("customer_sk").first()[0] == 102)
assert (fact_order_line.select("customer_sk").distinct()
  .join(dim_customer.select("customer_sk"), "customer_sk", "left_anti")
  .count() == 0)
assert (fact_order_line.select("product_sk").distinct()
  .join(dim_product.select("product_sk"), "product_sk", "left_anti")
  .count() == 0)
assert (fact_order_line.filter(F.col("order_id") == "O1004")
        .select("customer_sk", "product_sk").first() == (-1, -1))

fact_order_line.orderBy("order_id", "line_number").show(truncate=False)
print("PASS: one row per order line; net sales = 56.00")
spark.stop()

Run it in the existing container:

docker cp data_modeling_demo.py spark-master:/tmp/data_modeling_demo.py
docker exec spark-master /opt/spark/bin/spark-submit \
  --master "local[2]" \
  --conf spark.ui.enabled=false \
  /tmp/data_modeling_demo.py

The temporal join assigns the January order to customer version 101 and the February order to version 102. The unknown source IDs still produce a fact row, using the explicit unknown key -1. The four input lines remain four fact rows, and the reconciled net amount is 56.00.

This is a batch build from already curated source rows, not a complete CDC ingestion framework. In production, deduplicate replayed events by a source sequence or version before building dimensions and facts. If the source only exposes current state, it cannot reconstruct changes that happened between polls; use its change log or another history source when those changes matter.

Production failures: where models lie

A join changes the grain

Suppose one order line joins to two customer records because the Type 2 join uses only customer_id. Its quantity and revenue are now counted twice. Add an explicit uniqueness test for the fact key, and check that each fact matches at most one version of each dimension. A total that still looks plausible is not evidence that the join is correct.

A late dimension arrives after its fact

An order can arrive before its customer record, or a customer history update can be delayed. Mapping the fact immediately to an unknown key preserves the row, but may leave it associated with the unknown member permanently. Track which unknown reason was used, define a repair or restatement policy, and reprocess the affected facts when the dimension arrives. “Use key -1” is a referential-integrity technique, not a late-arrival strategy by itself.

An event timestamp is mistaken for valid time

updated_at may mean when the source row was last written, while a business effective date says when the new value became true. Those clocks answer different questions. If a retroactive correction is effective on January 1 but arrives on February 10, record both the business-valid interval and the warehouse observation time when audit requirements call for bitemporal history. Do not silently substitute ingestion time for business time.

Retries make “append” mean duplicate

At-least-once delivery can replay a batch after a timeout. Appending blindly turns one order line into two rows. Define an idempotency key at the business grain, keep source offsets or batch IDs as operational metadata, and choose whether corrections update a current-state fact or create explicit adjustment events. A table format’s atomic commit does not automatically make a pipeline idempotent.

A many-to-many bridge inflates measures

If an order is attributed to multiple campaigns or a customer belongs to multiple households, joining the bridge to the fact can repeat revenue. Use the bridge only with a documented filter or allocation rule. If sales must be allocated, store allocation weights whose sum is one for each fact at the appropriate time and test that invariant.

A changing definition masquerades as a schema change

Renaming a column is not the same as changing what it means. “Net sales” might exclude shipping in one definition and include it in another. Schema evolution can safely add or rename physical fields in formats that track field identity, but it cannot decide whether old and new metric definitions can be summed. Version semantic contracts or publish a new measure when the business meaning changes.

Part 4: Architectural Paradigms & Governance

The model sits inside a platform and an organization. Processing topology decides how results are refreshed; ownership decides who defines and supports the contract; metadata makes its meaning and dependencies discoverable. None of these choices replaces the need to define a correct row grain.

4.1 Data processing topologies: Lambda architecture vs. Kappa architecture

Both patterns address the tension between low-latency updates and the ability to recompute results after code or business rules change.

Pattern Data paths Strength Cost and failure mode
Lambda Batch path for complete recomputation plus a speed path for recent changes; query layer reconciles both Separate paths can meet historical and low-latency needs Business logic is duplicated or translated twice; reconciliation can disagree
Kappa One retained event log feeds one streaming model; replay rebuilds a new output One transformation path and a natural correction/replay story Requires retained, replayable events, enough replay capacity, and a safe output cutover

Kappa is not “streaming instead of batch” in every system. A bounded historical replay is still a batch-shaped workload even if it uses the same stream processor and code. If a source log retains only a few days, an arbitrary multi-year rebuild cannot be performed from that log alone. Keep an immutable archive or another reconstruction source when the business requires longer reprocessing windows.

For the sales model, separate arrival time from business event time. A late sale must join to a customer version valid at order_ts, not to the dimension row that happened to be current when the stream processed it. A replay must produce the same fact key and measure or write into a versioned output and switch readers after reconciliation.

4.2 Decentralized vs. centralized: data mesh vs. data fabric

Data mesh is primarily a way to organize analytical ownership around business domains and data products. Data fabric is a broad architectural approach for connecting, governing, and discovering data across systems, often through shared metadata and integration capabilities. They describe different axes and can coexist; neither is a single product or a table schema.

Question Data mesh emphasis Data fabric emphasis
Who owns and serves a domain dataset? Domain team owns the data product and its quality interface Shared capabilities connect assets across owners and systems
What is centralized? Platform capabilities and federated interoperability rules Metadata, integration, governance, and access automation may be shared
Main engineering risk Every domain invents incompatible keys, definitions, and SLOs A central catalog or virtualization layer promises semantics it cannot enforce
Modeling implication Publish a documented schema, owner, keys, and change policy per product Keep source, logical, and physical metadata discoverable across platforms

Centralize what creates interoperability: identity domains, schema compatibility policy, security classifications, and shared dimensions where their definitions truly match. Decentralize domain-specific meaning and delivery ownership. If two domains both use a field called customer_id, that name alone does not prove they identify the same entity.

4.3 Metadata and data lineage: the nervous system of a platform

Metadata describes data and how it is operated. At minimum, distinguish:

  • Structural metadata: names, types, nullability, keys, and schema versions.
  • Business metadata: definitions, owner, domain, sensitivity, and allowed use.
  • Operational metadata: freshness, row counts, quality results, source offsets, and failed runs.
  • Lineage: directed relationships from source datasets through jobs and runs to output datasets.

Lineage answers different questions from a catalog. A catalog helps find and understand a dataset; lineage explains where it came from and which consumers may be affected by a change. OpenLineage models jobs, runs, input datasets, output datasets, and extensible facets. A production contract should capture the source version/offset and model version that produced a snapshot, not just the code repository name.

Metadata is useful only if it is emitted and maintained. Define stable dataset identifiers across environments, include schema and quality facets, and connect run IDs to orchestrator logs. For regulated data, lineage records do not replace access control or retention policy; they make those policies and their effects auditable.

Part 5: Database Performance & System Scaling

Performance work starts from the physical execution plan and the data shape, not from a list of fashionable techniques. Indexes, partitions, sort order, and sharding solve different problems and carry different write and operating costs.

5.1 Indexing mechanics: B-trees, bitmaps, hash, clustered vs. non-clustered

An index is an access structure that helps an engine find rows without examining every data page. Its useful operators depend on the index type and engine.

Index family Good fit Important limitation
B-tree Equality, ranges, and ordered scans on sortable values Adds write amplification and storage; a low-selectivity predicate may still scan
Hash Equality lookups in engines that support hash indexes Does not provide range ordering
Bitmap Low-cardinality analytic predicates in engines that implement bitmap indexes Update/concurrency behavior is engine-specific; not a universal OLTP index
GIN / inverted Membership in arrays, tokens, or other multi-valued structures Index size and update cost can be high; supported operators depend on the engine
BRIN / min-max summary Large, physically correlated ranges such as append-ordered time Coarse summaries may return false-positive blocks that still need filtering

“Clustered” and “non-clustered” are not portable physical promises. In SQL Server, a clustered index defines the row-storage order and a table has one; non-clustered indexes are separate lookup structures. PostgreSQL’s CLUSTER rewrites a table in index order as a one-time operation; later inserts and updates do not continuously preserve that order. State the database when using these terms.

Open table formats add file- and manifest-level pruning, not a general B-tree over every row. Iceberg can use partition transforms and per-file metrics to skip files; Delta Lake can collect data-skipping statistics and supports Z-ordering. These mechanisms reduce files or row groups read, but do not replace a primary-key index for point lookups in a relational database.

Oracle’s bitmap-index implementation is aimed at read-heavy analytic workloads: updating an indexed value can lock the bitmap entry representing many rows. Do not carry bitmap-index advice from a star-schema warehouse into a high-concurrency transactional workload without checking that engine’s locking and write behavior.

5.2 The query optimization playbook for large databases

Use the same loop across engines: state the expected access path, inspect the actual plan, measure input and output cardinalities, then change one thing. Validate both semantics and cost after the change.

  1. Reduce bytes read. Project only required columns, push predicates to the source, and make date/time filters use the model’s declared timezone.
  2. Check selectivity and statistics. A predicate on a very common value may make a sequential scan cheaper than random index lookups. Refresh statistics after major loads.
  3. Join at the intended grain. Check uniqueness on the dimension side and inspect join estimates versus actual rows. A faster many-to-many join that multiplies measures is still incorrect.
  4. Look for avoidable exchanges and sorts. Partitioning, bucketing, clustering, and broadcast are workload-dependent; none is automatically beneficial for every query.
  5. Inspect skew and the long tail. Average task time can hide one hot partition. Compare per-partition rows/bytes and the maximum against the median.
  6. Tune only after the plan explains the bottleneck. Memory, parallelism, compaction, and file sizing are different controls; increasing one may move cost to shuffle, storage, or planning.

In PostgreSQL, use EXPLAIN (ANALYZE, BUFFERS) carefully: it executes the statement and can run writes or expensive functions. In Spark, compare the physical plan with executed SQL metrics; an initial adaptive plan may change after shuffle statistics arrive. Do not copy one engine’s plan vocabulary onto another.

5.3 Key considerations for designing scalable database systems

  • Choose keys for identity and distribution separately. A sequential surrogate can be an effective dimension key but a hotspot for a distributed write path; a random UUID may spread writes while increasing key size and reducing locality.
  • Avoid hot partitions. Tenant, date, or status keys can be extremely skewed. Measure the distribution before choosing a partition key, shard count, or hash strategy.
  • Specify uniqueness scope. A key unique inside one shard may not be globally unique. If consumers join across shards, provide a collision-safe global identity or an explicit compound key.
  • Account for cross-boundary operations. A join, transaction, or update that spans shards has coordination cost. Data locality helps only when the query’s filters align with the distribution key.
  • Make replay and migration part of the design. Preserve source offsets, support rebuilds into a new table, and define how consumers switch to the validated version.
  • Set growth and retention policy. Unbounded event retention, indexes, snapshots, and CDC logs all consume storage and can affect recovery time.

Scale is a workload property, not just row count. Include concurrent readers, write rate, skew, latency objectives, and correction/replay windows in the design envelope.

5.4 Partitioning, sharding, and Z-order

These words are often used interchangeably, but they refer to distinct mechanisms:

Mechanism Boundary Primary purpose Main cost
Database partitioning One logical table split into child partitions managed by one database Prune ranges/lists, manage lifecycle, or isolate hot data Too many partitions increase planning/metadata cost; uniqueness constraints may include the partition key
Sharding Data split across independent database instances or nodes Scale capacity and isolate failure/load domains Cross-shard joins, transactions, global uniqueness, and rebalancing
File partitioning Files/directories or table partition specs in a lake Skip files for common predicates and control maintenance scope High-cardinality partitions create small files and metadata overhead
Z-ordering Multi-dimensional locality layout in a table format that supports it Cluster values so file-level statistics can skip more data Rewrite cost; effectiveness falls as more columns are added and it is implementation-specific

For Iceberg, partition transforms such as days(order_ts) keep the logical timestamp column separate from the physical partition values, and partition specs can evolve. Delta Lake documents ZORDER BY as a mechanism to colocate related values for data skipping. Do not label an Iceberg sort order as Z-order or assume every lakehouse reader interprets a vendor-specific layout command.

Partition only when the table size and query pattern justify it. A predicate on a source date can enable pruning without asking every query writer to duplicate a hand-maintained event_date column. Verify pruning in the engine plan and monitor file counts after the write.

5.5 ACID vs. BASE and the CAP theorem in modeling

ACID describes transaction properties: atomicity, consistency with declared constraints, isolation, and durability. BASE is a broad descriptive design slogan—basically available, soft state, eventually consistent—not a competing SQL transaction standard. A system can use ACID transactions for an individual write while exposing asynchronously refreshed read models elsewhere.

The two meanings of “consistency” must not be conflated. ACID consistency means a committed transaction preserves application/database invariants. The C in CAP concerns whether distributed reads observe a single up-to-date copy. CAP’s trade-off applies when communication is partitioned: a system cannot guarantee both linearizable consistency and availability for every request during that partition. It does not say a normal distributed system must choose only two properties at all times.

For data models, make the trade visible to consumers. Is a dashboard allowed to read a replica behind the source? Can independent partitions accept writes that may conflict? How will those writes be reconciled, and which business invariants must never be violated? “Eventually consistent” is not a recovery plan; name the lag bound, conflict rule, and reconciliation owner.

Physical layout: schema versus stored bytes

The star schema says how records relate and what they mean. Physical layout says how bytes are stored and found. Keep those decisions separate so a new partition strategy does not force consumers to rewrite their SQL.

Concern Model-level decision Physical decision
Customer history One row per customer version and validity interval Sort or cluster files to help common customer/time lookups
Sales fact One row per order line Partition by a useful coarse date transform when data volume justifies it
Schema change Add sales_channel with a defined meaning and default/backfill policy Evolve the table schema; old files may not contain the new field
Query filters Use order_date_key and dimensional keys consistently Choose file format, partition transforms, and clustering from measured queries

Do not partition a large table by a high-cardinality business key such as customer_id. That tends to create many small directories and files while doing little to reduce scans. A coarse event-date transform is often a better starting point, but the correct choice follows query predicates, file sizes, and write frequency. Apache Iceberg supports hidden partitioning and partition evolution, so a logical filter on the source timestamp can remain stable as the physical partition spec changes. Evolution avoids requiring consumers to filter on hand-maintained partition columns; it does not remove the need to measure pruning or file counts.

Schema-on-read formats also need explicit schema discipline. Spark’s Parquet schema merge is disabled by default because merging file schemas has a cost. Do not rely on a random file’s inferred schema to define a production model. Publish an explicit contract, validate incoming schemas, and make compatible evolution deliberate.

5.6 Put model contracts into tests

Every published fact or dimension should have checks that express its contract:

Check Invariant
Grain uniqueness (order_id, line_number) appears at most once in the transaction fact
Non-null keys Required fact foreign keys resolve to a real or explicit unknown member
Dimension uniqueness A dimension surrogate key identifies one row
Type 2 intervals Versions for one business key do not overlap; event-time lookups match at most one version
Change ordering A later source version does not overwrite a newer accepted source sequence
Measure reconciliation Fact net amount equals gross less discount under the declared currency and rounding rules
Batch idempotency Replaying the same input does not change row counts or totals
Delete semantics A source delete is either retained, invalidated, or represented as a tombstone by an explicit policy

Also test volume and operations: rows rejected, unknown-member rates, late arrival age, duplicate source keys, fact-to-source reconciliation, file counts, and model freshness. Alert thresholds should distinguish a newly sparse source from a broken join.

A practical design sequence: identify the business process; write the grain as a sentence; inspect the source’s keys and change semantics; choose transaction, periodic, or accumulating facts; define dimensions and history; specify measures and their aggregation rules; publish the logical contract; then choose the storage layout and test the invariants. Revisit the physical layout when measured access patterns change, not every time a new report is requested.

Part 6: Database Lifecycle & Tooling Mechanics

Models move in both directions. Forward engineering turns agreed semantics into schemas, constraints, migrations, and pipelines. Reverse engineering starts from deployed objects and reconstructs their declared structure. Neither direction can infer all the business meaning without people and source documentation.

6.1 Forward engineering vs. reverse engineering

Workflow Starts from Produces What still needs review
Forward engineering Business requirements and conceptual/logical model DDL, migration scripts, constraints, transformations, and test contracts Whether the design matches source capabilities and query needs
Reverse engineering Existing schemas, views, constraints, and observed data An inventory, ER diagram, candidate model, and dependency graph Grain, hidden application rules, temporal meaning, and undocumented exceptions

Treat deployed schema changes as versioned migrations. A change record should state the contract version, compatibility impact, rollout order, validation, and backfill plan. For a breaking rename, an expand-and-contract rollout can add the new field, dual-write or backfill, migrate consumers, verify the old field is unused, and only then remove it. A rollback script cannot always recover dropped or reinterpreted data, so the recovery plan may be a forward repair rather than a literal inverse migration.

6.2 How reverse engineering works under the hood

For a relational database, an introspection tool reads the system catalog or standard information schema. It collects columns and types, nullability, defaults, primary/unique/foreign keys, indexes, views, and dependencies. It then maps declared foreign keys to relationship edges and can infer candidate cardinality from uniqueness and nullability constraints.

The distinction between declared and observed is important. A declared foreign key is evidence of an enforced relationship. A frequent join in query logs is only a candidate relationship. A missing foreign key might mean a performance choice, a cross-system relationship, or simply an integrity gap; reverse engineering cannot decide which explanation is correct.

The following read-only PostgreSQL queries list visible columns and declared foreign-key column mappings. information_schema is SQL-standard and portable; pg_catalog is PostgreSQL-specific and exposes engine details that the standard views do not.

SELECT table_schema,
       table_name,
       ordinal_position,
       column_name,
       data_type,
       is_nullable,
       column_default
FROM information_schema.columns
WHERE table_schema = ANY (current_schemas(false))
ORDER BY table_schema, table_name, ordinal_position;

SELECT source_ns.nspname AS source_schema,
       source_table.relname AS source_table,
       constraint_row.conname AS constraint_name,
       source_column.attname AS source_column,
       target_ns.nspname AS target_schema,
       target_table.relname AS target_table,
       target_column.attname AS target_column,
       source_key.ordinality AS key_position
FROM pg_constraint AS constraint_row
JOIN pg_class AS source_table
  ON source_table.oid = constraint_row.conrelid
JOIN pg_namespace AS source_ns
  ON source_ns.oid = source_table.relnamespace
JOIN pg_class AS target_table
  ON target_table.oid = constraint_row.confrelid
JOIN pg_namespace AS target_ns
  ON target_ns.oid = target_table.relnamespace
JOIN LATERAL unnest(constraint_row.conkey)
  WITH ORDINALITY AS source_key(attnum, ordinality)
  ON true
JOIN LATERAL unnest(constraint_row.confkey)
  WITH ORDINALITY AS target_key(attnum, ordinality)
  ON target_key.ordinality = source_key.ordinality
JOIN pg_attribute AS source_column
  ON source_column.attrelid = source_table.oid
 AND source_column.attnum = source_key.attnum
JOIN pg_attribute AS target_column
  ON target_column.attrelid = target_table.oid
 AND target_column.attnum = target_key.attnum
WHERE constraint_row.contype = 'f'
  AND source_ns.nspname = ANY (current_schemas(false))
ORDER BY source_schema, source_table, constraint_name, key_position;

Those queries reveal declared structure, not an ER model ready for publication. Continue by sampling value distributions, checking whether candidate keys are actually unique, examining null rates and orphan rows, and asking the source owners what the fields mean. Then compare the recovered schema to the desired conceptual model: legacy schemas often encode implementation history rather than current business boundaries.

Summary and key takeaways

Modeling is the work of making data interpretable without making every consumer rediscover how it was produced. Start with a row-level business contract, preserve the identity and relevant history of each dimension, and keep facts at a consistent grain. Then prove the contract with data tests.

The durable distinctions are simple:

  • The business key identifies an entity; the surrogate key identifies a warehouse row or historical version.
  • Event time says when a business event occurred; processing time says when the platform handled it.
  • A logical model defines grain and relationships; a physical table layout defines where bytes land and which files a query can skip.
  • Atomic commits protect table metadata; idempotent transformations protect business outcomes when inputs are replayed.

When a total changes unexpectedly, verify grain and join cardinality before tuning Spark. When history looks wrong, verify validity intervals and event time before changing a table format. Those checks find modeling errors closer to their cause than another layer of SQL can.

References

Trademarks

Apache Spark, Apache Iceberg, Apache Parquet and Apache are either registered trademarks or trademarks of The Apache Software Foundation in the United States and other countries.

Found this useful?

These posts and tools are free. If one saved you an afternoon, you can buy me a coffee.

Buy me a coffee