41. Why table formats exist
What key problem do open table formats like Apache Iceberg and Delta Lake solve compared with plain Parquet files in object storage?
- A.They make Parquet files smaller by using a new compression algorithm
- B.They replace SQL with a new query language
- C.They add a transaction log and metadata layer for ACID commits, schema evolution, time travel and safe concurrent writes
- D.They only work on local disks, which is faster
Show answer
Answer: C. They add a transaction log and metadata layer for ACID commits, schema evolution, time travel and safe concurrent writes
Plain files in a folder have no atomic commits, so readers can see half-written data and concurrent writers can clash. Table formats track which files belong to each snapshot, giving ACID transactions, time travel, schema evolution and efficient file-level pruning.
42. Distinct users on billions of rows
A dashboard needs daily unique users across 20 billion events. COUNT(DISTINCT user_id) is slow and costly, and a 1 to 2 percent error is fine. What is the best approach?
- A.Use COUNT(*) instead
- B.Sample 10 rows per day
- C.Use an approximate function such as APPROX_COUNT_DISTINCT (HyperLogLog), optionally storing HLL sketches per day to merge later
- D.Export the data to a spreadsheet
Show answer
Answer: C. Use an approximate function such as APPROX_COUNT_DISTINCT (HyperLogLog), optionally storing HLL sketches per day to merge later
Exact distinct counts must track every unique value and often require large shuffles. HyperLogLog estimates cardinality with a small fixed memory and about one percent error. Storing sketches lets you merge days into weeks or months without rescanning raw events, which a stored exact count cannot do.
43. Iceberg hidden partitioning
An Iceberg table is partitioned by day(event_ts). It has grown, and you switch new data to hour(event_ts). What happens to existing queries that filter on event_ts?
- A.They break until every query adds an event_hour column
- B.All old data must be rewritten before the change is allowed
- C.Queries fall back to scanning the whole table forever
- D.They keep working unchanged; Iceberg prunes old files by the day spec and new files by the hour spec
Show answer
Answer: D. They keep working unchanged; Iceberg prunes old files by the day spec and new files by the hour spec
Iceberg stores partition transforms in table metadata, so users filter on the real column and never reference partition columns directly. Partition evolution changes the spec for new writes only, and each data file remembers the spec it was written with. Planning prunes both generations without a rewrite.
44. Clustering for two filter columns
Queries on a large lakehouse table filter by customer_id in some reports and by product_id in others. Sorting by customer_id alone makes the product queries scan almost everything. What helps both?
- A.Partition by both columns, creating millions of folders
- B.Convert the table to CSV
- C.Sort by product_id only
- D.Multi-dimensional clustering such as Z-ordering or liquid/Hilbert clustering on both columns, so file min/max ranges are tight for each
Show answer
Answer: D. Multi-dimensional clustering such as Z-ordering or liquid/Hilbert clustering on both columns, so file min/max ranges are tight for each
A linear sort makes ranges tight only for the first sort column. Space-filling curves like Z-order or Hilbert interleave several columns so each file covers a narrow range of both, improving data skipping for either filter. Partitioning on two high-cardinality columns would cause a small-files problem.
45. A new CSV column shifts the data
A partner adds a discount column in the middle of a daily CSV. Your loader maps columns by position. The load succeeds, but amounts now hold discounts. What makes the pipeline robust to this?
- A.Map by header name; fail on schema surprises
- B.Keep mapping by position but add retries
- C.Load all columns as text so nothing fails
- D.Load only the first five columns
Show answer
Answer: A. Map by header name; fail on schema surprises
Positional mapping silently shifts values when columns move. Mapping by header name, validating the header against the expected schema, and failing (or alerting) on surprises turns silent corruption into a visible error. A data contract with the partner prevents it.
46. Fast single-order lookups in Snowflake
A 6 TB Snowflake table is clustered by order_date. Support staff run many queries like WHERE order_id = 'ORD-88123', and each one scans most micro-partitions because order IDs are spread across all dates. What is the most targeted fix?
- A.Resize the support warehouse from Medium to 2X-Large
- B.Change the clustering key to (order_date, order_id)
- C.Rely on the result cache, since lookups often repeat
- D.Enable search optimization on the order_id column
Show answer
Answer: D. Enable search optimization on the order_id column
Search optimization builds a persistent access path that lets Snowflake skip micro-partitions for selective equality filters on high-cardinality columns, whatever the clustering. A bigger warehouse only scans the same partitions faster at a higher cost, and the result cache helps only when an identical query repeats. A compound key led by order_date does little for filters on order_id alone.
47. A fact table with no measures
A school wants to analyse which students attended which classes on which days. There is no amount or quantity. How is this modelled?
- A.As a dimension table with a date column
- B.It cannot be modelled in a star schema
- C.As a factless fact table with keys to student, class and date
- D.As a single wide table with one column per class
Show answer
Answer: C. As a factless fact table with keys to student, class and date
A factless fact table records that an event happened, using only foreign keys to dimensions. Counting rows answers questions like attendance per class. It can also record coverage, such as which products were on promotion, to find what did not sell.
48. Where the invoice number lives
Each order line fact row carries the invoice number. There is nothing else to describe about the invoice. How is invoice number usually modelled?
- A.As a degenerate dimension stored on the fact row
- B.As its own dimension table with one column
- C.As a measure that can be summed
- D.As part of the date dimension
Show answer
Answer: A. As a degenerate dimension stored on the fact row
A degenerate dimension is a dimension key with no attributes, kept directly in the fact table. Invoice or order numbers are classic examples: useful for grouping lines and tracing back to the source, but a separate table would add nothing.
49. Many small flag columns
An order fact has six low-cardinality flags such as is_gift, payment_type and is_express. Which design keeps the fact table narrow without losing them?
- A.Drop the flags, since they are rarely used
- B.Combine them into one junk dimension of flag combinations
- C.Make each flag its own dimension table
- D.Store them as a JSON string in the date dimension
Show answer
Answer: B. Combine them into one junk dimension of flag combinations
A junk dimension holds the distinct combinations of small flags, and the fact gets one key to it. Six flags with a few values each only make a few hundred rows. This avoids six tiny dimensions or six extra columns on a huge fact.
50. Billing for a ten-second query
A suspended Snowflake warehouse resumes for a 10-second query and is suspended right after. How much compute time is billed?
- A.10 seconds
- B.1 hour
- C.60 seconds
- D.Nothing, since it was suspended
Show answer
Answer: C. 60 seconds
Snowflake bills warehouses per second with a 60-second minimum each time a warehouse starts or resumes. Many tiny queries that each wake a warehouse can cost far more than their run time suggests. Batching work or a sensible auto-suspend setting reduces this.
51. A query that spills to remote storage
Snowflake's query profile shows a large join with many GB 'spilled to remote storage', and the query is very slow. What is the usual fix?
- A.Turn off the result cache
- B.Add more clusters to the multi-cluster warehouse
- C.Use a larger warehouse or reduce the data entering the join
- D.Set a shorter auto-suspend time
Show answer
Answer: C. Use a larger warehouse or reduce the data entering the join
Spilling means the operation ran out of memory and wrote intermediate data to local disk, then to remote storage, which is very slow. A larger warehouse has more memory per query, and filtering or pre-aggregating earlier shrinks the join. Extra clusters help concurrency, not one big query.
52. Staging tables that cost too much
Staging tables in Snowflake are fully reloaded every night from S3, so their data can always be rebuilt. Storage bills show a lot of Time Travel and Fail-safe bytes for them. What change cuts this cost?
- A.Turn off auto-suspend on the load warehouse
- B.Cluster the staging tables by load date
- C.Make them transient tables with short Time Travel
- D.Clone the staging tables before every nightly load
Show answer
Answer: C. Make them transient tables with short Time Travel
Permanent tables keep changed and deleted data for Time Travel plus 7 days of Fail-safe, so a nightly full reload stores many old copies. Transient tables have no Fail-safe and at most 1 day of Time Travel. They suit data you can always reload, not data you cannot rebuild.
53. Changing partitioning on a live table
An Iceberg table was partitioned by month, but data grew and daily partitions are now needed. What does Iceberg partition evolution allow?
- A.It forces a full rewrite of all old data first
- B.It is not possible; a new table is required
- C.It only works if the table is empty
- D.New files use the daily spec; old ones stay monthly
Show answer
Answer: D. New files use the daily spec; old ones stay monthly
Iceberg stores the partition spec in metadata, and each data file records which spec it was written with. You can change the spec, and new writes use it while old files stay as they are. Queries plan across both specs, so no rewrite is needed.
54. How Iceberg plans a query
Iceberg plans scans without listing directories. In which order does it read its metadata to find data files?
- A.Data files, then manifests, then the metadata file
- B.Metadata file, then manifest list, then manifest files, then data files
- C.Directory listing, then partition folders, then data files
- D.Manifest files, then the catalog, then the metadata file
Show answer
Answer: B. Metadata file, then manifest list, then manifest files, then data files
The table metadata file points to the current snapshot's manifest list. The manifest list holds partition ranges per manifest, and each manifest lists data files with column stats. Pruning happens at every level, so planning stays fast even with millions of files.
55. Reading a Delta table as Iceberg
Data is written with Delta Lake on Databricks, but another team's engine only reads Iceberg. Which feature lets them read the same files without a copy?
- A.Delta change data feed
- B.VACUUM with zero retention
- C.Converting the table to ORC
- D.Delta UniForm
Show answer
Answer: D. Delta UniForm
UniForm generates Iceberg (and Hudi) metadata alongside the Delta log for the same Parquet files. Iceberg readers then see the table without data duplication. This works because all these formats share Parquet data files and differ mainly in metadata.
56. Checking data before readers see it
A team wants each day's load written to an Iceberg table, checked by data quality tests, and only then made visible to readers. Which Iceberg feature supports this write-audit-publish flow?
- A.Partition evolution on the daily column
- B.Hidden partitioning by load date
- C.Snapshot expiry after each load
- D.Branches, with main fast-forwarded after tests
Show answer
Answer: D. Branches, with main fast-forwarded after tests
Iceberg branches are named snapshot lines. The job writes to an audit branch, tests run against it, and if they pass main is fast-forwarded to that snapshot. Readers on main never see unchecked data, and a failed load is simply discarded.
57. Monthly users from daily users
A summary table stores daily active users per day. An analyst sums the 30 daily values to report monthly active users. What is wrong?
- A.Nothing; daily users always add up to monthly users
- B.The sum undercounts, since some days are missing
- C.It should be the average of the 30 values
- D.Users active on several days are counted many times
Show answer
Answer: D. Users active on several days are counted many times
Distinct counts are not additive: a user active on 20 days appears in 20 daily counts but is one monthly user. Compute monthly actives from user-level data, or store mergeable sketches such as HyperLogLog per day and merge them. The average of daily values answers a different question.
58. Counting sessions in BigQuery
Which expression correctly counts sessions in the GA4 BigQuery export?
- A.COUNT(DISTINCT CONCAT(user_pseudo_id, CAST(sid AS STRING)))
- B.COUNT(DISTINCT CAST(sid AS STRING)) over all users
- C.COUNTIF(event_name = 'session_start') + COUNT(DISTINCT sid)
- D.COUNT(DISTINCT user_pseudo_id) grouped by event_date
Show answer
Answer: A. COUNT(DISTINCT CONCAT(user_pseudo_id, CAST(sid AS STRING)))
ga_session_id (sid) is a start timestamp, so two users can share one value; it is only unique when combined with user_pseudo_id. Counting page_view events counts views, and distinct user_pseudo_id counts users, not sessions.
59. UI and BigQuery user counts differ
Your exact COUNT(DISTINCT user_pseudo_id) in BigQuery is 1.6% different from Total users in the GA4 interface for the same days. Most likely reason?
- A.BigQuery export drops all users from mobile apps
- B.GA4 reports estimate unique counts with HyperLogLog++
- C.The GA4 interface counts each session as a user
- D.BigQuery stores users in UTC, which adds new users
Show answer
Answer: B. GA4 reports estimate unique counts with HyperLogLog++
GA4 uses the HyperLogLog++ algorithm to estimate distinct counts like users and sessions in reports, which gives small differences. Other causes include thresholding, consent modelling and time zones, but a gap of one or two percent is typical of estimation.
60. Sampled exploration results
A free exploration over 14 months on a large property shows a "sampled" badge. What is the sound response?
- A.Ignore it; sampling only affects the chart colours
- B.Switch the reporting identity to fix sampling
- C.Shorten the date range or query BigQuery for exact numbers
- D.Add more dimensions so GA4 uses the full data
Show answer
Answer: C. Shorten the date range or query BigQuery for exact numbers
Explorations are sampled when a query passes the event quota (10 million events for standard properties). Shorter ranges reduce the events scanned, and the BigQuery export gives unsampled raw data. Standard reports are not sampled.
