Comments (3)
In the meantime, you can at least avoid the error by specifying .render()
on any refs/sources that you don't want dbt to turn into where false limit 0
subqueries.
If we added support for flags.EMPTY
, then you could write something like:
{% set src_cpc_raw = source('customer_preference_center', 'customer_preference') -%}
select
...,
_PARTITIONTIME as ingestion_dt
from
{{ src_cpc_raw.render() }}, -- this will be rendered simply into `project.dataset.identifier` (no subquery)
unnest(centralPreferences) as centralPreferences
where 1=1
{% if flags.EMPTY %}
and false limit 0 -- instead, I manually add the "empty limit" here
{% endif %}
{% if is_incremental() %}
and date(ingestion_dt) >= date_sub("{{ latest_partition_filter(src_cpc_raw) }}", interval 1 day)
{% endif %}
qualify row_number() over (partition by customerId_token, preference order by updateTS desc, ingstn_ts desc) = 1
from dbt-bigquery.
@christopherekfeldt Thanks for the report!
The mechanism we're using for --empty
is to wrap the source()
and ref()
calls in a subquery with select * ... where false limit 0
. This *
doesn't pass along pseudo-columns.
The first idea that came to mind:
- we first access BQ metadata to figure out if the source/ref relation is an ingestion-time partitioned table
- if it is, we include the pseudo-column — but even then, it must be aliased, so your subsequent query (referencing it as
_PARTITIONTIME
) will still fail
select *, _PARTITIONTIME as partition_time
from dbt_jcohen.myingestiontable
where false limit 0
Other ideas:
- Append
where false limit 0
without wrapping in a subquery (but this won't play nice with otherwhere
statements,unnest
, etc) - Allow you to opt out this particular
source()
from the default--empty
subquery, but accessflags.EMPTY
to apply your own conditional filter
from dbt-bigquery.
My suggestion to solve this issue is related to dbt-labs/dbt-core#8560:
we need to be able to override the rendering from sources/refs.
For sources, we could have a way to add a parameter to the macro to add those metadata fields and for refs, since it would be related to "time_ingestion_partitioning": True,
, we should be able to detect them by ourselves.
from dbt-bigquery.
Related Issues (20)
- [Bug] many timeouts with `priority: interactive` config HOT 7
- [Bug] Incremental models don't update new elements in struct column
- [Regression] `docs generate` raises error when querying dataset with BQ ML models HOT 1
- dbt bigquery 1.3 Flake8 is failing a mypy check
- [Bug] Cannot parse partition config when specified in property YAML file HOT 2
- [Test] testing workflow
- [Tech Debt] Run all python model tests in sequence across runs
- [Feature] Retrieving and using table/column descriptions from BigQuery
- [Bug] Using `grants` on `materialized_view` raises an error
- [Bug] Build verification is failing HOT 1
- [Bug] Updating partitioning/clustering on a table self referencing itself will fail and delete the table
- [Bug] Dataproc (Python models) do not retry polling on 5xx errors HOT 1
- [Bug] Data mix-up in struct with enforced contracts
- [Bug] Nested record field is not quoted and causes build error when contract enforced enabled. HOT 1
- [Bug] `dbt show` integration test using JSON struct is failing for `1.8`
- Update the build check workflow
- [Bug] spark_catalog requires a single-part namespace in dbt python incremental model
- [Bug] Jobs timing out early regardless of job_execution_timeout_seconds
- [Bug] Bug in partitions_match method causing tables to be dropped for full refresh even though partitioning spec did not change
- [Bug] is_incremental always return False when use Project Number instead of Project ID 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 dbt-bigquery.