Data Modeling Basics for BI Questions

Understand basic data model concepts: fact tables (transactional data, measures), dimension tables (descriptive attributes), and relationships between them. Know how BI tools use data models to enable efficient querying and visualization. Understand concept of primary keys (unique row identifiers) and foreign keys (links to other tables). Know that proper data relationships enable drill-down and filtering capabilities in dashboards. While entry-level analysts typically work with existing models, understanding basics helps effective data work.

MediumTechnical

23 practiced

A dimension contains very high-cardinality attributes (e.g., user_id, device_id) that cause slow filters and large model size in Power BI. Propose modeling and visualization strategies to support analysis without importing the entire attribute set to frontend memory. Discuss trade-offs between import and DirectQuery, pre-aggregation, and UI choices.

Sample Answer

Situation: You have a dimension with very high-cardinality attributes (user_id, device_id) causing slow filters and bloated Power BI models. Here's a practical approach for a BI analyst.

Modeling strategies- Avoid importing full high-cardinality columns. Replace raw IDs with surrogate keys only when needed for joins; keep the full IDs in the source DB.- Build aggregated/rolled-up tables (daily_user_metrics, device_summary) that pre-aggregate metrics at the level your users actually analyze (e.g., user cohort, device type). Store these as import tables.- Use materialized views or warehouse aggregates (CTEs, summary tables) refreshed on a schedule.- Use composite models: keep most tables in Import for performance, and expose the high-cardinality table via DirectQuery only when needed.

DirectQuery vs Import trade-offs- Import: fast visuals, DAX features available, but large memory footprint and slower refreshs for full-volume loads.- DirectQuery: minimal memory, always up-to-date, but higher visual latency, limited DAX functions, and potential gateway/DB load. Use DirectQuery for drill-to-detail scenarios or ad-hoc lookups.- Hybrid (composite): best balance—import aggregates for interactive dashboards; DirectQuery for detailed exploration.

Pre-aggregation techniques- Build multiple granularity tables (hourly, daily, user-cohort) and use query-reduction relationships or role-playing tables to route visuals to the appropriate aggregate.- Use aggregation tables feature in Power BI to automatically redirect queries from detailed DirectQuery tables to imported aggregates.

UI/visualization choices to reduce data pulled- Limit slicer cardinality: show Top N, buckets (cohorts, device type), or search-as-you-type connected to DirectQuery lookups.- Use drillthrough or detail pages: dashboard shows aggregates, user clicks to load details (invokes DirectQuery).- Use incremental loading and parameters (date range pickers) so queries only fetch relevant subsets.- Replace table visuals showing many IDs with count distinct, trend charts, heatmaps, or sampled lists; provide “export details” that triggers server-side extraction if needed.

Example pattern- Import: daily_user_metrics (aggregated counts, revenue by cohort)- DirectQuery: user_detail_lookup (only called when drilling into a specific cohort or via search)- UI: Top 10 device types slicer + drillthrough “View users” button that opens a detail page (DirectQuery call).

Trade-offs summary- Prioritize import for speed and UX; use pre-aggregations to reduce cardinality.- Use DirectQuery sparingly for high-cardinality/detail needs to avoid memory blow-up.- Composite models + aggregation tables give best of both but add complexity in ETL and model maintenance.

This approach keeps dashboards responsive, controls model size, and gives analysts a path to drill into full-detail data when necessary.

MediumTechnical

25 practiced

Compare ETL and ELT approaches for building dimension and fact tables for BI. Discuss trade-offs about data freshness, transformation complexity, compute costs, schema evolution, and how cloud data warehouses (Snowflake, BigQuery, Redshift) influence the choice between ETL and ELT.

Sample Answer

High-level summary:- ETL: Extract → Transform before loading into warehouse. Produces clean, shaped dimension and fact tables ready for BI.- ELT: Extract → Load raw data into warehouse, then Transform in-place (SQL/warehouse compute). Leverages warehouse scalability.

Trade-offs- Data freshness: - ELT typically enables faster ingest and near-real-time availability because raw data is loaded immediately; transformations can run incrementally or on query. ETL can introduce latency if complex transforms run before load.- Transformation complexity: - ETL suits complex, procedural transforms (heavy python/ETL tooling) or strict data governance enforced pre-load. ELT favors SQL-based transformations — great for set-based operations and modular transformations (e.g., dbt).- Compute costs: - ETL shifts compute to ETL infrastructure (may be cheaper if using spot instances). ELT uses warehouse compute — convenient but can increase costs for compute-heavy transformations or frequent recomputations; use clustering, partitioning, materialized views to mitigate.- Schema evolution and maintainability: - ETL enforces stable schemas before BI teams consume data, reducing surprise downstream. ELT gives flexibility: store raw data (source-of-truth), support ad-hoc schema changes, but requires strong discoverability, documented models, and tests to avoid breaking dashboards.

Cloud warehouse influence (Snowflake, BigQuery, Redshift)- Snowflake: separation of storage/compute and auto-scaling makes ELT attractive; pay-per-second compute helps ad-hoc transformations and dbt materializations. Use zero-copy cloning for testing models.- BigQuery: serverless, highly parallel SQL engine excels at ELT for large analytical workloads; but data egress and slot-based pricing (or flat-rate) affect cost planning. Best with query optimization and partitioned tables.- Redshift: traditionally favored ETL or ELT with careful distribution/key design; spectrum and RA3 nodes improve ELT viability but you must manage vacuuming and sort keys to control performance/cost.

Recommendation for BI Analyst:- Prefer ELT + modular transformation (dbt) when warehouse is modern (Snowflake/BigQuery) — faster delivery, easier lineage, raw data retention for audits.- Use ETL when you must enforce strict schema/PII masking before warehouse load, or when transformations are non-SQL and compute on ETL cluster is cheaper.- Hybrid: load raw via ELT, run lightweight ELT transforms for agility, and offload heavy or sensitive processing to ETL pipelines.

Operational best practices:- Implement automated tests, data quality checks, and versioned transformation code (dbt).- Use incremental models, partitions, and materialized views to control compute.- Monitor cost and query patterns; adjust strategy per warehouse characteristics.

HardTechnical

23 practiced

For a fact table with billions of rows, propose specific partitioning, indexing, compression, and pre-aggregation strategies that enable sub-second or low-second dashboard queries for the most common filters (date range, product category, region). Include concrete choices and trade-offs for maintenance and storage costs.

Sample Answer

Situation: You need sub-second or low-second dashboard queries over a fact table with billions of rows where end-users most commonly filter by date range, product category, and region. Below is a concrete, production-ready strategy with choices and trade-offs.

Partitioning- Primary partition: date (daily or monthly depending on query patterns). Use daily if users drill to recent days; monthly if most queries are multi-week/month. Example: RANGE partition on event_date (daily).- Sub-partition / clustering: within each date partition, cluster/sort by (region, product_category) so scans for common filter combos read minimal data (e.g., Hive/BigQuery partition + clustering; Redshift sort keys; SQL Server partitioned table + clustered columnstore).Trade-off: more granular partitions improve pruning but increase metadata and maintenance (more small files in object stores).

Indexing & Physical Layout- Use columnar storage (columnstore/Parquet/ORC/BigQuery native) for excellent compression and vectorized reads.- For row-store DBs: apply clustered columnstore index (SQL Server/Azure Synapse) or Redshift's compound/interleaved sort keys on (event_date, region, product_category).- Add bitmap or low-cardinality secondary indexes on product_category and region when supported (e.g., Oracle bitmap, Exasol).- Use zone maps/minmax indexes (Snowflake automatic, ClickHouse primary key) to further prune.Trade-off: secondary indexes consume storage and slow writes/loads; columnar + clustering gives best read performance for analytics.

Compression & Encoding- Columnar compression: Parquet/ORC with dictionary encoding for low-cardinality columns (product_category, region), delta encoding for date/timestamps, Zstd or Snappy depending on CPU vs size trade-off (Zstd for best compression, Snappy for fastest).- Store numeric measures in packed formats (INT/VARCHAR length reduction).Trade-off: heavier compression reduces storage but increases CPU cost on decompress; choose based on available compute.

Pre-aggregation & Materialization- Build incremental materialized aggregate tables at the common query grain: - daily_product_region_agg: date, product_category, region -> sum(sales), count, etc. - weekly/monthly rollups derived from daily table.- Maintain via incremental ETL or database materialized views refreshed hourly or near-real-time using CDC.- For highly interactive dashboards, create in-memory BI extracts / query caches (e.g., Looker PDTs, Tableau Hyper extracts) for top-level summary tiles.Trade-off: pre-agg storage multiplies storage needs but reduces query latency dramatically; requires ETL jobs and orchestration for correctness.

Query-layer optimizations- Use parameterized queries to allow partition pruning; enforce predicates on partition keys.- Push down filters to the warehouse; avoid client-side filtering.- Use result caching for repeated dashboard queries (warehouse or BI tool cache).

Operational & Cost Considerations- Retention: keep hot daily partitions (last 90 days) online; compress/compact or move older partitions to cheaper storage (S3 Glacier or separate cheaper cluster).- Maintenance: run compaction/optimizing jobs (merge small files, rebuild cluster keys) during off-peak windows. Monitor partition/file count to avoid small-file penalties.- Storage vs compute: choose heavier pre-aggregation and compression if compute is expensive; choose less aggressive compression if CPU is the bottleneck.- Freshness vs cost: hourly incremental refresh hits balance; real-time streaming increases cost and complexity.

Example stack recommendations- Cloud DW: BigQuery (date partitioning + clustering on region, product_category; materialized views + table decorators); or Redshift Spectrum with daily partitions in S3 + sort keys + materialized views; or Snowflake with micro-partitions and clustering keys.- Compression: Parquet/Zstd for S3-backed storage.- BI caching: Looker PDTs or Tableau extracts for sub-second dashboards.

This combination (date partitioning + clustering, columnar + dictionary compression, targeted materialized aggregates, and BI caching) yields sub-second or low-second responses for typical filters, while the trade-offs are higher storage for pre-aggregates and ongoing maintenance (compaction, refresh cadence) which you tune to balance cost and freshness.

EasyTechnical

23 practiced

Explain the difference between a fact table and a dimension table in a BI data warehouse. Give concrete examples (e.g., order_items as a fact, customers as a dimension), list typical columns for each (including measures and attributes), describe how measures and attributes map to charts and filters in dashboards, and explain why separating facts and dimensions matters for query performance and usability.

MediumTechnical

19 practiced

A dashboard joins a large fact table to a very wide customer dimension (hundreds of columns) and runs slowly. Propose a prioritized list of optimizations (schema changes, projection tables, materialized views, columnstore indexes, BI-level techniques) to speed up this report and explain trade-offs for each approach.

Sample Answer

1) Narrow required columns at BI layer (highest priority)- Only select the customer attributes actually shown/filtered. Use explicit field lists, not SELECT * or wide model pulls.- Trade-off: quick, no infra change; requires dashboard dev discipline and coordination with stakeholders.

2) Push filters into query / parameterize queries- Apply dashboard-level filters (date, region, cohort) to reduce fact scan before join.- Trade-off: may limit ad-hoc exploration if overly restrictive.

3) Create a projection / reporting table (denormalized)- Build a table that contains only the needed customer columns + keys used by reports (one row per customer). Update via incremental ETL (CDC).- Trade-off: storage duplication and ETL maintenance, but very fast reads and simple SQL.

4) Materialized view joining fact + skinny customer subset- Materialize the join/result aggregated at dashboard grain (daily, weekly). Refresh strategy: incremental or scheduled.- Trade-off: staleness vs speed; maintenance cost and refresh windows.

5) Columnstore or analytic indexes on fact and customer keys- Use columnstore index for large scans (reduces I/O, good for wide tables if only few columns needed). Ensure index covers join/filter columns.- Trade-off: insert/merge performance hit and storage; benefits OLAP reads.

6) Vertical split of customer dimension (partial normalization)- Move rarely-used hundreds of columns to a secondary table(s) and keep a skinny primary dimension for joins.- Trade-off: more joins when deep attributes needed; reduces IO for common dashboards.

7) BI-level caching / extracts- Use Tableau/Power BI extracts or Looker persistent derived tables to cache results for interactive speed.- Trade-off: data freshness and extra layer to manage.

Prioritization summary: start with BI query optimization (columns/filters), then lightweight infra (extracts), next denormalization or materialized views for repeatable heavy reports, and finally indexing/schema changes for systemic improvement. Choose based on data freshness needs, engineering capacity, and cost.

Unlock Full Question Bank

Get access to hundreds of Data Modeling Basics for BI interview questions and detailed answers.

Join thousands of developers preparing for their dream job.