Data and Technical Strategy Alignment Questions

Evaluates a candidate's ability to reason about the technical and architectural trade-offs that shape a data platform: batch versus streaming (and hybrid) pipelines, data warehouse versus data lake versus lakehouse architecture, ETL versus ELT, schema design and partitioning for analytics and ingestion, data contracts between producers and consumers, feature stores, and metrics (semantic) layers. Good answers pick a concrete architecture or approach for a stated scale, latency, and cost profile, name the trade-offs of the alternatives, and justify the choice rather than reciting definitions.

MediumSystem Design

51 practiced

Design a semantic layer that connects the warehouse to BI tools (Looker/Power BI). Define key components: canonical metric definitions, golden dimension and fact tables, access patterns for analysts, versioning strategy, and how you'll address performance bottlenecks.

Sample Answer

Requirements:- Single source of truth for business metrics usable by Looker/Power BI- Fast, predictable queries for interactive dashboards- Strong governance (versioning, lineage, access controls)

High-level architecture:Warehouse (raw) → ETL/ELT → Golden fact & dimension tables → Semantic layer (metric registry + modeling) → BI tools (LookML / Power BI datasets)

Key components:1. Canonical metric definitions- Central metric registry (YAML or DB table) storing name, SQL expression, business owner, unit, filter logic, grain, and tests.- Implement metrics as parameterized SQL snippets or LookML measure templates so all dashboards call the registry versioned metric, not ad-hoc SQL.

2. Golden dimension & fact tables- Golden dims: conformed dimensions (user, product, geography) cleansed, slowly changing dimension strategy (SCD2), standardized keys and attributes.- Golden facts: event-level and daily-rollup facts. Clearly document grain and allowed aggregations.- Provide aggregated (hour/day/week) and raw-level fact tables to support different access patterns.

3. Access patterns for analysts- Read-only views mapped to LookML explores and Power BI semantic models.- Encourage use of prebuilt measures from metric registry; forbid direct SELECTs on raw tables for production reports.- Provide sample queries, templates, and a sandbox dataset for exploration.

4. Versioning strategy- Store metric definitions and model files in Git; use branches & PRs for changes.- Tag releases (v1, v2) of metric registry; dashboards reference specific metric versions.- Automated tests: unit (SQL result checks), integration (compare previous release metrics), and data quality checks run in CI.

5. Performance and bottlenecks- Precompute heavy metrics into materialized views / aggregate tables. Use rolling windows to update incrementally.- Partition and cluster large fact tables by date and high-cardinality keys.- Cache BI tool extracts (Power BI incremental refresh, Looker persistent derived tables).- Limit wide joins in UI layer; push joins/filters into warehouse views.- Monitor slow queries, add query tagging, and set SLAs. Autoscale warehouse compute for heavy refresh windows.

Trade-offs:- Materializing aggregates improves UX but increases storage and ETL complexity—balance with usage metrics.- Strict governance improves trust but needs change-management and clear owner responsibilities.

This design gives analysts a governed, performant, and versioned semantic layer that ensures consistent metrics across Looker and Power BI.

HardTechnical

40 practiced

Design a robust rollback and audit process for analytics transformations that enables replaying historical metrics after discovering a transformation bug weeks later. Specify what raw data and metadata to retain, how to version transforms and code, and how to orchestrate reproducible replays while documenting changes for auditors and stakeholders.

Sample Answer

Requirements & goals:- Be able to reproduce any transformed metric as-of any historical run (weeks/months back)- Maintain immutable raw data and provenance so auditors can verify source-to-metric lineage- Minimize storage/cost while keeping reproducibility guarantees

Design (high level):1. Raw data & metadata to retain- Immutable raw ingests: store original event files/messages in append-only blob store (S3) partitioned by ingestion_date. Retain for retention window >= expected bug discovery window (e.g., 1 year).- Ledger metadata per ingest file/batch: source_system, source_schema_version, capture_timestamp, checkpoint_offset, checksum (SHA256), producer_id.- Record table snapshots / change-logs: for CDC sources keep ordered WAL or Kafka topic with offsets; for batch sources retain input file manifest.- Catalog schema versions & sample rows stored in a schema registry with hash of schema and examples.

2. Versioning transforms & code- All transformation SQL/ELT code stored in Git with strict PR review and CI. Tag releases with semantic versions and release notes.- Use a transformation framework that supports versioned models (dbt recommended): each model has config with model_version, description, test suite, and schema.yml tests.- Store compiled artifacts (compiled SQL, macros, model DAG) in an artifact repo keyed by git commit hash.- Persist environment metadata: runtime dependencies (db drivers, tool versions), config values, secrets version (redacted), and transform parameters.

3. Lineage & audit metadata- Emit lineage events (OpenLineage/Marquez) on each run: upstream sources, model, inputs (file path, offset, partition), git_commit, run_id, start/end timestamps, row counts, hashes of outputs.- For each metric, store a metric-definition registry with versioned SQL/logic and business owner.

4. Orchestrating reproducible replays- Orchestration engine (Airflow/Prefect) runs transformations deterministically given: - git commit hash (code), artifact snapshot - input pointers: exact raw file paths / Kafka offsets / snapshot ids - environment snapshot (container image tag, db connection, config)- Replay flow: pick commit + inputs → spin ephemeral compute (docker image pinned) → run dbt with --models as-of / state pointing to inputs → store outputs in replay schema (e.g., analytics_replay.run_<run_id>).- Validate replay by comparing row counts and output hashes to original run (if available). Produce diff report for auditors.

5. Documentation & stakeholder/audit reporting- Auto-generate run reports that include: run_id, git_commit, model_versions, input_manifest, checksums, row counts, anomalies, and business-impact summary (which dashboards/metrics depend on these models).- Maintain an incident registry: when a bug is found, record affected runs, affected dashboards, mitigation steps and re-run plan; notify stakeholders with expected timeline.- Retain replay artifacts (reports, output snapshots) for auditing window.

Operational practices & tests:- Enforce CI: unit tests, schema tests, freshness tests, and pre-deploy data samples.- Periodic snapshotting: run nightly checksums of key metric outputs and store historical hashes to detect drift early.- DR/Cost tradeoffs: If full raw retention is expensive, maintain compact replayable extracts: deterministic materialized snapshots + changelogs.

Example minimal replay steps for a discovered bug:1. Identify commit(s) that introduced bug (git bisect using tests or diff).2. For a target historical date, gather input_manifest (file paths & checksums or Kafka offsets).3. Launch replay DAG with parameters: git_commit=<good_commit>, inputs_manifest=<manifest>, replay_id=<uuid>.4. Run transforms into isolated schema, run validation checks and compare to original outputs, produce audit report, then optionally promote corrected outputs to production or create corrective adjustments for downstream dashboards.

This design provides deterministic, auditable replays, clear ownership, and tooling hooks for stakeholders and auditors to verify historical metrics.

HardTechnical

49 practiced

Plan a migration of 500 dashboards from Tableau to Looker (or from Looker to Power BI). Describe an end-to-end approach: inventory and classification, mapping calculations and joins, rewriting data models, validation of numbers, user training, phased rollout, and retirement of legacy assets. Call out major risks and mitigations.

Sample Answer

Framework: treat this as a program with phases — Discover, Design, Build, Validate, Deploy, Retire — run under a migration roadmap with stakeholder governance.

1) Inventory & classification- Run automated extractor (Tableau/Looker metadata APIs) to capture 500 dashboards, data sources, extracts, queries, embedded SQL, users, access patterns, last-used timestamps.- Classify by complexity (simple KPI, parameterized, heavy joins, custom SQL, LODs/expressions), criticality (C-level, operational), owner, and refresh SLA.

2) Mapping calculations & joins- Create a translation matrix: source objects → target constructs (Tableau LODs → Looker derived tables/ephemeral PDTs or LookML measures; Looker dimensions → Power BI calculated columns/measures using DAX).- Identify constructs without direct equivalents (complex LODs, table calculations, table-level blending) and flag for re‑engineering into the semantic layer or ETL.

3) Rewriting data models- Prefer centralized semantic layer (LookML model or Power BI shared datasets) rather than per-dashboard logic.- Convert datasets to governed views/PDTs in the warehouse or to federated semantic models. Normalize naming, metrics, and tags.- Implement version-controlled LookML / Power BI shared dataset with reusable measures.

4) Validation of numbers- Build an automated test suite: row counts, sums, nulls, and key metric comparisons between source and target for sampled dashboards and snapshots across time windows.- Use back-to-back testing: parallel run for critical dashboards for one reporting period, reconcile variances > tolerance and root-cause (aggregation, filter mismatch, timezone).

5) User training & change management- Create role-based documentation, quick reference cards, and recorded short demos mapping old to new dashboards.- Hold hands-on workshops for power users and office-hours for first 4 weeks post-rollout.- Maintain a migration FAQ and support SLA.

6) Phased rollout & retirement- Pilot: migrate 10 high-value dashboards (different complexity) → validate → iterate.- Batch migrations by complexity and by business unit; run in waves with rollback plan.- Decommission legacy assets after 2 successful reporting cycles and stakeholder sign-off; archive source dashboards and metadata for compliance.

Major risks & mitigations- Data drift/metric mismatch: mitigate with automated reconciliation, acceptance thresholds, and a data steward sign-off workflow.- Rewriting complexity/time: mitigate by prioritizing semantic layer and reusing warehouse transformations; allocate SME time for complex conversions.- User adoption resistance: mitigate via early involvement of business owners, training, and dual-run period.- Performance regressions: load-test; implement caching/PDTs and optimize queries.- Access/security gaps: replicate access controls, test row-level security, and audit logs.

Expected outcomes: governed, maintainable semantic layer, consistent metrics, fewer duplicate calculations, improved performance and maintainability, and an auditable migration trail with minimal business disruption.

HardTechnical

50 practiced

Design an enterprise metric catalog and lineage visualization that helps analysts discover metrics, understand their definitions and dependencies, and find owners. Specify how you would automate catalog population (e.g., from dbt manifests and warehouse metadata), the metadata fields you'd require, and how you'd surface this within BI tools.

Sample Answer

Requirements & constraints:- Discover metrics, read canonical definitions, see upstream/downstream lineage, find owners & SLA, integrate with dbt + warehouse, surface in BI tools (Looker/Power BI/Tableau), support updates via automation and manual curation, RBAC.

High-level design:- Central Metrics Catalog service (metadata DB + API + UI) + Lineage Graph store (Neo4j/DGraph) + Ingest pipelines (dbt manifest, warehouse metadata, BI tool models) + Connectors to BI tools (extensions, metadata banners, REST API) + Scheduler for syncs & tests.

Automating catalog population:- Parse dbt manifest.json to extract models, exposures, sources, tests, SQL for metric definitions and model->source edges.- Query warehouse INFORMATION_SCHEMA (or data catalog like Glue) to validate table/column existence, row counts, freshness.- Ingest BI tool metadata (LookML, Power BI dataset schema, Tableau metadata API) to link dashboards/charts to metrics.- Use SQL parser/lineage extractor to identify metric SQL (CTEs, aggregates) to create column-level lineage; update graph store.- Scheduled delta syncs + webhook from CI to re-ingest on dbt runs.

Required metadata fields (per metric):- id, canonical_name, display_name, short_description, full_definition (SQL + natural language), unit, aggregation_type, dimensions, granularity, calculation_timestamp, lineage_edges (upstream nodes), owners (primary, secondary, steward contact + Slack/Email), quality_checks (test ids, last_passed), SLA/freshness, tags/business_domain, sensitivity/classification, last_updated_by, last_updated_at, source_models (dbt refs), examples/sample_query, linked_dashboards (list with links).

Surface in BI tools:- Inject metric badges in BI tool UIs via extensions/visual plugins showing definition, owners, lineage link, freshness, trust score.- Provide a metrics API that BI tools can query to fetch canonical metric SQL for consistent computation or to point dashboards to metric IDs.- A "search & preview" sidebar plugin: search metric → preview definition, sample results, lineage graph, tests, and a one-click "copy SQL" or "insert metric" into dataset.- Alerting: when metric tests fail or upstream schema changes, notify linked dashboards owners.

UX features:- Interactive DAG with zoom, filter by domain/owner, highlight impacted dashboards on node selection, click-to-trace column-level lineage.- Governance workflows: propose/edit metric, review approvals, versioning, and audit log.

Why this works:- Combines automated extraction (dbt + warehouse + BI metadata) for coverage with manual curation for business semantics and ownership, enforces metric consistency across BI via API/embeds, and provides traceable lineage to build trust.

HardSystem Design

35 practiced

Design role-based access control (RBAC) and row-level security (RLS) for an organization with cross-functional BI needs. Describe policy definitions, how to implement them in the data warehouse (e.g., Snowflake roles) and BI tools (Power BI RLS/Looker), and strategies to minimize friction for analysts while enforcing least privilege.

Sample Answer

Requirements & constraints:- Enforce least privilege across datasets while enabling cross-functional BI (finance, sales, product).- Row-level restrictions by tenant, region, organization unit, or sensitivity.- Centralized governance with low friction for analysts and reproducible policies.

High-level design:- Central access catalog + policy-as-code repo (Git, CI/CD).- Data warehouse (Snowflake) as single source of truth; semantic layer (LookML/Power BI semantic models/Analysis Services) for published datasets.- BI tools connect via governed service accounts or per-user SSO depending on use-case.

Policy definitions (examples):- Role types: data_reader (read-only), analyst (can create views/materialized views), data_engineer (can create tables/pipelines), data_owner (owns dataset & policies), exec_viewer (wider access).- Row-level policies: tenant_scoped (users see rows where tenant_id IN user_tenants), region_scoped, sensitivity_block (redact rows with sensitivity_level > user_clearance).- Column masking: mask PII unless role has explicit unmask privilege.

Snowflake implementation:- Use RBAC roles: create hierarchical roles (ACCOUNTADMIN → SECURITY_ADMIN → DATA_OWNER → DATA_ENGINEER → ANALYST → READER). Grant minimally.- Use Row Access Policies (CREATE ROW ACCESS POLICY ... USING (predicate)) and attach to tables with ALTER TABLE ... ADD ROW ACCESS POLICY. Predicate references SESSION_USER or a mapping table (user_id → allowed_tenants).- Maintain a user-to-attributes table in Snowflake (authoritative mapping synced from IdP / HR / provisioning system). Policies join to that table: USING (EXISTS (SELECT 1 FROM user_tenants WHERE user = CURRENT_USER() AND tenant_id = tenant_id_col)).- Use Secure Views for complex joins and to hide logic; secure views + row access policies ensure enforced when BI queries run.- Column-level masking via Dynamic Data Masking and Tag-based policies for sensitive columns.- Use custom Snowflake roles for BI service accounts (Power BI service principal, Looker service account) with limited privileges; prefer per-user SSO for sensitive dashboards.

BI tool integration:- Power BI: - Prefer DirectQuery on governed datasets or import from curated Semantic Layer. - Implement RLS at model-level for per-user filtering if Snowflake row policies cannot be applied (but keep primary enforcement in Snowflake). - For DirectQuery/SSO: use OAuth/SSO with Power BI Premium with EffectiveIdentity to pass user identity so Snowflake row policies can evaluate CURRENT_USER(). - If service principal used, implement dynamic filtering by passing user custom claims (username, groups) into query parameters and mapping in Snowflake via session_context or temporary table.- Looker: - Use user_attributes (e.g., user.tenant_ids, role_clearance) and access_filter in model files to apply row-level filters. - Backstop with Snowflake row access policies so even if Looker filters are bypassed, Snowflake enforces constraints. - Use LookML Explores pointing to secure views rather than base tables.

Minimizing friction for analysts:- Central curated semantic layer: provide ready-to-use datasets, business-friendly field names, and shared explores/models so analysts rarely touch raw tables.- Self-serve onboarding: automated provisioning workflows (ticketing/Okta groups → sync mapping table) to grant analyst role and default tenant mappings.- Sandboxes: ephemeral analyst sandboxes (time-limited roles & schemas) where analysts can prototype with copied data; promote vetted assets to curated layer via PR process.- Templates & macros: reusable SQL templates, LookML snippets, Power BI templates to accelerate common filters and joins.- Documentation & training: publish policy catalog, examples of how RLS works, and troubleshooting steps for common access issues.- Observability: audit queries, denied access logs, and alert owners on failures—fast feedback reduces repeated support tickets.

Operational best practices:- Policy-as-code: define row/column policies in repo, review via PR, deploy via CI (Terraform/DBT for Snowflake).- Sync identities: keep user attribute table authoritative and automated from HR/IdP to avoid stale privileges.- Test suites: automated tests that validate RLS enforcement (simulate users, ensure denied/allowed rows).- Least privilege reviews: scheduled access reviews and role expiration for temporary grants.- Fallback safe-guards: always enforce critical privacy in Snowflake (server-side). BI tool RLS = UX layer, not sole enforcement.

Trade-offs:- Per-user SSO to Snowflake gives strongest enforcement but can complicate BI driver/connector setup; service principals simplify management but require careful passing of user attributes or stricter row policies keyed to group mappings.- Putting enforcement in Snowflake centralizes control and is harder to bypass; duplicating RLS in BI tools can improve UX but increases maintenance.

Result:This design enforces least privilege at the data layer (Snowflake row access policies + masking), provides convenient analyst experience via curated semantic layers and sandboxes, automates provisioning to reduce friction, and maintains governance through policy-as-code, audits, and regular reviews.

Unlock Full Question Bank

Get access to hundreds of Data and Technical Strategy Alignment interview questions and detailed answers.

Join thousands of developers preparing for their dream job.