AP Subject A 35 — Distributed databases and data warehouses: from partitioning, replication, CAP and two-phase commit to ETL and OLAP

AP Subject A · Installment 35 · An independent study resource for the 2026 exam and Syllabus Ver.7.2. It is not affiliated with IPA and does not guarantee a pass. Open the series hub

Based on the 2026 IPA Applied Information Technology Engineer Examination (AP), Subject A, Syllabus Ver.7.2; checked 2026-10-22. This is independent study material from Motitory: it is not affiliated with, reviewed or endorsed by IPA. It does not cover every item of Ver.7.2 or every distributed database and data warehouse technique, and does not guarantee a pass. All examples and questions here are Motitory originals, not IPA past questions.

This is AP35 (a supplement) of the fixed AP33–38 completion plan, not the last article of the series; every installment is listed in the series hub. The following were covered earlier and are not retaught here:

Cloud, virtualisation, load balancing and distributed processing in general: AP10
Keys, functional dependencies and normalisation: AP13
SQL, join semantics, aggregation and NULL: AP14
ACID, isolation levels, WAL and recovery inside a single DBMS: AP15
Indexes, performance, access control and the database final diagnostic: AP32

This article covers only the problems that appear once data sits on several sites (nodes) and analytical databases (the DWH).

1. Terminology at a glance

한국어	日本語 (exam term)	English	One-line definition
분산 데이터베이스	分散データベース	distributed database	Data on several sites used as if it were one database
수평 분할	水平分割	horizontal partitioning	Split the rows by a condition; rebuild with a union
수직 분할	垂直分割	vertical partitioning	Split the columns; keep the primary key in every fragment and rebuild with a join
샤딩	シャーディング	sharding	Place horizontal fragments (shards) on different servers
복제(레플리케이션)	レプリケーション	replication	Keep copies of the same data on several sites
분산 투과성	分散透過性 (位置透過性 etc.)	distribution transparency	Users need not be aware of where data is, how it is split or copied
2단계 커밋	2相コミット (2相コミットメント)	two-phase commit (2PC)	Distributed transaction protocol: prepare and vote, then all commit or all roll back
스플릿 브레인	スプリットブレイン	split brain	Both sides of a partition act as primary and the data diverges
강한 일관성	強い一貫性	strong consistency	Every replica read returns the latest committed value
결과 일관성(최종 일관성)	結果整合性	eventual consistency	Once new updates stop, replicas converge to the same value
CAP 정리	CAP定理	CAP theorem	During a network partition, consistency and availability cannot both be fully provided
BASE	BASE	basically available, soft state, eventually consistent	A design approach contrasted with ACID (not a guarantee)
데이터 웨어하우스	データウェアハウス	data warehouse (DWH)	Analytical database that integrates data from several systems and keeps history
데이터 마트	データマート	data mart	Analytical database narrowed to one department or purpose
ETL	ETL (抽出・変換・書出し)	extract, transform, load	Extract → transform → load
데이터 클렌징	データクレンジング	data cleansing	Fixing inconsistent notation, duplicates, missing values and errors
팩트 테이블	ファクトテーブル	fact table	Central table holding measures (sales amount etc.) and foreign keys to dimensions
디멘션 테이블	ディメンションテーブル	dimension table	Table holding the attributes of an analysis axis (product, region, period)
스타 스키마	スタースキーマ	star schema	Dimension tables arranged around a fact table like a star
OLAP	OLAP	online analytical processing	Multidimensional aggregation and analysis
OLTP	OLTP	online transaction processing	Business systems handling many short update transactions

2. Distributed databases and transparency

A distributed database makes data on several networked sites look to its users like a single database. The property this requires is transparency (透過性), and the exam distinguishes the following kinds.

Transparency	日本語	What users need not be aware of
Location transparency	位置透過性	Which site holds the data
Migration transparency	移動透過性	Whether the data has moved to another site
Fragmentation transparency	分割透過性	Whether a table is split into fragments
Replication transparency	重複透過性 (複製透過性)	Whether copies of the same data exist in several places
Failure transparency	障害透過性	Failures of some sites or links
Concurrency transparency	並行透過性	Whether it runs concurrently with transactions on other sites

Transparency means SQL and application programs need not change; it does not mean performance costs or failure effects disappear. Reading a remote site adds network latency, and as sections 4–5 show, failures and partitions force a choice between consistency and availability.

3. Partitioning data

Method	Unit of division	How to rebuild	Caution
Horizontal partitioning (水平分割)	Rows, e.g. by year or region	Union of the fragments	Fragments hold different rows; they are not copies
Vertical partitioning (垂直分割)	Columns, e.g. frequently and rarely used columns	Join on the primary key kept in each fragment	Without the key, the original rows cannot be rebuilt
Sharding (シャーディング)	Horizontal partitioning + a separate server per fragment	Find the owning server by the shard key	A poor shard key concentrates load on one server (a hotspot)

Partitioning is not replication. Horizontal partitioning splits rows so that they do not overlap; replication keeps overlapping copies of the same data. Real systems often combine the two (each shard is replicated in turn).
Partitioning and sharding: splitting a table into partitions inside one DBMS is also horizontal partitioning. Sharding is the term for spreading the fragments over independent servers; queries and transactions that span shards become the distributed processing of sections 6–7.

Worked example 1 — partition pruning and replica storage

Assumptions: a sales table of 12,000,000 rows with a fixed average row size of 600 bytes. Sizes are decimal (1 GB = 10^9 bytes) and are raw data sizes ignoring indexes, logs, page overhead and compression.

Raw size: 12,000,000 × 600 = 7,200,000,000 bytes = 7.2 GB.
Horizontal partitioning: 5 partitions by sales year, 2,400,000 rows each (assumed even). If the query asks for the last 3 years and the condition matches the partition key, ideally only 3 partitions are read (partition pruning). Rows read = 3 × 2,400,000 = 7,200,000 rows.
Reduction: (12,000,000 − 7,200,000) ÷ 12,000,000 = 4,800,000 ÷ 12,000,000 = 40% fewer rows scanned. The raw data read is 7,200,000 × 600 = 4.32 GB. Scanning 40% fewer rows does not guarantee a response time exactly 40% shorter (caching, indexes and parallelism matter).
Replication: 3 full replicas (3 copies in total, including the original) need 7.2 × 3 = 21.6 GB of raw data before overhead. Horizontal partitioning does not increase storage; replication multiplies it by the number of copies.
(Note) Vertical partitioning: say the 600 bytes are an 8-byte key + 92 bytes of frequently used columns + 500 bytes of rarely used columns. Both fragments carry the key, so the fragment rows are 100 and 508 bytes, 608 bytes in total: larger by one key. A query that needs only the frequently used columns reads just 12,000,000 × 100 = 1.2 GB. Joining the two fragments on the key rebuilds the original rows.

4. Replication and consistency

Method	When the commit is acknowledged	Advantage	Disadvantage
Synchronous replication	After the replica confirms receipt (or write)	Acknowledged updates are on the durable replica used for the acknowledgement; failover to that replica does not lose them	Higher response latency; if the replica is unreachable, updates stall or slow down
Asynchronous replication	As soon as the primary has written	Fast responses; suits long distances	During replication lag, replica reads return stale values; updates not yet shipped can be lost if the primary fails and the system fails over

Strong consistency: every replica read returns the latest committed value. The price is coordination between replicas, which adds latency and may require rejecting some requests during a partition.
Eventual consistency (結果整合性): a guarantee that once new updates stop, all replicas eventually converge to the same value. In the meantime, reads can be stale. It does not mean acknowledged updates may be lost arbitrarily. Durability (how many places record a write before it is acknowledged) and the conflict-resolution rule for concurrent updates must be designed and stated separately.
Replication is not backup. An accidentally deleted row or data corrupted by an application bug is shipped to the replicas just the same. Returning to a point in time needs separate backups and logs (recovery principles are in AP15).
Split brain (スプリットブレイン): if only the link between a primary and a standby site breaks, and the standby decides “the primary is dead” and promotes itself, both sides accept updates and the data diverges. Countermeasures: choose the primary only by majority (quorum) (with 3 nodes, only the side with 2 or more nodes may accept updates), a third witness node, and fencing that reliably cuts off the old primary. The side without a majority stops updating: it gives up availability there for the sake of consistency.

5. Network partitions and CAP

What the CAP theorem actually says: in a distributed system that replicates data, while a network partition (P) is in effect, the following two cannot both be fully provided.

C (consistency): every read returns the result of the latest write, or an error.
A (availability): every request to a non-failed node receives a (non-error) response.

If both sides keep answering during a partition (A first), neither knows the other’s updates, so they can return stale or conflicting values. To keep consistency (C first), one side (usually the side without a majority) must refuse or delay requests.

The common “pick any two of the three” explanation is inaccurate. In a real network a partition is a failure that can happen, so the choice arises between C and A when a partition occurs.
When there is no partition, CAP forces nothing. In normal operation C and A can both be provided. Even then there remains a trade-off between waiting for coordination among replicas (consistency) and answering quickly (latency), but that is a separate design question, not a claim of CAP.
BASE (Basically Available, Soft state, Eventually consistent) names a design approach that favours availability and eventual consistency, in contrast with ACID. It is not a specification that guarantees any property, and being BASE does not provide ACID atomicity.

6. Distributed queries and joins

Join semantics and SQL syntax are the same as in AP14. What changes in a distributed setting is where the computation runs and what is sent. In a distributed query, the volume transferred between sites largely determines the cost.

Select and project first: at each site, keep only the rows matching the condition (WHERE) and the needed columns before sending.
Send the smaller side: rather than moving a large table whole, send the smaller one to the larger one’s site and join there.
Semi-join (セミジョイン): site A sends only the join-column values to site B; B returns only the rows matching those values; A then finishes the join. Rows that take no part in the join are never sent, so the transfer volume falls. If sending the join-column values costs more than the rows saved, there is no gain.
Link to partitioning: if the condition matches the partition key, irrelevant sites need not be read at all (worked example 1). Joining or searching on a column that is not the shard key may require sending the query to every shard.

7. Two-phase commit

Two-phase commit (2相コミット) is the protocol that makes one transaction updating several sites either commit everywhere or roll back everywhere. It consists of a coordinator (コーディネータ) and participants (参加者). Atomicity and logging inside a single DBMS were covered in AP15.

Phase 1 (prepare and vote): the coordinator sends PREPARE to every participant. Each participant records what it needs to commit in its log on stable storage and votes YES (a promise that it can commit) or NO. A participant that votes NO rolls back its own part.
Phase 2 (decision): if all vote YES, the coordinator records the COMMIT decision in its log and then sends COMMIT. If any votes NO, or a vote does not arrive in time, it sends ABORT. Participants commit or roll back according to the decision and send an acknowledgement.

The blocking problem: a participant that has voted YES cannot, before receiving the decision, commit on its own (another participant may have voted NO) or roll back on its own (the coordinator may already have decided COMMIT and another participant may have committed). If the coordinator fails while a participant is in this uncertain (in-doubt) state, the participant waits, holding its locks, until it learns the decision. A participant that has not voted yet may roll back on its own. 2PC is therefore a protocol for atomicity and does not provide availability. Three-phase commit and consensus algorithms that reduce blocking are outside the scope of this article.

Worked example 2 — three cases of 2PC

One order updates both the stock in an inventory DB (site X) and the payment record in a payment DB (site Y). The coordinator is the order server.

Case	Phase 1 votes	Coordinator decision	Outcome
(a)	X: YES, Y: YES	Records COMMIT in its log, then sends it	X and Y both commit; done after acknowledgements
(b)	X: YES, Y: NO (limit exceeded)	ABORT	Y has already rolled back; X receives ABORT and rolls back. Stock is not reduced
(c)	X: YES, Y: YES (each participant sent its own vote)	Coordinator fails before it is certain to have received both votes and before any decision reaches a participant	X and Y are both uncertain. Even if they confirm that both participants sent YES, they cannot know whether the coordinator received both in time and recorded COMMIT, or missed one vote and recorded ABORT. They stay blocked, holding locks, until the coordinator recovers and resends the decision from its log

The key of (b) is “one NO rolls back everyone”; the key of (c) is “after YES, a participant cannot decide alone”.

8. Data warehouses and OLTP

Aspect	OLTP (business system)	Data warehouse (DWH)
Purpose	Business processing such as orders and payments	Analysis for decision-making
Workload	Many short update transactions	Aggregate queries reading many rows
Data	Mostly current values	Accumulated history (time series)
Sources	Usually one business area	Integrates several systems
Design	Mainly normalised to prevent update anomalies	Star schema and similar structures that are easy to aggregate (dimensions may be denormalised)
Updates	Frequent updates and deletes	Mainly periodic loads; loaded history is not overwritten

A DWH is often summarised as subject-oriented (サブジェクト指向), integrated (統合), time-variant (時系列) and non-volatile (不揮発). So a DWH is not simply a copy of an OLTP database. It integrates codes and notation from several sources, cleanses them (section 9) and stores them with history in an analysis-oriented structure (section 10).

Data mart (データマート): a small analytical database built from the DWH (or the sources) with only the data a particular department or purpose needs (for example, sales analysis).

9. ETL and ELT

Method	Order	Where the transformation runs	Characteristics
ETL	Extract → transform → load	Before loading, in a separate transformation process	Only cleansed data enters the DWH
ELT	Extract → load → transform	After loading, inside the DWH	Raw data is loaded first and transformed with the DWH’s processing power; the raw data can be transformed again

Data cleansing (データクレンジング): unifying notation (full-width/half-width characters, company-name spellings), unifying code systems, merging duplicate records (名寄せ), and handling missing and out-of-range values. Rows that break the rules are usually not discarded but kept in a reject list so the cause can be traced.

Worked example 3 — daily load counts

Assumptions: source A sends 1,200,000 rows a day and source B 800,000 rows a day. It is assumed that 2% of the combined input is rejected in cleansing or merged into existing rows, and so is not loaded as new rows. The same volume arrives every day for 30 days.

Combined input: 1,200,000 + 800,000 = 2,000,000 rows/day.
Rejected or merged: 2,000,000 × 0.02 = 40,000 rows.
Loaded: 2,000,000 − 40,000 = 1,960,000 rows/day.
30 days: 1,960,000 × 30 = 58,800,000 rows.

Caution: applying the 2% to source B only gives 2,000,000 − 16,000 = 1,984,000, a different answer. Always check in the question what the rate applies to.

10. Facts and dimensions

Fact table (ファクトテーブル): holds the measures of the events being analysed (sales amount, quantity) and foreign keys pointing to each dimension. It typically has the most rows.
Dimension table (ディメンションテーブル): the attributes of an analysis axis, e.g. product (name, category), region (store, city, area), period (day, month, quarter, year).
Star schema (スタースキーマ): dimension tables each linked one step away from a central fact table. The joins are simple, so aggregation is easy. Normalising the dimensions further into branches gives a snowflake schema (スノーフレークスキーマ).

11. OLAP operations

Data is viewed as a multidimensional cube such as product × region × month and analysed with the following operations.

Operation	日本語	What it does	Example
Roll-up	ロールアップ	Aggregates to a coarser level	Monthly → quarterly
Drill-down	ドリルダウン	Goes down to a more detailed level	Quarterly → monthly → daily
Slice	スライス	Fixes one dimension to a single value and views that cross-section	Product × month table for region = R1
Dice	ダイス	Picks ranges on several dimensions to cut out a smaller cube	Products P1–P5 × regions R1–R3 × January–March
Pivot	ピボット (回転)	Swaps the row and column axes to change the viewpoint; the aggregated values do not change	Rows = product / columns = month → rows = month / columns = product

Worked example 4 (optional) — cube cell count

40 products × 10 regions × 12 months = 4,800 cells at most. If only 1,500 combinations actually have sales, 3,300 cells are empty (sparse). Rolling months up to quarters (4) makes the maximum 40 × 10 × 4 = 1,600 cells.

12. Quick sheet and traps

Horizontal partitioning = rows, rebuilt by union. Vertical partitioning = columns, rebuilt by a join on the primary key. Sharding = horizontal fragments on separate servers.
Partitioning ≠ replication. Replication ≠ backup. Asynchronous replication = stale reads during lag, possible loss of unshipped updates on failover.
Eventual consistency = convergence once updates stop. Not permission for arbitrary loss.
CAP = choose between C and A during a partition. Without a partition, CAP forces nothing. BASE = an approach, not a guarantee.
Split brain → quorum, witness, fencing.
2PC = prepare and vote → all YES commits, any NO aborts. Coordinator failure after YES → blocked in the uncertain state. 2PC does not provide availability.
Distributed join = minimise transfer; semi-join.
DWH = subject-oriented, integrated, time-variant, non-volatile. Not a plain copy of OLTP. Data mart = purpose-specific subset.
ETL = transform then load; ELT = load then transform inside the DWH. Cleansing = unify, merge duplicates, handle missing values.
Roll-up = coarser, drill-down = finer, slice = fix one value, dice = several ranges, pivot = swap axes.

13. Five original diagnostic questions

The questions below are Motitory originals (unofficial); no IPA past question was used or adapted.

Q1 — Partitioning

Which statement about partitioning data is correct?

A. Horizontal partitioning stores the same rows on several nodes, so it is a form of replication.

B. In vertical partitioning, each fragment keeps the primary key, so joining on the key rebuilds the original rows.

C. Sharding splits a table by columns and places the parts on different servers.

D. Horizontally partitioned fragments are joined on the primary key to rebuild the original table.

A: Wrong. Horizontal fragments hold different rows. Keeping overlapping copies of the same data is replication.
B: Correct. The key must be in each fragment so that a join can restore the original rows (step 5 of worked example 1).
C: Wrong. Sharding places row-wise (horizontal) fragments on separate servers.
D: Wrong. Horizontal fragments are rebuilt by a union. Rebuilding by a key join is vertical partitioning.

Q2 — Failure in two-phase commit

In two-phase commit, participant P1 voted YES, and then the coordinator failed before P1 received the decision. Which statement about P1 is correct?

A. P1 voted YES, so it may commit on its own.

B. If no decision arrives within a set time, P1 may roll back on its own.

C. P1 is in an uncertain state and must keep its prepared state and locks until it can learn the decision.

D. Two-phase commit guarantees that all participants keep processing even if the coordinator fails, so availability is unaffected.

A: Wrong. Another participant may have voted NO, and the coordinator may have decided ABORT.
B: Wrong. The coordinator may already have decided COMMIT and another participant may have committed, so rolling back alone on a timeout breaks atomicity.
C: Correct. After YES a participant cannot decide alone and is blocked (case (c) of worked example 2).
D: Wrong. 2PC is a protocol for atomicity and can block participants when the coordinator fails. It does not provide availability.

Q3 — Consistency and CAP

Which statement about a replicated distributed database is correct?

A. By the CAP theorem, a distributed database can freely pick any two of C, A and P at any time.

B. While a network partition separates the replicas, availability (answering every request) and consistency (always returning the latest value) cannot both be fully provided.

C. Eventual consistency means acknowledged updates may be lost arbitrarily as long as the replicas eventually agree.

D. BASE is a specification that guarantees the same atomicity as ACID with higher performance.

A: Wrong. The choice arises between C and A when a partition occurs. “Any two” is inaccurate, and without a partition CAP forces nothing.
B: Correct. This is the accurate statement of the CAP theorem.
C: Wrong. Eventual consistency guarantees convergence once updates stop; it is not a definition that permits arbitrary loss. Durability and conflict resolution are designed separately.
D: Wrong. BASE is a design approach favouring availability and eventual consistency, not a guarantee, and it does not mean ACID atomicity is provided.

Q4 — ETL arithmetic

Source A sends 900,000 rows a day and source B 600,000 rows a day. Assume 4% of the combined input is rejected or merged in cleansing and not loaded. If the same volume arrives every day for 7 days, how many rows are loaded over the 7 days?

A. 10,500,000 rows

B. 10,332,000 rows

C. 10,080,000 rows

D. 1,440,000 rows

A: Wrong. (900,000 + 600,000) × 7 does not subtract the 4% rejected or merged.
B: Wrong. It applies the 4% to source B only: 600,000 × 0.04 = 24,000, (1,500,000 − 24,000) × 7 = 10,332,000.
C: Correct. 1,500,000 × 0.04 = 60,000; 1,500,000 − 60,000 = 1,440,000 rows/day; × 7 = 10,080,000 rows.
D: Wrong. That is the daily load, not the 7-day total.

Q5 — DWH and OLAP

You analyse a sales cube of product × region × month. Which statement is correct?

A. Changing the view from monthly totals to quarterly totals is a drill-down.

B. Fixing the region to R1 alone and viewing the product × month table is a slice.

C. Swapping a report’s rows and columns changes the aggregated values; this operation is called a dice.

D. A data warehouse is a nightly copy of the OLTP database without transformation, so analysis uses the same normalised schema as is.

A: Wrong. Moving to a coarser level is a roll-up; drill-down goes the other way.
B: Correct. Fixing one dimension to a single value and viewing the cross-section is a slice.
C: Wrong. Swapping axes is a pivot, and the aggregated values do not change. A dice picks ranges on several dimensions to cut out a smaller cube.
D: Wrong. A DWH integrates and cleanses several sources and keeps history, and it often uses an analysis-oriented structure such as a star schema.

Answers: B, C, B, C, B

14. Self-check and review schedule

I can distinguish horizontal partitioning, vertical partitioning, sharding and replication by “what is split and how it is rebuilt”.
I can recompute the 7.2 GB, 40% and 21.6 GB of worked example 1 together with its assumptions.
I can explain the trade-off between synchronous and asynchronous replication and why replication is not backup.
I can state the CAP theorem accurately, including the condition “during a partition”.
I can describe the two phases of 2PC and why the uncertain state after a YES vote blocks.
I can explain the difference between a DWH and OLTP, what a data mart is, and the difference between ETL and ELT.
I can recompute the 1,960,000 rows/day and 58,800,000 rows of worked example 3.
I can distinguish roll-up, drill-down, slice, dice and pivot with examples.

Suggested review cycle (separate from the publishing schedule): same day, redo worked examples 1–3 without looking → day 1, Q1–Q5 → day 3, fill in the terminology table from the quick sheet alone → day 7, change the row counts, rates and number of copies to rework worked examples 1 and 3, and recheck the questions you missed.

15. Official sources

IPA exam outlines and syllabus overview — the IPA page where the exam outline and syllabus of each exam category are published; used to confirm where the AP syllabus is officially posted. Checked 2026-10-22.
IPA Applied Information Technology Engineer Examination Syllabus Ver.7.2 (PDF) — the syllabus text that defines the exam scope. This article maps to major category 3 / middle category 9 Database / 5. データベース応用, items (1) データベースの応用 and (2) 分散データベース. Checked 2026-10-22.
IPA syllabus revision information — the page for the syllabus revision history and changes. Checked 2026-10-22.
IPA 2026 exam information — the page for the 2026 exam schedule and administration; used for exam administration details, not for the exam scope. Checked 2026-10-22.

This article is the third installment of the AP33–38 completion plan. The next planned topic is AP36 facility management, and the series ends at AP38.