Modeling Data in Apache Doris

Course README · Open Lab 4 · Open Quiz 4

Module goal

Turn the course's source events and its reporting needs into Doris tables: decide what one row means, what a repeated key does, which types and missing values each column allows, and how Partitions, Buckets, and the sort key lay the rows out.

The scenario is an ecommerce analytics platform with three needs at once: it keeps every browsing, cart, and purchase event; it exposes the current state of changing entities such as orders; and it serves a dashboard at daily, regional, and event-type grain. Lab 4 builds two of these data products, events_modelled for event detail and daily_event_metrics for the daily summary; Module 7 builds current state.

Learning objectives

After completing this module, you will be able to:

1. Define a table's grain from its source contract, and keep detail and summary grains in separate tables.
2. Choose the Duplicate Key, Unique Key, or Aggregate Key model from the behavior that a repeated key requires.
3. Derive a summary's dimensions and additive measures from a reporting requirement, and reconcile it with the detail.
4. Choose types for identifiers, time, money, categories, and nested values from their meaning and the operations they need.
5. Distinguish NULL, NOT NULL, and DEFAULT for values that are missing, omitted, or invalid.
6. Design a table's Partitions, Buckets, and sort key from its time filters, lifecycle, distribution, and volume.

Module structure

4.1 Define What One Row Represents

Before naming columns, finish this sentence:

One row in this table represents ...

That statement defines the table's grain. Grain comes from a business and source contract. It is not discovered by choosing whichever column happens to be unique in today's sample.

Separate three common data products

The ecommerce platform needs three different row meanings:

These products may originate from the same activity, but they are not interchangeable. Event history preserves the sequence and attributes of individual observations. Current state intentionally hides superseded values from an ordinary query. A summary intentionally removes dimensions and detail that its reports do not need.

Do not mix detail and summary grain

Suppose two purchase events have revenue 12.00 and 18.00. A daily summary for those events contains revenue 30.00:

Keep event history, current state, and summary metrics at separate declared grains.

If all three rows are placed in one table and a query simply calculates SUM(revenue), the result is 60.00. The same activity has been represented once as event detail and again as a summary. A row-type flag could let every query filter one representation, but the table would still contain mixed grain and make accidental double counting easy.

Keep the two grains in separate tables. Query event detail when the answer needs users, products, timestamps, or individual events. Query the summary when its declared dimensions and stored measures are sufficient.

Use observations to verify a declared contract

A row count can confirm that the expected source is available. Distinct counts, minimum and maximum values, and duplicate checks can reveal violations. They do not replace the declared meaning of a row.

For example, a duplicated event_id may indicate bad source data, an event identifier scoped to another field, or a legitimate repeated delivery. Only the source contract says which interpretation is correct. Lab 4 therefore confirms the baseline with COUNT(*) and reads its column metadata; the grain itself comes from the contract.

4.2 Choose a Table Model from Repeated-Key Semantics

Module 2 (Deep Dive into Apache Doris Architecture) showed what each Table Model does when a row arrives with a key that already exists: Duplicate Key keeps both rows, Unique Key keeps the latest, and Aggregate Key combines the value columns. Modeling asks the reverse question: which behavior does each data product need?

Choose the model from row meaning before thinking about performance. Doris cannot convert an existing table to another Table Model; a changed contract needs a new table and a controlled transition. See the Table Model overview and Table Model best practices.

Choose a Table Model from the result that a repeated key must produce.

Event history: Duplicate Key

An event log keeps every accepted observation, even two events that share all their key values. In this model the key columns set the sort key, so

DUPLICATE KEY(event_time, event_id, user_id)

does not mean "one row per (event_time, event_id, user_id)": the grain of events_modelled stays one accepted source event. See the Duplicate Key model.

Current state: Unique Key

An order-state table has a different contract:

If ordinary queries should see one current row for order_id = 7842, order_id is the Unique Key, and each new record for it is an upsert. Doris 4.x creates Unique Key tables with Merge-on-Write by default, so the write itself makes the new state the visible one. Arrival order is not always business order, because CDC events can arrive late or be retried: a Sequence column lets a source version decide which state wins. Module 7 builds current state with upserts, partial updates, Sequence columns, and deletes. See the Unique Key model and Merge-on-Write.

Summary metrics: Aggregate Key

A reporting pipeline may deliver several contributions for the same (event_date, region, event_type). If event_count and total_revenue should be added, the dimensions are the Aggregate Key and the measures are value columns declared SUM:

(2026-09-15, east, purchase, event_count=4, total_revenue=90.00)
(2026-09-15, east, purchase, event_count=3, total_revenue=65.00)
                                      |
                                      v
one logical group representing event_count=7 and total_revenue=155.00

The key columns are both the aggregation group and the sort key, and each value column declares how contributions merge. Such a table keeps no event identifiers or timestamps, so it cannot replace the detail, and a contribution that is loaded twice is added twice: retry safety still comes from the load contracts of Module 3. See the Aggregate Key model.

4.3 Derive a Summary Model from the Reporting Requirement

The dashboard repeatedly needs daily event count and total revenue by region and event type. Begin with that output, then derive the table contract.

Define dimensions and measures separately

One summary row represents one:

(event_date, region, event_type)

Those three dimensions become the Aggregate Key. The table stores two Value columns:

• event_count BIGINT SUM represents the number of events contributing to the group.
• total_revenue DECIMAL(18,2) SUM represents their additive revenue.

DECIMAL(18,2) gives the accumulated summary more integral range than the per-event DECIMAL(12,2) source. The required precision should follow expected group size and revenue range rather than copying the detail type automatically.

The summary does not carry event_id, user_id, or product_id, because they would change its grain. It therefore cannot answer which user purchased, recover an individual event, or group accurately by product.

Store only measures that combine correctly

Counts and revenue totals are additive across disjoint event contributions. Several common metrics are not safely additive:

• A distinct-user count can count the same user in several batches or groups. An ordinary SUM of those counts overstates the union. A mergeable Bitmap or HyperLogLog (HLL) state may fit an approximate or exact distinct-count design, depending on the requirement.
• An average should not normally be added. Store a compatible numerator and denominator, then calculate the weighted result.
• Ratios and percentages generally need their underlying additive components.
• Minimum and maximum can merge with their matching aggregate semantics, but they do not behave like a sum.

The reporting question must identify how later batches and rollups combine. Declaring SUM is a semantic promise, not just column syntax.

Reconcile at the summary grain

COUNT(*) means different things in the two models:

Lab 4 sees 10,158,080 detail rows and 280 summary rows: 280 is the number of summary groups that SQL returns.

Reconcile the models using measures that both can represent:

COUNT(*) from events_modelled
        = SUM(event_count) from daily_event_metrics
        = 10,158,080 represented events

SUM(revenue) from events_modelled
        = SUM(total_revenue) from daily_event_metrics
        = 39,984,455.64 represented revenue

Evidence boundary: matching totals verify the declared additive measures for this load. Lab 4 writes the summary with one pre-grouped insert, so the merge of separate batches is described here, not observed, and the Lab measures no speed.

4.4 Choose Data Types from Business Meaning

A data type should represent the valid domain of a field and support the operations that matter. Choosing from the values visible in one file can underestimate future range, precision, or structural needs.

Start with the required operations and correctness, then consider storage and calculation cost:

DOUBLE arithmetic can be cheaper than wide DECIMAL arithmetic. That is a performance question to measure on representative data, not a reason to store money approximately: first decide whether approximation is valid at all.

Keep identifiers consistent

An identifier may contain digits without being a quantity. Choose its type from the source contract:

• Use an integer type when the identifier is defined as numeric, fits the selected range, and does not require formatting such as leading zeroes.
• Use a string type when letters, leading zeroes, composite formatting, or an external opaque convention belongs to the identity.

The same business Key should use compatible types in fact, dimension, and state tables. Storing product_id as BIGINT in one table and as loosely formatted text in another introduces repeated casts and can complicate Join conditions. A cast in an occasional migration is normal; the same cast in every production query signals an unresolved schema boundary.

Represent time at the required precision

Use DATE when the value is a calendar date without time-of-day meaning. Use DATETIME when the source and analysis require date and time. Doris supports fractional-second precision in DATETIME(p); choose p from the actual source contract rather than inventing precision the source does not provide.

A temporal type does not define the business time zone. Document whether the value represents Coordinated Universal Time (UTC), a local business zone, or another convention. Daily boundaries are ambiguous until that convention is known. See DATETIME.

Use exact fixed-point values for money and deliberate approximation for measurements

DECIMAL(p, s) gives an exact fixed-point contract:

• p, the precision, is the total number of significant decimal digits.
• s, the scale, is the number of digits after the decimal point.

For DECIMAL(12,2), two digits belong to cents and the remaining capacity supports the integral range. Choose enough precision for valid future values, intermediate arithmetic, and required totals.

FLOAT and DOUBLE are approximate IEEE 754 binary floating-point types. Choose them for values such as temperature, signal strength, coordinates, or a statistical score only when small representation differences are acceptable. DOUBLE provides more precision and range than FLOAT, but it still does not represent every decimal fraction exactly. Floating-point addition is not strictly associative, so distributed aggregation order can also expose small differences. Avoid floating-point columns as exact Join Keys or equality-based business identifiers.

For money, sufficiently large floating-point values can no longer represent every cent. Text preserves characters but does not guarantee a valid amount and forces conversion before numeric operations. Lab 4 makes both problems observable: one text value fails conversion, and adding one cent to a large DOUBLE does not produce the exact decimal result. See DECIMAL and Floating-Point Types.

Bound category strings without confusing two concepts

event_type and region are short categories, so bounded VARCHAR types express their expected maximum length. String length and cardinality are different:

• Length is the maximum number of characters or bytes required by one value's type contract.
• Cardinality is the number of distinct values appearing in the data.

A region can have low cardinality while each label still needs several characters. An oversized VARCHAR does not create more category values, but it weakens the contract and can increase unnecessary storage or processing costs. Leave sensible headroom for controlled future values rather than using the largest possible declaration by habit. See VARCHAR.

4.5 Give Missing, Omitted, and Invalid Values Different Rules

The following source conditions are not equivalent:

1. A product does not apply to an event.
2. A region was omitted and the business has agreed to classify it as unknown.
3. Revenue contains not-a-number and cannot satisfy the monetary contract.

One universal NULL or default rule would hide these differences.

Choose nullability from allowed business states

Use a nullable column when absence is a valid state such as unknown, not yet provided, or not applicable. Queries must then define how that state behaves in filters, groups, counts, and Joins. For example, COUNT(product_id) ignores NULL, while COUNT(*) still counts the event row.

Use NOT NULL when every accepted row must contain a valid value. This makes a minimum data-quality requirement part of the table contract. Avoid declaring every field nullable merely because a source parser can produce missing values. That transfers unresolved decisions into every downstream query.

Use DEFAULT only for an agreed omission rule

A default supplies a value when the target column is omitted from an insert. For example:

region VARCHAR(16) NOT NULL DEFAULT "unknown"

supports a contract in which an omitted region belongs to the unknown reporting category. It does not mean that an explicitly supplied NULL should be silently converted, and it does not repair a malformed region value.

The distinction matters operationally:

Establish a typed ingestion boundary

TRY_CAST is useful when a landing table must preserve permissive source text. It returns NULL when the conversion cannot be performed. That lets an ingestion query count invalid rows before selecting valid typed values.

Do not calculate only SUM(TRY_CAST(revenue_text AS DECIMAL(12,2))) and declare success. Aggregate functions such as SUM ignore NULL, so malformed values could disappear from the total. Pair the calculation with an invalid-row count and an explicit policy. Once valid rows enter a typed analytical table, downstream queries can use SUM(revenue) without repeating parsing logic. See CAST and TRY_CAST.

Treat missing, omitted, and invalid input as different states at the ingestion boundary.

4.6 Choose Time Partitions from Query and Lifecycle Boundaries

Most event queries read recent time ranges, while retention, replacement, and backfill operate on complete calendar periods. A Range Partition maps each row to a declared value interval and gives Doris a data-management boundary inside the table.

Treat Partition as a value range that contains Tablets

Module 2 introduced the hierarchy: a rule such as [2020-03-01, 2020-04-01) decides which rows belong to a Partition, and each Partition holds one Tablet per Bucket. For events_modelled, the two levels answer different questions:

events_modelled
    |
    +-- December 2019 Partition: which time range?
    |       +-- Tablet 1: which hash bucket?
    |       +-- ...
    |       +-- Tablet 10
    |
    +-- March 2020 Partition: which time range?
            +-- Tablet 1: which hash bucket?
            +-- ...
            +-- Tablet 10

The Partition is what a time filter lets the FE skip, and what retention or a backfill drops or replaces as a whole. Module 2 measured the pruning, so Lab 4 only inspects the generated ranges.

Choose a useful time grain

Partition granularity follows the workload:

• Daily Partitions can give fine lifecycle control but create more metadata and smaller physical units.
• Monthly Partitions reduce the number of Partition objects but make a whole month the natural lifecycle boundary.
• A very coarse Partition can contain too much data for precise retention or replacement work.

There is no universal day-or-month answer. Consider the amount of data per period, common filter ranges, retention policy, backfill size, and expected number of Partition objects.

Use Auto Range Partitioning for rule-driven arrival

If new time values arrive continuously and their boundaries follow a stable rule, Auto Range Partitioning can create the required Partition when data needs it. The lab declaration uses:

AUTO PARTITION BY RANGE (date_trunc(event_time, 'month'))

The course data has events in December 2019 and March 2020, so Doris creates those two monthly ranges; January and February have no events and get no Partition.

Choose explicit Partition management when boundaries require advance approval, special names, or a tightly controlled lifecycle process. Auto Partitioning creates ranges; the Table Model, the grain, the Bucket key, and the sort key remain your decisions. See Auto Partitioning.

4.7 Use Buckets to Distribute Data Within Every Partition

One monthly Partition can still contain millions of rows. Doris divides each Partition into Buckets, each represented by a Tablet. Tablets are the physical data shards distributed and processed by Backend (BE) nodes.

Choose a Hash key that can spread the workload

With Hash distribution, Doris hashes the selected distribution columns to choose a Bucket inside the row's Partition. The key should have enough distinct and sufficiently distributed values for the workload.

The course data offers two candidates:

This is why events_modelled uses HASH(user_id). Hashing decides which Bucket a key goes to; how many rows each Tablet gets still depends on how often each key occurs. A key with few values limits distribution even when those values are evenly used, as here; skewed values make it worse, because the Buckets of the dominant values grow fastest.

Random distribution can spread rows without preserving a key-based placement relationship. In Doris 4.x it applies to Duplicate Key tables and to Aggregate Key tables whose value columns do not use REPLACE or REPLACE_IF_NOT_NULL; Unique Key tables, and Aggregate Key tables with such columns, must use Hash distribution, and Doris rejects DISTRIBUTED BY RANDOM for them when the table is created. Hash distribution is useful when stable placement by selected columns benefits filtering or Join layouts. Module 6 explains how a compatible distribution can help a Join; still choose the Hash key from the whole workload and its skew, not from one future Join.

Choose Bucket count as a capacity decision

Bucket count affects Tablet size, scheduling, and available parallel work:

• Too few Buckets can create very large Tablets and limit parallelism.
• Too many Buckets can create small Tablets and unnecessary metadata and scheduling overhead.

Consider rows and bytes per Partition, cluster size, expected growth, concurrency, compaction pressure, and the useful degree of parallelism. Lab 4 uses ten Buckets because they fit the course data and keep the layout easy to read.

For the observed table:

2 generated Partitions × 10 Buckets per Partition = 20 Tablets
20 Tablets × 1 replica in the sandbox = 20 Tablet replicas

Evidence boundary: the single-BE sandbox shows the Tablet count and how the rows are spread, not multi-node placement or parallel speed; Module 9 runs a cluster with several BEs. See Data Bucketing.

4.8 Align the Sort Key with the Main Access Path

Doris stores table data in the order defined by its Key columns. This ordering supports prefix-based data access and compression behavior. The Table Model determines what else those Key columns mean:

Begin the event sort key with the common range filter

Most queries over events_modelled first restrict event_time. The sort key

DUPLICATE KEY(event_time, event_id, user_id)

therefore begins with time, followed by stable detail identifiers; in a Duplicate Key table, these columns only order the rows.

Ordering should follow real access patterns, not an attempt to include every possible filter column. A leading low-selectivity field can weaken the usefulness of later fields for common predicates. Conversely, a field used only in rare queries may not justify an early position.

Keep the three layout questions separate

The three choices cooperate, but each answers its own question: a sort key creates no Partitions, a time Partition orders no rows, and a Hash key enforces no uniqueness. Lab 4 inspects the declaration and the layout; Lab 2 compared the scan work of two sort keys with the Query Profile.

Partition selects the time range, Hash selects the Tablet, and the sort key orders data within storage.

4.9 Map Complex Types from the Application Structure

Some attributes naturally belong inside one event row. Choose a complex type from that structure and its analytical operations, not because the source is nested or because one type appears more flexible.

An ARRAY preserves an ordered collection in one row; it does not represent several independent event rows. A STRUCT fits a stable set of named child fields. A MAP fits keys that vary while its key and value type contracts remain meaningful. JSON and VARIANT support document-oriented and evolving data, but flexibility does not remove the need for governance.

Promote stable analytical fields

Fields used frequently for filtering, Partitioning, Bucketing, sorting, Joins, grouping, or measures usually deserve ordinary typed columns. Keeping event_time, user_id, region, and revenue buried inside a flexible payload would force every query to recover the same contract.

Complex values work well for secondary attributes whose nested shape belongs to a single row. Before production use, check the current Doris version for:

• supported nesting and element types;
• available access, transformation, and expansion functions;
• nullability and conversion behavior;
• restrictions on Key, Partition, distribution, index, and Join use;
• ingestion format and schema-evolution behavior.

These capabilities evolve. Use the application shape to select candidates, then confirm syntax and restrictions in the current ARRAY, MAP, STRUCT, JSON, and VARIANT documentation. Lab 4 does not create complex-type columns; Lab 5 uses a small array literal to demonstrate row and element operations without changing the event schema.

Lab 4: Model Data for Analytical Workloads

Open Lab 4 — Model Data for Analytical Workloads after the Level 1 events baseline is available. Plan about 30 minutes. The Notebook uses four Module 4 target tables and does not replace or delete the baseline:

• modeling_raw_events contains a five-row permissive landing sample.
• modeling_typed_events contains the four valid typed sample rows.
• events_modelled contains the complete event-detail model for Modules 5, 6, 8, and 10.
• daily_event_metrics contains the daily reporting summary.

The five numbered lab steps provide these observations:

In Step 4, the two generated Partitions reflect only source months that occur: December 2019 and March 2020; January and February have no events, so Auto Partitioning created no ranges for them. The Tablet summary is metadata about the current layout.

In Step 5, distinguish three quantities:

• stored_rows from COUNT(*) is the number of SQL-visible logical rows in each table.
• represented_event_rows is one per detail row but comes from SUM(event_count) for the summary.
• represented_revenue comes from the compatible revenue measure in each grain.

Run the optional stop and restart cells only when you want to release and then restore sandbox resources. Docker named volumes preserve the baseline and Lab 4 tables. events_modelled must remain available for Modules 5, 6, 8, and 10.

The Lab ends with a practice: reconcile one day of the summary with the detail. Predict the result first, then write the code in the empty cell; the collapsed reference solution after it also runs with Run All and leaves the Lab's tables as they were.

Summary

• A table's grain comes from its source contract: one accepted event, one current entity, or one declared group of dimensions. Detail and summary rows belong in separate tables, or a SUM counts the same activity twice.
• The Duplicate Key model keeps every accepted row, the Unique Key model exposes one current row per key, and the Aggregate Key model combines value columns with their declared function; the key columns also form the sort key.
• A summary declares its dimensions as Aggregate Key columns and stores only measures that combine correctly, such as counts and totals; daily_event_metrics represents all 10,158,080 events and 39,984,455.64 in 280 rows.
• Types follow meaning: integers or strings for identifiers, DATE or DATETIME for time, DECIMAL for money, bounded VARCHAR for categories, and complex types only for attributes whose nested shape belongs to one row.
• NULL is a valid absence in a nullable column, NOT NULL makes a value required, DEFAULT applies only when a column is omitted, and a value that cannot convert is invalid and needs its own count and policy.
• Partitions follow time filters and the lifecycle, Hash Buckets on a key with many values spread each Partition over Tablets, and the sort key starts with the common range filter; Lab 4's two monthly Partitions with ten Buckets give 20 Tablets.

Quiz 4: Modeling Data in Apache Doris

Open Quiz 4: Modeling Data in Apache Doris. It has six single-choice questions and needs no running environment.

Next module: Module 5: Analyzing Data in Apache Doris.

References

• Table design workflow: Table Design Guide and Table Model Best Practices.
• Repeated-Key semantics: Table Model Overview, Duplicate Key, Unique Key, Merge-on-Write, and Aggregate Key.
• Doris 4.1.3 implementation terminology: KeysType.java maps the model types to DUPLICATE KEY, UNIQUE KEY, and AGGREGATE KEY; AggregateType.java defines supported stored aggregation types such as SUM, MIN, MAX, BITMAP_UNION, and HLL_UNION.
• Column contracts: Data Types, DECIMAL, DATETIME, VARCHAR, and CAST and TRY_CAST.
• Physical design: CREATE TABLE, Auto Partitioning, and Data Bucketing.
• Complex types: ARRAY, MAP, STRUCT, JSON, and VARIANT.