ccao-data / data-architecture Goto Github PK
View Code? Open in Web Editor NEWCodebase for CCAO data infrastructure construction and management
Home Page: https://ccao-data.github.io/data-architecture/
Codebase for CCAO data infrastructure construction and management
Home Page: https://ccao-data.github.io/data-architecture/
The initial dbt project we added in #23 contains documentation for many of the Athena views that we build on top of our source data, but not all of them. It also does not contain documentation for source tables yet. Add documentation for these tables and views.
Was looking through the views when I was linting things a few weeks back and found a few to-dos:
values_by_year
CTE in vw_assessment_roll can be replaced with the equivalent viewvalues_by_year
CTE in vw_top_5 can be replaced with the equivalent viewThe current "distance to water" feature is based on the hydrology layer from TIGER/the U.S. Census. It doesn't really have any concept of whether each PIN is close to "good water" (a nice pond, the lake, pretty river, etc.) or "bad water" (retention pond, streams that cause flooding). We should update the feature with a labeled layer of good vs. bad water, and potentially split the feature into two separate ones. Ray is reaching out to Highway/GIS to see if we have a more complete or labeled water layer already.
Every commit to the master
branch should run a workflow that rebuilds and deploys our documentation site (#33).
Bonus points if you can get deploy previews set up, but not required.
Depends on #33.
FHFA publishes quarterly UAD statistics aggregated to the Census tract level. We should collect this data in S3 for potential use in modeling and reporting.
We need to:
We should also:
@jeancochrane is making progress on implementing dbt. We need to
It seems like SQLFluff doesn't quite understand how to handle for
loops in our dbt Jinja templates, and it keeps wanting to pull the {% endfor %}
tag onto its own line. For example:
data-architecture/dbt/tests/generic/test_column_length.sql
Lines 16 to 20 in 3f31ca0
It's also not totally clear if SQLFluff wants the body of {% test %}
and {% macro %}
blocks to be indented. I would prefer if their bodies weren't indented, since the tags act more like file metadata than proper blocks given dbt's organization schema for tests and macros (where each test/macro gets its own file), but either way we should get SQLFluff to enforce the rule automatically.
Update our SQLFluff indentation rules so that we can fix and enforce both of these policies.
When we join vw_pin_sale to other tables/views, row count is inconsistent. This is likely cause by
ROW_NUMBER() OVER (
PARTITION BY sales.parid, sales.saledt ORDER BY sales.price DESC
) AS max_price,
-- Some pins sell for the exact same price a few months after
-- they're sold. These sales are unecessary for modeling and may be
-- duplicates
LAG(DATE_PARSE(SUBSTR(sales.saledt, 1, 10), '%Y-%m-%d')) OVER (
PARTITION BY sales.parid, sales.price ORDER BY sales.saledt
) AS same_price_earlier_date
We may be able to fix this by ordering by wen
field or something similar as well.
Certain iasWorld tables are constantly being updated with new data. When running unit tests and reports, it is critical that these tables be as up-to-date as possible.
dbt builds in tests for source freshness that we can use to check that the most recent possible data is loaded. Now that all iasWorld tables are sources (as of #71), we should use this functionality as an additional check (in addition to the current CloudWatch checks) to ensure that iasWorld data was successfully updated by sqoop.
Specifically, we should target the following tables:
dbt source freshness requires a loaded_at_field
. In our case, we should use the wen
column. It can also use an optional filter
, which in our case should constrain the check to the most recent tax year.
Some tables in iasWorld contain rows that have been deactivated - these rows should not be used for anything by Data, they are maintained in iasWorld purely for historical purposes. As of now, I only know of three tables where we need to be wary of removing deactivated rows - htpar, land, and asmt_all. I've added the conditional WHERE deactivat IS NULL where necessary.
Once we're feeling ready to put our data catalog work into production, merge the data-catalog
branch into the master
branch of this repo.
This will require updating our GitHub Actions workflows (#31) to remove references to data-catalog
and point any operations that were running against that environment to run against the prod
environment. Note that since the data-catalog
branch runs dbt in the ci
profile, we'll also want to double-check references to --target ci
in the data-catalog
branch context and switch them to --target prod
where appropriate.
This issue came up in appeal worksheet process and should be addressed in our characteristics views as well. Some parcels have multiple landlines that shouldn't actually be added given a non-null value for influ
in iasworld.land
We should update the data onboarding ticket to reflect the current state of this repo + include a requirement/task to update the data catalog.
Collect res and condo third-party data (spatial data, environmental data, etc). Update the ingest scripts in aws-s3
directory.
Microsoft publishes US building footprint data that contains GeoJSON polygons of (almost) every building in Cook County. ISGS publishes LiDAR-based estimates of height, including building height.
Condos currently lack building or unit square footage, but we may be able to combine these two data sources to produce an estimate of building size or even a rough unit sqft.
st_coords
The dbt models defined in #23 (and potentially #24) all included a _test
suffix in order to prevent us from overwriting production views before we had dedicated dev/prod environments in place (#28).
Once the environments have been established, update all relevant models to remove the unnecessary _test
suffix from their model names, which will allow us to start tracking our production models using dbt.
Depends on #28.
Many buildings in the city and denser suburbs have shared walls (i.e. they are touching). This not only applies to rowhomes and townhomes, but also mixed multi-family housing like 211s and 212s.
We should create a "shared wall" or "touching" indicator for each parcel. This indicator may not only affect value, it is also useful for things like averaging groups of like buildings (based on the assumption that building groups that share walls are likely to have similar values).
We can create this feature using parcel boundary and building footprint data already in S3. Spatial relations such as "touching" should be sufficient for a first pass attempt.
The modeling views share a lot of code that shouldn't be reproduced. We can move this code into its own views that can be shared across the modelling views to ensure consistency:
acs5 AS (
SELECT *
FROM census.vw_acs5_stat
WHERE geography = 'tract'
),
housing_index AS (
SELECT
geoid,
year,
AVG(CAST(ihs_index AS DOUBLE)) AS ihs_avg_year_index
FROM other.ihs_index
GROUP BY geoid, year
),
sqft_percentiles AS (
SELECT
ch.year,
leg.user1 AS township_code,
CAST(APPROX_PERCENTILE(ch.char_land_sf, 0.95) AS INT)
AS char_land_sf_95_percentile
FROM default.vw_pin_condo_char AS ch
LEFT JOIN iasworld.legdat AS leg
ON ch.pin = leg.parid AND ch.year = leg.taxyr
GROUP BY ch.year, leg.user1
),
tax_bill_amount AS (
SELECT
pardat.parid AS pin,
pardat.taxyr AS year,
pin.tax_bill_total AS tot_tax_amt,
tax_code.tax_code_rate AS tax_rate
FROM iasworld.pardat
LEFT JOIN tax.pin
ON pardat.parid = pin.pin
AND (
CASE WHEN pardat.taxyr > (SELECT MAX(year) FROM tax.pin)
THEN (SELECT MAX(year) FROM tax.pin)
ELSE pardat.taxyr
END = pin.year
)
LEFT JOIN (
SELECT DISTINCT
year,
tax_code_num,
tax_code_rate
FROM tax.tax_code
) AS tax_code
ON pin.tax_code_num = tax_code.tax_code_num
AND pin.year = tax_code.year
WHERE pin.pin IS NOT NULL
),
school_district_ratings AS (
SELECT
district_geoid,
district_type,
AVG(rating) AS school_district_avg_rating,
COUNT(*) AS num_schools_in_district
FROM other.great_schools_rating
GROUP BY district_geoid, district_type
)
Currently the public needs to download individual files from our website if they want commercial valuation data. We can aggregate it and host it on socrata.
We need to investigate the following data integrity tests that are currently failing.
Queries for each of these tests are saved under the test name in the Athena saved query dashboard. These queries should be useful for investigation.
vw_pin_appeal
vw_pin_appeal_unique_by_14_digit_pin_and_year
vw_pin_appeal_unique_by_case_number_and_year
vw_pin_appeal_no_unexpected_change_values
vw_pin_appeal_change_matches_appeal_outcome
vw_card_res_char
vw_card_res_char_unique_by_card_and_year
vw_card_res_char_renovation_fields_match
vw_pin_address
vw_pin_condo_char
unique_vw_pin_sale_doc_no
vw_pin_sale_reasonable_number_of_sales_per_year
Placeholder issue for upcoming "Data Mart" / "Factbook" progress in Q423 or Q124.
I anticipate we will be much faster at defining data integrity checks for our models than we are at actually fixing the data problems revealed by those checks. If that's true, we'll need to wrap dbt test
in a script that can distinguish between expected and unexpected test failures, or else our CI test runs will fail constantly.
I'm not totally sure what the best way to do this is, so this issue will require some design work. We'll probably want some sort of version controlled config file that we can use to map tests to their expected number of failures; perhaps we can even include this in the dbt schema.yml
files via a plugin API.
Design and implement a data catalog system for internal use that improves our data documentation, quality, and orchestration.
The Data Department currently has an extremely ad-hoc and incomplete data catalog system, spread out over multiple spreadsheets and services. We should consolidate these efforts into a single place or platform to serve as a "source of truth" for our department. Ideally, the catalog should provide (in descending rank order of priority):
See "Initial Issues" in the draft architecture project overview here.
This seems like an area where lots of good software solutions already exist, since these needs are common across organizations. The two I've looked at are DBT and Glue, both of which could likely meet the needs provided above. However, I'm sure there are plenty of other possible solutions.
We have some barebones documentation in dbt/README.md
, but currently it only explains how to install requirements and run some basic build and test commands. We should also document the scope of the project more broadly and explain how to develop it.
Some topics to cover, off the top of my head:
materialization
trickinesscleanup-dbt-resources
failsCompilation Error
dbt found two schema.yml entries for the same resource named location.vw_pin10_location. Resources and their associated columns may only be described a single time. To fix this, remove one of the resource entries for location.vw_pin10_location in this file:
- models/location/schema.yml
Depends on the final pieces of infrastructure being complete, namely
#55 and ccao-data/service-sqoop-iasworld#1.
Land aggregation is tricky. To make sure we get it right across all views, we should only pull it from default.vw_pin_land.
I need to produce an issue with the following information:
The goal here is to have a central source of truth that we can use to track our progress resolving these issues.
Running dbt docs generate
in the dbt/
directory will generate target/index.html
, an HTML version of the documentation for our dbt models and tests. Use this file to deploy our documentation as a static site.
There are a number of good options for deploying static sites; in roughly descending order of preference:
GitHub Pages is preferred since it is easy to use and already integrated into GitHub; S3 + CloudFront is next on the list since we already pay for AWS; and Netlify is last in the priority queue because we don't use it yet, but it's very easy to set up so it's a plausible option if the first two don't work for whatever reason.
The models defined in our dbt project currently only represent the Athena views that we build on top of our source data, but we would also like to include models for our source data tables so that we can add them to our data catalog documentation. To accomplish this, we'll need to add "stub" models that don't actually build new data, they just allow dbt to recognize the model as a valid entity that can be documented.
For guidance, see this dbt community post: https://discourse.getdbt.com/t/representing-non-sql-models-in-a-dbt-dag/2083
If this issue gets picked up before we have a proper dev/prod split set up (#28), it might be wise to continue following the pattern of suffixing models with _test
until we have environments up and we can safely remove the suffix (#29).
Inspired by this thread. Briefly: We want to avoid duplication between dbt/models/
and aws-athena/views/
as much as possible during development of the data catalog; in order to do that, we need to have these two versions of our views share as much logic as possible.
To do that, we plan to create a long-lived branch named data-catalog
that we will use for development of the data catalog. We will merge all of our data catalog work into this branch. On the data-catalog
branch, dbt models will be symlinked to the view definitions in aws-athena/views/
, and tweaked minimally so as to make them work with dbt (most importantly, removing CREATE TABLE AS
statements and using dbt references for other views in the DAG). Then, as changes are made to the views on the master
branch, we can merge them into data-catalog
and resolve merge conflicts; when we're ready to go live with the data catalog, we can merge data-catalog
into master
.
A couple (2) duplicates have made there way back into this view as it's gotten larger and more complicated. It also includes PINs it shouldn't (namely those that have been deactivated).
Assumption: corner lots tend to be more valuable than non-corner lots due to their size, increased accessibility (for commercial), and prominent location.
Given this assumption, we should add a corner lot indicator variable to all AVMs, as well as to commercial valuation spreadsheets/methods. We can impute a corner lot variable using a variety of GIS methods, including:
We should use a combination of methods to output a single indicator (0 or 1) for each PIN in the county. N at Mansueto and J at CSDS will likely have good ideas on this.
Create an R script that takes property locations (lat, lon) or shapes (parcel file) as an input, then outputs a corner lot indicator (1 or 0) for each PIN. Start with a prototype script using local data. The final script should pull raw data from S3 and write back to S3. It should run as a glue job. The script should be saved to was-glue/jobs/location-corners.R
. Use the branch attached to this issue to save your work and submit it for review.
@miaoqi, figuring out how to identify these lots algorithmically will be a large part of this task. There is existing literature in the space in various geography and spatial data science journals. I can also put you in touch with folks at UChicago who can provide help if needed. Let's aim to have a running script by Feb 15th.
Some R libraries you'll probably need to get started:
sf
, for all spatial operationsigraph
, for any graph manipulation i.e. street networksosmdata
, for OpenStreetMap data i.e. roadsThe taxdist
field in iasWorld's LEGDAT
table contains the unique Cook County tax code of each PIN. Tax codes can change year-over-year as new taxing districts are created or destroyed. The LEGDAT
table taxdist
field is currently not updated to reflect those changes, it is frozen to tax year 2021 (even for later years). As such, this field should not be used for anything other than determining a PIN's township code.
Once the Clerk migrates to iasWorld, the responsibility for updating this field (and retroactively updating it) will fall to them.
We want to use GitHub Actions to run the dbt run
and dbt test
commands that build and test our DAG, respectively. To do this, we'll need to add GitHub Actions workflow definitions to this repository.
Our workflow definition should support two different types of flows:
These two flows will be used in two different ways:
Caching is important in CI to help speed up development cycles, but it's unnecessary in the context of our nightly data integrity checks, where we want to validate all of the data on each run.
The CI workflow (1 above) should exhibit the following cache behavior:
master
OR since the last successful workflow run for this PR
master
branch, and subsequent runs should use the cache from the first successful run on the PR branchmaster
branch cache should never be updated by this flowmaster
:
master
prod
Athena environmentmaster
branch cache should be updated when this flow succeedsIn order to run dbt commands against Athena from the context of a GitHub Action workflow, we'll need to inject valid AWS credentials into the workflow. Credentials should be stored as encrypted secrets and should have their permissions restricted as much as possible to reduce the attack surface of the credentials. See the dbt-athena
docs for a list of the required permissions for the adapter.
Since our CI tests will only be useful if they can distinguish between expected and unexpected data integrity issues, this issue depends on #32.
We anticipate being asked to provide pins within or in addition to their current political boundaries. This data doesn't live in any tables or views currently because it's not relevant to our modeling pipeline.
Per valuations, when a condo building is demolished, often its primary PIN will revert to vacant land or an otherwise land-only class, however, the sub-PINs for each individual unit may still be 299s and thus included in reporting etc.
We should add a check for this edge case to the model and reporting to ensure anything that changes from a 299 to a land-only class is booted from the model and stats.
Currently, AWS resources are configured manually via the console. However, this approach will not scale as our infrastructure becomes more complicated. We should create configurations for Glue, S3, etc. using CloudFormation or Terraform.
Once we've added models and documentation for the remaining Athena views that we want to build with dbt (#26), we'll want to add tests for them. We'll also want to finish up the tests for all of the existing models that are marked with TODO (indicating that Jean didn't understand the test and required clarification). Billy will be a helpful resource for defining the remaining tests.
See this doc for explanations of the remaining tests.
This is a separate story since I'm anticipating that the work to define and write these tests will generate enough code to warrant a dedicated pull request, but if that's not true, feel free to roll it into #26.
I noticed that the most recent CI run for the data-catalog
branch did not (re)build any of the models we added or changed in #64. Testing things out locally, I noticed two problems:
state:new
selector in our workflow configuration, which is intended to select for new modelsstate:new
nor state:modified
properly picks up additions or changes to the model definitionDuckDB is the new thing in the data engineering space right now. It's basically SQLite but for analytical workloads instead of application workloads. It might fit our use-case pretty well and simplify some existing/future complexity. We should investigate its viability for our data at some point in the near future.
DuckDB's biggest value add for us is its ability to read Parquet files directly from S3. Something like the code below could be used to create a local database file full of views that are effectively pointers to S3 buckets. We could then build views on top of those pointers, just like in Athena. This would essentially make a DuckDB file our main query layer, replacing Athena.
library(duckdb)
conn <- DBI::dbConnect(duckdb("test.db"))
dbExecute(
conn,
"
INSTALL httpfs;
LOAD httpfs;
SET s3_region='---';
SET s3_access_key_id='---';
SET s3_secret_access_key='---';
"
)
dbExecute(
conn,
"
CREATE OR REPLACE SCHEMA model;
CREATE OR REPLACE VIEW model.shap AS
SELECT *
FROM parquet_scan('s3://bucket_name/shap/year=2023/run_id=2023-03-15-clever-kyra/*/*.parquet', hive_partitioning=true)
"
)
shap <- dbGetQuery(
conn,
"
SELECT *
FROM model.shap
WHERE run_id = '2023-03-15-clever-kyra'
AND year = '2023'
AND township_code = '77'
"
)
I think DuckDB could essentially be a drop-in replacement for Athena. We would create stub views (as shown above) that act as tables currently do in Athena. We would then just build our existing views on top of those pointers.
The result would be a local file that contains pointers and view definitions for our entire data lake. This file would be built and tested via GitHub Actions and then uploaded to S3. From there, we could download a single local copy of the file to the main Data Dept. server, and point all connections to that file. Any changes to the views or underlying data would kick off a new workflow and replace the local file.
If we wanted, we could also just build the entire current version of the data lake into the DB file. This would likely speed things up even further for local queries on the server.
We should add style and lint pre-commit hooks for everything in aws-s3/
. Update .pre-commit-config.yaml
then run pre-commit run --all-files
.
I would consider adding the Action for GitHub's super linter.
char_recent_renovation isn't partitioned by card, which it must be since dweldat isn't unique by just PIN.
The dbt profile created in #23 only supports a development Athena database. We would also like to add a profile that supports building views and tables into the production Athena databases.
This might be slightly tricky, since we override the database defined in profiles.yml
via a folder-level config in the models
attribute of dbt_project.yml
. We do this so that we can build models into separate databases depending on their surrounding subdirectory in the models/
directory, e.g. default/
or location/
. This means we can't just create a new prod
entry in profiles.yml
, which is the recommended way of supporting multiple environments.
We may want to investigate using a separate Athena Data Catalog for dev and production, but that would depend on dev being able to read data from the production catalog, since our source data is built directly into the production Data Catalog.
The UChicago Center for Spatial Data Science (CSDS) has created a new set of national tract-to-tract travel time matrices for different modes of transit. We should ingest these and then potentially replace some of our "distance to X" features with actual travel times. Long-term, exploratory project, no rush. Ask @dfsnow for the link to the files.
We need to complete our testing regime for DBT by documenting how our tables and views should function.
Currently, all our AWS Glue crawlers must be triggered manually or are run on a schedule (immediately after some files are expected to update). A better design would have the crawlers run automatically when new files are posted to the relevant S3 bucket.
This is a common design pattern for which there are many tutorials:
Design and implement a system of automated data integrity checks to validate our expectations about fields and join results. We need unit tests for data + transforms.
The CCAO has a complex backend system-of-record that ingests data from many processes, people, and places. Unfortunately, this system also lacks many of the data validation and integrity checks found in more robust systems. The result is frequent issues with incorrect records and invalid data types (e.g. a PIN populating a numeric field). Such errors flow downstream until they reach the Data Department's lake, where we will manually flag them for correction (via email).
We need to develop a much more robust and automated process for performing checks to detect invalid data and bad joins. I'm imagining this as a sort of "unit tests for data," wherein we have expectations/assertions about what the possible values of a field are (or what the results of a join should be) that must be met. Ideally, such a system would also provide a way to catalog any errors and notify relevant parties. I imagine this as an extension of #10, and likely built on top of that system.
See "Initial Issues" in the draft architecture project overview here.
A declarative, efficient, and flexible JavaScript library for building user interfaces.
๐ Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. ๐๐๐
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google โค๏ธ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.