March 2018 Summaries

The text discusses the performance differences between traditional web analytics tools like Google Analytics and data warehouses when handling large datasets, with a focus on how sampling techniques can address these differences. It explains the basics of sampling, which involves selecting a subset of data to estimate population properties, and highlights the importance of using unique user identifiers for accurate results. The text outlines two primary methods for selecting random rows in SQL: simple random sampling and systematic sampling, favoring the latter for its implementation simplicity in SQL environments. For sequence-generated user identifiers, systematic sampling can be executed using the MOD operation, whereas for string or non-sequential identifiers, hash functions such as FARM_FINGERPRINT in BigQuery are recommended to achieve uniform distribution. The document also cautions against potential pitfalls like sampling bias, especially in cases involving rare events, and encourages more sophisticated techniques when necessary to maintain precision.

BigQuery and Postgres have great tools for loading Big Data efficiently when data is immutable, distributed across tables with timestamp columns. To prepare for this, indexes must be created in the Postgres database before loading processes into BigQuery. The use of wild card tables pattern allows for efficient storage on both sides. A script can be used to automate the uploading process by using the BigQuery CLI and a bash script that compresses data before uploading it. This enables real-time data loading with just a single day's data, making it suitable for large-scale projects. The script can be scheduled to run daily or hourly, allowing for nearly real-time data updates.