Database Selection & Data Flow Design: Building Scalable Data Systems

Data is the one thing every solution has, and the database is the decision you live with longest. This tutorial covers how to pick the right kind of store for the job, then how to design the paths data travels — from entry to transformation to storage to consumption.

Table of Contents

• Why the Database Decision Matters
• The Main Families of Data Stores
• Choosing by Access Pattern
• Managed vs. Self-Hosted
• What Is a Data Flow?
• The Four Stages of a Data Flow
• Designing a Data Flow, Step by Step
• Data Quality Gates
• Working Example: Delivery Data, End to End
• Best Practices

Why the Database Decision Matters

The database is the hardest component to change later. You can swap a frontend framework in a week; migrating a production database with months of customer data in it is a project of its own. The choice also shapes everything downstream — query speed, scaling strategy, backup plans, and how easily reports get built.

The good news: the decision is mostly about fit, not about which database is "best." Match the store to how the data is actually accessed, and the rest follows.

The Main Families of Data Stores

Note: a relational database — usually PostgreSQL — is the right answer for the large majority of FDE solutions. Business data is structured, relationships matter, and SQL is the universal reporting language. Start there, and add a specialist store only when a specific requirement demands it.

Choosing by Access Pattern

The reliable way to choose is to list how the data will actually be read and written, then match:

Example for the delivery solution:

Access pattern                              → Store
-----------------------------------------------------------------
Delivery records, drivers, routes, joins    → PostgreSQL
Live driver location, refreshed every 30s   → PostgreSQL (fine at this scale)
Session data and hot dashboard counts       → Redis cache
Signed proof-of-delivery photos             → Object storage, URL in Postgres

Notice the pattern: one primary store, specialists only where justified. Every additional store type is another thing to back up, monitor, and explain.

Common Mistake: choosing a database because it's trendy or because one requirement sounds exotic. "We might do ML someday" is not a reason to add three stores today.

Managed vs. Self-Hosted

For each store, decide who operates it:

For a small-team FDE build, managed wins almost by default — automated backups and point-in-time recovery alone are worth it. Self-hosting becomes the answer mainly when data must stay inside the customer's network.

What Is a Data Flow?

A data flow is the path a piece of data travels: where it enters the system, what happens to it along the way, where it's stored, and who consumes it. If the database decision is about where data lives, data flow design is about how data moves.

Most data bugs are flow bugs: data arrives in the wrong format, gets transformed twice, lands in two places that disagree, or is consumed before it's complete. Drawing the flow is how you catch those on paper.

The Four Stages of a Data Flow

Every flow is a chain of four stages:

[ Ingest ] → [ Transform ] → [ Store ] → [ Serve ]
   enter        clean/shape     persist      read/report

1. Ingest — data enters: user input, API call, file upload, sensor, third-party feed.
2. Transform — validation, cleaning, enrichment, calculation. ("Raw driver confirmation" becomes "a delivery record with a computed on-time flag.")
3. Store — written to the chosen store(s), in the shape consumers need.
4. Serve — read back: dashboards, reports, APIs, exports, downstream systems.

Two design questions dominate:

• Where does transformation happen? Transforming at ingest means stored data is clean and ready, but you must know the rules now. Transforming at serve time keeps storage raw and flexible, but every consumer repeats the work. A common split: validate at ingest, aggregate at serve time.
• How fresh must the data be? Real-time flows push each record through immediately; batch flows collect and process on a schedule. Freshness is a business requirement — ask, don't assume.

Designing a Data Flow, Step by Step

1. Name the data. What exactly flows? List the fields, not just "the delivery data."
2. Find the source of truth. For each piece of data, exactly one system owns it. Everything else holds copies.
3. Trace the path. Draw source → transformations → store → consumers (a labeled data flow diagram from Tutorial 2).
4. Place the quality gates. Decide where validation and deduplication happen.
5. Set the freshness. Real-time or batch, per consumer — not one answer for everyone.
6. Plan the failures. What happens to in-flight data when a stage fails mid-flow?

Data Quality Gates

Data quality is enforced at specific points in the flow, not "somewhere":

• Validate at the door. Reject or quarantine bad input at ingest — wrong types, missing required fields, impossible values. Cheap to reject here, expensive to clean later.
• Deduplicate by identity. Give every record a stable external ID and upsert on it, so retries and resends can't create twins.
• Enrich deliberately. Computed fields (on-time flag, region, totals) should be computed once, in one named place — not independently by three consumers.
• Audit the changes. For data that matters (money, statuses), keep who-changed-what-and-when. A simple audit_log table prevents endless arguments.

Best Practice: the earlier a quality problem is caught, the cheaper it is. A validation rule at ingest costs one function; the same problem discovered in the monthly report costs a reconciliation project.

Working Example: Delivery Data, End to End

Designing the full flow for the logistics solution:

INGEST        TRANSFORM              STORE                SERVE
─────────     ──────────────────     ─────────────────    ──────────────────
Driver app    Validate fields        PostgreSQL           Office dashboard
confirmation  Attach driver/route    (deliveries table,   (live status)
   │          Compute on-time flag    one source of
   │                                  truth)              Daily KPI report
   ▼                                    │                 (batch, 6am)
Sync API ────────────────────────────▶  │
                                        ▼                 ERP invoice sync
Proof photo ──▶ Object storage ──▶ URL stored in row      (every 15 min,
                                                          Tutorial 4)

The decisions, stage by stage:

Failure plan: confirmations queue on the phone when offline (ingest can't fail in a dead zone); the ERP sync retries and leaves a flag on unsynced rows (Tutorial 4). Every stage has a defined behavior for "the next stage is down."

Best Practices

• One source of truth per fact. Copies are fine; two writable copies are a disagreement waiting to happen.
• Store raw, serve shaped — within reason. Keeping original input alongside computed results lets you recompute when the rules change.
• Design the flow before the schema. Knowing how data moves tells you what tables you need; the reverse leads to schemas that fight the flow.
• Batch is underrated. Most "real-time" needs are actually "within minutes" needs, and batch flows are simpler at every stage.