I85PREP
⌘ K
CWIIP study materials
CWIIP study resources

CWIIP: Database Systems and Their Role in Integration Projects

CWIIP · Work through your exam, one topic at a time.

All guide sections

Database Systems and Their Role in Integration Projects

What this domain tests

CWIIP examines how you choose a datastore for IoT workloads, model sensor data, and integrate the database with the rest of the platform. Expect questions on workload fit, schema and index design, retention, and consistency trade-offs.

Matching engine to workload

  • Relational (PostgreSQL, MySQL): ACID transactions, joins, strong schema. Right for device registries, provisioning records, users, billing.
  • Document (MongoDB): flexible JSON-shaped documents; good for heterogeneous device payloads.
  • Key-value / cache (Redis): sub-millisecond lookups, last-known-state, session and rate-limit counters. Not a system of record for history.
  • Time-series (InfluxDB, TimescaleDB): append-heavy, timestamp-indexed, built-in retention policies and downsampling. The default for telemetry.
  • Graph (Neo4j): relationship-heavy queries such as device-to-asset topology.

Modelling and scale

  • Time-series data is modelled as timestamp + tags (indexed metadata such as device_id, site) + fields (measured values). Keep tag cardinality low - a per-message UUID as a tag explodes the index.
  • Index on (device_id, time); partition or use hypertables by time so queries and deletes stay bounded.
  • Retention: keep raw data hot for days, downsample to 1-minute or 1-hour aggregates for dashboards, archive cold data to object storage.
  • CAP: during a network partition you choose consistency or availability; many IoT pipelines choose availability and reconcile later with last-write-wins or device sequence numbers.

Integration patterns

  • Access paths: REST/SQL over ODBC-JDBC, native drivers (psycopg, pymongo, influxdb-client), or a message broker writing asynchronously.
  • Batch versus stream ingestion; ETL versus ELT; change data capture to publish database changes downstream.
  • Batch writes and apply backpressure - one insert per reading per device collapses under load.

Worked example

5,000 devices send one reading every 10 seconds. Per device per day: 86,400 / 10 = 8,640 readings. Fleet total: 8,640 × 5,000 = 43.2 million rows per day, about 1.3 billion per month. A row-per-reading relational table with no partitioning will fail. The design: time-series store with a 30-day raw retention policy, continuous aggregates at 1 minute and 1 hour for dashboards, and device metadata kept in the relational registry, joined in the API layer.

Traps

  • High-cardinality tags used as index keys.
  • Storing telemetry in a relational table without partitioning or retention.
  • Assuming eventual consistency and ACID are interchangeable.
  • Ignoring time synchronisation: untrusted device clocks corrupt time-series ordering.
  • Testing with one device instead of the projected message rate.