Data architecture designer 🏗️

Most architecture decks say the same six words — medallion, lakehouse, governance, lineage, scalable, AI-ready — and leave every number blank. This does the opposite. Describe the requirement and it produces a specific architecture: which service reads which source, how much storage each layer actually needs, what warehouse size your concurrency demands, what it costs a month, how long the historical load takes on the wire, and which risks your own choices just created.

Named services

Every layer maps to a real product, chosen from your source mix and cloud.

Real numbers

Storage per layer, warehouse size, transfer days, monthly run cost.

Honest gaps

Skip backup or row-level security and it says so, in the diagram and the risk log.

Exportable

Copy the whole thing as Markdown, or the diagram as Mermaid.

Start from an RFP

Paste the requirements section of an RFP and it fills the questionnaire below, showing what it read directly and what it inferred. Nothing is costed until you have checked it.

Start from a reference scenario

Recommended platform

AWS + Databricks

S3 lakehouse · Glue/DMS · Databricks · Unity Catalog

Under management

4.5 TB

Latency target

T+1

All-environment run cost

$7,439/mo

In rupees

₹6,54,632/mo

Why

AWS is the target: S3 as the lakehouse floor, Databricks for Spark and SQL, and Kinesis for the streaming path.
S3 scales horizontally without a capacity decision, and EC2 spot for jobs compute typically takes 35–45% off transformation cost.

Also considered

Azure + Databricks
Cheaper ingestion (ADF folds five AWS services into one) and Purview bundles catalog, lineage and classification.
Microsoft Fabric
If Power BI is the primary consumption surface, Direct Lake removes the refresh window entirely.

Architecture diagrams

Two views of the same design, drawn with the official AWS and Azure architecture icon sets. Export as PNG, SVG, or an editable PowerPoint deck where every box is a real shape you can move and retype.

The delivery pipeline end to end, in the shape a steering committee expects

Icons are the official AWS and Microsoft Azure architecture sets plus vendor brand marks — attribution and licensing. The diagram renders on a light canvas in both themes so exports drop straight into a deck. The PPTX is 11 slides of native PowerPoint shapes, text boxes and tables — nothing is a flattened screenshot, so you can edit every element.

Layer detail

Data flows left to right. The bars underneath apply to every layer — governance, quality, security and operations are not a phase, they are a property of the platform.

Sources

3 source families

Transactional databases

Oracle, SQL Server, Postgres, MySQL — CDC from the log

Other SaaS / APIs

ServiceNow, Sprinklr, SuccessFactors, marketing tools

Files & spreadsheets

SFTP drops, Excel, CSV, syndicated market data

Ingestion

Batch & CDC

AWS DMS — continuous CDC to S3 Parquet

Transactional databases

Amazon AppFlow + API Gateway/Lambda for unsupported endpoints

Other SaaS / APIs

AWS Transfer Family (SFTP) + DataSync into the landing bucket

Files & spreadsheets

Databricks Workflows (avoids an always-on Airflow control plane)

Orchestration & scheduling

Lakehouse — medallion

4.5 TB

Amazon S3 (ap-south-1)

4.5 TB under management including archive

Bronze — raw, immutable

Append-only, source-faithful, partitioned by load date. 2.1 TB compressed.

Silver — cleansed & conformed

Deduplicated, typed, referential integrity enforced, business keys resolved. 1.5 TB.

Gold — business-ready

Star schemas and certified marts per function. 184 GB.

Databricks (Spark, Delta Lake, DLT)

Transformation engine — incremental ETL, schema evolution, quality gates

Semantic & serving

T+1

Databricks Unity Catalog + AWS Glue Data Catalog

Business glossary, classification, lineage, one place for access grants

Databricks SQL warehouse — Small serverless

Max 2 clusters, sized for 15 concurrent of 120 total users

Row & column-level policies

One entitlement table, enforced at the catalog and inherited by every tool

Consumption

120 users

Power BI

120 users, 15 concurrent at peak

Paginated & scheduled reports

Statutory and operational distribution, PDF/Excel bursting

Self-serve analytics

SQL editor and notebooks over Gold, governed by the same catalog

Governance & catalog

Databricks Unity Catalog + AWS Glue Data Catalog
Business glossary with owner and steward per domain
End-to-end lineage: source → Bronze → Silver → Gold → report
Impact analysis before any schema change
Quarterly access review and recertification

Data quality

Profile: column stats, null and duplicate analysis, volume baselines
Validate: schema conformance, range and pattern checks, referential integrity
Monitor: freshness SLA, row-count reconciliation against source, schema drift
Alert: P1/P2/P3 tiers, auto-quarantine, pipeline pause on breach
Report: quality scorecard per domain, trend view, monthly governance review

Security

AES-256 at rest, TLS 1.3 in transit, customer-managed keys
MFA, SSO against the corporate directory, least privilege per domain
Row and column-level security defined once at the catalog
Private networking only: no public endpoints on storage or compute
Compliance: India DPDP Act 2023 · ISO/IEC 27001

DevOps & resilience

Terraform for every resource — environments are reproducible, not hand-built
CI/CD with dev → UAT → prod promotion and automated tests on each layer
Cross-region replication, object versioning, RTO < 4h / RPO < 1h
24×7 pipeline monitoring with paging on freshness and failure
Capacity plan validated against 10× user growth

Scroll the panel sideways on a narrow screen.

Sizing

Storage is derived from the volumes you entered, assuming columnar storage with zstd compression — roughly a 3× reduction on typical ERP and CRM row data.

Historical load	5 TB	One-time migration
Archived data	2 TB	Cold tier, retrieval on request
Daily incremental	3 GB/day	1.1 TB in year one
Bronze	2.1 TB	Raw, immutable, source-faithful
Silver	1.5 TB	Conformed, deduplicated, typed
Gold	184 GB	Certified marts and aggregates
Hot tier	2.4 TB	Standard access, queried daily
Cool tier	1.4 TB	Bronze older than 90 days
Archive tier	717 GB	Retention only
Total under management	4.5 TB

Latency target

T+1, Gold layer certified before 06:00 local

Initial load

a few hours of continuous transfer for 7 TB over Cloud-to-cloud transfer at ~2.5 Gbps effective (~26 TB/day).

Consumption plan

Capacity sized from the concurrent and total user counts you entered.

Power BI

Capacity: F16 capacity (Power BI Premium) — sized for 15 concurrent of 120 total users
Connectivity: DirectQuery or a composite model against Databricks SQL warehouse — Small serverless. At ~22 GB the model will not import into F16's 5 GB ceiling, and buying a larger capacity to hold it is the expensive way to solve a modelling problem
Licensing: 8 × Pro for authors; viewers are covered by the capacity

Capacity is picked from concurrent query load, not lake size. The reporting model is assumed at ~12% of Gold (22 GB) — a certified subset, not the whole layer.
Row-level security defined once in the catalog and inherited, not re-implemented in DAX.
If the business insists on import, cut the model down — narrower tables and fewer years beat a bigger SKU.

Scalability — today vs 10×

What actually has to change if the user population grows an order of magnitude. Most of it is configuration; the one thing that is not is your partitioning strategy.

Dimension	Today	At 10×
Concurrent dashboard users	15 → Small warehouse, max 2 clusters	150 → X-Large warehouse, max 10 clusters (scale out, not up — queue depth is the trigger)
Serving cost	$976/mo	$14,040/mo — sub-linear, because extra clusters only spin up under queue pressure
Storage under management	4.5 TB	14 TB at 10× ingest — lifecycle rules move Bronze to cool at 90 days and archive at 400
Transformation compute	Autoscaling job clusters, spot for non-critical tasks	Same code, wider clusters. Partition Silver by date + business key now so a 10× table does not force a rewrite later.
Ingestion	Scheduled batch windows	Batch windows will not stretch 10×. Move the largest three tables to CDC before volume forces it.
Governance	2 compliance regimes in scope	Access reviews stop being manual at ~10× users. Budget for automated entitlement provisioning from HR data.

Indicative monthly run cost

List-price estimate built from the volumes, concurrency and services above. Treat it as a band for budgeting, not a quote — reserved capacity, enterprise agreements and licences you already own all move it.

Group	Item	Quantity	Unit rate	USD / mo
Storage	S3 StandardSilver, Gold and recent Bronze	2.4 TB	$0.025 / GB-Mo✓ fetched · AWS Price List	$61
Storage	S3 Standard-IABronze older than 90 days	1.4 TB	$0.0138 / GB-Mo✓ fetched · AWS Price List	$20
Storage	S3 Glacier IRRetention only	717 GB	$0.005 / GB-Mo✓ fetched · AWS Price List	$4
Storage	Write operationsIngest and compaction writes	50M requests	$0.000005 / Requests✓ fetched · AWS Price List	$250
Ingestion	AWS DMSdms.r5.large, continuous CDC	730 h	$0.53 / Hrs✓ fetched · AWS Price List	$387
Ingestion	Glue ETL & crawlers2 source families, 2 DPU x 2 h/day	240 DPU-h	$0.44 / DPU-Hour✓ fetched · AWS Price List	$106
Compute	Databricks jobs compute5 nodes x 60 h, daily	900 DBU-h	$0.15 / DBU-hour◆ maintained · databricks.com/product/pricing — A	$135
Compute	Underlying instancesr5.4xlarge-class workers beneath the DBUs	300 node-h, 60% spot	$1.216 / instance-hour◆ maintained · aws.amazon.com/ec2/pricing — on-de	$164
Compute	SQL warehouse (serving)Small · max 2 clusters · 15 concurrent	1742 DBU-h	$0.7 / DBU-hour◆ maintained · databricks.com/product/pricing — A	$1,220
BI licences	Power BI F16 capacityReserved 1-year · 15 concurrent users	1 capacity	$2102.4 / month◆ maintained · azure.microsoft.com/pricing — Fabr	$2,102
Governance	Glue Data CatalogTable and partition metadata	2M objects	$0.00001 / Obj-Month✓ fetched · AWS Price List	$20
Governance	Macie classificationPII discovery across the lake	40 buckets x 30 days	$0.003288 / Bucket-days✓ fetched · AWS Price List	$4
Security	CloudWatch LogsPipeline, VPC flow and audit logs	735 GB/mo	$0.67 / GB✓ fetched · AWS Price List	$492
Security	GuardDutyThreat detection over CloudTrail and S3 events	294 GB	$1.1 / GB✓ fetched · AWS Price List	$323
Security	KMS customer managed keysPer-domain key separation	8 keys	$1 / Keys✓ fetched · AWS Price List	$8
Resilience	Backup & cross-region replicationImmutable, RPO < 1 hour	2.6 TB	$0.0385 / GB-month✓ fetched · AWS Price List	$102
Networking	Data transfer outEgress to consumers and partners	500 GB/mo	$0.1093 / GB✓ fetched · AWS Price List	$55
Production				$5,453
Development & UAT (30% of production)				$1,636
DevOps & tooling				$350
All environments · ₹6,54,632 per month				$7,439

Power BI F16 capacity · 39% of productionSQL warehouse (serving) · 22% of productionCloudWatch Logs · 9% of production

Every line is quantity × unit rate, so any figure can be re-derived. Rates marked ✓ fetched come from the official AWS Price List and Azure Retail Prices APIs, snapshotted 2026-07-28 for ap-south-1 and centralindia. Rates marked ◆ maintained are transcribed from published price lists (reviewed 2026-07-28) because those vendors publish no pricing API. Converted at USD 1 = INR 88. BI licence lines assume net-new purchases — subtract anything you already own.

Delivery roadmap

Phase durations scale with the number of source families and the size of the historical load.

1 · Discover & design
Week 1–3
- Source-system profiling and data inventory across 3 source families
- Source-to-target mapping and the conformed dimension list
- Medallion layer contracts — what Bronze guarantees, what Silver conforms, what Gold certifies
- Security and compliance blueprint for 2 regimes
2 · Foundation
Week 4–9
- Landing zone, network and IAM provisioned as Terraform — no click-ops
- Amazon S3 (ap-south-1) bucket/container layout with lifecycle rules
- CI/CD pipelines, secret store, environment promotion path (dev → UAT → prod)
- Customer-managed keys, TLS 1.3 enforced, encryption verified end to end
3 · Ingest & migrate
Week 10–16
- Historical load of 5 TB
- Scheduled incremental loads with watermark tracking
- Bronze ingestion audit: row counts reconciled against source for every table
4 · Transform & govern
Week 17–22
- Silver conformance and Gold business marts built and certified
- Data quality gates at every hop, with quarantine and alerting
- Catalog populated, glossary agreed with function owners, lineage visible end to end
- Row-level security policies defined once at the catalog and inherited by every consumer
5 · Enable consumption
Week 23–28
- Power BI semantic model and the first certified dashboard set
- Onboarding and training for 120 users, 15 concurrent at peak
6 · Operate & optimise
Continuous, from go-live
- 8-week hypercare, then steady-state L1→L3 support
- 24×7 pipeline monitoring, P1 response < 1 hour
- Monthly cost review — spot coverage, warehouse right-sizing, lifecycle policy tuning
- Quarterly capacity review against the 10× growth plan

Risks & mitigations

Derived from the gaps in what you selected. The high-severity ones are the answers a steering committee will ask for.

Severity	Risk	Mitigation
medium	No agreed single version of business definitions	Function owners must sign off calculation rules before build, not during UAT. Ambiguous definitions surface as 'the number is wrong' three weeks after go-live.
medium	Business availability across project phases	Agree the percentage engagement per function at kick-off and name alternate approvers for BRD and UAT sign-off.

AI review

Optional. The engine above is deterministic — this reads the finished blueprint and adds judgement the rules cannot encode. It writes prose; it never changes a number.

A second read over the finished blueprint, looking for the interactions and organisational realities the rule-based risk log cannot encode. It comments only — no number above changes.

Context the questionnaire could not capture (optional)

Export

Take the blueprint into a document, a ticket or a diagramming tool.

Preview the Mermaid source

flowchart LR
  subgraph L0["Sources"]
    direction TB
    n0_0["Transactional databases"]
    n0_1["Other SaaS / APIs"]
    n0_2["Files & spreadsheets"]
  end
  subgraph L1["Ingestion"]
    direction TB
    n1_0["AWS DMS — continuous CDC to S3 Parquet"]
    n1_1["Amazon AppFlow + API Gateway/Lambda for unsupported endpoints"]
    n1_2["AWS Transfer Family SFTP + DataSync into the landing bucket"]
    n1_3["Databricks Workflows avoids an always-on Airflow control plane"]
  end
  subgraph L2["Lakehouse — medallion"]
    direction TB
    n2_0["Amazon S3 ap-south-1"]
    n2_1["Bronze — raw, immutable"]
    n2_2["Silver — cleansed & conformed"]
    n2_3["Gold — business-ready"]
    n2_4["Databricks Spark, Delta Lake, DLT"]
  end
  subgraph L3["Semantic & serving"]
    direction TB
    n3_0["Databricks Unity Catalog + AWS Glue Data Catalog"]
    n3_1["Databricks SQL warehouse — Small serverless"]
    n3_2["Row & column-level policies"]
  end
  subgraph L4["Consumption"]
    direction TB
    n4_0["Power BI"]
    n4_1["Paginated & scheduled reports"]
    n4_2["Self-serve analytics"]
  end
  n0_0 --> n1_0
  n0_1 --> n1_1
  n0_2 --> n1_2
  n1_0 --> n2_0
  n1_1 --> n2_0
  n1_2 --> n2_0
  n2_0 --> n2_1
  n2_1 --> n2_2
  n2_2 --> n2_3
  n2_3 --> n3_1
  n3_1 --> n4_0
  n3_1 --> n4_1
  n3_1 --> n4_2

Data architecture designer 🏗️

Start from an RFP

Start from a reference scenario

Sources

What exists today

Volumes

Hosting

Target platform

Modelling & consumption

Users & refresh

Security & resilience

Compliance & privacy

AWS + Databricks

Why

Also considered

Architecture diagrams

Layer detail

Sizing

Latency target

Initial load

Consumption plan

Power BI

Scalability — today vs 10×

Indicative monthly run cost

Delivery roadmap

1 · Discover & design

2 · Foundation

3 · Ingest & migrate

4 · Transform & govern

5 · Enable consumption

6 · Operate & optimise

Risks & mitigations

AI review

Export