-
Notifications
You must be signed in to change notification settings - Fork 4
/
iasworld.dweldat.yml
1771 lines (1768 loc) · 77.3 KB
/
iasworld.dweldat.yml
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
sources:
- name: iasworld
loaded_at_field: date_parse(loaded_at, '%Y-%m-%d %H:%i:%S.%f')
tags:
- load_auto
- data_test_iasworld
- type_res
tables:
- name: dweldat
description: '{{ doc("table_dweldat") }}'
freshness:
filter: taxyr >= date_format(current_date - interval '1' year, '%Y')
warn_after: {count: 24, period: hour}
error_after: {count: 48, period: hour}
columns:
- name: ac
description: Air condition
- name: acarea
description: Air conditioning area
- name: accode
description: Support AC code
- name: acval
description: Air conditioning value
- name: addfact
description: Additional factor
- name: addnarea
description: Living area in additions
- name: addnrcnval
description: The RCN from `ADDN`
- name: addnval
description: RCN value of additions
- name: adjarea
description: Adjusted area
- name: adjfact
description: '{{ doc("column_adjfact") }}'
- name: adjgrmfact
description: Neighborhood / class factor applied to `RESGRMVAL`
- name: adjrate
description: Adjusted base rate
- name: adjrcnld
description: Adjusted replacement cost new less depreciation
- name: areafact
description: Area factor
- name: areasum
description: '{{ doc("column_areasum") }}'
- name: attic
description: '{{ doc("shared_column_char_attic_type") }}'
data_tests:
- accepted_values:
name: iasworld_dweldat_attic_in_accepted_values
values: ['1', '2', '3', '4', '5']
additional_select_columns: &select-columns
- taxyr
- parid
- card
- who
- wen
# Excludes non-regression classes, as defined in Assessor's
# online PDF of all class definitions
config: &unique-conditions
where: |
CAST(taxyr AS int) BETWEEN {{ var('data_test_iasworld_year_start') }} AND {{ var('data_test_iasworld_year_end') }}
AND cur = 'Y'
AND deactivat IS NULL
AND class NOT IN ('201', '213', '218', '219', '220', '221', '224', '225', '236', '240', '241', '290', '294', '297')
meta:
description: >
attic (Attic Type) should be an integer between 1 and 5
- not_null:
name: iasworld_dweldat_attic_not_null
additional_select_columns: *select-columns
config: *unique-conditions
meta:
description: attic (Attic Type) should not be null
- name: atticarea
description: Attic area
- name: atticval
description: Attic value
- name: baserate
description: Calculated base rate
- name: basercn
description: Base replacement cost new
- name: basercnld
description: '`RCNLD` for main section of the dwelling'
- name: bgarval
description: Basement garage value
- name: bldguse
description: Building use
- name: bld_modelid
description: '{{ doc("column_bld_modelid") }}'
- name: bsmt
description: '{{ doc("shared_column_char_bsmt") }}'
data_tests:
- accepted_values:
name: iasworld_dweldat_bsmt_in_accepted_values
values: ['1', '2', '3', '4', '5', '6', '7']
additional_select_columns: *select-columns
config: *unique-conditions
meta:
description: >
bsmt (Basement Type) should be an integer between 1 and 7
- not_null:
name: iasworld_dweldat_bsmt_not_null
additional_select_columns: *select-columns
config: *unique-conditions
meta:
description: bsmt (Basement Type) should not be null
- name: bsmtarea
description: Unfinished basement area
- name: bsmtcar
description: Basement garage (number cars)
- name: bsmtval
description: Basement value
- name: calceffyr
description: Calculated effective year
- name: calc_meth
description: '{{ doc("shared_column_calc_meth") }}'
data_tests:
- accepted_values:
name: iasworld_dweldat_calc_meth_accepted_values
values: ["E"]
additional_select_columns: *select-columns
config:
where: |
CAST(taxyr AS int) BETWEEN {{ var('data_test_iasworld_year_start') }} AND {{ var('data_test_iasworld_year_end') }}
AND cur = 'Y'
AND deactivat IS NULL
AND class NOT IN ('201', '213', '218', '219', '220', '221', '224', '225', '236', '240', '241', '290', '294', '297')
AND calc_meth IS NOT NULL
meta:
description: >
calc_meth (Calculation Method) should be null or 'E'
- name: card
description: '{{ doc("column_card") }}'
data_tests:
- accepted_range:
name: iasworld_dweldat_card_gte_1
min_value: 1
additional_select_columns: &select-columns-no-card
- parid
- taxyr
- who
- wen
config: *unique-conditions
meta:
description: card should be >= 1
- not_null:
name: iasworld_dweldat_card_not_null
additional_select_columns: *select-columns-no-card
config: *unique-conditions
meta:
description: card should not be null
- name: catharea
description: Cathedral ceiling area
- name: cathval
description: Cathedral ceiling RCN
- name: cddesc
description: Cost and Design description code
- name: cdpct
description: Cost and Design percent
- name: cdu
description: '{{ doc("shared_column_cdu") }}'
- name: ceiling
description: Ceilling
- name: chgrsn
description: '{{ doc("column_chgrsn") }}'
- name: class
description: '{{ doc("shared_column_class") }}'
data_tests:
- relationships:
name: iasworld_dweldat_class_in_ccao_class_dict
to: ref('ccao.class_dict')
field: class_code
additional_select_columns: *select-columns
config:
where: |
CAST(taxyr AS int) BETWEEN {{ var('data_test_iasworld_year_start') }} AND {{ var('data_test_iasworld_year_end') }}
AND class NOT IN ('EX', 'RR')
AND cur = 'Y'
AND deactivat IS NULL
meta:
category: class_mismatch_or_issue
description: class code must be valid
- res_class_matches_pardat:
name: iasworld_dweldat_class_matches_pardat_class
additional_select_columns: &select-columns-for-pardat-join
- column: card
agg_func: array_agg
- column: taxyr
agg_func: max
- column: parid
agg_func: max
- column: who
agg_func: max
- column: wen
agg_func: max
config: *unique-conditions
meta:
description: at least one class should match pardat class
- res_class_matches_pardat:
name: iasworld_dweldat_exempt_classes_match_pardat_class
additional_select_columns: *select-columns-for-pardat-join
join_type: inner
additional_pardat_filter: AND class = 'EX'
config:
where: |
CAST(taxyr AS int) BETWEEN {{ var('data_test_iasworld_year_start') }} AND {{ var('data_test_iasworld_year_end') }}
AND cur = 'Y'
AND deactivat IS NULL
AND class NOT LIKE 'OA%'
meta:
description: >
at least one class should be exempt or omitted if pardat
is exempt
- name: cline
description: Condominium line
- name: cndadjval
description: Total living area
- name: cndbaseval
description: Main section ground floor area
- name: cndcmplx
description: Condominium complex
- name: cnstfact
description: Construction factor
- name: cnstval
description: Construction factor value
- name: comwallfact
description: Common wall factor
- name: comwallpct
description: Common wall percent
- name: cond
description: Condition code
- name: condolvl
description: Condominium floor level
- name: condosflaovr
description: Condo Sfla override at card level
- name: condotyp
description: Condominium type code
- name: condovw
description: Condominium View
- name: convbldg
description: '{{ doc("column_convbldg") }}'
- name: cur
description: '{{ doc("column_cur") }}'
data_tests:
- accepted_values:
name: iasworld_dweldat_cur_in_accepted_values
values: ['Y', 'D']
additional_select_columns: *select-columns
config:
where: |
CAST(taxyr AS int) BETWEEN {{ var('data_test_iasworld_year_start') }} AND {{ var('data_test_iasworld_year_end') }}
meta:
description: cur should be 'Y' or 'D'
- name: deactivat
description: '{{ doc("column_deactivat") }}'
- name: degrem
description: Degree remodeled
- name: depr
description: Percent good from tables
- name: deprt
description: '{{ doc("column_deprt") }}'
- name: ecndep
description: '{{ doc("column_ecndep") }}'
- name: ecnrsn
description: '{{ doc("column_ecnrsn") }}'
- name: effageovr
description: '{{ doc("column_effageovr") }}'
- name: eff_area
description: Support total by card
- name: effyr
description: '{{ doc("column_effyr") }}'
- name: effyrovr
description: Override effective year
- name: elepct
description: Electric percentage
- name: eleyr
description: Electric year
- name: estpct
description: Estimated percentage
- name: estyr
description: Estimated year
- name: excess
description: '{{ doc("column_excess") }}'
- name: exmppct
description: '{{ doc("column_exmppct") }}'
- name: exmppctover
description: Override exemption percent
- name: exmpval
description: '{{ doc("column_exmpval") }}'
- name: external_calc_rcnld
description: '{{ doc("shared_column_external_calc_rcnld") }}'
data_tests:
- not_accepted_values:
name: iasworld_dweldat_external_calc_rcnld_not_0
values: [0]
quote: false
additional_select_columns: *select-columns
config: *unique-conditions
meta:
description: >
external_calc_rcnld (Net Market Value) should not be 0
- name: external_occpct
description: '{{ doc("shared_column_external_occpct") }}'
data_tests:
- not_accepted_values:
name: iasworld_dweldat_external_occpct_not_0
values: [0]
quote: false
additional_select_columns: *select-columns
config: *unique-conditions
meta:
description: >
external_occpct (Occupancy %) should not be 0
- not_null:
name: iasworld_dweldat_external_occpct_not_null_when_mktrsn_eq_5_or_5b_and_mktadj_is_null
additional_select_columns:
- taxyr
- parid
- card
- who
- wen
- mktrsn
- mktadj
config:
where:
CAST(taxyr AS int) BETWEEN {{ var('data_test_iasworld_year_start') }} AND {{ var('data_test_iasworld_year_end') }}
AND cur = 'Y'
AND deactivat IS NULL
AND mktrsn IN ('5', '5B')
AND mktadj IS NULL
meta:
description: >
external_occpct (Occupancy % [current]) should not be null if
mktrsn (Reason for Override) is 5 or 5B and
mktadj (Occupancy % [deprecated]) is null
- name: external_propct
description: '{{ doc("shared_column_external_propct") }}'
data_tests:
- not_accepted_values:
name: iasworld_dweldat_external_propct_not_0
values: [0]
quote: false
additional_select_columns: *select-columns
config: *unique-conditions
meta:
description: >
external_propct (Proration %) should not be 0
- name: external_rcnld
description: '{{ doc("shared_column_external_rcnld") }}'
data_tests:
- not_accepted_values:
name: iasworld_dweldat_external_rcnld_not_0
values: [0]
quote: false
additional_select_columns: *select-columns
config: *unique-conditions
meta:
description: >
external_rcnld (Full Market Value) should not be 0
- name: extwall
description: '{{ doc("shared_column_char_ext_wall") }}'
data_tests:
- accepted_values:
name: iasworld_dweldat_extwall_in_accepted_values
values: ['1', '2', '3', '4', '6', '7', '8', '9']
config: *unique-conditions
meta:
description: >
extwall (Exterior Construction) should be 1, 2, 3, 4, 6, 7, 8, or 9
- not_null:
name: iasworld_dweldat_extwall_not_null
additional_select_columns: *select-columns
config: *unique-conditions
meta:
description: extwall (Exterior Construction) should not be null
- name: extwall1
description: Exterior wall code
- name: extwall1pct
description: Exterior wall % (for `EXTWALL1`)
- name: finbsmtarea
description: Finished basement living area
- name: finbsmtl
description: Finished basement living area length
- name: finbsmtval
description: Finished basement value
- name: finbsmtw
description: Finished basement living area width
- name: fixaddl
description: Number additional plumbing fixtures
- name: fixbath
description: '{{ doc("shared_column_char_fbath") }}'
data_tests:
- accepted_range:
name: iasworld_dweldat_fixbath_matches_number_of_units
min_value: 1
max_value: |
CASE
WHEN user14 IS NULL OR user14 = '0' OR user14 = '6' THEN 7
WHEN user14 = '1' THEN 14
WHEN user14 = '2' THEN 21
WHEN user14 = '3' THEN 28
WHEN user14 = '4' THEN 35
WHEN user14 = '5' THEN 42
ELSE 7
END
additional_select_columns: &select-columns-with-num-units
- taxyr
- parid
- card
- who
- wen
- user14
config: *unique-conditions
meta:
description: >
fixbath (Number of Full Baths) should be <= 7 times the
number of units (user14)
- not_null:
name: iasworld_dweldat_fixbath_not_null
additional_select_columns: *select-columns
config: *unique-conditions
meta:
description: >
fixbath (Number of Full Baths) should not be null
- name: fixbath1
description: Number of full bathrooms on the first floor
- name: fixbath2
description: Number of full bathrooms on the second floor
- name: fixbath3
description: Number of full bathrooms on the third floor
- name: fixbath4
description: Number of four fixture bathrooms
- name: fixbath4m
description: Number of four fixture bathrooms in Main Floor
- name: fixbath4u
description: Number of four fixture bathrooms in upper Floor
- name: fixbath5
description: Number of five fixture bathrooms
- name: fixbath5m
description: Number of five fixture bathrooms in Main Floor
- name: fixbath5u
description: Number of five fixture bathrooms in upper Floor
- name: fixbath6
description: Number of six fixture bathrooms
- name: fixbath7
description: Number of seven fixture bathrooms
- name: fixbathb
description: Number of full bathrooms in the basement
- name: fixbathm
description: Number full bathrooms in Main Floor
- name: fixbathu
description: Number full bathrooms in upper Floor
- name: fixhalf
description: '{{ doc("shared_column_char_hbath") }}'
data_tests:
- accepted_range:
name: iasworld_dweldat_fixhalf_gte_0
min_value: 0
additional_select_columns: *select-columns
config: *unique-conditions
meta:
description: >
fixhalf (Number of Half Baths) should be >= 0
- accepted_range:
name: iasworld_dweldat_fixhalf_matches_number_of_units
min_value: 0
max_value: |
CASE
WHEN user14 IS NULL OR user14 = '0' OR user14 = '6' THEN 5
WHEN user14 = '1' THEN 10
WHEN user14 = '2' THEN 15
WHEN user14 = '3' THEN 20
WHEN user14 = '4' THEN 25
WHEN user14 = '5' THEN 30
ELSE 5
END
additional_select_columns: *select-columns-with-num-units
config: *unique-conditions
meta:
description: >
fixhalf (Number of Half Baths) should be <= 5 times the
number of units (user14)
- name: fixhalf1
description: Number of half baths on the first floor
- name: fixhalf2
description: Number of half baths on the second floor
- name: fixhalf3
description: Number of half baths on the third floor
- name: fixhalfb
description: Number of half baths in the basement
- name: fixhalfm
description: Number half bathrooms in Main Floor
- name: fixhalfu
description: Number half bathrooms in upper Floor
- name: fixtot
description: Total plumbing fixtures
- name: fixtotovr
description: Bathroom fixture override
- name: floor
description: Floor
- name: floor1
description: Floor 1
- name: floor1pct
description: Floor percent
- name: floorfact
description: Floor factor
- name: flr1area
description: Sub total value
- name: flr1val
description: First floor structure RCN
- name: flrharea
description: Half floor structure square foot
- name: flrhval
description: Half floor structure RCN
- name: flruarea
description: Upper floor structure square foot
- name: flruval
description: Upper floor structure RCN
- name: fuel
description: Heating fuel type
- name: fundep
description: '{{ doc("column_fundep") }}'
- name: funrsn
description: '{{ doc("column_funrsn") }}'
- name: furpct
description: Furnace percentage
- name: furyr
description: Furnace year
- name: grade
description: '{{ doc("shared_column_char_cnst_qlty") }}'
- name: grdfact
description: Grade factor
- name: grmovr
description: Override for the GRM Multiplier pulled from `LPNBHD`
- name: grmrent
description: Gross rent multiplier rent
- name: grmunits
description: Gross rent multiplier units
- name: grpadj
description: '{{ doc("column_grpadj") }}'
- name: heat
description: '{{ doc("shared_column_char_heat") }}'
data_tests:
- accepted_values:
name: iasworld_dweldat_heat_in_accepted_values
values: ['1', '2', '3', '4']
additional_select_columns: *select-columns
config: *unique-conditions
meta:
description: >
heat (Heating) should be an integer between 1 and 4
- not_null:
name: iasworld_dweldat_heat_not_null
additional_select_columns: *select-columns
config: *unique-conditions
meta:
description: >
heat (Heating) should not be null
- name: heatarea
description: Heat area
- name: heatsys
description: Heating system type
- name: heatval
description: Heating/air value
- name: hga
description: '{{ doc("column_hga") }}'
- name: iasw_id
description: '{{ doc("column_iasw_id") }}'
- name: intext
description: Interior condition relative to exterior
- name: intwall
description: Inside wall
- name: intwall1
description: Inside wall 1
- name: intwall1pct
description: Inside wall percent
- name: intwallfact
description: Inside wall factor
- name: jur
description: '{{ doc("column_jur") }}'
- name: kitpct
description: Kitchen percentage
- name: kityr
description: Kitchen year
- name: loaded_at
description: '{{ doc("shared_column_loaded_at") }}'
- name: locmult
description: Local multiplier
- name: lumpcamod
description: '{{ doc("column_lumpcamod") }}'
- name: lumpcure
description: Lump cure value
- name: mastrimarea
description: Masonry trim square footage
- name: mastriml
description: Masonry trim, length
- name: mastrimw
description: Masonry trim, width
- name: mgfa
description: Main section ground floor area
- name: mktadj
description: '{{ doc("column_mktadj") }}'
data_tests:
- not_null:
name: iasworld_dweldat_mktadj_not_null_when_mktrsn_eq_5_or_5b_and_external_occpct_is_null
additional_select_columns:
- taxyr
- parid
- card
- who
- wen
- mktrsn
- external_occpct
config:
where:
CAST(taxyr AS int) BETWEEN {{ var('data_test_iasworld_year_start') }} AND {{ var('data_test_iasworld_year_end') }}
AND cur = 'Y'
AND deactivat IS NULL
AND mktrsn IN ('5', '5B')
AND external_occpct IS NULL
meta:
description: >
mktadj (Occupancy % [deprecated]) should not be null if
mktrsn (Reason for Override) is 5 or 5B and
external_occpct (Occupancy % [current]) is null
- name: mktrsn
description: '{{ doc("column_chgrsn") }}'
data_tests:
- accepted_values:
name: iasworld_dweldat_mktrsn_eq_5_or_5b_when_external_occpct_or_mktadj_not_null
values:
- '5'
- 5B
additional_select_columns:
- taxyr
- parid
- card
- who
- wen
- external_occpct
- mktadj
config:
where: |
CAST(taxyr AS int) BETWEEN {{ var('data_test_iasworld_year_start') }} AND {{ var('data_test_iasworld_year_end') }}
AND cur = 'Y'
AND deactivat IS NULL
AND (external_occpct IS NOT NULL OR mktadj IS NOT NULL)
meta:
description: >
mktrsn (Reason for Override) should be 5 or 5B
if external_occpct (Occupancy % [current]) or
mktadj (Occupancy % [deprecated]) is not null
- name: modover
description: Residential override model
- name: mvpattic
description: MVP Attic
- name: mvpbsmt
description: MVP Basement
- name: mvpsf
description: MVP square footage
- name: mvpstyle
description: MVP style
- name: mvpunfin
description: MVP unfinished
- name: nccalc
description: '{{ doc("column_nccalc") }}'
- name: ncoval
description: '{{ doc("column_ncoval") }}'
- name: nctot
description: '{{ doc("column_nctot") }}'
- name: ncval
description: '{{ doc("column_ncval") }}'
- name: newconmo
description: '{{ doc("column_newconmo") }}'
- name: newconpct
description: '{{ doc("column_newconpct") }}'
- name: numident
description: Identical units
- name: obsdep
description: Observable Condition
- name: obsrsn
description: Reason code for observable condition
- name: ovrmraval
description: '{{ doc("column_ovrmraval") }}'
- name: ovrnbhd
description: '{{ doc("column_ovrnbhd") }}'
- name: ovrrcn
description: Override RCN value
- name: ovrrcnld
description: '{{ doc("column_ovrrcnld") }}'
- name: parid
description: '{{ doc("shared_column_pin") }}'
data_tests:
- relationships:
name: iasworld_dweldat_parid_in_pardat_parid
to: source('iasworld', 'pardat')
field: parid
additional_select_columns: &select-columns-no-parid
- taxyr
- card
- who
- wen
config: *unique-conditions
meta:
category: parid
description: parid should be in pardat
- not_null:
name: iasworld_dweldat_parid_not_null
additional_select_columns: *select-columns-no-parid
config: *unique-conditions
meta:
description: parid should not be null
- name: pctcomplete
description: Percent of construction completed
- name: plumbgrade
description: Plumbing grade
- name: plumval
description: Plumbing value
- name: plupct
description: Plumbing percentage
- name: pluyr
description: Plumbing year
- name: prior_renolvl
description: Prior renovation Level
- name: prodamage
description: '{{ doc("column_prodamage") }}'
- name: prodate
description: '{{ doc("column_prodate") }}'
- name: profact
description: '{{ doc("column_profact") }}'
- name: protype
description: '{{ doc("column_protype") }}'
- name: rcnfact
description: RCN adjustment factor
- name: rcnld
description: '{{ doc("column_rcnld") }}'
- name: rcnval
description: Replacement cost new of dwelling
- name: recnr
description: '{{ doc("column_recnr") }}'
- name: recromarea
description: Recreational room area
- name: recroml
description: Recreational room length
- name: recromw
description: Recreational room width
- name: rectype
description: '{{ doc("column_rectype") }}'
- name: recval
description: Recreational room value
- name: rembath
description: Bathrooms remodeled
- name: remkit
description: Kitchen remodeled
- name: renolvl
description: Renovation level
- name: renoovr
description: Override for renovation level
- name: renopoint
description: Sum of renovation points assigned to this parcel
- name: renoyr
description: Year of renovation
- name: resfeatval
description: The total residential feature value
- name: resgrmval
description: Gross rent multiplier value
- name: resmod
description: Residential cost model
- name: rmbed
description: '{{ doc("shared_column_char_beds") }}'
data_tests:
- expression_is_true:
name: iasworld_dweldat_rmbed_lte_rmtot
expression: <= rmtot
additional_select_columns:
- parid
- taxyr
- card
- who
- wen
- rmtot
config: *unique-conditions
meta:
category: relationships
description: >
rmbed (Number of Bedrooms) should be <= rmtot (Number of Rooms)
- accepted_range:
name: iasworld_dweldat_rmbed_matches_number_of_units
min_value: 1
max_value: |
CASE
WHEN user14 IS NULL OR user14 = '0' OR user14 = '6' THEN 8
WHEN user14 = '1' THEN 16
WHEN user14 = '2' THEN 24
WHEN user14 = '3' THEN 32
WHEN user14 = '4' THEN 40
WHEN user14 = '5' THEN 48
ELSE 8
END
additional_select_columns: *select-columns-with-num-units
config: *unique-conditions
meta:
description: >
rmbed (Number of Bedrooms) should be <= 8 times the
number of units (user14)
- not_null:
name: iasworld_dweldat_rmbed_not_null
additional_select_columns: *select-columns
config: *unique-conditions
meta:
description: >
rmbed (Number of Bedrooms) should not be null
- name: rmbed1
description: Number of bedrooms on the first floor
- name: rmbed2
description: Number of bedrooms on the second floor
- name: rmbed3
description: Number of bedrooms on the third floor
- name: rmbedb
description: Number of bedrooms in the basement
- name: rmdin
description: Total number of dining rooms
- name: rmdin1
description: Number of dining rooms on the first floor
- name: rmdin2
description: Number of dining rooms on the second floor
- name: rmdin3
description: Number of dining rooms on the third floor
- name: rmdinb
description: Number of dining rooms in the basement
- name: rmfam
description: Number family rooms
- name: rmfam1
description: Number of family rooms on the first floor
- name: rmfam2
description: Number of family rooms on the second floor
- name: rmfam3
description: Number of family rooms on the third floor
- name: rmfamb
description: Number of family rooms in the basement
- name: rmkit
description: Total number of kitchens
- name: rmkit1
description: Number of kitchens on the first floor
- name: rmkit2
description: Number of kitchens on the second floor
- name: rmkit3
description: Number of kitchens on the third floor
- name: rmkitb
description: Number of kitchens in the basement
- name: rmliv
description: Total number of living rooms
- name: rmliv1
description: Number of living rooms on the first floor
- name: rmliv2
description: Number of living rooms on the second floor
- name: rmliv3
description: Number of living rooms on the third floor
- name: rmlivb
description: Number of living rooms in the basement
- name: rmoth
description: Total number of other rooms
- name: rmoth1
description: Number of other rooms on the first floor
- name: rmoth2
description: Number of other rooms on the second floor
- name: rmoth3
description: Number of other rooms on the third floor
- name: rmothb
description: Number of other rooms in the basement
- name: rmtot
description: '{{ doc("shared_column_char_rooms") }}'
data_tests:
- not_null:
name: iasworld_dweldat_rmtot_not_null
additional_select_columns: *select-columns
config: *unique-conditions
meta:
description: rmtot (Number of Rooms) should not be null
- accepted_range:
name: iasworld_dweldat_rmtot_sf_between_1_and_40
min_value: 1
max_value: 40
additional_select_columns: *select-columns
config:
where: |
CAST(taxyr AS int) BETWEEN {{ var('data_test_iasworld_year_start') }} AND {{ var('data_test_iasworld_year_end') }}
AND class NOT IN ('211', '212')
AND cur = 'Y'
AND deactivat IS NULL
meta:
description: rmtot (Number of Rooms) should be between 1 and 40
- accepted_range:
name: iasworld_dweldat_rmtot_sf_between_1_and_50
min_value: 1
max_value: 50
additional_select_columns: *select-columns
config:
where: |
CAST(taxyr AS int) BETWEEN {{ var('data_test_iasworld_year_start') }} AND {{ var('data_test_iasworld_year_end') }}
AND class IN ('211', '212')
AND cur = 'Y'
AND deactivat IS NULL
meta:
description: rmtot (Number of Rooms) should be between 1 and 50
- name: salekey
description: '{{ doc("column_salekey") }}'
- name: seq
description: '{{ doc("shared_column_seq") }}'
data_tests:
- sequential_values:
name: iasworld_dweldat_seq_all_sequential_exist
group_by_columns:
- parid
- taxyr
- card
additional_select_columns:
- who
- wen
config: *unique-conditions
meta:
description: seq should be sequential
- name: sfla
description: '{{ doc("shared_column_char_bldg_sf") }}'
data_tests:
- accepted_range:
name: iasworld_dweldat_sfla_between_1_and_999_for_class_202
min_value: 1
max_value: 999
additional_select_columns: &select-columns-with-class
- taxyr
- parid
- card
- class
- who
- wen
config:
where: |
CAST(taxyr AS int) BETWEEN {{ var('data_test_iasworld_year_start') }} AND {{ var('data_test_iasworld_year_end') }}
AND cur = 'Y'
AND deactivat IS NULL
AND class = '202'
meta:
description: >
sfla (Building Square Footage) should be between
1 and 999 for class 202 cards
- accepted_range:
name: iasworld_dweldat_sfla_between_1_and_2000_for_class_207
min_value: 1
max_value: 2000
additional_select_columns: *select-columns-with-class
config:
where: |
CAST(taxyr AS int) BETWEEN {{ var('data_test_iasworld_year_start') }} AND {{ var('data_test_iasworld_year_end') }}
AND cur = 'Y'
AND deactivat IS NULL
AND class = '207'
meta:
description: >
sfla (Building Square Footage) should be between
1 and 2000 for class 207 cards
- accepted_range:
name: iasworld_dweldat_sfla_between_1_and_2200_for_class_205
min_value: 1
max_value: 2200
additional_select_columns: *select-columns-with-class
config:
where: |
CAST(taxyr AS int) BETWEEN {{ var('data_test_iasworld_year_start') }} AND {{ var('data_test_iasworld_year_end') }}
AND cur = 'Y'
AND deactivat IS NULL
AND class = '205'
meta:
description: >
sfla (Building Square Footage) should be between
1 and 2200 for class 205 cards
- accepted_range:
name: iasworld_dweldat_sfla_between_1_and_10000_for_class_210
min_value: 1
max_value: 10000
additional_select_columns: *select-columns-with-class
config:
where: |
CAST(taxyr AS int) BETWEEN {{ var('data_test_iasworld_year_start') }} AND {{ var('data_test_iasworld_year_end') }}
AND cur = 'Y'
AND deactivat IS NULL
AND class = '210'
meta:
description: >
sfla (Building Square Footage) should be between
1 and 10000 for class 210 cards
- accepted_range:
name: iasworld_dweldat_sfla_between_1_and_10000_for_class_234
min_value: 1
max_value: 10000
additional_select_columns: *select-columns-with-class
config: