Comments (11)
not sure how much space an array of bools takes up, but we could also treat an INT64 as an array of 64 bools. That would probably make this even more magic to understand, but if we are hiding access behind UDFs that would be fine.
example:
CREATE TEMPORARY FUNCTION udf_days_since(seen INT64) AS ((
SELECT
MIN(days_since)
FROM
UNNEST(GENERATE_ARRAY(0,27)) AS days_since
WHERE
(seen >> days_since) & 1 = 1));
WITH
raw AS (
SELECT
0x0534 AS seen)
SELECT
BIT_COUNT(seen & 0x7F) AS days_seen_last7,
udf_days_since(seen) AS days_since_seen
FROM
raw
from bigquery-etl.
that said, I would be in favor of adding this as an additional column, and not replacing days_since_*
from bigquery-etl.
So days_seen would have a length of 28? Looks like it should work :)
My only suggestion is that we find a way to get away from needing a schema change every time we add a new usage criteria. I expect this to happen regularly. Perhaps a "reshape" and have a single usage_criteria
column and then we multiply the rows by the number of usage criteria that we have.
from bigquery-etl.
find a way to get away from needing a schema change every time we add a new usage criteria
It's indeed unfortunate that adding new usage criteria is fairly heavy-weight. I expect we can massage this in later-stage ETL so that it's more convenient for end users doing analysis to adapt to integrating new usage criteria, but at the earlier stages of ETL, establishing a reasonable process for backfilling a new criterion seems like the critical piece. Establishing and documenting that process will be powerful for making this pipeline productive for evolving analyses.
from bigquery-etl.
that said, I would be in favor of adding this as an additional column, and not replacing days_since_*
That sounds reasonable.
from bigquery-etl.
find a way to get away from needing a schema change every time we add a new usage criteria
It's indeed unfortunate that adding new usage criteria is fairly heavy-weight. I expect we can massage this in later-stage ETL so that it's more convenient for end users doing analysis to adapt to integrating new usage criteria, but at the earlier stages of ETL, establishing a reasonable process for backfilling a new criterion seems like the critical piece. Establishing and documenting that process will be powerful for making this pipeline productive for evolving analyses.
Ease of setting up a new usage criteria is a factor too, as we hope encourage, for example, a very small team that builds a particular browser feature, to start caring about growth, which will depend on the barrier to entry for enabling growth tracking of that feature.
I don't understand why it doesn't make sense to just have a feature_id column and, instead of writing to a new set of columns for each usage criteria, write to a new set of rows, while setting feature_id appropriately. That lack of understanding may just be because I'm not an engineer though ;)
from bigquery-etl.
establishing a reasonable process for backfilling a new criterion seems like the critical piece
So I learned this weekend that this kind of backfill is difficult. Backfills can't be done in a single query, because they take so long. Just calculating a new column from existing rows costs $1185, because the table is 237TiB.
from bigquery-etl.
In terms of adding criteria, maybe what we need is to have all pings in the last 28 days, so that derived tables can efficiently apply criteria post-facto
from bigquery-etl.
In terms of adding criteria, maybe what we need is to have all pings in the last 28 days, so that derived tables can efficiently apply criteria post-facto
@relud and I discussed this over video, and I'm very interested to see us pursue this path. It will be somewhat more expensive in terms of data storage and scan costs, but cheaper to maintain in terms of backfills and delaying definition of usage criteria to the exact_mau28_by_dimensions
stage for flexibility in adding new criteria.
from bigquery-etl.
I said all pings in the last 28 days, but I meant all clients_daily_v6 rows in the last 28 days
from bigquery-etl.
We've done this!
from bigquery-etl.
Related Issues (20)
- Add custom ping into Shredder HOT 4
- Change broken_site_report view to only use historical tables
- Add a separate view for live user_reports
- Improve dryrun skip logic for stage deploys
- Encode Derived Funnel Dimensions into UDF HOT 3
- Adding the ability to override scheduling parameters on backfill
- Schema updates for tables with not associated query.sql HOT 3
- Support publishing only authorized views
- Use schema.yaml for static tables instead of schema.json HOT 1
- [Data Checks Bug]: Apple CPUs are represented as empty string HOT 1
- Python 3.12 bootstrap - ModuleNotFoundError: No module named 'distutils'
- Create new derived query for Firefox-CI worker costs
- Support secrets in DAG generation
- event_monitoring_live generation is skipping apps that only have events defined in dependencies HOT 2
- materialized views don't get updated after deploy HOT 2
- Recreating materialized views may cause occasional dry run failures on main HOT 1
- Union default_table_workgroup_access with table-level workgroup_access in metadata generation logic
- `event` column from `events_stream` inaccessible in Looker HOT 2
- CI may be running stale tests in some cases HOT 1
- Monitor plus Attribution: Update ETL to store the channel for relevant Bigquery Tables and Looker explore HOT 2
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from bigquery-etl.