BigQuery Administration Interview questions

Difference between Native table vs external table

BigQuery Native table	External 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 BigQuery	Moderate to High performance
High storage costs : In BigQuery you pay for "uncompressed" data size	Reduced 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 tables	External tables are READ only
Query results are cached	No caching

BigQuery Native table	BigQuery Snapshot table
Has dedicated storage	Shared 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 tables	Read only

BigQuery on-demand	BigQuery Flat Rate
Server less, pay for the data scanned by the SQL queries	Purchase 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 queued	You 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 $40k	For 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 needs	Flexi slots can be purchased to surgically handle heavy work loads