Designing, operating, and optimizing reliable data pipelines and ensuring data quality across ingestion, transformation, and consumption. Covers extract transform load and extract load transform patterns, efficient incremental and batch loading, idempotent processing, change data capture, orchestration and scheduling, and performance tuning to meet service level objectives. Includes data validation strategies such as schema enforcement, null and type checks, range and referential integrity checks, deduplication, handling late arriving and out of order data, reconciliation processes, and data profiling and remediation. Emphasizes observability, monitoring, alerting, and root cause analysis for data quality incidents, as well as data lineage tracking, metadata management, clear ownership and process discipline, testing and deployment practices, and governance to maintain data integrity for analytics and business operations. Also covers data integration concerns across customer relationship management systems, marketing automation systems, reporting systems, and other operational systems, including pipeline error handling, data contracts, and how test and validation checks can be integrated into pipelines to prevent regressions.
HardTechnical
26 practiced
Design a data governance model that balances data accessibility for analysts and compliance with GDPR/CCPA. Include classification, PII detection and masking strategies, consent tracking, data retention policies, audit trails, dataset access workflows, and how enforcement is automated across pipelines and BI tools.
Sample Answer
Situation: As a data analyst working with sensitive customer data, we need a governance model that preserves analyst productivity while enforcing GDPR/CCPA.Design (high level):1) Classification taxonomy- Tier data as: Public, Internal, Confidential, Sensitive/PII. Subclass PII: Identifiers (name, SSN), Contact (email, phone), Financial, Health.- Maintain a central data catalog (e.g., Collibra/Alation) with schema-level and field-level tags.2) Automated PII detection & labeling- Pipeline stage uses deterministic rules (regex, dictionaries) + ML models (NER) to surface candidate PII.- Results write metadata tags to the catalog and to table/column-level ACLs.3) Masking & minimization strategies- Apply masking by sensitivity: - Identifiers: irreversible hashing or tokenization with vault (e.g., Hashicorp Vault / token service). - Contact info: deterministic pseudonymization for joinability when allowed. - Fields needed for analytics but not identity: k-anonymization / generalization or differential privacy for aggregated exports.- Provide masked and full datasets separated by access tier; analysts default to masked views.4) Consent tracking & purpose management- Central consent registry keyed by subject ID and data purpose. Attach consent status to user records in master customer table.- During ETL, filter or flag records lacking required consent for given purpose; pipelines enforce exclusion or anonymization.5) Retention & legal holds- Data lifecycle policies in catalog with TTLs per data type and jurisdiction.- Automate deletion or irreversible anonymization after retention; support legal holds to override deletion.6) Audit trails & monitoring- Immutable, tamper-evident logs (WORM storage) capturing data access, queries, masking actions, consent changes; shipped to SIEM.- Regular audits and anomaly detection (excessive exports, joins on identifiers).7) Dataset access workflows- Self-service catalog: analysts request access with purpose, duration; auto-approve for low-risk masked sets, manual review for full PII.- Role-based + attribute-based access control (RBAC + ABAC) integrated with IAM (Okta/Azure AD).- Provide ephemeral credentials and time-bounded access.8) Enforcement automation across pipelines & BI- Central policy engine (e.g., Open Policy Agent + Ranger) enforces policies at ingestion, storage (Data Lakehouse like Delta Lake), query layer, and BI connectors.- Use data virtualization/semantic layer to expose only masked columns or aggregated endpoints to BI tools (Tableau/Power BI) with row-level and column-level security enforced at query broker.- CI/CD checks (dbt tests) validate that no PII is promoted to public datasets; pipeline jobs fail if consent/retention rules violated.Example flow:- New table ingested -> PII scanner tags columns -> catalog records sensitivity -> masking transforms applied in ETL -> masked table published -> analyst requests full access via catalog -> approval checks consent/role -> temporary token issued -> all accesses logged.Why this balances needs:- Analysts keep fast self-service via masked/aggregated data and a catalog discovery UX.- Compliance is enforced automatically via pipelines, policy engine, consent registry and immutable audits, reducing manual overhead and risk.
HardTechnical
25 practiced
Given orders(order_id INT, customer_key STRING, order_ts TIMESTAMP) and customers_scd2(customer_key STRING, customer_id INT, effective_from TIMESTAMP, effective_to TIMESTAMP), write a SQL query to find orders without a matching customer version valid at the time of order (i.e., referential integrity violations under SCD2). Explain assumptions about inclusive/exclusive ranges and timezones.
Sample Answer
Approach: For SCD2, a valid customer version for an order is one where order_ts lies in the version’s [effective_from, effective_to) interval (common convention: inclusive start, exclusive end). Treat effective_to = NULL as “open/active” (infinity). Ensure both timestamps are compared in the same timezone (convert to UTC) to avoid mismatches.SQL (find orders with no matching valid customer version):
sql
SELECT o.order_id, o.customer_key, o.order_ts
FROM orders o
LEFT JOIN customers_scd2 c
ON o.customer_key = c.customer_key
AND o.order_ts >= c.effective_from
AND (c.effective_to IS NULL OR o.order_ts < c.effective_to)
-- normalize timezones if necessary, e.g.:
-- AND (o.order_ts AT TIME ZONE 'UTC') >= (c.effective_from AT TIME ZONE 'UTC')
-- AND (c.effective_to IS NULL OR (o.order_ts AT TIME ZONE 'UTC') < (c.effective_to AT TIME ZONE 'UTC'))
WHERE c.customer_key IS NULL;
Key points and reasoning:- Use LEFT JOIN + WHERE c.customer_key IS NULL to surface orders that didn’t match any valid SCD2 row.- Use >= on effective_from (inclusive) and < on effective_to (exclusive). This avoids overlapping boundaries assigning an order to two versions when one ends exactly when the next begins.- Treat effective_to IS NULL as active version.- Normalize timezones (convert both order_ts and effective_* to UTC) before comparing. If DB stores timezone-aware TIMESTAMP WITH TIME ZONE, comparisons are safe; if not, apply explicit conversion.Edge cases:- order_ts NULL: decide whether to exclude or flag; add WHERE o.order_ts IS NOT NULL if ignoring.- Overlapping SCD2 rows: if overlaps exist, data quality issue — you may detect orders matching multiple rows by replacing LEFT JOIN with INNER JOIN and GROUP BY to count matches.Alternative: using NOT EXISTS:
sql
SELECT o.*
FROM orders o
WHERE NOT EXISTS (
SELECT 1 FROM customers_scd2 c
WHERE c.customer_key = o.customer_key
AND o.order_ts >= c.effective_from
AND (c.effective_to IS NULL OR o.order_ts < c.effective_to)
);
This is functionally equivalent and can be more efficient on some engines.
EasyTechnical
25 practiced
List the key metrics, logs, and health checks you would include in a pipeline observability dashboard for ingestion and transformation jobs. For each item explain why it matters, how it is computed, and what alert thresholds you might set to detect data-quality regressions.
Sample Answer
Situation: Designing an observability dashboard for ingestion and transformation jobs used by analysts to trust downstream reporting.Key metrics, logs, and health checks:- Job success/failure rate - Why: Basic health; failed runs break reports. - Computed: percent of runs that exit non-zero in last 24h. - Alert: >1 failure in last 24h or success rate < 99%.- Pipeline latency / end-to-end freshness - Why: Ensures data is available when reports run. - Computed: time between source event timestamp and job completion. - Alert: median latency > SLA (e.g., 30 min) or P95 > 2× SLA.- Throughput / ingested volume - Why: Detect missing or duplicated data. - Computed: rows/bytes ingested per hour and relative to historical baseline (moving 7‑day avg). - Alert: drop >30% vs baseline or increase >50% (possible duplication).- Record counts by partition/key - Why: Catch partial loads or upstream schema changes. - Computed: counts grouped by date/source; compare to expected. - Alert: any partition count = 0 when expected >0, or deviation >30%.- Null / missing-value rate on critical columns - Why: Data quality for business keys and metrics. - Computed: percent nulls per column per run. - Alert: null rate >5% for PKs/IDs; >20% for non-critical fields.- Duplicate key rate - Why: Aggregation/reporting errors. - Computed: count(distinct pk) vs total rows; duplicates = total - distinct. - Alert: duplicates >0 or duplicates ratio >0.1%.- Schema drift / column changes - Why: Upstream changes break queries. - Computed: checksum/schema fingerprint compared to expected schema. - Alert: any unexpected column added/removed/type change.- Data distribution / statistical anomalies - Why: Catch subtle regressions (e.g., sudden mean shift). - Computed: compare key metric distributions (mean, median, stddev) to rolling baseline; use z-score. - Alert: z-score >3 for important metrics.- Error and exception logs (sampled) - Why: Root cause debugging. - Computed: aggregated error types, stack traces, counts. - Alert: error spike > threshold or recurring same error.- Resource utilization (CPU/memory/DB query time) - Why: Performance issues can cause timeouts and partial loads. - Computed: average/max per job. - Alert: sustained CPU/memory >85% or DB query time > expected.- SLA / run-time variance - Why: Predictable scheduling. - Computed: actual run time vs expected; variance percent. - Alert: run time > expected ×1.5 or jitter causing downstream misses.For each alert include a staging/notify policy (warning vs critical) and attach recent example rows or sample failed payload to speed debugging. These metrics let analysts trust freshness, completeness, and correctness of data feeding reports.
HardTechnical
35 practiced
Compare batch and streaming architectures for event analytics use cases (near real-time dashboards vs nightly reporting). Discuss trade-offs in latency, cost, complexity, data correctness (exactly-once semantics), operational overhead, and recommended architectures or hybrid approaches for different business needs.
Sample Answer
Situation: For event analytics you must choose between batch (nightly) and streaming (near real-time) architectures — or a hybrid. Below is a role-appropriate comparison and practical recommendations for a Data Analyst.Comparison (trade-offs)- Latency - Batch: High (minutes–hours). Good for end-of-day KPIs, financial close. - Streaming: Low (seconds–minutes). Required for live dashboards, alerts.- Cost - Batch: Lower compute cost if jobs run once per day; simpler infra. - Streaming: Higher continuous compute, storage for state, and often network costs.- Complexity - Batch: Simpler pipelines (ETL -> DW), easier to test and reason about. - Streaming: More complex (event ordering, windowing, backpressure, stateful processing).- Data correctness / Exactly-once - Batch: Easier to achieve idempotent results; reprocessing deterministic. - Streaming: Exactly-once requires careful design (Kafka + transactional sinks, Flink with checkpointing). At-least-once is common; de-duplication or idempotent sinks needed.- Operational overhead - Batch: Manage scheduling, monitoring of daily jobs; easier rollback via re-run. - Streaming: 24/7 ops, alerts for lag, state management, schema evolution handling.Recommended architectures- Nightly reporting / historical analysis: Batch ETL into a data warehouse (Snowflake/BigQuery/Redshift). Use partitioned tables, incremental loads, and scheduled quality checks. Reprocessability and simple SQL access fit analyst workflows.- Near real-time dashboards / operational metrics: Stream ingestion (Kafka/ Kinesis) -> stream processing (Flink/Beam/ksqlDB) -> materialized views or OLAP store (ClickHouse, Druid, or a DW with streaming ingest). Prefer windowed aggregations and late-arrival handling.- Hybrid / Lambda-inspired for analysts: Use streaming for low-latency materialized aggregates plus periodic batch recomputation for correctness and complex joins. Present final authoritative daily metrics from batch while powering live insights from stream aggregates.Practical guidance for Data Analysts- Define SLAs: Which dashboards need seconds vs daily accuracy.- Start with batch if requirements unclear; add streaming for key real-time KPIs.- Ensure data quality: monitor discrepancies between streaming and nightly batch outputs; keep lineage and metadata.- Favor platforms that expose SQL or BI-friendly connectors so analysts can self-serve.
EasyTechnical
34 practiced
Explain the differences between ETL and ELT in the context of building analytics pipelines for a modern cloud data warehouse (for example Snowflake or BigQuery). For each approach describe where transformations run, typical tools, performance and cost trade-offs, latency implications, and two real-world scenarios where you would recommend ETL versus ELT.
Sample Answer
ETL vs ELT — short version: ETL extracts data, transforms it before loading into the warehouse; ELT extracts and loads raw data into the warehouse, then transforms inside the warehouse.Where transformations run- ETL: transformations run in an external processing layer (on-prem ETL engine or cloud ETL service) before data lands.- ELT: transformations run inside the cloud warehouse (Snowflake/BigQuery) using SQL, stored procedures, or transformation frameworks (dbt).Typical tools- ETL: Talend, Informatica, Fivetran (with pre-load transforms), Azure Data Factory (mapping data flows), custom Spark jobs.- ELT: Fivetran/Stitch for ingestion + dbt, native warehouse SQL, BigQuery Dataflow for streaming transforms.Performance & cost trade-offs- ETL: Offloads compute from the warehouse; useful if warehouse compute is expensive or you must enforce heavy cleansing before load. But you maintain separate infrastructure and possibly duplicate storage/compute costs.- ELT: Leverages scalable, often cheaper warehouse compute and avoids maintaining separate transform infra. Can be faster for large-scale set-based operations but may increase warehouse compute costs if poorly optimized.Latency implications- ETL: Can add latency because transforms run before load; batch windows often longer. Good for strict data quality/formatting guarantees on arrival.- ELT: Enables faster load (raw data available quickly) and more flexible, incremental transforms for near-real-time analytical needs.When to recommend ETL1) Source systems produce sensitive/regulated data that must be masked or validated before entering the warehouse (compliance).2) Transformations require heavy custom processing best handled in Spark/Scala or external systems (complex joins across streaming and historical files) and you want to avoid expensive warehouse compute.When to recommend ELT1) Modern cloud warehouse available and team proficient in SQL/dbt — prefer ELT for faster ingestion, iterative modeling, and easier lineage/testing.2) You need raw event-level data in the warehouse for ad-hoc analyses, backfills, or reproducible models — load first, transform as needed.Practical note for a Data Analyst: ELT + dbt gives you versioned, SQL-based models you can read, test, and iterate on quickly. Use ETL when compliance, source complexity, or cost constraints make pre-load transforms necessary.
Unlock Full Question Bank
Get access to hundreds of Data Pipeline and Data Quality interview questions and detailed answers.
