DS0-002 — CompTIA DataSys+ V2 Cheat Sheet

Focus on decision-making: DS0-002-style questions often test whether you can choose the safest database design, operation, security control, or troubleshooting step—not just define a term.

Use this review with the current CompTIA exam objectives. Do not rely on memory of older versions or unofficial weightings.

Workload selection matrix

Database model selection

Relationship patterns

Constraint reference

Normalization decision table

High-yield distinction: normalization reduces update anomalies; denormalization can improve read performance but increases maintenance and consistency risk.

Logical SELECT processing order

Trap: WHERE filters rows before grouping. HAVING filters groups after aggregation.

SELECT c.customer_id, COUNT(*) AS order_count
FROM customers c
JOIN orders o
  ON o.customer_id = c.customer_id
WHERE o.order_date >= DATE '2026-01-01'
GROUP BY c.customer_id
HAVING COUNT(*) > 5
ORDER BY order_count DESC;

Join behavior

-- Find customers with no orders
SELECT c.customer_id
FROM customers c
LEFT JOIN orders o
  ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;

NULL behavior

Aggregation and window functions

SELECT
  customer_id,
  order_id,
  order_total,
  ROW_NUMBER() OVER (
    PARTITION BY customer_id
    ORDER BY order_date DESC
  ) AS order_rank
FROM orders;

DDL and constraints example

CREATE TABLE orders (
  order_id      INTEGER PRIMARY KEY,
  customer_id   INTEGER NOT NULL,
  order_date    DATE NOT NULL,
  order_total   DECIMAL(12,2) NOT NULL CHECK (order_total >= 0),
  status        VARCHAR(20) NOT NULL DEFAULT 'NEW',
  CONSTRAINT fk_orders_customer
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

Transaction example

BEGIN;

UPDATE accounts
SET balance = balance - 100
WHERE account_id = 10;

UPDATE accounts
SET balance = balance + 100
WHERE account_id = 20;

COMMIT;
-- Use ROLLBACK instead of COMMIT if validation fails.

SQL categories

Logical SELECT processing order

Remember the logical order, not the written order:

1. FROM and JOIN
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. DISTINCT
7. ORDER BY
8. LIMIT / FETCH / TOP, depending on platform

Exam trap: a column alias created in SELECT may not be available to WHERE because WHERE is logically processed earlier.

Join review

Filtering, grouping, and NULLs

UPDATE and DELETE safety

Before changing production data, the safest pattern is usually:

1. Confirm the target rows with SELECT.
2. Use a transaction when supported and appropriate.
3. Apply a specific WHERE clause.
4. Validate the affected row count.
5. Commit only after verification.
6. Keep a rollback or restore path.

Common trap: choosing a broad UPDATE or DELETE when the scenario asks for controlled, auditable change.

DROP vs DELETE vs TRUNCATE

Isolation and anomalies

Locking and contention

ACID

Common concurrency problems

High-yield rule: increasing isolation can improve consistency but may reduce concurrency. The best answer depends on the scenario’s balance between correctness and performance.

SARGability checklist

A predicate is more index-friendly when the database can search the index directly.

Query tuning sequence

1. Confirm the performance symptom and baseline.
2. Review execution plan: scan vs seek, join method, sort, spill, estimated vs actual rows if available.
3. Check predicates for SARGability.
4. Validate relevant indexes and index column order.
5. Check statistics freshness and cardinality estimates.
6. Inspect joins, missing filters, accidental cross joins, and implicit conversions.
7. Check locks, waits, I/O, CPU, memory, and temp space.
8. Test changes in non-production and compare measured results.

Performance tools and signals

Index fundamentals

Common index traps

• Indexing every column “just in case.”
• Adding an index before checking the query plan.
• Ignoring stale statistics.
• Using functions on indexed columns in filters and then wondering why the index is not used.
• Creating a composite index with columns in the wrong order for the query pattern.
• Forgetting write-heavy systems pay a cost for every additional index.
• Assuming partitioning automatically fixes poor query design.

Query tuning decision path

    flowchart TD
	    A[Slow query or workload] --> B[Confirm scope and baseline]
	    B --> C[Check wait events, locks, CPU, memory, I/O]
	    C --> D[Review execution plan]
	    D --> E{Root cause?}
	    E -->|Bad access path| F[Index, statistics, predicate rewrite]
	    E -->|Too much data scanned| G[Filter earlier, partition prune, aggregate appropriately]
	    E -->|Blocking| H[Shorten transactions, tune locks, review isolation]
	    E -->|Resource saturation| I[Capacity, configuration, workload scheduling]
	    F --> J[Test change safely]
	    G --> J
	    H --> J
	    I --> J
	    J --> K[Measure again and document]

Partitioning patterns

Partitioning is not automatically a performance fix. It helps most when queries filter on the partition key, maintenance can operate per partition, or data lifecycle actions need efficient archive/drop.

Replication choices

CAP and distributed data systems

Exam distinction: CAP applies under network partition conditions. It does not mean every system permanently chooses only two characteristics in all situations.

Least privilege SQL example

-- Example pattern; exact syntax varies by platform.
CREATE ROLE reporting_reader;

GRANT SELECT ON sales_summary TO reporting_reader;

GRANT reporting_reader TO analyst_user;

REVOKE INSERT, UPDATE, DELETE ON sales_summary FROM reporting_reader;

Role design

Sensitive data handling

Audit and logging focus

Trap: audit logs must be protected from tampering. Logging sensitive values can create a second data exposure location.

Core security controls

Permission model review

High-yield rule: if a user needs access, prefer granting access through an appropriate role or group rather than giving broad direct privileges.

Security traps

• Granting administrative rights to fix a simple read/write permission issue.
• Using shared accounts without accountability.
• Storing secrets in scripts, code repositories, or plain-text configuration files.
• Copying production data to test without masking sensitive fields.
• Encrypting data but leaving keys broadly accessible.
• Logging sensitive data into application or database logs.
• Ignoring failed login spikes, unusual exports, or privilege escalation events.
• Assuming network isolation replaces database-level access control.

Backup type comparison

Restore sequence reference

Backup validation checklist

• Confirm backups complete successfully.
• Test restore in a non-production environment.
• Verify application can connect after restore.
• Validate row counts, checksums, or reconciliation totals.
• Document restore steps and owners.
• Protect backups with encryption and access controls.
• Store backups separately from the primary failure domain.
• Monitor backup age, duration, failure, and capacity.
• Review retention and deletion settings against business requirements.

HA/DR decision path

    flowchart TD
	    A[Requirement: protect database service] --> B{Main concern?}
	    B -->|Short outage from server failure| C[High availability: cluster or failover replica]
	    B -->|Data loss tolerance is very low| D[Synchronous replication or frequent log shipping]
	    B -->|Regional/site disaster| E[Disaster recovery environment plus offsite backups]
	    B -->|Accidental data change| F[Point-in-time restore and audit trail]
	    C --> G[Test failover runbook]
	    D --> G
	    E --> G
	    F --> H[Test restore and data reconciliation]

RPO and RTO

High-yield rule: a backup strategy is not proven until a restore has been tested.

Backup types

Availability patterns

Operational mistakes

• Backing up but never testing restore.
• Restoring over production without confirming scope and authorization.
• Treating snapshots as the only disaster recovery plan.
• Ignoring transaction logs until storage fills.
• Failing over without knowing application connection behavior.
• Forgetting that replicas may replicate bad data or destructive changes.
• Not documenting recovery steps before an incident.

Data quality checks

-- Basic reconciliation checks after migration/load
SELECT COUNT(*) AS source_count FROM source_orders;
SELECT COUNT(*) AS target_count FROM target_orders;

SELECT SUM(order_total) AS source_total FROM source_orders;
SELECT SUM(order_total) AS target_total FROM target_orders;

Governance and metadata

Symptom-to-action table

Incident response for database issues

1. Detect and classify severity.
2. Preserve logs, alerts, and current state.
3. Stabilize service; avoid destructive “quick fixes.”
4. Identify blast radius: users, applications, tables, replicas, backups.
5. Communicate status through the agreed channel.
6. Apply tested recovery or remediation steps.
7. Validate service and data correctness.
8. Document root cause, timeline, and preventive actions.

Schema change checklist

• Use version-controlled migration scripts.
• Test in development and staging with realistic data volume.
• Identify dependent applications, views, reports, jobs, and permissions.
• Back up or snapshot before high-risk changes.
• Plan locking and downtime impact.
• Use backward-compatible changes where possible.
• Include rollback or roll-forward strategy.
• Validate data after migration.
• Document change, owner, approval, and implementation time.

Safe migration sequence

OLTP vs OLAP

Cardinality and optionality

Normalization

High-yield rule: Normalize to reduce redundancy and protect integrity; denormalize deliberately for performance or reporting after understanding the tradeoff.

Design traps

• Storing comma-separated values in one column instead of using a child table.
• Using free-text fields where a constrained lookup table is required.
• Omitting foreign keys because “the application handles it.”
• Confusing a surrogate primary key with a full uniqueness rule.
• Modeling many-to-many relationships without a junction table.
• Denormalizing transactional tables before measuring the performance need.
• Ignoring delete behavior: cascade, restrict, set null, and soft delete each have consequences.

If a scenario mentions a specific policy, contract, or regulation, apply the requirements given in the question. Do not assume unstated legal rules.

Data lifecycle

Data quality dimensions

Data pipeline traps

• No validation at ingestion.
• No idempotency, causing duplicates when a job is retried.
• Schema drift breaking downstream reports.
• No lineage, making errors hard to trace.
• Loading data in the wrong order and violating foreign keys.
• Mixing time zones or date formats without standardization.
• Treating successful job completion as proof of correct data.
• Not reconciling row counts, checksums, or business totals.

Dimensional modeling review

Common trap: building reports from highly normalized OLTP tables when a warehouse or dimensional model would better support analytics.

Symptom-to-cause review