Data Architecture Validation SOP

A data architecture validation SOP checks that the way you store, connect and copy data will survive real customers, real volume and a real bad day, and it takes about two weeks. You map the data model and where data travels, fill a test database with 10 times your expected volume, and time, break and restore it on purpose. Code can be rewritten in a weekend; a broken data model drags every customer record with it. You end up with a model you trust, a tested restore and a written answer to "where does this customer's data live?"

What is the data architecture validation SOP and when should you use it?

Data architecture is the plan for what data you keep, how tables and records relate, where data flows (app, analytics, email tool, backups) and how long it stays. Validation tests that plan against four things: integrity (data stays correct), speed at volume (queries stay fast as tables grow), recovery (you can get it back) and purpose (you keep only what you need, and can delete it).

It differs from its neighbours. The technical architecture review looks at the whole system's structure. The technical scalability assessment tests traffic load. Digital behavior tracking designs analytics events. This SOP zooms in on the data stores.

Use it:

• Before the first paying customer, while changing the schema needs no data migration.
• When moving from a no-code tool or spreadsheet to a real database.
• Before a large customer signs, if their contract asks about data location, retention or deletion.
• When reports disagree, such as revenue in the dashboard not matching revenue in the payment provider.

What do you need before you start?

• Read access to the schema and a staging database you can fill and break.
• An entity relationship diagram (ERD), generated from the schema with a tool such as dbdiagram.io.
• A list of your 10 most frequent and 5 heaviest queries, from pg_stat_statements in PostgreSQL or your database's equivalent.
• Volume targets: rows per main table at 12 and 24 months.
• Recovery targets in writing: RPO (recovery point objective: how much recent data you can lose) and RTO (recovery time objective: how long you can be offline).

How do you run it, step by step?

1. Map the model and every data flow

Print the ERD and draw arrows from each table to every place its data goes: analytics, email tool, CRM, data warehouse, logs, backups. Mark fields holding personal data and, separately, special categories under GDPR Article 9, such as health data. Copies you didn't plan are where leaks and wrong reports come from.

2. Check the model's structure

Look for repeated data that drifts out of sync; Edgar F. Codd's normal forms, up to third normal form, give you the test. Confirm money is stored as integer cents or a fixed decimal type (never floating point), timestamps are stored in UTC with time zone, and every table holding customer data carries a customer or tenant ID.

3. Enforce integrity in the database

Add NOT NULL, UNIQUE and CHECK constraints and foreign keys where the app code assumes them. Then run queries that hunt for violations already present: orphan records, duplicate emails, negative balances. Every violation found today is a bug that wrote bad data yesterday.

4. Load 10x data and time the queries

Generate realistic fake data with Faker or PostgreSQL's generate_series up to 10 times your 12-month volume, with the same skew as reality (a few large customers, many small ones). Run your top queries with EXPLAIN ANALYZE and record p95 times. Add indexes for slow queries and retest.

5. Run a real restore

Restore last night's backup to a fresh server, time it and check the data with row counts and a few known records. Compare the result with your RPO and RTO. Follow the 3-2-1 rule popularised by photographer Peter Krogh: three copies, on two kinds of storage, with one off-site.

6. Write the retention and deletion rules

For every personal data field, record its purpose, who can see it, how long you keep it and how you delete it from every copy, including analytics and backups. GDPR Article 5 requires data minimisation and Article 17 gives people a right to erasure, so test one deletion request end to end.

7. Record the decisions

Document the model, the targets and the test results in one page, and add an architecture decision record for each change.

Template you can copy

DATA ARCHITECTURE VALIDATION SHEET

Part A: Data inventory (one row per field with personal data)
Table | Field | Personal / special category | Purpose |
Copied to (analytics, CRM, email, logs, backups) | Who can read |
Retention period | Deletion method | Tested Y/N

Part B: Integrity checks
Check | Query used | Violations found | Fixed | Constraint added Y/N
Orphan records | | | |
Duplicate unique values | | | |
Invalid values (negative, out of range) | | | |
Missing tenant ID | | | |

Part C: Volume test (10x of 12-month volume)
Query | Calls per day | p95 today | p95 at 10x | Index added | p95 after fix

Part D: Restore drill
Backup date | Restore started | Restore finished | Total time |
RTO target | RPO achieved | RPO target | Data checks passed Y/N

Worked example: a leave-tracking tool for small companies

This is an illustrative case. A two-founder team sells leave and holiday tracking to companies with 20 to 200 staff. Their PostgreSQL database has 38 tables. They expect 500,000 leave records within a year, so they generate 5 million for the test.

The team calendar query, the most used screen, takes 4.1 seconds at p95 on the big dataset. A composite index on company and start date brings it to 45 ms. Integrity checks find 1,180 leave records pointing at deleted employees, and leave balances stored as floating point numbers that show values like 2.9999999 days. Foreign keys with clear delete rules and a switch to a fixed decimal type fix both.

The restore drill is the shock: nobody had tried one, and it takes 3 hours 40 minutes against an RTO target of 1 hour, with up to 24 hours of lost data against a 1-hour RPO. Turning on point-in-time recovery at their host fixes the RPO, and a smaller, faster restore server brings the restore to 50 minutes. The data inventory also shows that free-text sick leave reasons, which count as health data, flow into the product analytics tool. They stop sending that field and delete the copies.

What should you measure, and when do you stop?

Stop when every row passes and a second restore drill gives the same result. Repeat the restore drill every quarter and the volume test whenever a main table grows past half of the tested volume.

Which mistakes should you avoid?

• Testing on a tiny database. Indexes look unnecessary at 1,000 rows and become urgent at 1 million.
• Assuming backups work. A backup you have never restored is a hope.
• Storing money as floating point. Rounding errors pile up in invoices and balances.
• Letting the app enforce all rules. A script, an import or a second service will bypass app code; database constraints catch them.
• Copying everything into analytics. Each copy adds a deletion task and a leak risk.

How can AI speed this up?

Paste your schema (without data) into an AI assistant and ask it to flag missing foreign keys, unindexed columns used in WHERE clauses, floating point money and tables without a tenant ID. Ask it to write the Faker script for your 10x dataset and the integrity-hunting queries. Paste EXPLAIN ANALYZE output and ask what the plan says in plain words.

The human check: never paste real customer records into a public AI tool, and test every suggested index on staging, since extra indexes slow down writes. The F/MS AI for startups module covers how to use AI with company data safely.

Where does this SOP fit in your validation plan?

This SOP is part of the Technical Validation SOPs:

1. Before it: settle the overall design with the technical architecture review.
2. Alongside it: protect the data you mapped with the security validation SOP, and check data coming in from vendors with integration testing.
3. After it: test traffic growth with the technical scalability assessment.

To practise building a product from first idea to paying users, the Fe/male Switch startup game gives first-time founders deadlines and feedback.

FAQ

What is data architecture validation?

It is a set of tests that checks your data model, integrity rules, query speed at future volume, backups and personal data flows before they cause outages, wrong reports or legal trouble.

How much test data do I need?

Ten times your expected 12-month volume, with a realistic mix of large and small customers. Smaller tests hide slow queries.

How do I know if my backups work?

Restore one to a fresh server, time it, and check row counts and a few known records. Do it every quarter and after any change to your hosting.

Do small startups need a data retention policy?

Yes, if you hold personal data of people in the EU or UK, GDPR requires you to keep it no longer than needed. A one-page table with purpose, retention and deletion method per field is enough to start.