Redshift in database architecture

The canonical Redshift lesson owns SQL syntax, warehouse distribution, loading, workload management, maintenance, Serverless and cost guidance. This note deliberately avoids a duplicate implementation tutorial that can drift.

When an application needs a warehouse

Use a relational/document/key-value database for operational reads/writes that match its workload. Send analytical datasets into Redshift when joins, large aggregates and BI require a columnar warehouse. This is an application architecture choice, not a claim that Redshift replaces an OLTP database.

Prerequisites: data-store selection, relational databases and analytics. Define ingestion lag, supported transformations, schema ownership and access controls. COPY/UNLOAD, Spectrum and workload-management configuration belong to the warehouse lesson, not invented PostgreSQL-compatible administration statements.

Architectural exercise

Design a transactional order store plus an analytical dashboard. Identify the source of truth, ingestion boundary, accepted reporting delay, personal-data permissions and reconciliation evidence. Test a small load and compare row counts plus representative totals with the source; a successful load is not proof of complete/accurate reporting.

Decide separately how the operational database and warehouse meet RTO. A BI replica is not an automatically application-compatible restore target. Budget warehouse compute, managed storage, scans, loading/network and retained backups. Remove sandbox warehouse resources and staging outputs after the test.

Operational-to-analytical architecture

Operational order database
  → supported batch/CDC ingestion
  → private staging datasets/catalog
  → Redshift warehouse tables
  → authorized BI/query consumers

The transactional store owns customer-visible writes and constraints. The warehouse owns analytical models and aggregates; late events/backfills need reconciliation rather than silently replacing operational truth. Define whether the warehouse consumes immutable events, snapshots or CDC, and which component owns deletes/corrections.

Concern	Operational database	Warehouse
Request path	Point/range/transactional application reads/writes	Larger scans, joins and analytical workloads
Data model	Application constraints/access patterns	Facts/dimensions or justified analytical schema
Freshness	Transaction commit/client semantics	Ingestion and transformation lag
Recovery	Application restore and reconnect	Dataset/warehouse restore plus replay/reconciliation
Access	Application/database roles	BI/service roles and row/column controls
Cost	Instance/capacity/storage/I/O as selected	Provisioned or Serverless compute/storage/scans and related dimensions

Canonical warehouse implementation

The canonical Redshift lesson retains and corrects distribution/sort/compression comparisons, COPY/UNLOAD/VACUUM/view syntax, WLM/concurrency scaling, data sharing, security, monitoring, Serverless and data-lake integration. Maintaining a second conflicting set of administration snippets here would make future corrections drift again.

For an application handoff, document source schema/version, ingestion role, staging ownership, data-quality tests, accepted lag and BI authorization. Verify counts and representative financial totals across time boundaries, handle duplicate/late updates and retain replay evidence. A warehouse snapshot alone does not reconstruct the source application’s transactional constraints or its deployment configuration.

References

• Redshift concepts
• Redshift pricing

Across the wiki

• Google BigQuery — analytics and warehouses (GCP)
• Columnar Databases — analytics and warehouses (Architecture)
• Cloud Dataflow Architecture & Streaming Pipelines — analytics and warehouses (GCP)