DataX cheat sheet

Data governance assigns decision rights and accountability for data assets; it defines who may define, approve, change, and retire data, distinct from who operates systems.

DAMA-DMBOK functional areas: data architecture, modeling, storage, security, integration, document/content management, reference and master data, warehousing/BI, metadata, and data quality.

Data steward: business-side role accountable for definition accuracy, quality rules, and issue resolution within a domain; data owner holds budget and policy authority.

Metadata types: business (definitions, owners), technical (schemas, lineage, jobs), operational (run logs, row counts, latency); lineage is technical metadata linking source to target.

Master data is the shared, low-volatility entities (customer, product, location); reference data is controlled code sets (country codes, currency codes, status codes).

Data lifecycle stages: create/acquire, store, use/share, archive, purge; retention schedules must map to legal, regulatory, and contractual obligations.

Data catalog records dataset name, owner, description, classification, sensitivity tags, refresh cadence, and quality score to enable discovery and stewardship.

Policy hierarchy: policy (intent) → standard (mandatory rule) → procedure (steps) → guideline (recommended); controls are tested via evidence, not assertion.

Normalization: 1NF removes repeating groups; 2NF removes partial dependencies on part of a composite key; 3NF removes transitive dependencies on non-key attributes.

Star schema: one fact table with foreign keys plus measures, surrounded by denormalized dimensions; snowflake schema normalizes dimensions into sub-dimensions.

Fact table grain is the single most important design decision; declare grain (e.g., one row per order line per day) before choosing measures or dimensions.

Slowly changing dimension Type 1 overwrites history, Type 2 adds a new row with effective/expiry dates and current flag, Type 3 adds a prior-value column.

Kimball bus matrix maps business processes (rows) against conformed dimensions (columns) to plan incremental data mart delivery.

Data Vault uses hubs (business keys), links (relationships), and satellites (descriptive attributes with load timestamps) for auditable, parallel-load models.

Lambda architecture runs batch and speed layers separately; Kappa architecture treats all data as a stream and reprocesses via log replay.

Conceptual → logical → physical model progression moves from business entities to attributed, keyed structures to platform-specific DDL with indexes and partitions.

ETL transforms before loading into the warehouse; ELT loads raw data first and transforms inside the target using its compute, common with cloud columnar warehouses.

Change data capture methods: log-based (reads database redo/transaction logs), timestamp-based (updated_at column), and trigger-based; log-based is lowest impact.

Idempotency rule: a pipeline rerun must produce the same target state; implement via MERGE/upsert on natural or surrogate keys plus delete-and-insert by partition.

Watermark pattern stores the max processed timestamp or sequence; late-arriving records older than the watermark require a lookback window or reprocessing.

SCD2 load logic: compare incoming hash to current row; on change, close current row (end_date, is_current=false) and insert a new version.

Orchestration dependencies should be data-driven (task succeeds only when upstream partition exists), not fixed clock times, to avoid race conditions.

Full vs incremental: full reload is simple and self-healing but costly at scale; incremental requires reliable keys, deletes handling, and late-data strategy.

Exactly-once delivery is achieved by idempotent writes plus checkpointed offsets; at-least-once plus deduplication is the practical fallback.

Six core quality dimensions: completeness, accuracy, consistency, timeliness, uniqueness, and validity (conformity to format, type, or domain rules).

Completeness rate = non-null (or non-blank) records ÷ total records; report per critical column, not as a single dataset average.

Deduplication: standardize (case, whitespace, punctuation), then match on blocking keys plus fuzzy similarity (e.g., Levenshtein, Jaro-Winkler) above a threshold.

Outlier detection: IQR rule flags values below Q1 − 1.5×IQR or above Q3 + 1.5×IQR; z-score flags |z| > 3 assuming approximate normality.

Missing data mechanisms: MCAR (random), MAR (explained by observed variables), MNAR (depends on the unobserved value); MNAR biases naive imputation.

Data profiling produces column statistics (null rate, distinct count, min/max, pattern frequency) used to set validation rules and detect drift.

Referential integrity check: count child rows whose foreign key has no matching parent key; orphans indicate load-order or source-deletion defects.

Cleaning order matters: parse and type-cast, standardize formats, resolve duplicates, then impute or flag; imputing before dedupe corrupts aggregates.

Central limit theorem: sampling distribution of the mean approaches normal as n grows; standard error of the mean = σ ÷ √n.

Type I error (α) rejects a true null; Type II error (β) fails to reject a false null; power = 1 − β, conventionally targeted at 0.80.

p-value is the probability of data at least as extreme given the null is true; it is not the probability the null is true.

Confidence interval for a mean = x̄ ± z(α/2) × (s ÷ √n); a 95% interval uses z ≈ 1.96 for large samples.

Correlation coefficient r ranges −1 to 1 and measures linear association only; correlation does not establish causation without design or experiment.

Simpson's paradox: an association within every subgroup can reverse when subgroups are pooled, driven by a confounding variable.

Regression interpretation: a coefficient is the expected change in the outcome per one-unit increase in the predictor, holding other predictors constant.

Multiple comparisons inflate false positives; apply Bonferroni (α ÷ number of tests) or Benjamini-Hochberg false discovery rate control.

Encoding accuracy ranking (most to least precise): position on a common scale, position on non-aligned scales, length, angle/slope, area, volume, color hue.

Chart choice rule: comparison → bar; distribution → histogram or box plot; trend over time → line; part-to-whole → stacked bar or treemap; relationship → scatter.

Truncated y-axis on a bar chart exaggerates differences because bar length encodes magnitude; line charts may truncate when zero is not meaningful.

Dual-axis charts invite false correlation; prefer indexed values (base = 100) or small multiples when comparing series with different units.

Color: sequential palette for ordered magnitude, diverging for deviation around a midpoint, categorical for unordered groups; keep to about 5–7 hues.

Accessibility: contrast ratio of at least 4.5:1 for normal text (WCAG AA); never encode meaning by color alone-add labels, patterns, or shapes.

Data-ink ratio (Tufte): maximize the share of ink devoted to data; remove redundant gridlines, 3D effects, and decorative fills.

Dashboard design: one question per view, top-left to bottom-right reading flow, show context (comparison period, target) next to each KPI.

CIA triad: confidentiality (authorized disclosure only), integrity (accurate and unaltered), availability (accessible when needed).

Encryption: symmetric (AES) is fast and needs shared key management; asymmetric (RSA, ECC) solves key exchange and enables digital signatures.

Hashing is one-way and used for integrity and password storage; add a per-user salt and a slow function (bcrypt, scrypt, Argon2) to resist brute force.

Tokenization replaces sensitive values with non-reversible tokens mapped in a vault; format-preserving encryption keeps the original length and character set.

Access control models: RBAC grants by role, ABAC evaluates attributes and context, MAC uses classification labels; least privilege limits each grant to need.

GDPR principles: lawfulness, purpose limitation, data minimization, accuracy, storage limitation, integrity/confidentiality, accountability; breach notice within 72 hours.

Privacy techniques: pseudonymization (re-identifiable with extra data), anonymization (irreversible), k-anonymity (each record indistinguishable from k−1 others), differential privacy.

Defense in depth layers controls: network segmentation, identity and MFA, encryption at rest and in transit, logging/monitoring, and tested backup recovery.

OLTP systems are row-oriented and optimized for short transactions; OLAP warehouses are columnar and optimized for scans and aggregations.

Columnar storage improves analytical compression and scan speed by reading only referenced columns; row storage favors single-record reads and writes.

Partitioning splits data by a key (usually date) to enable partition pruning; clustering/bucketing co-locates related rows within partitions for joins.

Distributed file systems replicate blocks (default 3) for fault tolerance; the NameNode/metadata service tracks block locations, not the data itself.

MPP query engines parallelize across nodes; shuffle (data exchange) is the most expensive operator, so join keys and distribution keys should align.

Streaming platforms retain an append-only log; consumers track offsets, and retention policy (time or size) determines replay window.

File formats: Parquet/ORC are columnar with embedded schema and predicate pushdown; Avro is row-based with schema evolution; CSV lacks types and schema.

Cloud storage tiers trade retrieval cost and latency against storage price (hot → warm → archive); lifecycle rules automate tier transitions.