BigQuery Administration Interview questions

Difference between Native table vs external table

BigQuery Native tableExternal table
Data is Imported and stored in BigQuery
Data resides in cloud storage
Data is in capacitor format (managed storage)External table can be created on CSV, JSON, Parquet and AVRO file types
High performance. Since the data is organized and managed by BigQueryModerate to High performance
High storage costs : In BigQuery you pay for "uncompressed" data sizeReduced storage cost. Storing compressed files (eg Parquet with Snappy compression) can yield up to 90% reduction in file size
CRUD Compatible - Run Create, Read, Update and Delete on native tablesExternal tables are READ only
Query results are cachedNo caching

Difference between Native table vs snapshot table

BigQuery Native tableBigQuery Snapshot table
Has dedicated storageShared storage, Only modified partitions are stored in dedicated storage
You pay 100% of the table storage cost
You pay only modified and deleted partitions. In most cases 80% savings in storage 
CRUD Compatible - Run Create, Read, Update and Delete on native tablesRead only

BigQuery on-demand vs flat rate pricing

BigQuery on-demandBigQuery Flat Rate
Server less, pay for the data scanned by the SQL queriesPurchase dedicated slots. The slots are always running regardless of usage
Your project can burst and use up to 2000 slots, queries beyond that will be queuedYou are capped at the number of slots that you purchase. Beyond capacity the queries are queued
Highly economical. Great value if your project has Analysis costs less than $40kFor large enterprise customers with high scale (>$40k), Flat Rate might help
Costs are unpredictable (Pay per use)Costs are fixed and no surprises
Good for regular analytics needsFlexi slots can be purchased to surgically handle heavy work loads